Date: Tue, 2 Sep 2025 17:02:41 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Jelte Fennema-Nio <me@jeltef.nl>
Cc: Robert Haas <robertmhaas@gmail.com>,
	Artem Gavrilov <artem.gavrilov@percona.com>,
	Tomas Vondra <tomas@vondra.me>,
	"David G. Johnston" <david.g.johnston@gmail.com>,
	Jeff Davis <pgsql@j-davis.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Extension security improvement: Add support for extensions with
 an owned schema
Message-ID: <aLaysb-v12hPW22V@jrouhaud>
References: 
 <CAKFQuwaT4_n=e0YKBZAyox1CQUra2ka0cySs+3pGZR5p50pn-g@mail.gmail.com>
 <CAGECzQTOJrnnJkmMe9nems0jouiKUbFcEb1rb9kE_svsAZiGQg@mail.gmail.com>
 <585e996c-a5c6-4e61-acc4-d92b7a1458ea@vondra.me>
 <CAGECzQS02M6YPDXemo36tShO-ZYObjqnyTJyVttua1PGyN4xRw@mail.gmail.com>
 <CAFPkQKzALOTTBrhj2qDHwVxZQyjF5Xg_P9M=Tn_Dcm3vr=xdTA@mail.gmail.com>
 <DBOFQN54M5FX.1XWJE4LKMR8XH@jeltef.nl>
 <CA+TgmoY=NO7_L=UDuoUWj-icABF-7EP=UNUXCFBYpDNFoUZmbA@mail.gmail.com>
 <CA+TgmoYDdYA1paUKtfHfx-iDdCKrL05m2OwPHz7SQ03t49f2oQ@mail.gmail.com>
 <CAOBaU_YTJwo=jevDDKXRjwFUqON2VoWqz=Aw0FedyxbfYSiisw@mail.gmail.com>
 <CAGECzQS9JqWv+zJR-e-1JMH7GhCnLc4vD9H-uEui8E5Ba9Trpw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAGECzQS9JqWv+zJR-e-1JMH7GhCnLc4vD9H-uEui8E5Ba9Trpw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aLaysb-v12hPW22V%40jrouhaud>
Precedence: bulk

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@gmail.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?