Re: Role Self-Administration

Greetings,

* Mark Dilger (mark(dot)dilger(at)enterprisedb(dot)com) wrote:
> > On Oct 5, 2021, at 10:20 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > On Tue, Oct 5, 2021 at 13:17 Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
> > > On Oct 5, 2021, at 10:14 AM, Stephen Frost <sfrost(at)snowman(dot)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