Re: [PATCH] New predefined role pg_manage_extensions

On Fri, 7 Mar 2025 at 15:37, Michael Banck <mbanck(at)gmx(dot)net> wrote:
> On Fri, Mar 07, 2025 at 09:17:46AM -0500, Robert Haas wrote:
> > Why wouldn't the cloud provider just change add 'trusted = true' to
> > the relevant control files instead of doing this?
>
> That would be possible, but maybe the cloud provider is using
> distribution packages and does not want to muck around in the file
> system (as is usually frowned upon), or, maybe more likely, is using
> container images based on (what I've seen most of them are) the Debian
> packages and cannot (or does not want to anyway) muck around in the file
> system easily.

Yeah exactly, having to do this for every extension that you onboard
is quite a hassle to maintain. It seems much nicer to allow people to
assign a single role and be done with it.

Also many cloud providers have some slightly forked/extended postgres
to allow this already.

> Also, I think there is case to be made that a cloud provider (or site
> admin) would like to delegate the decision whether users with CREATE
> rights on a particular database are allowed to install some extensions
> or not. Or rather, assign somebody they believe would make the right
> call to do that, by granting pg_manage_extensions.

I think this is a really good point. Adding trusted=true gives any
database owner the ability to install these more dangerous extensions.
While by using pg_manage_extensions you can limit this ability to the
cluster administrator.

> On the other hand, maybe trusted should be part of the catalog and not
> (just) the extension control file, so that somebody with appropriate
> permissions (like the cloud provider during instance bootstrap) could do
> "ALTER EXTENSION foo (SET trusted|TRUSTED);" or whatever. ISTR that I
> reviewed the discussion around trusted back then and did not see that
> possiblity discussed at all, but I might be misremembering, it's been a
> while.

I think that would be hard because there's no record in the
pg_extension for extensions that are not installed. So there's also no
way to mark such an extension as trusted. To be able to do this we'd
probably need a system-wide catalog. If we'd go this route then I
think what we'd really want is a way to do:

GRANT INSTALL ON EXTENSION TO user;

And that seems orthogonal to having this pg_manage_extensions role,
because then pg_manage_extensions could grant that permission to
people.