Robert, José, I've rebased this on top of master and added a few additional checks and regression tests.
I'm planning to continue going over the patch tomorrow morning with plans to push this before the feature freeze deadline. Thanks! Stephen
From a0724d91ffd1a93034d5b5d5df2b4ff54339d763 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Mon, 29 Feb 2016 21:27:46 -0500
Subject: [PATCH 1/2] Reserve the "pg_" namespace for roles
This will prevent users from creating roles which begin with "pg_" and
will check for those roles before allowing an upgrade using pg_upgrade.
This will allow for default roles to be provided at initdb time.
---
doc/src/sgml/ref/psql-ref.sgml | 8 +++++--
src/backend/catalog/catalog.c | 5 +++--
src/backend/commands/foreigncmds.c | 4 ++++
src/backend/commands/policy.c | 5 +++++
src/backend/commands/schemacmds.c | 4 ++++
src/backend/commands/user.c | 40 +++++++++++++++++++++++++++++++++
src/backend/commands/variable.c | 3 +++
src/backend/utils/adt/acl.c | 39 ++++++++++++++++++++++++++++++++
src/bin/pg_dump/pg_dumpall.c | 11 ++++++++-
src/bin/pg_upgrade/check.c | 40 +++++++++++++++++++++++++++++++--
src/bin/psql/command.c | 4 ++--
src/bin/psql/describe.c | 5 ++++-
src/bin/psql/describe.h | 2 +-
src/bin/psql/help.c | 4 ++--
src/include/utils/acl.h | 1 +
src/test/regress/expected/rolenames.out | 20 +++++++++++++++++
src/test/regress/sql/rolenames.sql | 10 +++++++++
17 files changed, 192 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index d8b9a03..60f8822 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1365,13 +1365,15 @@ testdb=>
<varlistentry>
- <term><literal>\dg[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dg[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists database roles.
(Since the concepts of <quote>users</> and <quote>groups</> have been
unified into <quote>roles</>, this command is now equivalent to
<literal>\du</literal>.)
+ By default, only user-created roles are shown; supply the
+ <literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
If the form <literal>\dg+</literal> is used, additional information
@@ -1525,13 +1527,15 @@ testdb=>
</varlistentry>
<varlistentry>
- <term><literal>\du[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\du[S+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists database roles.
(Since the concepts of <quote>users</> and <quote>groups</> have been
unified into <quote>roles</>, this command is now equivalent to
<literal>\dg</literal>.)
+ By default, only user-created roles are shown; supply the
+ <literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
If the form <literal>\du+</literal> is used, additional information
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index bead2c1..d1cf45b 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -184,8 +184,9 @@ IsToastNamespace(Oid namespaceId)
* True iff name starts with the pg_ prefix.
*
* For some classes of objects, the prefix pg_ is reserved for
- * system objects only. As of 8.0, this is only true for
- * schema and tablespace names.
+ * system objects only. As of 8.0, this was only true for
+ * schema and tablespace names. With 9.6, this is also true
+ * for roles.
*/
bool
IsReservedName(const char *name)
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index 804bab2..c402e64 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -1148,6 +1148,10 @@ CreateUserMapping(CreateUserMappingStmt *stmt)
else
useId = get_rolespec_oid(stmt->user, false);
+ /* Additional check to protect reserved role names */
+ check_rolespec_name(stmt->user,
+ "Cannot specify reserved role as mapping user.");
+
/* Check that the server exists. */
srv = GetForeignServerByName(stmt->servername, false);
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index 93d15e4..146b36c 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -176,8 +176,13 @@ policy_role_list_to_array(List *roles, int *num_roles)
return role_oids;
}
else
+ {
+ /* Additional check to protect reserved role names */
+ check_rolespec_name((Node *) spec,
+ "Cannot specify reserved role as policy target");
role_oids[i++] =
ObjectIdGetDatum(get_rolespec_oid((Node *) spec, false));
+ }
}
return role_oids;
diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c
index a60ceb8..6ecfb70 100644
--- a/src/backend/commands/schemacmds.c
+++ b/src/backend/commands/schemacmds.c
@@ -78,6 +78,10 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString)
ReleaseSysCache(tuple);
}
+ /* Additional check to protect reserved role names */
+ check_rolespec_name(stmt->authrole,
+ "Cannot specify reserved role as owner.");
+
/*
* To create a schema, must have schema-create privilege on the current
* database and must be able to become the target role (this does not
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 4baeaa2..ee37397 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -17,6 +17,7 @@
#include "access/htup_details.h"
#include "access/xact.h"
#include "catalog/binary_upgrade.h"
+#include "catalog/catalog.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
@@ -312,6 +313,17 @@ CreateRole(CreateRoleStmt *stmt)
}
/*
+ * Check that the user is not trying to create a role in the reserved
+ * "pg_" namespace.
+ */
+ if (IsReservedName(stmt->role))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved",
+ stmt->role),
+ errdetail("Role names starting with \"pg_\" are reserved.")));
+
+ /*
* Check the pg_authid relation to be certain the role doesn't already
* exist.
*/
@@ -1117,6 +1129,7 @@ RenameRole(const char *oldname, const char *newname)
int i;
Oid roleid;
ObjectAddress address;
+ Form_pg_authid authform;
rel = heap_open(AuthIdRelationId, RowExclusiveLock);
dsc = RelationGetDescr(rel);
@@ -1136,6 +1149,7 @@ RenameRole(const char *oldname, const char *newname)
*/
roleid = HeapTupleGetOid(oldtuple);
+ authform = (Form_pg_authid) GETSTRUCT(oldtuple);
if (roleid == GetSessionUserId())
ereport(ERROR,
@@ -1146,6 +1160,24 @@ RenameRole(const char *oldname, const char *newname)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("current user cannot be renamed")));
+ /*
+ * Check that the user is not trying to rename a system role and
+ * not trying to rename a role into the reserved "pg_" namespace.
+ */
+ if (IsReservedName(NameStr(authform->rolname)))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved",
+ NameStr(authform->rolname)),
+ errdetail("Role names starting with \"pg_\" are reserved.")));
+
+ if (IsReservedName(newname))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved",
+ newname),
+ errdetail("Role names starting with \"pg_\" are reserved.")));
+
/* make sure the new name doesn't exist */
if (SearchSysCacheExists1(AUTHNAME, CStringGetDatum(newname)))
ereport(ERROR,
@@ -1224,10 +1256,18 @@ GrantRole(GrantRoleStmt *stmt)
ListCell *item;
if (stmt->grantor)
+ {
+ check_rolespec_name(stmt->grantor,
+ "Cannot specify reserved role as grantor.");
grantor = get_rolespec_oid(stmt->grantor, false);
+ }
else
grantor = GetUserId();
+ foreach(item, stmt->grantee_roles)
+ check_rolespec_name(lfirst(item),
+ "Cannot GRANT roles to a reserved role.");
+
grantee_ids = roleSpecsToIds(stmt->grantee_roles);
/* AccessShareLock is enough since we aren't modifying pg_authid */
diff --git a/src/backend/commands/variable.c b/src/backend/commands/variable.c
index f801faa..57da014 100644
--- a/src/backend/commands/variable.c
+++ b/src/backend/commands/variable.c
@@ -854,6 +854,9 @@ check_role(char **newval, void **extra, GucSource source)
roleid = InvalidOid;
is_superuser = false;
}
+ /* Do not allow setting role to a reserved role. */
+ else if (strncmp(*newval, "pg_", 3) == 0)
+ return false;
else
{
if (!IsTransactionState())
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 63fbce0..d2b23d0 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -17,6 +17,7 @@
#include <ctype.h>
#include "access/htup_details.h"
+#include "catalog/catalog.h"
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_auth_members.h"
@@ -5247,3 +5248,41 @@ get_rolespec_name(const Node *node)
return rolename;
}
+
+/*
+ * Given a RoleSpec, throw an error if the name is reserved, using detail_msg,
+ * if provided.
+ *
+ * If node is NULL, no error is thrown. If detail_msg is NULL then no detail
+ * message is provided.
+ */
+void
+check_rolespec_name(const Node *node, const char *detail_msg)
+{
+ RoleSpec *role;
+
+ if (!node)
+ return;
+
+ role = (RoleSpec *) node;
+
+ Assert(IsA(node, RoleSpec));
+
+ if (role->roletype != ROLESPEC_CSTRING)
+ return;
+
+ if (IsReservedName(role->rolename))
+ {
+ if (detail_msg)
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role \"%s\" is reserved",
+ role->rolename),
+ errdetail("%s", detail_msg)));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role \"%s\" is reserved",
+ role->rolename)));
+ }
+}
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index a594937..a7dc41c 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -665,7 +665,7 @@ dumpRoles(PGconn *conn)
int i;
/* note: rolconfig is dumped later */
- if (server_version >= 90500)
+ if (server_version >= 90600)
printfPQExpBuffer(buf,
"SELECT oid, rolname, rolsuper, rolinherit, "
"rolcreaterole, rolcreatedb, "
@@ -674,6 +674,7 @@ dumpRoles(PGconn *conn)
"pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
"rolname = current_user AS is_current_user "
"FROM pg_authid "
+ "WHERE rolname !~ '^pg_' "
"ORDER BY 2");
else if (server_version >= 90100)
printfPQExpBuffer(buf,
@@ -771,6 +772,13 @@ dumpRoles(PGconn *conn)
auth_oid = atooid(PQgetvalue(res, i, i_oid));
rolename = PQgetvalue(res, i, i_rolname);
+ if (strncmp(rolename,"pg_",3) == 0)
+ {
+ fprintf(stderr, _("%s: role name starting with 'pg_' skipped (%s)\n"),
+ progname, rolename);
+ continue;
+ }
+
resetPQExpBuffer(buf);
if (binary_upgrade)
@@ -896,6 +904,7 @@ dumpRoleMembership(PGconn *conn)
"LEFT JOIN pg_authid ur on ur.oid = a.roleid "
"LEFT JOIN pg_authid um on um.oid = a.member "
"LEFT JOIN pg_authid ug on ug.oid = a.grantor "
+ "WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')"
"ORDER BY 1,2,3");
if (PQntuples(res) > 0)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index f932094..6b6f5ba 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -24,6 +24,7 @@ static void check_for_prepared_transactions(ClusterInfo *cluster);
static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
static void check_for_reg_data_type_usage(ClusterInfo *cluster);
static void check_for_jsonb_9_4_usage(ClusterInfo *cluster);
+static void check_for_pg_role_prefix(ClusterInfo *cluster);
static void get_bin_version(ClusterInfo *cluster);
static char *get_canonical_locale_name(int category, const char *locale);
@@ -96,6 +97,11 @@ check_and_dump_old_cluster(bool live_check)
check_for_prepared_transactions(&old_cluster);
check_for_reg_data_type_usage(&old_cluster);
check_for_isn_and_int8_passing_mismatch(&old_cluster);
+
+ /* 9.5 and below should not have roles starting with pg_ */
+ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 905)
+ check_for_pg_role_prefix(&old_cluster);
+
if (GET_MAJOR_VERSION(old_cluster.major_version) == 904 &&
old_cluster.controldata.cat_ver < JSONB_FORMAT_CHANGE_CAT_VER)
check_for_jsonb_9_4_usage(&old_cluster);
@@ -629,7 +635,8 @@ check_is_install_user(ClusterInfo *cluster)
res = executeQueryOrDie(conn,
"SELECT rolsuper, oid "
"FROM pg_catalog.pg_roles "
- "WHERE rolname = current_user");
+ "WHERE rolname = current_user "
+ "AND rolname !~ '^pg_'");
/*
* We only allow the install user in the new cluster (see comment below)
@@ -645,7 +652,8 @@ check_is_install_user(ClusterInfo *cluster)
res = executeQueryOrDie(conn,
"SELECT COUNT(*) "
- "FROM pg_catalog.pg_roles ");
+ "FROM pg_catalog.pg_roles "
+ "WHERE rolname !~ '^pg_'");
if (PQntuples(res) != 1)
pg_fatal("could not determine the number of users\n");
@@ -1033,6 +1041,34 @@ check_for_jsonb_9_4_usage(ClusterInfo *cluster)
check_ok();
}
+/*
+ * check_for_pg_role_prefix()
+ *
+ * Versions older than 9.6 should not have any pg_* roles
+ */
+static void
+check_for_pg_role_prefix(ClusterInfo *cluster)
+{
+ PGresult *res;
+ PGconn *conn = connectToServer(cluster, "template1");
+
+ prep_status("Checking for roles starting with 'pg_'");
+
+ res = executeQueryOrDie(conn,
+ "SELECT * "
+ "FROM pg_catalog.pg_roles "
+ "WHERE rolname ~ '^pg_'");
+
+ if (PQntuples(res) != 0)
+ pg_fatal("The %s cluster contains roles starting with 'pg_'\n",
+ CLUSTER_NAME(cluster));
+
+ PQclear(res);
+
+ PQfinish(conn);
+
+ check_ok();
+}
static void
get_bin_version(ClusterInfo *cluster)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 1d326a8..9eae76f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -429,7 +429,7 @@ exec_command(const char *cmd,
break;
case 'g':
/* no longer distinct from \du */
- success = describeRoles(pattern, show_verbose);
+ success = describeRoles(pattern, show_verbose, show_system);
break;
case 'l':
success = do_lo_list();
@@ -474,7 +474,7 @@ exec_command(const char *cmd,
success = PSQL_CMD_UNKNOWN;
break;
case 'u':
- success = describeRoles(pattern, show_verbose);
+ success = describeRoles(pattern, show_verbose, show_system);
break;
case 'F': /* text search subsystem */
switch (cmd[2])
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 7b2f4e6..0a771bd 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2646,7 +2646,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
* Describes roles. Any schema portion of the pattern is ignored.
*/
bool
-describeRoles(const char *pattern, bool verbose)
+describeRoles(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
@@ -2691,6 +2691,9 @@ describeRoles(const char *pattern, bool verbose)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
+ if (!showSystem && !pattern)
+ appendPQExpBufferStr(&buf, "WHERE r.rolname !~ '^pg_'\n");
+
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "r.rolname", NULL, NULL);
}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index e4fc79e..9672275 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -25,7 +25,7 @@ extern bool describeTypes(const char *pattern, bool verbose, bool showSystem);
extern bool describeOperators(const char *pattern, bool verbose, bool showSystem);
/* \du, \dg */
-extern bool describeRoles(const char *pattern, bool verbose);
+extern bool describeRoles(const char *pattern, bool verbose, bool showSystem);
/* \drds */
extern bool listDbRoleSettings(const char *pattern1, const char *pattern2);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 7549451..eda31e2 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -229,7 +229,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dFd[+] [PATTERN] list text search dictionaries\n"));
fprintf(output, _(" \\dFp[+] [PATTERN] list text search parsers\n"));
fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n"));
- fprintf(output, _(" \\dg[+] [PATTERN] list roles\n"));
+ fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n"));
@@ -242,7 +242,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
- fprintf(output, _(" \\du[+] [PATTERN] list roles\n"));
+ fprintf(output, _(" \\du[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
fprintf(output, _(" \\dE[S+] [PATTERN] list foreign tables\n"));
fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n"));
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 4e15a14..d91437b 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -231,6 +231,7 @@ extern void check_is_member_of_role(Oid member, Oid role);
extern Oid get_role_oid(const char *rolename, bool missing_ok);
extern Oid get_role_oid_or_public(const char *rolename);
extern Oid get_rolespec_oid(const Node *node, bool missing_ok);
+extern void check_rolespec_name(const Node *node, const char *detail_msg);
extern HeapTuple get_rolespec_tuple(const Node *node);
extern char *get_rolespec_name(const Node *node);
diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out
index 8f88c02..01b3b90 100644
--- a/src/test/regress/expected/rolenames.out
+++ b/src/test/regress/expected/rolenames.out
@@ -78,6 +78,18 @@ CREATE ROLE "none"; -- error
ERROR: role name "none" is reserved
LINE 1: CREATE ROLE "none";
^
+CREATE ROLE pg_abc; -- error
+ERROR: role name "pg_abc" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE "pg_abc"; -- error
+ERROR: role name "pg_abc" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE pg_abcdef; -- error
+ERROR: role name "pg_abcdef" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE "pg_abcdef"; -- error
+ERROR: role name "pg_abcdef" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
CREATE ROLE testrol0 SUPERUSER LOGIN;
CREATE ROLE testrolx SUPERUSER LOGIN;
CREATE ROLE testrol2 SUPERUSER;
@@ -804,6 +816,14 @@ LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9;
DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error
NOTICE: role "nonexistent" does not exist, skipping
-- GRANT/REVOKE
+GRANT testrol0 TO pg_abc; -- error
+ERROR: role "pg_abc" is reserved
+DETAIL: Cannot GRANT roles to a reserved role.
+GRANT pg_abc TO pg_abcdef; -- error
+ERROR: role "pg_abcdef" is reserved
+DETAIL: Cannot GRANT roles to a reserved role.
+SET ROLE pg_testrole; -- error
+ERROR: invalid value for parameter "role": "pg_testrole"
UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
proname | proacl
diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql
index e8c6b33..1e0e9af 100644
--- a/src/test/regress/sql/rolenames.sql
+++ b/src/test/regress/sql/rolenames.sql
@@ -57,6 +57,11 @@ CREATE ROLE "public"; -- error
CREATE ROLE none; -- error
CREATE ROLE "none"; -- error
+CREATE ROLE pg_abc; -- error
+CREATE ROLE "pg_abc"; -- error
+CREATE ROLE pg_abcdef; -- error
+CREATE ROLE "pg_abcdef"; -- error
+
CREATE ROLE testrol0 SUPERUSER LOGIN;
CREATE ROLE testrolx SUPERUSER LOGIN;
CREATE ROLE testrol2 SUPERUSER;
@@ -376,6 +381,11 @@ DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error
DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error
-- GRANT/REVOKE
+GRANT testrol0 TO pg_abc; -- error
+GRANT pg_abc TO pg_abcdef; -- error
+
+SET ROLE pg_testrole; -- error
+
UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
--
2.5.0
From 3b0ca0ab8c2d3ddf39a2538932d9e9aa8a99f71c Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Wed, 30 Sep 2015 07:08:03 -0400
Subject: [PATCH 2/2] Create default roles
This creates an initial set of default roles which administrators may
use to grant access to, historically, superuser-only functions. Using
these roles instead of granting superuser access reduces the number of
superuser roles required for a system. Documention for each of the
default roles has been added to user-manag.sgml.
---
doc/src/sgml/func.sgml | 8 ++++--
doc/src/sgml/user-manag.sgml | 51 +++++++++++++++++++++++++++++++++
src/backend/utils/adt/misc.c | 8 ++++--
src/include/catalog/pg_authid.h | 8 +++++-
src/test/regress/expected/rolenames.out | 5 ++++
src/test/regress/sql/rolenames.sql | 2 ++
6 files changed, 75 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9b0778b..d42b497 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17398,7 +17398,8 @@ SELECT set_config('log_statement_stats', 'off', false);
</entry>
<entry><type>boolean</type></entry>
<entry>Cancel a backend's current query. This is also allowed if the
- calling role is a member of the role whose backend is being canceled,
+ calling role is a member of the role whose backend is being canceled or
+ the calling role has been granted <literal>pg_signal_backend</literal>,
however only superusers can cancel superuser backends.
</entry>
</row>
@@ -17422,8 +17423,9 @@ SELECT set_config('log_statement_stats', 'off', false);
</entry>
<entry><type>boolean</type></entry>
<entry>Terminate a backend. This is also allowed if the calling role
- is a member of the role whose backend is being terminated, however only
- superusers can terminate superuser backends.
+ is a member of the role whose backend is being terminated or the
+ calling role has been granted <literal>pg_signal_backend</literal>,
+ however only superusers can terminate superuser backends.
</entry>
</row>
</tbody>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index d1b6e59..7eaefe5 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -483,6 +483,57 @@ DROP ROLE doomed_role;
</para>
</sect1>
+ <sect1 id="default-roles">
+ <title>Default Roles</title>
+
+ <indexterm zone="default-roles">
+ <primary>role</>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> provides a set of default roles
+ which provide access to certain, commonly needed, privileged capabilities
+ and information. Administrators can GRANT these roles to users and/or
+ other roles in their environment, providing those users with access to
+ the specified capabilities and information.
+ </para>
+
+ <para>
+ The default roles are described in <xref linkend="default-roles-table">.
+ Note that the specific permissions for each of the default roles may
+ change in the future as additional capabilities are added. Administrators
+ should monitor the release notes for changes.
+ </para>
+
+ <table tocentry="1" id="default-roles-table">
+ <title>Default Roles</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Role</entry>
+ <entry>Allowed Access</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>pg_signal_backend</entry>
+ <entry>Send signals to other backends (eg: cancel query, terminate).</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ Administrators can grant access to these roles to users using the GRANT
+ command:
+
+<programlisting>
+GRANT pg_signal_backend TO admin_user;
+</programlisting>
+ </para>
+
+ </sect1>
+
<sect1 id="perm-functions">
<title>Function and Trigger Security</title>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index ebc7bb3..a44fa38 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -21,6 +21,7 @@
#include <unistd.h>
#include "access/sysattr.h"
+#include "catalog/pg_authid.h"
#include "catalog/catalog.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_type.h"
@@ -244,7 +245,8 @@ pg_signal_backend(int pid, int sig)
return SIGNAL_BACKEND_NOSUPERUSER;
/* Users can signal backends they have role membership in. */
- if (!has_privs_of_role(GetUserId(), proc->roleId))
+ if (!has_privs_of_role(GetUserId(), proc->roleId) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_SIGNAL_BACKENDID))
return SIGNAL_BACKEND_NOPERMISSION;
/*
@@ -290,7 +292,7 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be a member of the role whose query is being canceled"))));
+ (errmsg("must be a member of the role whose query is being canceled or member of pg_signal_backend"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
}
@@ -314,7 +316,7 @@ pg_terminate_backend(PG_FUNCTION_ARGS)
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be a member of the role whose process is being terminated"))));
+ (errmsg("must be a member of the role whose process is being terminated or member of pg_signal_backend"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
}
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index c163083..533081d 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -93,10 +93,16 @@ typedef FormData_pg_authid *Form_pg_authid;
*
* The uppercase quantities will be replaced at initdb time with
* user choices.
+ *
+ * If adding new default roles or changing the OIDs below, be sure to add or
+ * update the #defines which follow as appropriate.
* ----------------
*/
DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_ _null_));
+DATA(insert OID = 4200 ( "pg_signal_backend" f t f f f f f -1 _null_ _null_));
+
+#define BOOTSTRAP_SUPERUSERID 10
-#define BOOTSTRAP_SUPERUSERID 10
+#define DEFAULT_ROLE_SIGNAL_BACKENDID 4200
#endif /* PG_AUTHID_H */
diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out
index 01b3b90..e5d39e1 100644
--- a/src/test/regress/expected/rolenames.out
+++ b/src/test/regress/expected/rolenames.out
@@ -824,6 +824,11 @@ ERROR: role "pg_abcdef" is reserved
DETAIL: Cannot GRANT roles to a reserved role.
SET ROLE pg_testrole; -- error
ERROR: invalid value for parameter "role": "pg_testrole"
+SET ROLE pg_signal_backend; --error
+ERROR: invalid value for parameter "role": "pg_signal_backend"
+CREATE SCHEMA test_schema AUTHORIZATION pg_signal_backend;
+ERROR: role "pg_signal_backend" is reserved
+DETAIL: Cannot specify reserved role as owner.
UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
proname | proacl
diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql
index 1e0e9af..1f4ff98 100644
--- a/src/test/regress/sql/rolenames.sql
+++ b/src/test/regress/sql/rolenames.sql
@@ -385,6 +385,8 @@ GRANT testrol0 TO pg_abc; -- error
GRANT pg_abc TO pg_abcdef; -- error
SET ROLE pg_testrole; -- error
+SET ROLE pg_signal_backend; --error
+CREATE SCHEMA test_schema AUTHORIZATION pg_signal_backend;
UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
--
2.5.0
signature.asc
Description: Digital signature
