Horton

Thread

Commits

  1. Apply quotes more consistently to GUC names in logs

  1. [PATCH] New predefined role pg_manage_extensions

    Michael Banck <mbanck@gmx.net> — 2024-01-12T14:53:01Z

    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

  2. Re: [PATCH] New predefined role pg_manage_extensions

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2024-01-12T15:13:27Z

    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.

  3. Re: [PATCH] New predefined role pg_manage_extensions

    Michael Banck <mbanck@gmx.net> — 2024-01-13T08:20:40Z

    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

  4. Re: [PATCH] New predefined role pg_manage_extensions

    Michael Banck <mbanck@gmx.net> — 2024-10-31T21:47:16Z

    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.

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.
> 
> 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

  5. Re: [PATCH] New predefined role pg_manage_extensions

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2024-11-14T12:30:21Z

    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.

  6. Re: [PATCH] New predefined role pg_manage_extensions

    Kirill Reshke <reshkekirill@gmail.com> — 2024-11-18T06:26:40Z

    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

  7. Re: [PATCH] New predefined role pg_manage_extensions

    Kirill Reshke <reshkekirill@gmail.com> — 2024-11-18T06:30:09Z

    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

  8. Re: [PATCH] New predefined role pg_manage_extensions

    Michael Banck <mbanck@gmx.net> — 2024-12-31T11:15:53Z

    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

  9. Re: [PATCH] New predefined role pg_manage_extensions

    Shinya Kato <shinya11.kato@gmail.com> — 2025-01-16T07:09:44Z

    On Thu, Jan 16, 2025 at 3:31 PM Michael Banck <mbanck@gmx.net> wrote:

I agree with this idea.
I think it is natural to delegate a part of superuser privileges to
another role because superuser privilege is too strong.

> > 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.

Exactly. pg_execute_server_program can escalate to superuser
privileges, so pg_manage_extensions is not the only dangerous
pre-defined role.

> 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.

To use a certain extension, you may need to install the
postgresql-contrib package. In that case, is there a way to restrict
extensions other than the required one? Or is it unnecessary to impose
such restrictions?

Regards,
Shinya Kato

  10. Re: [PATCH] New predefined role pg_manage_extensions

    Michael Banck <mbanck@gmx.net> — 2025-01-16T07:35:32Z

    Hi,

On Thu, Jan 16, 2025 at 04:09:44PM +0900, Shinya Kato wrote:
> On Thu, Jan 16, 2025 at 3:31 PM Michael Banck <mbanck@gmx.net> wrote:
> > 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.
> 
> To use a certain extension, you may need to install the
> postgresql-contrib package. In that case, is there a way to restrict
> extensions other than the required one? Or is it unnecessary to impose
> such restrictions?

I was thinking about the following (increasinly common, I think)
use-case: we have a largish organisation where the platform/whatever
team wants to deploy Postgres in a uniform way and install the common
set of all contrib and external extensions that might be needed for each
instance. But then you have instance-specific admins that might want to
restrict the set of extensions their instance (or their developers/app
admins/whatever) is allowed to use. However, this is not the purpose of
the patch in discussion, just a side-remark that this functionality
would be good to have in addition.


Michael

  11. Re: [PATCH] New predefined role pg_manage_extensions

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-01-17T09:03:17Z

    On Thu, 2024-10-31 at 22:47 +0100, Michael Banck 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.

I had a look at the patch.

> --- a/doc/src/sgml/user-manag.sgml
> +++ b/doc/src/sgml/user-manag.sgml
> @@ -669,6 +669,17 @@ GRANT pg_signal_backend TO admin_user;
>       </listitem>
>      </varlistentry>
>  
> +    <varlistentry id="predefined-role-pg-manage-extensions" xreflabel="pg_manage_extensions">
> +     <term><varname>pg_manage_extensions</varname></term>
> +     <listitem>
> +      <para>
> +       <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.
> +      </para>
> +     </listitem>
> +    </varlistentry>
> +

The inaccuracy of the database owner has already been mentioned.

Should we say "creating, altering or dropping extensions" to make the connection to
the respective commands obvious?

> --- a/src/backend/commands/extension.c
> +++ b/src/backend/commands/extension.c
> @@ -994,13 +994,14 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
>     ListCell   *lc2;
>  
>     /*
> -    * Enforce superuser-ness if appropriate.  We postpone these checks until
> -    * here so that the control flags are correctly associated with the right
> +    * Enforce superuser-ness/membership of the pg_manage_extensions
> +    * predefined role if appropriate.  We postpone these checks until here
> +    * so that the control flags are correctly associated with the right
>      * script(s) if they happen to be set in secondary control files.
>      */

This is just an attempt to improve the English:

  If appropriate, enforce superuser-ness or membership of the predefined role
  pg_manage_extensions.

> -                    : errhint("Must be superuser to create this extension.")));
> +                    : errhint("Only roles with privileges of the \"%s\" role are allowed to create this extension.", "pg_manage_extensions")));

I don't see the point of breaking out the role name from the message.
We don't do that in other places.
Besides, I think that the mention of the superuser should be retained.
Moreover, I think that commit 8d9978a717 pretty much established that we
should not quote names if they contain underscores.
Perhaps:

  errhint("Must be superuser or member of pg_manage_extensions to create this extension.")));

Yours,
Laurenz Albe

  12. Re: [PATCH] New predefined role pg_manage_extensions

    Michael Banck <mbanck@gmx.net> — 2025-02-28T18:16:39Z

    Hi,

On Fri, Jan 17, 2025 at 10:03:17AM +0100, Laurenz Albe wrote:
> On Thu, 2024-10-31 at 22:47 +0100, Michael Banck 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.
> 
> I had a look at the patch.

Thanks! And sorry for the long delay...
 
> > --- a/doc/src/sgml/user-manag.sgml
> > +++ b/doc/src/sgml/user-manag.sgml
> > @@ -669,6 +669,17 @@ GRANT pg_signal_backend TO admin_user;
> >       </listitem>
> >      </varlistentry>
> >  
> > +    <varlistentry id="predefined-role-pg-manage-extensions" xreflabel="pg_manage_extensions">
> > +     <term><varname>pg_manage_extensions</varname></term>
> > +     <listitem>
> > +      <para>
> > +       <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.
> > +      </para>
> > +     </listitem>
> > +    </varlistentry>
> > +
> 
> The inaccuracy of the database owner has already been mentioned.

Right, I had already changed that in my tree but never sent out an
updated version with this.
 
> Should we say "creating, altering or dropping extensions" to make the connection to
> the respective commands obvious?

Alright, did so.

> > --- a/src/backend/commands/extension.c
> > +++ b/src/backend/commands/extension.c
> > @@ -994,13 +994,14 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
> >     ListCell   *lc2;
> >  
> >     /*
> > -    * Enforce superuser-ness if appropriate.  We postpone these checks until
> > -    * here so that the control flags are correctly associated with the right
> > +    * Enforce superuser-ness/membership of the pg_manage_extensions
> > +    * predefined role if appropriate.  We postpone these checks until here
> > +    * so that the control flags are correctly associated with the right
> >      * script(s) if they happen to be set in secondary control files.
> >      */
> 
> This is just an attempt to improve the English:
> 
>   If appropriate, enforce superuser-ness or membership of the predefined role
>   pg_manage_extensions.

Done.

> > -                    : errhint("Must be superuser to create this extension.")));
> > +                    : errhint("Only roles with privileges of the \"%s\" role are allowed to create this extension.", "pg_manage_extensions")));
> 
> I don't see the point of breaking out the role name from the message.
> We don't do that in other places.

We actually do, I think I modelled it after other predefined roles,
e.g.:

|src/backend/commands/subscriptioncmds.c:				 errdetail("Only roles with privileges of the \"%s\" role may create subscriptions.",
|src/backend/commands/subscriptioncmds.c-						   "pg_create_subscription")));
|--
|src/backend/commands/copy.c:						 errdetail("Only roles with privileges of the \"%s\" role may COPY to or from an external program.",
|src/backend/commands/copy.c-								   "pg_execute_server_program"),
|--
|src/backend/commands/copy.c:						 errdetail("Only roles with privileges of the \"%s\" role may COPY from a file.",
|src/backend/commands/copy.c-								   "pg_read_server_files"),
|--
|src/backend/commands/copy.c:						 errdetail("Only roles with privileges of the \"%s\" role may COPY to a file.",
|src/backend/commands/copy.c-								   "pg_write_server_files"),

However, those are all errdetail, while the previous "Must be superuser
to [...]" is errhint, and that error message has different hints based
on whether the extension is trusted or not...

> Besides, I think that the mention of the superuser should be retained.
> Moreover, I think that commit 8d9978a717 pretty much established that we
> should not quote names if they contain underscores.
> Perhaps:
> 
>   errhint("Must be superuser or member of pg_manage_extensions to create this extension.")));

Alright, I think it makes more sense to have it like that than the
above, so changed it to that.

New version 3 attached.


Michael

  13. Re: [PATCH] New predefined role pg_manage_extensions

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-03-07T13:14:04Z

    On Fri, 2025-02-28 at 19:16 +0100, Michael Banck wrote:
> New version 3 attached.

I am fine with v3, and I'll mark it "ready for committer".

I have been wondering about regression tests.
We cannot have them in the core tests, because we cannot rely on any
extension being available.
We could shove a test into a regression test for an existing contrib,
but that would be somewhat off-topic there.  Not sure what would be best.

Yours,
Laurenz Albe

  14. Re: [PATCH] New predefined role pg_manage_extensions

    Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> — 2025-03-07T13:40:00Z

    Laurenz Albe <laurenz.albe@cybertec.at> writes:

> On Fri, 2025-02-28 at 19:16 +0100, Michael Banck wrote:
>> New version 3 attached.
>
> I am fine with v3, and I'll mark it "ready for committer".
>
> I have been wondering about regression tests.
> We cannot have them in the core tests, because we cannot rely on any
> extension being available.

That's what the extensions in src/test/modules/ are for.  The
test_extensions subdirectory seems suitable, it has tests for all sorts
of behaviour around extensions.

- ilmari

  15. Re: [PATCH] New predefined role pg_manage_extensions

    Robert Haas <robertmhaas@gmail.com> — 2025-03-07T13:57:50Z

    On Fri, Jan 12, 2024 at 10:13 AM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
> 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.

I see that Jelte walked this comment back, but I think this issue
needs more discussion. I'm not intrinsically against having a role
like pg_execute_server_programs that allows escalation to superuser,
but I don't see how it would help a cloud provider whose goal is to
NOT allow administrators to escalate to superuser.

What am I missing?

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  16. Re: [PATCH] New predefined role pg_manage_extensions

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-03-07T14:02:15Z

    On Fri, 7 Mar 2025 at 14:58, Robert Haas <robertmhaas@gmail.com> wrote:
> I see that Jelte walked this comment back, but I think this issue
> needs more discussion. I'm not intrinsically against having a role
> like pg_execute_server_programs that allows escalation to superuser,
> but I don't see how it would help a cloud provider whose goal is to
> NOT allow administrators to escalate to superuser.
>
> What am I missing?

The reason why I walked back my comment was that cloud providers can
simply choose which extensions they actually add to the image. If an
extension is marked as not trusted by the author, then with this role
they can still choose to add it without having to make changes to the
control file if they think it's "secure enough".

  17. Re: [PATCH] New predefined role pg_manage_extensions

    Robert Haas <robertmhaas@gmail.com> — 2025-03-07T14:17:46Z

    On Fri, Mar 7, 2025 at 9:02 AM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
> The reason why I walked back my comment was that cloud providers can
> simply choose which extensions they actually add to the image. If an
> extension is marked as not trusted by the author, then with this role
> they can still choose to add it without having to make changes to the
> control file if they think it's "secure enough".

Hmm. It would be easy to do dumb things here, but I agree there are
probably a bunch of debatable cases. Maybe it would be smart if we
labelled our untrusted extensions somehow with why they're untrusted,
or documented that.

Why wouldn't the cloud provider just change add 'trusted = true' to
the relevant control files instead of doing this?

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  18. Re: [PATCH] New predefined role pg_manage_extensions

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-03-07T14:33:43Z

    On Fri, 2025-03-07 at 09:17 -0500, Robert Haas wrote:
> On Fri, Mar 7, 2025 at 9:02 AM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
> > The reason why I walked back my comment was that cloud providers can
> > simply choose which extensions they actually add to the image. If an
> > extension is marked as not trusted by the author, then with this role
> > they can still choose to add it without having to make changes to the
> > control file if they think it's "secure enough".
> 
> Hmm. It would be easy to do dumb things here, but I agree there are
> probably a bunch of debatable cases. Maybe it would be smart if we
> labelled our untrusted extensions somehow with why they're untrusted,
> or documented that.
> 
> Why wouldn't the cloud provider just change add 'trusted = true' to
> the relevant control files instead of doing this?

That's quite true.  Perhaps the patch should be rejected after all.

Yours,
Laurenz Albe

  19. Re: [PATCH] New predefined role pg_manage_extensions

    Michael Banck <mbanck@gmx.net> — 2025-03-07T14:37:27Z

    Hi,

On Fri, Mar 07, 2025 at 09:17:46AM -0500, Robert Haas wrote:
> Why wouldn't the cloud provider just change add 'trusted = true' to
> the relevant control files instead of doing this?

That would be possible, but  maybe the cloud provider is using
distribution packages and does not want to muck around in the file
system (as is usually frowned upon), or, maybe more likely, is using
container images based on (what I've seen most of them are) the Debian
packages and cannot (or does not want to anyway) muck around in the file
system easily.

Also, I think there is case to be made that a cloud provider (or site
admin) would like to delegate the decision whether users with CREATE
rights on a particular database are allowed to install some extensions
or not. Or rather, assign somebody they believe would make the right
call to do that, by granting pg_manage_extensions.

On the other hand, maybe trusted should be part of the catalog and not
(just) the extension control file, so that somebody with appropriate
permissions (like the cloud provider during instance bootstrap) could do
"ALTER EXTENSION foo (SET trusted|TRUSTED);" or whatever. ISTR that I
reviewed the discussion around trusted back then and did not see that
possiblity discussed at all, but I might be misremembering, it's been a
while.


Michael

  20. Re: [PATCH] New predefined role pg_manage_extensions

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-03-07T14:54:28Z

    On Fri, 7 Mar 2025 at 15:37, Michael Banck <mbanck@gmx.net> wrote:
> On Fri, Mar 07, 2025 at 09:17:46AM -0500, Robert Haas wrote:
> > Why wouldn't the cloud provider just change add 'trusted = true' to
> > the relevant control files instead of doing this?
>
> That would be possible, but  maybe the cloud provider is using
> distribution packages and does not want to muck around in the file
> system (as is usually frowned upon), or, maybe more likely, is using
> container images based on (what I've seen most of them are) the Debian
> packages and cannot (or does not want to anyway) muck around in the file
> system easily.

Yeah exactly, having to do this for every extension that you onboard
is quite a hassle to maintain. It seems much nicer to allow people to
assign a single role and be done with it.

Also many cloud providers have some slightly forked/extended postgres
to allow this already.

> Also, I think there is case to be made that a cloud provider (or site
> admin) would like to delegate the decision whether users with CREATE
> rights on a particular database are allowed to install some extensions
> or not. Or rather, assign somebody they believe would make the right
> call to do that, by granting pg_manage_extensions.

I think this is a really good point. Adding trusted=true gives any
database owner the ability to install these more dangerous extensions.
While by using pg_manage_extensions you can limit this ability to the
cluster administrator.

> On the other hand, maybe trusted should be part of the catalog and not
> (just) the extension control file, so that somebody with appropriate
> permissions (like the cloud provider during instance bootstrap) could do
> "ALTER EXTENSION foo (SET trusted|TRUSTED);" or whatever. ISTR that I
> reviewed the discussion around trusted back then and did not see that
> possiblity discussed at all, but I might be misremembering, it's been a
> while.

I think that would be hard because there's no record in the
pg_extension for extensions that are not installed. So there's also no
way to mark such an extension as trusted. To be able to do this we'd
probably need a system-wide catalog. If we'd go this route then I
think what we'd really want is a way to do:

GRANT INSTALL ON EXTENSION TO user;

And that seems orthogonal to having this pg_manage_extensions role,
because then pg_manage_extensions could grant that permission to
people.

  21. Re: [PATCH] New predefined role pg_manage_extensions

    Robert Haas <robertmhaas@gmail.com> — 2025-03-07T15:56:59Z

    On Fri, Mar 7, 2025 at 9:37 AM Michael Banck <mbanck@gmx.net> wrote:
> Also, I think there is case to be made that a cloud provider (or site
> admin) would like to delegate the decision whether users with CREATE
> rights on a particular database are allowed to install some extensions
> or not. Or rather, assign somebody they believe would make the right
> call to do that, by granting pg_manage_extensions.

Hypothetically, somebody could want a feature at various levels of
granularity. The most fine-grained would be something like: [1] allow
user X to install extension Y. Then, more broadly, you could have: [2]
allow any user who can install extensions to install extension Y. Or
conversely: [3] allow user X to install any extension. This patch
implements [3], but you could make an argument for any of the others.
My previous proposal amounted to allowing [2] via filesystem hacks,
but you could also have a GUC to allow [2], approximately:
artifically_trusted_extensions = foo, bar. That would actually allow
for [1] as well, because you could apply that GUC via ALTER ROLE ..
SET. I'm not saying that's necessarily better than what you're
proposing, but I think it's worth taking the time to think through the
options carefully.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  22. Re: [PATCH] New predefined role pg_manage_extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-07T16:21:24Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Mar 7, 2025 at 9:37 AM Michael Banck <mbanck@gmx.net> wrote:
>> Also, I think there is case to be made that a cloud provider (or site
>> admin) would like to delegate the decision whether users with CREATE
>> rights on a particular database are allowed to install some extensions
>> or not. Or rather, assign somebody they believe would make the right
>> call to do that, by granting pg_manage_extensions.

> Hypothetically, somebody could want a feature at various levels of
> granularity. The most fine-grained would be something like: [1] allow
> user X to install extension Y. Then, more broadly, you could have: [2]
> allow any user who can install extensions to install extension Y. Or
> conversely: [3] allow user X to install any extension. This patch
> implements [3], but you could make an argument for any of the others.

It's not apparent to me how [3] is meaningfully different from
giving user X superuser.  If you have the ability to install and
use, say, file_fdw, then nothing except honesty stands between you
and a superuser bit.  Is the argument for this feature that cloud
providers won't realize that?  Or perhaps the argument is that the
provider will only provide pre-vetted extensions to install ---
but then the existing "trusted extension" feature does everything
they need.

While I'm all for chipping away at what superuser privilege is
needed for, we have to tread VERY carefully about chipping away
at things that allow any outside-the-database access.

			regards, tom lane

  23. Re: [PATCH] New predefined role pg_manage_extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-03-07T16:23:51Z

    Jelte Fennema-Nio <postgres@jeltef.nl> writes:
> The reason why I walked back my comment was that cloud providers can
> simply choose which extensions they actually add to the image. If an
> extension is marked as not trusted by the author, then with this role
> they can still choose to add it without having to make changes to the
> control file if they think it's "secure enough".

If they think it's "secure enough", they can mark it trusted in their
images.  Why do we need anything beyond that?

			regards, tom lane

  24. Re: [PATCH] New predefined role pg_manage_extensions

    Robert Haas <robertmhaas@gmail.com> — 2025-03-07T16:46:53Z

    On Fri, Mar 7, 2025 at 11:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> While I'm all for chipping away at what superuser privilege is
> needed for, we have to tread VERY carefully about chipping away
> at things that allow any outside-the-database access.

I agree, but I also don't want the security decisions that the core
project takes to become irrelevant in practice. It seems like what's
starting to happen is that all of the cloud providers end up finding
the same issues and working around them in very similar ways and they
don't do anything in PostgreSQL itself, I guess because that would
require winning an argument on the mailing list. I think that dynamic
is bad for us as an open-source project, so I'm trying to be
particularly careful about evaluating proposals that smell like "all
the cloud providers are already doing this, why don't we maybe just
agree that it's needed".

I'm not certain that this is such a case, and I'd like to have more
information about what the cloud providers are actually doing in this
area, but I'm alert to the possibility that it might be the case.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

  25. Re: [PATCH] New predefined role pg_manage_extensions

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-04-05T23:07:19Z

    I took another look at this patch, and I still can't persuade
myself that a good case has been made for it.  There are too
many scenarios where pg_manage_extensions would be a security
problem and too few where it seems to really improve manageability.

As an example, it was asserted upthread that it's not a security
problem to let someone install plpython3u, because they still
couldn't create any plpython3u functions unless they were
superuser.  Well, fine, but then what's the point of installing
it?  If there is someone around with enough privilege to use
plpython3u, that person can install it first.

I also don't buy the argument that service providers would be
unwilling to set the "trusted" flag on extensions that for whatever
reason they are willing to let be installed by non-superusers.
This thread is full of (unsupported) assertions that those same
providers are making far more invasive changes to the PG code base
to achieve their desired results.

Robert Haas <robertmhaas@gmail.com> writes:
> I agree, but I also don't want the security decisions that the core
> project takes to become irrelevant in practice. It seems like what's
> starting to happen is that all of the cloud providers end up finding
> the same issues and working around them in very similar ways and they
> don't do anything in PostgreSQL itself, I guess because that would
> require winning an argument on the mailing list.

It would at least require showing up on the mailing list.
None of the assertions made in this thread about what cloud
providers are doing have been supported by a whit of evidence
about that.

What I'm wishing for is that some of the providers would show up here
and provide specific details (preferably patches) about what they are
changing and why.  Then we could have an informed discussion about
how to make their lives less painful in the future.  Right now
I think we're guessing --- I certainly am.  Maybe some of the people
on this thread have access to such details, but they aren't sharing.

			regards, tom lane

  26. Re: [PATCH] New predefined role pg_manage_extensions

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-04-09T08:42:36Z

    On Sat, 2025-04-05 at 19:07 -0400, Tom Lane wrote:
> I took another look at this patch, and I still can't persuade
> myself that a good case has been made for it.  There are too
> many scenarios where pg_manage_extensions would be a security
> problem and too few where it seems to really improve manageability.
> 
> As an example, it was asserted upthread that it's not a security
> problem to let someone install plpython3u, because they still
> couldn't create any plpython3u functions unless they were
> superuser.  Well, fine, but then what's the point of installing
> it?  If there is someone around with enough privilege to use
> plpython3u, that person can install it first.
> 
> I also don't buy the argument that service providers would be
> unwilling to set the "trusted" flag on extensions that for whatever
> reason they are willing to let be installed by non-superusers.
> This thread is full of (unsupported) assertions that those same
> providers are making far more invasive changes to the PG code base
> to achieve their desired results.
> 
> Robert Haas <robertmhaas@gmail.com> writes:
> > I agree, but I also don't want the security decisions that the core
> > project takes to become irrelevant in practice. It seems like what's
> > starting to happen is that all of the cloud providers end up finding
> > the same issues and working around them in very similar ways and they
> > don't do anything in PostgreSQL itself, I guess because that would
> > require winning an argument on the mailing list.
> 
> It would at least require showing up on the mailing list.
> None of the assertions made in this thread about what cloud
> providers are doing have been supported by a whit of evidence
> about that.
> 
> What I'm wishing for is that some of the providers would show up here
> and provide specific details (preferably patches) about what they are
> changing and why.  Then we could have an informed discussion about
> how to make their lives less painful in the future.  Right now
> I think we're guessing --- I certainly am.  Maybe some of the people
> on this thread have access to such details, but they aren't sharing.

So - should this patch be rejected or moved to the next CF in the hope
to attract some review from said hosting providers?

Michael, do you know any hosting providers you can prompt to comment?

Yours,
Laurenz Albe

  27. Re: [PATCH] New predefined role pg_manage_extensions

    John H <johnhyvr@gmail.com> — 2025-05-09T22:45:13Z

    Hi,

Chiming in as one of said providers...

> On Sat, 2025-04-05 at 19:07 -0400, Tom Lane wrote:
> > I took another look at this patch, and I still can't persuade
> > myself that a good case has been made for it. There are too
> > many scenarios where pg_manage_extensions would be a security
> > problem and too few where it seems to really improve manageability.

I agree extensions expose a large surface area making it possible for
privilege escalation. However, I think a new role that allows
CREATE EXTENSION is fairly similar to Trusted Extensions and the scrutiny that
requires. Providers that care about superuser distinction should already be
vetting extensions marked as trusted don't allow elevation today. If
they want to allow end-users to leverage the pg_manage_extensions role,
they would need to extend that review to all extensions that they are offering.

There isn't anything preventing a community extension from incorrectly marking a
dangerous (however that is viewed) extension as trusted.

> > What I'm wishing for is that some of the providers would show up here
> > and provide specific details (preferably patches) about what they are
> > changing and why. Then we could have an informed discussion about
> > how to make their lives less painful in the future.

superusers can always provide a SECURITY DEFINER function that wraps around
CREATE EXTENSION with some input, but end-users expect to call "CREATE
EXTENSION"
instead of some random function. I view these new roles as a quality
of life change
for users.

One reason we introduced our own extension role, 'rds_extensions', was
we had users
wanting to provide 'CREATE EXTENSION' functionality to their own end-users.
They were also not necessarily comfortable with allowing their users
to install *all*
extensions like Robert suggested so we provided a way for them to
specify a subset of
them through a parameter.

Going back to the patch, I think it depends on what we're trying to
solve. Given that
we've been trying to split out superuser powers out, do we want to
make it easier to
create "all extensions" like superuser, or delegate a subset of
extensions out without
having to edit all the control files as trusted.

--
John Hsu - Amazon Web Services