On Tue, Sep 02, 2025 at 09:37:31AM +0200, Jelte Fennema-Nio wrote: > On Tue, 2 Sept 2025 at 02:03, Julien Rouhaud <[email protected]> wrote: > > One not too uncommon scenario is an extension in a dedicated schema that > > creates additional objects dynamically, for instance creating new > > partitions using triggers on one of the extension table. > > Interesting. I didn't know there were extensions that did that. That > definitely doesn't seem like a very common pattern though.
I think that there are way more extensions that dynamically create objects than what you think. Some years ago I was working on such an extension at work, and pgtt is also creating some objects under the hood. That's already 3 extensions that I know on top of my head without having to think about it. > But I don't think that's a problem for this idea. In the > implementation I'm working on, superuser would still be allowed to > create objects in such locked down owned schemas. So as long as the > extension upgrades its permissions to superuser during these DDLs it > should still be fine. (easy to do with SECURITY DEFINER or by > temporarily changing permissions from C) Requiring superuser permission seems like a big penalty, especially since the last few years have been all about *not* requiring superuser privileges. Note also that not all extensions embeds compiled code, some are just doing plain plpgsql and work just fine. Why not requiring schema owner privileges?
