Clarification on DROP OWNED BY command in PG18

From: DIPESH DHAMELIYA <dipeshdhameliya125(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Clarification on DROP OWNED BY command in PG18
Date: 2025-09-15 17:13:06
Message-ID: CABgZEgczOFicCJoqtrH9gbYMe_BV3Hq8zzCBRcMgmU6LRsihUA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-hackers by date

  From Date Subject
Next Message Marcos Pegoraro 2025-09-15 17:26:22 Changing on SGML Notes
Previous Message Masahiko Sawada 2025-09-15 17:00:18 Re: Make COPY format extendable: Extract COPY TO format implementations