All, 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 reserves the prefix 'pg_' as being for default roles.
Having these default roles also means that third party applications
(eg: check_postgres.pl) can depend on their availability in their
installation instructions and have a clear understanding of what they
provide.
* Robert Haas (robertmh...@gmail.com) wrote:
> On Wed, Apr 29, 2015 at 8:20 PM, Alvaro Herrera <alvhe...@2ndquadrant.com>
> wrote:
> > On this part I have a bit of a problem -- the prefix is not really
> > reserved, is it. I mean, evidently it's still possible to create roles
> > with the pg_ prefix ... otherwise, how come the new lines to
> > system_views.sql that create the "predefined" roles work in the first
> > place? I think if we're going to reserve role names, we should reserve
> > them for real: CREATE ROLE should flat out reject creation of such
> > roles, and the default ones should be created during bootstrap.
Agreed, and done.
> This is exactly what I mean about needing separate discussion for
> separate parts of the patch. There's so much different stuff in there
> right now that objections like this won't necessarily come out until
> it's far too late to change things around.
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.
This patch is significantly reduced in complexity (it's literally less
than 1/3 of the prior patch, with the largest change being in
system_views.sql where all the REVOKE/GRANT commands are dropped) as it
doesn't modify pg_dump, at all. pg_dumpall is minimally modified by
simply not dumping out roles starting with "pg_" on 9.5 and above
systems. pg_upgrade is similairly modified to check for roles which
start with "pg_" in pre-9.5 versions and complain if found.
I'll be playing around with the patch itself, testing, etc, but what we
really need is a discussion on if anyone is concerned about reserving
"pg_" for default roles. Exactly what roles are created and what
privileges are granted to them can be tweaked easily, though there has
been little discussion and therefore, presumably, little issue with the
categories that were proposed back in October when this was proposed
with role attributes instead of default roles.
As for the previously proposed changes to pg_dump, I don't particularly
have a reason to continue with that effort unless others are interested.
The default roles proposed in this patch solve the use-cases which I had
set out to solve 6 months ago. Please let me know if there is interest
in changing pg_dump to try and preserve permissions which are set in
pg_catalog by administrators, but we've never supported that and it
might be best left as-is.
Thanks!
Stephen
From b04196b49db30b4b9f969500efb851c5e05902f3 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, 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. For a few functions (pg_logicial_slot_* and friends), this
means users who have only the "replication" role attribute will no
longer have accesst to call them directly. This is easily rectified by
granting "pg_replication" to those roles and should be noted in the
release notes. pg_basebackup and other tools which use the replication
protocol are not impacted as those users do not access the functions
through the normal GRANT system.
---
contrib/test_decoding/expected/permissions.out | 9 +-
contrib/test_decoding/sql/permissions.sql | 2 +
doc/src/sgml/user-manag.sgml | 69 ++++++++++++++
src/backend/access/transam/xlogfuncs.c | 30 ------
src/backend/catalog/catalog.c | 5 +-
src/backend/catalog/system_views.sql | 127 +++++++++++++++++++++++++
src/backend/commands/user.c | 13 ++-
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/bin/pg_dump/pg_dumpall.c | 1 +
src/bin/pg_upgrade/check.c | 40 +++++++-
src/include/catalog/pg_authid.h | 5 +
src/include/catalog/pg_proc.h | 6 ++
src/include/replication/walsender.h | 1 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/rules.out | 42 ++++++++
19 files changed, 537 insertions(+), 99 deletions(-)
diff --git a/contrib/test_decoding/expected/permissions.out b/contrib/test_decoding/expected/permissions.out
index 212fd1d..68dd7b1 100644
--- a/contrib/test_decoding/expected/permissions.out
+++ b/contrib/test_decoding/expected/permissions.out
@@ -4,6 +4,7 @@ SET synchronous_commit = on;
CREATE ROLE lr_normal;
CREATE ROLE lr_superuser SUPERUSER;
CREATE ROLE lr_replication REPLICATION;
+GRANT pg_replication TO lr_replication;
CREATE TABLE lr_test(data text);
-- superuser can control replication
SET ROLE lr_superuser;
@@ -54,13 +55,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 +91,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/contrib/test_decoding/sql/permissions.sql b/contrib/test_decoding/sql/permissions.sql
index 8680c55..fad5a09 100644
--- a/contrib/test_decoding/sql/permissions.sql
+++ b/contrib/test_decoding/sql/permissions.sql
@@ -5,6 +5,8 @@ SET synchronous_commit = on;
CREATE ROLE lr_normal;
CREATE ROLE lr_superuser SUPERUSER;
CREATE ROLE lr_replication REPLICATION;
+GRANT pg_replication TO lr_replication;
+
CREATE TABLE lr_test(data text);
-- superuser can control replication
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 177ac7a..49680a6 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -414,6 +414,75 @@ 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_admin</entry>
+ <entry>Granted pg_backup, pg_monitor, pg_reply, pg_replication, roles, and allowed to rotate logfiles, and signal other backends.</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>
+
+ </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 2ad01f4..38e7187 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -623,6 +623,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,
@@ -657,6 +683,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,
@@ -916,3 +967,79 @@ 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 role is created to allow access to these functions
+GRANT EXECUTE ON FUNCTION pg_start_backup(text, boolean) TO pg_backup;
+GRANT EXECUTE ON FUNCTION pg_stop_backup() TO pg_backup;
+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;
+
+-- Admin user allowed to rotate logfiles, signal backends
+-- and gets monitor, backup, replay, and replication
+GRANT pg_monitor, pg_backup, pg_replay, pg_replication TO pg_admin;
+GRANT EXECUTE ON FUNCTION pg_rotate_logfile() TO pg_admin;
+GRANT EXECUTE ON FUNCTION pg_signal_backend(int, int) 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..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/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 7169ad0..27a0606 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,
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/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index d5f19d6..7dcda9e 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -96,6 +96,11 @@ 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 = 3287 ( "pg_monitor" f f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3288 ( "pg_backup" f f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3289 ( "pg_replay" f f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3290 ( "pg_replication" f f f f f f f -1 _null_ _null_));
+DATA(insert OID = 3291 ( "pg_admin" f f f f f f f -1 _null_ _null_));
#define BOOTSTRAP_SUPERUSERID 10
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index bd67d72..b8a051d 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_ ));
@@ -3119,6 +3123,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/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 e8104f0..c68aa06 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/rules.out b/src/test/regress/expected/rules.out
index f7f016b..63b5ab2 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1642,6 +1642,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,
@@ -1749,6 +1771,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,
--
1.9.1
signature.asc
Description: Digital signature
