Message-ID: <65a1524e.050a0220.49266.7670@mx.google.com>
Sender: Michael Banck <mbanck@gmail.com>
Date: Fri, 12 Jan 2024 15:53:01 +0100
From: Michael Banck <mbanck@gmx.net>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: [PATCH] New predefined role pg_manage_extensions
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="UlVJffcvxoiEqYs2"
Content-Disposition: inline
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: <https://www.postgresql.org/message-id/65a1524e.050a0220.49266.7670%40mx.google.com>
Precedence: bulk


--UlVJffcvxoiEqYs2
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--UlVJffcvxoiEqYs2
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="0001-Add-new-pg_manage_extensions-predefined-role.patch"

From 59497e825184f0de30a18573ffd7d331be3b233d Mon Sep 17 00:00:00 2001
From: Michael Banck <michael.banck@credativ.de>
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


--UlVJffcvxoiEqYs2--