From 88615d712892220cfe4c338317842e368f1fb62e Mon Sep 17 00:00:00 2001 From: Stephen Frost 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. + + 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. + + Overview 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 name; + + Default Roles + + + role + + + + PostgreSQL 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. + + + + The default roles are described in . + 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. + + + + Default Roles + + + + Role + Allowed Access + + + + + pg_backup + Start and stop backups, switch xlogs, and create restore points. + + + pg_montior + To privileged system information (eg: activity of other users, replication lag) + + + pg_replay + Pause and resume xlog replay on replicas. + + + pg_replication + Create, destroy, and work with replication slots. + + + pg_rotate_logfile + Request logfile rotation + + + pg_signal_backend + Send signals to other backends (eg: cancel query, terminate) + + + pg_file_settings + Allowed to view config settings from all config files + + + pg_admin + + Granted pg_backup, pg_monitor, pg_reply, pg_replication, + pg_rotate_logfile, pg_signal_backend and pg_file_settings roles. + + + + +
+ + + Administrators can grant access to these roles to users using the GRANT + command: + + +GRANT pg_backup TO backup_user; +GRANT pg_monitor TO nagios; +GRANT pg_admin TO admin_user; + + + + + 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. + + + + Function and Trigger Security 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 #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 = ""; - 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 = ""; else if (*(beentry->st_activity) == '\0') activity = ""; @@ -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