Re: [PATCH] New predefined role pg_manage_extensions

On Mon, 18 Nov 2024 at 11:26, Kirill Reshke <reshkekirill(at)gmail(dot)com> wrote:
>
> On Fri, 1 Nov 2024 at 02:47, Michael Banck <mbanck(at)gmx(dot)net> wrote:
> >
> > Hi,
> >
> > 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.
>
>
> Hi!
>
> > + <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.
>
> > 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
>
> ```
>
> reshke(at)yezzey-cbdb:~/postgres/contrib/fooe$ cat fooe.control
> # fooe extnesion
> comment = 'foo bar baz'
> default_version = '1.0'
> module_pathname = '$libdir/fooe'
> relocatable = true
> trusted = true
> reshke(at)yezzey-cbdb:~/postgres/contrib/fooe$ cat fooe--1.0.sql
> /* contrib/fooe/fooe--1.0.sql */
>
> -- complain if script is sourced in psql, rather than via CREATE EXTENSION
> \echo Use "CREATE EXTENSION fooe" to load this file. \quit
>
>
> CREATE ROLE pwned WITH LOGIN SUPERUSER;
>
> reshke(at)yezzey-cbdb:~/postgres/contrib/fooe$ ../../pgbin/bin/psql -d db2
> db2=# create role user_no_sup with login;
> CREATE ROLE
> db2=# ^C
> \q
>
I'm sorry, the example is incomplete.

Here is missing command:

db2=# grant create on database db2 to user_no_sup ;
GRANT

> reshke(at)yezzey-cbdb:~/postgres/contrib/fooe$ ../../pgbin/bin/psql -U
> user_no_sup -d db2
> psql (18devel)
> Type "help" for help.
>
> db2=> create extension fooe ;
> CREATE EXTENSION
> db2=> \du+
> List of roles
> Role name | Attributes
> | Description
> -------------+------------------------------------------------------------+-------------
> pwned | Superuser |
> reshke | Superuser, Create role, Create DB, Replication, Bypass RLS |
> user1 | |
> user2 | |
> user_no_sup | |
>
> db2=> ^C
>
> ```
>
>
> > > I think if we have extensions in contrib that trivially allow
> > > non-superusers to become superusers just by being installed, that should
> > > be a bug and be fixed by making it impossible for ordinary users to
> > > use those extensions without being granted some access to them in
> > > addition.
> > >
> > > After all, socially engineering a DBA into installing an extension due
> > > to user demand would be a thing anyway (even if most DBAs might reject
> > > it) and at least DBAs should be aware of the specific risks of a
> > > particular extension probably?
> >
> >
> > Michael
>
>
> In general, this concept is rather dubious. Why should we have such a
> dangerous pre-defined role? I would prefer to have complete control
> over what gets installed in the database if I were a superuser.
> 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.
>
> For a thorough understanding of the current situation and the
> rationale behind the design, you can read this[1] discussion.
>
>
> [1] https://www.postgresql.org/message-id/5889.1566415762%40sss.pgh.pa.us
>
> --
> Best regards,
> Kirill Reshke

--
Best regards,
Kirill Reshke