Re: Extension security improvement: Add support for extensions with an owned schema

On Tue, Sep 02, 2025 at 09:37:31AM +0200, Jelte Fennema-Nio wrote:
> On Tue, 2 Sept 2025 at 02:03, Julien Rouhaud <rjuju123(at)gmail(dot)com> 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?