Skip to content

Navigation Menu

Sign in

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
Appearance settings

Commit cf5eb37

Browse filesBrowse files
robertmhaasrobertmhaas
committed
Restrict the privileges of CREATEROLE users.
Previously, CREATEROLE users were permitted to make nearly arbitrary changes to roles that they didn't create, with certain exceptions, particularly superuser roles. Instead, allow CREATEROLE users to make such changes to roles for which they possess ADMIN OPTION, and to grant membership only in roles for which they possess ADMIN OPTION. When a CREATEROLE user who is not a superuser creates a role, grant ADMIN OPTION on the newly-created role to the creator, so that they can administer roles they create or for which they have been given privileges. With these changes, CREATEROLE users still have very significant powers that unprivileged users do not receive: they can alter, rename, drop, comment on, change the password for, and change security labels on roles. However, they can now do these things only for roles for which they possess appropriate privileges, rather than all non-superuser roles; moreover, they cannot grant a role such as pg_execute_server_program unless they themselves possess it. Patch by me, reviewed by Mark Dilger. Discussion: https://postgr.es/m/CA+TgmobN59ct+Emmz6ig1Nua2Q-_o=r6DSD98KfU53kctq_kQw@mail.gmail.com
1 parent f026c16 commit cf5eb37
Copy full SHA for cf5eb37

File tree

15 files changed

+210
-106
lines changed
Filter options

15 files changed

+210
-106
lines changed

‎doc/src/sgml/ddl.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ddl.sgml
+4-6Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3216,13 +3216,11 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
32163216
name. Therefore, if each user has a separate schema, they access
32173217
their own schemas by default.) This pattern is a secure schema
32183218
usage pattern unless an untrusted user is the database owner or
3219-
holds the <literal>CREATEROLE</literal> privilege, in which case no
3220-
secure schema usage pattern exists.
3219+
has been granted <literal>ADMIN OPTION</literal> on a relevant role,
3220+
in which case no secure schema usage pattern exists.
32213221
</para>
32223222
<!-- A database owner can attack the database's users via "CREATE SCHEMA
3223-
trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". A
3224-
CREATEROLE user can issue "GRANT $dbowner TO $me" and then use the
3225-
database owner attack. -->
3223+
trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". -->
32263224

32273225
<para>
32283226
In <productname>PostgreSQL</productname> 15 and later, the default
@@ -3250,7 +3248,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
32503248
unreliable</link>. If you create functions or extensions in the public
32513249
schema, use the first pattern instead. Otherwise, like the first
32523250
pattern, this is secure unless an untrusted user is the database owner
3253-
or holds the <literal>CREATEROLE</literal> privilege.
3251+
or has been granted <literal>ADMIN OPTION</literal> on a relevant role.
32543252
</para>
32553253
</listitem>
32563254

‎doc/src/sgml/ref/alter_role.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/alter_role.sgml
+5-3Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -73,15 +73,16 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
7373
Roles having <literal>CREATEROLE</literal> privilege can change any of these
7474
settings except <literal>SUPERUSER</literal>, <literal>REPLICATION</literal>,
7575
and <literal>BYPASSRLS</literal>; but only for non-superuser and
76-
non-replication roles.
76+
non-replication roles for which they have been
77+
granted <literal>ADMIN OPTION</literal>.
7778
Ordinary roles can only change their own password.
7879
</para>
7980

8081
<para>
8182
The second variant changes the name of the role.
8283
Database superusers can rename any role.
8384
Roles having <literal>CREATEROLE</literal> privilege can rename non-superuser
84-
roles.
85+
roles for which they have been granted <literal>ADMIN OPTION</literal>.
8586
The current session user cannot be renamed.
8687
(Connect as a different user if you need to do that.)
8788
Because <literal>MD5</literal>-encrypted passwords use the role name as
@@ -116,7 +117,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
116117
<para>
117118
Superusers can change anyone's session defaults. Roles having
118119
<literal>CREATEROLE</literal> privilege can change defaults for non-superuser
119-
roles. Ordinary roles can only set defaults for themselves.
120+
roles for which they have been granted <literal>ADMIN OPTION</literal>.
121+
Ordinary roles can only set defaults for themselves.
120122
Certain configuration variables cannot be set this way, or can only be
121123
set if a superuser issues the command. Only superusers can change a setting
122124
for all roles in all databases.

‎doc/src/sgml/ref/comment.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/comment.sgml
+2-1Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -99,7 +99,8 @@ COMMENT ON
9999
For most kinds of object, only the object's owner can set the comment.
100100
Roles don't have owners, so the rule for <literal>COMMENT ON ROLE</literal> is
101101
that you must be superuser to comment on a superuser role, or have the
102-
<literal>CREATEROLE</literal> privilege to comment on non-superuser roles.
102+
<literal>CREATEROLE</literal> privilege and have been granted
103+
<literal>ADMIN OPTION</literal> on the target role.
103104
Likewise, access methods don't have owners either; you must be superuser
104105
to comment on an access method.
105106
Of course, a superuser can comment on anything.

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/create_role.sgml
+2-2Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -119,8 +119,8 @@ in sync when changing the above synopsis!
119119
<listitem>
120120
<para>
121121
These clauses determine whether a role will be permitted to
122-
create, alter, drop, comment on, change the security label for,
123-
and grant or revoke membership in other roles.
122+
create, alter, drop, comment on, and change the security label for
123+
other roles.
124124
See <xref linkend='role-creation' /> for more details about what
125125
capabilities are conferred by this privilege.
126126
If not specified, <literal>NOCREATEROLE</literal> is the default.

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/createuser.sgml
+1-2Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -252,8 +252,7 @@ PostgreSQL documentation
252252
<listitem>
253253
<para>
254254
The new user will be allowed to create, alter, drop, comment on,
255-
change the security label for, and grant or revoke membership in
256-
other roles; that is,
255+
change the security label for other roles; that is,
257256
this user will have <literal>CREATEROLE</literal> privilege.
258257
See <xref linkend='role-creation' /> for more details about what
259258
capabilities are conferred by this privilege.

‎doc/src/sgml/ref/drop_role.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/drop_role.sgml
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,7 @@ DROP ROLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...
3232
<command>DROP ROLE</command> removes the specified role(s).
3333
To drop a superuser role, you must be a superuser yourself;
3434
to drop non-superuser roles, you must have <literal>CREATEROLE</literal>
35-
privilege.
35+
privilege and have been granted <literal>ADMIN OPTION</literal> on the role.
3636
</para>
3737

3838
<para>

‎doc/src/sgml/ref/dropuser.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/dropuser.sgml
+4-3Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -35,9 +35,10 @@ PostgreSQL documentation
3535
<para>
3636
<application>dropuser</application> removes an existing
3737
<productname>PostgreSQL</productname> user.
38-
Only superusers and users with the <literal>CREATEROLE</literal> privilege can
39-
remove <productname>PostgreSQL</productname> users. (To remove a
40-
superuser, you must yourself be a superuser.)
38+
Superusers can use this command to remove any role; otherwise, only
39+
non-superuser roles can be removed, and only by a user who possesses
40+
the <literal>CREATEROLE</literal> privilege and has been granted
41+
<literal>ADMIN OPTION</literal> on the target role.
4142
</para>
4243

4344
<para>

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/grant.sgml
+1-3Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -271,9 +271,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
271271
in the role as well. Without the admin option, ordinary users cannot
272272
do that. A role is not considered to hold <literal>WITH ADMIN
273273
OPTION</literal> on itself. Database superusers can grant or revoke
274-
membership in any role to anyone. Roles having
275-
<literal>CREATEROLE</literal> privilege can grant or revoke membership
276-
in any role that is not a superuser. This option defaults to
274+
membership in any role to anyone. This option defaults to
277275
<literal>FALSE</literal>.
278276
</para>
279277

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

Copy file name to clipboardExpand all lines: doc/src/sgml/user-manag.sgml
+34-10Lines changed: 34 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -199,7 +199,12 @@ CREATE USER <replaceable>name</replaceable>;
199199
checks). To create such a role, use <literal>CREATE ROLE
200200
<replaceable>name</replaceable> CREATEROLE</literal>.
201201
A role with <literal>CREATEROLE</literal> privilege can alter and drop
202-
other roles, too, as well as grant or revoke membership in them.
202+
roles which have been granted to the <literal>CREATEROLE</literal>
203+
user with the <literal>ADMIN</literal> option. Such a grant occurs
204+
automatically when a <literal>CREATEROLE</literal> user that is not
205+
a superuser creates a new role, so that by default, a
206+
<literal>CREATEROLE</literal> user can alter and drop the roles
207+
which they have created.
203208
Altering a role includes most changes that can be made using
204209
<literal>ALTER ROLE</literal>, including, for example, changing
205210
passwords. It also includes modifications to a role that can
@@ -224,15 +229,6 @@ CREATE USER <replaceable>name</replaceable>;
224229
confer the ability to grant or revoke the <literal>BYPASSRLS</literal>
225230
privilege.
226231
</para>
227-
<para>
228-
Because the <literal>CREATEROLE</literal> privilege allows a user
229-
to grant or revoke membership even in roles to which it does not (yet)
230-
have any access, a <literal>CREATEROLE</literal> user can obtain access
231-
to the capabilities of every predefined role in the system, including
232-
highly privileged roles such as
233-
<literal>pg_execute_server_program</literal> and
234-
<literal>pg_write_server_files</literal>.
235-
</para>
236232
</listitem>
237233
</varlistentry>
238234

@@ -329,6 +325,34 @@ ALTER ROLE myname SET enable_indexscan TO off;
329325
<literal>LOGIN</literal> privilege are fairly useless, since they will never
330326
be invoked.
331327
</para>
328+
329+
<para>
330+
When a non-superuser creates a role using the <literal>CREATEROLE</literal>
331+
privilege, the created role is automatically granted back to the creating
332+
user, just as if the bootstrap superuser had executed the command
333+
<literal>GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE,
334+
INHERIT FALSE</literal>. Since a <literal>CREATEROLE</literal> user can
335+
only exercise special privileges with regard to an existing role if they
336+
have <literal>ADMIN OPTION</literal> on it, this grant is just sufficient
337+
to allow a <literal>CREATEROLE</literal> user to administer the roles they
338+
created. However, because it is created with <literal>INHERIT FALSE, SET
339+
FALSE</literal>, the <literal>CREATEROLE</literal> user doesn't inherit the
340+
privileges of the created role, nor can it access the privileges of that
341+
role using <literal>SET ROLE</literal>. However, since any user who has
342+
<literal>ADMIN OPTION</literal> on a role can grant membership in that
343+
role to any other user, the <literal>CREATEROLE</literal> user can gain
344+
access to the created role by simplying granting that role back to
345+
themselves with the <literal>INHERIT</literal> and/or <literal>SET</literal>
346+
options. Thus, the fact that privileges are not inherited by default nor
347+
is <literal>SET ROLE</literal> granted by default is a safeguard against
348+
accidents, not a security feature. Also note that, because this automatic
349+
grant is granted by the bootstrap user, it cannot be removed or changed by
350+
the <literal>CREATEROLE</literal> user; however, any superuser could
351+
revoke it, modify it, and/or issue additional such grants to other
352+
<literal>CREATEROLE</literal> users. Whichever <literal>CREATEROLE</literal>
353+
users have <literal>ADMIN OPTION</literal> on a role at any given time
354+
can administer it.
355+
</para>
332356
</sect1>
333357

334358
<sect1 id="role-membership">

‎src/backend/catalog/objectaddress.c

Copy file name to clipboardExpand all lines: src/backend/catalog/objectaddress.c
+9-1Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2538,7 +2538,9 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
25382538

25392539
/*
25402540
* We treat roles as being "owned" by those with CREATEROLE priv,
2541-
* except that superusers are only owned by superusers.
2541+
* provided that they also have admin option on the role.
2542+
*
2543+
* However, superusers are only owned by superusers.
25422544
*/
25432545
if (superuser_arg(address.objectId))
25442546
{
@@ -2553,6 +2555,12 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
25532555
ereport(ERROR,
25542556
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
25552557
errmsg("must have CREATEROLE privilege")));
2558+
if (!is_admin_of_role(roleid, address.objectId))
2559+
ereport(ERROR,
2560+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
2561+
errmsg("must have admin option on role \"%s\"",
2562+
GetUserNameFromId(address.objectId,
2563+
true))));
25562564
}
25572565
break;
25582566
case OBJECT_TSPARSER:

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.