Default Roles (was: Additional role attributes)

From 381a3e619b8450a0f3a5225d70098fcb8291fd52 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Thu, 7 May 2015 23:35:03 -0400
Subject: [PATCH] Create default roles for administrative functions

To reduce the number of users on a system who are superusers,
create a set of roles by default during initdb which are granted rights
to certain functions and views that allow non-superusers to perform
specific administrative tasks and have access to privileged information.

The prefix "pg_" is reserved for default system roles, similar to
schemas and tablespaces.  pg_upgrade is modified to check for any roles
which start with "pg_" and complain if they exist.  pg_dumpall is
modified to not dump out roles starting with "pg_" on 9.5-and-above
systems.  CreateRole is modified to refuse creation of roles which start
with "pg_", similar to CreateSchema.

Roles created are: pg_backup, pg_monitor, pg_replay, pg_replication,
pg_rotate_logfile, pg_signal_backend and pg_admin.

Behavior of existing system views is unchanged.  Views and functions are
added for pg_stat_activity_all and pg_stat_replication_all, to provide
unfiltered results for users granted the pg_monitor role.

is_superuser() checks are removed and EXECUTE revoked from public and
instead granted to the appropriate role for appropriate administrative
functions.

Role attribute REPLICATION is superseded by the pg_replication role and
therefore removed.  CREATE ROLE and ALTER ROLE will still accept the
option and transform it into a GRANT pg_replication TO role; to
facilitate upgrades from older versions.
---
 contrib/test_decoding/expected/permissions.out |   8 +-
 doc/src/sgml/catalogs.sgml                     |  32 +---
 doc/src/sgml/ref/alter_role.sgml               |   5 +-
 doc/src/sgml/ref/create_role.sgml              |  16 --
 doc/src/sgml/ref/createuser.sgml               |  22 ---
 doc/src/sgml/ref/pg_basebackup.sgml            |   4 +-
 doc/src/sgml/ref/pg_receivexlog.sgml           |   4 +-
 doc/src/sgml/user-manag.sgml                   |  87 ++++++++++
 src/backend/access/transam/xlogfuncs.c         |  30 ----
 src/backend/catalog/catalog.c                  |   5 +-
 src/backend/catalog/system_views.sql           | 134 ++++++++++++++-
 src/backend/commands/user.c                    |  61 +++++--
 src/backend/replication/logical/logicalfuncs.c |  11 --
 src/backend/replication/slotfuncs.c            |  15 --
 src/backend/replication/walsender.c            |  92 ++++++++--
 src/backend/utils/adt/misc.c                   |  64 +++++--
 src/backend/utils/adt/pgstatfuncs.c            | 103 +++++++++++-
 src/backend/utils/init/miscinit.c              |  18 --
 src/backend/utils/init/postinit.c              |   2 +-
 src/bin/pg_dump/pg_dumpall.c                   |  17 +-
 src/bin/pg_upgrade/check.c                     |  40 ++++-
 src/bin/psql/describe.c                        |   2 +-
 src/bin/psql/tab-complete.c                    |  16 +-
 src/bin/scripts/createuser.c                   |  15 --
 src/include/catalog/pg_authid.h                |  31 +++-
 src/include/catalog/pg_proc.h                  |   6 +
 src/include/miscadmin.h                        |   1 -
 src/include/replication/walsender.h            |   1 +
 src/include/utils/builtins.h                   |   1 +
 src/test/regress/expected/rolenames.out        | 224 ++-----------------------
 src/test/regress/expected/rules.out            |  45 ++++-
 src/test/regress/sql/rolenames.sql             |  46 +----
 32 files changed, 664 insertions(+), 494 deletions(-)

diff --git a/contrib/test_decoding/expected/permissions.out b/contrib/test_decoding/expected/permissions.out
index 212fd1d..f2c1895 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:  permission denied for function pg_create_logical_replication_slot
 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:  permission denied for function pg_logical_slot_get_changes
 SELECT pg_drop_replication_slot('regression_slot');
-ERROR:  must be superuser or replication role to use replication slots
+ERROR:  permission denied for function pg_drop_replication_slot
 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:  permission denied for function pg_drop_replication_slot
 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 2756652..1937ddf 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>
 
@@ -1440,17 +1449,6 @@
      </row>
 
      <row>
-      <entry><structfield>rolreplication</structfield></entry>
-      <entry><type>bool</type></entry>
-      <entry>
-       Role is a replication role. That is, this role can initiate streaming
-       replication (see <xref linkend="streaming-replication">) and set/unset
-       the system backup mode using <function>pg_start_backup</> and
-       <function>pg_stop_backup</>
-      </entry>
-     </row>
-
-     <row>
       <entry><structfield>rolbypassrls</structfield></entry>
       <entry><type>bool</type></entry>
       <entry>
@@ -8713,18 +8711,6 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
      </row>
 
      <row>
-      <entry><structfield>rolreplication</structfield></entry>
-      <entry><type>bool</type></entry>
-      <entry></entry>
-      <entry>
-       Role is a replication role. That is, this role can initiate streaming
-       replication (see <xref linkend="streaming-replication">) and set/unset
-       the system backup mode using <function>pg_start_backup</> and
-       <function>pg_stop_backup</>
-      </entry>
-     </row>
-
-     <row>
       <entry><structfield>rolconnlimit</structfield></entry>
       <entry><type>int4</type></entry>
       <entry></entry>
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index e97bf4c..0ef7dac 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -31,7 +31,6 @@ ALTER ROLE <replaceable class="PARAMETER">role_specification</replaceable> [ WIT
     | CREATEUSER | NOCREATEUSER
     | INHERIT | NOINHERIT
     | LOGIN | NOLOGIN
-    | REPLICATION | NOREPLICATION
     | BYPASSRLS | NOBYPASSRLS
     | CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
     | [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
@@ -71,7 +70,7 @@ ALTER ROLE { <replaceable class="PARAMETER">role_specification</replaceable> | A
    Attributes not mentioned in the command retain their previous settings.
    Database superusers can change any of these settings for any role.
    Roles having <literal>CREATEROLE</> privilege can change any of these
-   settings, but only for non-superuser and non-replication roles.
+   settings, but only for non-superuser roles.
    Ordinary roles can only change their own password.
   </para>
 
@@ -166,8 +165,6 @@ ALTER ROLE { <replaceable class="PARAMETER">role_specification</replaceable> | A
       <term><literal>NOINHERIT</literal></term>
       <term><literal>LOGIN</literal></term>
       <term><literal>NOLOGIN</literal></term>
-      <term><literal>REPLICATION</literal></term>
-      <term><literal>NOREPLICATION</literal></term>
       <term><literal>BYPASSRLS</literal></term>
       <term><literal>NOBYPASSRLS</literal></term>
       <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index ea26027..c3daaf2 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -31,7 +31,6 @@ CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
     | CREATEUSER | NOCREATEUSER
     | INHERIT | NOINHERIT
     | LOGIN | NOLOGIN
-    | REPLICATION | NOREPLICATION
     | BYPASSRLS | NOBYPASSRLS
     | CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
     | [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
@@ -177,21 +176,6 @@ CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replac
      </varlistentry>
 
      <varlistentry>
-      <term><literal>REPLICATION</literal></term>
-      <term><literal>NOREPLICATION</literal></term>
-      <listitem>
-       <para>
-        These clauses determine whether a role is allowed to initiate
-        streaming replication or put the system in and out of backup mode.
-        A role having the <literal>REPLICATION</> attribute is a very
-        highly privileged role, and should only be used on roles actually
-        used for replication. If not specified,
-        <literal>NOREPLICATION</literal> is the default.
-       </para>
-      </listitem>
-     </varlistentry>
-
-     <varlistentry>
       <term><literal>BYPASSRLS</literal></term>
       <term><literal>NOBYPASSRLS</literal></term>
       <listitem>
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 4332008..e54927e 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -280,28 +280,6 @@ PostgreSQL documentation
      </varlistentry>
 
      <varlistentry>
-      <term><option>--replication</></term>
-      <listitem>
-       <para>
-        The new user will have the <literal>REPLICATION</literal> privilege,
-        which is described more fully in the documentation for <xref
-        linkend="sql-createrole">.
-       </para>
-      </listitem>
-     </varlistentry>
-
-     <varlistentry>
-      <term><option>--no-replication</></term>
-      <listitem>
-       <para>
-        The new user will not have the <literal>REPLICATION</literal>
-        privilege, which is described more fully in the documentation for <xref
-        linkend="sql-createrole">.
-       </para>
-      </listitem>
-     </varlistentry>
-
-     <varlistentry>
        <term><option>-?</></term>
        <term><option>--help</></term>
        <listitem>
diff --git a/doc/src/sgml/ref/pg_basebackup.sgml b/doc/src/sgml/ref/pg_basebackup.sgml
index 642fccf..17986ee 100644
--- a/doc/src/sgml/ref/pg_basebackup.sgml
+++ b/doc/src/sgml/ref/pg_basebackup.sgml
@@ -51,8 +51,8 @@ PostgreSQL documentation
   <para>
    The backup is made over a regular <productname>PostgreSQL</productname>
    connection, and uses the replication protocol. The connection must be made
-   with a superuser or a user having <literal>REPLICATION</literal>
-   permissions (see <xref linkend="role-attributes">),
+   with a superuser or a user who has been granted the
+   <literal>pg_replication</literal> role (see <xref linkend="default-roles">),
    and <filename>pg_hba.conf</filename> must explicitly permit the replication
    connection. The server must also be configured
    with <xref linkend="guc-max-wal-senders"> set high enough to leave at least
diff --git a/doc/src/sgml/ref/pg_receivexlog.sgml b/doc/src/sgml/ref/pg_receivexlog.sgml
index 0c99744..c5dfead 100644
--- a/doc/src/sgml/ref/pg_receivexlog.sgml
+++ b/doc/src/sgml/ref/pg_receivexlog.sgml
@@ -59,8 +59,8 @@ PostgreSQL documentation
    The transaction log is streamed over a regular
    <productname>PostgreSQL</productname> connection, and uses the replication
    protocol. The connection must be made with a superuser or a user
-   having <literal>REPLICATION</literal> permissions (see
-   <xref linkend="role-attributes">), and <filename>pg_hba.conf</filename>
+   having who has been granted the <literal>pg_replication</literal> role (see
+   <xref linkend="default-roles">), and <filename>pg_hba.conf</filename>
    must explicitly permit the replication connection. The server must also be
    configured with <xref linkend="guc-max-wal-senders"> set high enough to
    leave at least one session available for the stream.
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 177ac7a..cd25475 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -414,6 +414,93 @@ 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_admin</entry>
+       <entry>
+        Granted pg_backup, pg_monitor, pg_reply, pg_replication,
+        pg_rotate_logfile and pg_signal_backend 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 2179bf7..c9d9f3d 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -54,11 +54,6 @@ pg_start_backup(PG_FUNCTION_ARGS)
 
 	backupidstr = text_to_cstring(backupid);
 
-	if (!superuser() && !has_rolreplication(GetUserId()))
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-		   errmsg("must be superuser or replication role to run a backup")));
-
 	startpoint = do_pg_start_backup(backupidstr, fast, NULL, NULL);
 
 	PG_RETURN_LSN(startpoint);
@@ -82,11 +77,6 @@ pg_stop_backup(PG_FUNCTION_ARGS)
 {
 	XLogRecPtr	stoppoint;
 
-	if (!superuser() && !has_rolreplication(GetUserId()))
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-		 (errmsg("must be superuser or replication role to run a backup"))));
-
 	stoppoint = do_pg_stop_backup(NULL, true, NULL);
 
 	PG_RETURN_LSN(stoppoint);
@@ -100,11 +90,6 @@ pg_switch_xlog(PG_FUNCTION_ARGS)
 {
 	XLogRecPtr	switchpoint;
 
-	if (!superuser())
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-			 (errmsg("must be superuser to switch transaction log files"))));
-
 	if (RecoveryInProgress())
 		ereport(ERROR,
 				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -129,11 +114,6 @@ pg_create_restore_point(PG_FUNCTION_ARGS)
 	char	   *restore_name_str;
 	XLogRecPtr	restorepoint;
 
-	if (!superuser())
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to create a restore point"))));
-
 	if (RecoveryInProgress())
 		ereport(ERROR,
 				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -338,11 +318,6 @@ pg_xlogfile_name(PG_FUNCTION_ARGS)
 Datum
 pg_xlog_replay_pause(PG_FUNCTION_ARGS)
 {
-	if (!superuser())
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to control recovery"))));
-
 	if (!RecoveryInProgress())
 		ereport(ERROR,
 				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -360,11 +335,6 @@ pg_xlog_replay_pause(PG_FUNCTION_ARGS)
 Datum
 pg_xlog_replay_resume(PG_FUNCTION_ARGS)
 {
-	if (!superuser())
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to control recovery"))));
-
 	if (!RecoveryInProgress())
 		ereport(ERROR,
 				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index fa2aa27..9c3b760 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.5, this is also true
+ *		for roles.
  */
 bool
 IsReservedName(const char *name)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 18921c4..e619121 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -14,7 +14,6 @@ CREATE VIEW pg_roles AS
         rolcreaterole,
         rolcreatedb,
         rolcanlogin,
-        rolreplication,
         rolconnlimit,
         '********'::text as rolpassword,
         rolvaliduntil,
@@ -30,7 +29,6 @@ CREATE VIEW pg_shadow AS
         pg_authid.oid AS usesysid,
         rolcreatedb AS usecreatedb,
         rolsuper AS usesuper,
-        rolreplication AS userepl,
         rolbypassrls AS usebypassrls,
         rolpassword AS passwd,
         rolvaliduntil::abstime AS valuntil,
@@ -55,7 +53,6 @@ CREATE VIEW pg_user AS
         usesysid,
         usecreatedb,
         usesuper,
-        userepl,
         usebypassrls,
         '********'::text as passwd,
         valuntil,
@@ -629,6 +626,32 @@ CREATE VIEW pg_stat_activity AS
     WHERE S.datid = D.oid AND
             S.usesysid = U.oid;
 
+CREATE VIEW pg_stat_activity_all AS
+    SELECT
+            S.datid AS datid,
+            D.datname AS datname,
+            S.pid,
+            S.usesysid,
+            U.rolname AS usename,
+            S.application_name,
+            S.client_addr,
+            S.client_hostname,
+            S.client_port,
+            S.backend_start,
+            S.xact_start,
+            S.query_start,
+            S.state_change,
+            S.waiting,
+            S.state,
+            S.backend_xid,
+            s.backend_xmin,
+            S.query
+    FROM pg_database D, pg_stat_get_activity_all(NULL) AS S, pg_authid U
+    WHERE S.datid = D.oid AND
+            S.usesysid = U.oid;
+
+REVOKE ALL on pg_stat_activity_all FROM public;
+
 CREATE VIEW pg_stat_replication AS
     SELECT
             S.pid,
@@ -663,6 +686,31 @@ CREATE VIEW pg_stat_ssl AS
             S.sslclientdn AS clientdn
     FROM pg_stat_get_activity(NULL) AS S;
 
+CREATE VIEW pg_stat_replication_all AS
+    SELECT
+            S.pid,
+            S.usesysid,
+            U.rolname AS usename,
+            S.application_name,
+            S.client_addr,
+            S.client_hostname,
+            S.client_port,
+            S.backend_start,
+            S.backend_xmin,
+            W.state,
+            W.sent_location,
+            W.write_location,
+            W.flush_location,
+            W.replay_location,
+            W.sync_priority,
+            W.sync_state
+    FROM pg_stat_get_activity_all(NULL) AS S, pg_authid U,
+            pg_stat_get_wal_senders_all() AS W
+    WHERE S.usesysid = U.oid AND
+            S.pid = W.pid;
+
+REVOKE ALL on pg_stat_replication_all FROM public;
+
 CREATE VIEW pg_replication_slots AS
     SELECT
             L.slot_name,
@@ -922,3 +970,83 @@ RETURNS interval
 LANGUAGE INTERNAL
 STRICT IMMUTABLE
 AS 'make_interval';
+
+-- Revoke privileges for functions that should not be available to
+-- all users.  Administrators are allowed to change this later, if
+-- they wish.
+
+-- XLOG location can leak information based on compressed WAL records
+REVOKE EXECUTE ON FUNCTION pg_current_xlog_insert_location() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_last_xlog_receive_location() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_current_xlog_location() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_last_xlog_replay_location() FROM public;
+
+-- Unfiltered information about activity on the system
+REVOKE EXECUTE ON FUNCTION pg_stat_get_activity_all(integer) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_stat_get_wal_senders_all() FROM public;
+
+-- Monitoring user needs to be able to see XLOG replay info
+-- Also allowed to view all user activity
+GRANT EXECUTE ON FUNCTION pg_current_xlog_insert_location() TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_last_xlog_receive_location() TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_current_xlog_location() TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_last_xlog_replay_location() TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_stat_get_activity_all(integer) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_stat_get_wal_senders_all() TO pg_monitor;
+GRANT SELECT ON TABLE pg_stat_activity_all TO pg_monitor;
+GRANT SELECT ON TABLE pg_stat_replication_all TO pg_monitor;
+
+-- Starting/stopping backups are not appropriate for normal users
+REVOKE EXECUTE ON FUNCTION pg_start_backup(text, boolean) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_stop_backup() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_switch_xlog() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_create_restore_point(text) FROM public;
+
+-- pg_backup and pg_replication roles are allowed access to these functions
+GRANT EXECUTE ON FUNCTION pg_start_backup(text, boolean) TO pg_backup, pg_replication;
+GRANT EXECUTE ON FUNCTION pg_stop_backup() TO pg_backup, pg_replication;
+
+-- pg_backup role is allowed access to these functions
+GRANT EXECUTE ON FUNCTION pg_switch_xlog() TO pg_backup;
+GRANT EXECUTE ON FUNCTION pg_create_restore_point(text) TO pg_backup;
+
+-- Only certain users should be able to rotate logfiles
+REVOKE EXECUTE ON FUNCTION pg_rotate_logfile() FROM public;
+
+-- Only privileged users should be able to signal other backends
+REVOKE EXECUTE ON FUNCTION pg_signal_backend(int, int) FROM public;
+
+-- Replica control
+REVOKE EXECUTE ON FUNCTION pg_xlog_replay_pause() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_xlog_replay_resume() FROM public;
+
+-- Create pg_replay role for controlling replica replay
+GRANT EXECUTE ON FUNCTION pg_xlog_replay_pause() TO pg_replay;
+GRANT EXECUTE ON FUNCTION pg_xlog_replay_resume() TO pg_replay;
+
+-- Creating and working with replication slots should not be available
+-- to all users.
+REVOKE EXECUTE ON FUNCTION pg_create_physical_replication_slot(name) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_create_logical_replication_slot(name, name) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_drop_replication_slot(name) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_logical_slot_get_changes(name, pg_lsn, int, VARIADIC options text[]) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_logical_slot_peek_changes(name, pg_lsn, int, VARIADIC options text[]) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_logical_slot_get_binary_changes(name, pg_lsn, int, VARIADIC options text[]) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_logical_slot_peek_binary_changes(name, pg_lsn, int, VARIADIC options text[]) FROM public;
+
+-- Role which is allowed to work with replication slots
+GRANT EXECUTE ON FUNCTION pg_create_physical_replication_slot(name) TO pg_replication;
+GRANT EXECUTE ON FUNCTION pg_drop_replication_slot(name) TO pg_replication;
+GRANT EXECUTE ON FUNCTION pg_create_logical_replication_slot(name,name) TO pg_replication;
+GRANT EXECUTE ON FUNCTION pg_logical_slot_get_changes(name, pg_lsn, int, VARIADIC options text[])TO pg_replication;
+GRANT EXECUTE ON FUNCTION pg_logical_slot_peek_changes(name, pg_lsn, int, VARIADIC options text[])TO pg_replication;
+GRANT EXECUTE ON FUNCTION pg_logical_slot_get_binary_changes(name, pg_lsn, int, VARIADIC options text[])TO pg_replication;
+GRANT EXECUTE ON FUNCTION pg_logical_slot_peek_binary_changes(name, pg_lsn, int, VARIADIC options text[])TO pg_replication;
+
+-- Roles specifically for these rights
+GRANT EXECUTE ON FUNCTION pg_rotate_logfile() TO pg_rotate_logfile;
+GRANT EXECUTE ON FUNCTION pg_signal_backend(int, int) TO pg_signal_backend;
+
+-- Admin user allowed to rotate logfiles, signal backends
+-- and gets monitor, backup, replay, and replication
+GRANT pg_monitor, pg_backup, pg_replay, pg_replication, pg_rotate_logfile, pg_signal_backend TO pg_admin;
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 456c27e..a356698 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"
@@ -310,12 +311,20 @@ CreateRole(CreateRoleStmt *stmt)
 					 errmsg("permission denied to create role")));
 	}
 
+	/*
+	 * Check that the user is not trying to create a role whose name is
+	 * reserved for special users- public means "all", none means "none"
+	 * and the prefix "pg_" is reserved for system roles (those roles are
+	 * created at initdb time, see include/catalog/pg_authid.h).
+	 */
 	if (strcmp(stmt->role, "public") == 0 ||
-		strcmp(stmt->role, "none") == 0)
+		strcmp(stmt->role, "none") == 0 ||
+		IsReservedName(stmt->role))
 		ereport(ERROR,
 				(errcode(ERRCODE_RESERVED_NAME),
 				 errmsg("role name \"%s\" is reserved",
-						stmt->role)));
+						stmt->role),
+				 errdetail("Names \"public\", \"none\", and the prefix \"pg_\" are reserved.")));
 
 	/*
 	 * Check the pg_authid relation to be certain the role doesn't already
@@ -369,7 +378,6 @@ CreateRole(CreateRoleStmt *stmt)
 	new_record[Anum_pg_authid_rolcreaterole - 1] = BoolGetDatum(createrole);
 	new_record[Anum_pg_authid_rolcreatedb - 1] = BoolGetDatum(createdb);
 	new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
-	new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
 	new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
 
 	if (password)
@@ -421,10 +429,22 @@ CreateRole(CreateRoleStmt *stmt)
 	 * Advance command counter so we can see new record; else tests in
 	 * AddRoleMems may fail.
 	 */
-	if (addroleto || adminmembers || rolemembers)
+	if (addroleto || adminmembers || rolemembers || isreplication)
 		CommandCounterIncrement();
 
 	/*
+	 * In 9.4 and earlier, we supported a "replication" role attribute, so
+	 * if REPLICATION is specified then we add the role to the replication
+	 * role pg_replication which provides the equivilant rights in post-9.4
+	 * versions.
+	 */
+	if (isreplication)
+		AddRoleMems("pg_replication", DEFAULT_ROLE_REPLICATIONID,
+					list_make1(makeString(stmt->role)),
+					list_make1_oid(roleid),
+					GetUserId(), false);
+
+	/*
 	 * Add the new role to the specified existing roles.
 	 */
 	foreach(item, addroleto)
@@ -659,6 +679,16 @@ AlterRole(AlterRoleStmt *stmt)
 	roleid = HeapTupleGetOid(tuple);
 
 	/*
+	 * Adding replication to a role is supported for backwards compatibility
+	 * with 9.4 and earlier, but we do not support removing replication via
+	 * this method.
+	 */
+	if (isreplication == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("must revoke pg_replication from role to remove replication privilege")));
+
+	/*
 	 * To mess with a superuser you gotta be superuser; else you need
 	 * createrole, or just want to change your own password
 	 */
@@ -669,12 +699,12 @@ AlterRole(AlterRoleStmt *stmt)
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must be superuser to alter superusers")));
 	}
-	else if (authform->rolreplication || isreplication >= 0)
+	else if (isreplication >= 0)
 	{
 		if (!superuser())
 			ereport(ERROR,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-					 errmsg("must be superuser to alter replication users")));
+					 errmsg("must be superuser to add replication users")));
 	}
 	else if (authform->rolbypassrls || bypassrls >= 0)
 	{
@@ -689,7 +719,6 @@ AlterRole(AlterRoleStmt *stmt)
 			  createrole < 0 &&
 			  createdb < 0 &&
 			  canlogin < 0 &&
-			  isreplication < 0 &&
 			  !dconnlimit &&
 			  !rolemembers &&
 			  !validUntil &&
@@ -767,12 +796,6 @@ AlterRole(AlterRoleStmt *stmt)
 		new_record_repl[Anum_pg_authid_rolcanlogin - 1] = true;
 	}
 
-	if (isreplication >= 0)
-	{
-		new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication > 0);
-		new_record_repl[Anum_pg_authid_rolreplication - 1] = true;
-	}
-
 	if (dconnlimit)
 	{
 		new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
@@ -833,6 +856,18 @@ AlterRole(AlterRoleStmt *stmt)
 	if (rolemembers)
 		CommandCounterIncrement();
 
+	/*
+	 * In 9.4 and earlier, we supported a "replication" role attribute, so
+	 * if REPLICATION is specified then we add the role to the replication
+	 * role pg_replication which provides the equivilant rights in post-9.4
+	 * versions.
+	 */
+	if (isreplication > 0)
+		AddRoleMems("pg_replication", DEFAULT_ROLE_REPLICATIONID,
+					list_make1(makeString(rolename)),
+					list_make1_oid(roleid),
+					GetUserId(), false);
+
 	if (stmt->action == +1)		/* add members to role */
 		AddRoleMems(rolename, roleid,
 					rolemembers, roleSpecsToIds(rolemembers),
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index 3be5263..2995bfa 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -202,15 +202,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,8 +315,6 @@ 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();
-
 	CheckLogicalDecodingRequirements();
 
 	arr = PG_GETARG_ARRAYTYPE_P(3);
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 3d9aadb..4cdc78d 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -23,15 +23,6 @@
 #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.
@@ -51,8 +42,6 @@ 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();
-
 	CheckSlotRequirements();
 
 	/* acquire replication slot, this will check for conflicting names */
@@ -94,8 +83,6 @@ 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();
-
 	CheckLogicalDecodingRequirements();
 
 	/*
@@ -143,8 +130,6 @@ pg_drop_replication_slot(PG_FUNCTION_ARGS)
 {
 	Name		name = PG_GETARG_NAME(0);
 
-	check_permissions();
-
 	CheckSlotRequirements();
 
 	ReplicationSlotDrop(NameStr(*name));
diff --git a/src/backend/replication/walsender.c b/src/backend/replication/walsender.c
index 4a20569..75e1df9 100644
--- a/src/backend/replication/walsender.c
+++ b/src/backend/replication/walsender.c
@@ -217,6 +217,7 @@ static XLogRecPtr WalSndWaitForWal(XLogRecPtr loc);
 
 static void XLogRead(char *buf, XLogRecPtr startptr, Size count);
 
+static void populate_pg_stat_get_wal_senders(TupleDesc tupdesc, Tuplestorestate *tupstore, bool filter);
 
 /* Initialize walsender process before entering the main command loop */
 void
@@ -2720,19 +2721,17 @@ WalSndGetStateString(WalSndState state)
 
 /*
  * Returns activity of walsenders, including pids and xlog locations sent to
- * standby servers.
+ * standby servers.  Note that this version filters out the results unless the
+ * caller is a superuser.
  */
 Datum
 pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_WAL_SENDERS_COLS	8
 	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
 	TupleDesc	tupdesc;
 	Tuplestorestate *tupstore;
 	MemoryContext per_query_ctx;
 	MemoryContext oldcontext;
-	WalSnd	   *sync_standby;
-	int			i;
 
 	/* check to see if caller supports us returning a tuplestore */
 	if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
@@ -2760,6 +2759,80 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
 	MemoryContextSwitchTo(oldcontext);
 
 	/*
+	 * Populate the tuplestore.
+	 *
+	 * For non-superusers, we ask that the results be filtered.
+	 */
+	populate_pg_stat_get_wal_senders(tupdesc, tupstore, !superuser());
+
+	/* clean up and return the tuplestore */
+	tuplestore_donestoring(tupstore);
+
+	return (Datum) 0;
+}
+
+/*
+ * Returns activity of walsenders, including pids and xlog locations sent to
+ * standby servers.  Note that this version does NOT filter out the results,
+ * therefore the permissions must be managed at the GRANT level.
+ */
+Datum
+pg_stat_get_wal_senders_all(PG_FUNCTION_ARGS)
+{
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	TupleDesc	tupdesc;
+	Tuplestorestate *tupstore;
+	MemoryContext per_query_ctx;
+	MemoryContext oldcontext;
+
+	/* check to see if caller supports us returning a tuplestore */
+	if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("set-valued function called in context that cannot accept a set")));
+	if (!(rsinfo->allowedModes & SFRM_Materialize))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("materialize mode required, but it is not " \
+						"allowed in this context")));
+
+	/* Build a tuple descriptor for our result type */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+	oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+	tupstore = tuplestore_begin_heap(true, false, work_mem);
+	rsinfo->returnMode = SFRM_Materialize;
+	rsinfo->setResult = tupstore;
+	rsinfo->setDesc = tupdesc;
+
+	MemoryContextSwitchTo(oldcontext);
+
+	/* Populate the tuplestore */
+	populate_pg_stat_get_wal_senders(tupdesc, tupstore, false);
+
+	/* clean up and return the tuplestore */
+	tuplestore_donestoring(tupstore);
+
+	return (Datum) 0;
+}
+
+/*
+ * Returns activity of walsenders, possibly filtered.
+ *
+ * If filter is true, then the results are sanitized for public consumption,
+ * otherwise all details are returned.
+ */
+static void
+populate_pg_stat_get_wal_senders(TupleDesc tupdesc, Tuplestorestate *tupstore, bool filter)
+{
+#define PG_STAT_GET_WAL_SENDERS_COLS	8
+	WalSnd	   *sync_standby;
+	int			i;
+
+	/*
 	 * Get the currently active synchronous standby.
 	 */
 	LWLockAcquire(SyncRepLock, LW_SHARED);
@@ -2794,11 +2867,11 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
 		memset(nulls, 0, sizeof(nulls));
 		values[0] = Int32GetDatum(walsnd->pid);
 
-		if (!superuser())
+		if (filter)
 		{
 			/*
-			 * Only superusers can see details. Other users only get the pid
-			 * value to know it's a walsender, but no details.
+			 * When asked to filter record results, set all the rest of the
+			 * columns to NULL.
 			 */
 			MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
 		}
@@ -2843,10 +2916,7 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 	}
 
-	/* clean up and return the tuplestore */
-	tuplestore_donestoring(tupstore);
-
-	return (Datum) 0;
+	return;
 }
 
 /*
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 61d609f..0204da6 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -76,11 +76,19 @@ current_query(PG_FUNCTION_ARGS)
 }
 
 /*
- * Send a signal to another backend.
+ * Internal helper function for sending a signal to another backend.
  *
- * The signal is delivered if the user is either a superuser or the same
- * role as the backend being signaled. For "dangerous" signals, an explicit
- * check for superuser needs to be done prior to calling this function.
+ * The signal is delivered if the user is a superuser.  If the other backend
+ * is owned by a superuser role, then the calling user must be a superuser.
+ *
+ * When perm_check is passed in as true, then the user must be a member of
+ * the role which owns the backend being signaled.  For "dangerous" signals,
+ * an explicit check for superuser needs to be done prior to calling this
+ * function.
+ *
+ * When perm_check is passwd in as false, then no check of role membership is
+ * performed as the GRANT system is expected to have been used to manage access
+ * to calling the function which called us.
  *
  * Returns 0 on success, 1 on general failure, 2 on normal permission error
  * and 3 if the caller needs to be a superuser.
@@ -94,7 +102,7 @@ current_query(PG_FUNCTION_ARGS)
 #define SIGNAL_BACKEND_NOPERMISSION 2
 #define SIGNAL_BACKEND_NOSUPERUSER 3
 static int
-pg_signal_backend(int pid, int sig)
+pg_signal_backend_helper(int pid, int sig, bool perm_check)
 {
 	PGPROC	   *proc = BackendPidGetProc(pid);
 
@@ -122,7 +130,7 @@ 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 (perm_check && !has_privs_of_role(GetUserId(), proc->roleId))
 		return SIGNAL_BACKEND_NOPERMISSION;
 
 	/*
@@ -150,6 +158,41 @@ pg_signal_backend(int pid, int sig)
 }
 
 /*
+ * Signal a backend process.  Permissions for this are managed by the GRANT
+ * system and therefore we do not do any extra permissions checks through
+ * this path.
+ *
+ * Note that only superusers can signal superuser-owned processes.
+ */
+Datum
+pg_signal_backend(PG_FUNCTION_ARGS)
+{
+	int			backend = PG_GETARG_INT32(0);
+	int			signal = PG_GETARG_INT32(1);
+	int			r;
+
+	/*
+	 * We only allow "safe" signals to be used through this, unless the user
+	 * is a superuser.
+	 */
+	if (!superuser() && signal != SIGINT && signal != SIGTERM)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be a superuser to send signals other than SIGINT and SIGTERM"))));
+
+	r = pg_signal_backend_helper(backend, signal, false);
+
+	if (r == SIGNAL_BACKEND_NOSUPERUSER)
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be a superuser to cancel superuser query"))));
+
+	Assert (r != SIGNAL_BACKEND_NOPERMISSION);
+
+	PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
+}
+
+/*
  * Signal to cancel a backend process.  This is allowed if you are a member of
  * the role whose process is being canceled.
  *
@@ -158,7 +201,7 @@ pg_signal_backend(int pid, int sig)
 Datum
 pg_cancel_backend(PG_FUNCTION_ARGS)
 {
-	int			r = pg_signal_backend(PG_GETARG_INT32(0), SIGINT);
+	int			r = pg_signal_backend_helper(PG_GETARG_INT32(0), SIGINT, true);
 
 	if (r == SIGNAL_BACKEND_NOSUPERUSER)
 		ereport(ERROR,
@@ -182,7 +225,7 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
 Datum
 pg_terminate_backend(PG_FUNCTION_ARGS)
 {
-	int			r = pg_signal_backend(PG_GETARG_INT32(0), SIGTERM);
+	int			r = pg_signal_backend_helper(PG_GETARG_INT32(0), SIGTERM, true);
 
 	if (r == SIGNAL_BACKEND_NOSUPERUSER)
 		ereport(ERROR,
@@ -225,11 +268,6 @@ pg_reload_conf(PG_FUNCTION_ARGS)
 Datum
 pg_rotate_logfile(PG_FUNCTION_ARGS)
 {
-	if (!superuser())
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to rotate log files"))));
-
 	if (!Logging_collector)
 	{
 		ereport(WARNING,
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2b3778b..fdf11be 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -53,6 +53,7 @@ extern Datum pg_stat_get_function_self_time(PG_FUNCTION_ARGS);
 
 extern Datum pg_stat_get_backend_idset(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_activity(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_activity_all(PG_FUNCTION_ARGS);
 extern Datum pg_backend_pid(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_pid(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_backend_dbid(PG_FUNCTION_ARGS);
@@ -126,6 +127,8 @@ extern Datum pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS);
 /* Global bgwriter statistics, from bgwriter.c */
 extern PgStat_MsgBgWriter bgwriterStats;
 
+static void populate_pg_stat_get_activity(TupleDesc tupdesc, Tuplestorestate *tupstore, int pid, Oid calling_user);
+
 Datum
 pg_stat_get_numscans(PG_FUNCTION_ARGS)
 {
@@ -525,14 +528,12 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
 }
 
 /*
- * Returns activity of PG backends.
+ * Returns activity of PG backends, filtered based on the PID passed in and on
+ * the rights of the calling user, as anyone can call this function.
  */
 Datum
 pg_stat_get_activity(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_ACTIVITY_COLS	22
-	int					num_backends = pgstat_fetch_stat_numbackends();
-	int					curr_backend;
 	int					pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
 	ReturnSetInfo	   *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
 	TupleDesc			tupdesc;
@@ -565,6 +566,92 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 
 	MemoryContextSwitchTo(oldcontext);
 
+	/*
+	 * Populate the tuple store based on the pid passed in (if any) and the
+	 * rights of the calling user.  This is necessary because this function can
+	 * be executed by any user and therefore the results need to be filtered to
+	 * only what the calling user is allowed to see.
+	 */
+	populate_pg_stat_get_activity(tupdesc, tupstore, pid, GetUserId());
+
+	/* clean up and return the tuplestore */
+	tuplestore_donestoring(tupstore);
+
+	return (Datum) 0;
+}
+
+/*
+ * Returns activity of PG backends, filtered based on the PID passed in, if
+ * any, but *not* filtered based on the calling user.  This function should
+ * NOT be made publically available as the information returned is sensitive.
+ *
+ * This is handled by the ACLs on the function (the normal GRANT system).
+ */
+Datum
+pg_stat_get_activity_all(PG_FUNCTION_ARGS)
+{
+	int					pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
+	ReturnSetInfo	   *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	TupleDesc			tupdesc;
+	Tuplestorestate	   *tupstore;
+	MemoryContext		per_query_ctx;
+	MemoryContext		oldcontext;
+
+	/* check to see if caller supports us returning a tuplestore */
+	if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("set-valued function called in context that cannot accept a set")));
+	if (!(rsinfo->allowedModes & SFRM_Materialize))
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("materialize mode required, but it is not " \
+						"allowed in this context")));
+
+	/* Build a tuple descriptor for our result type */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
+	oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+	tupstore = tuplestore_begin_heap(true, false, work_mem);
+	rsinfo->returnMode = SFRM_Materialize;
+	rsinfo->setResult = tupstore;
+	rsinfo->setDesc = tupdesc;
+
+	MemoryContextSwitchTo(oldcontext);
+
+	/*
+	 * Populate the tuple store based on the pid passed in (if any).  We pass
+	 * InvalidOid for the calling user as the results should NOT be filtered.
+	 */
+	populate_pg_stat_get_activity(tupdesc, tupstore, pid, InvalidOid);
+
+	/* clean up and return the tuplestore */
+	tuplestore_donestoring(tupstore);
+
+	return (Datum) 0;
+}
+
+/*
+ * Returns activity of PG backends.
+ *
+ * If pid is -1 then activity for all backends is returned, otherwise just the
+ * activity of the backend with the specified pid is returned.
+ *
+ * If calling_user is InvalidOid then all information is returned, otherwise the
+ * information for all backends which are members of the same role as the
+ * calling_user is returned and the information about other backends is limited
+ * to only public information.
+ */
+static void
+populate_pg_stat_get_activity(TupleDesc tupdesc, Tuplestorestate *tupstore, int pid, Oid calling_user)
+{
+#define PG_STAT_GET_ACTIVITY_COLS	22
+	int					num_backends = pgstat_fetch_stat_numbackends();
+	int					curr_backend;
+
 	/* 1-based index */
 	for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
 	{
@@ -642,7 +729,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 		}
 
 		/* Values only available to role member */
-		if (has_privs_of_role(GetUserId(), beentry->st_userid))
+		if (calling_user == InvalidOid ||
+			has_privs_of_role(calling_user, beentry->st_userid))
 		{
 			SockAddr	zero_clientaddr;
 
@@ -784,10 +872,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			break;
 	}
 
-	/* clean up and return the tuplestore */
-	tuplestore_donestoring(tupstore);
-
-	return (Datum) 0;
+	return;
 }
 
 
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
index b0d85af..503fd2e 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -432,24 +432,6 @@ SetUserIdAndContext(Oid userid, bool sec_def_context)
 
 
 /*
- * Check whether specified role has explicit REPLICATION privilege
- */
-bool
-has_rolreplication(Oid roleid)
-{
-	bool		result = false;
-	HeapTuple	utup;
-
-	utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid));
-	if (HeapTupleIsValid(utup))
-	{
-		result = ((Form_pg_authid) GETSTRUCT(utup))->rolreplication;
-		ReleaseSysCache(utup);
-	}
-	return result;
-}
-
-/*
  * Initialize user identity during normal backend startup
  */
 void
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index debadf0..965a1ab 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -765,7 +765,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
 	{
 		Assert(!bootstrap);
 
-		if (!superuser() && !has_rolreplication(GetUserId()))
+		if (!is_member_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
 			ereport(FATAL,
 					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 					 errmsg("must be superuser or replication role to start walsender")));
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 7169ad0..32488b9 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -674,10 +674,11 @@ dumpRoles(PGconn *conn)
 						  "SELECT oid, rolname, rolsuper, rolinherit, "
 						  "rolcreaterole, rolcreatedb, "
 						  "rolcanlogin, rolconnlimit, rolpassword, "
-						  "rolvaliduntil, rolreplication, rolbypassrls, "
+						  "rolvaliduntil, rolbypassrls, "
 			 "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,
@@ -759,7 +760,8 @@ dumpRoles(PGconn *conn)
 	i_rolconnlimit = PQfnumber(res, "rolconnlimit");
 	i_rolpassword = PQfnumber(res, "rolpassword");
 	i_rolvaliduntil = PQfnumber(res, "rolvaliduntil");
-	i_rolreplication = PQfnumber(res, "rolreplication");
+	if (server_version < 90500)
+		i_rolreplication = PQfnumber(res, "rolreplication");
 	i_rolbypassrls = PQfnumber(res, "rolbypassrls");
 	i_rolcomment = PQfnumber(res, "rolcomment");
 	i_is_current_user = PQfnumber(res, "is_current_user");
@@ -823,10 +825,13 @@ dumpRoles(PGconn *conn)
 		else
 			appendPQExpBufferStr(buf, " NOLOGIN");
 
-		if (strcmp(PQgetvalue(res, i, i_rolreplication), "t") == 0)
-			appendPQExpBufferStr(buf, " REPLICATION");
-		else
-			appendPQExpBufferStr(buf, " NOREPLICATION");
+		if (server_version < 90500)
+		{
+			if (strcmp(PQgetvalue(res, i, i_rolreplication), "t") == 0)
+				appendPQExpBufferStr(buf, " REPLICATION");
+			else
+				appendPQExpBufferStr(buf, " NOREPLICATION");
+		}
 
 		if (strcmp(PQgetvalue(res, i, i_rolbypassrls), "t") == 0)
 			appendPQExpBufferStr(buf, " BYPASSRLS");
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 6db223a..f117840 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -23,6 +23,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.4 and below should not have roles starting with pg_ */
+	if (GET_MAJOR_VERSION(old_cluster.major_version) <= 904)
+		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);
@@ -603,7 +609,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)
@@ -619,7 +626,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");
@@ -952,6 +960,34 @@ check_for_jsonb_9_4_usage(ClusterInfo *cluster)
 		check_ok();
 }
 
+/*
+ * check_for_pg_role_prefix()
+ *
+ *	Versions older than 9.5 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/describe.c b/src/bin/psql/describe.c
index 04d769e..0415b3a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2672,7 +2672,7 @@ describeRoles(const char *pattern, bool verbose)
 			appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
 			ncols++;
 		}
-		if (pset.sversion >= 90100)
+		if (pset.sversion >= 90100 && pset.sversion < 90500)
 		{
 			appendPQExpBufferStr(&buf, "\n, r.rolreplication");
 		}
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 750e29d..502346d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1219,8 +1219,8 @@ psql_completion(const char *text, int start, int end)
 		{"BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
 			"CREATEUSER", "ENCRYPTED", "INHERIT", "LOGIN", "NOBYPASSRLS",
 			"NOCREATEDB", "NOCREATEROLE", "NOCREATEUSER", "NOINHERIT",
-			"NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "RENAME TO",
-			"REPLICATION", "RESET", "SET", "SUPERUSER", "UNENCRYPTED",
+			"NOLOGIN", "NOSUPERUSER", "RENAME TO", "RESET", "SET",
+			"SUPERUSER", "UNENCRYPTED",
 		"VALID UNTIL", "WITH", NULL};
 
 		COMPLETE_WITH_LIST(list_ALTERUSER);
@@ -1237,8 +1237,8 @@ psql_completion(const char *text, int start, int end)
 		{"BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
 			"CREATEUSER", "ENCRYPTED", "INHERIT", "LOGIN", "NOBYPASSRLS",
 			"NOCREATEDB", "NOCREATEROLE", "NOCREATEUSER", "NOINHERIT",
-			"NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "RENAME TO",
-			"REPLICATION", "RESET", "SET", "SUPERUSER", "UNENCRYPTED",
+			"NOLOGIN", "NOSUPERUSER", "RENAME TO", "RESET", "SET",
+			"SUPERUSER", "UNENCRYPTED",
 		"VALID UNTIL", NULL};
 
 		COMPLETE_WITH_LIST(list_ALTERUSER_WITH);
@@ -2576,8 +2576,8 @@ psql_completion(const char *text, int start, int end)
 		{"ADMIN", "BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
 			"CREATEUSER", "ENCRYPTED", "IN", "INHERIT", "LOGIN", "NOBYPASSRLS",
 			"NOCREATEDB", "NOCREATEROLE", "NOCREATEUSER", "NOINHERIT",
-			"NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "REPLICATION", "ROLE",
-		"SUPERUSER", "SYSID", "UNENCRYPTED", "VALID UNTIL", "WITH", NULL};
+			"NOLOGIN", "NOSUPERUSER", "ROLE", "SUPERUSER", "SYSID",
+			"UNENCRYPTED", "VALID UNTIL", "WITH", NULL};
 
 		COMPLETE_WITH_LIST(list_CREATEROLE);
 	}
@@ -2594,8 +2594,8 @@ psql_completion(const char *text, int start, int end)
 		{"ADMIN", "BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
 			"CREATEUSER", "ENCRYPTED", "IN", "INHERIT", "LOGIN", "NOBYPASSRLS",
 			"NOCREATEDB", "NOCREATEROLE", "NOCREATEUSER", "NOINHERIT",
-			"NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "REPLICATION", "ROLE",
-		"SUPERUSER", "SYSID", "UNENCRYPTED", "VALID UNTIL", NULL};
+			"NOLOGIN", "NOSUPERUSER", "ROLE", "SUPERUSER", "SYSID",
+			"UNENCRYPTED", "VALID UNTIL", NULL};
 
 		COMPLETE_WITH_LIST(list_CREATEROLE_WITH);
 	}
diff --git a/src/bin/scripts/createuser.c b/src/bin/scripts/createuser.c
index fba21a1..5de2e8d 100644
--- a/src/bin/scripts/createuser.c
+++ b/src/bin/scripts/createuser.c
@@ -38,8 +38,6 @@ main(int argc, char *argv[])
 		{"no-inherit", no_argument, NULL, 'I'},
 		{"login", no_argument, NULL, 'l'},
 		{"no-login", no_argument, NULL, 'L'},
-		{"replication", no_argument, NULL, 1},
-		{"no-replication", no_argument, NULL, 2},
 		{"interactive", no_argument, NULL, 3},
 		/* adduser is obsolete, undocumented spelling of superuser */
 		{"adduser", no_argument, NULL, 'a'},
@@ -72,7 +70,6 @@ main(int argc, char *argv[])
 				createrole = TRI_DEFAULT,
 				inherit = TRI_DEFAULT,
 				login = TRI_DEFAULT,
-				replication = TRI_DEFAULT,
 				encrypted = TRI_DEFAULT;
 
 	PQExpBufferData sql;
@@ -155,12 +152,6 @@ main(int argc, char *argv[])
 			case 'N':
 				encrypted = TRI_NO;
 				break;
-			case 1:
-				replication = TRI_YES;
-				break;
-			case 2:
-				replication = TRI_NO;
-				break;
 			case 3:
 				interactive = true;
 				break;
@@ -301,10 +292,6 @@ main(int argc, char *argv[])
 		appendPQExpBufferStr(&sql, " LOGIN");
 	if (login == TRI_NO)
 		appendPQExpBufferStr(&sql, " NOLOGIN");
-	if (replication == TRI_YES)
-		appendPQExpBufferStr(&sql, " REPLICATION");
-	if (replication == TRI_NO)
-		appendPQExpBufferStr(&sql, " NOREPLICATION");
 	if (conn_limit != NULL)
 		appendPQExpBuffer(&sql, " CONNECTION LIMIT %s", conn_limit);
 	if (roles.head != NULL)
@@ -368,8 +355,6 @@ help(const char *progname)
 	printf(_("  -V, --version             output version information, then exit\n"));
 	printf(_("  --interactive             prompt for missing role name and attributes rather\n"
 			 "                            than using defaults\n"));
-	printf(_("  --replication             role can initiate replication\n"));
-	printf(_("  --no-replication          role cannot initiate replication\n"));
 	printf(_("  -?, --help                show this help, then exit\n"));
 	printf(_("\nConnection options:\n"));
 	printf(_("  -h, --host=HOSTNAME       database server host or socket directory\n"));
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index d5f19d6..0c7f25f 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -50,7 +50,6 @@ CATALOG(pg_authid,1260) BKI_SHARED_RELATION BKI_ROWTYPE_OID(2842) BKI_SCHEMA_MAC
 	bool		rolcreaterole;	/* allowed to create more roles? */
 	bool		rolcreatedb;	/* allowed to create databases? */
 	bool		rolcanlogin;	/* allowed to log in as session user? */
-	bool		rolreplication; /* role used for streaming replication */
 	bool		rolbypassrls;	/* allowed to bypass row level security? */
 	int32		rolconnlimit;	/* max connections allowed (-1=no limit) */
 
@@ -75,18 +74,17 @@ typedef FormData_pg_authid *Form_pg_authid;
  *		compiler constants for pg_authid
  * ----------------
  */
-#define Natts_pg_authid					11
+#define Natts_pg_authid					10
 #define Anum_pg_authid_rolname			1
 #define Anum_pg_authid_rolsuper			2
 #define Anum_pg_authid_rolinherit		3
 #define Anum_pg_authid_rolcreaterole	4
 #define Anum_pg_authid_rolcreatedb		5
 #define Anum_pg_authid_rolcanlogin		6
-#define Anum_pg_authid_rolreplication	7
-#define Anum_pg_authid_rolbypassrls		8
-#define Anum_pg_authid_rolconnlimit		9
-#define Anum_pg_authid_rolpassword		10
-#define Anum_pg_authid_rolvaliduntil	11
+#define Anum_pg_authid_rolbypassrls		7
+#define Anum_pg_authid_rolconnlimit		8
+#define Anum_pg_authid_rolpassword		9
+#define Anum_pg_authid_rolvaliduntil	10
 
 /* ----------------
  *		initial contents of pg_authid
@@ -95,8 +93,23 @@ typedef FormData_pg_authid *Form_pg_authid;
  * user choices.
  * ----------------
  */
-DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_ _null_));
+DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_));
+DATA(insert OID = 3287 ( "pg_monitor" f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3288 ( "pg_backup" f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3289 ( "pg_replay" f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3290 ( "pg_replication" f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3291 ( "pg_rotate_logfile" f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3292 ( "pg_signal_backend" f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3293 ( "pg_admin" f f f f f f -1 _null_ _null_));
 
-#define BOOTSTRAP_SUPERUSERID 10
+#define BOOTSTRAP_SUPERUSERID			10
+
+#define DEFAULT_ROLE_MONITORID			3287
+#define DEFAULT_ROLE_BACKUPID			3288
+#define DEFAULT_ROLE_REPLAYID			3289
+#define DEFAULT_ROLE_REPLICATIONID		3290
+#define DEFAULT_ROLE_ROTATE_LOGFILEID	3291
+#define DEFAULT_ROLE_SIGNAL_BACKENDID	3292
+#define DEFAULT_ROLE_ADMINID			3293
 
 #endif   /* PG_AUTHID_H */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 5fa65d6..e7d42a0 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2768,8 +2768,12 @@ DATA(insert OID = 1936 (  pg_stat_get_backend_idset		PGNSP PGUID 12 1 100 0 0 f
 DESCR("statistics: currently active backend IDs");
 DATA(insert OID = 2022 (  pg_stat_get_activity			PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23,28,28,16,25,25,23,16,25}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,ssl,sslversion,sslcipher,sslbits,sslcompression,sslclientdn}" _null_ _null_ pg_stat_get_activity _null_ _null_ _null_ ));
 DESCR("statistics: information about currently active backends");
+DATA(insert OID = 3286 (  pg_stat_get_activity_all			PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23,28,28,16,25,25,23,16,25}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,ssl,sslversion,sslcipher,sslbits,sslcompression,sslclientdn}" _null_ _null_ pg_stat_get_activity _null_ _null_ _null_ ));
+DESCR("statistics: information about currently active backends, unfiltered");
 DATA(insert OID = 3099 (  pg_stat_get_wal_senders	PGNSP PGUID 12 1 10 0 0 f f f f f t s 0 0 2249 "" "{23,25,3220,3220,3220,3220,23,25}" "{o,o,o,o,o,o,o,o}" "{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}" _null_ _null_ pg_stat_get_wal_senders _null_ _null_ _null_ ));
 DESCR("statistics: information about currently active replication");
+DATA(insert OID = 3285 (  pg_stat_get_wal_senders_all	PGNSP PGUID 12 1 10 0 0 f f f f f t s 0 0 2249 "" "{23,25,3220,3220,3220,3220,23,25}" "{o,o,o,o,o,o,o,o}" "{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}" _null_ _null_ pg_stat_get_wal_senders _null_ _null_ _null_ ));
+DESCR("statistics: information about currently active replication, unfiltered");
 DATA(insert OID = 2026 (  pg_backend_pid				PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 23 "" _null_ _null_ _null_ _null_ _null_ pg_backend_pid _null_ _null_ _null_ ));
 DESCR("statistics: current backend PID");
 DATA(insert OID = 1937 (  pg_stat_get_backend_pid		PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 23 "23" _null_ _null_ _null_ _null_ _null_ pg_stat_get_backend_pid _null_ _null_ _null_ ));
@@ -3121,6 +3125,8 @@ DATA(insert OID = 2171 ( pg_cancel_backend		PGNSP PGUID 12 1 0 0 0 f f f f t f v
 DESCR("cancel a server process' current query");
 DATA(insert OID = 2096 ( pg_terminate_backend		PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 16 "23" _null_ _null_ _null_ _null_ _null_ pg_terminate_backend _null_ _null_ _null_ ));
 DESCR("terminate a server process");
+DATA(insert OID = 3284 ( pg_signal_backend		PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 16 "23 23" _null_ _null_ _null_ _null_ _null_ pg_signal_backend _null_ _null_ _null_ ));
+DESCR("signal a server process");
 DATA(insert OID = 2172 ( pg_start_backup		PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 3220 "25 16" _null_ _null_ _null_ _null_ _null_ pg_start_backup _null_ _null_ _null_ ));
 DESCR("prepare for taking an online backup");
 DATA(insert OID = 2173 ( pg_stop_backup			PGNSP PGUID 12 1 0 0 0 f f f f t f v 0 0 3220 "" _null_ _null_ _null_ _null_ _null_ pg_stop_backup _null_ _null_ _null_ ));
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 71aa505..f0ce2d5 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -455,7 +455,6 @@ extern void ValidatePgVersion(const char *path);
 extern void process_shared_preload_libraries(void);
 extern void process_session_preload_libraries(void);
 extern void pg_bindtextdomain(const char *domain);
-extern bool has_rolreplication(Oid roleid);
 
 /* in access/transam/xlog.c */
 extern bool BackupInProgress(void);
diff --git a/src/include/replication/walsender.h b/src/include/replication/walsender.h
index b10e784..e458621 100644
--- a/src/include/replication/walsender.h
+++ b/src/include/replication/walsender.h
@@ -37,6 +37,7 @@ extern void WalSndWakeup(void);
 extern void WalSndRqstFileReload(void);
 
 extern Datum pg_stat_get_wal_senders(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_wal_senders_all(PG_FUNCTION_ARGS);
 
 /*
  * Remember that we want to wakeup walsenders later
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index a90bfe2..01a8eaf 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -482,6 +482,7 @@ extern Datum pg_ls_dir(PG_FUNCTION_ARGS);
 extern Datum current_database(PG_FUNCTION_ARGS);
 extern Datum current_query(PG_FUNCTION_ARGS);
 extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
+extern Datum pg_signal_backend(PG_FUNCTION_ARGS);
 extern Datum pg_terminate_backend(PG_FUNCTION_ARGS);
 extern Datum pg_reload_conf(PG_FUNCTION_ARGS);
 extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out
index 8f88c02..d32141d 100644
--- a/src/test/regress/expected/rolenames.out
+++ b/src/test/regress/expected/rolenames.out
@@ -1,7 +1,7 @@
 CREATE OR REPLACE FUNCTION chkrolattr()
- RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool)
+ RETURNS TABLE ("role" name, rolekeyword text, canlogin bool)
  AS $$
-SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication
+SELECT r.rolname, v.keyword, r.rolcanlogin
  FROM pg_roles r
  JOIN (VALUES(CURRENT_USER, 'current_user'),
              (SESSION_USER, 'session_user'),
@@ -88,88 +88,14 @@ SET ROLE testrol2;
 --  ALTER ROLE
 BEGIN;
 SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | f
- session_user | -            | f        | f
- testrol1     | session_user | t        | f
- testrol2     | current_user | f        | f
-(6 rows)
-
-ALTER ROLE CURRENT_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | f
- session_user | -            | f        | f
- testrol1     | session_user | t        | f
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER ROLE "current_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | t
- session_user | -            | f        | f
- testrol1     | session_user | t        | f
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER ROLE SESSION_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | t
- session_user | -            | f        | f
- testrol1     | session_user | t        | t
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER ROLE "session_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | t
- session_user | -            | f        | t
- testrol1     | session_user | t        | t
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER USER "Public" WITH REPLICATION;
-ALTER USER "None" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | t
- Public       | -            | f        | t
- current_user | -            | f        | t
- session_user | -            | f        | t
- testrol1     | session_user | t        | t
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER USER testrol1 WITH NOREPLICATION;
-ALTER USER testrol2 WITH NOREPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | t
- Public       | -            | f        | t
- current_user | -            | f        | t
- session_user | -            | f        | t
- testrol1     | session_user | t        | f
- testrol2     | current_user | f        | f
+     role     | rolekeyword  | canlogin 
+--------------+--------------+----------
+ None         | -            | f
+ Public       | -            | f
+ current_user | -            | f
+ session_user | -            | f
+ testrol1     | session_user | t
+ testrol2     | current_user | f
 (6 rows)
 
 ROLLBACK;
@@ -181,111 +107,17 @@ ALTER ROLE CURRENT_ROLE WITH LOGIN; --error
 ERROR:  syntax error at or near "CURRENT_ROLE"
 LINE 1: ALTER ROLE CURRENT_ROLE WITH LOGIN;
                    ^
-ALTER ROLE ALL WITH REPLICATION; -- error
-ERROR:  syntax error at or near "WITH"
-LINE 1: ALTER ROLE ALL WITH REPLICATION;
-                       ^
-ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error
-ERROR:  role "session_role" does not exist
-ALTER ROLE PUBLIC WITH NOREPLICATION; -- error
-ERROR:  role "public" does not exist
-ALTER ROLE "public" WITH NOREPLICATION; -- error
-ERROR:  role "public" does not exist
-ALTER ROLE NONE WITH NOREPLICATION; -- error
-ERROR:  role name "none" is reserved
-LINE 1: ALTER ROLE NONE WITH NOREPLICATION;
-                   ^
-ALTER ROLE "none" WITH NOREPLICATION; -- error
-ERROR:  role name "none" is reserved
-LINE 1: ALTER ROLE "none" WITH NOREPLICATION;
-                   ^
-ALTER ROLE nonexistent WITH NOREPLICATION; -- error
-ERROR:  role "nonexistent" does not exist
 --  ALTER USER
 BEGIN;
 SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | f
- session_user | -            | f        | f
- testrol1     | session_user | t        | f
- testrol2     | current_user | f        | f
-(6 rows)
-
-ALTER USER CURRENT_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | f
- session_user | -            | f        | f
- testrol1     | session_user | t        | f
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER USER "current_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | t
- session_user | -            | f        | f
- testrol1     | session_user | t        | f
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER USER SESSION_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | t
- session_user | -            | f        | f
- testrol1     | session_user | t        | t
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER USER "session_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | f
- Public       | -            | f        | f
- current_user | -            | f        | t
- session_user | -            | f        | t
- testrol1     | session_user | t        | t
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER USER "Public" WITH REPLICATION;
-ALTER USER "None" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | t
- Public       | -            | f        | t
- current_user | -            | f        | t
- session_user | -            | f        | t
- testrol1     | session_user | t        | t
- testrol2     | current_user | f        | t
-(6 rows)
-
-ALTER USER testrol1 WITH NOREPLICATION;
-ALTER USER testrol2 WITH NOREPLICATION;
-SELECT * FROM chkrolattr();
-     role     | rolekeyword  | canlogin | replication 
---------------+--------------+----------+-------------
- None         | -            | f        | t
- Public       | -            | f        | t
- current_user | -            | f        | t
- session_user | -            | f        | t
- testrol1     | session_user | t        | f
- testrol2     | current_user | f        | f
+     role     | rolekeyword  | canlogin 
+--------------+--------------+----------
+ None         | -            | f
+ Public       | -            | f
+ current_user | -            | f
+ session_user | -            | f
+ testrol1     | session_user | t
+ testrol2     | current_user | f
 (6 rows)
 
 ROLLBACK;
@@ -297,26 +129,6 @@ ALTER USER CURRENT_ROLE WITH LOGIN; -- error
 ERROR:  syntax error at or near "CURRENT_ROLE"
 LINE 1: ALTER USER CURRENT_ROLE WITH LOGIN;
                    ^
-ALTER USER ALL WITH REPLICATION; -- error
-ERROR:  syntax error at or near "ALL"
-LINE 1: ALTER USER ALL WITH REPLICATION;
-                   ^
-ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error
-ERROR:  role "session_role" does not exist
-ALTER USER PUBLIC WITH NOREPLICATION; -- error
-ERROR:  role "public" does not exist
-ALTER USER "public" WITH NOREPLICATION; -- error
-ERROR:  role "public" does not exist
-ALTER USER NONE WITH NOREPLICATION; -- error
-ERROR:  role name "none" is reserved
-LINE 1: ALTER USER NONE WITH NOREPLICATION;
-                   ^
-ALTER USER "none" WITH NOREPLICATION; -- error
-ERROR:  role name "none" is reserved
-LINE 1: ALTER USER "none" WITH NOREPLICATION;
-                   ^
-ALTER USER nonexistent WITH NOREPLICATION; -- error
-ERROR:  role "nonexistent" does not exist
 --  ALTER ROLE SET/RESET
 SELECT * FROM chksetconfig();
  db | role | rolkeyword | setconfig 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a379a72..ce2b725 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1423,7 +1423,6 @@ pg_roles| SELECT pg_authid.rolname,
     pg_authid.rolcreaterole,
     pg_authid.rolcreatedb,
     pg_authid.rolcanlogin,
-    pg_authid.rolreplication,
     pg_authid.rolconnlimit,
     '********'::text AS rolpassword,
     pg_authid.rolvaliduntil,
@@ -1622,7 +1621,6 @@ pg_shadow| SELECT pg_authid.rolname AS usename,
     pg_authid.oid AS usesysid,
     pg_authid.rolcreatedb AS usecreatedb,
     pg_authid.rolsuper AS usesuper,
-    pg_authid.rolreplication AS userepl,
     pg_authid.rolbypassrls AS usebypassrls,
     pg_authid.rolpassword AS passwd,
     (pg_authid.rolvaliduntil)::abstime AS valuntil,
@@ -1652,6 +1650,28 @@ pg_stat_activity| SELECT s.datid,
     pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn),
     pg_authid u
   WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
+pg_stat_activity_all| SELECT s.datid,
+    d.datname,
+    s.pid,
+    s.usesysid,
+    u.rolname AS usename,
+    s.application_name,
+    s.client_addr,
+    s.client_hostname,
+    s.client_port,
+    s.backend_start,
+    s.xact_start,
+    s.query_start,
+    s.state_change,
+    s.waiting,
+    s.state,
+    s.backend_xid,
+    s.backend_xmin,
+    s.query
+   FROM pg_database d,
+    pg_stat_get_activity_all(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn),
+    pg_authid u
+  WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
 pg_stat_all_indexes| SELECT c.oid AS relid,
     i.oid AS indexrelid,
     n.nspname AS schemaname,
@@ -1759,6 +1779,26 @@ pg_stat_replication| SELECT s.pid,
     pg_authid u,
     pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
   WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
+pg_stat_replication_all| SELECT s.pid,
+    s.usesysid,
+    u.rolname AS usename,
+    s.application_name,
+    s.client_addr,
+    s.client_hostname,
+    s.client_port,
+    s.backend_start,
+    s.backend_xmin,
+    w.state,
+    w.sent_location,
+    w.write_location,
+    w.flush_location,
+    w.replay_location,
+    w.sync_priority,
+    w.sync_state
+   FROM pg_stat_get_activity_all(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn),
+    pg_authid u,
+    pg_stat_get_wal_senders_all() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
+  WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
 pg_stat_ssl| SELECT s.pid,
     s.ssl,
     s.sslversion AS version,
@@ -2084,7 +2124,6 @@ pg_user| SELECT pg_shadow.usename,
     pg_shadow.usesysid,
     pg_shadow.usecreatedb,
     pg_shadow.usesuper,
-    pg_shadow.userepl,
     pg_shadow.usebypassrls,
     '********'::text AS passwd,
     pg_shadow.valuntil,
diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql
index e8c6b33..74e9ec1 100644
--- a/src/test/regress/sql/rolenames.sql
+++ b/src/test/regress/sql/rolenames.sql
@@ -1,7 +1,7 @@
 CREATE OR REPLACE FUNCTION chkrolattr()
- RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool)
+ RETURNS TABLE ("role" name, rolekeyword text, canlogin bool)
  AS $$
-SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication
+SELECT r.rolname, v.keyword, r.rolcanlogin
  FROM pg_roles r
  JOIN (VALUES(CURRENT_USER, 'current_user'),
              (SESSION_USER, 'session_user'),
@@ -69,60 +69,18 @@ SET ROLE testrol2;
 --  ALTER ROLE
 BEGIN;
 SELECT * FROM chkrolattr();
-ALTER ROLE CURRENT_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER ROLE "current_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER ROLE SESSION_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER ROLE "session_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER "Public" WITH REPLICATION;
-ALTER USER "None" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER testrol1 WITH NOREPLICATION;
-ALTER USER testrol2 WITH NOREPLICATION;
-SELECT * FROM chkrolattr();
 ROLLBACK;
 
 ALTER ROLE USER WITH LOGIN; -- error
 ALTER ROLE CURRENT_ROLE WITH LOGIN; --error
-ALTER ROLE ALL WITH REPLICATION; -- error
-ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error
-ALTER ROLE PUBLIC WITH NOREPLICATION; -- error
-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 USER
 BEGIN;
 SELECT * FROM chkrolattr();
-ALTER USER CURRENT_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER "current_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER SESSION_USER WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER "session_user" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER "Public" WITH REPLICATION;
-ALTER USER "None" WITH REPLICATION;
-SELECT * FROM chkrolattr();
-ALTER USER testrol1 WITH NOREPLICATION;
-ALTER USER testrol2 WITH NOREPLICATION;
-SELECT * FROM chkrolattr();
 ROLLBACK;
 
 ALTER USER USER WITH LOGIN; -- error
 ALTER USER CURRENT_ROLE WITH LOGIN; -- error
-ALTER USER ALL WITH REPLICATION; -- error
-ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error
-ALTER USER PUBLIC WITH NOREPLICATION; -- error
-ALTER USER "public" WITH NOREPLICATION; -- error
-ALTER USER NONE WITH NOREPLICATION; -- error
-ALTER USER "none" WITH NOREPLICATION; -- error
-ALTER USER nonexistent WITH NOREPLICATION; -- error
 
 --  ALTER ROLE SET/RESET
 SELECT * FROM chksetconfig();
-- 
1.9.1

From 698a74ea2b627bf1a75babe177817da8dfcae464 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Thu, 7 May 2015 23:35:03 -0400
Subject: [PATCH] Create default roles for administrative functions

To reduce the number of users on a system who are superusers,
create a set of roles by default during initdb which are allowed to run
certain functions that allow non-superusers to perform specific
administrative tasks and have access to privileged information.

The prefix "pg_" is reserved for default system roles, similar to
schemas and tablespaces.  pg_upgrade is modified to check for any roles
which start with "pg_" and complain if they exist.  pg_dumpall is
modified to not dump out roles starting with "pg_" on 9.5-and-above
systems.  CreateRole is modified to refuse creation of roles which start
with "pg_", similar to CreateSchema.

Roles created are: pg_backup, pg_monitor, pg_replay, pg_replication,
pg_rotate_logfile, pg_signal_backend, pg_file_settings and pg_admin.

The XLOG location information functions now requires the pg_monitor role
as the compression rate can be used to derive sensitive information.
---
 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         | 50 ++++++++++----
 src/backend/catalog/catalog.c                  |  5 +-
 src/backend/catalog/system_views.sql           |  6 +-
 src/backend/commands/user.c                    | 13 +++-
 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/bin/pg_dump/pg_dumpall.c                   |  2 +
 src/bin/pg_upgrade/check.c                     | 40 ++++++++++-
 src/include/catalog/pg_authid.h                | 19 +++++-
 16 files changed, 277 insertions(+), 64 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 2756652..7a3ca1e 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 177ac7a..1fd4fcc 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..1176f6f 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,
@@ -183,6 +189,11 @@ pg_current_xlog_location(PG_FUNCTION_ARGS)
 {
 	XLogRecPtr	current_recptr;
 
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must be superuser or member of pg_monitor to see xlog location")));
+
 	if (RecoveryInProgress())
 		ereport(ERROR,
 				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -204,6 +215,11 @@ pg_current_xlog_insert_location(PG_FUNCTION_ARGS)
 {
 	XLogRecPtr	current_recptr;
 
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must be superuser or member of pg_monitor to see xlog location")));
+
 	if (RecoveryInProgress())
 		ereport(ERROR,
 				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -226,6 +242,11 @@ pg_last_xlog_receive_location(PG_FUNCTION_ARGS)
 {
 	XLogRecPtr	recptr;
 
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must be superuser or member of pg_monitor to see xlog location")));
+
 	recptr = GetWalRcvWriteRecPtr(NULL, NULL);
 
 	if (recptr == 0)
@@ -245,6 +266,11 @@ pg_last_xlog_replay_location(PG_FUNCTION_ARGS)
 {
 	XLogRecPtr	recptr;
 
+	if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("must be superuser or member of pg_monitor to see xlog location")));
+
 	recptr = GetXLogReplayRecPtr(NULL);
 
 	if (recptr == 0)
@@ -348,10 +374,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 +396,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/catalog/catalog.c b/src/backend/catalog/catalog.c
index fa2aa27..9c3b760 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.5, this is also true
+ *		for roles.
  */
 bool
 IsReservedName(const char *name)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 18921c4..de1ed77 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -414,9 +414,6 @@ GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
 CREATE VIEW pg_file_settings AS
    SELECT * FROM pg_show_all_file_settings() AS A;
 
-REVOKE ALL on pg_file_settings FROM PUBLIC;
-REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC;
-
 CREATE VIEW pg_timezone_abbrevs AS
     SELECT * FROM pg_timezone_abbrevs();
 
@@ -922,3 +919,6 @@ RETURNS interval
 LANGUAGE INTERNAL
 STRICT IMMUTABLE
 AS 'make_interval';
+
+-- Grant default roles to pg_admin user
+GRANT pg_monitor, pg_backup, pg_replay, pg_replication, pg_rotate_logfile, pg_signal_backend, pg_file_settings TO pg_admin;
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 456c27e..89505e9 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"
@@ -310,12 +311,20 @@ CreateRole(CreateRoleStmt *stmt)
 					 errmsg("permission denied to create role")));
 	}
 
+	/*
+	 * Check that the user is not trying to create a role whose name is
+	 * reserved for special users- public means "all", none means "none"
+	 * and the prefix "pg_" is reserved for system roles (those roles are
+	 * created at initdb time, see include/catalog/pg_authid.h).
+	 */
 	if (strcmp(stmt->role, "public") == 0 ||
-		strcmp(stmt->role, "none") == 0)
+		strcmp(stmt->role, "none") == 0 ||
+		IsReservedName(stmt->role))
 		ereport(ERROR,
 				(errcode(ERRCODE_RESERVED_NAME),
 				 errmsg("role name \"%s\" is reserved",
-						stmt->role)));
+						stmt->role),
+				 errdetail("Names \"public\", \"none\", and the prefix \"pg_\" are reserved.")));
 
 	/*
 	 * Check the pg_authid relation to be certain the role doesn't already
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index 3be5263..4594afd 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 3d9aadb..1debf15 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.
@@ -51,7 +44,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();
 
@@ -94,7 +91,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();
 
@@ -143,7 +144,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 4a20569..c773704 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"
@@ -2794,11 +2796,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 61d609f..d304e4a 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 2b3778b..dcbfe89 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 5f71ded..d1a0e92 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"
@@ -8187,6 +8189,11 @@ show_all_file_settings(PG_FUNCTION_ARGS)
 	AttInMetadata *attinmeta;
 	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")));
+
 	if (SRF_IS_FIRSTCALL())
 	{
 		funcctx = SRF_FIRSTCALL_INIT();
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 7169ad0..f21f66a 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -678,6 +678,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,
@@ -900,6 +901,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 be66b24..638e866 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -23,6 +23,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.4 and below should not have roles starting with pg_ */
+	if (GET_MAJOR_VERSION(old_cluster.major_version) <= 904)
+		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);
@@ -605,7 +611,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)
@@ -621,7 +628,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");
@@ -954,6 +962,34 @@ check_for_jsonb_9_4_usage(ClusterInfo *cluster)
 		check_ok();
 }
 
+/*
+ * check_for_pg_role_prefix()
+ *
+ *	Versions older than 9.5 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/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

From 88615d712892220cfe4c338317842e368f1fb62e Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Thu, 7 May 2015 23:35:03 -0400
Subject: [PATCH] Create default roles for administrative functions

To reduce the number of users on a system who are superusers,
create a set of roles by default during initdb which are allowed to run
certain functions that allow non-superusers to perform specific
administrative tasks and have access to privileged information.

The prefix "pg_" is reserved for default system roles, similar to
schemas and tablespaces.  pg_upgrade is modified to check for any roles
which start with "pg_" and complain if they exist.  pg_dumpall is
modified to not dump out roles starting with "pg_" on 9.5-and-above
systems.  CreateRole is modified to refuse creation of roles which start
with "pg_", similar to CreateSchema.  psql only shows default roles when
called with \duS or \dgS, similar to other system objects.

Roles created are: pg_backup, pg_monitor, pg_replay, pg_replication,
pg_rotate_logfile, pg_signal_backend, pg_file_settings and pg_admin.
---
 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/catalog/catalog.c                  |  5 +-
 src/backend/catalog/system_views.sql           |  6 +-
 src/backend/commands/user.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/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 +-
 src/include/catalog/pg_authid.h                | 19 +++++-
 20 files changed, 280 insertions(+), 73 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 2756652..7a3ca1e 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 177ac7a..1fd4fcc 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/catalog/catalog.c b/src/backend/catalog/catalog.c
index fa2aa27..9c3b760 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.5, this is also true
+ *		for roles.
  */
 bool
 IsReservedName(const char *name)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 18921c4..de1ed77 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -414,9 +414,6 @@ GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
 CREATE VIEW pg_file_settings AS
    SELECT * FROM pg_show_all_file_settings() AS A;
 
-REVOKE ALL on pg_file_settings FROM PUBLIC;
-REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC;
-
 CREATE VIEW pg_timezone_abbrevs AS
     SELECT * FROM pg_timezone_abbrevs();
 
@@ -922,3 +919,6 @@ RETURNS interval
 LANGUAGE INTERNAL
 STRICT IMMUTABLE
 AS 'make_interval';
+
+-- Grant default roles to pg_admin user
+GRANT pg_monitor, pg_backup, pg_replay, pg_replication, pg_rotate_logfile, pg_signal_backend, pg_file_settings TO pg_admin;
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 456c27e..53f2960 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"
@@ -310,12 +311,20 @@ CreateRole(CreateRoleStmt *stmt)
 					 errmsg("permission denied to create role")));
 	}
 
+	/*
+	 * Check that the user is not trying to create a role whose name is
+	 * reserved for special users- public means "all", none means "none"
+	 * and the prefix "pg_" is reserved for system roles (those roles are
+	 * created at initdb time, see include/catalog/pg_authid.h).
+	 */
 	if (strcmp(stmt->role, "public") == 0 ||
-		strcmp(stmt->role, "none") == 0)
+		strcmp(stmt->role, "none") == 0 ||
+		IsReservedName(stmt->role))
 		ereport(ERROR,
 				(errcode(ERRCODE_RESERVED_NAME),
 				 errmsg("role name \"%s\" is reserved",
-						stmt->role)));
+						stmt->role),
+				 errdetail("Names \"public\", \"none\", and the prefix \"pg_\" are reserved.")));
 
 	/*
 	 * Check the pg_authid relation to be certain the role doesn't already
@@ -1123,6 +1132,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);
@@ -1142,6 +1152,7 @@ RenameRole(const char *oldname, const char *newname)
 	 */
 
 	roleid = HeapTupleGetOid(oldtuple);
+	authform = (Form_pg_authid) GETSTRUCT(oldtuple);
 
 	if (roleid == GetSessionUserId())
 		ereport(ERROR,
@@ -1158,17 +1169,26 @@ RenameRole(const char *oldname, const char *newname)
 				(errcode(ERRCODE_DUPLICATE_OBJECT),
 				 errmsg("role \"%s\" already exists", newname)));
 
+	/*
+	 * Check that the user is not trying to change a role whose name is
+	 * reserved for special users- public means "all", none means "none"
+	 * and the prefix "pg_" is reserved for system roles (those roles are
+	 * created at initdb time, see include/catalog/pg_authid.h).
+	 */
 	if (strcmp(newname, "public") == 0 ||
-		strcmp(newname, "none") == 0)
+		strcmp(newname, "none") == 0 ||
+		IsReservedName(NameStr(authform->rolname)) ||
+		IsReservedName(newname))
 		ereport(ERROR,
 				(errcode(ERRCODE_RESERVED_NAME),
 				 errmsg("role name \"%s\" is reserved",
-						newname)));
+						newname),
+				 errdetail("Names \"public\", \"none\", and the prefix \"pg_\" are reserved.")));
 
 	/*
 	 * createrole is enough privilege unless you want to mess with a superuser
 	 */
-	if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper)
+	if (authform->rolsuper)
 	{
 		if (!superuser())
 			ereport(ERROR,
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index 3be5263..4594afd 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 3d9aadb..1debf15 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.
@@ -51,7 +44,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();
 
@@ -94,7 +91,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();
 
@@ -143,7 +144,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 4a20569..c773704 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"
@@ -2794,11 +2796,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 61d609f..d304e4a 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 2b3778b..dcbfe89 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 5f71ded..d1a0e92 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"
@@ -8187,6 +8189,11 @@ show_all_file_settings(PG_FUNCTION_ARGS)
 	AttInMetadata *attinmeta;
 	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")));
+
 	if (SRF_IS_FIRSTCALL())
 	{
 		funcctx = SRF_FIRSTCALL_INIT();
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 7169ad0..f21f66a 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -678,6 +678,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,
@@ -900,6 +901,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 be66b24..638e866 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -23,6 +23,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.4 and below should not have roles starting with pg_ */
+	if (GET_MAJOR_VERSION(old_cluster.major_version) <= 904)
+		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);
@@ -605,7 +611,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)
@@ -621,7 +628,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");
@@ -954,6 +962,34 @@ check_for_jsonb_9_4_usage(ClusterInfo *cluster)
 		check_ok();
 }
 
+/*
+ * check_for_pg_role_prefix()
+ *
+ *	Versions older than 9.5 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 70b7d3b..77518ef 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -418,7 +418,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();
@@ -463,7 +463,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 04d769e..8829423 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 ea05c3e..2b7714a 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -218,7 +218,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"));
@@ -231,7 +231,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/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

From 0db7ebf549aeee7f04b8383ac391f349f810ef4b Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Fri, 8 Apr 2016 17:18:27 -0400
Subject: [PATCH] Remove superuser checks in pgstattuple 1.4

Now that we track initial privileges on extension objects and changes to
those permissions, we can drop the superuser() checks from the various
functions which are part of the pgstattuple extension.

Since a pg_upgrade will preserve the version of the extension which
existed prior to the upgrade, we can't simply modify the existing
functions but instead need to create new functions which remove the
checks and update the SQL-level functions to use the new functions
(and to REVOKE EXECUTE rights on those functions from PUBLIC).

Approach suggested by Noah.
---
 contrib/pgstattuple/Makefile                  |   2 +-
 contrib/pgstattuple/pgstatapprox.c            |  35 ++++++--
 contrib/pgstattuple/pgstatindex.c             | 108 +++++++++++++++++++++++--
 contrib/pgstattuple/pgstattuple--1.3--1.4.sql | 111 ++++++++++++++++++++++++++
 contrib/pgstattuple/pgstattuple--1.3.sql      |  95 ----------------------
 contrib/pgstattuple/pgstattuple--1.4.sql      | 111 ++++++++++++++++++++++++++
 contrib/pgstattuple/pgstattuple.c             |  36 +++++++++
 contrib/pgstattuple/pgstattuple.control       |   2 +-
 8 files changed, 392 insertions(+), 108 deletions(-)
 create mode 100644 contrib/pgstattuple/pgstattuple--1.3--1.4.sql
 delete mode 100644 contrib/pgstattuple/pgstattuple--1.3.sql
 create mode 100644 contrib/pgstattuple/pgstattuple--1.4.sql

diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile
index 6083dab..01f1feb 100644
--- a/contrib/pgstattuple/Makefile
+++ b/contrib/pgstattuple/Makefile
@@ -4,7 +4,7 @@ MODULE_big	= pgstattuple
 OBJS		= pgstattuple.o pgstatindex.o pgstatapprox.o $(WIN32RES)
 
 EXTENSION = pgstattuple
-DATA = pgstattuple--1.3.sql pgstattuple--1.2--1.3.sql pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
+DATA = pgstattuple--1.4.sql pgstattuple--1.3--1.4.sql pgstattuple--1.2--1.3.sql pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
 PGFILEDESC = "pgstattuple - tuple-level statistics"
 
 REGRESS = pgstattuple
diff --git a/contrib/pgstattuple/pgstatapprox.c b/contrib/pgstattuple/pgstatapprox.c
index b7734fa..5671791 100644
--- a/contrib/pgstattuple/pgstatapprox.c
+++ b/contrib/pgstattuple/pgstatapprox.c
@@ -29,6 +29,9 @@
 #include "commands/vacuum.h"
 
 PG_FUNCTION_INFO_V1(pgstattuple_approx);
+PG_FUNCTION_INFO_V1(pgstattuple_approx_v1_4);
+
+Datum pgstattuple_approx_internal(Oid relid, FunctionCallInfo fcinfo);
 
 typedef struct output_type
 {
@@ -209,6 +212,33 @@ Datum
 pgstattuple_approx(PG_FUNCTION_ARGS)
 {
 	Oid			relid = PG_GETARG_OID(0);
+
+	if (!superuser())
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser to use pgstattuple functions"))));
+
+	PG_RETURN_DATUM(pgstattuple_approx_internal(relid, fcinfo));
+}
+
+/*
+ * As of pgstattuple version 1.4, we no longer need to check if the user
+ * is a superuser because we REVOKE EXECUTE on the function from PUBLIC.
+ * Users can then grant access to it based on their policies.
+ *
+ * Otherwise identical to pgstattuple_approx (above).
+ */
+Datum
+pgstattuple_approx_v1_4(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	PG_RETURN_DATUM(pgstattuple_approx_internal(relid, fcinfo));
+}
+
+Datum
+pgstattuple_approx_internal(Oid relid, FunctionCallInfo fcinfo)
+{
 	Relation	rel;
 	output_type stat = {0};
 	TupleDesc	tupdesc;
@@ -217,11 +247,6 @@ pgstattuple_approx(PG_FUNCTION_ARGS)
 	HeapTuple	ret;
 	int			i = 0;
 
-	if (!superuser())
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to use pgstattuple functions"))));
-
 	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
 		elog(ERROR, "return type must be a row type");
 
diff --git a/contrib/pgstattuple/pgstatindex.c b/contrib/pgstattuple/pgstatindex.c
index 4596632..d03be25 100644
--- a/contrib/pgstattuple/pgstatindex.c
+++ b/contrib/pgstattuple/pgstatindex.c
@@ -54,6 +54,14 @@ PG_FUNCTION_INFO_V1(pg_relpages);
 PG_FUNCTION_INFO_V1(pg_relpagesbyid);
 PG_FUNCTION_INFO_V1(pgstatginindex);
 
+PG_FUNCTION_INFO_V1(pgstatindex_v1_4);
+PG_FUNCTION_INFO_V1(pgstatindexbyid_v1_4);
+PG_FUNCTION_INFO_V1(pg_relpages_v1_4);
+PG_FUNCTION_INFO_V1(pg_relpagesbyid_v1_4);
+PG_FUNCTION_INFO_V1(pgstatginindex_v1_4);
+
+Datum pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo);
+
 #define IS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
 #define IS_BTREE(r) ((r)->rd_rel->relam == BTREE_AM_OID)
 #define IS_GIN(r) ((r)->rd_rel->relam == GIN_AM_OID)
@@ -129,6 +137,26 @@ pgstatindex(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(pgstatindex_impl(rel, fcinfo));
 }
 
+/*
+ * As of pgstattuple version 1.4, we no longer need to check if the user
+ * is a superuser because we REVOKE EXECUTE on the function from PUBLIC.
+ * Users can then grant access to it based on their policies.
+ *
+ * Otherwise identical to pgstatindex (above).
+ */
+Datum
+pgstatindex_v1_4(PG_FUNCTION_ARGS)
+{
+	text	   *relname = PG_GETARG_TEXT_P(0);
+	Relation	rel;
+	RangeVar   *relrv;
+
+	relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+	rel = relation_openrv(relrv, AccessShareLock);
+
+	PG_RETURN_DATUM(pgstatindex_impl(rel, fcinfo));
+}
+
 Datum
 pgstatindexbyid(PG_FUNCTION_ARGS)
 {
@@ -145,6 +173,18 @@ pgstatindexbyid(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(pgstatindex_impl(rel, fcinfo));
 }
 
+/* Remove superuser checks in v1.4, see above */
+Datum
+pgstatindexbyid_v1_4(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	Relation	rel;
+
+	rel = relation_open(relid, AccessShareLock);
+
+	PG_RETURN_DATUM(pgstatindex_impl(rel, fcinfo));
+}
+
 static Datum
 pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
 {
@@ -329,6 +369,27 @@ pg_relpages(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(relpages);
 }
 
+/* Remove superuser checks in v1.4, see above */
+Datum
+pg_relpages_v1_4(PG_FUNCTION_ARGS)
+{
+	text	   *relname = PG_GETARG_TEXT_P(0);
+	int64		relpages;
+	Relation	rel;
+	RangeVar   *relrv;
+
+	relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+	rel = relation_openrv(relrv, AccessShareLock);
+
+	/* note: this will work OK on non-local temp tables */
+
+	relpages = RelationGetNumberOfBlocks(rel);
+
+	relation_close(rel, AccessShareLock);
+
+	PG_RETURN_INT64(relpages);
+}
+
 Datum
 pg_relpagesbyid(PG_FUNCTION_ARGS)
 {
@@ -352,6 +413,25 @@ pg_relpagesbyid(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(relpages);
 }
 
+/* Remove superuser checks in v1.4, see above */
+Datum
+pg_relpagesbyid_v1_4(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	int64		relpages;
+	Relation	rel;
+
+	rel = relation_open(relid, AccessShareLock);
+
+	/* note: this will work OK on non-local temp tables */
+
+	relpages = RelationGetNumberOfBlocks(rel);
+
+	relation_close(rel, AccessShareLock);
+
+	PG_RETURN_INT64(relpages);
+}
+
 /* ------------------------------------------------------
  * pgstatginindex()
  *
@@ -362,6 +442,27 @@ Datum
 pgstatginindex(PG_FUNCTION_ARGS)
 {
 	Oid			relid = PG_GETARG_OID(0);
+
+	if (!superuser())
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 (errmsg("must be superuser to use pgstattuple functions"))));
+
+	PG_RETURN_DATUM(pgstatginindex_internal(relid, fcinfo));
+}
+
+/* Remove superuser checks in v1.4, see above */
+Datum
+pgstatginindex_v1_4(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+
+	PG_RETURN_DATUM(pgstatginindex_internal(relid, fcinfo));
+}
+
+Datum
+pgstatginindex_internal(Oid relid, FunctionCallInfo fcinfo)
+{
 	Relation	rel;
 	Buffer		buffer;
 	Page		page;
@@ -373,11 +474,6 @@ pgstatginindex(PG_FUNCTION_ARGS)
 	bool		nulls[3] = {false, false, false};
 	Datum		result;
 
-	if (!superuser())
-		ereport(ERROR,
-				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-				 (errmsg("must be superuser to use pgstattuple functions"))));
-
 	rel = relation_open(relid, AccessShareLock);
 
 	if (!IS_INDEX(rel) || !IS_GIN(rel))
@@ -425,5 +521,5 @@ pgstatginindex(PG_FUNCTION_ARGS)
 	tuple = heap_form_tuple(tupleDesc, values, nulls);
 	result = HeapTupleGetDatum(tuple);
 
-	PG_RETURN_DATUM(result);
+	return (result);
 }
diff --git a/contrib/pgstattuple/pgstattuple--1.3--1.4.sql b/contrib/pgstattuple/pgstattuple--1.3--1.4.sql
new file mode 100644
index 0000000..80f18c3
--- /dev/null
+++ b/contrib/pgstattuple/pgstattuple--1.3--1.4.sql
@@ -0,0 +1,111 @@
+/* contrib/pgstattuple/pgstattuple--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION pgstattuple(IN relname text,
+    OUT table_len BIGINT,		-- physical table length in bytes
+    OUT tuple_count BIGINT,		-- number of live tuples
+    OUT tuple_len BIGINT,		-- total tuples length in bytes
+    OUT tuple_percent FLOAT8,		-- live tuples in %
+    OUT dead_tuple_count BIGINT,	-- number of dead tuples
+    OUT dead_tuple_len BIGINT,		-- total dead tuples length in bytes
+    OUT dead_tuple_percent FLOAT8,	-- dead tuples in %
+    OUT free_space BIGINT,		-- free space in bytes
+    OUT free_percent FLOAT8)		-- free space in %
+AS 'MODULE_PATHNAME', 'pgstattuple_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstattuple(text) FROM PUBLIC;
+
+CREATE OR REPLACE FUNCTION pgstatindex(IN relname text,
+    OUT version INT,
+    OUT tree_level INT,
+    OUT index_size BIGINT,
+    OUT root_block_no BIGINT,
+    OUT internal_pages BIGINT,
+    OUT leaf_pages BIGINT,
+    OUT empty_pages BIGINT,
+    OUT deleted_pages BIGINT,
+    OUT avg_leaf_density FLOAT8,
+    OUT leaf_fragmentation FLOAT8)
+AS 'MODULE_PATHNAME', 'pgstatindex_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstatindex(text) FROM PUBLIC;
+
+CREATE OR REPLACE FUNCTION pg_relpages(IN relname text)
+RETURNS BIGINT
+AS 'MODULE_PATHNAME', 'pg_relpages_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pg_relpages(text) FROM PUBLIC;
+
+/* New stuff in 1.1 begins here */
+
+CREATE OR REPLACE FUNCTION pgstatginindex(IN relname regclass,
+    OUT version INT4,
+    OUT pending_pages INT4,
+    OUT pending_tuples BIGINT)
+AS 'MODULE_PATHNAME', 'pgstatginindex_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstatginindex(regclass) FROM PUBLIC;
+
+/* New stuff in 1.2 begins here */
+
+CREATE OR REPLACE FUNCTION pgstattuple(IN reloid regclass,
+    OUT table_len BIGINT,		-- physical table length in bytes
+    OUT tuple_count BIGINT,		-- number of live tuples
+    OUT tuple_len BIGINT,		-- total tuples length in bytes
+    OUT tuple_percent FLOAT8,		-- live tuples in %
+    OUT dead_tuple_count BIGINT,	-- number of dead tuples
+    OUT dead_tuple_len BIGINT,		-- total dead tuples length in bytes
+    OUT dead_tuple_percent FLOAT8,	-- dead tuples in %
+    OUT free_space BIGINT,		-- free space in bytes
+    OUT free_percent FLOAT8)		-- free space in %
+AS 'MODULE_PATHNAME', 'pgstattuplebyid_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstattuple(regclass) FROM PUBLIC;
+
+CREATE OR REPLACE FUNCTION pgstatindex(IN relname regclass,
+    OUT version INT,
+    OUT tree_level INT,
+    OUT index_size BIGINT,
+    OUT root_block_no BIGINT,
+    OUT internal_pages BIGINT,
+    OUT leaf_pages BIGINT,
+    OUT empty_pages BIGINT,
+    OUT deleted_pages BIGINT,
+    OUT avg_leaf_density FLOAT8,
+    OUT leaf_fragmentation FLOAT8)
+AS 'MODULE_PATHNAME', 'pgstatindexbyid_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstatindex(regclass) FROM PUBLIC;
+
+CREATE OR REPLACE FUNCTION pg_relpages(IN relname regclass)
+RETURNS BIGINT
+AS 'MODULE_PATHNAME', 'pg_relpagesbyid_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pg_relpages(regclass) FROM PUBLIC;
+
+/* New stuff in 1.3 begins here */
+
+CREATE OR REPLACE FUNCTION pgstattuple_approx(IN reloid regclass,
+    OUT table_len BIGINT,               -- physical table length in bytes
+    OUT scanned_percent FLOAT8,         -- what percentage of the table's pages was scanned
+    OUT approx_tuple_count BIGINT,      -- estimated number of live tuples
+    OUT approx_tuple_len BIGINT,        -- estimated total length in bytes of live tuples
+    OUT approx_tuple_percent FLOAT8,    -- live tuples in % (based on estimate)
+    OUT dead_tuple_count BIGINT,        -- exact number of dead tuples
+    OUT dead_tuple_len BIGINT,          -- exact total length in bytes of dead tuples
+    OUT dead_tuple_percent FLOAT8,      -- dead tuples in % (based on estimate)
+    OUT approx_free_space BIGINT,       -- estimated free space in bytes
+    OUT approx_free_percent FLOAT8)     -- free space in % (based on estimate)
+AS 'MODULE_PATHNAME', 'pgstattuple_approx_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstattuple_approx(regclass) FROM PUBLIC;
diff --git a/contrib/pgstattuple/pgstattuple--1.3.sql b/contrib/pgstattuple/pgstattuple--1.3.sql
deleted file mode 100644
index f3996e7..0000000
--- a/contrib/pgstattuple/pgstattuple--1.3.sql
+++ /dev/null
@@ -1,95 +0,0 @@
-/* contrib/pgstattuple/pgstattuple--1.3.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit
-
-CREATE FUNCTION pgstattuple(IN relname text,
-    OUT table_len BIGINT,		-- physical table length in bytes
-    OUT tuple_count BIGINT,		-- number of live tuples
-    OUT tuple_len BIGINT,		-- total tuples length in bytes
-    OUT tuple_percent FLOAT8,		-- live tuples in %
-    OUT dead_tuple_count BIGINT,	-- number of dead tuples
-    OUT dead_tuple_len BIGINT,		-- total dead tuples length in bytes
-    OUT dead_tuple_percent FLOAT8,	-- dead tuples in %
-    OUT free_space BIGINT,		-- free space in bytes
-    OUT free_percent FLOAT8)		-- free space in %
-AS 'MODULE_PATHNAME', 'pgstattuple'
-LANGUAGE C STRICT;
-
-CREATE FUNCTION pgstatindex(IN relname text,
-    OUT version INT,
-    OUT tree_level INT,
-    OUT index_size BIGINT,
-    OUT root_block_no BIGINT,
-    OUT internal_pages BIGINT,
-    OUT leaf_pages BIGINT,
-    OUT empty_pages BIGINT,
-    OUT deleted_pages BIGINT,
-    OUT avg_leaf_density FLOAT8,
-    OUT leaf_fragmentation FLOAT8)
-AS 'MODULE_PATHNAME', 'pgstatindex'
-LANGUAGE C STRICT;
-
-CREATE FUNCTION pg_relpages(IN relname text)
-RETURNS BIGINT
-AS 'MODULE_PATHNAME', 'pg_relpages'
-LANGUAGE C STRICT;
-
-/* New stuff in 1.1 begins here */
-
-CREATE FUNCTION pgstatginindex(IN relname regclass,
-    OUT version INT4,
-    OUT pending_pages INT4,
-    OUT pending_tuples BIGINT)
-AS 'MODULE_PATHNAME', 'pgstatginindex'
-LANGUAGE C STRICT;
-
-/* New stuff in 1.2 begins here */
-
-CREATE FUNCTION pgstattuple(IN reloid regclass,
-    OUT table_len BIGINT,		-- physical table length in bytes
-    OUT tuple_count BIGINT,		-- number of live tuples
-    OUT tuple_len BIGINT,		-- total tuples length in bytes
-    OUT tuple_percent FLOAT8,		-- live tuples in %
-    OUT dead_tuple_count BIGINT,	-- number of dead tuples
-    OUT dead_tuple_len BIGINT,		-- total dead tuples length in bytes
-    OUT dead_tuple_percent FLOAT8,	-- dead tuples in %
-    OUT free_space BIGINT,		-- free space in bytes
-    OUT free_percent FLOAT8)		-- free space in %
-AS 'MODULE_PATHNAME', 'pgstattuplebyid'
-LANGUAGE C STRICT;
-
-CREATE FUNCTION pgstatindex(IN relname regclass,
-    OUT version INT,
-    OUT tree_level INT,
-    OUT index_size BIGINT,
-    OUT root_block_no BIGINT,
-    OUT internal_pages BIGINT,
-    OUT leaf_pages BIGINT,
-    OUT empty_pages BIGINT,
-    OUT deleted_pages BIGINT,
-    OUT avg_leaf_density FLOAT8,
-    OUT leaf_fragmentation FLOAT8)
-AS 'MODULE_PATHNAME', 'pgstatindexbyid'
-LANGUAGE C STRICT;
-
-CREATE FUNCTION pg_relpages(IN relname regclass)
-RETURNS BIGINT
-AS 'MODULE_PATHNAME', 'pg_relpagesbyid'
-LANGUAGE C STRICT;
-
-/* New stuff in 1.3 begins here */
-
-CREATE FUNCTION pgstattuple_approx(IN reloid regclass,
-    OUT table_len BIGINT,               -- physical table length in bytes
-    OUT scanned_percent FLOAT8,         -- what percentage of the table's pages was scanned
-    OUT approx_tuple_count BIGINT,      -- estimated number of live tuples
-    OUT approx_tuple_len BIGINT,        -- estimated total length in bytes of live tuples
-    OUT approx_tuple_percent FLOAT8,    -- live tuples in % (based on estimate)
-    OUT dead_tuple_count BIGINT,        -- exact number of dead tuples
-    OUT dead_tuple_len BIGINT,          -- exact total length in bytes of dead tuples
-    OUT dead_tuple_percent FLOAT8,      -- dead tuples in % (based on estimate)
-    OUT approx_free_space BIGINT,       -- estimated free space in bytes
-    OUT approx_free_percent FLOAT8)     -- free space in % (based on estimate)
-AS 'MODULE_PATHNAME', 'pgstattuple_approx'
-LANGUAGE C STRICT;
diff --git a/contrib/pgstattuple/pgstattuple--1.4.sql b/contrib/pgstattuple/pgstattuple--1.4.sql
new file mode 100644
index 0000000..be7f003
--- /dev/null
+++ b/contrib/pgstattuple/pgstattuple--1.4.sql
@@ -0,0 +1,111 @@
+/* contrib/pgstattuple/pgstattuple--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit
+
+CREATE FUNCTION pgstattuple(IN relname text,
+    OUT table_len BIGINT,		-- physical table length in bytes
+    OUT tuple_count BIGINT,		-- number of live tuples
+    OUT tuple_len BIGINT,		-- total tuples length in bytes
+    OUT tuple_percent FLOAT8,		-- live tuples in %
+    OUT dead_tuple_count BIGINT,	-- number of dead tuples
+    OUT dead_tuple_len BIGINT,		-- total dead tuples length in bytes
+    OUT dead_tuple_percent FLOAT8,	-- dead tuples in %
+    OUT free_space BIGINT,		-- free space in bytes
+    OUT free_percent FLOAT8)		-- free space in %
+AS 'MODULE_PATHNAME', 'pgstattuple_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstattuple(text) FROM PUBLIC;
+
+CREATE FUNCTION pgstatindex(IN relname text,
+    OUT version INT,
+    OUT tree_level INT,
+    OUT index_size BIGINT,
+    OUT root_block_no BIGINT,
+    OUT internal_pages BIGINT,
+    OUT leaf_pages BIGINT,
+    OUT empty_pages BIGINT,
+    OUT deleted_pages BIGINT,
+    OUT avg_leaf_density FLOAT8,
+    OUT leaf_fragmentation FLOAT8)
+AS 'MODULE_PATHNAME', 'pgstatindex_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstatindex(text) FROM PUBLIC;
+
+CREATE FUNCTION pg_relpages(IN relname text)
+RETURNS BIGINT
+AS 'MODULE_PATHNAME', 'pg_relpages_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pg_relpages(text) FROM PUBLIC;
+
+/* New stuff in 1.1 begins here */
+
+CREATE FUNCTION pgstatginindex(IN relname regclass,
+    OUT version INT4,
+    OUT pending_pages INT4,
+    OUT pending_tuples BIGINT)
+AS 'MODULE_PATHNAME', 'pgstatginindex_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstatginindex(regclass) FROM PUBLIC;
+
+/* New stuff in 1.2 begins here */
+
+CREATE FUNCTION pgstattuple(IN reloid regclass,
+    OUT table_len BIGINT,		-- physical table length in bytes
+    OUT tuple_count BIGINT,		-- number of live tuples
+    OUT tuple_len BIGINT,		-- total tuples length in bytes
+    OUT tuple_percent FLOAT8,		-- live tuples in %
+    OUT dead_tuple_count BIGINT,	-- number of dead tuples
+    OUT dead_tuple_len BIGINT,		-- total dead tuples length in bytes
+    OUT dead_tuple_percent FLOAT8,	-- dead tuples in %
+    OUT free_space BIGINT,		-- free space in bytes
+    OUT free_percent FLOAT8)		-- free space in %
+AS 'MODULE_PATHNAME', 'pgstattuplebyid_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstattuple(regclass) FROM PUBLIC;
+
+CREATE FUNCTION pgstatindex(IN relname regclass,
+    OUT version INT,
+    OUT tree_level INT,
+    OUT index_size BIGINT,
+    OUT root_block_no BIGINT,
+    OUT internal_pages BIGINT,
+    OUT leaf_pages BIGINT,
+    OUT empty_pages BIGINT,
+    OUT deleted_pages BIGINT,
+    OUT avg_leaf_density FLOAT8,
+    OUT leaf_fragmentation FLOAT8)
+AS 'MODULE_PATHNAME', 'pgstatindexbyid_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstatindex(regclass) FROM PUBLIC;
+
+CREATE FUNCTION pg_relpages(IN relname regclass)
+RETURNS BIGINT
+AS 'MODULE_PATHNAME', 'pg_relpagesbyid_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pg_relpages(regclass) FROM PUBLIC;
+
+/* New stuff in 1.3 begins here */
+
+CREATE FUNCTION pgstattuple_approx(IN reloid regclass,
+    OUT table_len BIGINT,               -- physical table length in bytes
+    OUT scanned_percent FLOAT8,         -- what percentage of the table's pages was scanned
+    OUT approx_tuple_count BIGINT,      -- estimated number of live tuples
+    OUT approx_tuple_len BIGINT,        -- estimated total length in bytes of live tuples
+    OUT approx_tuple_percent FLOAT8,    -- live tuples in % (based on estimate)
+    OUT dead_tuple_count BIGINT,        -- exact number of dead tuples
+    OUT dead_tuple_len BIGINT,          -- exact total length in bytes of dead tuples
+    OUT dead_tuple_percent FLOAT8,      -- dead tuples in % (based on estimate)
+    OUT approx_free_space BIGINT,       -- estimated free space in bytes
+    OUT approx_free_percent FLOAT8)     -- free space in % (based on estimate)
+AS 'MODULE_PATHNAME', 'pgstattuple_approx_v1_4'
+LANGUAGE C STRICT;
+
+REVOKE EXECUTE ON FUNCTION pgstattuple_approx(regclass) FROM PUBLIC;
diff --git a/contrib/pgstattuple/pgstattuple.c b/contrib/pgstattuple/pgstattuple.c
index 46655ac..da0a8f1 100644
--- a/contrib/pgstattuple/pgstattuple.c
+++ b/contrib/pgstattuple/pgstattuple.c
@@ -40,7 +40,9 @@
 PG_MODULE_MAGIC;
 
 PG_FUNCTION_INFO_V1(pgstattuple);
+PG_FUNCTION_INFO_V1(pgstattuple_v1_4);
 PG_FUNCTION_INFO_V1(pgstattuplebyid);
+PG_FUNCTION_INFO_V1(pgstattuplebyid_v1_4);
 
 /*
  * struct pgstattuple_type
@@ -174,6 +176,27 @@ pgstattuple(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(pgstat_relation(rel, fcinfo));
 }
 
+/*
+ * As of pgstattuple version 1.4, we no longer need to check if the user
+ * is a superuser because we REVOKE EXECUTE on the function from PUBLIC.
+ * Users can then grant access to it based on their policies.
+ *
+ * Otherwise identical to pgstattuple (above).
+ */
+Datum
+pgstattuple_v1_4(PG_FUNCTION_ARGS)
+{
+	text	   *relname = PG_GETARG_TEXT_P(0);
+	RangeVar   *relrv;
+	Relation	rel;
+
+	/* open relation */
+	relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
+	rel = relation_openrv(relrv, AccessShareLock);
+
+	PG_RETURN_DATUM(pgstat_relation(rel, fcinfo));
+}
+
 Datum
 pgstattuplebyid(PG_FUNCTION_ARGS)
 {
@@ -191,6 +214,19 @@ pgstattuplebyid(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(pgstat_relation(rel, fcinfo));
 }
 
+/* Remove superuser() check for 1.4 version, see above */
+Datum
+pgstattuplebyid_v1_4(PG_FUNCTION_ARGS)
+{
+	Oid			relid = PG_GETARG_OID(0);
+	Relation	rel;
+
+	/* open relation */
+	rel = relation_open(relid, AccessShareLock);
+
+	PG_RETURN_DATUM(pgstat_relation(rel, fcinfo));
+}
+
 /*
  * pgstat_relation
  */
diff --git a/contrib/pgstattuple/pgstattuple.control b/contrib/pgstattuple/pgstattuple.control
index c03b180..fa328fd 100644
--- a/contrib/pgstattuple/pgstattuple.control
+++ b/contrib/pgstattuple/pgstattuple.control
@@ -1,5 +1,5 @@
 # pgstattuple extension
 comment = 'show tuple-level statistics'
-default_version = '1.3'
+default_version = '1.4'
 module_pathname = '$libdir/pgstattuple'
 relocatable = true
-- 
2.5.0