Thread: [PATCH] New predefined role pg_manage_extensions
Hi, I propose to add a new predefined role to Postgres, pg_manage_extensions. The idea is that it allows Superusers to delegate the rights to create, update or delete extensions to other roles, even if those extensions are not trusted or those users are not the database owner. I have attached a WIP patch for this. Thoughts? Michael
Attachment
On Fri, 12 Jan 2024 at 15:53, Michael Banck <mbanck@gmx.net> wrote: > I propose to add a new predefined role to Postgres, > pg_manage_extensions. The idea is that it allows Superusers to delegate > the rights to create, update or delete extensions to other roles, even > if those extensions are not trusted or those users are not the database > owner. I agree that extension creation is one of the main reasons people require superuser access, and I think it would be beneficial to try to reduce that. But I'm not sure that such a pg_manage_extensions role would have any fewer permissions than superuser in practice. 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.
Hi, 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. 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
On Thu, 31 Oct 2024 at 22:47, Michael Banck <mbanck@gmx.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. After considering this again, I actually think this is a good change. Basically all cloud providers already provide something similar. It would be great if we could have a standardized way of doing this. Regarding the actual patch: I think it looks good, but it needs some tests.
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
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
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
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@gmx.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