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
>From bda5db90d6d56a025f275c85f775f48c8083b7f0 Mon Sep 17 00:00:00 2001
From: Michael Banck <michael.ba...@credativ.de>
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;
/*
- * 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,
@@ -1009,7 +1010,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),
@@ -1017,7 +1018,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 0129f67eaa..c6781b0acf 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -104,5 +104,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.5
