diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index ddd205d656..4a11a0f124 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -5440,61 +5440,79 @@ pg_statistics_object_ownercheck(Oid stat_oid, Oid roleid) bool pg_role_ownercheck(Oid role_oid, Oid roleid) { - HeapTuple tuple; - Form_pg_authid authform; - Oid owner_oid; - /* Superusers bypass all permission checking. */ if (superuser_arg(roleid)) return true; - /* Otherwise, look up the owner of the role */ - tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(role_oid)); - if (!HeapTupleIsValid(tuple)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("role with OID %u does not exist", - role_oid))); - authform = (Form_pg_authid) GETSTRUCT(tuple); - owner_oid = authform->rolowner; - /* - * Roles must necessarily have owners. Even the bootstrap user has an - * owner. (It owns itself). Other roles must form a proper tree. + * Start with the owned role and traverse the ownership hierarchy upward. + * We stop when we find the owner we are looking for or when we reach the + * top. */ - if (!OidIsValid(owner_oid)) - ereport(ERROR, - (errcode(ERRCODE_DATA_CORRUPTED), - errmsg("role \"%s\" with OID %u has invalid owner", - authform->rolname.data, authform->oid))); - if (authform->oid != BOOTSTRAP_SUPERUSERID && - authform->rolowner == authform->oid) - ereport(ERROR, - (errcode(ERRCODE_DATA_CORRUPTED), - errmsg("role \"%s\" with OID %u owns itself", - authform->rolname.data, authform->oid))); - if (authform->oid == BOOTSTRAP_SUPERUSERID && - authform->rolowner != BOOTSTRAP_SUPERUSERID) - ereport(ERROR, - (errcode(ERRCODE_DATA_CORRUPTED), - errmsg("role \"%s\" with OID %u owned by role with OID %u", - authform->rolname.data, authform->oid, - authform->rolowner))); - ReleaseSysCache(tuple); + while (OidIsValid(role_oid)) + { + HeapTuple tuple; + Form_pg_authid authform; + Oid owner_oid; + + /* Find the owner of the current iteration's role */ + tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(role_oid)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("role with OID %u does not exist", + role_oid))); + authform = (Form_pg_authid) GETSTRUCT(tuple); + owner_oid = authform->rolowner; + + /* + * Roles must necessarily have owners. Even the bootstrap user has an + * owner. (It owns itself). Other roles must form a proper tree. + */ + if (!OidIsValid(owner_oid)) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg("role \"%s\" with OID %u has invalid owner", + authform->rolname.data, authform->oid))); + if (authform->oid != BOOTSTRAP_SUPERUSERID && + authform->rolowner == authform->oid) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg("role \"%s\" with OID %u owns itself", + authform->rolname.data, authform->oid))); + if (authform->oid == BOOTSTRAP_SUPERUSERID && + authform->rolowner != BOOTSTRAP_SUPERUSERID) + ereport(ERROR, + (errcode(ERRCODE_DATA_CORRUPTED), + errmsg("role \"%s\" with OID %u owned by role with OID %u", + authform->rolname.data, authform->oid, + authform->rolowner))); + ReleaseSysCache(tuple); + + /* Have we found the target role? */ + if (roleid == owner_oid) + return true; + + /* + * If we have reached a role which owns itself, we must iterate no + * further, else we fall into an infinite loop. + */ + if (role_oid == owner_oid) + return false; + + /* Set up for the next iteration. */ + role_oid = owner_oid; + } - return (owner_oid == roleid); + return false; } /* * Check whether specified role has CREATEROLE privilege (or is a superuser) * - * Note: roles do not have owners per se; instead we use this test in - * places where an ownership-like permissions test is needed for a role. - * Be sure to apply it to the role trying to do the operation, not the - * role being operated on! Also note that this generally should not be - * considered enough privilege if the target role is a superuser. - * (We don't handle that consideration here because we want to give a - * separate error message for such cases, so the caller has to deal with it.) + * Note: In versions prior to PostgreSQL version 15, roles did not have owners + * per se; instead we used this test in places where an ownership-like + * permissions test was needed for a role. */ bool has_createrole_privilege(Oid roleid) diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index 2bae3fbb17..cc19409ca3 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -2596,25 +2596,9 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, NameListToString(castNode(List, object))); break; case OBJECT_ROLE: - - /* - * We treat roles as being "owned" by those with CREATEROLE priv, - * except that superusers are only owned by superusers. - */ - if (superuser_arg(address.objectId)) - { - if (!superuser_arg(roleid)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must be superuser"))); - } - else - { - if (!has_createrole_privilege(roleid)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have CREATEROLE privilege"))); - } + if (!pg_role_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, objtype, + strVal(object)); break; case OBJECT_TSPARSER: case OBJECT_TSTEMPLATE: diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c index 6c6ab9ee34..3447806756 100644 --- a/src/backend/commands/schemacmds.c +++ b/src/backend/commands/schemacmds.c @@ -363,7 +363,7 @@ AlterSchemaOwner_internal(HeapTuple tup, Relation rel, Oid newOwnerId) /* * must have create-schema rights * - * NOTE: This is different from other alter-owner checks in that the + * NOTE: This is different from most other alter-owner checks in that the * current user is checked for create privileges instead of the * destination owner. This is consistent with the CREATE case for * schemas. Because superusers will always have this right, we need diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index e1296020b3..9c40766d83 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -724,7 +724,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) !rolemembers && !validUntil && dpassword && - roleid == GetUserId())) + !pg_role_ownercheck(roleid, GetUserId()))) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied"))); @@ -925,7 +925,8 @@ AlterRoleSet(AlterRoleSetStmt *stmt) } else { - if (!have_createrole_privilege() && roleid != GetUserId()) + if (!have_createrole_privilege() && + !pg_role_ownercheck(roleid, GetUserId())) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied"))); @@ -977,11 +978,6 @@ DropRole(DropRoleStmt *stmt) pg_auth_members_rel; ListCell *item; - if (!have_createrole_privilege()) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("permission denied to drop role"))); - /* * Scan the pg_authid relation to find the Oid of the role(s) to be * deleted. @@ -1053,6 +1049,12 @@ DropRole(DropRoleStmt *stmt) (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to drop superusers"))); + if (!have_createrole_privilege() && + !pg_role_ownercheck(roleid, GetUserId())) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied to drop role"))); + /* DROP hook for the role being removed */ InvokeObjectDropHook(AuthIdRelationId, roleid, 0); diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index 67f8b29434..04eae9d4e5 100644 --- a/src/backend/utils/adt/acl.c +++ b/src/backend/utils/adt/acl.c @@ -4850,6 +4850,10 @@ has_privs_of_role(Oid member, Oid role) if (superuser_arg(member)) return true; + /* Owners of roles have every privilge the owned role has */ + if (pg_role_ownercheck(role, member)) + return true; + /* * Find all the roles that member has the privileges of, including * multi-level recursion, then see if target role is any one of them. diff --git a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out index b2d898a7d1..93cf82b750 100644 --- a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out +++ b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out @@ -7,8 +7,11 @@ SET client_min_messages TO 'warning'; DROP ROLE IF EXISTS regress_dummy_seclabel_user1; DROP ROLE IF EXISTS regress_dummy_seclabel_user2; RESET client_min_messages; -CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE; +CREATE USER regress_dummy_seclabel_user0 WITH CREATEROLE; +SET SESSION AUTHORIZATION regress_dummy_seclabel_user0; +CREATE USER regress_dummy_seclabel_user1; CREATE USER regress_dummy_seclabel_user2; +RESET SESSION AUTHORIZATION; CREATE TABLE dummy_seclabel_tbl1 (a int, b text); CREATE TABLE dummy_seclabel_tbl2 (x int, y text); CREATE VIEW dummy_seclabel_view1 AS SELECT * FROM dummy_seclabel_tbl2; @@ -19,7 +22,7 @@ ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2; -- -- Test of SECURITY LABEL statement with a plugin -- -SET SESSION AUTHORIZATION regress_dummy_seclabel_user1; +SET SESSION AUTHORIZATION regress_dummy_seclabel_user0; SECURITY LABEL ON TABLE dummy_seclabel_tbl1 IS 'classified'; -- OK SECURITY LABEL ON COLUMN dummy_seclabel_tbl1.a IS 'unclassified'; -- OK SECURITY LABEL ON COLUMN dummy_seclabel_tbl1 IS 'unclassified'; -- fail @@ -29,6 +32,7 @@ ERROR: '...invalid label...' is not a valid security label SECURITY LABEL FOR 'dummy' ON TABLE dummy_seclabel_tbl1 IS 'unclassified'; -- OK SECURITY LABEL FOR 'unknown_seclabel' ON TABLE dummy_seclabel_tbl1 IS 'classified'; -- fail ERROR: security label provider "unknown_seclabel" is not loaded +SET SESSION AUTHORIZATION regress_dummy_seclabel_user1; SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'unclassified'; -- fail (not owner) ERROR: must be owner of table dummy_seclabel_tbl2 SECURITY LABEL ON TABLE dummy_seclabel_tbl1 IS 'secret'; -- fail (not superuser) @@ -42,7 +46,7 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified'; -- OK -- -- Test for shared database object -- -SET SESSION AUTHORIZATION regress_dummy_seclabel_user1; +SET SESSION AUTHORIZATION regress_dummy_seclabel_user0; SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified'; -- OK SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...'; -- fail ERROR: '...invalid label...' is not a valid security label @@ -55,7 +59,7 @@ SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'unclassified'; -- fail ( ERROR: role "regress_dummy_seclabel_user3" does not exist SET SESSION AUTHORIZATION regress_dummy_seclabel_user2; SECURITY LABEL ON ROLE regress_dummy_seclabel_user2 IS 'unclassified'; -- fail (not privileged) -ERROR: must have CREATEROLE privilege +ERROR: must be owner of role regress_dummy_seclabel_user2 RESET SESSION AUTHORIZATION; -- -- Test for various types of object diff --git a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql index 8c347b6a68..bf575343cf 100644 --- a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql +++ b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql @@ -11,8 +11,12 @@ DROP ROLE IF EXISTS regress_dummy_seclabel_user2; RESET client_min_messages; -CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE; +CREATE USER regress_dummy_seclabel_user0 WITH CREATEROLE; + +SET SESSION AUTHORIZATION regress_dummy_seclabel_user0; +CREATE USER regress_dummy_seclabel_user1; CREATE USER regress_dummy_seclabel_user2; +RESET SESSION AUTHORIZATION; CREATE TABLE dummy_seclabel_tbl1 (a int, b text); CREATE TABLE dummy_seclabel_tbl2 (x int, y text); @@ -26,7 +30,7 @@ ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2; -- -- Test of SECURITY LABEL statement with a plugin -- -SET SESSION AUTHORIZATION regress_dummy_seclabel_user1; +SET SESSION AUTHORIZATION regress_dummy_seclabel_user0; SECURITY LABEL ON TABLE dummy_seclabel_tbl1 IS 'classified'; -- OK SECURITY LABEL ON COLUMN dummy_seclabel_tbl1.a IS 'unclassified'; -- OK @@ -34,6 +38,8 @@ SECURITY LABEL ON COLUMN dummy_seclabel_tbl1 IS 'unclassified'; -- fail SECURITY LABEL ON TABLE dummy_seclabel_tbl1 IS '...invalid label...'; -- fail SECURITY LABEL FOR 'dummy' ON TABLE dummy_seclabel_tbl1 IS 'unclassified'; -- OK SECURITY LABEL FOR 'unknown_seclabel' ON TABLE dummy_seclabel_tbl1 IS 'classified'; -- fail + +SET SESSION AUTHORIZATION regress_dummy_seclabel_user1; SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'unclassified'; -- fail (not owner) SECURITY LABEL ON TABLE dummy_seclabel_tbl1 IS 'secret'; -- fail (not superuser) SECURITY LABEL ON TABLE dummy_seclabel_tbl3 IS 'unclassified'; -- fail (not found) @@ -45,7 +51,7 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified'; -- OK -- -- Test for shared database object -- -SET SESSION AUTHORIZATION regress_dummy_seclabel_user1; +SET SESSION AUTHORIZATION regress_dummy_seclabel_user0; SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified'; -- OK SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...'; -- fail diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out index e1ce5f3a66..492162e5a1 100644 --- a/src/test/regress/expected/create_role.out +++ b/src/test/regress/expected/create_role.out @@ -55,8 +55,9 @@ CREATE ROLE regress_role_9 INHERIT; CREATE ROLE regress_role_10 CONNECTION LIMIT 5; CREATE ROLE regress_role_11 PASSWORD NULL; CREATE ROLE regress_role_12 - CREATEDB CREATEROLE LOGIN INHERIT CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'foo' - IN ROLE regress_role_6, regress_role_7, regress_role_8; + CREATEDB CREATEROLE INHERIT CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'foo' + IN ROLE regress_role_6, regress_role_7; +COMMENT ON ROLE regress_role_12 IS 'no login test role'; \du+ regress_role_6 List of roles Role name | Owner | Attributes | Member of | Description @@ -95,11 +96,11 @@ CREATE ROLE regress_role_12 regress_role_11 | regress_role_1 | Cannot login | {} | \du+ regress_role_12 - List of roles - Role name | Owner | Attributes | Member of | Description ------------------+----------------+------------------------+------------------------------------------------+------------- - regress_role_12 | regress_role_1 | Create role, Create DB+| {regress_role_6,regress_role_7,regress_role_8} | - | | 2 connections | | + List of roles + Role name | Owner | Attributes | Member of | Description +-----------------+----------------+--------------------------------------+---------------------------------+-------------------- + regress_role_12 | regress_role_1 | Create role, Create DB, Cannot login+| {regress_role_6,regress_role_7} | no login test role + | | 2 connections | | -- ok, backwards compatible noise words should be ignored CREATE ROLE regress_role_13 SYSID 12345; @@ -140,21 +141,18 @@ CREATE TABLE regress_tbl_22 (i integer); CREATE INDEX regress_idx_22 ON regress_tbl_22(i); CREATE VIEW regress_view_22 AS SELECT * FROM pg_catalog.pg_class; REVOKE ALL PRIVILEGES ON regress_tbl_22 FROM PUBLIC; --- fail, these objects belonging to regress_role_22 +-- ok, owning role can manage owned role's objects SET SESSION AUTHORIZATION regress_role_7; DROP INDEX regress_idx_22; -ERROR: must be owner of index regress_idx_22 ALTER TABLE regress_tbl_22 ADD COLUMN t text; -ERROR: must be owner of table regress_tbl_22 DROP TABLE regress_tbl_22; -ERROR: must be owner of table regress_tbl_22 +-- fail, not a member of target role ALTER VIEW regress_view_22 OWNER TO regress_role_1; -ERROR: must be owner of view regress_view_22 +ERROR: must be member of role "regress_role_1" +-- ok DROP VIEW regress_view_22; -ERROR: must be owner of view regress_view_22 --- fail, cannot take ownership of these objects from regress_role_22 +-- ok, can take ownership objects from owned roles REASSIGN OWNED BY regress_role_22 TO regress_role_7; -ERROR: permission denied to reassign objects -- ok, having CREATEROLE is 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; @@ -166,15 +164,9 @@ CREATE ROLE regress_role_29 IN ROLE pg_read_server_files; CREATE ROLE regress_role_30 IN ROLE pg_write_server_files; CREATE ROLE regress_role_31 IN ROLE pg_execute_server_program; CREATE ROLE regress_role_32 IN ROLE pg_signal_backend; --- fail, cannot take ownership of these objects from regress_role_7 +-- ok, can take ownership from owned roles SET SESSION AUTHORIZATION regress_role_1; ALTER ROLE regress_role_20 OWNER TO regress_role_1; -ERROR: must be owner of role regress_role_20 -REASSIGN OWNED BY regress_role_20 TO regress_role_1; -ERROR: permission denied to reassign objects --- superuser can do it, though -RESET SESSION AUTHORIZATION; -ALTER ROLE regress_role_20 OWNER TO regress_role_1; REASSIGN OWNED BY regress_role_20 TO regress_role_1; -- ok, superuser roles can drop superuser roles they own SET SESSION AUTHORIZATION regress_role_super; @@ -184,14 +176,6 @@ SET SESSION AUTHORIZATION regress_role_1; DROP ROLE regress_role_3; DROP ROLE regress_role_4; DROP ROLE regress_role_5; -DROP ROLE regress_role_14; -ERROR: role "regress_role_14" does not exist -DROP ROLE regress_role_15; -ERROR: role "regress_role_15" does not exist -DROP ROLE regress_role_17; -ERROR: role "regress_role_17" does not exist -DROP ROLE regress_role_19; -ERROR: role "regress_role_19" does not exist DROP ROLE regress_role_20; -- fail, cannot drop roles that own other roles DROP ROLE regress_role_7; @@ -219,6 +203,7 @@ DROP ROLE regress_role_13; 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; @@ -229,28 +214,15 @@ DROP ROLE regress_role_29; DROP ROLE regress_role_30; DROP ROLE regress_role_31; DROP ROLE regress_role_32; --- fail, role still owns database objects -DROP ROLE regress_role_22; -ERROR: role "regress_role_22" cannot be dropped because some objects depend on it -DETAIL: owner of table regress_tbl_22 -owner of view regress_view_22 --- fail, role still owns other roles -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_22 -- fail, cannot drop ourself nor superusers DROP ROLE regress_role_super; ERROR: must be superuser to drop superusers DROP ROLE regress_role_1; ERROR: current user cannot be dropped --- ok -RESET SESSION AUTHORIZATION; -DROP INDEX regress_idx_22; -DROP TABLE regress_tbl_22; -DROP VIEW regress_view_22; -DROP ROLE regress_role_22; +-- ok, no more owned roles remain DROP ROLE regress_role_7; -- fail, cannot drop role with remaining privileges +RESET SESSION AUTHORIZATION; DROP ROLE regress_role_1; ERROR: role "regress_role_1" cannot be dropped because some objects depend on it DETAIL: privileges for database regression diff --git a/src/test/regress/sql/create_role.sql b/src/test/regress/sql/create_role.sql index 8a4f177574..678f728f52 100644 --- a/src/test/regress/sql/create_role.sql +++ b/src/test/regress/sql/create_role.sql @@ -36,8 +36,9 @@ CREATE ROLE regress_role_9 INHERIT; CREATE ROLE regress_role_10 CONNECTION LIMIT 5; CREATE ROLE regress_role_11 PASSWORD NULL; CREATE ROLE regress_role_12 - CREATEDB CREATEROLE LOGIN INHERIT CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'foo' - IN ROLE regress_role_6, regress_role_7, regress_role_8; + CREATEDB CREATEROLE INHERIT CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'foo' + IN ROLE regress_role_6, regress_role_7; +COMMENT ON ROLE regress_role_12 IS 'no login test role'; \du+ regress_role_6 \du+ regress_role_7 @@ -92,15 +93,19 @@ CREATE INDEX regress_idx_22 ON regress_tbl_22(i); CREATE VIEW regress_view_22 AS SELECT * FROM pg_catalog.pg_class; REVOKE ALL PRIVILEGES ON regress_tbl_22 FROM PUBLIC; --- fail, these objects belonging to regress_role_22 +-- ok, owning role can manage owned role's objects SET SESSION AUTHORIZATION regress_role_7; DROP INDEX regress_idx_22; ALTER TABLE regress_tbl_22 ADD COLUMN t text; DROP TABLE regress_tbl_22; + +-- fail, not a member of target role ALTER VIEW regress_view_22 OWNER TO regress_role_1; + +-- ok DROP VIEW regress_view_22; --- fail, cannot take ownership of these objects from regress_role_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 @@ -115,16 +120,11 @@ CREATE ROLE regress_role_30 IN ROLE pg_write_server_files; CREATE ROLE regress_role_31 IN ROLE pg_execute_server_program; CREATE ROLE regress_role_32 IN ROLE pg_signal_backend; --- fail, cannot take ownership of these objects from regress_role_7 +-- ok, can take ownership from owned roles SET SESSION AUTHORIZATION regress_role_1; ALTER ROLE regress_role_20 OWNER TO regress_role_1; REASSIGN OWNED BY regress_role_20 TO regress_role_1; --- superuser can do it, though -RESET SESSION AUTHORIZATION; -ALTER ROLE regress_role_20 OWNER TO regress_role_1; -REASSIGN OWNED BY regress_role_20 TO regress_role_1; - -- ok, superuser roles can drop superuser roles they own SET SESSION AUTHORIZATION regress_role_super; DROP ROLE regress_role_2; @@ -134,10 +134,6 @@ SET SESSION AUTHORIZATION regress_role_1; DROP ROLE regress_role_3; DROP ROLE regress_role_4; DROP ROLE regress_role_5; -DROP ROLE regress_role_14; -DROP ROLE regress_role_15; -DROP ROLE regress_role_17; -DROP ROLE regress_role_19; DROP ROLE regress_role_20; -- fail, cannot drop roles that own other roles @@ -154,6 +150,7 @@ DROP ROLE regress_role_13; 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; @@ -165,25 +162,15 @@ DROP ROLE regress_role_30; DROP ROLE regress_role_31; DROP ROLE regress_role_32; --- fail, role still owns database objects -DROP ROLE regress_role_22; - --- fail, role still owns other roles -DROP ROLE regress_role_7; - -- fail, cannot drop ourself nor superusers DROP ROLE regress_role_super; DROP ROLE regress_role_1; --- ok -RESET SESSION AUTHORIZATION; -DROP INDEX regress_idx_22; -DROP TABLE regress_tbl_22; -DROP VIEW regress_view_22; -DROP ROLE regress_role_22; +-- ok, no more owned roles remain DROP ROLE regress_role_7; -- fail, cannot drop role with remaining privileges +RESET SESSION AUTHORIZATION; DROP ROLE regress_role_1; -- ok, can drop role if we revoke privileges first