Re: [PATCH] New predefined role pg_manage_extensions

Hi,

first, sorry for the late reply :-/

On Mon, Nov 18, 2024 at 11:26:40AM +0500, Kirill Reshke wrote:
> On Fri, 1 Nov 2024 at 02:47, Michael Banck <mbanck(at)gmx(dot)net> wrote:
> > Even though there has not been a lot of discussion on this, here is a
> > rebased patch. I have also added it to the upcoming commitfest.
> >
> > + <literal>pg_manage_extensions</literal> allows creating, removing or
> > + updating extensions, even if the extensions are untrusted or the user is
> > + not the database owner.
>
> Users are not required to be a database owner to create extensions.
> They are required to have CREATE priv on database.

Ah right, thanks, I have changed that.

> > On Sat, Jan 13, 2024 at 09:20:40AM +0100, Michael Banck wrote:
> > > On Fri, Jan 12, 2024 at 04:13:27PM +0100, Jelte Fennema-Nio wrote:
> > > > But I'm not sure that such a pg_manage_extensions role would have any
> > > > fewer permissions than superuser in practice.
> > >
> > > Note that just being able to create an extension does not give blanket
> > > permission to use it. I did a few checks with things I thought might be
> > > problematic like adminpack or plpython3u, and a pg_manage_extensions
> > > user is not allowed to call those functions or use the untrusted
> > > language.
> > >
> > > > Afaik many extensions that are not marked as trusted, are not trusted
> > > > because they would allow fairly trivial privilege escalation to
> > > > superuser if they were.
> > >
> > > While that might be true (or we err on the side of caution), I thought
> > > the rationale was more that they either disclose more information about
> > > the database server than we want to disclose to ordinary users, or that
> > > they allow access to the file system etc.
>
> Extension installation script can execute arbitrary code with
> superuser privilege if markled trusted.
>
> Take this example

[...]

Right, but this implies that a superuser installed that
rogue/sketchy/unsafe-but-declared-safe extension in the first place.
I would assume that the person having pg_manage_extensions privs not
have the ability to install extensions at the system level. Maybe this
should be cautioned some more in the documentation part of this patch,
though.

Unless one of the current untrusted contrib extensions allows to attain
superuser rights by being created?

My opinion was that superusers (or system administrators) would need to
explicitly install this (presumably external) extension somehow, either
via package management or by compile-installing it. So, what is the
threat vector here? I think the system administrator has (in most/all(?)
cases) superuser access to Postgres in practise anyway, via sudo/root
access to the postgres user.

Maybe this should be revisited in light of the extension_destdir GUC
patch, but I think in that case the system admins/postgres superuser
should make sure that this auxiliary directory is not writable to
others.

> In general, this concept is rather dubious. Why should we have such a
> dangerous pre-defined role?

Well, I would say pg_execute_server_program could be regarded as a
precedent.

> I would prefer to have complete control over what gets installed in
> the database if I were a superuser.

A superuser will have to grant this attribute to somebody, so there is
certainly some gate-keeping here.

As a side note, maybe what we are missing is a way for site admins to
disable some of the predefined roles (i.e. something better than just
DELETE FROM pg_authid).

> Additionally, if a dangerous extension is inadvertently or otherwise
> loaded on the host, I never want a normal user to run code with
> superuser privileges.

Well again, normal users with pg_execute_server_program rights can
basically already do this. I would consider a user with a
pg_manage_extensions right not a normal user, but an application DBA
or a pseudo-superuser in the managed Postgres cloud parlance.

> For a thorough understanding of the current situation and the
> rationale behind the design, you can read this[1] discussion.

I have re-read this since, thanks.

I do think having a whitelist of allowed-to-be-installed extensions
(similar/like https://github.com/dimitri/pgextwlist) makes sense
additionally in today's container/cloud word where the local Postgres
admin might not have control over which packages get installed but wants
to have control over which extension the application admins (or whoever)
may create, but that is another topic I think.

Michael