Clarification on DROP OWNED BY command in PG18
От | DIPESH DHAMELIYA |
---|---|
Тема | Clarification on DROP OWNED BY command in PG18 |
Дата | |
Msg-id | CABgZEgczOFicCJoqtrH9gbYMe_BV3Hq8zzCBRcMgmU6LRsihUA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Clarification on DROP OWNED BY command in PG18
|
Список | pgsql-hackers |
Hello everyone, Starting from commit 98fc31d (PG18 only), there is a new behaviour for DROP OWNED BY command where it deletes entries from pg_auth_members (including entries with ADMIN option). This change can cause a user/role to lose the ability to DROP the role for which DROP OWNED BY was executed. Even when following the documentation guidance[0], users cannot DROP ROLE (except superuser). The same guidance succeeds on REL_17_STABLE. Here's a reproducer to demonstrate the issue: postgres=# create role role1 createrole; CREATE ROLE postgres=# set role role1; SET postgres=> create role role2 role role1; CREATE ROLE postgres=> select roleid::regrole, member::regrole, grantor::regrole, admin_option from pg_auth_members where roleid = 'role2'::regrole; roleid | member | grantor | admin_option --------+--------+----------+-------------- role2 | role1 | dddhamel | t role2 | role1 | role1 | f (2 rows) postgres=> create role role3 role role1; CREATE ROLE postgres=> select roleid::regrole, member::regrole, grantor::regrole, admin_option from pg_auth_members where roleid = 'role3'::regrole; roleid | member | grantor | admin_option --------+--------+----------+-------------- role3 | role1 | dddhamel | t role3 | role1 | role1 | f (2 rows) postgres=> reassign owned by role2 to role3; REASSIGN OWNED postgres=> select roleid::regrole, member::regrole, grantor::regrole, admin_option from pg_auth_members where roleid = 'role2'::regrole; roleid | member | grantor | admin_option --------+--------+----------+-------------- role2 | role1 | dddhamel | t role2 | role1 | role1 | f (2 rows) postgres=> drop owned by role2; DROP OWNED postgres=> select roleid::regrole, member::regrole, grantor::regrole, admin_option from pg_auth_members where roleid = 'role2'::regrole; roleid | member | grantor | admin_option --------+--------+---------+-------------- (0 rows) postgres=> drop role role2; ERROR: permission denied to drop role DETAIL: Only roles with the CREATEROLE attribute and the ADMIN option on role "role2" may drop this role. Is this behavior change intentional? If so, what is the recommended way for non-superuser roles to clean up such roles? Should the documentation be updated to reflect this change? [0] https://www.postgresql.org/docs/devel/role-removal.html Thanks, Dipesh
В списке pgsql-hackers по дате отправления: