MIME-Version: 1.0
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>
 <aLt9f7u_jUnMgGOe@jrouhaud>
In-Reply-To: <aLt9f7u_jUnMgGOe@jrouhaud>
From: Jelte Fennema-Nio <me@jeltef.nl>
Date: Sat, 6 Sep 2025 09:35:10 +0200
Message-ID: 
 <CAGECzQR8gnJ92R2joimAfg6VX_VZO2Dy2n2gG-Ozr3zQ7evmSA@mail.gmail.com>
Subject: Re: Extension security improvement: Add support for extensions with
 an owned schema
To: Julien Rouhaud <rjuju123@gmail.com>
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>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGECzQR8gnJ92R2joimAfg6VX_VZO2Dy2n2gG-Ozr3zQ7evmSA%40mail.gmail.com>
Precedence: bulk

On Sat, 6 Sept 2025 at 02:17, Julien Rouhaud <rjuju123@gmail.com> wrote:
> 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 think that sounds like reasonable change to Roberts initial
proposal: Allowing the schema owner and superusers to add objects in
the schema, but disallow all other users (even if they have CREATE
privileges on the schema).

I think this seems reasonable from a security perspective. The thing
owned_schema protects against, is accidentally executing code with
permissions of the extension script runner. The owner of the schema is
always the same user as the extension script runner. But it protects
users from the somewhat easy to make mistake of GRANT ALL ON SCHEMA
(instead of GRANT USAGE ON SCHEMA).

Note that this means that even with trusted=true, a non-superuser
extension owner would still not be able to the schema. For that
superuser=false is needed in the control file.

The only thing I'm wondering is if we should allow changing the schema
owner with ALTER SCHEMA OWNER TO. Because that would break this
assumption:
> The owner of the schema is always the same user as the extension script runner.

But that command seems unlikely to be run by accident. But on the
other hand, I don't really see a usecase for changing the schema
owner, except for breaking this protection. So I'm leaning towards
disallowing ALTER SCHEMA OWNER TO on the schema, probably even for
superusers.