Hello!
In an environment where we control the host system and all installed
extensions, we need to allow postgresql non-superuser to install all of
them, without opening gaps that will let this user gain superuser
privileges. We have a sample solution to add a new default role
pg_create_extension which does not need superuser privilege to create any
extensions.
However we are not sure if it's the best approach. Are there any other
ideas, proposals or feedback?
Is this something you would consider adding to the next major release?
Best regards,
Alexandra Ryzhevich
From 262347d1052c64f36fd6662e98a56609350ce2ff Mon Sep 17 00:00:00 2001
From: Alexandra Ryzhevich <aryzhev...@google.com>
Date: Fri, 10 Aug 2018 11:44:49 +0100
Subject: [PATCH 1/1] Add default create extension role
---
src/backend/catalog/aclchk.c | 3 ++-
src/backend/commands/aggregatecmds.c | 6 +++++-
src/backend/commands/extension.c | 4 +++-
src/backend/commands/functioncmds.c | 5 ++++-
src/backend/commands/opclasscmds.c | 11 ++++++++---
src/backend/commands/typecmds.c | 4 +++-
src/include/catalog/pg_authid.dat | 5 +++++
7 files changed, 30 insertions(+), 8 deletions(-)
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 578e4c6592..46e0d7e531 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -4438,7 +4438,8 @@ pg_type_aclmask(Oid type_oid, Oid roleid, AclMode mask, AclMaskHow how)
Form_pg_type typeForm;
/* Bypass permission checks for superusers */
- if (superuser_arg(roleid))
+ if (superuser_arg(roleid) ||
+ (creating_extension && is_member_of_role(GetUserId(), DEFAULT_ROLE_CREATE_EXTENSION)))
return mask;
/*
diff --git a/src/backend/commands/aggregatecmds.c b/src/backend/commands/aggregatecmds.c
index 877f658ce7..53b524fbcf 100644
--- a/src/backend/commands/aggregatecmds.c
+++ b/src/backend/commands/aggregatecmds.c
@@ -26,10 +26,12 @@
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/alter.h"
#include "commands/defrem.h"
+#include "commands/extension.h"
#include "miscadmin.h"
#include "parser/parse_func.h"
#include "parser/parse_type.h"
@@ -336,7 +338,9 @@ DefineAggregate(ParseState *pstate, List *name, List *args, bool oldstyle, List
if (transTypeType == TYPTYPE_PSEUDO &&
!IsPolymorphicType(transTypeId))
{
- if (transTypeId == INTERNALOID && superuser())
+ if (transTypeId == INTERNALOID &&
+ (superuser() ||
+ (creating_extension && is_member_of_role(GetUserId(), DEFAULT_ROLE_CREATE_EXTENSION))))
/* okay */ ;
else
ereport(ERROR,
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 2e4538146d..506ee77982 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -36,6 +36,7 @@
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/objectaccess.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_extension.h"
@@ -799,7 +800,8 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
* here so that the flag is correctly associated with the right script(s)
* if it's set in secondary control files.
*/
- if (control->superuser && !superuser())
+ if (control->superuser && !superuser() &&
+ !is_member_of_role(GetUserId(), DEFAULT_ROLE_CREATE_EXTENSION))
{
if (from_version == NULL)
ereport(ERROR,
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 68109bfda0..dfaa0574c7 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -40,6 +40,7 @@
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_cast.h"
#include "catalog/pg_language.h"
#include "catalog/pg_namespace.h"
@@ -48,6 +49,7 @@
#include "catalog/pg_type.h"
#include "commands/alter.h"
#include "commands/defrem.h"
+#include "commands/extension.h"
#include "commands/proclang.h"
#include "executor/execdesc.h"
#include "executor/executor.h"
@@ -953,7 +955,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
else
{
/* if untrusted language, must be superuser */
- if (!superuser())
+ if (!superuser() &&
+ !(creating_extension && is_member_of_role(GetUserId(), DEFAULT_ROLE_CREATE_EXTENSION)))
aclcheck_error(ACLCHECK_NO_PRIV, OBJECT_LANGUAGE,
NameStr(languageStruct->lanname));
}
diff --git a/src/backend/commands/opclasscmds.c b/src/backend/commands/opclasscmds.c
index e4b1369f19..082aa87812 100644
--- a/src/backend/commands/opclasscmds.c
+++ b/src/backend/commands/opclasscmds.c
@@ -30,6 +30,7 @@
#include "catalog/pg_am.h"
#include "catalog/pg_amop.h"
#include "catalog/pg_amproc.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
@@ -39,6 +40,7 @@
#include "commands/alter.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
+#include "commands/extension.h"
#include "miscadmin.h"
#include "parser/parse_func.h"
#include "parser/parse_oper.h"
@@ -397,7 +399,8 @@ DefineOpClass(CreateOpClassStmt *stmt)
*
* XXX re-enable NOT_USED code sections below if you remove this test.
*/
- if (!superuser())
+ if (!superuser() &&
+ !(creating_extension && is_member_of_role(GetUserId(), DEFAULT_ROLE_CREATE_EXTENSION)))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to create an operator class")));
@@ -742,7 +745,8 @@ DefineOpFamily(CreateOpFamilyStmt *stmt)
* Currently, we require superuser privileges to create an opfamily. See
* comments in DefineOpClass.
*/
- if (!superuser())
+ if (!superuser() &&
+ !(creating_extension && is_member_of_role(GetUserId(), DEFAULT_ROLE_CREATE_EXTENSION)))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to create an operator family")));
@@ -798,7 +802,8 @@ AlterOpFamily(AlterOpFamilyStmt *stmt)
*
* XXX re-enable NOT_USED code sections below if you remove this test.
*/
- if (!superuser())
+ if (!superuser() &&
+ !(creating_extension && is_member_of_role(GetUserId(), DEFAULT_ROLE_CREATE_EXTENSION)))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to alter an operator family")));
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 175ecc8b48..f16eda8c03 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -49,6 +49,7 @@
#include "catalog/pg_range.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
+#include "commands/extension.h"
#include "commands/tablecmds.h"
#include "commands/typecmds.h"
#include "executor/executor.h"
@@ -174,7 +175,8 @@ DefineType(ParseState *pstate, List *names, List *parameters)
*
* XXX re-enable NOT_USED code sections below if you remove this test.
*/
- if (!superuser())
+ if (!superuser() &&
+ !(creating_extension && is_member_of_role(GetUserId(), DEFAULT_ROLE_CREATE_EXTENSION)))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must be superuser to create a base type")));
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 55be317369..623a0ef7e6 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -55,6 +55,11 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '4572', oid_symbol => 'DEFAULT_ROLE_CREATE_EXTENSION',
+ rolname => 'pg_create_extension', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
{ oid => '4200', oid_symbol => 'DEFAULT_ROLE_SIGNAL_BACKENDID',
rolname => 'pg_signal_backend', rolsuper => 'f', rolinherit => 't',
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
--
2.18.0.597.ga71716f1ad-goog
