Michael, Thanks for the review!
* Michael Paquier (michael.paqu...@gmail.com) wrote:
> 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).
Done (did it a bit differently from what you had, to hopefully avoid
future OID conflicts and also to allow us a bit of room to add
additional default roles later, if we choose, in nearby OID space).
> =# 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?
Done (in the second patch in the series, the one reserving 'pg_').
> + <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.
Done as a separate patch (first one in the series).
> + <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/
Fixed.
> It seems weird to not have a dedicated role for pg_switch_xlog.
I didn't add a pg_switch_xlog default role in this patch series, but
would be happy to do so if that's the consensus. It's quite easy to do.
> 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.
Fixed. Also did a review and found a number of other places which
required updating, so those have also been fixed.
> + <entry>pg_montior</entry>
> Typo, pg_monitor.
Fixed.
> + <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.
I didn't take any action on this.
> + <row>
> + <entry>pg_replication</entry>
> + <entry>Create, destroy, and work with replication slots.</entry>
> + </row>
> pg_replication_slots would be more adapted?
Perhaps... I didn't make this change, but if others agree that adding
"_slots" would help, I'd be happy to do so. Personally, I'd like to
keep these names shorter, if possible, but I don't want it to be
confusing either.
> + <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.
Done.
> + <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.
I added <literal> markups around the role names, where used.
I'm guessing you were referring to pg_admin with your comment that you
were "not convinced that we actually need it." After thinking about
it for a bit, I tend to agree. A user could certainly create their own
role which combines these all together if they wanted to (or do any
other combinations, based on their particular needs). I've gone ahead
and removed pg_admin from the set of default roles.
> + 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.
I'm a bit confused- this is a separate change. Note that the previous
patch was actually a git patchset which had two patches- one to do the
reservation and a second to add the default roles. The attached
patchset is actually three patches:
1- Update to catalog documentation regarding permissions
2- Reserve 'pg_' role namespace
3- Add default roles
> 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.
For my 2c, I believe they should. I've not modified them in that way in
this patchset, but I can certainly do so if others agree.
> It would be nice to have regression tests as well to check that role
> restrictions are applied where they should.
I've added regression tests for the default roles where it was
relatively straight-forward to do so. I don't see a trivial way to test
that the pg_signal_backend role works though, as an example.
> Bonus:
> -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"))));
> -}
> I would have let check_permissions and directly done the checks on
> has_rolreplication and has_privs_of_role in it, the same code being
> duplicated three times.
For my 2c, I dislike the notion of "check_permissions()" functions being
added throughout the codebase as I'm afraid it'd get confusing, which is
why I got rid of it. I'm much happier seeing the actual permissions
check as it should be happening early on in the primary function which
is being called into. If there is really a push to go back to having a
'check_permissions()' like function, I'd argue that we should make the
function name more descriptive of what's actually being done and have
them be distinct from each other. As I recall, I discussed this change
with Andres and he didn't feel particularly strongly about this one way
or the other, therefore, I've not made this change for this patchset.
Thanks!
Stephen
From 20b942a6957c6cbe54c92b0eda09ab08a4b3bc04 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Wed, 18 Nov 2015 11:50:57 -0500
Subject: [PATCH 1/3] Add note regarding permissions in pg_catalog
Add a note to the system catalog section pointing out that while
modifying the permissions on catalog tables is possible, it's
unlikely to have the desired effect.
---
doc/src/sgml/catalogs.sgml | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 97ef618..3b7768c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -21,6 +21,17 @@
particularly esoteric operations, such as adding index access methods.
</para>
+ <note>
+ <para>
+ 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>
+ </note>
+
<sect1 id="catalogs-overview">
<title>Overview</title>
--
2.5.0
From 20ddd329e486bdb23ee96e0c1956c7d45596781a Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Wed, 30 Sep 2015 07:04:55 -0400
Subject: [PATCH 2/3] Reserve the "pg_" namespace for roles
This will prevent users from creating roles which being with "pg_" and
will check for those roles before allowing an upgrade using pg_upgrade.
This will allow for default roles to be provided at initdb time.
---
src/backend/catalog/catalog.c | 5 +++--
src/backend/commands/user.c | 38 ++++++++++++++++++++++++++++++++++++++
src/backend/utils/adt/acl.c | 26 ++++++++++++++++++++++++++
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/utils/acl.h | 1 +
10 files changed, 117 insertions(+), 10 deletions(-)
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..b5d23f3 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,
@@ -1224,10 +1256,16 @@ GrantRole(GrantRoleStmt *stmt)
ListCell *item;
if (stmt->grantor)
+ {
+ check_rolespec_name(stmt->grantor);
grantor = get_rolespec_oid(stmt->grantor, false);
+ }
else
grantor = GetUserId();
+ foreach(item, stmt->grantee_roles)
+ check_rolespec_name(lfirst(item));
+
grantee_ids = roleSpecsToIds(stmt->grantee_roles);
/* AccessShareLock is enough since we aren't modifying pg_authid */
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 3ca168b..fdbe2d4 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -17,6 +17,7 @@
#include <ctype.h>
#include "access/htup_details.h"
+#include "catalog/catalog.h"
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_auth_members.h"
@@ -5247,3 +5248,28 @@ get_rolespec_name(const Node *node)
return rolename;
}
+
+/*
+ * Given a RoleSpec, throw an error if the name is reserved.
+ */
+void
+check_rolespec_name(const Node *node)
+{
+ RoleSpec *role;
+
+ role = (RoleSpec *) node;
+ if (!IsA(node, RoleSpec))
+ elog(ERROR, "invalid node type %d", node->type);
+
+ if (role->roletype != ROLESPEC_CSTRING)
+ return;
+
+ if (IsReservedName(role->rolename))
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role \"%s\" is reserved",
+ role->rolename),
+ errdetail("Roles starting with \"pg_\" are reserved.")));
+
+ return;
+}
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/utils/acl.h b/src/include/utils/acl.h
index 915ea39..7bad9fe 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -231,6 +231,7 @@ extern void check_is_member_of_role(Oid member, Oid role);
extern Oid get_role_oid(const char *rolename, bool missing_ok);
extern Oid get_role_oid_or_public(const char *rolename);
extern Oid get_rolespec_oid(const Node *node, bool missing_ok);
+extern void check_rolespec_name(const Node *node);
extern HeapTuple get_rolespec_tuple(const Node *node);
extern char *get_rolespec_name(const Node *node);
--
2.5.0
From d24fb4d2d74cfba8519b3d63a924e73f60ad859d Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Wed, 30 Sep 2015 07:08:03 -0400
Subject: [PATCH 3/3] Create default roles
---
contrib/test_decoding/expected/permissions.out | 8 +-
doc/src/sgml/func.sgml | 23 ++---
doc/src/sgml/user-manag.sgml | 83 +++++++++++++++++
src/backend/access/transam/xlogfuncs.c | 30 ++++---
src/backend/catalog/system_views.sql | 2 +
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/include/catalog/pg_authid.h | 20 ++++-
src/test/regress/expected/privileges.out | 120 +++++++++++++++++++++++++
src/test/regress/expected/rolenames.out | 18 ++++
src/test/regress/sql/privileges.sql | 78 ++++++++++++++++
src/test/regress/sql/rolenames.sql | 8 ++
16 files changed, 423 insertions(+), 65 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/func.sgml b/doc/src/sgml/func.sgml
index 60b9a09..5c110cc 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16678,7 +16678,8 @@ SELECT set_config('log_statement_stats', 'off', false);
</entry>
<entry><type>boolean</type></entry>
<entry>Cancel a backend's current query. This is also allowed if the
- calling role is a member of the role whose backend is being canceled,
+ calling role is a member of the role whose backend is being canceled or
+ the calling role has been granted <literal>pg_signal_backend</literal>,
however only superusers can cancel superuser backends.
</entry>
</row>
@@ -16702,8 +16703,9 @@ SELECT set_config('log_statement_stats', 'off', false);
</entry>
<entry><type>boolean</type></entry>
<entry>Terminate a backend. This is also allowed if the calling role
- is a member of the role whose backend is being terminated, however only
- superusers can terminate superuser backends.
+ is a member of the role whose backend is being terminated or the
+ calling role has been granted <literal>pg_signal_backend</literal>,
+ however only superusers can terminate superuser backends.
</entry>
</row>
</tbody>
@@ -16807,7 +16809,7 @@ SELECT set_config('log_statement_stats', 'off', false);
<literal><function>pg_create_restore_point(<parameter>name</> <type>text</>)</function></literal>
</entry>
<entry><type>pg_lsn</type></entry>
- <entry>Create a named point for performing restore (restricted to superusers)</entry>
+ <entry>Create a named point for performing restore (restricted to superusers or <literal>pg_backup</literal> roles)</entry>
</row>
<row>
<entry>
@@ -16828,14 +16830,14 @@ SELECT set_config('log_statement_stats', 'off', false);
<literal><function>pg_start_backup(<parameter>label</> <type>text</> <optional>, <parameter>fast</> <type>boolean</> </optional>)</function></literal>
</entry>
<entry><type>pg_lsn</type></entry>
- <entry>Prepare for performing on-line backup (restricted to superusers or replication roles)</entry>
+ <entry>Prepare for performing on-line backup (restricted to superusers, <literal>pg_backup</literal> or <literal>pg_replication</literal> roles)</entry>
</row>
<row>
<entry>
<literal><function>pg_stop_backup()</function></literal>
</entry>
<entry><type>pg_lsn</type></entry>
- <entry>Finish performing on-line backup (restricted to superusers or replication roles)</entry>
+ <entry>Finish performing on-line backup (restricted to superusers, <literal>pg_backup</literal> or <literal>pg_replication</literal> roles)</entry>
</row>
<row>
<entry>
@@ -16856,7 +16858,7 @@ SELECT set_config('log_statement_stats', 'off', false);
<literal><function>pg_switch_xlog()</function></literal>
</entry>
<entry><type>pg_lsn</type></entry>
- <entry>Force switch to a new transaction log file (restricted to superusers)</entry>
+ <entry>Force switch to a new transaction log file (restricted to superusers or <literal>pg_backup</literal> roles)</entry>
</row>
<row>
<entry>
@@ -17112,7 +17114,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
<literal><function>pg_xlog_replay_pause()</function></literal>
</entry>
<entry><type>void</type></entry>
- <entry>Pauses recovery immediately (restricted to superusers).
+ <entry>Pauses recovery immediately (restricted to superusers or <literal>pg_replay</literal> roles).
</entry>
</row>
<row>
@@ -17120,7 +17122,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
<literal><function>pg_xlog_replay_resume()</function></literal>
</entry>
<entry><type>void</type></entry>
- <entry>Restarts recovery if it was paused (restricted to superusers).
+ <entry>Restarts recovery if it was paused (restricted to superusers or <literal>pg_replay</literal> roles).
</entry>
</row>
</tbody>
@@ -17230,7 +17232,8 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
See <xref linkend="streaming-replication">,
<xref linkend="streaming-replication-slots">, <xref linkend="replication-origins">
for information about the underlying features. Use of these
- functions is restricted to superusers.
+ functions is restricted to superuser or <literal>pg_replication</literal>
+ roles.
</para>
<para>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 1952cac..89ac5c2 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -472,6 +472,89 @@ 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 to a new transaction log file, and create restore points.</entry>
+ </row>
+ <row>
+ <entry>pg_monitor</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 configuration files</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;
+</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/system_views.sql b/src/backend/catalog/system_views.sql
index ccc030f..7457f98 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -418,6 +418,8 @@ CREATE VIEW pg_file_settings AS
REVOKE ALL on pg_file_settings FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION pg_show_all_file_settings() FROM PUBLIC;
+GRANT SELECT on pg_file_settings TO pg_file_settings;
+GRANT EXECUTE ON FUNCTION pg_show_all_file_settings() TO pg_file_settings;
CREATE VIEW pg_timezone_abbrevs AS
SELECT * FROM pg_timezone_abbrevs();
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..c320966 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 canceled 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/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index 2c8565e..693f0b6 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -93,10 +93,28 @@ typedef FormData_pg_authid *Form_pg_authid;
*
* The uppercase quantities will be replaced at initdb time with
* user choices.
+ *
+ * If adding new default roles or changing the OIDs below, be sure to add or
+ * update the #defines which follow as appropriate.
* ----------------
*/
DATA(insert OID = 10 ( "POSTGRES" t t t t t t t -1 _null_ _null_));
+DATA(insert OID = 4201 ( "pg_monitor" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4202 ( "pg_backup" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4203 ( "pg_replay" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4204 ( "pg_replication" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4205 ( "pg_rotate_logfile" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4206 ( "pg_signal_backend" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4207 ( "pg_file_settings" f t f f f f f -1 _null_ _null_));
+
+#define BOOTSTRAP_SUPERUSERID 10
-#define BOOTSTRAP_SUPERUSERID 10
+#define DEFAULT_ROLE_MONITORID 4201
+#define DEFAULT_ROLE_BACKUPID 4202
+#define DEFAULT_ROLE_REPLAYID 4203
+#define DEFAULT_ROLE_REPLICATIONID 4204
+#define DEFAULT_ROLE_ROTATE_LOGFILEID 4205
+#define DEFAULT_ROLE_SIGNAL_BACKENDID 4206
+#define DEFAULT_ROLE_FILE_SETTINGSID 4207
#endif /* PG_AUTHID_H */
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 88bdc2c..7e02b36 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1523,6 +1523,126 @@ revoke select on dep_priv_test from regressuser4 cascade;
set session role regressuser1;
drop table dep_priv_test;
+-- test default roles
+-- pg_backup
+\c
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup or pg_replication to run a backup
+SELECT pg_stop_backup(); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup or pg_replication to run a backup
+SELECT pg_switch_xlog(); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup to switch transaction log files
+\c
+GRANT pg_backup TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail
+ERROR: WAL level not sufficient for making an online backup
+HINT: wal_level must be set to "archive", "hot_standby", or "logical" at server start.
+SELECT pg_stop_backup(); -- fail
+ERROR: WAL level not sufficient for making an online backup
+HINT: wal_level must be set to "archive", "hot_standby", or "logical" at server start.
+SELECT pg_switch_xlog() limit 0; -- success
+ pg_switch_xlog
+----------------
+(0 rows)
+
+\c
+REVOKE pg_backup FROM regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup or pg_replication to run a backup
+SELECT pg_stop_backup(); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup or pg_replication to run a backup
+SELECT pg_switch_xlog(); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup to switch transaction log files
+-- pg_file_settings
+\c
+SET SESSION AUTHORIZATION regressuser1;
+SELECT 1 FROM pg_show_all_file_settings() LIMIT 1; -- fail-no-perm
+ERROR: permission denied for function pg_show_all_file_settings
+SELECT 1 FROM pg_file_settings LIMIT 1; -- fail-no-perm
+ERROR: permission denied for relation pg_file_settings
+\c
+GRANT pg_file_settings TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT 1 FROM pg_show_all_file_settings() LIMIT 1; -- success
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT 1 FROM pg_file_settings LIMIT 1; -- success
+ ?column?
+----------
+ 1
+(1 row)
+
+\c
+REVOKE pg_file_settings FROM regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT 1 FROM pg_show_all_file_settings() LIMIT 1; -- fail-no-perm
+ERROR: permission denied for function pg_show_all_file_settings
+SELECT 1 FROM pg_file_settings LIMIT 1; -- fail-no-perm
+ERROR: permission denied for relation pg_file_settings
+-- pg_replay
+\c
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_xlog_replay_pause(); -- fail-no-perm
+ERROR: must be superuser or member of pg_replay to control recovery
+SELECT pg_xlog_replay_resume(); -- fail-no-perm
+ERROR: must be superuser or member of pg_replay to control recovery
+\c
+GRANT pg_replay TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_xlog_replay_pause(); -- fail
+ERROR: recovery is not in progress
+HINT: Recovery control functions can only be executed during recovery.
+SELECT pg_xlog_replay_resume(); -- fail
+ERROR: recovery is not in progress
+HINT: Recovery control functions can only be executed during recovery.
+\c
+REVOKE pg_replay FROM regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_xlog_replay_pause(); -- fail-no-perm
+ERROR: must be superuser or member of pg_replay to control recovery
+SELECT pg_xlog_replay_resume(); -- fail-no-perm
+ERROR: must be superuser or member of pg_replay to control recovery
+-- pg_replication
+\c
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup or pg_replication to run a backup
+SELECT pg_stop_backup(); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup or pg_replication to run a backup
+SELECT * FROM pg_create_physical_replication_slot('asd',true); -- fail-no-perm
+ERROR: must be superuser or member of pg_replication to use replication slots
+SELECT pg_drop_replication_slot('asd'); -- fail-no-perm
+ERROR: must be superuser or member of pg_replication to use replication slots
+\c
+GRANT pg_replication TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail (not a permission failure)
+ERROR: WAL level not sufficient for making an online backup
+HINT: wal_level must be set to "archive", "hot_standby", or "logical" at server start.
+SELECT pg_stop_backup(); -- fail
+ERROR: WAL level not sufficient for making an online backup
+HINT: wal_level must be set to "archive", "hot_standby", or "logical" at server start.
+SELECT * FROM pg_create_physical_replication_slot('asd',true); -- fail
+ERROR: replication slots can only be used if max_replication_slots > 0
+SELECT pg_drop_replication_slot('asd'); -- fail
+ERROR: replication slots can only be used if max_replication_slots > 0
+\c
+REVOKE pg_replication FROM regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup or pg_replication to run a backup
+SELECT pg_stop_backup(); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup or pg_replication to run a backup
+SELECT * FROM pg_create_physical_replication_slot('asd',true); -- fail-no-perm
+ERROR: must be superuser or member of pg_replication to use replication slots
+SELECT pg_drop_replication_slot('asd'); -- fail-no-perm
+ERROR: must be superuser or member of pg_replication to use replication slots
-- clean up
\c
drop sequence x_seq;
diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out
index 8f88c02..99e6aec 100644
--- a/src/test/regress/expected/rolenames.out
+++ b/src/test/regress/expected/rolenames.out
@@ -78,6 +78,18 @@ CREATE ROLE "none"; -- error
ERROR: role name "none" is reserved
LINE 1: CREATE ROLE "none";
^
+CREATE ROLE pg_abc; -- error
+ERROR: role name "pg_abc" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE "pg_abc"; -- error
+ERROR: role name "pg_abc" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE pg_backup; -- error
+ERROR: role name "pg_backup" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
+CREATE ROLE "pg_backup"; -- error
+ERROR: role name "pg_backup" is reserved
+DETAIL: Role names starting with "pg_" are reserved.
CREATE ROLE testrol0 SUPERUSER LOGIN;
CREATE ROLE testrolx SUPERUSER LOGIN;
CREATE ROLE testrol2 SUPERUSER;
@@ -804,6 +816,12 @@ LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9;
DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error
NOTICE: role "nonexistent" does not exist, skipping
-- GRANT/REVOKE
+GRANT testrol0 TO pg_backup; -- error
+ERROR: role "pg_backup" is reserved
+DETAIL: Roles starting with "pg_" are reserved.
+GRANT pg_backup TO pg_monitor; -- error
+ERROR: role "pg_monitor" is reserved
+DETAIL: Roles starting with "pg_" are reserved.
UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
proname | proacl
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index c1837c4..856f5ec 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -932,6 +932,84 @@ revoke select on dep_priv_test from regressuser4 cascade;
set session role regressuser1;
drop table dep_priv_test;
+-- test default roles
+
+-- pg_backup
+\c
+SET SESSION AUTHORIZATION regressuser1;
+
+SELECT pg_start_backup('abc'); -- fail-no-perm
+SELECT pg_stop_backup(); -- fail-no-perm
+SELECT pg_switch_xlog(); -- fail-no-perm
+\c
+GRANT pg_backup TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail
+SELECT pg_stop_backup(); -- fail
+SELECT pg_switch_xlog() limit 0; -- success
+\c
+REVOKE pg_backup FROM regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail-no-perm
+SELECT pg_stop_backup(); -- fail-no-perm
+SELECT pg_switch_xlog(); -- fail-no-perm
+
+-- pg_file_settings
+\c
+SET SESSION AUTHORIZATION regressuser1;
+
+SELECT 1 FROM pg_show_all_file_settings() LIMIT 1; -- fail-no-perm
+SELECT 1 FROM pg_file_settings LIMIT 1; -- fail-no-perm
+\c
+GRANT pg_file_settings TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT 1 FROM pg_show_all_file_settings() LIMIT 1; -- success
+SELECT 1 FROM pg_file_settings LIMIT 1; -- success
+\c
+REVOKE pg_file_settings FROM regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT 1 FROM pg_show_all_file_settings() LIMIT 1; -- fail-no-perm
+SELECT 1 FROM pg_file_settings LIMIT 1; -- fail-no-perm
+
+-- pg_replay
+\c
+SET SESSION AUTHORIZATION regressuser1;
+
+SELECT pg_xlog_replay_pause(); -- fail-no-perm
+SELECT pg_xlog_replay_resume(); -- fail-no-perm
+\c
+GRANT pg_replay TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_xlog_replay_pause(); -- fail
+SELECT pg_xlog_replay_resume(); -- fail
+\c
+REVOKE pg_replay FROM regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_xlog_replay_pause(); -- fail-no-perm
+SELECT pg_xlog_replay_resume(); -- fail-no-perm
+
+-- pg_replication
+\c
+SET SESSION AUTHORIZATION regressuser1;
+
+SELECT pg_start_backup('abc'); -- fail-no-perm
+SELECT pg_stop_backup(); -- fail-no-perm
+SELECT * FROM pg_create_physical_replication_slot('asd',true); -- fail-no-perm
+SELECT pg_drop_replication_slot('asd'); -- fail-no-perm
+\c
+GRANT pg_replication TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail (not a permission failure)
+SELECT pg_stop_backup(); -- fail
+SELECT * FROM pg_create_physical_replication_slot('asd',true); -- fail
+SELECT pg_drop_replication_slot('asd'); -- fail
+\c
+REVOKE pg_replication FROM regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_start_backup('abc'); -- fail-no-perm
+SELECT pg_stop_backup(); -- fail-no-perm
+SELECT * FROM pg_create_physical_replication_slot('asd',true); -- fail-no-perm
+SELECT pg_drop_replication_slot('asd'); -- fail-no-perm
-- clean up
diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql
index e8c6b33..65c97ec 100644
--- a/src/test/regress/sql/rolenames.sql
+++ b/src/test/regress/sql/rolenames.sql
@@ -57,6 +57,11 @@ CREATE ROLE "public"; -- error
CREATE ROLE none; -- error
CREATE ROLE "none"; -- error
+CREATE ROLE pg_abc; -- error
+CREATE ROLE "pg_abc"; -- error
+CREATE ROLE pg_backup; -- error
+CREATE ROLE "pg_backup"; -- error
+
CREATE ROLE testrol0 SUPERUSER LOGIN;
CREATE ROLE testrolx SUPERUSER LOGIN;
CREATE ROLE testrol2 SUPERUSER;
@@ -376,6 +381,9 @@ DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error
DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error
-- GRANT/REVOKE
+GRANT testrol0 TO pg_backup; -- error
+GRANT pg_backup TO pg_monitor; -- error
+
UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
--
2.5.0
signature.asc
Description: Digital signature
