* Michael Paquier (michael.paqu...@gmail.com) wrote: > On Tue, Dec 22, 2015 at 2:54 PM, Amit Langote > <langote_amit...@lab.ntt.co.jp> wrote: > > On 2015/12/22 14:05, Michael Paquier wrote: > >> On Tue, Dec 22, 2015 at 1:41 AM, Stephen Frost <sfr...@snowman.net> wrote: > >>> Updated and rebased patch attached which takes the 'pg_switch_xlog' > >>> default role back out, leaving us with: > >>> > >>> pg_monitor - View privileged info > >>> pg_backup - start/stop backups, switch xlog, create restore points > >>> pg_replay - Pause/resume xlog replay on replicas > >>> pg_replication - Create/destroy/etc replication slots > >>> pg_rotate_logfile - Request logfile rotation > >>> pg_signal_backend - Signal other backends (cancel query/terminate) > >>> pg_file_settings - View configuration settings in all config files > >> > >> Thanks. This looks fine to me. I just have one single comment: > >> > >> + <entry>Request logfile rotation.</entry> > >> s/logfile/transaction log file/ > > > > Looks correct as is. Or maybe "server's log file" as in: > > > > 9.26.2. Server Signaling Functions > > > > pg_rotate_logfile(): Rotate server's log file > > You're right, this is not a WAL segment, just a normal log file. Your > phrasing is better.
Works for me. Updated patch attached. I'll give it another good look and then commit it, barring objections. Thanks! Stephen
From f493051be96514c0f0e178ef74d6d824f702a7c2 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 dece838e3ad74549c6f07dc878c8637dc2db674d 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 begin 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 | 41 +++++++++++++++++++++++++++++++++
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, 166 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 6d0cb3d..76bb642 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1365,13 +1365,15 @@ testdb=>
<varlistentry>
- <term><literal>\dg[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\dg[S+] [ <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
@@ -1525,13 +1527,15 @@ testdb=>
</varlistentry>
<varlistentry>
- <term><literal>\du[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <term><literal>\du[S+] [ <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 2961ccb..03382f2 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 in 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..eb153a3 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,43 @@ get_rolespec_name(const Node *node)
return rolename;
}
+
+/*
+ * Given a RoleSpec, throw an error if the name is reserved, using detail_msg,
+ * if provided.
+ *
+ * If node is NULL, no error is thrown. If detail_msg is NULL then no detail
+ * message is 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 539f197..70b33eb 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 cf6876b..31c7af7 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -429,7 +429,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();
@@ -474,7 +474,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 bb59bc2..32cd58b 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2646,7 +2646,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;
@@ -2691,6 +2691,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 5f240be..70223cc 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 297f18933d316fcf2ac09908cc8dd6fd5b100957 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
This creates an initial set of default roles which administrators may
use to grant access to, historically, superuser-only functions. Using
these roles instead of granting superuser access reduces the number of
superuser roles required for a system. Documention for each of the
default roles has been added to user-manag.sgml.
---
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 | 54 +++++++---
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 | 142 +++++++++++++++++++++++++
src/test/regress/sql/privileges.sql | 83 +++++++++++++++
14 files changed, 448 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 d1b6e59..f39250b 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -483,6 +483,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, view transaction log position information, and create restore points.</entry>
+ </row>
+ <row>
+ <entry>pg_file_settings</entry>
+ <entry>View configuration settings from all configuration files.</entry>
+ </row>
+ <row>
+ <entry>pg_monitor</entry>
+ <entry>View privileged system information (eg: activity of other users, transaction log position information).</entry>
+ </row>
+ <row>
+ <entry>pg_replay</entry>
+ <entry>Pause and resume transaction log 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>Rotate server's log file.</entry>
+ </row>
+ <row>
+ <entry>pg_signal_backend</entry>
+ <entry>Send signals to other backends (eg: cancel query, terminate).</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..1c5f56c 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,
@@ -183,6 +189,12 @@ 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))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser or member of pg_monitor or pg_backup to view current xlog location")));
+
if (RecoveryInProgress())
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -204,6 +216,12 @@ 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))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser or member of pg_monitor or pg_backup to view current xlog insert location")));
+
if (RecoveryInProgress())
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -226,6 +244,12 @@ 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))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser or member of pg_monitor or pg_backup to view last xlog receive location")));
+
recptr = GetWalRcvWriteRecPtr(NULL, NULL);
if (recptr == 0)
@@ -245,6 +269,12 @@ 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))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser or member of pg_monitor or pg_backup to view last xlog replay location")));
+
recptr = GetXLogReplayRecPtr(NULL);
if (recptr == 0)
@@ -348,10 +378,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 +400,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 536c805..c00d5f4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -426,6 +426,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 7b1b7f1..fc82201 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"
@@ -2811,11 +2813,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..4946903 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 configuration 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..9f79b99 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 = 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_));
+
+#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
#endif /* PG_AUTHID_H */
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 88bdc2c..aeac29a 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1523,6 +1523,148 @@ 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
+SELECT pg_current_xlog_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor or pg_backup to view current xlog location
+SELECT pg_current_xlog_insert_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor or pg_backup to view current xlog insert location
+SELECT pg_last_xlog_receive_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor or pg_backup to view last xlog receive location
+SELECT pg_last_xlog_replay_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor or pg_backup to view last xlog replay location
+\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 or pg_backup to view current xlog location
+SELECT pg_current_xlog_insert_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor or pg_backup to view current xlog insert location
+SELECT pg_last_xlog_receive_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor or pg_backup to view last xlog receive location
+SELECT pg_last_xlog_replay_location(); -- fail-no-perm
+ERROR: must be superuser or member of pg_monitor or pg_backup to view last xlog replay location
+\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;
-- 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..a2815e2 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -932,6 +932,89 @@ 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;
-- clean up
--
2.5.0
signature.asc
Description: Digital signature
