Skip to content

Commit fd4fe64

Browse files
robertmhaaspull[bot]
robertmhaas
authored and
pull[bot]
committed
Add new GUC createrole_self_grant.
Can be set to the empty string, or to either or both of "set" or "inherit". If set to a non-empty value, a non-superuser who creates a role (necessarily by relying up the CREATEROLE privilege) will grant that role back to themselves with the specified options. This isn't a security feature, because the grant that this feature triggers can also be performed explicitly. Instead, it's a user experience feature. A superuser would necessarily inherit the privileges of any created role and be able to access all such roles via SET ROLE; with this patch, you can configure createrole_self_grant = 'set, inherit' to provide a similar experience for a user who has CREATEROLE but not SUPERUSER. Discussion: https://postgr.es/m/CA+TgmobN59ct+Emmz6ig1Nua2Q-_o=r6DSD98KfU53kctq_kQw@mail.gmail.com
1 parent 9214d35 commit fd4fe64

File tree

9 files changed

+220
-5
lines changed

9 files changed

+220
-5
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9447,6 +9447,39 @@ SET XML OPTION { DOCUMENT | CONTENT };
94479447
</listitem>
94489448
</varlistentry>
94499449

9450+
<varlistentry id="guc-createrole-self-grant" xreflabel="createrole_self_grant">
9451+
<term><varname>createrole_self_grant</varname> (<type>string</type>)
9452+
<indexterm>
9453+
<primary><varname>createrole_self_grant</varname></primary>
9454+
<secondary>configuration parameter</secondary>
9455+
</indexterm>
9456+
</term>
9457+
<listitem>
9458+
<para>
9459+
If a user who has <literal>CREATEROLE</literal> but not
9460+
<literal>SUPERUSER</literal> creates a role, and if this
9461+
is set to a non-empty value, the newly-created role will be granted
9462+
to the creating user with the options specified. The value must be
9463+
<literal>set</literal>, <literal>inherit</literal>, or a
9464+
comma-separated list of these.
9465+
</para>
9466+
<para>
9467+
The purpose of this option is to allow a <literal>CREATEROLE</literal>
9468+
user who is not a superuser to automatically inherit, or automatically
9469+
gain the ability to <literal>SET ROLE</literal> to, any created users.
9470+
Since a <literal>CREATEROLE</literal> user is always implicitly granted
9471+
<literal>ADMIN OPTION</literal> on created roles, that user could
9472+
always execute a <literal>GRANT</literal> statement that would achieve
9473+
the same effect as this setting. However, it can be convenient for
9474+
usability reasons if the grant happens automatically. A superuser
9475+
automatically inherits the privileges of every role and can always
9476+
<literal>SET ROLE</literal> to any role, and this setting can be used
9477+
to produce a similar behavior for <literal>CREATEROLE</literal> users
9478+
for users which they create.
9479+
</para>
9480+
</listitem>
9481+
</varlistentry>
9482+
94509483
</variablelist>
94519484
</sect2>
94529485
<sect2 id="runtime-config-client-format">

‎doc/src/sgml/ref/create_role.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -506,6 +506,7 @@ CREATE ROLE <replaceable class="parameter">name</replaceable> [ WITH ADMIN <repl
506506
<member><xref linkend="sql-grant"/></member>
507507
<member><xref linkend="sql-revoke"/></member>
508508
<member><xref linkend="app-createuser"/></member>
509+
<member><xref linkend="guc-createrole-self-grant"/></member>
509510
</simplelist>
510511
</refsect1>
511512
</refentry>

‎doc/src/sgml/ref/createuser.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -555,6 +555,7 @@ PostgreSQL documentation
555555
<simplelist type="inline">
556556
<member><xref linkend="app-dropuser"/></member>
557557
<member><xref linkend="sql-createrole"/></member>
558+
<member><xref linkend="guc-createrole-self-grant"/></member>
558559
</simplelist>
559560
</refsect1>
560561

‎src/backend/commands/user.c

Lines changed: 94 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@
3939
#include "utils/fmgroids.h"
4040
#include "utils/syscache.h"
4141
#include "utils/timestamp.h"
42+
#include "utils/varlena.h"
4243

4344
/*
4445
* Removing a role grant - or the admin option on it - might recurse to
@@ -81,8 +82,11 @@ typedef struct
8182
#define GRANT_ROLE_SPECIFIED_INHERIT 0x0002
8283
#define GRANT_ROLE_SPECIFIED_SET 0x0004
8384

84-
/* GUC parameter */
85+
/* GUC parameters */
8586
int Password_encryption = PASSWORD_TYPE_SCRAM_SHA_256;
87+
char *createrole_self_grant = "";
88+
bool createrole_self_grant_enabled = false;
89+
GrantRoleOptions createrole_self_grant_options;
8690

8791
/* Hook to check passwords in CreateRole() and AlterRole() */
8892
check_password_hook_type check_password_hook = NULL;
@@ -532,10 +536,13 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
532536
if (!superuser())
533537
{
534538
RoleSpec *current_role = makeNode(RoleSpec);
535-
GrantRoleOptions poptself;
539+
GrantRoleOptions poptself;
540+
List *memberSpecs;
541+
List *memberIds = list_make1_oid(currentUserId);
536542

537543
current_role->roletype = ROLESPEC_CURRENT_ROLE;
538544
current_role->location = -1;
545+
memberSpecs = list_make1(current_role);
539546

540547
poptself.specified = GRANT_ROLE_SPECIFIED_ADMIN
541548
| GRANT_ROLE_SPECIFIED_INHERIT
@@ -545,14 +552,28 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
545552
poptself.set = false;
546553

547554
AddRoleMems(BOOTSTRAP_SUPERUSERID, stmt->role, roleid,
548-
list_make1(current_role), list_make1_oid(GetUserId()),
555+
memberSpecs, memberIds,
549556
BOOTSTRAP_SUPERUSERID, &poptself);
550557

551558
/*
552559
* We must make the implicit grant visible to the code below, else
553560
* the additional grants will fail.
554561
*/
555562
CommandCounterIncrement();
563+
564+
/*
565+
* Because of the implicit grant above, a CREATEROLE user who creates
566+
* a role has the ability to grant that role back to themselves with
567+
* the INHERIT or SET options, if they wish to inherit the role's
568+
* privileges or be able to SET ROLE to it. The createrole_self_grant
569+
* GUC can be used to make this happen automatically. This has no
570+
* security implications since the same user is able to make the same
571+
* grant using an explicit GRANT statement; it's just convenient.
572+
*/
573+
if (createrole_self_grant_enabled)
574+
AddRoleMems(currentUserId, stmt->role, roleid,
575+
memberSpecs, memberIds,
576+
currentUserId, &createrole_self_grant_options);
556577
}
557578

558579
/*
@@ -2414,3 +2435,73 @@ InitGrantRoleOptions(GrantRoleOptions *popt)
24142435
popt->inherit = false;
24152436
popt->set = true;
24162437
}
2438+
2439+
/*
2440+
* GUC check_hook for createrole_self_grant
2441+
*/
2442+
bool
2443+
check_createrole_self_grant(char **newval, void **extra, GucSource source)
2444+
{
2445+
char *rawstring;
2446+
List *elemlist;
2447+
ListCell *l;
2448+
unsigned options = 0;
2449+
unsigned *result;
2450+
2451+
/* Need a modifiable copy of string */
2452+
rawstring = pstrdup(*newval);
2453+
2454+
if (!SplitIdentifierString(rawstring, ',', &elemlist))
2455+
{
2456+
/* syntax error in list */
2457+
GUC_check_errdetail("List syntax is invalid.");
2458+
pfree(rawstring);
2459+
list_free(elemlist);
2460+
return false;
2461+
}
2462+
2463+
foreach(l, elemlist)
2464+
{
2465+
char *tok = (char *) lfirst(l);
2466+
2467+
if (pg_strcasecmp(tok, "SET") == 0)
2468+
options |= GRANT_ROLE_SPECIFIED_SET;
2469+
else if (pg_strcasecmp(tok, "INHERIT") == 0)
2470+
options |= GRANT_ROLE_SPECIFIED_INHERIT;
2471+
else
2472+
{
2473+
GUC_check_errdetail("Unrecognized key word: \"%s\".", tok);
2474+
pfree(rawstring);
2475+
list_free(elemlist);
2476+
return false;
2477+
}
2478+
}
2479+
2480+
pfree(rawstring);
2481+
list_free(elemlist);
2482+
2483+
result = (unsigned *) guc_malloc(LOG, sizeof(unsigned));
2484+
*result = options;
2485+
*extra = result;
2486+
2487+
return true;
2488+
}
2489+
2490+
/*
2491+
* GUC assign_hook for createrole_self_grant
2492+
*/
2493+
void
2494+
assign_createrole_self_grant(const char *newval, void *extra)
2495+
{
2496+
unsigned options = * (unsigned *) extra;
2497+
2498+
createrole_self_grant_enabled = (options != 0);
2499+
createrole_self_grant_options.specified = GRANT_ROLE_SPECIFIED_ADMIN
2500+
| GRANT_ROLE_SPECIFIED_INHERIT
2501+
| GRANT_ROLE_SPECIFIED_SET;
2502+
createrole_self_grant_options.admin = false;
2503+
createrole_self_grant_options.inherit =
2504+
(options & GRANT_ROLE_SPECIFIED_INHERIT) != 0;
2505+
createrole_self_grant_options.set =
2506+
(options & GRANT_ROLE_SPECIFIED_SET) != 0;
2507+
}

‎src/backend/utils/misc/guc_tables.c

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3949,6 +3949,18 @@ struct config_string ConfigureNamesString[] =
39493949
check_temp_tablespaces, assign_temp_tablespaces, NULL
39503950
},
39513951

3952+
{
3953+
{"createrole_self_grant", PGC_USERSET, CLIENT_CONN_STATEMENT,
3954+
gettext_noop("Sets whether a CREATEROLE user automatically grants "
3955+
"the role to themselves, and with which options."),
3956+
NULL,
3957+
GUC_LIST_INPUT
3958+
},
3959+
&createrole_self_grant,
3960+
"",
3961+
check_createrole_self_grant, assign_createrole_self_grant, NULL
3962+
},
3963+
39523964
{
39533965
{"dynamic_library_path", PGC_SUSET, CLIENT_CONN_OTHER,
39543966
gettext_noop("Sets the path for dynamically loadable modules."),

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -703,6 +703,7 @@
703703
#xmlbinary = 'base64'
704704
#xmloption = 'content'
705705
#gin_pending_list_limit = 4MB
706+
#createrole_self_grant = '' # set and/or inherit
706707

707708
# - Locale and Formatting -
708709

‎src/include/commands/user.h

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -15,9 +15,11 @@
1515
#include "libpq/crypt.h"
1616
#include "nodes/parsenodes.h"
1717
#include "parser/parse_node.h"
18+
#include "utils/guc.h"
1819

19-
/* GUC. Is actually of type PasswordType. */
20-
extern PGDLLIMPORT int Password_encryption;
20+
/* GUCs */
21+
extern PGDLLIMPORT int Password_encryption; /* values from enum PasswordType */
22+
extern PGDLLIMPORT char *createrole_self_grant;
2123

2224
/* Hook to check passwords in CreateRole() and AlterRole() */
2325
typedef void (*check_password_hook_type) (const char *username, const char *shadow_pass, PasswordType password_type, Datum validuntil_time, bool validuntil_null);
@@ -34,4 +36,8 @@ extern void DropOwnedObjects(DropOwnedStmt *stmt);
3436
extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt);
3537
extern List *roleSpecsToIds(List *memberNames);
3638

39+
extern bool check_createrole_self_grant(char **newval, void **extra,
40+
GucSource source);
41+
extern void assign_createrole_self_grant(const char *newval, void *extra);
42+
3743
#endif /* USER_H */

‎src/test/regress/expected/create_role.out

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
-- ok, superuser can create users with any set of privileges
22
CREATE ROLE regress_role_super SUPERUSER;
33
CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS;
4+
GRANT CREATE ON DATABASE regression TO regress_role_admin WITH GRANT OPTION;
45
CREATE ROLE regress_role_normal;
56
-- fail, only superusers can create users with these privileges
67
SET SESSION AUTHORIZATION regress_role_admin;
@@ -15,6 +16,7 @@ ERROR: must be superuser to create bypassrls users
1516
-- ok, having CREATEROLE is enough to create users with these privileges
1617
CREATE ROLE regress_createdb CREATEDB;
1718
CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
19+
GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION;
1820
CREATE ROLE regress_login LOGIN;
1921
CREATE ROLE regress_inherit INHERIT;
2022
CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -83,9 +85,37 @@ ALTER VIEW tenant_view OWNER TO regress_role_admin;
8385
ERROR: must be owner of view tenant_view
8486
DROP VIEW tenant_view;
8587
ERROR: must be owner of view tenant_view
88+
-- fail, can't create objects owned as regress_tenant
89+
CREATE SCHEMA regress_tenant_schema AUTHORIZATION regress_tenant;
90+
ERROR: must be able to SET ROLE "regress_tenant"
8691
-- fail, we don't inherit permissions from regress_tenant
8792
REASSIGN OWNED BY regress_tenant TO regress_createrole;
8893
ERROR: permission denied to reassign objects
94+
-- ok, create a role with a value for createrole_self_grant
95+
SET createrole_self_grant = 'set, inherit';
96+
CREATE ROLE regress_tenant2;
97+
GRANT CREATE ON DATABASE regression TO regress_tenant2;
98+
-- ok, regress_tenant2 can create objects within the database
99+
SET SESSION AUTHORIZATION regress_tenant2;
100+
CREATE TABLE tenant2_table (i integer);
101+
REVOKE ALL PRIVILEGES ON tenant2_table FROM PUBLIC;
102+
-- ok, because we have SET and INHERIT on regress_tenant2
103+
SET SESSION AUTHORIZATION regress_createrole;
104+
CREATE SCHEMA regress_tenant2_schema AUTHORIZATION regress_tenant2;
105+
ALTER SCHEMA regress_tenant2_schema OWNER TO regress_createrole;
106+
ALTER TABLE tenant2_table OWNER TO regress_createrole;
107+
ALTER TABLE tenant2_table OWNER TO regress_tenant2;
108+
-- with SET but not INHERIT, we can give away objects but not take them
109+
REVOKE INHERIT OPTION FOR regress_tenant2 FROM regress_createrole;
110+
ALTER SCHEMA regress_tenant2_schema OWNER TO regress_tenant2;
111+
ALTER TABLE tenant2_table OWNER TO regress_createrole;
112+
ERROR: must be owner of table tenant2_table
113+
-- with INHERIT but not SET, we can take objects but not give them away
114+
GRANT regress_tenant2 TO regress_createrole WITH INHERIT TRUE, SET FALSE;
115+
ALTER TABLE tenant2_table OWNER TO regress_createrole;
116+
ALTER TABLE tenant2_table OWNER TO regress_tenant2;
117+
ERROR: must be able to SET ROLE "regress_tenant2"
118+
DROP TABLE tenant2_table;
89119
-- fail, CREATEROLE is not enough to create roles in privileged roles
90120
CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
91121
ERROR: must have admin option on role "pg_read_all_data"
@@ -131,6 +161,8 @@ ERROR: role "regress_nosuch_recursive" does not exist
131161
DROP ROLE regress_nosuch_admin_recursive;
132162
ERROR: role "regress_nosuch_admin_recursive" does not exist
133163
DROP ROLE regress_plainrole;
164+
-- must revoke privileges before dropping role
165+
REVOKE CREATE ON DATABASE regression FROM regress_createrole CASCADE;
134166
-- ok, should be able to drop non-superuser roles we created
135167
DROP ROLE regress_createdb;
136168
DROP ROLE regress_createrole;
@@ -149,6 +181,7 @@ DROP ROLE regress_role_admin;
149181
ERROR: current user cannot be dropped
150182
-- ok
151183
RESET SESSION AUTHORIZATION;
184+
REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE;
152185
DROP INDEX tenant_idx;
153186
DROP TABLE tenant_table;
154187
DROP VIEW tenant_view;

‎src/test/regress/sql/create_role.sql

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
-- ok, superuser can create users with any set of privileges
22
CREATE ROLE regress_role_super SUPERUSER;
33
CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS;
4+
GRANT CREATE ON DATABASE regression TO regress_role_admin WITH GRANT OPTION;
45
CREATE ROLE regress_role_normal;
56

67
-- fail, only superusers can create users with these privileges
@@ -13,6 +14,7 @@ CREATE ROLE regress_nosuch_bypassrls BYPASSRLS;
1314
-- ok, having CREATEROLE is enough to create users with these privileges
1415
CREATE ROLE regress_createdb CREATEDB;
1516
CREATE ROLE regress_createrole CREATEROLE NOINHERIT;
17+
GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION;
1618
CREATE ROLE regress_login LOGIN;
1719
CREATE ROLE regress_inherit INHERIT;
1820
CREATE ROLE regress_connection_limit CONNECTION LIMIT 5;
@@ -83,9 +85,40 @@ DROP TABLE tenant_table;
8385
ALTER VIEW tenant_view OWNER TO regress_role_admin;
8486
DROP VIEW tenant_view;
8587

88+
-- fail, can't create objects owned as regress_tenant
89+
CREATE SCHEMA regress_tenant_schema AUTHORIZATION regress_tenant;
90+
8691
-- fail, we don't inherit permissions from regress_tenant
8792
REASSIGN OWNED BY regress_tenant TO regress_createrole;
8893

94+
-- ok, create a role with a value for createrole_self_grant
95+
SET createrole_self_grant = 'set, inherit';
96+
CREATE ROLE regress_tenant2;
97+
GRANT CREATE ON DATABASE regression TO regress_tenant2;
98+
99+
-- ok, regress_tenant2 can create objects within the database
100+
SET SESSION AUTHORIZATION regress_tenant2;
101+
CREATE TABLE tenant2_table (i integer);
102+
REVOKE ALL PRIVILEGES ON tenant2_table FROM PUBLIC;
103+
104+
-- ok, because we have SET and INHERIT on regress_tenant2
105+
SET SESSION AUTHORIZATION regress_createrole;
106+
CREATE SCHEMA regress_tenant2_schema AUTHORIZATION regress_tenant2;
107+
ALTER SCHEMA regress_tenant2_schema OWNER TO regress_createrole;
108+
ALTER TABLE tenant2_table OWNER TO regress_createrole;
109+
ALTER TABLE tenant2_table OWNER TO regress_tenant2;
110+
111+
-- with SET but not INHERIT, we can give away objects but not take them
112+
REVOKE INHERIT OPTION FOR regress_tenant2 FROM regress_createrole;
113+
ALTER SCHEMA regress_tenant2_schema OWNER TO regress_tenant2;
114+
ALTER TABLE tenant2_table OWNER TO regress_createrole;
115+
116+
-- with INHERIT but not SET, we can take objects but not give them away
117+
GRANT regress_tenant2 TO regress_createrole WITH INHERIT TRUE, SET FALSE;
118+
ALTER TABLE tenant2_table OWNER TO regress_createrole;
119+
ALTER TABLE tenant2_table OWNER TO regress_tenant2;
120+
DROP TABLE tenant2_table;
121+
89122
-- fail, CREATEROLE is not enough to create roles in privileged roles
90123
CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data;
91124
CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data;
@@ -113,6 +146,9 @@ DROP ROLE regress_nosuch_recursive;
113146
DROP ROLE regress_nosuch_admin_recursive;
114147
DROP ROLE regress_plainrole;
115148

149+
-- must revoke privileges before dropping role
150+
REVOKE CREATE ON DATABASE regression FROM regress_createrole CASCADE;
151+
116152
-- ok, should be able to drop non-superuser roles we created
117153
DROP ROLE regress_createdb;
118154
DROP ROLE regress_createrole;
@@ -131,6 +167,7 @@ DROP ROLE regress_role_admin;
131167

132168
-- ok
133169
RESET SESSION AUTHORIZATION;
170+
REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE;
134171
DROP INDEX tenant_idx;
135172
DROP TABLE tenant_table;
136173
DROP VIEW tenant_view;

0 commit comments

Comments
 (0)