Обсуждение: Clarification on DROP OWNED BY command in PG18

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

Thanks for reporting.

On Mon, Sep 15, 2025 at 10:43:06PM +0530, DIPESH DHAMELIYA wrote:
> 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.

Yeah, that doesn't seem right to me.  It's quite late in the game for v18,
and given the low severity of the bug that commit 98fc31d intended to fix
and the fact that it wasn't back-patched, I'm wondering if we should revert
for v18 and revisit in v19.

-- 
nathan

Nathan Bossart <nathandbossart@gmail.com> writes:
> On Mon, Sep 15, 2025 at 10:43:06PM +0530, DIPESH DHAMELIYA wrote:
>> 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.

> Yeah, that doesn't seem right to me.  It's quite late in the game for v18,
> and given the low severity of the bug that commit 98fc31d intended to fix
> and the fact that it wasn't back-patched, I'm wondering if we should revert
> for v18 and revisit in v19.

Hmm ... so 98fc31d64 was in error to claim that after DROP OWNED BY
there's no reason to be a member of the role.  You at least want to
keep admin privilege on it so you can drop it.  The shdep-based
approach doesn't seem able to handle that distinction.

I agree it's a bit late to be trying to solve this for v18,
and this problem is worse than the one we were trying to fix.
Will revert.

            regards, tom lane