Subject: [PATCH] New predefined role pg_manage_extensions

From: Michael Banck <[email-masked]>
Date: Fri, 12 Jan 2024 15:53:01 +0100

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
>From 59497e825184f0de30a18573ffd7d331be3b233d Mon Sep 17 00:00:00 2001
From: Michael Banck <[email-masked]>
Date: Fri, 12 Jan 2024 13:56:59 +0100
Subject: [PATCH] Add new pg_manage_extensions predefined role.

This allows any role that is granted this new predefined role to CREATE, UPDATE
or DROP extensions, no matter whether they are trusted or not.
---
 doc/src/sgml/user-manag.sgml      |  5 +++++
 src/backend/commands/extension.c  | 11 ++++++-----
 src/include/catalog/pg_authid.dat |  5 +++++
 3 files changed, 16 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 92a299d2d3..ebb82801ec 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -693,6 +693,11 @@ DROP ROLE doomed_role;
        database to issue
        <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
       </row>
+      <row>
+       <entry>pg_manage_extensions</entry>
+       <entry>Allow creating, removing or updating extensions, even if the
+       extensions are untrusted or the user is not the database owner.</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 226f85d0e3..71481d9a73 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -882,13 +882,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.
 	 */
 	if (control->superuser && !superuser())
 	{
-		if (extension_is_trusted(control))
+		if (extension_is_trusted(control) || has_privs_of_role(GetUserId(), ROLE_PG_MANAGE_EXTENSIONS))
 			switch_to_superuser = true;
 		else if (from_version == NULL)
 			ereport(ERROR,
@@ -897,7 +898,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 							control->name),
 					 control->trusted
 					 ? errhint("Must have CREATE privilege on current database to create this extension.")
-					 : 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")));
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
@@ -905,7 +906,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 							control->name),
 					 control->trusted
 					 ? errhint("Must have CREATE privilege on current database to update this extension.")
-					 : errhint("Must be superuser to update this extension.")));
+					 : errhint("Only roles with privileges of the \"%s\" role are allowed to update this extension.", "pg_manage_extensions")));
 	}
 
 	filename = get_extension_script_filename(control, from_version, version);
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 82a2ec2862..ac70603d26 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -94,5 +94,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '8801', oid_symbol => 'ROLE_PG_MANAGE_EXTENSIONS',
+  rolname => 'pg_manage_extensions', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
-- 
2.39.2

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Fri, 12 Jan 2024 16:13:27 +0100

> if those extensions are not trusted or those users are not the database

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.

---

From: Michael Banck <[email-masked]>
Date: Sat, 13 Jan 2024 09:20:40 +0100

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

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

---

From: Michael Banck <[email-masked]>
Date: Thu, 31 Oct 2024 22:47:16 +0100

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.

Michael
>From bda5db90d6d56a025f275c85f775f48c8083b7f0 Mon Sep 17 00:00:00 2001
From: Michael Banck <[email-masked]>
Date: Thu, 31 Oct 2024 22:36:12 +0100
Subject: [PATCH v2] Add new pg_manage_extensions predefined role.

This allows any role that is granted this new predefined role to CREATE, UPDATE
or DROP extensions, no matter whether they are trusted or not.
---
 doc/src/sgml/user-manag.sgml      | 11 +++++++++++
 src/backend/commands/extension.c  | 11 ++++++-----
 src/include/catalog/pg_authid.dat |  5 +++++
 3 files changed, 22 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index ed18704a9c..b25f985697 100644
--- 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>
+
     <varlistentry id="predefined-role-pg-monitor" xreflabel="pg_monitor">
      <term><varname>pg_monitor</varname></term>
      <term><varname>pg_read_all_settings</varname></term>
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index af6bd8ff42..3974da0ef9 100644
--- 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;
 
 	/*
@@ -1009,7 +1010,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
@@ -1017,7 +1018,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 
index 0129f67eaa..c6781b0acf 100644
@@ -104,5 +104,10 @@
 
 ]
-- 
2.39.5

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Thu, 14 Nov 2024 13:30:21 +0100

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

---

From: Kirill Reshke <[email-masked]>
Date: Mon, 18 Nov 2024 11:26:40 +0500

> rebased patch.  I have also added it to the upcoming commitfest.

Hi!

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

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

```

> > particular extension probably?

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

---

From: Michael Banck <[email-masked]>
Date: Tue, 31 Dec 2024 12:15:53 +0100

Hi,

first, sorry for the late reply :-/

> They are required to have CREATE priv on database.

Ah right, thanks, I have changed that.

> superuser privilege if markled trusted.

[...]

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.

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

> loaded on the host, I never want a normal user to run code with

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

---

From: Shinya Kato <[email-masked]>
Date: Thu, 16 Jan 2025 16:09:44 +0900

On Thu, Jan 16, 2025 at 3:31 PM Michael Banck <[email-masked]> 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.

> Well, I would say pg_execute_server_program could be regarded as a

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

> 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

---

From: Michael Banck <[email-masked]>
Date: Thu, 16 Jan 2025 08:35:32 +0100

Hi,

> extensions other than the required one? Or is it unnecessary to impose

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

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 17 Jan 2025 10:03:17 +0100

> rebased patch.  I have also added it to the upcoming commitfest.

I had a look at the patch.

> +       not the database owner.

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?

>      * 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("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

---

From: Michael Banck <[email-masked]>
Date: Fri, 28 Feb 2025 19:16:39 +0100

Hi,

> I had a look at the patch.

Thanks! And sorry for the long delay...
 
> 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.
 
> the respective commands obvious?

Alright, did so.

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

Done.

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

>   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
>From 57e02d95ace2a390ea1e40266735529f313b31ec Mon Sep 17 00:00:00 2001
From: Michael Banck <[email-masked]>
Date: Thu, 31 Oct 2024 22:36:12 +0100
Subject: [PATCH v3] Add new pg_manage_extensions predefined role.

index ed18704a9c2..2a44f41da4f 100644
 
+    <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, altering or
+       dropping extensions, even if the extensions are untrusted or the user
+       does not have <literal>CREATE</literal> rights on the database.
+      </para>
+     </listitem>
+    </varlistentry>
+
     <varlistentry id="predefined-role-pg-monitor" xreflabel="pg_monitor">
      <term><varname>pg_monitor</varname></term>
      <term><varname>pg_read_all_settings</varname></term>
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index d9bb4ce5f1e..2426c3e0eda 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -996,13 +996,14 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
 	ListCell   *lc2;
 
 	/*
@@ -1011,7 +1012,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
+					 : errhint("Must be superuser or member of pg_manage_extensions to create this extension.")));
@@ -1019,7 +1020,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
+					 : errhint("Must be superuser or member of pg_manage_extensions to update this extension.")));
 	}
 
index eb4dab5c6aa..0e3eb20e2b9 100644
 
 ]
-- 
2.39.5

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 07 Mar 2025 14:14:04 +0100

> 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

---

From: Dagfinn Ilmari Mannsåker <[email-masked]>
Date: Fri, 07 Mar 2025 13:40:00 +0000

> 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

---

From: Robert Haas <[email-masked]>
Date: Fri, 7 Mar 2025 08:57:50 -0500

> because they would allow fairly trivial privilege escalation to

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

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Fri, 7 Mar 2025 15:02:15 +0100

> NOT allow administrators to escalate to superuser.

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

---

From: Robert Haas <[email-masked]>
Date: Fri, 7 Mar 2025 09:17:46 -0500

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

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 07 Mar 2025 15:33:43 +0100

> the relevant control files instead of doing this?

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

Yours,
Laurenz Albe

---

From: Michael Banck <[email-masked]>
Date: Fri, 7 Mar 2025 15:37:27 +0100

Hi,

> 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

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Fri, 7 Mar 2025 15:54:28 +0100

> packages and cannot (or does not want to anyway) muck around in the file

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.

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

> possiblity discussed at all, but I might be misremembering, it's been a

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.

---

From: Robert Haas <[email-masked]>
Date: Fri, 7 Mar 2025 10:56:59 -0500

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

---

From: Tom Lane <[email-masked]>
Date: Fri, 07 Mar 2025 11:21:24 -0500

> 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

---

From: Tom Lane <[email-masked]>
Date: Fri, 07 Mar 2025 11:23:51 -0500

> 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

---

From: Robert Haas <[email-masked]>
Date: Fri, 7 Mar 2025 11:46:53 -0500

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

---

From: Tom Lane <[email-masked]>
Date: Sat, 05 Apr 2025 19:07:19 -0400

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 <[email-masked]> writes:
> 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

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 09 Apr 2025 10:42:36 +0200

> 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

---

From: John H <[email-masked]>
Date: Fri, 9 May 2025 15:45:13 -0700

Hi,

Chiming in as one of said providers...

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

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