From cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Tue, 10 Jan 2023 12:44:30 -0500 Subject: 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 --- doc/src/sgml/ddl.sgml | 10 ++++----- doc/src/sgml/ref/alter_role.sgml | 8 ++++--- doc/src/sgml/ref/comment.sgml | 3 ++- doc/src/sgml/ref/create_role.sgml | 4 ++-- doc/src/sgml/ref/createuser.sgml | 3 +-- doc/src/sgml/ref/drop_role.sgml | 2 +- doc/src/sgml/ref/dropuser.sgml | 7 ++++--- doc/src/sgml/ref/grant.sgml | 4 +--- doc/src/sgml/user-manag.sgml | 44 ++++++++++++++++++++++++++++++--------- 9 files changed, 54 insertions(+), 31 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index d91a7814794..4b219435d45 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3216,13 +3216,11 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; name. Therefore, if each user has a separate schema, they access their own schemas by default.) This pattern is a secure schema usage pattern unless an untrusted user is the database owner or - holds the CREATEROLE privilege, in which case no - secure schema usage pattern exists. + has been granted ADMIN OPTION on a relevant role, + in which case no secure schema usage pattern exists. + trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". --> In PostgreSQL 15 and later, the default @@ -3250,7 +3248,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; unreliable. If you create functions or extensions in the public schema, use the first pattern instead. Otherwise, like the first pattern, this is secure unless an untrusted user is the database owner - or holds the CREATEROLE privilege. + or has been granted ADMIN OPTION on a relevant role. diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml index f5c1264942f..43067d3feca 100644 --- a/doc/src/sgml/ref/alter_role.sgml +++ b/doc/src/sgml/ref/alter_role.sgml @@ -73,7 +73,8 @@ ALTER ROLE { role_specification | A Roles having CREATEROLE privilege can change any of these settings except SUPERUSER, REPLICATION, and BYPASSRLS; but only for non-superuser and - non-replication roles. + non-replication roles for which they have been + granted ADMIN OPTION. Ordinary roles can only change their own password. @@ -81,7 +82,7 @@ ALTER ROLE { role_specification | A The second variant changes the name of the role. Database superusers can rename any role. Roles having CREATEROLE privilege can rename non-superuser - roles. + roles for which they have been granted ADMIN OPTION. The current session user cannot be renamed. (Connect as a different user if you need to do that.) Because MD5-encrypted passwords use the role name as @@ -116,7 +117,8 @@ ALTER ROLE { role_specification | A Superusers can change anyone's session defaults. Roles having CREATEROLE privilege can change defaults for non-superuser - roles. Ordinary roles can only set defaults for themselves. + roles for which they have been granted ADMIN OPTION. + Ordinary roles can only set defaults for themselves. Certain configuration variables cannot be set this way, or can only be set if a superuser issues the command. Only superusers can change a setting for all roles in all databases. diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index 23d9029af9c..7499da1d62a 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -99,7 +99,8 @@ COMMENT ON For most kinds of object, only the object's owner can set the comment. Roles don't have owners, so the rule for COMMENT ON ROLE is that you must be superuser to comment on a superuser role, or have the - CREATEROLE privilege to comment on non-superuser roles. + CREATEROLE privilege and have been granted + ADMIN OPTION on the target role. Likewise, access methods don't have owners either; you must be superuser to comment on an access method. Of course, a superuser can comment on anything. diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 1ccc8325588..0863acbcac4 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -119,8 +119,8 @@ in sync when changing the above synopsis! These clauses determine whether a role will be permitted to - create, alter, drop, comment on, change the security label for, - and grant or revoke membership in other roles. + create, alter, drop, comment on, and change the security label for + other roles. See for more details about what capabilities are conferred by this privilege. If not specified, NOCREATEROLE is the default. diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml index a41a2b24e6c..f91dc500a40 100644 --- a/doc/src/sgml/ref/createuser.sgml +++ b/doc/src/sgml/ref/createuser.sgml @@ -252,8 +252,7 @@ PostgreSQL documentation The new user will be allowed to create, alter, drop, comment on, - change the security label for, and grant or revoke membership in - other roles; that is, + change the security label for other roles; that is, this user will have CREATEROLE privilege. See for more details about what capabilities are conferred by this privilege. diff --git a/doc/src/sgml/ref/drop_role.sgml b/doc/src/sgml/ref/drop_role.sgml index 13dc1cc6499..cbcb3cd3d3e 100644 --- a/doc/src/sgml/ref/drop_role.sgml +++ b/doc/src/sgml/ref/drop_role.sgml @@ -32,7 +32,7 @@ DROP ROLE [ IF EXISTS ] name [, ... DROP ROLE removes the specified role(s). To drop a superuser role, you must be a superuser yourself; to drop non-superuser roles, you must have CREATEROLE - privilege. + privilege and have been granted ADMIN OPTION on the role. diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml index 81580507e82..b6be26d5b0a 100644 --- a/doc/src/sgml/ref/dropuser.sgml +++ b/doc/src/sgml/ref/dropuser.sgml @@ -35,9 +35,10 @@ PostgreSQL documentation dropuser removes an existing PostgreSQL user. - Only superusers and users with the CREATEROLE privilege can - remove PostgreSQL users. (To remove a - superuser, you must yourself be a superuser.) + Superusers can use this command to remove any role; otherwise, only + non-superuser roles can be removed, and only by a user who possesses + the CREATEROLE privilege and has been granted + ADMIN OPTION on the target role. diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 518bdb32d82..85f5f42ea6e 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -271,9 +271,7 @@ GRANT role_name [, ...] TO WITH ADMIN OPTION on itself. Database superusers can grant or revoke - membership in any role to anyone. Roles having - CREATEROLE privilege can grant or revoke membership - in any role that is not a superuser. This option defaults to + membership in any role to anyone. This option defaults to FALSE. diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 7aa6bdac163..71a2d8f2985 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -199,7 +199,12 @@ CREATE USER name; checks). To create such a role, use CREATE ROLE name CREATEROLE. A role with CREATEROLE privilege can alter and drop - other roles, too, as well as grant or revoke membership in them. + roles which have been granted to the CREATEROLE + user with the ADMIN option. Such a grant occurs + automatically when a CREATEROLE user that is not + a superuser creates a new role, so that by default, a + CREATEROLE user can alter and drop the roles + which they have created. Altering a role includes most changes that can be made using ALTER ROLE, including, for example, changing passwords. It also includes modifications to a role that can @@ -224,15 +229,6 @@ CREATE USER name; confer the ability to grant or revoke the BYPASSRLS privilege. - - Because the CREATEROLE privilege allows a user - to grant or revoke membership even in roles to which it does not (yet) - have any access, a CREATEROLE user can obtain access - to the capabilities of every predefined role in the system, including - highly privileged roles such as - pg_execute_server_program and - pg_write_server_files. - @@ -329,6 +325,34 @@ ALTER ROLE myname SET enable_indexscan TO off; LOGIN privilege are fairly useless, since they will never be invoked. + + + When a non-superuser creates a role using the CREATEROLE + privilege, the created role is automatically granted back to the creating + user, just as if the bootstrap superuser had executed the command + GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, + INHERIT FALSE. Since a CREATEROLE user can + only exercise special privileges with regard to an existing role if they + have ADMIN OPTION on it, this grant is just sufficient + to allow a CREATEROLE user to administer the roles they + created. However, because it is created with INHERIT FALSE, SET + FALSE, the CREATEROLE user doesn't inherit the + privileges of the created role, nor can it access the privileges of that + role using SET ROLE. However, since any user who has + ADMIN OPTION on a role can grant membership in that + role to any other user, the CREATEROLE user can gain + access to the created role by simplying granting that role back to + themselves with the INHERIT and/or SET + options. Thus, the fact that privileges are not inherited by default nor + is SET ROLE granted by default is a safeguard against + accidents, not a security feature. Also note that, because this automatic + grant is granted by the bootstrap user, it cannot be removed or changed by + the CREATEROLE user; however, any superuser could + revoke it, modify it, and/or issue additional such grants to other + CREATEROLE users. Whichever CREATEROLE + users have ADMIN OPTION on a role at any given time + can administer it. + -- cgit v1.2.3