On Wed, Sep 30, 2015 at 8:11 PM, Stephen Frost <sfr...@snowman.net> wrote:
> * Heikki Linnakangas (hlinn...@iki.fi) wrote:
>> I agree with Robert's earlier point that this needs to be split into
>> multiple patches, which can then be reviewed and discussed
>> separately. Pending that, I'm going to mark this as "Waiting on
>> author" in the commitfest.
>
> Attached is an initial split which divides up reserving the role names
> from actually adding the initial set of default roles.
I have begun looking at this patch, and here are some comments after
screening it.
Patch needs a rebase, some catalog OIDs and there was a conflict in misc.c
(see attached for the rebase. none of the comments mentioning issues are
fixed by it).
=# grant pg_replay to pg_backup ;
GRANT ROLE
=# \du pg_backup
List of roles
Role name | Attributes | Member of
-----------+--------------+-------------
pg_backup | Cannot login | {pg_replay}
Perhaps we should restrict granting a default role to another default role?
+ <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>
This bit could be perhaps applied as a separate patch.
+ <row>
+ <entry>pg_backup</entry>
+ <entry>Start and stop backups, switch xlogs, and create restore
points.</entry>
+ </row>
s/switch xlogs/switch to a new transaction log file/
It seems weird to not have a dedicated role for pg_switch_xlog.
In func.sgml, the descriptions of pg_switch_xlog, pg_xlog_replay_pause and
pg_xlog_replay_resume still mention that those functions are restricted
only to superusers. The documentation needs an update. It would be good to
double-check if there are similar mistakes for the other functions.
+ <entry>pg_montior</entry>
Typo, pg_monitor.
+ <entry>Pause and resume xlog replay on replicas.</entry>
Those descriptions should be complete sentences or not? Both styles are
mixed in user-manag.sgml.
+ <row>
+ <entry>pg_replication</entry>
+ <entry>Create, destroy, and work with replication slots.</entry>
+ </row>
pg_replication_slots would be more adapted?
+ <row>
+ <entry>pg_file_settings</entry>
+ <entry>Allowed to view config settings from all config files</entry>
+ </row>
I would say "configuration files" instead. An abbreviation is not adapted.
+ <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>
Typo, s/pg_reply/pg_replay and I think that there should be <literal>
markups around those role names. I am actually not convinced that we
actually need it.
+ if (IsReservedName(stmt->role))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved",
+ stmt->role),
+ errdetail("Role names starting with
\"pg_\" are reserved.")));
Perhaps this could be a separate change? It seems that restricting roles
with pg_ on the cluster is not a bad restriction in any case. You may want
to add regression tests to trigger those errors as well.
Shouldn't pg_current_xlog_location, pg_current_xlog_insert_location,
pg_last_xlog_receive_location and pg_last_xlog_replay_location fall under a
restriction category like pg_monitor? I recall of course that we discussed
that some months ago and that a lot of people were reluctant to harden this
area to not break any existing monitoring tool, still this patch may be the
occasion to restrict a bit those functions, particularly on servers where
wal_compression is enabled.
It would be nice to have regression tests as well to check that role
restrictions are applied where they should.
Regards,
--
Michael
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 97ef618..89d3ae0 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 1952cac..66e2493 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -472,6 +472,97 @@ DROP ROLE doomed_role;
</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 81ccebf..184aa7d 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.6, this is also true
+ * for roles.
*/
bool
IsReservedName(const char *name)
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index 295e0b0..fde8d15 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"
@@ -312,6 +313,17 @@ CreateRole(CreateRoleStmt *stmt)
}
/*
+ * Check that the user is not trying to create a role is the reserved
+ * "pg_" namespace.
+ */
+ if (IsReservedName(stmt->role))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved",
+ stmt->role),
+ errdetail("Role names starting with \"pg_\" are reserved.")));
+
+ /*
* Check the pg_authid relation to be certain the role doesn't already
* exist.
*/
@@ -1117,6 +1129,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);
@@ -1136,6 +1149,7 @@ RenameRole(const char *oldname, const char *newname)
*/
roleid = HeapTupleGetOid(oldtuple);
+ authform = (Form_pg_authid) GETSTRUCT(oldtuple);
if (roleid == GetSessionUserId())
ereport(ERROR,
@@ -1146,6 +1160,24 @@ RenameRole(const char *oldname, const char *newname)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("current user cannot be renamed")));
+ /*
+ * Check that the user is not trying to rename a system role and
+ * not trying to rename a role into the reserved "pg_" namespace.
+ */
+ if (IsReservedName(NameStr(authform->rolname)))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved",
+ NameStr(authform->rolname)),
+ errdetail("Role names starting with \"pg_\" are reserved.")));
+
+ if (IsReservedName(newname))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved",
+ newname),
+ errdetail("Role names starting with \"pg_\" are reserved.")));
+
/* make sure the new name doesn't exist */
if (SearchSysCacheExists1(AUTHNAME, CStringGetDatum(newname)))
ereport(ERROR,
diff --git a/src/backend/replication/logical/logicalfuncs.c b/src/backend/replication/logical/logicalfuncs.c
index 012987a..968ad93 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 b3c8140..421c6ed 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.
@@ -52,7 +45,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();
@@ -110,7 +107,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();
@@ -159,7 +160,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 4a4643e..bf0194c 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"
@@ -2808,11 +2810,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 3ef6e43..6da052b 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 canceled"))));
+ (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 f7c9bf6..8cac7c2 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 a185749..e28eae3 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"
@@ -8249,6 +8251,11 @@ show_all_file_settings(PG_FUNCTION_ARGS)
MemoryContext per_query_ctx;
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")));
+
/* Check to see if caller supports us returning a tuplestore */
if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
ereport(ERROR,
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 3461335..addabd0 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -673,6 +673,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,
@@ -895,6 +896,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 41d4606..d115b2a 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -24,6 +24,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);
@@ -98,6 +99,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.5 and below should not have roles starting with pg_ */
+ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 905)
+ 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);
@@ -613,7 +619,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)
@@ -629,7 +636,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");
@@ -1017,6 +1025,34 @@ check_for_jsonb_9_4_usage(ClusterInfo *cluster)
check_ok();
}
+/*
+ * check_for_pg_role_prefix()
+ *
+ * Versions older than 9.6 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 72c00c1..3e7cc3f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -431,7 +431,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();
@@ -476,7 +476,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 92ed6e2..488693a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2647,7 +2647,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;
@@ -2692,6 +2692,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 5b63e76..ff60a85 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -227,7 +227,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"));
@@ -240,7 +240,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 2c8565e..16e3474 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -95,8 +95,25 @@ typedef FormData_pg_authid *Form_pg_authid;
* user choices.
* ----------------
*/
-DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_ _null_));
+DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_ _null_));
+DATA(insert OID = 3317 ( "pg_admin" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3318 ( "pg_monitor" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3319 ( "pg_backup" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3320 ( "pg_replay" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3321 ( "pg_replication" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3322 ( "pg_rotate_logfile" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3323 ( "pg_signal_backend" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 3324 ( "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 3317
+#define DEFAULT_ROLE_MONITORID 3318
+#define DEFAULT_ROLE_BACKUPID 3319
+#define DEFAULT_ROLE_REPLAYID 3320
+#define DEFAULT_ROLE_REPLICATIONID 3321
+#define DEFAULT_ROLE_ROTATE_LOGFILEID 3322
+#define DEFAULT_ROLE_SIGNAL_BACKENDID 3323
+#define DEFAULT_ROLE_FILE_SETTINGSID 3324
#endif /* PG_AUTHID_H */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
