All, * Stephen Frost (sfr...@snowman.net) wrote: > Starting a new thread, as suggested by Robert, for consideration of > adding default roles for sets of administrative functions, therefore > removing the need for superuser-level roles in many use-cases.
[...]
> This is part 2 and really the "guts" of the changes proposed. Part 1
> was the patch sent earlier today to change pg_stat_get_activity() to use
> a tuplestore, and this patch depends on that one. I'll rebase and
> resend after that's gone in. I did notice that Andres just pushed
> upsert though, and it wouldn't surprise me if there are now merge
> conflicts. Will address all of that tomorrow, in any case.
Here's a rebase with a few additional items as follows.
Andres suggested that we drop the REPLICATION role attribute and just
use membership in pg_replication instead. That's turned out quite
fantastically as we can now handle upgrades without breaking anything-
CREATE ROLE and ALTER ROLE still accept the attribute but simply grant
pg_replication to the role instead, and postinit.c has been changed to
check role membership similar to other pg_hba role membership checks
when a replication connection comes in. Hat's off to Andres for his
suggestion.
I've added two more default roles, since it was pointed out to me that I
hadn't exactly mimicked the role attributes originally proposed. These
are pg_rotate_logfile and pg_signal_backend. This also removes any
direct object grants to pg_admin; it now means only "all of the other
roles combined" without anything additional.
Documentation and regression tests updated.
Comments and suggestions are most welcome, as always.
Thanks!
Stephen
From 381a3e619b8450a0f3a5225d70098fcb8291fd52 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@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
signature.asc
Description: Digital signature
