Re: [PATCH] New predefined role pg_manage_extensions

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

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