Greetings, * Mark Dilger (mark.dil...@enterprisedb.com) wrote: > > On Oct 5, 2021, at 10:20 AM, Stephen Frost <sfr...@snowman.net> wrote: > > On Tue, Oct 5, 2021 at 13:17 Mark Dilger <mark.dil...@enterprisedb.com> > > wrote: > > > On Oct 5, 2021, at 10:14 AM, Stephen Frost <sfr...@snowman.net> wrote: > > > > > > What does the “ownership” concept actually buy us then? > > > > DROP ... CASCADE > > > > I’m not convinced that we need to invent the concept of ownership in order > > to find a sensible way to make this work- though it would be helpful to > > first get everyone’s idea of just what *would* this command do if run on a > > role who “owns” or has “admin rights” of another role? > > Ok, I'll start. Here is how I envision it: > > If roles have owners, then DROP ROLE bob CASCADE drops bob, bob's objects, > roles owned by bob, their objects and any roles they own, recursively. Roles > which bob merely has admin rights on are unaffected, excepting that they are > administered by one fewer roles once bob is gone. > > This design allows you to delegate to a new role some task, and you don't > have to worry what network of other roles and objects they create, because in > the end you just drop the one role cascade and all that other stuff is > guaranteed to be cleaned up without any leaks. > > If roles do not have owners, then DROP ROLE bob CASCADE drops role bob plus > all objects that bob owns. It doesn't cascade to other roles because the > concept of "roles that bob owns" doesn't exist. If bob created other roles, > those will be left around. Objects that bob created and then transferred to > these other roles are also left around.
I can see how what you describe as the behavior you'd like to see of DROP ROLE ... CASCADE could be useful... However, at least in the latest version of the standard that I'm looking at, when a DROP ROLE ... CASCADE is executed, what happens for all authorization identifiers is: REVOKE R FROM A DB Where R is the role being dropped and A is the authoriztaion identifier. In other words, the SQL committee seems to disagree with you when it comes to what CASCADE on DROP ROLE means (though I can't say I'm too surprised- generally speaking, CASCADE is about getting rid of the dependency so the system stays consistent, not as a method of object management...). I'm not against having something that would do what you want, but it seems like we'd have to at least call it something else and maybe we should worry about that later, once we've addressed the bigger issue of making the system handle GRANTORs correctly. Thanks, Stephen
signature.asc
Description: PGP signature