Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite5b8a4c

Browse files
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 createsa role (necessarily by relying up the CREATEROLE privilege) willgrant that role back to themselves with the specified options.This isn't a security feature, because the grant that this featuretriggers can also be performed explicitly. Instead, it's a user experiencefeature. A superuser would necessarily inherit the privileges of anycreated 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 notSUPERUSER.Discussion:https://postgr.es/m/CA+TgmobN59ct+Emmz6ig1Nua2Q-_o=r6DSD98KfU53kctq_kQw@mail.gmail.com
1 parentcf5eb37 commite5b8a4c

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
#defineGRANT_ROLE_SPECIFIED_INHERIT0x0002
8283
#defineGRANT_ROLE_SPECIFIED_SET0x0004
8384

84-
/* GUCparameter */
85+
/* GUCparameters */
8586
intPassword_encryption=PASSWORD_TYPE_SCRAM_SHA_256;
87+
char*createrole_self_grant="";
88+
boolcreaterole_self_grant_enabled= false;
89+
GrantRoleOptionscreaterole_self_grant_options;
8690

8791
/* Hook to check passwords in CreateRole() and AlterRole() */
8892
check_password_hook_typecheck_password_hook=NULL;
@@ -532,10 +536,13 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
532536
if (!superuser())
533537
{
534538
RoleSpec*current_role=makeNode(RoleSpec);
535-
GrantRoleOptionspoptself;
539+
GrantRoleOptionspoptself;
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,GucSourcesource)
2444+
{
2445+
char*rawstring;
2446+
List*elemlist;
2447+
ListCell*l;
2448+
unsignedoptions=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+
elseif (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(constchar*newval,void*extra)
2495+
{
2496+
unsignedoptions=* (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-
externPGDLLIMPORTintPassword_encryption;
20+
/* GUCs */
21+
externPGDLLIMPORTintPassword_encryption;/* values from enum PasswordType */
22+
externPGDLLIMPORTchar*createrole_self_grant;
2123

2224
/* Hook to check passwords in CreateRole() and AlterRole() */
2325
typedefvoid (*check_password_hook_type) (constchar*username,constchar*shadow_pass,PasswordTypepassword_type,Datumvaliduntil_time,boolvaliduntil_null);
@@ -34,4 +36,8 @@ extern void DropOwnedObjects(DropOwnedStmt *stmt);
3436
externvoidReassignOwnedObjects(ReassignOwnedStmt*stmt);
3537
externList*roleSpecsToIds(List*memberNames);
3638

39+
externboolcheck_createrole_self_grant(char**newval,void**extra,
40+
GucSourcesource);
41+
externvoidassign_createrole_self_grant(constchar*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 CREATEON DATABASE regression TO regress_role_admin WITHGRANT 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 CREATEON DATABASE regression TO regress_createrole WITHGRANT OPTION;
1618
CREATE ROLE regress_login LOGIN;
1719
CREATE ROLE regress_inherit INHERIT;
1820
CREATE ROLE regress_connection_limit CONNECTIONLIMIT5;
@@ -83,9 +85,40 @@ DROP TABLE tenant_table;
8385
ALTERVIEW tenant_view OWNER TO regress_role_admin;
8486
DROPVIEW tenant_view;
8587

88+
-- fail, can't create objects owned as regress_tenant
89+
CREATESCHEMAregress_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 CREATEON DATABASE regression TO regress_tenant2;
98+
99+
-- ok, regress_tenant2 can create objects within the database
100+
SET SESSION AUTHORIZATION regress_tenant2;
101+
CREATETABLEtenant2_table (iinteger);
102+
REVOKE ALL PRIVILEGESON tenant2_tableFROM PUBLIC;
103+
104+
-- ok, because we have SET and INHERIT on regress_tenant2
105+
SET SESSION AUTHORIZATION regress_createrole;
106+
CREATESCHEMAregress_tenant2_schema AUTHORIZATION regress_tenant2;
107+
ALTERSCHEMA regress_tenant2_schema OWNER TO regress_createrole;
108+
ALTERTABLE tenant2_table OWNER TO regress_createrole;
109+
ALTERTABLE 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_tenant2FROM regress_createrole;
113+
ALTERSCHEMA regress_tenant2_schema OWNER TO regress_tenant2;
114+
ALTERTABLE 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+
ALTERTABLE tenant2_table OWNER TO regress_createrole;
119+
ALTERTABLE tenant2_table OWNER TO regress_tenant2;
120+
DROPTABLE tenant2_table;
121+
89122
-- fail, CREATEROLE is not enough to create roles in privileged roles
90123
CREATE ROLE regress_read_all_dataIN ROLE pg_read_all_data;
91124
CREATE ROLE regress_write_all_dataIN 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 CREATEON DATABASE regressionFROM 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 CREATEON DATABASE regressionFROM regress_role_admin CASCADE;
134171
DROPINDEX tenant_idx;
135172
DROPTABLE tenant_table;
136173
DROPVIEW tenant_view;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp