Re: Clarification on DROP OWNED BY command in PG18

Nathan Bossart <nathandbossart(at)gmail(dot)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