Default Roles

From 090994ca5a9ae0c64f8752b43801141582f31af7 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@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/user.c             | 40 +++++++++++++++++++++++++++++++++
 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 | 18 +++++++++++++++
 src/test/regress/sql/rolenames.sql      |  8 +++++++
 13 files changed, 172 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 8a85804..20f2e9f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1365,13 +1365,15 @@ testdb=&gt;
 
 
       <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=&gt;
       </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/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/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 be6b4a8..a4bb30f 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -664,7 +664,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, "
@@ -673,6 +673,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,
@@ -770,6 +771,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)
@@ -895,6 +903,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 9750a5b..bd521ef 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 fd8dc91..5722848 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 59f6f25..30232b5 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -227,7 +227,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"));
@@ -240,7 +240,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..c9be282 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_backup; -- error
+ERROR:  role name "pg_backup" is reserved
+DETAIL:  Role names starting with "pg_" are reserved.
+CREATE ROLE "pg_backup"; -- error
+ERROR:  role name "pg_backup" 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,12 @@ 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_backup; -- error
+ERROR:  role "pg_backup" is reserved
+DETAIL:  Cannot GRANT roles to a reserved role.
+GRANT pg_backup TO pg_monitor; -- error
+ERROR:  role "pg_monitor" is reserved
+DETAIL:  Cannot GRANT roles to a reserved role.
 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..65c97ec 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_backup; -- error
+CREATE ROLE "pg_backup"; -- error
+
 CREATE ROLE testrol0 SUPERUSER LOGIN;
 CREATE ROLE testrolx SUPERUSER LOGIN;
 CREATE ROLE testrol2 SUPERUSER;
@@ -376,6 +381,9 @@ 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_backup; -- error
+GRANT pg_backup TO pg_monitor; -- 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 f6c227203ea8f68a92154224db0b3d3d9fb99f88 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@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 ++++++-
 4 files changed, 68 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 000489d..24248fd 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17296,7 +17296,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>
@@ -17320,8 +17321,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 43f36db..28feb55 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"
@@ -243,7 +244,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;
 
 	/*
@@ -289,7 +291,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);
 }
@@ -313,7 +315,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 */
-- 
2.5.0

From a0724d91ffd1a93034d5b5d5df2b4ff54339d763 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@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=&gt;
 
 
       <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=&gt;
       </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 <sfrost@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

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index cc3d564..9b7ac73 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -274,7 +274,14 @@ CreateRole(CreateRoleStmt *stmt)
 	if (daddroleto)
 		addroleto = (List *) daddroleto->arg;
 	if (drolemembers)
+	{
+		ListCell   *rolemember;
+
 		rolemembers = (List *) drolemembers->arg;
+		foreach (rolemember, rolemembers)
+			check_rolespec_name(lfirst(rolemember),
+					"Cannot add reserved role to another role.");
+	}
 	if (dadminmembers)
 		adminmembers = (List *) dadminmembers->arg;
 	if (dvalidUntil)
@@ -650,7 +657,14 @@ AlterRole(AlterRoleStmt *stmt)
 					 errmsg("invalid connection limit: %d", connlimit)));
 	}
 	if (drolemembers)
+	{
+		ListCell   *rolemember;
+
 		rolemembers = (List *) drolemembers->arg;
+		foreach (rolemember, rolemembers)
+			check_rolespec_name(lfirst(rolemember),
+					"Cannot add reserved role to another role.");
+	}
 	if (dvalidUntil)
 		validUntil = strVal(dvalidUntil->arg);
 	if (dbypassRLS)
diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out
index 15a97ab..a2a4f6e 100644
--- a/src/test/regress/expected/rolenames.out
+++ b/src/test/regress/expected/rolenames.out
@@ -90,6 +90,9 @@ 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 USER pg_abc; -- error
+ERROR:  role "pg_abc" is reserved
+DETAIL:  Cannot add reserved role to another role.
 CREATE ROLE testrol0 SUPERUSER LOGIN;
 CREATE ROLE testrolx SUPERUSER LOGIN;
 CREATE ROLE testrol2 SUPERUSER;
@@ -213,6 +216,9 @@ LINE 1: ALTER ROLE "none" WITH NOREPLICATION;
                    ^
 ALTER ROLE nonexistent WITH NOREPLICATION; -- error
 ERROR:  role "nonexistent" does not exist
+ALTER ROLE testrol1 USER pg_abc; -- error
+ERROR:  role "pg_abc" is reserved
+DETAIL:  Cannot add reserved role to another role.
 --  ALTER USER
 BEGIN;
 SELECT * FROM chkrolattr();
diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql
index b58a163..c160793 100644
--- a/src/test/regress/sql/rolenames.sql
+++ b/src/test/regress/sql/rolenames.sql
@@ -61,6 +61,7 @@ CREATE ROLE pg_abc; -- error
 CREATE ROLE "pg_abc"; -- error
 CREATE ROLE pg_abcdef; -- error
 CREATE ROLE "pg_abcdef"; -- error
+CREATE ROLE testrol0 USER pg_abc; -- error
 
 CREATE ROLE testrol0 SUPERUSER LOGIN;
 CREATE ROLE testrolx SUPERUSER LOGIN;
@@ -99,6 +100,7 @@ ALTER ROLE "public" WITH NOREPLICATION; -- error
 ALTER ROLE NONE WITH NOREPLICATION; -- error
 ALTER ROLE "none" WITH NOREPLICATION; -- error
 ALTER ROLE nonexistent WITH NOREPLICATION; -- error
+ALTER ROLE testrol1 USER pg_abc; -- error
 
 --  ALTER USER
 BEGIN;