Re: [PATCH] New predefined role pg_manage_extensions

Hi,

Chiming in as one of said providers...

> On Sat, 2025-04-05 at 19:07 -0400, Tom Lane wrote:
> > I took another look at this patch, and I still can't persuade
> > myself that a good case has been made for it. There are too
> > many scenarios where pg_manage_extensions would be a security
> > problem and too few where it seems to really improve manageability.

I agree extensions expose a large surface area making it possible for
privilege escalation. However, I think a new role that allows
CREATE EXTENSION is fairly similar to Trusted Extensions and the scrutiny that
requires. Providers that care about superuser distinction should already be
vetting extensions marked as trusted don't allow elevation today. If
they want to allow end-users to leverage the pg_manage_extensions role,
they would need to extend that review to all extensions that they are offering.

There isn't anything preventing a community extension from incorrectly marking a
dangerous (however that is viewed) extension as trusted.

> > What I'm wishing for is that some of the providers would show up here
> > and provide specific details (preferably patches) about what they are
> > changing and why. Then we could have an informed discussion about
> > how to make their lives less painful in the future.

superusers can always provide a SECURITY DEFINER function that wraps around
CREATE EXTENSION with some input, but end-users expect to call "CREATE
EXTENSION"
instead of some random function. I view these new roles as a quality
of life change
for users.

One reason we introduced our own extension role, 'rds_extensions', was
we had users
wanting to provide 'CREATE EXTENSION' functionality to their own end-users.
They were also not necessarily comfortable with allowing their users
to install *all*
extensions like Robert suggested so we provided a way for them to
specify a subset of
them through a parameter.

Going back to the patch, I think it depends on what we're trying to
solve. Given that
we've been trying to split out superuser powers out, do we want to
make it easier to
create "all extensions" like superuser, or delegate a subset of
extensions out without
having to edit all the control files as trusted.

--
John Hsu - Amazon Web Services