All, * Heikki Linnakangas (hlinn...@iki.fi) wrote: > On 05/13/2015 06:07 AM, Stephen Frost wrote: > >This does change the XLOG functions to require pg_monitor, as discussed > >on the other thread where it was pointed out by Heikki that the XLOG > >location information could be used to extract sensitive information > >based on what happens during compression. > > That seems like an orthogonal issue, not something that should be > bundled in this patch. IIRC we didn't reach a consensus on what to > do about the compression-leaks-information issue. One idea was to > make it configurable on a per-table basis, and if we do that, > perhaps we don't need to restrict access to > pg_current_xlog_location() and friends.
Updated patch attached which removes the changes to the XLOG location
functions and adds checks for AlterRole and RenameRole to prevent
altering or renaming the default roles. Also adds '\duS'/'\dgS'
support to psql, to show default roles only when asked.
Thanks!
Stephen
From 88615d712892220cfe4c338317842e368f1fb62e 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 allowed to run
certain functions that allow non-superusers to perform specific
administrative tasks and have access to privileged information.
The prefix "pg_" is reserved for default system roles, similar to
schemas and tablespaces. pg_upgrade is modified to check for any roles
which start with "pg_" and complain if they exist. pg_dumpall is
modified to not dump out roles starting with "pg_" on 9.5-and-above
systems. CreateRole is modified to refuse creation of roles which start
with "pg_", similar to CreateSchema. psql only shows default roles when
called with \duS or \dgS, similar to other system objects.
Roles created are: pg_backup, pg_monitor, pg_replay, pg_replication,
pg_rotate_logfile, pg_signal_backend, pg_file_settings and pg_admin.
---
contrib/test_decoding/expected/permissions.out | 8 +--
doc/src/sgml/catalogs.sgml | 9 +++
doc/src/sgml/user-manag.sgml | 91 ++++++++++++++++++++++++++
src/backend/access/transam/xlogfuncs.c | 30 +++++----
src/backend/catalog/catalog.c | 5 +-
src/backend/catalog/system_views.sql | 6 +-
src/backend/commands/user.c | 30 +++++++--
src/backend/replication/logical/logicalfuncs.c | 17 ++---
src/backend/replication/slotfuncs.c | 29 ++++----
src/backend/replication/walsender.c | 8 ++-
src/backend/utils/adt/misc.c | 12 ++--
src/backend/utils/adt/pgstatfuncs.c | 25 ++++---
src/backend/utils/misc/guc.c | 7 ++
src/bin/pg_dump/pg_dumpall.c | 2 +
src/bin/pg_upgrade/check.c | 40 ++++++++++-
src/bin/psql/command.c | 4 +-
src/bin/psql/describe.c | 5 +-
src/bin/psql/describe.h | 2 +-
src/bin/psql/help.c | 4 +-
src/include/catalog/pg_authid.h | 19 +++++-
20 files changed, 280 insertions(+), 73 deletions(-)
diff --git a/contrib/test_decoding/expected/permissions.out b/contrib/test_decoding/expected/permissions.out
index 212fd1d..79a7f86 100644
--- a/contrib/test_decoding/expected/permissions.out
+++ b/contrib/test_decoding/expected/permissions.out
@@ -54,13 +54,13 @@ RESET ROLE;
-- plain user *can't* can control replication
SET ROLE lr_normal;
SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding');
-ERROR: must be superuser or replication role to use replication slots
+ERROR: must be superuser or member of pg_replication to use replication slots
INSERT INTO lr_test VALUES('lr_superuser_init');
ERROR: permission denied for relation lr_test
SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');
-ERROR: must be superuser or replication role to use replication slots
+ERROR: must be superuser or member of pg_replication to use replication slots
SELECT pg_drop_replication_slot('regression_slot');
-ERROR: must be superuser or replication role to use replication slots
+ERROR: must be superuser or member of pg_replication to use replication slots
RESET ROLE;
-- replication users can drop superuser created slots
SET ROLE lr_superuser;
@@ -90,7 +90,7 @@ SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', 'test_d
RESET ROLE;
SET ROLE lr_normal;
SELECT pg_drop_replication_slot('regression_slot');
-ERROR: must be superuser or replication role to use replication slots
+ERROR: must be superuser or member of pg_replication to use replication slots
RESET ROLE;
-- all users can see existing slots
SET ROLE lr_superuser;
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2756652..7a3ca1e 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -21,6 +21,15 @@
particularly esoteric operations, such as adding index access methods.
</para>
+ <para>
+ Also note that changing the permissions on objects in the system
+ catalogs, while possible, is unlikely to have the desired effect as
+ the internal lookup functions use a cache and do not check the
+ permissions nor policies of tables in the system catalog. Further,
+ permission changes to objects in the system catalogs are not
+ preserved by pg_dump or across upgrades.
+ </para>
+
<sect1 id="catalogs-overview">
<title>Overview</title>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 177ac7a..1fd4fcc 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -414,6 +414,97 @@ DROP ROLE <replaceable>name</replaceable>;
</para>
</sect1>
+ <sect1 id="default-roles">
+ <title>Default Roles</title>
+
+ <indexterm zone="default-roles">
+ <primary>role</>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> provides a set of default roles
+ which provide access to certain, commonly needed, privileged capabilities
+ and information. Administrators can GRANT these roles to users and/or
+ other roles in their environment, providing those users with access to
+ the specified capabilities and information.
+ </para>
+
+ <para>
+ The default roles are described in <xref linkend="default-roles-table">.
+ Note that the specific permissions for each of the default roles may
+ change in the future as additional capabilities are added. Administrators
+ should monitor the release notes for changes.
+ </para>
+
+ <table tocentry="1" id="default-roles-table">
+ <title>Default Roles</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Role</entry>
+ <entry>Allowed Access</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>pg_backup</entry>
+ <entry>Start and stop backups, switch xlogs, and create restore points.</entry>
+ </row>
+ <row>
+ <entry>pg_montior</entry>
+ <entry>To privileged system information (eg: activity of other users, replication lag)</entry>
+ </row>
+ <row>
+ <entry>pg_replay</entry>
+ <entry>Pause and resume xlog replay on replicas.</entry>
+ </row>
+ <row>
+ <entry>pg_replication</entry>
+ <entry>Create, destroy, and work with replication slots.</entry>
+ </row>
+ <row>
+ <entry>pg_rotate_logfile</entry>
+ <entry>Request logfile rotation</entry>
+ </row>
+ <row>
+ <entry>pg_signal_backend</entry>
+ <entry>Send signals to other backends (eg: cancel query, terminate)</entry>
+ </row>
+ <row>
+ <entry>pg_file_settings</entry>
+ <entry>Allowed to view config settings from all config files</entry>
+ </row>
+ <row>
+ <entry>pg_admin</entry>
+ <entry>
+ Granted pg_backup, pg_monitor, pg_reply, pg_replication,
+ pg_rotate_logfile, pg_signal_backend and pg_file_settings roles.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ Administrators can grant access to these roles to users using the GRANT
+ command:
+
+<programlisting>
+GRANT pg_backup TO backup_user;
+GRANT pg_monitor TO nagios;
+GRANT pg_admin TO admin_user;
+</programlisting>
+ </para>
+
+ <para>
+ Administrators should use the default roles for managing access to capabilities
+ and not change the permissions on the objects in the system catalogs, as such
+ changes are unlikely to have the desired effect and will not be preserved by
+ pg_dump or across upgrades.
+ </para>
+
+ </sect1>
+
<sect1 id="perm-functions">
<title>Function and Trigger Security</title>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 329bb8c..dda39ad 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -22,11 +22,13 @@
#include "access/xlog_internal.h"
#include "access/xlogutils.h"
#include "catalog/catalog.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "replication/walreceiver.h"
#include "storage/smgr.h"
+#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/numeric.h"
#include "utils/guc.h"
@@ -55,10 +57,12 @@ pg_start_backup(PG_FUNCTION_ARGS)
backupidstr = text_to_cstring(backupid);
- if (!superuser() && !has_rolreplication(GetUserId()))
+ if (!has_rolreplication(GetUserId()) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser or replication role to run a backup")));
+ errmsg("must be superuser or member of pg_backup or pg_replication to run a backup")));
/* Make sure we can open the directory with tablespaces in it */
dir = AllocateDir("pg_tblspc");
@@ -92,10 +96,12 @@ pg_stop_backup(PG_FUNCTION_ARGS)
{
XLogRecPtr stoppoint;
- if (!superuser() && !has_rolreplication(GetUserId()))
+ if (!has_rolreplication(GetUserId()) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser or replication role to run a backup"))));
+ errmsg("must be superuser or member of pg_backup or pg_replication to run a backup")));
stoppoint = do_pg_stop_backup(NULL, true, NULL);
@@ -110,10 +116,10 @@ pg_switch_xlog(PG_FUNCTION_ARGS)
{
XLogRecPtr switchpoint;
- if (!superuser())
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser to switch transaction log files"))));
+ errmsg("must be superuser or member of pg_backup to switch transaction log files")));
if (RecoveryInProgress())
ereport(ERROR,
@@ -139,10 +145,10 @@ pg_create_restore_point(PG_FUNCTION_ARGS)
char *restore_name_str;
XLogRecPtr restorepoint;
- if (!superuser())
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser to create a restore point"))));
+ errmsg("must be superuser or member of pg_backup to create a restore point")));
if (RecoveryInProgress())
ereport(ERROR,
@@ -348,10 +354,10 @@ pg_xlogfile_name(PG_FUNCTION_ARGS)
Datum
pg_xlog_replay_pause(PG_FUNCTION_ARGS)
{
- if (!superuser())
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLAYID))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser to control recovery"))));
+ errmsg("must be superuser or member of pg_replay to control recovery")));
if (!RecoveryInProgress())
ereport(ERROR,
@@ -370,10 +376,10 @@ pg_xlog_replay_pause(PG_FUNCTION_ARGS)
Datum
pg_xlog_replay_resume(PG_FUNCTION_ARGS)
{
- if (!superuser())
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLAYID))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser to control recovery"))));
+ errmsg("must be superuser or member of pg_replay to control recovery")));
if (!RecoveryInProgress())
ereport(ERROR,
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index fa2aa27..9c3b760 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -184,8 +184,9 @@ IsToastNamespace(Oid namespaceId)
* True iff name starts with the pg_ prefix.
*
* For some classes of objects, the prefix pg_ is reserved for
- * system objects only. As of 8.0, this is only true for
- * schema and tablespace names.
+ * system objects only. As of 8.0, this was only true for
+ * schema and tablespace names. With 9.5, this is also true
+ * for roles.
*/
bool
IsReservedName(const char *name)
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 18921c4..de1ed77 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -414,9 +414,6 @@ GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
CREATE VIEW pg_file_settings AS
SELECT * FROM pg_show_all_file_settings() AS A;
-REVOKE ALL on pg_file_settings FROM PUBLIC;
-REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC;
-
CREATE VIEW pg_timezone_abbrevs AS
SELECT * FROM pg_timezone_abbrevs();
@@ -922,3 +919,6 @@ RETURNS interval
LANGUAGE INTERNAL
STRICT IMMUTABLE
AS 'make_interval';
+
+-- Grant default roles to pg_admin user
+GRANT pg_monitor, pg_backup, pg_replay, pg_replication, pg_rotate_logfile, pg_signal_backend, pg_file_settings TO pg_admin;
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 456c27e..53f2960 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -17,6 +17,7 @@
#include "access/htup_details.h"
#include "access/xact.h"
#include "catalog/binary_upgrade.h"
+#include "catalog/catalog.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
@@ -310,12 +311,20 @@ CreateRole(CreateRoleStmt *stmt)
errmsg("permission denied to create role")));
}
+ /*
+ * Check that the user is not trying to create a role whose name is
+ * reserved for special users- public means "all", none means "none"
+ * and the prefix "pg_" is reserved for system roles (those roles are
+ * created at initdb time, see include/catalog/pg_authid.h).
+ */
if (strcmp(stmt->role, "public") == 0 ||
- strcmp(stmt->role, "none") == 0)
+ strcmp(stmt->role, "none") == 0 ||
+ IsReservedName(stmt->role))
ereport(ERROR,
(errcode(ERRCODE_RESERVED_NAME),
errmsg("role name \"%s\" is reserved",
- stmt->role)));
+ stmt->role),
+ errdetail("Names \"public\", \"none\", and the prefix \"pg_\" are reserved.")));
/*
* Check the pg_authid relation to be certain the role doesn't already
@@ -1123,6 +1132,7 @@ RenameRole(const char *oldname, const char *newname)
int i;
Oid roleid;
ObjectAddress address;
+ Form_pg_authid authform;
rel = heap_open(AuthIdRelationId, RowExclusiveLock);
dsc = RelationGetDescr(rel);
@@ -1142,6 +1152,7 @@ RenameRole(const char *oldname, const char *newname)
*/
roleid = HeapTupleGetOid(oldtuple);
+ authform = (Form_pg_authid) GETSTRUCT(oldtuple);
if (roleid == GetSessionUserId())
ereport(ERROR,
@@ -1158,17 +1169,26 @@ RenameRole(const char *oldname, const char *newname)
(errcode(ERRCODE_DUPLICATE_OBJECT),
errmsg("role \"%s\" already exists", newname)));
+ /*
+ * Check that the user is not trying to change a role whose name is
+ * reserved for special users- public means "all", none means "none"
+ * and the prefix "pg_" is reserved for system roles (those roles are
+ * created at initdb time, see include/catalog/pg_authid.h).
+ */
if (strcmp(newname, "public") == 0 ||
- strcmp(newname, "none") == 0)
+ strcmp(newname, "none") == 0 ||
+ IsReservedName(NameStr(authform->rolname)) ||
+ IsReservedName(newname))
ereport(ERROR,
(errcode(ERRCODE_RESERVED_NAME),
errmsg("role name \"%s\" is reserved",
- newname)));
+ newname),
+ errdetail("Names \"public\", \"none\", and the prefix \"pg_\" are reserved.")));
/*
* createrole is enough privilege unless you want to mess with a superuser
*/
- if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper)
+ if (authform->rolsuper)
{
if (!superuser())
ereport(ERROR,
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index 3be5263..4594afd 100644
--- a/src/backend/replication/logical/logicalfuncs.c
+++ b/src/backend/replication/logical/logicalfuncs.c
@@ -23,12 +23,14 @@
#include "access/xlog_internal.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_type.h"
#include "nodes/makefuncs.h"
#include "mb/pg_wchar.h"
+#include "utils/acl.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/inval.h"
@@ -202,15 +204,6 @@ XLogRead(char *buf, TimeLineID tli, XLogRecPtr startptr, Size count)
}
}
-static void
-check_permissions(void)
-{
- if (!superuser() && !has_rolreplication(GetUserId()))
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser or replication role to use replication slots"))));
-}
-
/*
* read_page callback for logical decoding contexts.
*
@@ -324,7 +317,11 @@ pg_logical_slot_get_changes_guts(FunctionCallInfo fcinfo, bool confirm, bool bin
if (get_call_result_type(fcinfo, NULL, &p->tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
- check_permissions();
+ if (!has_rolreplication(GetUserId()) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser or member of pg_replication to use replication slots"))));
CheckLogicalDecodingRequirements();
diff --git a/src/backend/replication/slotfuncs.c b/src/backend/replication/slotfuncs.c
index 3d9aadb..1debf15 100644
--- a/src/backend/replication/slotfuncs.c
+++ b/src/backend/replication/slotfuncs.c
@@ -17,21 +17,14 @@
#include "miscadmin.h"
#include "access/htup_details.h"
+#include "catalog/pg_authid.h"
#include "replication/slot.h"
#include "replication/logical.h"
#include "replication/logicalfuncs.h"
+#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/pg_lsn.h"
-static void
-check_permissions(void)
-{
- if (!superuser() && !has_rolreplication(GetUserId()))
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser or replication role to use replication slots"))));
-}
-
/*
* SQL function for creating a new physical (streaming replication)
* replication slot.
@@ -51,7 +44,11 @@ pg_create_physical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
- check_permissions();
+ if (!has_rolreplication(GetUserId()) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser or member of pg_replication to use replication slots"))));
CheckSlotRequirements();
@@ -94,7 +91,11 @@ pg_create_logical_replication_slot(PG_FUNCTION_ARGS)
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
- check_permissions();
+ if (!has_rolreplication(GetUserId()) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser or member of pg_replication to use replication slots"))));
CheckLogicalDecodingRequirements();
@@ -143,7 +144,11 @@ pg_drop_replication_slot(PG_FUNCTION_ARGS)
{
Name name = PG_GETARG_NAME(0);
- check_permissions();
+ if (!has_rolreplication(GetUserId()) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_REPLICATIONID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser or member of pg_replication to use replication slots"))));
CheckSlotRequirements();
diff --git a/src/backend/replication/walsender.c b/src/backend/replication/walsender.c
index 4a20569..c773704 100644
--- a/src/backend/replication/walsender.c
+++ b/src/backend/replication/walsender.c
@@ -48,6 +48,7 @@
#include "access/xact.h"
#include "access/xlog_internal.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_type.h"
#include "commands/dbcommands.h"
#include "funcapi.h"
@@ -71,6 +72,7 @@
#include "storage/proc.h"
#include "storage/procarray.h"
#include "tcop/tcopprot.h"
+#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/memutils.h"
@@ -2794,11 +2796,11 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
memset(nulls, 0, sizeof(nulls));
values[0] = Int32GetDatum(walsnd->pid);
- if (!superuser())
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
{
/*
- * Only superusers can see details. Other users only get the pid
- * value to know it's a walsender, but no details.
+ * Only members of pg_monitor can see details. Other users only get
+ * the pid value to know it's a walsender, but no details.
*/
MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
}
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 61d609f..d304e4a 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -21,6 +21,7 @@
#include <unistd.h>
#include "access/sysattr.h"
+#include "catalog/pg_authid.h"
#include "catalog/catalog.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_type.h"
@@ -122,7 +123,8 @@ pg_signal_backend(int pid, int sig)
return SIGNAL_BACKEND_NOSUPERUSER;
/* Users can signal backends they have role membership in. */
- if (!has_privs_of_role(GetUserId(), proc->roleId))
+ if (!has_privs_of_role(GetUserId(), proc->roleId) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_SIGNAL_BACKENDID))
return SIGNAL_BACKEND_NOPERMISSION;
/*
@@ -168,7 +170,7 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be a member of the role whose query is being cancelled"))));
+ (errmsg("must be a member of the role whose query is being cancelled or member of pg_signal_backend"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
}
@@ -192,7 +194,7 @@ pg_terminate_backend(PG_FUNCTION_ARGS)
if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be a member of the role whose process is being terminated"))));
+ (errmsg("must be a member of the role whose process is being terminated or member of pg_signal_backend"))));
PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
}
@@ -225,10 +227,10 @@ pg_reload_conf(PG_FUNCTION_ARGS)
Datum
pg_rotate_logfile(PG_FUNCTION_ARGS)
{
- if (!superuser())
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_ROTATE_LOGFILEID))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser to rotate log files"))));
+ (errmsg("must be superuser or member of pg_rotate_logfile to rotate log files"))));
if (!Logging_collector)
{
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 2b3778b..dcbfe89 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -15,6 +15,7 @@
#include "postgres.h"
#include "access/htup_details.h"
+#include "catalog/pg_authid.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
#include "libpq/ip.h"
@@ -642,7 +643,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
}
/* Values only available to role member */
- if (has_privs_of_role(GetUserId(), beentry->st_userid))
+ if (has_privs_of_role(GetUserId(), beentry->st_userid) ||
+ has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
{
SockAddr zero_clientaddr;
@@ -846,7 +848,8 @@ pg_stat_get_backend_activity(PG_FUNCTION_ARGS)
if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
activity = "<backend information not available>";
- else if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+ else if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
activity = "<insufficient privilege>";
else if (*(beentry->st_activity) == '\0')
activity = "<command string not enabled>";
@@ -867,7 +870,8 @@ pg_stat_get_backend_waiting(PG_FUNCTION_ARGS)
if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
PG_RETURN_NULL();
- if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+ if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
PG_RETURN_NULL();
result = beentry->st_waiting;
@@ -886,7 +890,8 @@ pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS)
if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
PG_RETURN_NULL();
- if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+ if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
PG_RETURN_NULL();
result = beentry->st_activity_start_timestamp;
@@ -912,7 +917,8 @@ pg_stat_get_backend_xact_start(PG_FUNCTION_ARGS)
if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
PG_RETURN_NULL();
- if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+ if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
PG_RETURN_NULL();
result = beentry->st_xact_start_timestamp;
@@ -934,7 +940,8 @@ pg_stat_get_backend_start(PG_FUNCTION_ARGS)
if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
PG_RETURN_NULL();
- if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+ if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
PG_RETURN_NULL();
result = beentry->st_proc_start_timestamp;
@@ -958,7 +965,8 @@ pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS)
if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
PG_RETURN_NULL();
- if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+ if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
PG_RETURN_NULL();
/* A zeroed client addr means we don't know */
@@ -1005,7 +1013,8 @@ pg_stat_get_backend_client_port(PG_FUNCTION_ARGS)
if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL)
PG_RETURN_NULL();
- if (!has_privs_of_role(GetUserId(), beentry->st_userid))
+ if (!has_privs_of_role(GetUserId(), beentry->st_userid) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID))
PG_RETURN_NULL();
/* A zeroed client addr means we don't know */
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 5f71ded..d1a0e92 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -32,6 +32,7 @@
#include "access/twophase.h"
#include "access/xact.h"
#include "catalog/namespace.h"
+#include "catalog/pg_authid.h"
#include "commands/async.h"
#include "commands/prepare.h"
#include "commands/vacuum.h"
@@ -71,6 +72,7 @@
#include "storage/predicate.h"
#include "tcop/tcopprot.h"
#include "tsearch/ts_cache.h"
+#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/bytea.h"
#include "utils/guc_tables.h"
@@ -8187,6 +8189,11 @@ show_all_file_settings(PG_FUNCTION_ARGS)
AttInMetadata *attinmeta;
MemoryContext oldcontext;
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_FILE_SETTINGSID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser or member of pg_file_settings to see all config file settings")));
+
if (SRF_IS_FIRSTCALL())
{
funcctx = SRF_FIRSTCALL_INIT();
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 7169ad0..f21f66a 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -678,6 +678,7 @@ dumpRoles(PGconn *conn)
"pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, "
"rolname = current_user AS is_current_user "
"FROM pg_authid "
+ "WHERE rolname !~ '^pg_' "
"ORDER BY 2");
else if (server_version >= 90100)
printfPQExpBuffer(buf,
@@ -900,6 +901,7 @@ dumpRoleMembership(PGconn *conn)
"LEFT JOIN pg_authid ur on ur.oid = a.roleid "
"LEFT JOIN pg_authid um on um.oid = a.member "
"LEFT JOIN pg_authid ug on ug.oid = a.grantor "
+ "WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')"
"ORDER BY 1,2,3");
if (PQntuples(res) > 0)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index be66b24..638e866 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -23,6 +23,7 @@ static void check_for_prepared_transactions(ClusterInfo *cluster);
static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
static void check_for_reg_data_type_usage(ClusterInfo *cluster);
static void check_for_jsonb_9_4_usage(ClusterInfo *cluster);
+static void check_for_pg_role_prefix(ClusterInfo *cluster);
static void get_bin_version(ClusterInfo *cluster);
static char *get_canonical_locale_name(int category, const char *locale);
@@ -96,6 +97,11 @@ check_and_dump_old_cluster(bool live_check)
check_for_prepared_transactions(&old_cluster);
check_for_reg_data_type_usage(&old_cluster);
check_for_isn_and_int8_passing_mismatch(&old_cluster);
+
+ /* 9.4 and below should not have roles starting with pg_ */
+ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 904)
+ check_for_pg_role_prefix(&old_cluster);
+
if (GET_MAJOR_VERSION(old_cluster.major_version) == 904 &&
old_cluster.controldata.cat_ver < JSONB_FORMAT_CHANGE_CAT_VER)
check_for_jsonb_9_4_usage(&old_cluster);
@@ -605,7 +611,8 @@ check_is_install_user(ClusterInfo *cluster)
res = executeQueryOrDie(conn,
"SELECT rolsuper, oid "
"FROM pg_catalog.pg_roles "
- "WHERE rolname = current_user");
+ "WHERE rolname = current_user "
+ "AND rolname !~ '^pg_'");
/*
* We only allow the install user in the new cluster (see comment below)
@@ -621,7 +628,8 @@ check_is_install_user(ClusterInfo *cluster)
res = executeQueryOrDie(conn,
"SELECT COUNT(*) "
- "FROM pg_catalog.pg_roles ");
+ "FROM pg_catalog.pg_roles "
+ "WHERE rolname !~ '^pg_'");
if (PQntuples(res) != 1)
pg_fatal("could not determine the number of users\n");
@@ -954,6 +962,34 @@ check_for_jsonb_9_4_usage(ClusterInfo *cluster)
check_ok();
}
+/*
+ * check_for_pg_role_prefix()
+ *
+ * Versions older than 9.5 should not have any pg_* roles
+ */
+static void
+check_for_pg_role_prefix(ClusterInfo *cluster)
+{
+ PGresult *res;
+ PGconn *conn = connectToServer(cluster, "template1");
+
+ prep_status("Checking for roles starting with 'pg_'");
+
+ res = executeQueryOrDie(conn,
+ "SELECT * "
+ "FROM pg_catalog.pg_roles "
+ "WHERE rolname ~ '^pg_'");
+
+ if (PQntuples(res) != 0)
+ pg_fatal("The %s cluster contains roles starting with 'pg_'\n",
+ CLUSTER_NAME(cluster));
+
+ PQclear(res);
+
+ PQfinish(conn);
+
+ check_ok();
+}
static void
get_bin_version(ClusterInfo *cluster)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 70b7d3b..77518ef 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -418,7 +418,7 @@ exec_command(const char *cmd,
break;
case 'g':
/* no longer distinct from \du */
- success = describeRoles(pattern, show_verbose);
+ success = describeRoles(pattern, show_verbose, show_system);
break;
case 'l':
success = do_lo_list();
@@ -463,7 +463,7 @@ exec_command(const char *cmd,
success = PSQL_CMD_UNKNOWN;
break;
case 'u':
- success = describeRoles(pattern, show_verbose);
+ success = describeRoles(pattern, show_verbose, show_system);
break;
case 'F': /* text search subsystem */
switch (cmd[2])
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 04d769e..8829423 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2639,7 +2639,7 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
* Describes roles. Any schema portion of the pattern is ignored.
*/
bool
-describeRoles(const char *pattern, bool verbose)
+describeRoles(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
@@ -2684,6 +2684,9 @@ describeRoles(const char *pattern, bool verbose)
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
+ if (!showSystem && !pattern)
+ appendPQExpBufferStr(&buf, "WHERE r.rolname !~ '^pg_'\n");
+
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "r.rolname", NULL, NULL);
}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 822e71a..9e31c02 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -25,7 +25,7 @@ extern bool describeTypes(const char *pattern, bool verbose, bool showSystem);
extern bool describeOperators(const char *pattern, bool verbose, bool showSystem);
/* \du, \dg */
-extern bool describeRoles(const char *pattern, bool verbose);
+extern bool describeRoles(const char *pattern, bool verbose, bool showSystem);
/* \drds */
extern bool listDbRoleSettings(const char *pattern1, const char *pattern2);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index ea05c3e..2b7714a 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -218,7 +218,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dFd[+] [PATTERN] list text search dictionaries\n"));
fprintf(output, _(" \\dFp[+] [PATTERN] list text search parsers\n"));
fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n"));
- fprintf(output, _(" \\dg[+] [PATTERN] list roles\n"));
+ fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n"));
@@ -231,7 +231,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
- fprintf(output, _(" \\du[+] [PATTERN] list roles\n"));
+ fprintf(output, _(" \\du[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
fprintf(output, _(" \\dE[S+] [PATTERN] list foreign tables\n"));
fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n"));
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index d5f19d6..852aac4 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -96,7 +96,24 @@ typedef FormData_pg_authid *Form_pg_authid;
* ----------------
*/
DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_ _null_));
+DATA(insert OID = 3288 ( "pg_admin" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3289 ( "pg_monitor" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3290 ( "pg_backup" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3291 ( "pg_replay" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3292 ( "pg_replication" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3293 ( "pg_rotate_logfile" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3294 ( "pg_signal_backend" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3295 ( "pg_file_settings" f t f f f f f -1 _null_ _null_));
-#define BOOTSTRAP_SUPERUSERID 10
+#define BOOTSTRAP_SUPERUSERID 10
+
+#define DEFAULT_ROLE_ADMINID 3288
+#define DEFAULT_ROLE_MONITORID 3289
+#define DEFAULT_ROLE_BACKUPID 3290
+#define DEFAULT_ROLE_REPLAYID 3291
+#define DEFAULT_ROLE_REPLICATIONID 3292
+#define DEFAULT_ROLE_ROTATE_LOGFILEID 3293
+#define DEFAULT_ROLE_SIGNAL_BACKENDID 3294
+#define DEFAULT_ROLE_FILE_SETTINGSID 3295
#endif /* PG_AUTHID_H */
--
1.9.1
signature.asc
Description: Digital signature
