Date: Sat, 6 Sep 2025 08:17:03 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Robert Haas <robertmhaas@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>
Subject: Re: Extension security improvement: Add support for extensions with
 an owned schema
Message-ID: <aLt9f7u_jUnMgGOe@jrouhaud>
References: <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>
 <CA+TgmoawwAoRZH2Hm8w-RP1QOebK9LQ=NzeJWWAz+pYhSQPT0g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CA+TgmoawwAoRZH2Hm8w-RP1QOebK9LQ=NzeJWWAz+pYhSQPT0g@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aLt9f7u_jUnMgGOe%40jrouhaud>
Precedence: bulk

Hi,

On Tue, Sep 02, 2025 at 09:35:45AM -0400, Robert Haas wrote:
> On Tue, Sep 2, 2025 at 5:02 AM 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 plain
> > 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

Requiring schema owner privilege wouldn't allow the user who created the
extension to allow other users to mess up with the extension's private schema?
At least not with a simple GRANT on the schema.

I'm also wondering how much you can prevent the owner from doing changes in the
owned schema without leading to unhelpful behavior.  Would the owner still be
allowed to create extra indexes on extension owned table for instance, change
the TOAST setting, move them to other tablespace or ...?

> 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.

Arguably there is pg_extension_config_dump() for that, assuming that this would
become allowed in scenario like the one I described (and modified to also emit
the DDL in such case).  But it would be hard for extension authors to use as
you would have to call it only with some major versions.

We could do it automatically, but extensions may not need to dump all tables
and/or all rows.  For instance in powa we have some unlogged tables that are
use transiently during a snapshot, and those tables shouldn't be dumped.  Right
now if such tables are created by the extension they're always dumped, but it
would be good to make it configurable if dynamically created tables become part
of the extension, one way or another.