diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 64d9030652..bfdd6403cd 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3132,9 +3132,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; doesn't preserve that DROP. A database owner can attack the database's users via "CREATE SCHEMA - trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". A - CREATEROLE user can issue "GRANT $dbowner TO $me" and then use the - database owner attack. --> + trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". --> Constrain ordinary users to user-private schemas. To implement this, first issue REVOKE CREATE ON SCHEMA public FROM @@ -3146,9 +3144,8 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; pattern in a database where untrusted users had already logged in, consider auditing the public schema for objects named like objects in schema pg_catalog. 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. + usage pattern unless an untrusted user is the database owner, in which + case no secure schema usage pattern exists. If the database originated in an upgrade @@ -3170,8 +3167,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC; schema will be unsafe or 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. + pattern, this is secure unless an untrusted user is the database owner. diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml index 5aa5648ae7..96e60d5a09 100644 --- a/doc/src/sgml/ref/alter_role.sgml +++ b/doc/src/sgml/ref/alter_role.sgml @@ -70,18 +70,18 @@ ALTER ROLE { role_specification | A REVOKE for that.) Attributes not mentioned in the command retain their previous settings. Database superusers can change any of these settings for any role. - Roles having CREATEROLE privilege can change any of these - settings except SUPERUSER, REPLICATION, - and BYPASSRLS; but only for non-superuser and - non-replication roles. - Ordinary roles can only change their own password. + Role owners can change any of these settings on roles they own except + SUPERUSER, REPLICATION, and + BYPASSRLS; but only for non-superuser and non-replication + roles, and only if the role owner does not alter the target role to have a + privilege which the role owner itself lacks. Ordinary roles can only change + their own password. The second variant changes the name of the role. Database superusers can rename any role. - Roles having CREATEROLE privilege can rename non-superuser - roles. + Role owners can rename non-superuser roles they own. 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 @@ -114,9 +114,9 @@ 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. + Superusers can change anyone's session defaults. Owning roles may change + privilege for non-superuser roles they own. 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 e07fc47fd3..1ba374f3a1 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -92,12 +92,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. - 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. + Access methods don't have owners; 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 b6a4ea1f72..2e73102562 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -107,8 +107,10 @@ in sync when changing the above synopsis! CREATEDB is specified, the role being defined will be allowed to create new databases. Specifying NOCREATEDB will deny a role the ability to - create databases. If not specified, - NOCREATEDB is the default. + create databases. Only roles with the CREATEDB + attribute may create roles with the CREATEDB + attribute. If not specified, NOCREATEDB is the + default. @@ -120,8 +122,6 @@ in sync when changing the above synopsis! These clauses determine whether a role will be permitted to create new roles (that is, execute CREATE ROLE). - A role with CREATEROLE privilege can also alter - and drop other roles. If not specified, NOCREATEROLE is the default. @@ -163,6 +163,8 @@ in sync when changing the above synopsis! NOLOGIN is the default, except when CREATE ROLE is invoked through its alternative spelling CREATE USER. + You must have the LOGIN attribute to create a new role + with the LOGIN attribute. @@ -194,8 +196,8 @@ in sync when changing the above synopsis! These clauses determine whether a role bypasses every row-level security (RLS) policy. NOBYPASSRLS is the default. - You must be a superuser to create a new role having - the BYPASSRLS attribute. + You must have the BYPASSRLS attribute to create a + new role having the BYPASSRLS attribute. @@ -281,6 +283,10 @@ in sync when changing the above synopsis! member. (Note that there is no option to add the new role as an administrator; use a separate GRANT command to do that.) + + If not a superuser, the creating role must either own or have admin + privilege on each listed role. + @@ -301,6 +307,10 @@ in sync when changing the above synopsis! roles which are automatically added as members of the new role. (This in effect makes the new role a group.) + + If not a superuser, the creating role must either own or have admin + privilege on each listed role. + @@ -313,6 +323,10 @@ in sync when changing the above synopsis! OPTION, giving them the right to grant membership in this role to others. + + If not a superuser, the creating role must either own or have admin + privilege on each listed role. + diff --git a/doc/src/sgml/ref/drop_role.sgml b/doc/src/sgml/ref/drop_role.sgml index 13dc1cc649..c3d57ee8db 100644 --- a/doc/src/sgml/ref/drop_role.sgml +++ b/doc/src/sgml/ref/drop_role.sgml @@ -31,8 +31,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. + to drop non-superuser roles, you must own the target role. diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml index 81580507e8..30a99eaf68 100644 --- a/doc/src/sgml/ref/dropuser.sgml +++ b/doc/src/sgml/ref/dropuser.sgml @@ -35,9 +35,9 @@ 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.) + A PostgreSQL user may only be removed by its + owner or by a superuser. (To remove a superuser, you must yourself be a + superuser.) diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index a897712de2..86fc387af2 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -254,8 +254,8 @@ GRANT role_name [, ...] TO on itself, but it may grant or revoke membership in itself from a database session where the session user matches the role. 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. + to anyone. Roles can revoke membership in any role they own, and + may grant membership in any role they own to any role they own. diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 9067be1d9c..e65b55a004 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -198,9 +198,10 @@ CREATE USER name; (except for superusers, since those bypass all permission 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. - However, to create, alter, drop, or change membership of a + A role which creates a new role becomes the new role's owner, able to + alter or drop that new role, and sharing ownership of any additional + objects (including additional roles) that new role creates. + To create, alter, drop, or change membership of a superuser role, superuser status is required; CREATEROLE is insufficient for that. @@ -246,11 +247,14 @@ CREATE USER name; - It is good practice to create a role that has the CREATEDB - and CREATEROLE privileges, but is not a superuser, and then + It is good practice to create a role that has the + CREATEDB, LOGIN and + CREATEROLE privileges, but is not a superuser, and then use this role for all routine management of databases and roles. This - approach avoids the dangers of operating as a superuser for tasks that - do not really require it. + approach avoids the dangers of operating as a superuser for tasks that do + not really require it. This role must also have + REPLICATION if it will create replication users, and + must have BYPASSRLS if it will create bypassrls users. @@ -387,15 +391,22 @@ RESET ROLE; The role attributes LOGIN, SUPERUSER, - CREATEDB, and CREATEROLE can be thought of as - special privileges, but they are never inherited as ordinary privileges - on database objects are. You must actually SET ROLE to a - specific role having one of these attributes in order to make use of - the attribute. Continuing the above example, we might choose to + CREATEDB, REPLICATION, + BYPASSRLS, and CREATEROLE can be + thought of as special privileges, but they are never inherited as ordinary + privileges on database objects are. You must actually SET + ROLE to a specific role having one of these attributes in order to + make use of the attribute. Continuing the above example, we might choose to grant CREATEDB and CREATEROLE to the - admin role. Then a session connecting as role joe - would not have these privileges immediately, only after doing - SET ROLE admin. + admin role. Then a session connecting as role + joe would not have these privileges immediately, only + after doing SET ROLE admin. Roles with these attributes + may only be created by roles which themselves have these attributes. + Superusers may always do so, but non-superuser roles with + CREATEROLE may only create new roles with + LOGIN, CREATEDB, + REPLICATION, or BYPASSRLS if they + themselves have the same attribute. @@ -493,8 +504,7 @@ DROP ROLE doomed_role; PostgreSQL provides a set of predefined roles that provide access to certain, commonly needed, privileged capabilities - and information. Administrators (including roles that have the - CREATEROLE privilege) can GRANT these + and information. Administrators can GRANT these roles to users and/or other roles in their environment, providing those users with access to the specified capabilities and information. diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 4a11a0f124..a72f412e90 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -5552,6 +5552,97 @@ has_bypassrls_privilege(Oid roleid) return result; } +bool +has_rolinherit_privilege(Oid roleid) +{ + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolinherit; + ReleaseSysCache(utup); + } + return result; +} + +bool +has_createdb_privilege(Oid roleid) +{ + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolcreatedb; + ReleaseSysCache(utup); + } + return result; +} + +bool +has_login_privilege(Oid roleid) +{ + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolcanlogin; + ReleaseSysCache(utup); + } + return result; +} + +bool +has_replication_privilege(Oid roleid) +{ + bool result = false; + HeapTuple utup; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolreplication; + ReleaseSysCache(utup); + } + return result; +} + +int32 +role_connection_limit(Oid roleid) +{ + int32 result = -1; + HeapTuple utup; + + utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); + if (HeapTupleIsValid(utup)) + { + result = ((Form_pg_authid) GETSTRUCT(utup))->rolconnlimit; + ReleaseSysCache(utup); + } + return result; +} + /* * Fetch pg_default_acl entry for given role, namespace and object type * (object type must be given in pg_default_acl's encoding). diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index 9c40766d83..274047c232 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -58,15 +58,6 @@ static void DelRoleMems(const char *rolename, Oid roleid, static void AlterRoleOwner_internal(HeapTuple tup, Relation rel, Oid newOwnerId); - -/* Check if current user has createrole privileges */ -static bool -have_createrole_privilege(void) -{ - return has_createrole_privilege(GetUserId()); -} - - /* * CREATE ROLE */ @@ -276,24 +267,32 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) } else if (isreplication) { - if (!superuser()) + if (!has_replication_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to create replication users"))); + errmsg("must have replication privilege to create replication users"))); } else if (bypassrls) { - if (!superuser()) + if (!has_bypassrls_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser to create bypassrls users"))); + errmsg("must have bypassrls privilege to create bypassrls users"))); } - else + else if (!superuser()) { - if (!have_createrole_privilege()) + if (!has_createrole_privilege(GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to create role"))); + if (createdb && !has_createdb_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have createdb privilege to create createdb users"))); + if (canlogin && !has_login_privilege(GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have login privilege to create login users"))); } /* @@ -713,7 +712,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to change bypassrls attribute"))); } - else if (!have_createrole_privilege()) + else if (!superuser()) { /* We already checked issuper, isreplication, and bypassrls */ if (!(inherit < 0 && @@ -914,7 +913,7 @@ AlterRoleSet(AlterRoleSetStmt *stmt) /* * To mess with a superuser you gotta be superuser; else you need - * createrole, or just want to change your own settings + * to own the role, or just want to change your own settings */ if (roleform->rolsuper) { @@ -925,8 +924,7 @@ AlterRoleSet(AlterRoleSetStmt *stmt) } else { - if (!have_createrole_privilege() && - !pg_role_ownercheck(roleid, GetUserId())) + if (!pg_role_ownercheck(roleid, GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied"))); @@ -1039,18 +1037,12 @@ DropRole(DropRoleStmt *stmt) (errcode(ERRCODE_OBJECT_IN_USE), errmsg("session user cannot be dropped"))); - /* - * For safety's sake, we allow createrole holders to drop ordinary - * roles but not superuser roles. This is mainly to avoid the - * scenario where you accidentally drop the last superuser. - */ if (roleform->rolsuper && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to drop superusers"))); - if (!have_createrole_privilege() && - !pg_role_ownercheck(roleid, GetUserId())) + if (!pg_role_ownercheck(roleid, GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to drop role"))); @@ -1231,7 +1223,7 @@ RenameRole(const char *oldname, const char *newname) errmsg("role \"%s\" already exists", newname))); /* - * createrole is enough privilege unless you want to mess with a superuser + * role ownership is enough privilege unless you want to mess with a superuser */ if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper) { @@ -1242,7 +1234,7 @@ RenameRole(const char *oldname, const char *newname) } else { - if (!have_createrole_privilege()) + if (!pg_role_ownercheck(roleid, GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to rename role"))); @@ -1468,7 +1460,7 @@ AddRoleMems(const char *rolename, Oid roleid, return; /* - * Check permissions: must have createrole or admin option on the role to + * Check permissions: must be owner or have admin option on the role to * be changed. To mess with a superuser role, you gotta be superuser. */ if (superuser_arg(roleid)) @@ -1478,9 +1470,9 @@ AddRoleMems(const char *rolename, Oid roleid, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to alter superusers"))); } - else + else if (!superuser()) { - if (!have_createrole_privilege() && + if (!pg_role_ownercheck(roleid, grantorId) && !is_admin_of_role(grantorId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), @@ -1656,9 +1648,9 @@ DelRoleMems(const char *rolename, Oid roleid, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to alter superusers"))); } - else + else if (!superuser()) { - if (!have_createrole_privilege() && + if (!pg_role_ownercheck(roleid, GetUserId()) && !is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), @@ -1814,7 +1806,7 @@ AlterRoleOwner_internal(HeapTuple tup, Relation rel, Oid newOwnerId) * roles. Because superusers will always have this right, we need no * special case for them. */ - if (!have_createrole_privilege()) + if (!has_createrole_privilege(GetUserId())) aclcheck_error(ACLCHECK_NO_PRIV, OBJECT_ROLE, NameStr(authForm->rolname)); diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index 04eae9d4e5..3438abbcee 100644 --- a/src/backend/utils/adt/acl.c +++ b/src/backend/utils/adt/acl.c @@ -4656,7 +4656,7 @@ initialize_acl(void) /* * In normal mode, set a callback on any syscache invalidation of rows * of pg_auth_members (for roles_is_member_of()), pg_authid (for - * has_rolinherit()), or pg_database (for roles_is_member_of()) + * has_rolinherit_privilege()), or pg_database (for roles_is_member_of()) */ CacheRegisterSyscacheCallback(AUTHMEMROLEMEM, RoleMembershipCacheCallback, @@ -4690,23 +4690,6 @@ RoleMembershipCacheCallback(Datum arg, int cacheid, uint32 hashvalue) } -/* Check if specified role has rolinherit set */ -static bool -has_rolinherit(Oid roleid) -{ - bool result = false; - HeapTuple utup; - - utup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid)); - if (HeapTupleIsValid(utup)) - { - result = ((Form_pg_authid) GETSTRUCT(utup))->rolinherit; - ReleaseSysCache(utup); - } - return result; -} - - /* * Get a list of roles that the specified roleid is a member of * @@ -4776,7 +4759,7 @@ roles_is_member_of(Oid roleid, enum RoleRecurseType type, CatCList *memlist; int i; - if (type == ROLERECURSE_PRIVS && !has_rolinherit(memberid)) + if (type == ROLERECURSE_PRIVS && !has_rolinherit_privilege(memberid)) continue; /* ignore non-inheriting roles */ /* Find roles that memberid is directly a member of */ diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h index ec9d480d67..63cde442a8 100644 --- a/src/include/utils/acl.h +++ b/src/include/utils/acl.h @@ -319,5 +319,10 @@ extern bool pg_statistics_object_ownercheck(Oid stat_oid, Oid roleid); extern bool pg_role_ownercheck(Oid role_oid, Oid roleid); extern bool has_createrole_privilege(Oid roleid); extern bool has_bypassrls_privilege(Oid roleid); +extern bool has_rolinherit_privilege(Oid roleid); +extern bool has_createdb_privilege(Oid roleid); +extern bool has_login_privilege(Oid roleid); +extern bool has_replication_privilege(Oid roleid); +extern int32 role_connection_limit(Oid roleid); #endif /* ACL_H */ diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out index 492162e5a1..7df3683f2b 100644 --- a/src/test/regress/expected/create_role.out +++ b/src/test/regress/expected/create_role.out @@ -6,22 +6,16 @@ GRANT CREATE ON DATABASE regression TO regress_role_1; SET SESSION AUTHORIZATION regress_role_1; CREATE ROLE regress_role_2 SUPERUSER; ERROR: must be superuser to create superusers +-- ok, can assign privileges the creator has CREATE ROLE regress_role_3 REPLICATION BYPASSRLS; -ERROR: must be superuser to create replication users CREATE ROLE regress_role_4 REPLICATION; -ERROR: must be superuser to create replication users CREATE ROLE regress_role_5 BYPASSRLS; -ERROR: must be superuser to create bypassrls users -- fail, only superusers can own superusers RESET SESSION AUTHORIZATION; CREATE ROLE regress_role_2 AUTHORIZATION regress_role_1 SUPERUSER; ERROR: must be superuser to own superusers -- ok, superuser can create superusers belonging to other superusers CREATE ROLE regress_role_2 AUTHORIZATION regress_role_super SUPERUSER; --- ok, superuser can create users with these privileges for normal role -CREATE ROLE regress_role_3 AUTHORIZATION regress_role_1 REPLICATION BYPASSRLS; -CREATE ROLE regress_role_4 AUTHORIZATION regress_role_1 REPLICATION; -CREATE ROLE regress_role_5 AUTHORIZATION regress_role_1 BYPASSRLS; \du+ regress_role_2 List of roles Role name | Owner | Attributes | Member of | Description @@ -50,7 +44,10 @@ CREATE ROLE regress_role_5 AUTHORIZATION regress_role_1 BYPASSRLS; SET SESSION AUTHORIZATION regress_role_1; CREATE ROLE regress_role_6 CREATEDB; CREATE ROLE regress_role_7 CREATEROLE; +-- fail, cannot assign LOGIN privilege that creator lacks CREATE ROLE regress_role_8 LOGIN; +ERROR: must have login privilege to create login users +-- ok, having CREATEROLE is enough for these CREATE ROLE regress_role_9 INHERIT; CREATE ROLE regress_role_10 CONNECTION LIMIT 5; CREATE ROLE regress_role_11 PASSWORD NULL; @@ -70,12 +67,6 @@ COMMENT ON ROLE regress_role_12 IS 'no login test role'; ----------------+----------------+---------------------------+-----------+------------- regress_role_7 | regress_role_1 | Create role, Cannot login | {} | -\du+ regress_role_8 - List of roles - Role name | Owner | Attributes | Member of | Description -----------------+----------------+------------+-----------+------------- - regress_role_8 | regress_role_1 | | {} | - \du+ regress_role_9 List of roles Role name | Owner | Attributes | Member of | Description @@ -108,19 +99,19 @@ NOTICE: SYSID can no longer be specified -- fail, cannot grant membership in superuser role CREATE ROLE regress_role_14 IN ROLE regress_role_super; ERROR: must be superuser to alter superusers --- fail, database owner cannot have members +-- fail, do not have ADMIN privilege on database owner CREATE ROLE regress_role_15 IN ROLE pg_database_owner; -ERROR: role "pg_database_owner" cannot have explicit members +ERROR: must have admin option on role "pg_database_owner" -- ok, can grant other users into a role CREATE ROLE regress_role_16 ROLE - regress_role_super, regress_role_6, regress_role_7, regress_role_8, + regress_role_super, regress_role_6, regress_role_7, regress_role_9, regress_role_10, regress_role_11, regress_role_12; -- fail, cannot grant a role into itself CREATE ROLE regress_role_17 ROLE regress_role_17; ERROR: role "regress_role_17" is a member of role "regress_role_17" -- ok, can grant other users into a role with admin option CREATE ROLE regress_role_18 ADMIN - regress_role_super, regress_role_6, regress_role_7, regress_role_8, + regress_role_super, regress_role_6, regress_role_7, regress_role_9, regress_role_10, regress_role_11, regress_role_12; -- fail, cannot grant a role into itself with admin option CREATE ROLE regress_role_19 ADMIN regress_role_19; @@ -133,8 +124,11 @@ ERROR: permission denied to create database CREATE ROLE regress_role_20; -- ok, roles with CREATEROLE can create new roles with it CREATE ROLE regress_role_21 CREATEROLE; --- ok, roles with CREATEROLE can create new roles with privilege they lack +-- fail, roles with CREATEROLE cannot create new roles with privilege they lack CREATE ROLE regress_role_22 CREATEDB CREATEROLE LOGIN INHERIT CONNECTION LIMIT 5; +ERROR: must have createdb privilege to create createdb users +-- ok, roles with CREATEROLE can create new roles with privilege they have +CREATE ROLE regress_role_22 CREATEROLE INHERIT CONNECTION LIMIT 5; -- ok, regress_role_22 can create objects within the database SET SESSION AUTHORIZATION regress_role_22; CREATE TABLE regress_tbl_22 (i integer); @@ -153,17 +147,27 @@ ERROR: must be member of role "regress_role_1" DROP VIEW regress_view_22; -- ok, can take ownership objects from owned roles REASSIGN OWNED BY regress_role_22 TO regress_role_7; --- ok, having CREATEROLE is enough to create roles in privileged roles +-- fail, having CREATEROLE is not enough to create roles in privileged roles CREATE ROLE regress_role_23 IN ROLE pg_read_all_data; +ERROR: must have admin option on role "pg_read_all_data" CREATE ROLE regress_role_24 IN ROLE pg_write_all_data; +ERROR: must have admin option on role "pg_write_all_data" CREATE ROLE regress_role_25 IN ROLE pg_monitor; +ERROR: must have admin option on role "pg_monitor" CREATE ROLE regress_role_26 IN ROLE pg_read_all_settings; +ERROR: must have admin option on role "pg_read_all_settings" CREATE ROLE regress_role_27 IN ROLE pg_read_all_stats; +ERROR: must have admin option on role "pg_read_all_stats" CREATE ROLE regress_role_28 IN ROLE pg_stat_scan_tables; +ERROR: must have admin option on role "pg_stat_scan_tables" CREATE ROLE regress_role_29 IN ROLE pg_read_server_files; +ERROR: must have admin option on role "pg_read_server_files" CREATE ROLE regress_role_30 IN ROLE pg_write_server_files; +ERROR: must have admin option on role "pg_write_server_files" CREATE ROLE regress_role_31 IN ROLE pg_execute_server_program; +ERROR: must have admin option on role "pg_execute_server_program" CREATE ROLE regress_role_32 IN ROLE pg_signal_backend; +ERROR: must have admin option on role "pg_signal_backend" -- ok, can take ownership from owned roles SET SESSION AUTHORIZATION regress_role_1; ALTER ROLE regress_role_20 OWNER TO regress_role_1; @@ -182,19 +186,8 @@ DROP ROLE regress_role_7; ERROR: role "regress_role_7" cannot be dropped because some objects depend on it DETAIL: owner of role regress_role_21 owner of role regress_role_22 -owner of role regress_role_23 -owner of role regress_role_24 -owner of role regress_role_25 -owner of role regress_role_26 -owner of role regress_role_27 -owner of role regress_role_28 -owner of role regress_role_29 -owner of role regress_role_30 -owner of role regress_role_31 -owner of role regress_role_32 -- ok, should be able to drop these non-superuser roles DROP ROLE regress_role_6; -DROP ROLE regress_role_8; DROP ROLE regress_role_9; DROP ROLE regress_role_10; DROP ROLE regress_role_11; @@ -204,16 +197,6 @@ DROP ROLE regress_role_16; DROP ROLE regress_role_18; DROP ROLE regress_role_21; DROP ROLE regress_role_22; -DROP ROLE regress_role_23; -DROP ROLE regress_role_24; -DROP ROLE regress_role_25; -DROP ROLE regress_role_26; -DROP ROLE regress_role_27; -DROP ROLE regress_role_28; -DROP ROLE regress_role_29; -DROP ROLE regress_role_30; -DROP ROLE regress_role_31; -DROP ROLE regress_role_32; -- fail, cannot drop ourself nor superusers DROP ROLE regress_role_super; ERROR: must be superuser to drop superusers diff --git a/src/test/regress/sql/create_role.sql b/src/test/regress/sql/create_role.sql index 678f728f52..b1c764cb82 100644 --- a/src/test/regress/sql/create_role.sql +++ b/src/test/regress/sql/create_role.sql @@ -6,6 +6,8 @@ GRANT CREATE ON DATABASE regression TO regress_role_1; -- fail, only superusers can create users with these privileges SET SESSION AUTHORIZATION regress_role_1; CREATE ROLE regress_role_2 SUPERUSER; + +-- ok, can assign privileges the creator has CREATE ROLE regress_role_3 REPLICATION BYPASSRLS; CREATE ROLE regress_role_4 REPLICATION; CREATE ROLE regress_role_5 BYPASSRLS; @@ -17,11 +19,6 @@ CREATE ROLE regress_role_2 AUTHORIZATION regress_role_1 SUPERUSER; -- ok, superuser can create superusers belonging to other superusers CREATE ROLE regress_role_2 AUTHORIZATION regress_role_super SUPERUSER; --- ok, superuser can create users with these privileges for normal role -CREATE ROLE regress_role_3 AUTHORIZATION regress_role_1 REPLICATION BYPASSRLS; -CREATE ROLE regress_role_4 AUTHORIZATION regress_role_1 REPLICATION; -CREATE ROLE regress_role_5 AUTHORIZATION regress_role_1 BYPASSRLS; - \du+ regress_role_2 \du+ regress_role_3 \du+ regress_role_4 @@ -31,7 +28,11 @@ CREATE ROLE regress_role_5 AUTHORIZATION regress_role_1 BYPASSRLS; SET SESSION AUTHORIZATION regress_role_1; CREATE ROLE regress_role_6 CREATEDB; CREATE ROLE regress_role_7 CREATEROLE; + +-- fail, cannot assign LOGIN privilege that creator lacks CREATE ROLE regress_role_8 LOGIN; + +-- ok, having CREATEROLE is enough for these CREATE ROLE regress_role_9 INHERIT; CREATE ROLE regress_role_10 CONNECTION LIMIT 5; CREATE ROLE regress_role_11 PASSWORD NULL; @@ -42,7 +43,6 @@ COMMENT ON ROLE regress_role_12 IS 'no login test role'; \du+ regress_role_6 \du+ regress_role_7 -\du+ regress_role_8 \du+ regress_role_9 \du+ regress_role_10 \du+ regress_role_11 @@ -54,12 +54,12 @@ CREATE ROLE regress_role_13 SYSID 12345; -- fail, cannot grant membership in superuser role CREATE ROLE regress_role_14 IN ROLE regress_role_super; --- fail, database owner cannot have members +-- fail, do not have ADMIN privilege on database owner CREATE ROLE regress_role_15 IN ROLE pg_database_owner; -- ok, can grant other users into a role CREATE ROLE regress_role_16 ROLE - regress_role_super, regress_role_6, regress_role_7, regress_role_8, + regress_role_super, regress_role_6, regress_role_7, regress_role_9, regress_role_10, regress_role_11, regress_role_12; -- fail, cannot grant a role into itself @@ -67,7 +67,7 @@ CREATE ROLE regress_role_17 ROLE regress_role_17; -- ok, can grant other users into a role with admin option CREATE ROLE regress_role_18 ADMIN - regress_role_super, regress_role_6, regress_role_7, regress_role_8, + regress_role_super, regress_role_6, regress_role_7, regress_role_9, regress_role_10, regress_role_11, regress_role_12; -- fail, cannot grant a role into itself with admin option @@ -83,9 +83,12 @@ CREATE ROLE regress_role_20; -- ok, roles with CREATEROLE can create new roles with it CREATE ROLE regress_role_21 CREATEROLE; --- ok, roles with CREATEROLE can create new roles with privilege they lack +-- fail, roles with CREATEROLE cannot create new roles with privilege they lack CREATE ROLE regress_role_22 CREATEDB CREATEROLE LOGIN INHERIT CONNECTION LIMIT 5; +-- ok, roles with CREATEROLE can create new roles with privilege they have +CREATE ROLE regress_role_22 CREATEROLE INHERIT CONNECTION LIMIT 5; + -- ok, regress_role_22 can create objects within the database SET SESSION AUTHORIZATION regress_role_22; CREATE TABLE regress_tbl_22 (i integer); @@ -108,7 +111,7 @@ DROP VIEW regress_view_22; -- ok, can take ownership objects from owned roles REASSIGN OWNED BY regress_role_22 TO regress_role_7; --- ok, having CREATEROLE is enough to create roles in privileged roles +-- fail, having CREATEROLE is not enough to create roles in privileged roles CREATE ROLE regress_role_23 IN ROLE pg_read_all_data; CREATE ROLE regress_role_24 IN ROLE pg_write_all_data; CREATE ROLE regress_role_25 IN ROLE pg_monitor; @@ -141,7 +144,6 @@ DROP ROLE regress_role_7; -- ok, should be able to drop these non-superuser roles DROP ROLE regress_role_6; -DROP ROLE regress_role_8; DROP ROLE regress_role_9; DROP ROLE regress_role_10; DROP ROLE regress_role_11; @@ -151,16 +153,6 @@ DROP ROLE regress_role_16; DROP ROLE regress_role_18; DROP ROLE regress_role_21; DROP ROLE regress_role_22; -DROP ROLE regress_role_23; -DROP ROLE regress_role_24; -DROP ROLE regress_role_25; -DROP ROLE regress_role_26; -DROP ROLE regress_role_27; -DROP ROLE regress_role_28; -DROP ROLE regress_role_29; -DROP ROLE regress_role_30; -DROP ROLE regress_role_31; -DROP ROLE regress_role_32; -- fail, cannot drop ourself nor superusers DROP ROLE regress_role_super;