* Michael Paquier (michael.paqu...@gmail.com) wrote: > On Thu, Nov 19, 2015 at 7:10 AM, Stephen Frost wrote: > > * Michael Paquier (michael.paqu...@gmail.com) wrote: > >> 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. > > Agreed. I am not actually getting why that's part of the backup > actually. That would be more related to archiving, both being > unrelated concepts. But at this point I guess that's mainly a > philosophical split.
As David notes, they're actually quite related. Note that in our
documentation pg_switch_xlog() is listed in the "Backup Control
Functions" table.
I can think of a use-case for a user who can call pg_switch_xlog, but
not pg_start_backup()/pg_stop_backup(), but I have to admit that it
seems rather limited and I'm on the fence about it being a worthwhile
distinction.
Even so, in the interest of having more fine-grained permission
controls, I've gone ahead and added a pg_switch_xlog default role.
Note that this means that pg_switch_xlog() can be called by both
pg_switch_xlog roles and pg_backup roles. I'd be very much against
removing the ability to call pg_switch_xlog from the pg_backup role as
that really is a capability which is needed by users running backups and
it'd just add unnecessary complexity to require users setting up backup
tools to grant two different roles to get the backup to work.
> > 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
>
> I see, that's why I got confused. I just downloaded your file and
> applied it blindly with git apply or patch (don't recall which). Other
> folks tend to publish that as a separate set of files generated by
> git-format-patch.
The file was generated using 'git format-patch', but sent to one file
instead of multiple. 'git am' understands that format and will add the
independent commits to the current branch. Note that the git
documentation (see 'man git-format-patch' and 'man git-apply', at least
on Debian-based systems) specifically recommends using 'git am' to
create commits from patches generated by 'git format-patch'.
The workflow you're describing would require saving off each patch,
doing a 'patch' or 'git apply' run for each, then committing the changes
with your own commit message and only then would you have the entire
series. That doesn't seem like a better approach.
> >> 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.
>
> My vote goes into hardening them a bit this way.
I've made those changes in this patch set. Note that these functions
can now only be called by roles which are members of pg_monitor,
pg_backup, or pg_switch_xlog (or superuser, of course).
> > 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.
>
> It is at least possible to check that the error code paths are
> working.
Perhaps, but...
> For the code paths where a backend would be actually running,
> you could use the following:
> SET client_min_messages TO 'error';
> -- This should return "1" and not an ERROR nor a WARNING if PID does not
> exist.
> select count(pg_terminate_backend(0));
> But that's ugly depending on your taste.
I really dislike that.
> Do you think that it makes sense to add tests as well in the second
> patch to check that restrictions pg_* are in place? Except that I
> don't have much more to say about patches 1 and 2 which are rather
> straight-forward.
Ah, yes. I've now moved those hunks to the second patch where they
really should have been.
> Regarding patch 3, the documentation of psql should mention the new
> subommands \dgS and \dgS+. That's a one-liner.
Ah, right. I've updated the psql SGML documentation for \duS and \dgS.
The '\h' output had already been updated. Was there something else
which you meant above that I've missed? Note that these fixes went into
the second patch.
> +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.
> make install would wal on a server with something else than wal_level
> = minimal. Just checking that errors kick correctly looks fine to me
> here.
I've removed those checks as they could get annoying on the buildfarm or
for people doing make installcheck, as you say, but I'm not really
thrilled that we're only testing the failure paths.
> +GRANT pg_backup TO pg_monitor; -- error
> +ERROR: role "pg_monitor" is reserved
> +DETAIL: Roles starting with "pg_" are reserved.
> +GRANT testrol0 TO pg_backup; -- error
> +ERROR: role "pg_backup" is reserved
> We would gain with verbose error messages here, like, "cannot GRANT
> somerole to a system role", and "cannot GRANT system role to
> somerole".
Alright, I've changed these to have better detail messages.
Updated patchset attached.
Thanks!
Stephen
From 141fce786f3fdb7e5cb16d9aaa0c81f8b6caa6d7 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 0a5df0084d4411e82835be67606bbd0109cee1e6 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.
---
doc/src/sgml/ref/psql-ref.sgml | 8 +++++--
src/backend/catalog/catalog.c | 5 +++--
src/backend/commands/user.c | 40 +++++++++++++++++++++++++++++++++
src/backend/utils/adt/acl.c | 39 ++++++++++++++++++++++++++++++++
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 +
src/test/regress/expected/rolenames.out | 18 +++++++++++++++
src/test/regress/sql/rolenames.sql | 8 +++++++
13 files changed, 164 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 5899bb4..9f6742b 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1336,13 +1336,15 @@ testdb=>
<varlistentry>
- <term><literal>\dg[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dgS[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists database roles.
(Since the concepts of <quote>users</> and <quote>groups</> have been
unified into <quote>roles</>, this command is now equivalent to
<literal>\du</literal>.)
+ By default, only user-created roles are shown; supply the
+ <literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
If the form <literal>\dg+</literal> is used, additional information
@@ -1496,13 +1498,15 @@ testdb=>
</varlistentry>
<varlistentry>
- <term><literal>\du[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\duS[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
Lists database roles.
(Since the concepts of <quote>users</> and <quote>groups</> have been
unified into <quote>roles</>, this command is now equivalent to
<literal>\dg</literal>.)
+ By default, only user-created roles are shown; supply the
+ <literal>S</literal> modifier to include system roles.
If <replaceable class="parameter">pattern</replaceable> is specified,
only those roles whose names match the pattern are listed.
If the form <literal>\du+</literal> is used, additional information
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..22c8d3e 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,18 @@ GrantRole(GrantRoleStmt *stmt)
ListCell *item;
if (stmt->grantor)
+ {
+ check_rolespec_name(stmt->grantor,
+ "Cannot specify reserved role as grantor.");
grantor = get_rolespec_oid(stmt->grantor, false);
+ }
else
grantor = GetUserId();
+ foreach(item, stmt->grantee_roles)
+ check_rolespec_name(lfirst(item),
+ "Cannot GRANT roles to a reserved role.");
+
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..be4ad45 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,41 @@ get_rolespec_name(const Node *node)
return rolename;
}
+
+/*
+ * Given a RoleSpec, throw an error if the name is reserved, using errdetail.
+ *
+ * Node and errdetail must be provided.
+ */
+void
+check_rolespec_name(const Node *node, const char *detail_msg)
+{
+ RoleSpec *role;
+
+ if (!node)
+ return;
+
+ 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))
+ {
+ if (detail_msg)
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role \"%s\" is reserved",
+ role->rolename),
+ errdetail("%s", detail_msg)));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role \"%s\" is reserved",
+ role->rolename)));
+ }
+
+ 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..5fccd88 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, const char *detail_msg);
extern HeapTuple get_rolespec_tuple(const Node *node);
extern char *get_rolespec_name(const Node *node);
diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out
index 8f88c02..c9be282 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: Cannot GRANT roles to a reserved role.
+GRANT pg_backup TO pg_monitor; -- error
+ERROR: role "pg_monitor" is reserved
+DETAIL: Cannot GRANT roles to a reserved role.
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/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
From 7f5ffcb6940a859e485807345ebe82f34d5780a0 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 | 59 ++++++--
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 | 22 ++-
src/test/regress/expected/privileges.out | 185 +++++++++++++++++++++++++
src/test/regress/sql/privileges.sql | 103 ++++++++++++++
14 files changed, 518 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..5819377 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,11 @@ pg_switch_xlog(PG_FUNCTION_ARGS)
{
XLogRecPtr switchpoint;
- if (!superuser())
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_SWITCH_XLOGID))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg("must be superuser to switch transaction log files"))));
+ errmsg("must be superuser or member of pg_backup or pg_switch_xlog to switch transaction log files")));
if (RecoveryInProgress())
ereport(ERROR,
@@ -139,10 +146,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,
@@ -183,6 +190,13 @@ pg_current_xlog_location(PG_FUNCTION_ARGS)
{
XLogRecPtr current_recptr;
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_SWITCH_XLOGID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files")));
+
if (RecoveryInProgress())
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -204,6 +218,13 @@ pg_current_xlog_insert_location(PG_FUNCTION_ARGS)
{
XLogRecPtr current_recptr;
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_SWITCH_XLOGID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files")));
+
if (RecoveryInProgress())
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -226,6 +247,13 @@ pg_last_xlog_receive_location(PG_FUNCTION_ARGS)
{
XLogRecPtr recptr;
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_SWITCH_XLOGID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files")));
+
recptr = GetWalRcvWriteRecPtr(NULL, NULL);
if (recptr == 0)
@@ -245,6 +273,13 @@ pg_last_xlog_replay_location(PG_FUNCTION_ARGS)
{
XLogRecPtr recptr;
+ if (!has_privs_of_role(GetUserId(), DEFAULT_ROLE_BACKUPID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_MONITORID) &&
+ !has_privs_of_role(GetUserId(), DEFAULT_ROLE_SWITCH_XLOGID))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files")));
+
recptr = GetXLogReplayRecPtr(NULL);
if (recptr == 0)
@@ -348,10 +383,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 +405,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..d285536 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -93,10 +93,30 @@ 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 = 4200 ( "pg_monitor" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4201 ( "pg_backup" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4202 ( "pg_replay" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4203 ( "pg_replication" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4204 ( "pg_rotate_logfile" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4205 ( "pg_signal_backend" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4206 ( "pg_file_settings" f t f f f f f -1 _null_ _null_));
+DATA(insert OID = 4207 ( "pg_switch_xlog" f t f f f f f -1 _null_ _null_));
+
+#define BOOTSTRAP_SUPERUSERID 10
-#define BOOTSTRAP_SUPERUSERID 10
+#define DEFAULT_ROLE_MONITORID 4200
+#define DEFAULT_ROLE_BACKUPID 4201
+#define DEFAULT_ROLE_REPLAYID 4202
+#define DEFAULT_ROLE_REPLICATIONID 4203
+#define DEFAULT_ROLE_ROTATE_LOGFILEID 4204
+#define DEFAULT_ROLE_SIGNAL_BACKENDID 4205
+#define DEFAULT_ROLE_FILE_SETTINGSID 4206
+#define DEFAULT_ROLE_SWITCH_XLOGID 4207
#endif /* PG_AUTHID_H */
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 88bdc2c..1a5c1ae 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1523,6 +1523,191 @@ 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 or pg_switch_xlog to switch transaction log files
+SELECT pg_current_xlog_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+SELECT pg_current_xlog_insert_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+SELECT pg_last_xlog_receive_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+SELECT pg_last_xlog_replay_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+\c
+GRANT pg_backup TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_switch_xlog() limit 0; -- success
+ pg_switch_xlog
+----------------
+(0 rows)
+
+SELECT pg_current_xlog_location() limit 0; -- success
+ pg_current_xlog_location
+--------------------------
+(0 rows)
+
+SELECT pg_current_xlog_insert_location() limit 0; -- success
+ pg_current_xlog_insert_location
+---------------------------------
+(0 rows)
+
+SELECT pg_last_xlog_receive_location() limit 0; -- success
+ pg_last_xlog_receive_location
+-------------------------------
+(0 rows)
+
+SELECT pg_last_xlog_replay_location() limit 0; -- success
+ pg_last_xlog_replay_location
+------------------------------
+(0 rows)
+
+\c
+REVOKE pg_backup FROM regressuser1;
+-- 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;
+-- pg_monitor
+\c
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_current_xlog_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+SELECT pg_current_xlog_insert_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+SELECT pg_last_xlog_receive_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+SELECT pg_last_xlog_replay_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+\c
+GRANT pg_monitor TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_current_xlog_location() limit 0; -- success
+ pg_current_xlog_location
+--------------------------
+(0 rows)
+
+SELECT pg_current_xlog_insert_location() limit 0; -- success
+ pg_current_xlog_insert_location
+---------------------------------
+(0 rows)
+
+SELECT pg_last_xlog_receive_location() limit 0; -- success
+ pg_last_xlog_receive_location
+-------------------------------
+(0 rows)
+
+SELECT pg_last_xlog_replay_location() limit 0; -- success
+ pg_last_xlog_replay_location
+------------------------------
+(0 rows)
+
+\c
+REVOKE pg_monitor FROM regressuser1;
+-- 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;
+-- 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;
+\c
+REVOKE pg_replication FROM regressuser1;
+-- pg_switch_xlog
+\c
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_switch_xlog(); -- fail-no-perm
+ERROR: must be superuser or member of pg_backup or pg_switch_xlog to switch transaction log files
+SELECT pg_current_xlog_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+SELECT pg_current_xlog_insert_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+SELECT pg_last_xlog_receive_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+SELECT pg_last_xlog_replay_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor, pg_backup, or pg_switch_xlog to switch transaction log files
+\c
+GRANT pg_switch_xlog TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_switch_xlog() limit 0; -- success
+ pg_switch_xlog
+----------------
+(0 rows)
+
+SELECT pg_current_xlog_location() limit 0; -- success
+ pg_current_xlog_location
+--------------------------
+(0 rows)
+
+SELECT pg_current_xlog_insert_location() limit 0; -- success
+ pg_current_xlog_insert_location
+---------------------------------
+(0 rows)
+
+SELECT pg_last_xlog_receive_location() limit 0; -- success
+ pg_last_xlog_receive_location
+-------------------------------
+(0 rows)
+
+SELECT pg_last_xlog_replay_location() limit 0; -- success
+ pg_last_xlog_replay_location
+------------------------------
+(0 rows)
+
+\c
+REVOKE pg_switch_xlog FROM regressuser1;
-- clean up
\c
drop sequence x_seq;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index c1837c4..9b905a5 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -932,6 +932,109 @@ 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
+SELECT pg_current_xlog_location(); -- fail-no-perm
+SELECT pg_current_xlog_insert_location(); -- fail-no-perm
+SELECT pg_last_xlog_receive_location(); -- fail-no-perm
+SELECT pg_last_xlog_replay_location(); -- fail-no-perm
+\c
+GRANT pg_backup TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_switch_xlog() limit 0; -- success
+SELECT pg_current_xlog_location() limit 0; -- success
+SELECT pg_current_xlog_insert_location() limit 0; -- success
+SELECT pg_last_xlog_receive_location() limit 0; -- success
+SELECT pg_last_xlog_replay_location() limit 0; -- success
+\c
+REVOKE pg_backup FROM regressuser1;
+
+-- 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;
+
+-- pg_monitor
+\c
+SET SESSION AUTHORIZATION regressuser1;
+
+SELECT pg_current_xlog_location(); -- fail-no-perm
+SELECT pg_current_xlog_insert_location(); -- fail-no-perm
+SELECT pg_last_xlog_receive_location(); -- fail-no-perm
+SELECT pg_last_xlog_replay_location(); -- fail-no-perm
+\c
+GRANT pg_monitor TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_current_xlog_location() limit 0; -- success
+SELECT pg_current_xlog_insert_location() limit 0; -- success
+SELECT pg_last_xlog_receive_location() limit 0; -- success
+SELECT pg_last_xlog_replay_location() limit 0; -- success
+\c
+REVOKE pg_monitor FROM regressuser1;
+
+-- 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;
+
+-- 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;
+\c
+REVOKE pg_replication FROM regressuser1;
+
+-- pg_switch_xlog
+\c
+SET SESSION AUTHORIZATION regressuser1;
+
+SELECT pg_switch_xlog(); -- fail-no-perm
+SELECT pg_current_xlog_location(); -- fail-no-perm
+SELECT pg_current_xlog_insert_location(); -- fail-no-perm
+SELECT pg_last_xlog_receive_location(); -- fail-no-perm
+SELECT pg_last_xlog_replay_location(); -- fail-no-perm
+\c
+GRANT pg_switch_xlog TO regressuser1;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT pg_switch_xlog() limit 0; -- success
+SELECT pg_current_xlog_location() limit 0; -- success
+SELECT pg_current_xlog_insert_location() limit 0; -- success
+SELECT pg_last_xlog_receive_location() limit 0; -- success
+SELECT pg_last_xlog_replay_location() limit 0; -- success
+\c
+REVOKE pg_switch_xlog FROM regressuser1;
-- clean up
--
2.5.0
signature.asc
Description: Digital signature
