Re: Role Self-Administration

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 5, 2021, at 10:20 AM, Stephen Frost <sfrost@snowman.net> wrote:
> > On Tue, Oct 5, 2021 at 13:17 Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> > > On Oct 5, 2021, at 10:14 AM, Stephen Frost <sfrost@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
rolesthey own, recursively.  Roles which bob merely has admin rights on are unaffected, excepting that they are
administeredby 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
andobjects they create, because in the end you just drop the one role cascade and all that other stuff is guaranteed to
becleaned 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
cascadeto other roles because the concept of "roles that bob owns" doesn't exist.  If bob created other roles, those
willbe 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