MIME-Version: 1.0
References: <65a1524e.050a0220.49266.7670@mx.google.com> <CAGECzQQ2HB85N9PjTAdDTpFCciQmpeE2PcXbc8EKhSF=RPi3fA@mail.gmail.com>
 <65a247d9.050a0220.ecb2e.1177@mx.google.com> <6723fae4.a70a0220.28a8f2.27e4@mx.google.com>
 <CALdSSPi+9htTHtgm+y92uUgEEovhur5URkXDMCpR=ZqmQbxHhQ@mail.gmail.com>
In-Reply-To: <CALdSSPi+9htTHtgm+y92uUgEEovhur5URkXDMCpR=ZqmQbxHhQ@mail.gmail.com>
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Mon, 18 Nov 2024 11:30:09 +0500
Message-ID: <CALdSSPi7TCFHb0guBnqiQHi79PCawBW9jeV7r960RO1=YRkaTA@mail.gmail.com>
Subject: Re: [PATCH] New predefined role pg_manage_extensions
To: Michael Banck <mbanck@gmx.net>
Cc: Jelte Fennema-Nio <postgres@jeltef.nl>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CALdSSPi7TCFHb0guBnqiQHi79PCawBW9jeV7r960RO1%3DYRkaTA%40mail.gmail.com>
Precedence: bulk

On Mon, 18 Nov 2024 at 11:26, Kirill Reshke <reshkekirill@gmail.com> wrote:
>
> On Fri, 1 Nov 2024 at 02:47, Michael Banck <mbanck@gmx.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@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@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@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@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