MIME-Version: 1.0
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>
 <aLaysb-v12hPW22V@jrouhaud>
In-Reply-To: <aLaysb-v12hPW22V@jrouhaud>
From: Robert Haas <robertmhaas@gmail.com>
Date: Tue, 2 Sep 2025 09:35:45 -0400
Message-ID: 
 <CA+TgmoawwAoRZH2Hm8w-RP1QOebK9LQ=NzeJWWAz+pYhSQPT0g@mail.gmail.com>
Subject: Re: Extension security improvement: Add support for extensions with
 an owned schema
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Jelte Fennema-Nio <me@jeltef.nl>,
 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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoawwAoRZH2Hm8w-RP1QOebK9LQ%3DNzeJWWAz%2BpYhSQPT0g%40mail.gmail.com>
Precedence: bulk

On Tue, Sep 2, 2025 at 5:02=E2=80=AFAM Julien Rouhaud <rjuju123@gmail.com> =
wrote:
> 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 pl=
ain
> plpgsql and work just fine.
>
> Why not requiring schema owner privileges?

I agree with you that requiring superuser privileges is undesirable.
However, requiring schema owner privileges isn't really requiring
anything above and beyond what the permissions system would require
anyway, since at the start, nobody else will have privileges on that
schema. And that's where what Jelte was proposing -- and also what you
propose here -- seems very accident-prone to me. It would be quite
easy for the user who created the extension, and who therefore also
owns the schema IIUC, to accidentally put other stuff there, or allow
others to do so, and that might undermine the safe search_path
guarantee that is the purpose of this patch. I am not fixed on any
particular method of making that guarantee more robust, but I am in
favor of trying to come up with some way of making it more robust.

The first thought that popped into my head was that maybe your
extension should make the objects it creates part of the extension,
but I think that doesn't actually work, because it would mean that
they would not be dumped. I think what you've got is something
intermediate between full extension membership (where installing the
extension creates the objects) and non-membership (where the objects
are created by the user and unaffiliated with the extension) but we
have no concept of that in the system today. Should we?

Another thing to consider here is what happens when somebody tries to
drop the extension. As Jelte coded it in the last version I read, it
just blows away the schema. I don't remember whether it used RESTRICT
(in which case the presence of unrelated objects in the schema would
result in failure) or CASCADE (in which case they would be silently
blown away) but I don't like either option very much. The former would
be pretty inconvenient in a use case like yours, and the latter would
be terrible if there were valuable, unrelated objects in the schema. I
think CASCADE is the right answer as long as there's something that
makes it unlikely that the schema has any unanticipated contents.

Here are a few possible ways forward:

(1) Invent, as proposed above, an intermediate level of extension
membership where objects are dropped with the extension but are still
dumped, and find a way for extensions to create objects in an owned
schema only if the new object will be either fully owned by the
extension or at least at this intermediate level.

(2) Decide that extensions that create additional objects in the
schema shouldn't use owned schemas, and document this.

(3) Add a mechanism to track when an extension-owned function is
executing, and only allow objects to be created in an extension-owned
schema when that's the case.

(4) Add a GUC that overrides the usual prohibition on creating objects
in an extension-owned schema, and let users or the extension set it if
they wish.

(5) Decide I'm wrong and that we should just let objects be created
freely in the owned schema. Document the consequences of this and
blame any problems on user error.

(6) Your superior idea goes here!

--=20
Robert Haas
EDB: http://www.enterprisedb.com