* Heikki Linnakangas (hlinn...@iki.fi) wrote:
> I agree with Robert's earlier point that this needs to be split into
> multiple patches, which can then be reviewed and discussed
> separately. Pending that, I'm going to mark this as "Waiting on
> author" in the commitfest.

Attached is an initial split which divides up reserving the role names
from actually adding the initial set of default roles.

Thanks!

Stephen
From 593ae64bf22bc343d7a5824d65c1224a77091710 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Wed, 30 Sep 2015 07:04:55 -0400
Subject: [PATCH 1/2] Reserve the "pg_" namespace for roles

This will prevent users from creating roles which being 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.
---
 src/backend/catalog/catalog.c |  5 +++--
 src/backend/commands/user.c   | 32 ++++++++++++++++++++++++++++++++
 src/bin/pg_dump/pg_dumpall.c  |  2 ++
 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 ++--
 8 files changed, 84 insertions(+), 10 deletions(-)

diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index 81ccebf..184aa7d 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 295e0b0..fde8d15 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 is 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,
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 3461335..addabd0 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -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,
@@ -895,6 +896,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 41d4606..d115b2a 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);
 
@@ -98,6 +99,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);
@@ -613,7 +619,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)
@@ -629,7 +636,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");
@@ -1017,6 +1025,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 8156b76..008726c 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -431,7 +431,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();
@@ -476,7 +476,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 898f8b3..703e065 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2639,7 +2639,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;
@@ -2684,6 +2684,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 822e71a..9e31c02 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 5b63e76..ff60a85 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"));
-- 
1.9.1


From 2a142cd8b1cf2ca31b4f975ee7912a4b8ae67a7d 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

---
 contrib/test_decoding/expected/permissions.out |  8 +--
 doc/src/sgml/catalogs.sgml                     |  9 +++
 doc/src/sgml/user-manag.sgml                   | 91 ++++++++++++++++++++++++++
 src/backend/access/transam/xlogfuncs.c         | 30 +++++----
 src/backend/replication/logical/logicalfuncs.c | 17 ++---
 src/backend/replication/slotfuncs.c            | 29 ++++----
 src/backend/replication/walsender.c            |  8 ++-
 src/backend/utils/adt/misc.c                   | 12 ++--
 src/backend/utils/adt/pgstatfuncs.c            | 25 ++++---
 src/backend/utils/misc/guc.c                   |  7 ++
 src/include/catalog/pg_authid.h                | 19 +++++-
 11 files changed, 200 insertions(+), 55 deletions(-)

diff --git a/contrib/test_decoding/expected/permissions.out b/contrib/test_decoding/expected/permissions.out
index 212fd1d..79a7f86 100644
--- a/contrib/test_decoding/expected/permissions.out
+++ b/contrib/test_decoding/expected/permissions.out
@@ -54,13 +54,13 @@ RESET ROLE;
 -- plain user *can't* can control replication
 SET ROLE lr_normal;
 SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
-ERROR:  must be superuser or replication role to use replication slots
+ERROR:  must be superuser or member of pg_replication to use replication slots
 INSERT INTO lr_test VALUES('lr_superuser_init');
 ERROR:  permission denied for relation lr_test
 SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
-ERROR:  must be superuser or replication role to use replication slots
+ERROR:  must be superuser or member of pg_replication to use replication slots
 SELECT pg_drop_replication_slot('regression_slot');
-ERROR:  must be superuser or replication role to use replication slots
+ERROR:  must be superuser or member of pg_replication to use replication slots
 RESET ROLE;
 -- replication users can drop superuser created slots
 SET ROLE lr_superuser;
@@ -90,7 +90,7 @@ SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_d
 RESET ROLE;
 SET ROLE lr_normal;
 SELECT pg_drop_replication_slot('regression_slot');
-ERROR:  must be superuser or replication role to use replication slots
+ERROR:  must be superuser or member of pg_replication to use replication slots
 RESET ROLE;
 -- all users can see existing slots
 SET ROLE lr_superuser;
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index e8bd3a1..db05afc 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -21,6 +21,15 @@
    particularly esoteric operations, such as adding index access methods.
   </para>
 
+  <para>
+   Also note that changing the permissions on objects in the system
+   catalogs, while possible, is unlikely to have the desired effect as
+   the internal lookup functions use a cache and do not check the
+   permissions nor policies of tables in the system catalog.  Further,
+   permission changes to objects in the system catalogs are not
+   preserved by pg_dump or across upgrades.
+  </para>
+
  <sect1 id="catalogs-overview">
   <title>Overview</title>
 
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 6476e3f..2b22557 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -414,6 +414,97 @@ DROP ROLE <replaceable>name</replaceable>;
   </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_backup</entry>
+       <entry>Start and stop backups, switch xlogs, and create restore points.</entry>
+      </row>
+      <row>
+       <entry>pg_montior</entry>
+       <entry>To privileged system information (eg: activity of other users, replication lag)</entry>
+      </row>
+      <row>
+       <entry>pg_replay</entry>
+       <entry>Pause and resume xlog replay on replicas.</entry>
+      </row>
+      <row>
+       <entry>pg_replication</entry>
+       <entry>Create, destroy, and work with replication slots.</entry>
+      </row>
+      <row>
+       <entry>pg_rotate_logfile</entry>
+       <entry>Request logfile rotation</entry>
+      </row>
+      <row>
+       <entry>pg_signal_backend</entry>
+       <entry>Send signals to other backends (eg: cancel query, terminate)</entry>
+      </row>
+      <row>
+       <entry>pg_file_settings</entry>
+       <entry>Allowed to view config settings from all config files</entry>
+      </row>
+      <row>
+       <entry>pg_admin</entry>
+       <entry>
+        Granted pg_backup, pg_monitor, pg_reply, pg_replication,
+        pg_rotate_logfile, pg_signal_backend and pg_file_settings roles.
+       </entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  <para>
+   Administrators can grant access to these roles to users using the GRANT
+   command:
+
+<programlisting>
+GRANT pg_backup TO backup_user;
+GRANT pg_monitor TO nagios;
+GRANT pg_admin TO admin_user;
+</programlisting>
+  </para>
+
+  <para>
+   Administrators should use the default roles for managing access to capabilities
+   and not change the permissions on the objects in the system catalogs, as such
+   changes are unlikely to have the desired effect and will not be preserved by
+   pg_dump or across upgrades.
+  </para>
+
+ </sect1>
+
  <sect1 id="perm-functions">
   <title>Function and Trigger Security</title>
 
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 329bb8c..dda39ad 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -22,11 +22,13 @@
 #include "access/xlog_internal.h"
 #include "access/xlogutils.h"
 #include "catalog/catalog.h"
+#include "catalog/pg_authid.h"
 #include "catalog/pg_type.h"
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "replication/walreceiver.h"
 #include "storage/smgr.h"
+#include "utils/acl.h"
 #include "utils/builtins.h"
 #include "utils/numeric.h"
 #include "utils/guc.h"
@@ -55,10 +57,12 @@ pg_start_backup(PG_FUNCTION_ARGS)
 
 	backupidstr = text_to_cstring(backupid);
 
-	if (!superuser() && !has_rolreplication(GetUserId()))
+	if (!has_rolreplication(GetUserId()) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-		   errmsg("must be superuser or replication role to run a backup")));
+				 errmsg("must be superuser or member of pg_backup or pg_replication to run a backup")));
 
 	/* Make sure we can open the directory with tablespaces in it */
 	dir = AllocateDir("pg_tblspc");
@@ -92,10 +96,12 @@ pg_stop_backup(PG_FUNCTION_ARGS)
 {
 	XLogRecPtr	stoppoint;
 
-	if (!superuser() && !has_rolreplication(GetUserId()))
+	if (!has_rolreplication(GetUserId()) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-		 (errmsg("must be superuser or replication role to run a backup"))));
+				 errmsg("must be superuser or member of pg_backup or pg_replication to run a backup")));
 
 	stoppoint = do_pg_stop_backup(NULL, true, NULL);
 
@@ -110,10 +116,10 @@ pg_switch_xlog(PG_FUNCTION_ARGS)
 {
 	XLogRecPtr	switchpoint;
 
-	if (!superuser())
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID))
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-			 (errmsg("must be superuser to switch transaction log files"))));
+				 errmsg("must be superuser or member of pg_backup to switch transaction log files")));
 
 	if (RecoveryInProgress())
 		ereport(ERROR,
@@ -139,10 +145,10 @@ pg_create_restore_point(PG_FUNCTION_ARGS)
 	char	   *restore_name_str;
 	XLogRecPtr	restorepoint;
 
-	if (!superuser())
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID))
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to create a restore point"))));
+				 errmsg("must be superuser or member of pg_backup to create a restore point")));
 
 	if (RecoveryInProgress())
 		ereport(ERROR,
@@ -348,10 +354,10 @@ pg_xlogfile_name(PG_FUNCTION_ARGS)
 Datum
 pg_xlog_replay_pause(PG_FUNCTION_ARGS)
 {
-	if (!superuser())
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLAYID))
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to control recovery"))));
+				 errmsg("must be superuser or member of pg_replay to control recovery")));
 
 	if (!RecoveryInProgress())
 		ereport(ERROR,
@@ -370,10 +376,10 @@ pg_xlog_replay_pause(PG_FUNCTION_ARGS)
 Datum
 pg_xlog_replay_resume(PG_FUNCTION_ARGS)
 {
-	if (!superuser())
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLAYID))
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to control recovery"))));
+				 errmsg("must be superuser or member of pg_replay to control recovery")));
 
 	if (!RecoveryInProgress())
 		ereport(ERROR,
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index a354a3f..766987f 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -23,12 +23,14 @@
 
 #include "access/xlog_internal.h"
 
+#include "catalog/pg_authid.h"
 #include "catalog/pg_type.h"
 
 #include "nodes/makefuncs.h"
 
 #include "mb/pg_wchar.h"
 
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/inval.h"
@@ -202,15 +204,6 @@ XLogRead(char *buf, TimeLineID tli, XLogRecPtr startptr, Size count)
 	}
 }
 
-static void
-check_permissions(void)
-{
-	if (!superuser() && !has_rolreplication(GetUserId()))
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser or replication role to use replication slots"))));
-}
-
 /*
  * read_page callback for logical decoding contexts.
  *
@@ -324,7 +317,11 @@ pg_logical_slot_get_changes_guts(FunctionCallInfo fcinfo, bool confirm, bool bin
 	if (get_call_result_type(fcinfo, NULL, &p->tupdesc) != TYPEFUNC_COMPOSITE)
 		elog(ERROR, "return type must be a row type");
 
-	check_permissions();
+	if (!has_rolreplication(GetUserId()) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser or member of pg_replication to use replication slots"))));
 
 	CheckLogicalDecodingRequirements();
 
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index b3c8140..421c6ed 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,21 +17,14 @@
 #include "miscadmin.h"
 
 #include "access/htup_details.h"
+#include "catalog/pg_authid.h"
 #include "replication/slot.h"
 #include "replication/logical.h"
 #include "replication/logicalfuncs.h"
+#include "utils/acl.h"
 #include "utils/builtins.h"
 #include "utils/pg_lsn.h"
 
-static void
-check_permissions(void)
-{
-	if (!superuser() && !has_rolreplication(GetUserId()))
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser or replication role to use replication slots"))));
-}
-
 /*
  * SQL function for creating a new physical (streaming replication)
  * replication slot.
@@ -52,7 +45,11 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
 	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
 		elog(ERROR, "return type must be a row type");
 
-	check_permissions();
+	if (!has_rolreplication(GetUserId()) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser or member of pg_replication to use replication slots"))));
 
 	CheckSlotRequirements();
 
@@ -110,7 +107,11 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
 	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
 		elog(ERROR, "return type must be a row type");
 
-	check_permissions();
+	if (!has_rolreplication(GetUserId()) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser or member of pg_replication to use replication slots"))));
 
 	CheckLogicalDecodingRequirements();
 
@@ -159,7 +160,11 @@ pg_drop_replication_slot(PG_FUNCTION_ARGS)
 {
 	Name		name = PG_GETARG_NAME(0);
 
-	check_permissions();
+	if (!has_rolreplication(GetUserId()) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser or member of pg_replication to use replication slots"))));
 
 	CheckSlotRequirements();
 
diff --git a/src/backend/replication/walsender.c b/src/backend/replication/walsender.c
index c95fe75..842aa95 100644
--- a/src/backend/replication/walsender.c
+++ b/src/backend/replication/walsender.c
@@ -48,6 +48,7 @@
 #include "access/xact.h"
 #include "access/xlog_internal.h"
 
+#include "catalog/pg_authid.h"
 #include "catalog/pg_type.h"
 #include "commands/dbcommands.h"
 #include "funcapi.h"
@@ -71,6 +72,7 @@
 #include "storage/proc.h"
 #include "storage/procarray.h"
 #include "tcop/tcopprot.h"
+#include "utils/acl.h"
 #include "utils/builtins.h"
 #include "utils/guc.h"
 #include "utils/memutils.h"
@@ -2803,11 +2805,11 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
 		memset(nulls, 0, sizeof(nulls));
 		values[0] = Int32GetDatum(walsnd->pid);
 
-		if (!superuser())
+		if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
 		{
 			/*
-			 * Only superusers can see details. Other users only get the pid
-			 * value to know it's a walsender, but no details.
+			 * Only members of pg_monitor can see details. Other users only get
+			 * the pid value to know it's a walsender, but no details.
 			 */
 			MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
 		}
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index c0495d9..6da052b 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"
@@ -122,7 +123,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;
 
 	/*
@@ -168,7 +170,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 cancelled"))));
+				 (errmsg("must be a member of the role whose query is being cancelled or member of pg_signal_backend"))));
 
 	PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
 }
@@ -192,7 +194,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);
 }
@@ -225,10 +227,10 @@ pg_reload_conf(PG_FUNCTION_ARGS)
 Datum
 pg_rotate_logfile(PG_FUNCTION_ARGS)
 {
-	if (!superuser())
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_ROTATE_LOGFILEID))
 		ereport(ERROR,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to rotate log files"))));
+				 (errmsg("must be superuser or member of pg_rotate_logfile to rotate log files"))));
 
 	if (!Logging_collector)
 	{
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f7c9bf6..8cac7c2 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -15,6 +15,7 @@
 #include "postgres.h"
 
 #include "access/htup_details.h"
+#include "catalog/pg_authid.h"
 #include "catalog/pg_type.h"
 #include "funcapi.h"
 #include "libpq/ip.h"
@@ -642,7 +643,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 		}
 
 		/* Values only available to role member */
-		if (has_privs_of_role(GetUserId(), beentry->st_userid))
+		if (has_privs_of_role(GetUserId(), beentry->st_userid) ||
+			has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
 		{
 			SockAddr	zero_clientaddr;
 
@@ -846,7 +848,8 @@ pg_stat_get_backend_activity(PG_FUNCTION_ARGS)
 
 	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 		activity = "<backend information not available>";
-	else if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+	else if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+			 !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
 		activity = "<insufficient privilege>";
 	else if (*(beentry->st_activity) == '\0')
 		activity = "<command string not enabled>";
@@ -867,7 +870,8 @@ pg_stat_get_backend_waiting(PG_FUNCTION_ARGS)
 	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 		PG_RETURN_NULL();
 
-	if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+	if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
 		PG_RETURN_NULL();
 
 	result = beentry->st_waiting;
@@ -886,7 +890,8 @@ pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS)
 	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 		PG_RETURN_NULL();
 
-	if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+	if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
 		PG_RETURN_NULL();
 
 	result = beentry->st_activity_start_timestamp;
@@ -912,7 +917,8 @@ pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS)
 	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 		PG_RETURN_NULL();
 
-	if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+	if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
 		PG_RETURN_NULL();
 
 	result = beentry->st_xact_start_timestamp;
@@ -934,7 +940,8 @@ pg_stat_get_backend_start(PG_FUNCTION_ARGS)
 	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 		PG_RETURN_NULL();
 
-	if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+	if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
 		PG_RETURN_NULL();
 
 	result = beentry->st_proc_start_timestamp;
@@ -958,7 +965,8 @@ pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS)
 	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 		PG_RETURN_NULL();
 
-	if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+	if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
 		PG_RETURN_NULL();
 
 	/* A zeroed client addr means we don't know */
@@ -1005,7 +1013,8 @@ pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
 	if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
 		PG_RETURN_NULL();
 
-	if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+	if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+		!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
 		PG_RETURN_NULL();
 
 	/* A zeroed client addr means we don't know */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 17053af..009925f 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -32,6 +32,7 @@
 #include "access/twophase.h"
 #include "access/xact.h"
 #include "catalog/namespace.h"
+#include "catalog/pg_authid.h"
 #include "commands/async.h"
 #include "commands/prepare.h"
 #include "commands/vacuum.h"
@@ -71,6 +72,7 @@
 #include "storage/predicate.h"
 #include "tcop/tcopprot.h"
 #include "tsearch/ts_cache.h"
+#include "utils/acl.h"
 #include "utils/builtins.h"
 #include "utils/bytea.h"
 #include "utils/guc_tables.h"
@@ -8198,6 +8200,11 @@ show_all_file_settings(PG_FUNCTION_ARGS)
 	MemoryContext per_query_ctx;
 	MemoryContext oldcontext;
 
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_FILE_SETTINGSID))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must be superuser or member of pg_file_settings to see all config file settings")));
+
 	/* Check to see if caller supports us returning a tuplestore */
 	if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
 		ereport(ERROR,
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index d5f19d6..852aac4 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -96,7 +96,24 @@ typedef FormData_pg_authid *Form_pg_authid;
  * ----------------
  */
 DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_ _null_));
+DATA(insert OID = 3288 ( "pg_admin" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3289 ( "pg_monitor" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3290 ( "pg_backup" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3291 ( "pg_replay" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3292 ( "pg_replication" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3293 ( "pg_rotate_logfile" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3294 ( "pg_signal_backend" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3295 ( "pg_file_settings" f t f f f f f -1 _null_ _null_));
 
-#define BOOTSTRAP_SUPERUSERID 10
+#define BOOTSTRAP_SUPERUSERID			10
+
+#define DEFAULT_ROLE_ADMINID			3288
+#define DEFAULT_ROLE_MONITORID			3289
+#define DEFAULT_ROLE_BACKUPID			3290
+#define DEFAULT_ROLE_REPLAYID			3291
+#define DEFAULT_ROLE_REPLICATIONID		3292
+#define DEFAULT_ROLE_ROTATE_LOGFILEID	3293
+#define DEFAULT_ROLE_SIGNAL_BACKENDID	3294
+#define DEFAULT_ROLE_FILE_SETTINGSID	3295
 
 #endif   /* PG_AUTHID_H */
-- 
1.9.1

Attachment: signature.asc
Description: Digital signature

Reply via email to