Thank you so much for the backtrace!
This latest patch should address by moving the dumpRoleMembership call to
before the pointer is closed.
On Sat, Jan 22, 2022 at 1:11 AM Julien Rouhaud <rjuju...@gmail.com> wrote:
> Hi,
>
> On Fri, Jan 21, 2022 at 07:01:21PM -0800, Kenaniah Cerny wrote:
> > Thanks for the feedback.
> >
> > I have attached an alternate version of the v5 patch that incorporates
> the
> > suggested changes to the documentation and DRYs up some of the acl.c code
> > for comparison. As for the databaseOid / InvalidOid parameter, I'm open
> to
> > any suggestions for how to make that even cleaner, but am currently at a
> > loss as to how that would look.
> >
> > CI is showing a failure to run pg_dump on just the Linux - Debian
> Bullseye
> > job (https://cirrus-ci.com/task/5265343722553344). Does anyone have any
> > ideas as to where I should look in order to debug that?
>
> Did you try to reproduce it on some GNU/Linux system? FTR I had and I get
> a
> segfault in pg_dumpall:
>
> (gdb) bt
> #0 __pthread_kill_implementation (threadid=<optimized out>,
> signo=signo@entry=6, no_tid=no_tid@entry=0) at pthread_kill.c:44
> #1 0x00007f329e7e40cf in __pthread_kill_internal (signo=6,
> threadid=<optimized out>) at pthread_kill.c:78
> #2 0x00007f329e7987a2 in __GI_raise (sig=sig@entry=6) at
> ../sysdeps/posix/raise.c:26
> #3 0x00007f329e783449 in __GI_abort () at abort.c:79
> #4 0x00007f329e7d85d8 in __libc_message (action=action@entry=do_abort,
> fmt=fmt@entry=0x7f329e90b6aa "%s\n") at ../sysdeps/posix/libc_fatal.c:155
> #5 0x00007f329e7edcfa in malloc_printerr (str=str@entry=0x7f329e9092c3
> "free(): invalid pointer") at malloc.c:5536
> #6 0x00007f329e7ef504 in _int_free (av=<optimized out>, p=<optimized
> out>, have_lock=0) at malloc.c:4327
> #7 0x00007f329e7f1f81 in __GI___libc_free (mem=<optimized out>) at
> malloc.c:3279
> #8 0x00007f329e7dbec5 in __GI__IO_free_backup_area
> (fp=fp@entry=0x561775f126c0)
> at genops.c:190
> #9 0x00007f329e7db6af in _IO_new_file_overflow (f=0x561775f126c0, ch=-1)
> at fileops.c:758
> #10 0x00007f329e7da7be in _IO_new_file_xsputn (n=2, data=<optimized out>,
> f=<optimized out>) at
> /usr/src/debug/sys-libs/glibc-2.34-r4/glibc-2.34/libio/libioP.h:947
> #11 _IO_new_file_xsputn (f=0x561775f126c0, data=<optimized out>, n=2) at
> fileops.c:1197
> #12 0x00007f329e7cfd32 in __GI__IO_fwrite (buf=0x7ffc90bb0ac0, size=1,
> count=2, fp=0x561775f126c0) at
> /usr/src/debug/sys-libs/glibc-2.34-r4/glibc-2.34/libio/libioP.h:947
> #13 0x000056177483c758 in flushbuffer (target=0x7ffc90bb0a90) at
> snprintf.c:310
> #14 0x000056177483c4e8 in pg_vfprintf (stream=0x561775f126c0,
> fmt=0x561774840dec "\n\n", args=0x7ffc90bb0f00) at snprintf.c:259
> #15 0x000056177483c5ce in pg_fprintf (stream=0x561775f126c0,
> fmt=0x561774840dec "\n\n") at snprintf.c:270
> #16 0x0000561774831893 in dumpRoleMembership (conn=0x561775f09600,
> databaseId=0x561775f152d2 "1") at pg_dumpall.c:991
> #17 0x0000561774832426 in dumpDatabases (conn=0x561775f09600) at
> pg_dumpall.c:1332
> #18 0x000056177483049e in main (argc=3, argv=0x7ffc90bb1658) at
> pg_dumpall.c:596
>
> I didn't look in detail, but:
>
> @@ -1323,6 +1327,10 @@ dumpDatabases(PGconn *conn)
> exit_nicely(1);
> }
>
> + /* Dump database-specific roles if server is running 15.0 or later
> */
> + if (server_version >= 150000)
> + dumpRoleMembership(conn, dbid);
> +
>
> Isn't that trying print to OPF after the possible fclose(OPF) a bit before
> and
> before it's reopened?
>
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 1e65c426b28e..a0beec6136e6 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1642,11 +1642,10 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</para>
<para>
- Because user identities are cluster-wide,
- <structname>pg_auth_members</structname>
- is shared across all databases of a cluster: there is only one
- copy of <structname>pg_auth_members</structname> per cluster, not
- one per database.
+ User identities are cluster-wide, but role memberships can be either
+ cluster-wide or database-specific (as specified by the value of the
+ <structfield>dbid</structfield> column). The <structname>pg_auth_members</structname>
+ catalog is shared across all databases of a cluster.
</para>
<table>
@@ -1703,6 +1702,17 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<structfield>roleid</structfield> to others
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dbid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ ID of the database that this membership is constrained to; zero if membership is cluster-wide
+ </para></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index a897712de2e5..98bcfed5f507 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -93,6 +93,7 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replaceable class="parameter">role_specification</replaceable> [, ...]
+ [ IN DATABASE <replaceable class="parameter">database_name</replaceable> | IN CURRENT DATABASE ]
[ WITH ADMIN OPTION ]
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
@@ -243,7 +244,23 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
This variant of the <command>GRANT</command> command grants membership
in a role to one or more other roles. Membership in a role is significant
because it conveys the privileges granted to a role to each of its
- members.
+ members. Membership is effective cluster-wide unless otherwise constrained
+ through the use of a database-specific clause. Both database-specific and cluster-wide
+ versions of a role membership grant may exist at the same time. In the event that
+ multiple grants apply, the membership privileges conferred are additive.
+ </para>
+
+ <para>
+ If <literal>IN DATABASE <replaceable class="parameter">database_name</replaceable></literal>
+ is specified, membership in <replaceable class="parameter">role_name</replaceable>
+ will be effective only when the recipient is connected to the database specified by
+ <replaceable class="parameter">database_name</replaceable>.
+ </para>
+
+ <para>
+ If <literal>IN CURRENT DATABASE</literal> is specified, the membership in
+ <replaceable class="parameter">role_name</replaceable> will be effective only when the
+ recipient is connected to the same database that the grant was issued in.
</para>
<para>
@@ -270,6 +287,10 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
does not allow the noise word <literal>GROUP</literal>
in <replaceable class="parameter">role_specification</replaceable>.
</para>
+
+ <para>
+ See <xref linkend="role-membership"/> for more information about role memberships.
+ </para>
</refsect2>
</refsect1>
@@ -391,10 +412,18 @@ GRANT ALL PRIVILEGES ON kinds TO manuel;
</para>
<para>
- Grant membership in role <literal>admins</literal> to user <literal>joe</literal>:
+ Grant cluster-wide membership in role <literal>admins</literal> to user <literal>joe</literal>:
<programlisting>
GRANT admins TO joe;
+</programlisting></para>
+
+ <para>
+ Grant read and write access to user <literal>alice</literal> in the database
+ named <literal>sales</literal>:
+
+<programlisting>
+GRANT pg_read_all_data, pg_write_all_data TO alice IN DATABASE sales;
</programlisting></para>
</refsect1>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 3014c864ea3c..b5013f004451 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -120,6 +120,7 @@ REVOKE [ GRANT OPTION FOR ]
REVOKE [ ADMIN OPTION FOR ]
<replaceable class="parameter">role_name</replaceable> [, ...] FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
+ [ IN DATABASE <replaceable class="parameter">database_name</replaceable> | IN CURRENT DATABASE ]
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
[ CASCADE | RESTRICT ]
@@ -298,6 +299,14 @@ REVOKE ALL PRIVILEGES ON kinds FROM manuel;
<programlisting>
REVOKE admins FROM joe;
+</programlisting></para>
+
+ <para>
+ Revoke write access for user <literal>bob</literal> from the <literal>sales</literal>
+ database:
+
+<programlisting>
+REVOKE pg_write_all_data FROM bob IN DATABASE sales;
</programlisting></para>
</refsect1>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 9067be1d9c78..9a32d27945a4 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -308,6 +308,11 @@ CREATE ROLE <replaceable>name</replaceable>;
<synopsis>
GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ;
REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ;
+</synopsis>
+ Role membership can also be granted and revoked within the context of a specific database:
+<synopsis>
+GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... IN CURRENT DATABASE;
+REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... IN DATABASE <replaceable>database_name</replaceable>;
</synopsis>
You can grant membership to other group roles, too (since there isn't
really any distinction between group roles and non-group roles). The
@@ -398,9 +403,6 @@ RESET ROLE;
<command>SET ROLE admin</command>.
</para>
- <para>
- </para>
-
<para>
To destroy a group role, use <link
linkend="sql-droprole"><command>DROP ROLE</command></link>:
@@ -639,7 +641,7 @@ DROP ROLE doomed_role;
</para>
<para>
- Administrators can grant access to these roles to users using the
+ Administrators can grant cluster-wide access to these roles to users using the
<link linkend="sql-grant"><command>GRANT</command></link> command, for example:
<programlisting>
@@ -647,6 +649,14 @@ GRANT pg_signal_backend TO admin_user;
</programlisting>
</para>
+ <para>
+ Access can also be granted within the context of a specific database,
+ for example:
+
+<programlisting>
+GRANT pg_read_all_data TO reporting_user IN DATABASE sales;
+</programlisting>
+ </para>
</sect1>
<sect1 id="perm-functions">
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index dfd5fb669eef..c4b2325a6c05 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -258,8 +258,9 @@ IsSharedRelation(Oid relationId)
/* These are their indexes */
if (relationId == AuthIdRolnameIndexId ||
relationId == AuthIdOidIndexId ||
- relationId == AuthMemRoleMemIndexId ||
- relationId == AuthMemMemRoleIndexId ||
+ relationId == AuthMemDbMemRoleIndexId ||
+ relationId == AuthMemRoleMemDbIndexId ||
+ relationId == AuthMemMemRoleDbIndexId ||
relationId == DatabaseNameIndexId ||
relationId == DatabaseOidIndexId ||
relationId == SharedDescriptionObjIndexId ||
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index da8345561d8f..822d3ab5c96c 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -46,6 +46,7 @@
#include "commands/defrem.h"
#include "commands/seclabel.h"
#include "commands/tablespace.h"
+#include "commands/user.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "pgstat.h"
@@ -915,6 +916,11 @@ dropdb(const char *dbname, bool missing_ok, bool force)
DeleteSharedComments(db_id, DatabaseRelationId);
DeleteSharedSecurityLabel(db_id, DatabaseRelationId);
+ /*
+ * Delete any roles memberships directly associated with this database.
+ */
+ DropDatabaseSpecificRoles(db_id);
+
/*
* Remove settings associated with this database
*/
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index f9d3c1246bb2..e7f3a6b9104c 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -51,10 +51,10 @@ check_password_hook_type check_password_hook = NULL;
static void AddRoleMems(const char *rolename, Oid roleid,
List *memberSpecs, List *memberIds,
- Oid grantorId, bool admin_opt);
+ Oid grantorId, bool admin_opt, Oid dbid);
static void DelRoleMems(const char *rolename, Oid roleid,
List *memberSpecs, List *memberIds,
- bool admin_opt);
+ bool admin_opt, Oid dbid);
/* Check if current user has createrole privileges */
@@ -453,7 +453,7 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
AddRoleMems(oldrolename, oldroleid,
thisrole_list,
thisrole_oidlist,
- GetUserId(), false);
+ GetUserId(), false, InvalidOid);
ReleaseSysCache(oldroletup);
}
@@ -465,10 +465,10 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
*/
AddRoleMems(stmt->role, roleid,
adminmembers, roleSpecsToIds(adminmembers),
- GetUserId(), true);
+ GetUserId(), true, InvalidOid);
AddRoleMems(stmt->role, roleid,
rolemembers, roleSpecsToIds(rolemembers),
- GetUserId(), false);
+ GetUserId(), false, InvalidOid);
/* Post creation hook for new role */
InvokeObjectPostCreateHook(AuthIdRelationId, roleid, 0);
@@ -805,11 +805,11 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt)
if (stmt->action == +1) /* add members to role */
AddRoleMems(rolename, roleid,
rolemembers, roleSpecsToIds(rolemembers),
- GetUserId(), false);
+ GetUserId(), false, InvalidOid);
else if (stmt->action == -1) /* drop members from role */
DelRoleMems(rolename, roleid,
rolemembers, roleSpecsToIds(rolemembers),
- false);
+ false, InvalidOid);
}
/*
@@ -1025,7 +1025,7 @@ DropRole(DropRoleStmt *stmt)
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(roleid));
- sscan = systable_beginscan(pg_auth_members_rel, AuthMemRoleMemIndexId,
+ sscan = systable_beginscan(pg_auth_members_rel, AuthMemRoleMemDbIndexId,
true, NULL, 1, &scankey);
while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
@@ -1040,7 +1040,7 @@ DropRole(DropRoleStmt *stmt)
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(roleid));
- sscan = systable_beginscan(pg_auth_members_rel, AuthMemMemRoleIndexId,
+ sscan = systable_beginscan(pg_auth_members_rel, AuthMemMemRoleDbIndexId,
true, NULL, 1, &scankey);
while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
@@ -1230,6 +1230,17 @@ GrantRole(GrantRoleStmt *stmt)
Oid grantor;
List *grantee_ids;
ListCell *item;
+ Oid dbid;
+
+ /* Determine if this grant/revoke is database-specific */
+ if (stmt->database == NULL) {
+ dbid = InvalidOid;
+ } else if (strcmp(stmt->database, "") == 0) {
+ dbid = MyDatabaseId;
+ } else {
+ dbid = get_database_oid(stmt->database, false);
+ }
+
if (stmt->grantor)
grantor = get_rolespec_oid(stmt->grantor, false);
@@ -1264,11 +1275,11 @@ GrantRole(GrantRoleStmt *stmt)
if (stmt->is_grant)
AddRoleMems(rolename, roleid,
stmt->grantee_roles, grantee_ids,
- grantor, stmt->admin_opt);
+ grantor, stmt->admin_opt, dbid);
else
DelRoleMems(rolename, roleid,
stmt->grantee_roles, grantee_ids,
- stmt->admin_opt);
+ stmt->admin_opt, dbid);
}
/*
@@ -1375,7 +1386,7 @@ roleSpecsToIds(List *memberNames)
static void
AddRoleMems(const char *rolename, Oid roleid,
List *memberSpecs, List *memberIds,
- Oid grantorId, bool admin_opt)
+ Oid grantorId, bool admin_opt, Oid dbid)
{
Relation pg_authmem_rel;
TupleDesc pg_authmem_dsc;
@@ -1402,7 +1413,7 @@ AddRoleMems(const char *rolename, Oid roleid,
else
{
if (!have_createrole_privilege() &&
- !is_admin_of_role(grantorId, roleid))
+ !is_admin_of_role(grantorId, roleid, dbid))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must have admin option on role \"%s\"",
@@ -1490,16 +1501,23 @@ AddRoleMems(const char *rolename, Oid roleid,
* Check if entry for this role/member already exists; if so, give
* warning unless we are adding admin option.
*/
- authmem_tuple = SearchSysCache2(AUTHMEMROLEMEM,
+ authmem_tuple = SearchSysCache3(AUTHMEMROLEMEMDB,
ObjectIdGetDatum(roleid),
- ObjectIdGetDatum(memberid));
+ ObjectIdGetDatum(memberid),
+ ObjectIdGetDatum(dbid));
if (HeapTupleIsValid(authmem_tuple) &&
(!admin_opt ||
((Form_pg_auth_members) GETSTRUCT(authmem_tuple))->admin_option))
{
- ereport(NOTICE,
- (errmsg("role \"%s\" is already a member of role \"%s\"",
- get_rolespec_name(memberRole), rolename)));
+ if (dbid == InvalidOid) {
+ ereport(NOTICE,
+ (errmsg("role \"%s\" is already a member of role \"%s\"",
+ get_rolespec_name(memberRole), rolename)));
+ } else {
+ ereport(NOTICE,
+ (errmsg("role \"%s\" is already a member of role \"%s\" in database \"%s\"",
+ get_rolespec_name(memberRole), rolename, get_database_name(dbid))));
+ }
ReleaseSysCache(authmem_tuple);
continue;
}
@@ -1513,6 +1531,7 @@ AddRoleMems(const char *rolename, Oid roleid,
new_record[Anum_pg_auth_members_member - 1] = ObjectIdGetDatum(memberid);
new_record[Anum_pg_auth_members_grantor - 1] = ObjectIdGetDatum(grantorId);
new_record[Anum_pg_auth_members_admin_option - 1] = BoolGetDatum(admin_opt);
+ new_record[Anum_pg_auth_members_dbid - 1] = ObjectIdGetDatum(dbid);
if (HeapTupleIsValid(authmem_tuple))
{
@@ -1553,7 +1572,7 @@ AddRoleMems(const char *rolename, Oid roleid,
static void
DelRoleMems(const char *rolename, Oid roleid,
List *memberSpecs, List *memberIds,
- bool admin_opt)
+ bool admin_opt, Oid dbid)
{
Relation pg_authmem_rel;
TupleDesc pg_authmem_dsc;
@@ -1580,7 +1599,7 @@ DelRoleMems(const char *rolename, Oid roleid,
else
{
if (!have_createrole_privilege() &&
- !is_admin_of_role(GetUserId(), roleid))
+ !is_admin_of_role(GetUserId(), roleid, dbid))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("must have admin option on role \"%s\"",
@@ -1599,14 +1618,21 @@ DelRoleMems(const char *rolename, Oid roleid,
/*
* Find entry for this role/member
*/
- authmem_tuple = SearchSysCache2(AUTHMEMROLEMEM,
+ authmem_tuple = SearchSysCache3(AUTHMEMROLEMEMDB,
ObjectIdGetDatum(roleid),
- ObjectIdGetDatum(memberid));
+ ObjectIdGetDatum(memberid),
+ ObjectIdGetDatum(dbid));
if (!HeapTupleIsValid(authmem_tuple))
{
- ereport(WARNING,
- (errmsg("role \"%s\" is not a member of role \"%s\"",
- get_rolespec_name(memberRole), rolename)));
+ if (dbid == InvalidOid){
+ ereport(WARNING,
+ (errmsg("role \"%s\" is not a member of role \"%s\"",
+ get_rolespec_name(memberRole), rolename)));
+ } else {
+ ereport(WARNING,
+ (errmsg("role \"%s\" is not a member of role \"%s\" in database \"%s\"",
+ get_rolespec_name(memberRole), rolename, get_database_name(dbid))));
+ }
continue;
}
@@ -1648,3 +1674,42 @@ DelRoleMems(const char *rolename, Oid roleid,
*/
table_close(pg_authmem_rel, NoLock);
}
+
+/*
+ * DropDatabaseSpecificRoles
+ *
+ * Delete pg_auth_members entries corresponding to a database that's being
+ * dropped.
+ */
+void
+DropDatabaseSpecificRoles(Oid databaseId)
+{
+ Relation pg_authmem_rel;
+ ScanKeyData key[1];
+ SysScanDesc scan;
+ HeapTuple tup;
+
+ pg_authmem_rel = table_open(AuthMemRelationId, RowExclusiveLock);
+
+ /*
+ * First, delete all the entries that have the database Oid in the dbid
+ * field.
+ */
+ ScanKeyInit(&key[0],
+ Anum_pg_auth_members_dbid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(databaseId));
+ /* We leave the other index fields unspecified */
+
+ scan = systable_beginscan(pg_authmem_rel, AuthMemDbMemRoleIndexId, true,
+ NULL, 1, key);
+
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ CatalogTupleDelete(pg_authmem_rel, &tup->t_self);
+ }
+
+ systable_endscan(scan);
+
+ table_close(pg_authmem_rel, RowExclusiveLock);
+}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 90b5da51c950..db2e950f95f8 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3437,6 +3437,7 @@ _copyGrantRoleStmt(const GrantRoleStmt *from)
COPY_NODE_FIELD(granted_roles);
COPY_NODE_FIELD(grantee_roles);
+ COPY_SCALAR_FIELD(database);
COPY_SCALAR_FIELD(is_grant);
COPY_SCALAR_FIELD(admin_opt);
COPY_NODE_FIELD(grantor);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 06345da3ba84..66e15ca79a2b 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1215,6 +1215,7 @@ _equalGrantRoleStmt(const GrantRoleStmt *a, const GrantRoleStmt *b)
{
COMPARE_NODE_FIELD(granted_roles);
COMPARE_NODE_FIELD(grantee_roles);
+ COMPARE_SCALAR_FIELD(database);
COMPARE_SCALAR_FIELD(is_grant);
COMPARE_SCALAR_FIELD(admin_opt);
COMPARE_NODE_FIELD(grantor);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b5966712ce14..f6f31102c009 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -361,7 +361,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <str> opt_type
%type <str> foreign_server_version opt_foreign_server_version
-%type <str> opt_in_database
+%type <str> opt_in_database opt_grant_in_database
%type <str> OptSchemaName
%type <list> OptSchemaEltList
@@ -7252,6 +7252,11 @@ grantee:
;
+opt_grant_in_database:
+ IN_P CURRENT_P DATABASE { $$ = ""; }
+ | opt_in_database { $$ = $1; }
+ ;
+
opt_grant_grant_option:
WITH GRANT OPTION { $$ = true; }
| /*EMPTY*/ { $$ = false; }
@@ -7264,37 +7269,40 @@ opt_grant_grant_option:
*****************************************************************************/
GrantRoleStmt:
- GRANT privilege_list TO role_list opt_grant_admin_option opt_granted_by
+ GRANT privilege_list TO role_list opt_grant_in_database opt_grant_admin_option opt_granted_by
{
GrantRoleStmt *n = makeNode(GrantRoleStmt);
n->is_grant = true;
n->granted_roles = $2;
n->grantee_roles = $4;
- n->admin_opt = $5;
- n->grantor = $6;
+ n->database = $5;
+ n->admin_opt = $6;
+ n->grantor = $7;
$$ = (Node*)n;
}
;
RevokeRoleStmt:
- REVOKE privilege_list FROM role_list opt_granted_by opt_drop_behavior
+ REVOKE privilege_list FROM role_list opt_grant_in_database opt_granted_by opt_drop_behavior
{
GrantRoleStmt *n = makeNode(GrantRoleStmt);
n->is_grant = false;
n->admin_opt = false;
n->granted_roles = $2;
n->grantee_roles = $4;
- n->behavior = $6;
+ n->database = $5;
+ n->behavior = $7;
$$ = (Node*)n;
}
- | REVOKE ADMIN OPTION FOR privilege_list FROM role_list opt_granted_by opt_drop_behavior
+ | REVOKE ADMIN OPTION FOR privilege_list FROM role_list opt_grant_in_database opt_granted_by opt_drop_behavior
{
GrantRoleStmt *n = makeNode(GrantRoleStmt);
n->is_grant = false;
n->admin_opt = true;
n->granted_roles = $5;
n->grantee_roles = $7;
- n->behavior = $9;
+ n->database = $8;
+ n->behavior = $10;
$$ = (Node*)n;
}
;
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 0a16f8156cb4..be37285d4753 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -4624,7 +4624,7 @@ pg_role_aclcheck(Oid role_oid, Oid roleid, AclMode mode)
* session and call stack. That suits two-argument pg_has_role(), but
* it gives the three-argument version a lamentable whimsy.
*/
- if (is_admin_of_role(roleid, role_oid))
+ if (is_admin_of_role(roleid, role_oid, MyDatabaseId))
return ACLCHECK_OK;
}
if (mode & ACL_CREATE)
@@ -4658,7 +4658,7 @@ initialize_acl(void)
* of pg_auth_members (for roles_is_member_of()), pg_authid (for
* has_rolinherit()), or pg_database (for roles_is_member_of())
*/
- CacheRegisterSyscacheCallback(AUTHMEMROLEMEM,
+ CacheRegisterSyscacheCallback(AUTHMEMROLEMEMDB,
RoleMembershipCacheCallback,
(Datum) 0);
CacheRegisterSyscacheCallback(AUTHOID,
@@ -4707,6 +4707,48 @@ has_rolinherit(Oid roleid)
}
+/*
+ * Appends role memberships to the list of roles
+ */
+static void
+append_role_memberships(List *roles_list, bool *is_admin, Oid admin_of,
+ Oid memberid, Oid targetDatabaseId, Oid databaseId)
+{
+ CatCList *memlist;
+ int i;
+
+ /* Find roles that memberid is directly a member of */
+ memlist = SearchSysCacheList2(AUTHMEMDBMEMROLE,
+ ObjectIdGetDatum(targetDatabaseId),
+ ObjectIdGetDatum(memberid));
+ for (i = 0; i < memlist->n_members; i++)
+ {
+ HeapTuple tup = &memlist->members[i]->tuple;
+ Oid otherid = ((Form_pg_auth_members) GETSTRUCT(tup))->roleid;
+
+ /*
+ * While otherid==InvalidOid shouldn't appear in the catalog, the
+ * OidIsValid() avoids crashing if that arises. This reports if
+ * the admin option has been granted.
+ */
+ if (otherid == admin_of &&
+ ((Form_pg_auth_members) GETSTRUCT(tup))->admin_option &&
+ ((Form_pg_auth_members) GETSTRUCT(tup))->dbid == databaseId &&
+ OidIsValid(admin_of))
+ *is_admin = true;
+
+ /*
+ * Even though there shouldn't be any loops in the membership
+ * graph, we must test for having already seen this role. It is
+ * legal for instance to have both A->B and A->C->B.
+ */
+ roles_list = list_append_unique_oid(roles_list, otherid);
+ }
+ ReleaseSysCacheList(memlist);
+
+}
+
+
/*
* Get a list of roles that the specified roleid is a member of
*
@@ -4724,7 +4766,7 @@ has_rolinherit(Oid roleid)
*/
static List *
roles_is_member_of(Oid roleid, enum RoleRecurseType type,
- Oid admin_of, bool *is_admin)
+ Oid admin_of, bool *is_admin, Oid databaseId)
{
Oid dba;
List *roles_list;
@@ -4773,37 +4815,15 @@ roles_is_member_of(Oid roleid, enum RoleRecurseType type,
foreach(l, roles_list)
{
Oid memberid = lfirst_oid(l);
- CatCList *memlist;
- int i;
if (type == ROLERECURSE_PRIVS && !has_rolinherit(memberid))
continue; /* ignore non-inheriting roles */
- /* Find roles that memberid is directly a member of */
- memlist = SearchSysCacheList1(AUTHMEMMEMROLE,
- ObjectIdGetDatum(memberid));
- for (i = 0; i < memlist->n_members; i++)
- {
- HeapTuple tup = &memlist->members[i]->tuple;
- Oid otherid = ((Form_pg_auth_members) GETSTRUCT(tup))->roleid;
-
- /*
- * While otherid==InvalidOid shouldn't appear in the catalog, the
- * OidIsValid() avoids crashing if that arises.
- */
- if (otherid == admin_of &&
- ((Form_pg_auth_members) GETSTRUCT(tup))->admin_option &&
- OidIsValid(admin_of))
- *is_admin = true;
-
- /*
- * Even though there shouldn't be any loops in the membership
- * graph, we must test for having already seen this role. It is
- * legal for instance to have both A->B and A->C->B.
- */
- roles_list = list_append_unique_oid(roles_list, otherid);
- }
- ReleaseSysCacheList(memlist);
+ /* Find roles that memberid is directly a member of globally */
+ append_role_memberships(roles_list, is_admin, admin_of, memberid, InvalidOid, InvalidOid);
+
+ /* Find roles that memberid is directly a member of in the current database */
+ append_role_memberships(roles_list, is_admin, admin_of, memberid, MyDatabaseId, databaseId);
/* implement pg_database_owner implicit membership */
if (memberid == dba && OidIsValid(dba))
@@ -4855,7 +4875,7 @@ has_privs_of_role(Oid member, Oid role)
* multi-level recursion, then see if target role is any one of them.
*/
return list_member_oid(roles_is_member_of(member, ROLERECURSE_PRIVS,
- InvalidOid, NULL),
+ InvalidOid, NULL, InvalidOid),
role);
}
@@ -4881,7 +4901,7 @@ is_member_of_role(Oid member, Oid role)
* recursion, then see if target role is any one of them.
*/
return list_member_oid(roles_is_member_of(member, ROLERECURSE_MEMBERS,
- InvalidOid, NULL),
+ InvalidOid, NULL, InvalidOid),
role);
}
@@ -4917,7 +4937,7 @@ is_member_of_role_nosuper(Oid member, Oid role)
* recursion, then see if target role is any one of them.
*/
return list_member_oid(roles_is_member_of(member, ROLERECURSE_MEMBERS,
- InvalidOid, NULL),
+ InvalidOid, NULL, InvalidOid),
role);
}
@@ -4928,7 +4948,7 @@ is_member_of_role_nosuper(Oid member, Oid role)
* or a superuser?
*/
bool
-is_admin_of_role(Oid member, Oid role)
+is_admin_of_role(Oid member, Oid role, Oid databaseId)
{
bool result = false;
@@ -4968,7 +4988,8 @@ is_admin_of_role(Oid member, Oid role)
return member == GetSessionUserId() &&
!InLocalUserIdChange() && !InSecurityRestrictedOperation();
- (void) roles_is_member_of(member, ROLERECURSE_MEMBERS, role, &result);
+ /* Check for WITH ADMIN OPTION either globally or for the given database */
+ (void) roles_is_member_of(member, ROLERECURSE_MEMBERS, role, &result, databaseId);
return result;
}
@@ -5044,7 +5065,7 @@ select_best_grantor(Oid roleId, AclMode privileges,
* doesn't query any role memberships.
*/
roles_list = roles_is_member_of(roleId, ROLERECURSE_PRIVS,
- InvalidOid, NULL);
+ InvalidOid, NULL, InvalidOid);
/* initialize candidate result as default */
*grantorId = roleId;
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index eb8308808937..d98948120cbf 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1110,7 +1110,7 @@ IndexScanOK(CatCache *cache, ScanKey cur_skey)
case AUTHNAME:
case AUTHOID:
- case AUTHMEMMEMROLE:
+ case AUTHMEMMEMROLEDB:
case DATABASEOID:
/*
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 2e760e8a3bdc..80463e614569 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4060,7 +4060,7 @@ RelationCacheInitializePhase3(void)
AuthIdRelationId);
load_critical_index(AuthIdOidIndexId,
AuthIdRelationId);
- load_critical_index(AuthMemMemRoleIndexId,
+ load_critical_index(AuthMemMemRoleDbIndexId,
AuthMemRelationId);
load_critical_index(SharedSecLabelObjectIndexId,
SharedSecLabelRelationId);
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index f4e7819f1e2d..491dd3ab86fb 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -210,24 +210,35 @@ static const struct cachedesc cacheinfo[] = {
},
128
},
- {AuthMemRelationId, /* AUTHMEMMEMROLE */
- AuthMemMemRoleIndexId,
- 2,
+ {AuthMemRelationId, /* AUTHMEMDBMEMROLE */
+ AuthMemDbMemRoleIndexId,
+ 3,
{
+ Anum_pg_auth_members_dbid,
Anum_pg_auth_members_member,
Anum_pg_auth_members_roleid,
- 0,
0
},
8
},
- {AuthMemRelationId, /* AUTHMEMROLEMEM */
- AuthMemRoleMemIndexId,
- 2,
+ {AuthMemRelationId, /* AUTHMEMMEMROLEDB */
+ AuthMemMemRoleDbIndexId,
+ 3,
+ {
+ Anum_pg_auth_members_member,
+ Anum_pg_auth_members_roleid,
+ Anum_pg_auth_members_dbid,
+ 0
+ },
+ 8
+ },
+ {AuthMemRelationId, /* AUTHMEMROLEMEMDB */
+ AuthMemRoleMemDbIndexId,
+ 3,
{
Anum_pg_auth_members_roleid,
Anum_pg_auth_members_member,
- 0,
+ Anum_pg_auth_members_dbid,
0
},
8
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 10383c713fee..94d61e538335 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -35,7 +35,7 @@ static void help(void);
static void dropRoles(PGconn *conn);
static void dumpRoles(PGconn *conn);
-static void dumpRoleMembership(PGconn *conn);
+static void dumpRoleMembership(PGconn *conn, const char *databaseId);
static void dropTablespaces(PGconn *conn);
static void dumpTablespaces(PGconn *conn);
static void dropDBs(PGconn *conn);
@@ -584,7 +584,7 @@ main(int argc, char *argv[])
dumpRoles(conn);
/* Dump role memberships */
- dumpRoleMembership(conn);
+ dumpRoleMembership(conn, "0");
}
/* Dump tablespaces */
@@ -937,7 +937,7 @@ dumpRoles(PGconn *conn)
* no membership yet.
*/
static void
-dumpRoleMembership(PGconn *conn)
+dumpRoleMembership(PGconn *conn, const char *databaseId)
{
PQExpBuffer buf = createPQExpBuffer();
PGresult *res;
@@ -951,8 +951,9 @@ dumpRoleMembership(PGconn *conn)
"LEFT JOIN %s ur on ur.oid = a.roleid "
"LEFT JOIN %s um on um.oid = a.member "
"LEFT JOIN %s ug on ug.oid = a.grantor "
- "WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')"
- "ORDER BY 1,2,3", role_catalog, role_catalog, role_catalog);
+ "WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_') "
+ "AND a.dbid = %s"
+ "ORDER BY 1,2,3", role_catalog, role_catalog, role_catalog, databaseId);
res = executeQuery(conn, buf->data);
if (PQntuples(res) > 0)
@@ -966,6 +967,8 @@ dumpRoleMembership(PGconn *conn)
fprintf(OPF, "GRANT %s", fmtId(roleid));
fprintf(OPF, " TO %s", fmtId(member));
+ if (strcmp(databaseId, "0") != 0)
+ fprintf(OPF, " IN CURRENT DATABASE");
if (*option == 't')
fprintf(OPF, " WITH ADMIN OPTION");
@@ -1262,7 +1265,7 @@ dumpDatabases(PGconn *conn)
* doesn't have some failure mode with --clean.
*/
res = executeQuery(conn,
- "SELECT datname "
+ "SELECT datname, oid "
"FROM pg_database d "
"WHERE datallowconn "
"ORDER BY (datname <> 'template1'), datname");
@@ -1273,6 +1276,7 @@ dumpDatabases(PGconn *conn)
for (i = 0; i < PQntuples(res); i++)
{
char *dbname = PQgetvalue(res, i, 0);
+ char *dbid = PQgetvalue(res, i, 1);
const char *create_opts;
int ret;
@@ -1313,6 +1317,10 @@ dumpDatabases(PGconn *conn)
else
create_opts = "--create";
+ /* Dump database-specific roles if server is running 15.0 or later */
+ if (server_version >= 150000)
+ dumpRoleMembership(conn, dbid);
+
if (filename)
fclose(OPF);
diff --git a/src/include/catalog/pg_auth_members.h b/src/include/catalog/pg_auth_members.h
index 1bc027f133d5..26d0d5381e8b 100644
--- a/src/include/catalog/pg_auth_members.h
+++ b/src/include/catalog/pg_auth_members.h
@@ -33,6 +33,7 @@ CATALOG(pg_auth_members,1261,AuthMemRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_
Oid member BKI_LOOKUP(pg_authid); /* ID of a member of that role */
Oid grantor BKI_LOOKUP(pg_authid); /* who granted the membership */
bool admin_option; /* granted with admin option? */
+ Oid dbid BKI_LOOKUP_OPT(pg_database); /* ID of a database this mapping is effective in */
} FormData_pg_auth_members;
/* ----------------
@@ -42,7 +43,8 @@ CATALOG(pg_auth_members,1261,AuthMemRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_
*/
typedef FormData_pg_auth_members *Form_pg_auth_members;
-DECLARE_UNIQUE_INDEX_PKEY(pg_auth_members_role_member_index, 2694, AuthMemRoleMemIndexId, on pg_auth_members using btree(roleid oid_ops, member oid_ops));
-DECLARE_UNIQUE_INDEX(pg_auth_members_member_role_index, 2695, AuthMemMemRoleIndexId, on pg_auth_members using btree(member oid_ops, roleid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_auth_members_role_member_dbid_index, 2694, AuthMemRoleMemDbIndexId, on pg_auth_members using btree(roleid oid_ops, member oid_ops, dbid oid_ops));
+DECLARE_UNIQUE_INDEX(pg_auth_members_member_role_dbid_index, 2695, AuthMemMemRoleDbIndexId, on pg_auth_members using btree(member oid_ops, roleid oid_ops, dbid oid_ops));
+DECLARE_UNIQUE_INDEX(pg_auth_members_dbid_member_role_index, 4715, AuthMemDbMemRoleIndexId, on pg_auth_members using btree(dbid oid_ops, member oid_ops, roleid oid_ops));
#endif /* PG_AUTH_MEMBERS_H */
diff --git a/src/include/commands/user.h b/src/include/commands/user.h
index 0b7a3cd65fd2..f5f8e4c00055 100644
--- a/src/include/commands/user.h
+++ b/src/include/commands/user.h
@@ -33,5 +33,6 @@ extern ObjectAddress RenameRole(const char *oldname, const char *newname);
extern void DropOwnedObjects(DropOwnedStmt *stmt);
extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt);
extern List *roleSpecsToIds(List *memberNames);
+extern void DropDatabaseSpecificRoles(Oid databaseId);
#endif /* USER_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3e9bdc781f9b..ad0a64c1f2c0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2092,6 +2092,8 @@ typedef struct GrantRoleStmt
NodeTag type;
List *granted_roles; /* list of roles to be granted/revoked */
List *grantee_roles; /* list of member roles to add/delete */
+ char *database; /* name of DB this grant applies to
+ NULL = global, "" = current database, otherwise a named database */
bool is_grant; /* true = GRANT, false = REVOKE */
bool admin_opt; /* with admin option */
RoleSpec *grantor; /* set grantor to other than current role */
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 1ce4c5556e70..57dfe78f1673 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -208,7 +208,7 @@ extern int aclmembers(const Acl *acl, Oid **roleids);
extern bool has_privs_of_role(Oid member, Oid role);
extern bool is_member_of_role(Oid member, Oid role);
extern bool is_member_of_role_nosuper(Oid member, Oid role);
-extern bool is_admin_of_role(Oid member, Oid role);
+extern bool is_admin_of_role(Oid member, Oid role, Oid databaseId);
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);
diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h
index 9c1a76e8bb66..6ad506357aa2 100644
--- a/src/include/utils/syscache.h
+++ b/src/include/utils/syscache.h
@@ -39,8 +39,9 @@ enum SysCacheIdentifier
AMPROCNUM,
ATTNAME,
ATTNUM,
- AUTHMEMMEMROLE,
- AUTHMEMROLEMEM,
+ AUTHMEMDBMEMROLE,
+ AUTHMEMMEMROLEDB,
+ AUTHMEMROLEMEMDB,
AUTHNAME,
AUTHOID,
CASTSOURCETARGET,
diff --git a/src/test/modules/unsafe_tests/Makefile b/src/test/modules/unsafe_tests/Makefile
index 3ecf5fcfc5bb..6cf403afcd04 100644
--- a/src/test/modules/unsafe_tests/Makefile
+++ b/src/test/modules/unsafe_tests/Makefile
@@ -1,6 +1,6 @@
# src/test/modules/unsafe_tests/Makefile
-REGRESS = rolenames alter_system_table
+REGRESS = rolenames alter_system_table role_membership
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/src/test/modules/unsafe_tests/expected/role_membership.out b/src/test/modules/unsafe_tests/expected/role_membership.out
new file mode 100644
index 000000000000..2ea9ba093d13
--- /dev/null
+++ b/src/test/modules/unsafe_tests/expected/role_membership.out
@@ -0,0 +1,537 @@
+CREATE ROLE role_admin LOGIN SUPERUSER;
+\connect postgres role_admin
+CREATE FUNCTION check_memberships()
+ RETURNS TABLE (role name, member name, grantor name, admin_option boolean, datname name)
+ AS $$
+SELECT
+ r.rolname as role,
+ m.rolname as member,
+ g.rolname as grantor,
+ admin_option,
+ d.datname
+FROM pg_auth_members a
+LEFT JOIN pg_roles r ON r.oid = a.roleid
+LEFT JOIN pg_roles m ON m.oid = a.member
+LEFT JOIN pg_roles g ON g.oid = a.grantor
+LEFT JOIN pg_database d ON d.oid = a.dbid
+WHERE
+ m.rolname LIKE 'role_%'
+ORDER BY
+ 1, 2, 5
+$$ LANGUAGE SQL;
+-- Populate test databases
+\connect template1
+CREATE TABLE data AS SELECT generate_series(1, 3);
+CREATE DATABASE db_1;
+CREATE DATABASE db_2;
+CREATE DATABASE db_3;
+CREATE DATABASE db_4;
+-- Read all cluster-wide with admin option
+CREATE ROLE role_read_all_with_admin;
+GRANT pg_read_all_data TO role_read_all_with_admin WITH ADMIN OPTION;
+-- Read all in databases 1 and 2
+CREATE ROLE role_read_12;
+GRANT pg_read_all_data TO role_read_12 IN DATABASE db_1;
+GRANT pg_read_all_data TO role_read_12 IN DATABASE db_2;
+-- Read all in databases 3 and 4 with admin option
+CREATE ROLE role_read_34;
+GRANT pg_read_all_data TO role_read_34 IN DATABASE db_3 WITH ADMIN OPTION;
+GRANT pg_read_all_data TO role_read_34 IN DATABASE db_4 WITH ADMIN OPTION;
+-- Inherits read all in databases 3 and 4
+CREATE ROLE role_inherited_34;
+GRANT role_read_34 TO role_inherited_34;
+-- Inherits read all in database 3
+CREATE ROLE role_inherited_3;
+GRANT role_read_34 TO role_inherited_3 IN DATABASE db_3;
+-- No inherit
+CREATE ROLE role_read_all_noinherit NOINHERIT;
+GRANT role_read_all_with_admin TO role_read_all_noinherit;
+-- No inherit in databases 1 and 2
+CREATE ROLE role_read_12_noinherit NOINHERIT;
+GRANT role_read_12 TO role_read_12_noinherit;
+-- Alternate syntax
+CREATE ROLE role_read_template1;
+GRANT pg_read_all_data TO role_read_template1, role_read_all_noinherit IN CURRENT DATABASE;
+-- Failure due to missing database
+GRANT pg_read_all_data TO role_read_template1 IN DATABASE non_existent; -- error
+ERROR: database "non_existent" does not exist
+-- Should warn on duplicate grants
+GRANT pg_read_all_data TO role_read_all_with_admin; -- notice
+NOTICE: role "role_read_all_with_admin" is already a member of role "pg_read_all_data"
+GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1; -- notice
+NOTICE: role "role_read_template1" is already a member of role "pg_read_all_data" in database "template1"
+-- Should not warn if adjusting admin option
+GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1 WITH ADMIN OPTION; -- silent
+GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1 WITH ADMIN OPTION; -- notice
+NOTICE: role "role_read_template1" is already a member of role "pg_read_all_data" in database "template1"
+-- Check membership table
+\connect postgres role_admin
+SELECT * FROM check_memberships();
+ role | member | grantor | admin_option | datname
+--------------------------+--------------------------+------------+--------------+-----------
+ pg_read_all_data | role_read_12 | role_admin | f | db_1
+ pg_read_all_data | role_read_12 | role_admin | f | db_2
+ pg_read_all_data | role_read_34 | role_admin | t | db_3
+ pg_read_all_data | role_read_34 | role_admin | t | db_4
+ pg_read_all_data | role_read_all_noinherit | role_admin | f | template1
+ pg_read_all_data | role_read_all_with_admin | role_admin | t |
+ pg_read_all_data | role_read_template1 | role_admin | t | template1
+ role_read_12 | role_read_12_noinherit | role_admin | f |
+ role_read_34 | role_inherited_3 | role_admin | f | db_3
+ role_read_34 | role_inherited_34 | role_admin | f |
+ role_read_all_with_admin | role_read_all_noinherit | role_admin | f |
+(11 rows)
+
+-- Test membership privileges (db_1)
+\connect db_1
+SET ROLE role_read_all_with_admin;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_read_12;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_read_34;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_inherited_34;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_inherited_3;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_all_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_12_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET SESSION AUTHORIZATION role_read_12;
+SET ROLE pg_read_all_data; -- success
+SET SESSION AUTHORIZATION role_inherited_34;
+SET ROLE pg_read_all_data; -- error
+ERROR: permission denied to set role "pg_read_all_data"
+SET ROLE role_read_34; -- success
+SET SESSION AUTHORIZATION role_inherited_3;
+SET ROLE pg_read_all_data; -- error
+ERROR: permission denied to set role "pg_read_all_data"
+SET ROLE role_read_34; -- error
+ERROR: permission denied to set role "role_read_34"
+SET SESSION AUTHORIZATION role_read_all_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE pg_read_all_data; -- success
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET SESSION AUTHORIZATION role_read_12_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_12; -- success
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+-- Test membership privileges (db_2)
+\connect db_2
+SET ROLE role_read_all_with_admin;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_read_12;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_read_34;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_inherited_34;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_inherited_3;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_all_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_12_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET SESSION AUTHORIZATION role_read_12;
+SET ROLE pg_read_all_data; -- success
+SET SESSION AUTHORIZATION role_inherited_34;
+SET ROLE pg_read_all_data; -- error
+ERROR: permission denied to set role "pg_read_all_data"
+SET ROLE role_read_34; -- success
+SET SESSION AUTHORIZATION role_inherited_3;
+SET ROLE pg_read_all_data; -- error
+ERROR: permission denied to set role "pg_read_all_data"
+SET ROLE role_read_34; -- error
+ERROR: permission denied to set role "role_read_34"
+SET SESSION AUTHORIZATION role_read_all_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE pg_read_all_data; -- success
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET SESSION AUTHORIZATION role_read_12_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_12; -- success
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+-- Test membership privileges (db_3)
+\connect db_3
+SET ROLE role_read_all_with_admin;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_read_12;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_34;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_inherited_34;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_inherited_3;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_read_all_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_12_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET SESSION AUTHORIZATION role_read_12;
+SET ROLE pg_read_all_data; -- error
+ERROR: permission denied to set role "pg_read_all_data"
+SET SESSION AUTHORIZATION role_inherited_34;
+SET ROLE pg_read_all_data; -- success
+SET ROLE role_read_34; -- success
+SET SESSION AUTHORIZATION role_inherited_3;
+SET ROLE pg_read_all_data; -- success
+SET ROLE role_read_34; -- success
+SET SESSION AUTHORIZATION role_read_all_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE pg_read_all_data; -- success
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET SESSION AUTHORIZATION role_read_12_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_12; -- error
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+-- Test membership privileges (db_4)
+\connect db_4
+SET ROLE role_read_all_with_admin;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_read_12;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_34;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_inherited_34;
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET ROLE role_inherited_3;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_all_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_12_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET SESSION AUTHORIZATION role_read_12;
+SET ROLE pg_read_all_data; -- error
+ERROR: permission denied to set role "pg_read_all_data"
+SET SESSION AUTHORIZATION role_inherited_34;
+SET ROLE pg_read_all_data; -- success
+SET ROLE role_read_34; -- success
+SET SESSION AUTHORIZATION role_inherited_3;
+SET ROLE pg_read_all_data; -- error
+ERROR: permission denied to set role "pg_read_all_data"
+SET ROLE role_read_34; -- error
+ERROR: permission denied to set role "role_read_34"
+SET SESSION AUTHORIZATION role_read_all_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE pg_read_all_data; -- success
+SELECT * FROM data; -- success
+ generate_series
+-----------------
+ 1
+ 2
+ 3
+(3 rows)
+
+SET SESSION AUTHORIZATION role_read_12_noinherit;
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+SET ROLE role_read_12; -- error
+SELECT * FROM data; -- error
+ERROR: permission denied for table data
+\connect postgres role_admin
+-- Should not warn if revoking admin option
+REVOKE ADMIN OPTION FOR pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- silent
+REVOKE ADMIN OPTION FOR pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- silent
+SELECT * FROM check_memberships();
+ role | member | grantor | admin_option | datname
+--------------------------+--------------------------+------------+--------------+-----------
+ pg_read_all_data | role_read_12 | role_admin | f | db_1
+ pg_read_all_data | role_read_12 | role_admin | f | db_2
+ pg_read_all_data | role_read_34 | role_admin | t | db_3
+ pg_read_all_data | role_read_34 | role_admin | t | db_4
+ pg_read_all_data | role_read_all_noinherit | role_admin | f | template1
+ pg_read_all_data | role_read_all_with_admin | role_admin | t |
+ pg_read_all_data | role_read_template1 | role_admin | f | template1
+ role_read_12 | role_read_12_noinherit | role_admin | f |
+ role_read_34 | role_inherited_3 | role_admin | f | db_3
+ role_read_34 | role_inherited_34 | role_admin | f |
+ role_read_all_with_admin | role_read_all_noinherit | role_admin | f |
+(11 rows)
+
+-- Should warn if revoking a non-existent membership
+REVOKE pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- success
+REVOKE pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- warning
+WARNING: role "role_read_template1" is not a member of role "pg_read_all_data" in database "template1"
+SELECT * FROM check_memberships();
+ role | member | grantor | admin_option | datname
+--------------------------+--------------------------+------------+--------------+-----------
+ pg_read_all_data | role_read_12 | role_admin | f | db_1
+ pg_read_all_data | role_read_12 | role_admin | f | db_2
+ pg_read_all_data | role_read_34 | role_admin | t | db_3
+ pg_read_all_data | role_read_34 | role_admin | t | db_4
+ pg_read_all_data | role_read_all_noinherit | role_admin | f | template1
+ pg_read_all_data | role_read_all_with_admin | role_admin | t |
+ role_read_12 | role_read_12_noinherit | role_admin | f |
+ role_read_34 | role_inherited_3 | role_admin | f | db_3
+ role_read_34 | role_inherited_34 | role_admin | f |
+ role_read_all_with_admin | role_read_all_noinherit | role_admin | f |
+(10 rows)
+
+-- Revoke should only apply to the specified level
+REVOKE pg_read_all_data FROM role_read_12; -- warning
+WARNING: role "role_read_12" is not a member of role "pg_read_all_data"
+SELECT * FROM check_memberships();
+ role | member | grantor | admin_option | datname
+--------------------------+--------------------------+------------+--------------+-----------
+ pg_read_all_data | role_read_12 | role_admin | f | db_1
+ pg_read_all_data | role_read_12 | role_admin | f | db_2
+ pg_read_all_data | role_read_34 | role_admin | t | db_3
+ pg_read_all_data | role_read_34 | role_admin | t | db_4
+ pg_read_all_data | role_read_all_noinherit | role_admin | f | template1
+ pg_read_all_data | role_read_all_with_admin | role_admin | t |
+ role_read_12 | role_read_12_noinherit | role_admin | f |
+ role_read_34 | role_inherited_3 | role_admin | f | db_3
+ role_read_34 | role_inherited_34 | role_admin | f |
+ role_read_all_with_admin | role_read_all_noinherit | role_admin | f |
+(10 rows)
+
+-- Ensure cluster-wide admin option can grant cluster-wide and in specific databases
+CREATE ROLE role_granted;
+SET SESSION AUTHORIZATION role_read_all_with_admin;
+GRANT pg_read_all_data TO role_granted; -- success
+GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success
+GRANT pg_read_all_data TO role_granted IN DATABASE db_1; -- success
+GRANT role_read_34 TO role_granted; -- error
+ERROR: must have admin option on role "role_read_34"
+SELECT * FROM check_memberships();
+ role | member | grantor | admin_option | datname
+--------------------------+--------------------------+--------------------------+--------------+-----------
+ pg_read_all_data | role_granted | role_read_all_with_admin | f | db_1
+ pg_read_all_data | role_granted | role_read_all_with_admin | f | postgres
+ pg_read_all_data | role_granted | role_read_all_with_admin | f |
+ pg_read_all_data | role_read_12 | role_admin | f | db_1
+ pg_read_all_data | role_read_12 | role_admin | f | db_2
+ pg_read_all_data | role_read_34 | role_admin | t | db_3
+ pg_read_all_data | role_read_34 | role_admin | t | db_4
+ pg_read_all_data | role_read_all_noinherit | role_admin | f | template1
+ pg_read_all_data | role_read_all_with_admin | role_admin | t |
+ role_read_12 | role_read_12_noinherit | role_admin | f |
+ role_read_34 | role_inherited_3 | role_admin | f | db_3
+ role_read_34 | role_inherited_34 | role_admin | f |
+ role_read_all_with_admin | role_read_all_noinherit | role_admin | f |
+(13 rows)
+
+-- Ensure database-specific admin option can only grant within that database
+SET SESSION AUTHORIZATION role_read_34;
+GRANT pg_read_all_data TO role_granted; -- error
+ERROR: must have admin option on role "pg_read_all_data"
+GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- error
+ERROR: must have admin option on role "pg_read_all_data"
+GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- error
+ERROR: must have admin option on role "pg_read_all_data"
+GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- error
+ERROR: must have admin option on role "pg_read_all_data"
+\connect db_3
+SET SESSION AUTHORIZATION role_read_34;
+GRANT pg_read_all_data TO role_granted; -- error
+ERROR: must have admin option on role "pg_read_all_data"
+GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success
+GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- notice
+NOTICE: role "role_granted" is already a member of role "pg_read_all_data" in database "db_3"
+GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- error
+ERROR: must have admin option on role "pg_read_all_data"
+\connect db_4
+SET SESSION AUTHORIZATION role_read_34;
+GRANT pg_read_all_data TO role_granted; -- error
+ERROR: must have admin option on role "pg_read_all_data"
+GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success
+GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- error
+ERROR: must have admin option on role "pg_read_all_data"
+GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- notice
+NOTICE: role "role_granted" is already a member of role "pg_read_all_data" in database "db_4"
+\connect postgres role_admin
+SELECT * FROM check_memberships();
+ role | member | grantor | admin_option | datname
+--------------------------+--------------------------+--------------------------+--------------+-----------
+ pg_read_all_data | role_granted | role_read_all_with_admin | f | db_1
+ pg_read_all_data | role_granted | role_read_34 | f | db_3
+ pg_read_all_data | role_granted | role_read_34 | f | db_4
+ pg_read_all_data | role_granted | role_read_all_with_admin | f | postgres
+ pg_read_all_data | role_granted | role_read_all_with_admin | f |
+ pg_read_all_data | role_read_12 | role_admin | f | db_1
+ pg_read_all_data | role_read_12 | role_admin | f | db_2
+ pg_read_all_data | role_read_34 | role_admin | t | db_3
+ pg_read_all_data | role_read_34 | role_admin | t | db_4
+ pg_read_all_data | role_read_all_noinherit | role_admin | f | template1
+ pg_read_all_data | role_read_all_with_admin | role_admin | t |
+ role_read_12 | role_read_12_noinherit | role_admin | f |
+ role_read_34 | role_inherited_3 | role_admin | f | db_3
+ role_read_34 | role_inherited_34 | role_admin | f |
+ role_read_all_with_admin | role_read_all_noinherit | role_admin | f |
+(15 rows)
+
+-- Should clean up the membership table when dropping a database
+\connect postgres role_admin
+DROP DATABASE db_3;
+SELECT * FROM check_memberships();
+ role | member | grantor | admin_option | datname
+--------------------------+--------------------------+--------------------------+--------------+-----------
+ pg_read_all_data | role_granted | role_read_all_with_admin | f | db_1
+ pg_read_all_data | role_granted | role_read_34 | f | db_4
+ pg_read_all_data | role_granted | role_read_all_with_admin | f | postgres
+ pg_read_all_data | role_granted | role_read_all_with_admin | f |
+ pg_read_all_data | role_read_12 | role_admin | f | db_1
+ pg_read_all_data | role_read_12 | role_admin | f | db_2
+ pg_read_all_data | role_read_34 | role_admin | t | db_4
+ pg_read_all_data | role_read_all_noinherit | role_admin | f | template1
+ pg_read_all_data | role_read_all_with_admin | role_admin | t |
+ role_read_12 | role_read_12_noinherit | role_admin | f |
+ role_read_34 | role_inherited_34 | role_admin | f |
+ role_read_all_with_admin | role_read_all_noinherit | role_admin | f |
+(12 rows)
+
+-- Should clean up the membership table when dropping a role
+DROP ROLE role_read_34;
+SELECT * FROM check_memberships();
+ role | member | grantor | admin_option | datname
+--------------------------+--------------------------+--------------------------+--------------+-----------
+ pg_read_all_data | role_granted | role_read_all_with_admin | f | db_1
+ pg_read_all_data | role_granted | | f | db_4
+ pg_read_all_data | role_granted | role_read_all_with_admin | f | postgres
+ pg_read_all_data | role_granted | role_read_all_with_admin | f |
+ pg_read_all_data | role_read_12 | role_admin | f | db_1
+ pg_read_all_data | role_read_12 | role_admin | f | db_2
+ pg_read_all_data | role_read_all_noinherit | role_admin | f | template1
+ pg_read_all_data | role_read_all_with_admin | role_admin | t |
+ role_read_12 | role_read_12_noinherit | role_admin | f |
+ role_read_all_with_admin | role_read_all_noinherit | role_admin | f |
+(10 rows)
+
diff --git a/src/test/modules/unsafe_tests/sql/role_membership.sql b/src/test/modules/unsafe_tests/sql/role_membership.sql
new file mode 100644
index 000000000000..66b6ec6bf7a9
--- /dev/null
+++ b/src/test/modules/unsafe_tests/sql/role_membership.sql
@@ -0,0 +1,291 @@
+CREATE ROLE role_admin LOGIN SUPERUSER;
+
+\connect postgres role_admin
+
+CREATE FUNCTION check_memberships()
+ RETURNS TABLE (role name, member name, grantor name, admin_option boolean, datname name)
+ AS $$
+SELECT
+ r.rolname as role,
+ m.rolname as member,
+ g.rolname as grantor,
+ admin_option,
+ d.datname
+FROM pg_auth_members a
+LEFT JOIN pg_roles r ON r.oid = a.roleid
+LEFT JOIN pg_roles m ON m.oid = a.member
+LEFT JOIN pg_roles g ON g.oid = a.grantor
+LEFT JOIN pg_database d ON d.oid = a.dbid
+WHERE
+ m.rolname LIKE 'role_%'
+ORDER BY
+ 1, 2, 5
+$$ LANGUAGE SQL;
+
+-- Populate test databases
+\connect template1
+CREATE TABLE data AS SELECT generate_series(1, 3);
+
+CREATE DATABASE db_1;
+CREATE DATABASE db_2;
+CREATE DATABASE db_3;
+CREATE DATABASE db_4;
+
+-- Read all cluster-wide with admin option
+CREATE ROLE role_read_all_with_admin;
+GRANT pg_read_all_data TO role_read_all_with_admin WITH ADMIN OPTION;
+
+-- Read all in databases 1 and 2
+CREATE ROLE role_read_12;
+GRANT pg_read_all_data TO role_read_12 IN DATABASE db_1;
+GRANT pg_read_all_data TO role_read_12 IN DATABASE db_2;
+
+-- Read all in databases 3 and 4 with admin option
+CREATE ROLE role_read_34;
+GRANT pg_read_all_data TO role_read_34 IN DATABASE db_3 WITH ADMIN OPTION;
+GRANT pg_read_all_data TO role_read_34 IN DATABASE db_4 WITH ADMIN OPTION;
+
+-- Inherits read all in databases 3 and 4
+CREATE ROLE role_inherited_34;
+GRANT role_read_34 TO role_inherited_34;
+
+-- Inherits read all in database 3
+CREATE ROLE role_inherited_3;
+GRANT role_read_34 TO role_inherited_3 IN DATABASE db_3;
+
+-- No inherit
+CREATE ROLE role_read_all_noinherit NOINHERIT;
+GRANT role_read_all_with_admin TO role_read_all_noinherit;
+
+-- No inherit in databases 1 and 2
+CREATE ROLE role_read_12_noinherit NOINHERIT;
+GRANT role_read_12 TO role_read_12_noinherit;
+
+-- Alternate syntax
+CREATE ROLE role_read_template1;
+GRANT pg_read_all_data TO role_read_template1, role_read_all_noinherit IN CURRENT DATABASE;
+
+-- Failure due to missing database
+GRANT pg_read_all_data TO role_read_template1 IN DATABASE non_existent; -- error
+
+-- Should warn on duplicate grants
+GRANT pg_read_all_data TO role_read_all_with_admin; -- notice
+GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1; -- notice
+
+-- Should not warn if adjusting admin option
+GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1 WITH ADMIN OPTION; -- silent
+GRANT pg_read_all_data TO role_read_template1 IN DATABASE template1 WITH ADMIN OPTION; -- notice
+
+-- Check membership table
+\connect postgres role_admin
+SELECT * FROM check_memberships();
+
+-- Test membership privileges (db_1)
+\connect db_1
+SET ROLE role_read_all_with_admin;
+SELECT * FROM data; -- success
+SET ROLE role_read_12;
+SELECT * FROM data; -- success
+SET ROLE role_read_34;
+SELECT * FROM data; -- error
+SET ROLE role_inherited_34;
+SELECT * FROM data; -- error
+SET ROLE role_inherited_3;
+SELECT * FROM data; -- error
+SET ROLE role_read_all_noinherit;
+SELECT * FROM data; -- error
+SET ROLE role_read_12_noinherit;
+SELECT * FROM data; -- error
+
+SET SESSION AUTHORIZATION role_read_12;
+SET ROLE pg_read_all_data; -- success
+
+SET SESSION AUTHORIZATION role_inherited_34;
+SET ROLE pg_read_all_data; -- error
+SET ROLE role_read_34; -- success
+
+SET SESSION AUTHORIZATION role_inherited_3;
+SET ROLE pg_read_all_data; -- error
+SET ROLE role_read_34; -- error
+
+SET SESSION AUTHORIZATION role_read_all_noinherit;
+SELECT * FROM data; -- error
+SET ROLE pg_read_all_data; -- success
+SELECT * FROM data; -- success
+
+SET SESSION AUTHORIZATION role_read_12_noinherit;
+SELECT * FROM data; -- error
+SET ROLE role_read_12; -- success
+SELECT * FROM data; -- success
+
+-- Test membership privileges (db_2)
+\connect db_2
+SET ROLE role_read_all_with_admin;
+SELECT * FROM data; -- success
+SET ROLE role_read_12;
+SELECT * FROM data; -- success
+SET ROLE role_read_34;
+SELECT * FROM data; -- error
+SET ROLE role_inherited_34;
+SELECT * FROM data; -- error
+SET ROLE role_inherited_3;
+SELECT * FROM data; -- error
+SET ROLE role_read_all_noinherit;
+SELECT * FROM data; -- error
+SET ROLE role_read_12_noinherit;
+SELECT * FROM data; -- error
+
+SET SESSION AUTHORIZATION role_read_12;
+SET ROLE pg_read_all_data; -- success
+
+SET SESSION AUTHORIZATION role_inherited_34;
+SET ROLE pg_read_all_data; -- error
+SET ROLE role_read_34; -- success
+
+SET SESSION AUTHORIZATION role_inherited_3;
+SET ROLE pg_read_all_data; -- error
+SET ROLE role_read_34; -- error
+
+SET SESSION AUTHORIZATION role_read_all_noinherit;
+SELECT * FROM data; -- error
+SET ROLE pg_read_all_data; -- success
+SELECT * FROM data; -- success
+
+SET SESSION AUTHORIZATION role_read_12_noinherit;
+SELECT * FROM data; -- error
+SET ROLE role_read_12; -- success
+SELECT * FROM data; -- success
+
+-- Test membership privileges (db_3)
+\connect db_3
+SET ROLE role_read_all_with_admin;
+SELECT * FROM data; -- success
+SET ROLE role_read_12;
+SELECT * FROM data; -- error
+SET ROLE role_read_34;
+SELECT * FROM data; -- success
+SET ROLE role_inherited_34;
+SELECT * FROM data; -- success
+SET ROLE role_inherited_3;
+SELECT * FROM data; -- success
+SET ROLE role_read_all_noinherit;
+SELECT * FROM data; -- error
+SET ROLE role_read_12_noinherit;
+SELECT * FROM data; -- error
+
+SET SESSION AUTHORIZATION role_read_12;
+SET ROLE pg_read_all_data; -- error
+
+SET SESSION AUTHORIZATION role_inherited_34;
+SET ROLE pg_read_all_data; -- success
+SET ROLE role_read_34; -- success
+
+SET SESSION AUTHORIZATION role_inherited_3;
+SET ROLE pg_read_all_data; -- success
+SET ROLE role_read_34; -- success
+
+SET SESSION AUTHORIZATION role_read_all_noinherit;
+SELECT * FROM data; -- error
+SET ROLE pg_read_all_data; -- success
+SELECT * FROM data; -- success
+
+SET SESSION AUTHORIZATION role_read_12_noinherit;
+SELECT * FROM data; -- error
+SET ROLE role_read_12; -- error
+SELECT * FROM data; -- error
+
+-- Test membership privileges (db_4)
+\connect db_4
+SET ROLE role_read_all_with_admin;
+SELECT * FROM data; -- success
+SET ROLE role_read_12;
+SELECT * FROM data; -- error
+SET ROLE role_read_34;
+SELECT * FROM data; -- success
+SET ROLE role_inherited_34;
+SELECT * FROM data; -- success
+SET ROLE role_inherited_3;
+SELECT * FROM data; -- error
+SET ROLE role_read_all_noinherit;
+SELECT * FROM data; -- error
+SET ROLE role_read_12_noinherit;
+SELECT * FROM data; -- error
+
+SET SESSION AUTHORIZATION role_read_12;
+SET ROLE pg_read_all_data; -- error
+
+SET SESSION AUTHORIZATION role_inherited_34;
+SET ROLE pg_read_all_data; -- success
+SET ROLE role_read_34; -- success
+
+SET SESSION AUTHORIZATION role_inherited_3;
+SET ROLE pg_read_all_data; -- error
+SET ROLE role_read_34; -- error
+
+SET SESSION AUTHORIZATION role_read_all_noinherit;
+SELECT * FROM data; -- error
+SET ROLE pg_read_all_data; -- success
+SELECT * FROM data; -- success
+
+SET SESSION AUTHORIZATION role_read_12_noinherit;
+SELECT * FROM data; -- error
+SET ROLE role_read_12; -- error
+SELECT * FROM data; -- error
+
+\connect postgres role_admin
+
+-- Should not warn if revoking admin option
+REVOKE ADMIN OPTION FOR pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- silent
+REVOKE ADMIN OPTION FOR pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- silent
+SELECT * FROM check_memberships();
+
+-- Should warn if revoking a non-existent membership
+REVOKE pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- success
+REVOKE pg_read_all_data FROM role_read_template1 IN DATABASE template1; -- warning
+SELECT * FROM check_memberships();
+
+-- Revoke should only apply to the specified level
+REVOKE pg_read_all_data FROM role_read_12; -- warning
+SELECT * FROM check_memberships();
+
+-- Ensure cluster-wide admin option can grant cluster-wide and in specific databases
+CREATE ROLE role_granted;
+SET SESSION AUTHORIZATION role_read_all_with_admin;
+GRANT pg_read_all_data TO role_granted; -- success
+GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success
+GRANT pg_read_all_data TO role_granted IN DATABASE db_1; -- success
+GRANT role_read_34 TO role_granted; -- error
+SELECT * FROM check_memberships();
+
+-- Ensure database-specific admin option can only grant within that database
+SET SESSION AUTHORIZATION role_read_34;
+GRANT pg_read_all_data TO role_granted; -- error
+GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- error
+GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- error
+GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- error
+
+\connect db_3
+SET SESSION AUTHORIZATION role_read_34;
+GRANT pg_read_all_data TO role_granted; -- error
+GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success
+GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- notice
+GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- error
+
+\connect db_4
+SET SESSION AUTHORIZATION role_read_34;
+GRANT pg_read_all_data TO role_granted; -- error
+GRANT pg_read_all_data TO role_granted IN CURRENT DATABASE; -- success
+GRANT pg_read_all_data TO role_granted IN DATABASE db_3; -- error
+GRANT pg_read_all_data TO role_granted IN DATABASE db_4; -- notice
+
+\connect postgres role_admin
+SELECT * FROM check_memberships();
+
+-- Should clean up the membership table when dropping a database
+\connect postgres role_admin
+DROP DATABASE db_3;
+SELECT * FROM check_memberships();
+
+-- Should clean up the membership table when dropping a role
+DROP ROLE role_read_34;
+SELECT * FROM check_memberships();
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index 215eb899be3e..79fb69059b68 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -197,6 +197,7 @@ NOTICE: checking pg_tablespace {spcowner} => pg_authid {oid}
NOTICE: checking pg_auth_members {roleid} => pg_authid {oid}
NOTICE: checking pg_auth_members {member} => pg_authid {oid}
NOTICE: checking pg_auth_members {grantor} => pg_authid {oid}
+NOTICE: checking pg_auth_members {dbid} => pg_database {oid}
NOTICE: checking pg_shdepend {dbid} => pg_database {oid}
NOTICE: checking pg_shdepend {classid} => pg_class {oid}
NOTICE: checking pg_shdepend {refclassid} => pg_class {oid}
