Proposal: DROP ROLE ... REASSIGN OWNED TO ...
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
On Thu, Apr 23, 2026 at 2:59 AM Zach Manifold <zachlweaver00@gmail.com> wrote:
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.
There's no problem of this type -- the whole statement would execute
as a single transaction, and any failure would role the whole thing
back.
But I'm a little bit skeptical of the underlying proposal for related
reasons. This doesn't really let you do anything that you can't easily
do already:
rhaas=# create role joe;
CREATE ROLE
rhaas=# begin;
BEGIN
rhaas=*# reassign owned by joe to fred;
REASSIGN OWNED
rhaas=*# drop role joe;
DROP ROLE
rhaas=*# commit;
COMMIT
This would fail if the user to be dropped owned objects in another
database, but your hypothetical version of DROP ROLE would have that
issue, too. Even if you couldn't wrap both commands in a single
transaction -- we have some DDL commands that are like that -- running
them one after another wouldn't lose much. So I'm just not sure I
really see the point. If we add a bunch of stuff like this, it will
take work to maintain, but most users won't be able to remember all
the variations that exist at the moment when they might benefit from
them. We might also end up with a patchwork where some things are
supported and seemingly related things are not supported, just because
of the idiosyncrasies of what got implemented and what didn't. I'm not
saying nobody would ever benefit from something like this -- probably
some people would -- but I don't know that there would be all that
many of them or that the benefit would be all that much.
--
Robert Haas
EDB: http://www.enterprisedb.com
If we add a bunch of stuff like this, it will
take work to maintain, but most users won't be able to remember all
the variations that exist at the moment when they might benefit from
them.
That's a fair point. I agree the value add is fairly minimal, since my
intention was a tiny quality of life enhancement, but to your point
if there are different variations of doing the same thing it can either
end up being confusing or not utilized.
Thanks for reviewing.
- Zach Weaver
On Fri, Apr 24, 2026 at 5:29 PM Robert Haas <robertmhaas@gmail.com> wrote:
Show quoted text
On Thu, Apr 23, 2026 at 2:59 AM Zach Manifold <zachlweaver00@gmail.com>
wrote: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.There's no problem of this type -- the whole statement would execute
as a single transaction, and any failure would role the whole thing
back.But I'm a little bit skeptical of the underlying proposal for related
reasons. This doesn't really let you do anything that you can't easily
do already:rhaas=# create role joe;
CREATE ROLE
rhaas=# begin;
BEGIN
rhaas=*# reassign owned by joe to fred;
REASSIGN OWNED
rhaas=*# drop role joe;
DROP ROLE
rhaas=*# commit;
COMMITThis would fail if the user to be dropped owned objects in another
database, but your hypothetical version of DROP ROLE would have that
issue, too. Even if you couldn't wrap both commands in a single
transaction -- we have some DDL commands that are like that -- running
them one after another wouldn't lose much. So I'm just not sure I
really see the point. If we add a bunch of stuff like this, it will
take work to maintain, but most users won't be able to remember all
the variations that exist at the moment when they might benefit from
them. We might also end up with a patchwork where some things are
supported and seemingly related things are not supported, just because
of the idiosyncrasies of what got implemented and what didn't. I'm not
saying nobody would ever benefit from something like this -- probably
some people would -- but I don't know that there would be all that
many of them or that the benefit would be all that much.--
Robert Haas
EDB: http://www.enterprisedb.com
Robert Haas <robertmhaas@gmail.com> writes:
This would fail if the user to be dropped owned objects in another
database, but your hypothetical version of DROP ROLE would have that
issue, too. Even if you couldn't wrap both commands in a single
transaction -- we have some DDL commands that are like that -- running
them one after another wouldn't lose much. So I'm just not sure I
really see the point.
Yeah. We intentionally separated this functionality because the
expectation is that you'll likely have to do REASSIGN OWNED in
multiple databases before DROP ROLE will succeed. So I'm not
much in favor of adding a combined command; it will just lead
people to try to do things that won't work.
regards, tom lane