Re: Role Self-Administration

> On Oct 5, 2021, at 10:20 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> Greetings,
>
> 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.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company