public inbox for [email protected]help / color / mirror / Atom feed
Proposal: DROP ROLE ... REASSIGN OWNED TO ... 4+ messages / 3 participants [nested] [flat]
* Proposal: DROP ROLE ... REASSIGN OWNED TO ... @ 2026-04-23 06:59 Zach Manifold <[email protected]> 2026-04-25 00:29 ` Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ... Robert Haas <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Zach Manifold @ 2026-04-23 06:59 UTC (permalink / raw) To: pgsql-hackers 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ... 2026-04-23 06:59 Proposal: DROP ROLE ... REASSIGN OWNED TO ... Zach Manifold <[email protected]> @ 2026-04-25 00:29 ` Robert Haas <[email protected]> 2026-04-25 00:41 ` Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ... Zach Manifold <[email protected]> 2026-04-25 01:12 ` Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ... Tom Lane <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Robert Haas @ 2026-04-25 00:29 UTC (permalink / raw) To: Zach Manifold <[email protected]>; +Cc: pgsql-hackers On Thu, Apr 23, 2026 at 2:59 AM Zach Manifold <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ... 2026-04-23 06:59 Proposal: DROP ROLE ... REASSIGN OWNED TO ... Zach Manifold <[email protected]> 2026-04-25 00:29 ` Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ... Robert Haas <[email protected]> @ 2026-04-25 00:41 ` Zach Manifold <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Zach Manifold @ 2026-04-25 00:41 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: pgsql-hackers > 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 <[email protected]> wrote: > On Thu, Apr 23, 2026 at 2:59 AM Zach Manifold <[email protected]> > 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 > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ... 2026-04-23 06:59 Proposal: DROP ROLE ... REASSIGN OWNED TO ... Zach Manifold <[email protected]> 2026-04-25 00:29 ` Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ... Robert Haas <[email protected]> @ 2026-04-25 01:12 ` Tom Lane <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Tom Lane @ 2026-04-25 01:12 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Zach Manifold <[email protected]>; pgsql-hackers Robert Haas <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-04-25 01:12 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-04-23 06:59 Proposal: DROP ROLE ... REASSIGN OWNED TO ... Zach Manifold <[email protected]> 2026-04-25 00:29 ` Robert Haas <[email protected]> 2026-04-25 00:41 ` Zach Manifold <[email protected]> 2026-04-25 01:12 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox