Skip to content

Commit 3d14e17

Browse files
robertmhaasrobertmhaas
committed
Add a SET option to the GRANT command.
Similar to how the INHERIT option controls whether or not the permissions of the granted role are automatically available to the grantee, the new SET permission controls whether or not the grantee may use the SET ROLE command to assume the privileges of the granted role. In addition, the new SET permission controls whether or not it is possible to transfer ownership of objects to the target role or to create new objects owned by the target role using commands such as CREATE DATABASE .. OWNER. We could alternatively have made this controlled by the INHERIT option, or allow it when either option is given. An advantage of this approach is that if you are granted a predefined role with INHERIT TRUE, SET FALSE, you can't go and create objects owned by that role. The underlying theory here is that the ability to create objects as a target role is not a privilege per se, and thus does not depend on whether you inherit the target role's privileges. However, it's surely something you could do anyway if you could SET ROLE to the target role, and thus making it contingent on whether you have that ability is reasonable. Design review by Nathan Bossat, Wolfgang Walther, Jeff Davis, Peter Eisentraut, and Stephen Frost. Discussion: http://postgr.es/m/CA+Tgmob+zDSRS6JXYrgq0NWdzCXuTNzT5eK54Dn2hhgt17nm8A@mail.gmail.com
1 parent f84ff0c commit 3d14e17

23 files changed

+315
-91
lines changed

‎doc/src/sgml/catalogs.sgml

+11
Original file line numberDiff line numberDiff line change
@@ -1727,6 +1727,17 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
17271727
granted role
17281728
</para></entry>
17291729
</row>
1730+
1731+
<row>
1732+
<entry role="catalog_table_entry"><para role="column_definition">
1733+
<structfield>set_option</structfield> <type>bool</type>
1734+
</para>
1735+
<para>
1736+
True if the member can
1737+
<link linkend="sql-set-role"><command>SET ROLE</command></link>
1738+
to the granted role
1739+
</para></entry>
1740+
</row>
17301741
</tbody>
17311742
</tgroup>
17321743
</table>

‎doc/src/sgml/func.sgml

+6-3
Original file line numberDiff line numberDiff line change
@@ -23033,11 +23033,14 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
2303323033
<para>
2303423034
Does user have privilege for role?
2303523035
Allowable privilege types are
23036-
<literal>MEMBER</literal> and <literal>USAGE</literal>.
23036+
<literal>MEMBER</literal>, <literal>USAGE</literal>,
23037+
and <literal>SET</literal>.
2303723038
<literal>MEMBER</literal> denotes direct or indirect membership in
23038-
the role (that is, the right to do <command>SET ROLE</command>), while
23039+
the role without regard to what specific privileges may be conferred.
2303923040
<literal>USAGE</literal> denotes whether the privileges of the role
23040-
are immediately available without doing <command>SET ROLE</command>.
23041+
are immediately available without doing <command>SET ROLE</command>,
23042+
while <literal>SET</literal> denotes whether it is possible to change
23043+
to the role using the <literal>SET ROLE</literal> command.
2304123044
This function does not allow the special case of
2304223045
setting <parameter>user</parameter> to <literal>public</literal>,
2304323046
because the PUBLIC pseudo-role can never be a member of real roles.

‎doc/src/sgml/ref/grant.sgml

+22-10
Original file line numberDiff line numberDiff line change
@@ -98,7 +98,7 @@ GRANT { USAGE | ALL [ PRIVILEGES ] }
9898
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
9999

100100
GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replaceable class="parameter">role_specification</replaceable> [, ...]
101-
[ WITH { ADMIN | INHERIT } { OPTION | TRUE | FALSE } ]
101+
[ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
102102
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
103103

104104
<phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>
@@ -250,17 +250,17 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
250250
<para>
251251
This variant of the <command>GRANT</command> command grants membership
252252
in a role to one or more other roles. Membership in a role is significant
253-
because it conveys the privileges granted to a role to each of its
254-
members.
253+
because it potentially allows access to the privileges granted to a role
254+
to each of its members, and potentially also the ability to make changes
255+
to the role itself. However, the actualy permisions conferred depend on
256+
the options associated with the grant.
255257
</para>
256258

257259
<para>
258-
The effect of membership in a role can be modified by specifying the
259-
<literal>ADMIN</literal> or <literal>INHERIT</literal> option, each
260-
of which can be set to either <literal>TRUE</literal> or
261-
<literal>FALSE</literal>. The keyword <literal>OPTION</literal> is accepted
262-
as a synonym for <literal>TRUE</literal>, so that
263-
<literal>WITH ADMIN OPTION</literal>
260+
Each of the options described below can be set to either
261+
<literal>TRUE</literal> or <literal>FALSE</literal>. The keyword
262+
<literal>OPTION</literal> is accepted as a synonym for
263+
<literal>TRUE</literal>, so that <literal>WITH ADMIN OPTION</literal>
264264
is a synonym for <literal>WITH ADMIN TRUE</literal>.
265265
</para>
266266

@@ -272,7 +272,8 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
272272
OPTION</literal> on itself. Database superusers can grant or revoke
273273
membership in any role to anyone. Roles having
274274
<literal>CREATEROLE</literal> privilege can grant or revoke membership
275-
in any role that is not a superuser.
275+
in any role that is not a superuser. This option defaults to
276+
<literal>FALSE</literal>.
276277
</para>
277278

278279
<para>
@@ -287,6 +288,17 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
287288
See <link linkend="sql-createrole"><command>CREATE ROLE</command></link>.
288289
</para>
289290

291+
<para>
292+
The <literal>SET</literal> option, if it is set to
293+
<literal>TRUE</literal>, allows the member to change to the granted
294+
role using the
295+
<link linkend="sql-set-role"><command>SET ROLE</command></link>
296+
command. If a role is an indirect member of another role, it can use
297+
<literal>SET ROLE</literal> to change to that role only if there is a
298+
chain of grants each of which has <literal>SET TRUE</literal>.
299+
This option defaults to <literal>TRUE</literal>.
300+
</para>
301+
290302
<para>
291303
If <literal>GRANTED BY</literal> is specified, the grant is recorded as
292304
having been done by the specified role. A user can only attribute a grant

‎doc/src/sgml/ref/revoke.sgml

+4-4
Original file line numberDiff line numberDiff line change
@@ -125,7 +125,7 @@ REVOKE [ GRANT OPTION FOR ]
125125
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
126126
[ CASCADE | RESTRICT ]
127127

128-
REVOKE [ { ADMIN | INHERIT } OPTION FOR ]
128+
REVOKE [ { ADMIN | INHERIT | SET } OPTION FOR ]
129129
<replaceable class="parameter">role_name</replaceable> [, ...] FROM <replaceable class="parameter">role_specification</replaceable> [, ...]
130130
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
131131
[ CASCADE | RESTRICT ]
@@ -209,9 +209,9 @@ REVOKE [ { ADMIN | INHERIT } OPTION FOR ]
209209

210210
<para>
211211
Just as <literal>ADMIN OPTION</literal> can be removed from an existing
212-
role grant, it is also possible to revoke <literal>INHERIT OPTION</literal>.
213-
This is equivalent to setting the value of that option to
214-
<literal>FALSE</literal>.
212+
role grant, it is also possible to revoke <literal>INHERIT OPTION</literal>
213+
or <literal>SET OPTION</literal>. This is equivalent to setting the value
214+
of the corresponding option to <literal>FALSE</literal>.
215215
</para>
216216
</refsect1>
217217

‎doc/src/sgml/ref/set_role.sgml

+6-3
Original file line numberDiff line numberDiff line change
@@ -77,14 +77,17 @@ RESET ROLE
7777
effectively drops all the privileges except for those which the target role
7878
directly possesses or inherits. On the other hand, if the session user role
7979
has been granted memberships <literal>WITH INHERIT FALSE</literal>, the
80-
privileges of the granted roles can't be accessed by default. However, the
80+
privileges of the granted roles can't be accessed by default. However, if
81+
the role was granted <literal>WITH SET TRUE</literal>, the
8182
session user can use <command>SET ROLE</command> to drop the privileges
8283
assigned directly to the session user and instead acquire the privileges
83-
available to the named role.
84+
available to the named role. If the role was granted <literal>WITH INHERIT
85+
FALSE, SET FALSE</literal> then the privileges of that role cannot be
86+
exercised either with or without <literal>SET ROLE</literal>.
8487
</para>
8588

8689
<para>
87-
In particular, when a superuser chooses to <command>SET ROLE</command> to a
90+
Note that when a superuser chooses to <command>SET ROLE</command> to a
8891
non-superuser role, they lose their superuser privileges.
8992
</para>
9093

‎doc/src/sgml/user-manag.sgml

+15-5
Original file line numberDiff line numberDiff line change
@@ -354,7 +354,8 @@ REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceabl
354354

355355
<para>
356356
The members of a group role can use the privileges of the role in two
357-
ways. First, every member of a group can explicitly do
357+
ways. First, member roles that have been granted membership with the
358+
<literal>SET</literal> option can do
358359
<link linkend="sql-set-role"><command>SET ROLE</command></link> to
359360
temporarily <quote>become</quote> the group role. In this state, the
360361
database session has access to the privileges of the group role rather
@@ -369,13 +370,16 @@ REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceabl
369370
CREATE ROLE joe LOGIN;
370371
CREATE ROLE admin;
371372
CREATE ROLE wheel;
373+
CREATE ROLE island;
372374
GRANT admin TO joe WITH INHERIT TRUE;
373375
GRANT wheel TO admin WITH INHERIT FALSE;
376+
GRANT island TO joe WITH INHERIT TRUE, SET FALSE;
374377
</programlisting>
375378
Immediately after connecting as role <literal>joe</literal>, a database
376379
session will have use of privileges granted directly to <literal>joe</literal>
377-
plus any privileges granted to <literal>admin</literal>, because <literal>joe</literal>
378-
<quote>inherits</quote> <literal>admin</literal>'s privileges. However, privileges
380+
plus any privileges granted to <literal>admin</literal> and
381+
<literal>island</literal>, because <literal>joe</literal>
382+
<quote>inherits</quote> those privileges. However, privileges
379383
granted to <literal>wheel</literal> are not available, because even though
380384
<literal>joe</literal> is indirectly a member of <literal>wheel</literal>, the
381385
membership is via <literal>admin</literal> which was granted using
@@ -384,7 +388,8 @@ GRANT wheel TO admin WITH INHERIT FALSE;
384388
SET ROLE admin;
385389
</programlisting>
386390
the session would have use of only those privileges granted to
387-
<literal>admin</literal>, and not those granted to <literal>joe</literal>. After:
391+
<literal>admin</literal>, and not those granted to <literal>joe</literal> or
392+
<literal>island</literal>. After:
388393
<programlisting>
389394
SET ROLE wheel;
390395
</programlisting>
@@ -402,9 +407,14 @@ RESET ROLE;
402407
<note>
403408
<para>
404409
The <command>SET ROLE</command> command always allows selecting any role
405-
that the original login role is directly or indirectly a member of.
410+
that the original login role is directly or indirectly a member of,
411+
provided that there is a chain of membership grants each of which has
412+
<literal>SET TRUE</literal> (which is the default).
406413
Thus, in the above example, it is not necessary to become
407414
<literal>admin</literal> before becoming <literal>wheel</literal>.
415+
On the other hand, it is not possible to become <literal>island</literal>
416+
at all; <literal>joe</literal> can only access those privileges via
417+
inheritance.
408418
</para>
409419
</note>
410420

‎src/backend/commands/alter.c

+1-1
Original file line numberDiff line numberDiff line change
@@ -999,7 +999,7 @@ AlterObjectOwner_internal(Relation rel, Oid objectId, Oid new_ownerId)
999999
objname);
10001000
}
10011001
/* Must be able to become new owner */
1002-
check_is_member_of_role(GetUserId(), new_ownerId);
1002+
check_can_set_role(GetUserId(), new_ownerId);
10031003

10041004
/* New owner must have CREATE privilege on namespace */
10051005
if (OidIsValid(namespaceId))

‎src/backend/commands/dbcommands.c

+2-2
Original file line numberDiff line numberDiff line change
@@ -941,7 +941,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
941941
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
942942
errmsg("permission denied to create database")));
943943

944-
check_is_member_of_role(GetUserId(), datdba);
944+
check_can_set_role(GetUserId(), datdba);
945945

946946
/*
947947
* Lookup database (template) to be cloned, and obtain share lock on it.
@@ -2495,7 +2495,7 @@ AlterDatabaseOwner(const char *dbname, Oid newOwnerId)
24952495
dbname);
24962496

24972497
/* Must be able to become new owner */
2498-
check_is_member_of_role(GetUserId(), newOwnerId);
2498+
check_can_set_role(GetUserId(), newOwnerId);
24992499

25002500
/*
25012501
* must have createdb rights

‎src/backend/commands/foreigncmds.c

+1-1
Original file line numberDiff line numberDiff line change
@@ -363,7 +363,7 @@ AlterForeignServerOwner_internal(Relation rel, HeapTuple tup, Oid newOwnerId)
363363
NameStr(form->srvname));
364364

365365
/* Must be able to become new owner */
366-
check_is_member_of_role(GetUserId(), newOwnerId);
366+
check_can_set_role(GetUserId(), newOwnerId);
367367

368368
/* New owner must have USAGE privilege on foreign-data wrapper */
369369
aclresult = object_aclcheck(ForeignDataWrapperRelationId, form->srvfdw, newOwnerId, ACL_USAGE);

‎src/backend/commands/publicationcmds.c

+1-1
Original file line numberDiff line numberDiff line change
@@ -1911,7 +1911,7 @@ AlterPublicationOwner_internal(Relation rel, HeapTuple tup, Oid newOwnerId)
19111911
NameStr(form->pubname));
19121912

19131913
/* Must be able to become new owner */
1914-
check_is_member_of_role(GetUserId(), newOwnerId);
1914+
check_can_set_role(GetUserId(), newOwnerId);
19151915

19161916
/* New owner must have CREATE privilege on database */
19171917
aclresult = object_aclcheck(DatabaseRelationId, MyDatabaseId, newOwnerId, ACL_CREATE);

‎src/backend/commands/schemacmds.c

+2-2
Original file line numberDiff line numberDiff line change
@@ -97,7 +97,7 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString,
9797
aclcheck_error(aclresult, OBJECT_DATABASE,
9898
get_database_name(MyDatabaseId));
9999

100-
check_is_member_of_role(saved_uid, owner_uid);
100+
check_can_set_role(saved_uid, owner_uid);
101101

102102
/* Additional check to protect reserved schema names */
103103
if (!allowSystemTableMods && IsReservedName(schemaName))
@@ -370,7 +370,7 @@ AlterSchemaOwner_internal(HeapTuple tup, Relation rel, Oid newOwnerId)
370370
NameStr(nspForm->nspname));
371371

372372
/* Must be able to become new owner */
373-
check_is_member_of_role(GetUserId(), newOwnerId);
373+
check_can_set_role(GetUserId(), newOwnerId);
374374

375375
/*
376376
* must have create-schema rights

‎src/backend/commands/tablecmds.c

+1-1
Original file line numberDiff line numberDiff line change
@@ -13833,7 +13833,7 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock
1383313833
RelationGetRelationName(target_rel));
1383413834

1383513835
/* Must be able to become new owner */
13836-
check_is_member_of_role(GetUserId(), newOwnerId);
13836+
check_can_set_role(GetUserId(), newOwnerId);
1383713837

1383813838
/* New owner must have CREATE privilege on namespace */
1383913839
aclresult = object_aclcheck(NamespaceRelationId, namespaceOid, newOwnerId,

‎src/backend/commands/typecmds.c

+1-1
Original file line numberDiff line numberDiff line change
@@ -3745,7 +3745,7 @@ AlterTypeOwner(List *names, Oid newOwnerId, ObjectType objecttype)
37453745
aclcheck_error_type(ACLCHECK_NOT_OWNER, typTup->oid);
37463746

37473747
/* Must be able to become new owner */
3748-
check_is_member_of_role(GetUserId(), newOwnerId);
3748+
check_can_set_role(GetUserId(), newOwnerId);
37493749

37503750
/* New owner must have CREATE privilege on namespace */
37513751
aclresult = object_aclcheck(NamespaceRelationId, typTup->typnamespace,

‎src/backend/commands/user.c

+41-3
Original file line numberDiff line numberDiff line change
@@ -51,8 +51,8 @@
5151
* RRG_REMOVE_ADMIN_OPTION indicates a grant that would need to have
5252
* admin_option set to false by the operation.
5353
*
54-
* RRG_REMOVE_INHERIT_OPTION indicates a grant that would need to have
55-
* inherit_option set to false by the operation.
54+
* Similarly, RRG_REMOVE_INHERIT_OPTION and RRG_REMOVE_SET_OPTION indicate
55+
* grants that would need to have the corresponding options set to false.
5656
*
5757
* RRG_DELETE_GRANT indicates a grant that would need to be removed entirely
5858
* by the operation.
@@ -62,6 +62,7 @@ typedef enum
6262
RRG_NOOP,
6363
RRG_REMOVE_ADMIN_OPTION,
6464
RRG_REMOVE_INHERIT_OPTION,
65+
RRG_REMOVE_SET_OPTION,
6566
RRG_DELETE_GRANT
6667
} RevokeRoleGrantAction;
6768

@@ -73,10 +74,12 @@ typedef struct
7374
unsigned specified;
7475
bool admin;
7576
bool inherit;
77+
bool set;
7678
} GrantRoleOptions;
7779

7880
#define GRANT_ROLE_SPECIFIED_ADMIN 0x0001
7981
#define GRANT_ROLE_SPECIFIED_INHERIT 0x0002
82+
#define GRANT_ROLE_SPECIFIED_SET 0x0004
8083

8184
/* GUC parameter */
8285
int Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
@@ -1389,6 +1392,12 @@ GrantRole(ParseState *pstate, GrantRoleStmt *stmt)
13891392
if (parse_bool(optval, &popt.inherit))
13901393
continue;
13911394
}
1395+
else if (strcmp(opt->defname, "set") == 0)
1396+
{
1397+
popt.specified |= GRANT_ROLE_SPECIFIED_SET;
1398+
if (parse_bool(optval, &popt.set))
1399+
continue;
1400+
}
13921401
else
13931402
ereport(ERROR,
13941403
errcode(ERRCODE_SYNTAX_ERROR),
@@ -1776,6 +1785,16 @@ AddRoleMems(const char *rolename, Oid roleid,
17761785
at_least_one_change = true;
17771786
}
17781787

1788+
if ((popt->specified & GRANT_ROLE_SPECIFIED_SET) != 0
1789+
&& authmem_form->set_option != popt->set)
1790+
{
1791+
new_record[Anum_pg_auth_members_set_option - 1] =
1792+
BoolGetDatum(popt->set);
1793+
new_record_repl[Anum_pg_auth_members_set_option - 1] =
1794+
true;
1795+
at_least_one_change = true;
1796+
}
1797+
17791798
if (!at_least_one_change)
17801799
{
17811800
ereport(NOTICE,
@@ -1798,9 +1817,15 @@ AddRoleMems(const char *rolename, Oid roleid,
17981817
Oid objectId;
17991818
Oid *newmembers = palloc(sizeof(Oid));
18001819

1801-
/* Set admin option if user set it to true, otherwise not. */
1820+
/*
1821+
* The values for these options can be taken directly from 'popt'.
1822+
* Either they were specified, or the defaults as set by
1823+
* InitGrantRoleOptions are correct.
1824+
*/
18021825
new_record[Anum_pg_auth_members_admin_option - 1] =
18031826
BoolGetDatum(popt->admin);
1827+
new_record[Anum_pg_auth_members_set_option - 1] =
1828+
BoolGetDatum(popt->set);
18041829

18051830
/*
18061831
* If the user specified a value for the inherit option, use
@@ -1989,6 +2014,13 @@ DelRoleMems(const char *rolename, Oid roleid,
19892014
new_record_repl[Anum_pg_auth_members_inherit_option - 1] =
19902015
true;
19912016
}
2017+
else if (actions[i] == RRG_REMOVE_SET_OPTION)
2018+
{
2019+
new_record[Anum_pg_auth_members_set_option - 1] =
2020+
BoolGetDatum(false);
2021+
new_record_repl[Anum_pg_auth_members_set_option - 1] =
2022+
true;
2023+
}
19922024
else
19932025
elog(ERROR, "unknown role revoke action");
19942026

@@ -2182,6 +2214,11 @@ plan_single_revoke(CatCList *memlist, RevokeRoleGrantAction *actions,
21822214
*/
21832215
actions[i] = RRG_REMOVE_INHERIT_OPTION;
21842216
}
2217+
else if ((popt->specified & GRANT_ROLE_SPECIFIED_SET) != 0)
2218+
{
2219+
/* Here too, no need to recurse. */
2220+
actions[i] = RRG_REMOVE_SET_OPTION;
2221+
}
21852222
else
21862223
{
21872224
bool revoke_admin_option_only;
@@ -2331,4 +2368,5 @@ InitGrantRoleOptions(GrantRoleOptions *popt)
23312368
popt->specified = 0;
23322369
popt->admin = false;
23332370
popt->inherit = false;
2371+
popt->set = true;
23342372
}

‎src/backend/commands/variable.c

+1-1
Original file line numberDiff line numberDiff line change
@@ -939,7 +939,7 @@ check_role(char **newval, void **extra, GucSource source)
939939
* leader's state.
940940
*/
941941
if (!InitializingParallelWorker &&
942-
!is_member_of_role(GetSessionUserId(), roleid))
942+
!member_can_set_role(GetSessionUserId(), roleid))
943943
{
944944
if (source == PGC_S_TEST)
945945
{

0 commit comments

Comments
 (0)