MIME-Version: 1.0
From: Zach Manifold <zachlweaver00@gmail.com>
Date: Wed, 22 Apr 2026 23:59:19 -0700
Message-ID: 
 <CACLQnSmysDOQEn9HJ+AEXMxQ0KvNg=T3+Emz65jkVSqxXaNcPQ@mail.gmail.com>
Subject: Proposal: DROP ROLE ... REASSIGN OWNED TO ...
To: pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CACLQnSmysDOQEn9HJ%2BAEXMxQ0KvNg%3DT3%2BEmz65jkVSqxXaNcPQ%40mail.gmail.com>
Precedence: bulk

Hi all,

First-time hacker here wanting to get into the community.

I am proposing a fairly small quality of life feature for
role management which is to allow users to reassign
ownership within the same command as dropping
a role.

Currently you must reassign ownership prior to dropping
a role (if it owns anything) such as:
REASSIGN OWNED BY role1 [, ...]TO role2;
DROP ROLE role1;

I am thinking of the usage:
DROP ROLE role1 [, ...] REASSIGN OWNED TO role2;

Original behavior would remain if not specifying the option:
DROP ROLE role1 [, ...];

For the implementation, I was thinking of adding a
RoleSpec into DropRoleStmt to hold the "assigned to role"
which would be NULL unless otherwise provided by the option.
The grammar for DropRoleStmt would be updated to add a
new option (opt_reassign) to optionally provide the RoleSpec.
There are other cases in the DropRoleStmt grammar such as
users and groups - I'm wondering if this would apply to those
as well (e.g., DROP USER ..., DROP GROUP ...)

Within the DropRole command (after all the initial validity checks,)
I was thinking to check if this value was non-null and create
a ReassignOwnedStmt before making a call to ReassignOwnedObjects
before the foreach loop is called to actually remove the list of roles.

My one concern is the possible failure mode - is it possible for
the reassignment to work but the role to fail to drop? Is this
preventable? I'm not sure how to "cleanly" approach this type
of safety where I can assure that both of these operations
must succeed rather than reassigning ownership and failing
to drop a role.

Are there any other failure modes I should be considering?

Would like to hear from anyone if there's any appetite for this and
your thoughts on how this can be approached.

Regards,
- Zach Weaver