Re: Role Self-Administration - Mailing list pgsql-hackers

On Tue, Oct 5, 2021 at 3:41 PM Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> 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'm not sure that I'm totally on board with the role ownership
concept, but I do think it has some potential advantages. For
instance, suppose the dba creates a bunch of "master tenant" roles
which correspond to particular customers: say, amazon, google, and
facebook. Now each of those master tenant rolls creates roles under it
which represent the various people or applications from those
companies that will be accessing the server: e.g. sbrin and lpage.
Now, if Google runs out of money and stops paying the hosting bill, we
can just "DROP ROLE google CASCADE" and sbrin and lpage get nuked too.
That's kind of cool. What happens if we don't have that? Then we'll
need to work harder to make sure all traces of Google are expunged
from the system.

In fact, how do we do that, exactly? In this particular instance it
should be straightforward: if we see that google can administrer sbrin
and lpage and nobody else can, then it probably follows that those
roles should be nuked when the google role is nuked. But what if we
have separate users apple and nextstep either of whom can administer
the role sjobs? If nextstep goes away, we had better not remove sjobs
because he's still able to be administered by apple, but if apple also
goes away, then we'll want to remove sjobs then. That's doable, but
complicated, and all the logic that figures this out now lives outside
the database. With role ownership, we can enforce that the roles form
a tree, and subtrees can be easily lopped off without the user needing
to do anything complicated.

Without role ownership, we've just got a directed graph of who can
administer who, and it need not be connected or acyclic. Now we may be
able to cope with that, or we may be able to set things up so that
users can cope with that using logic external to the database without
anything getting too complicated. But I certainly see the appeal of a
system where the lines of control form a DAG rather than a general
directed graph. It seems to make it a whole lot easier to reason about
what operations should and should not be permitted and how the whole
thing should actually work. It's a fairly big change from the status
quo, though, and maybe it has disadvantages that make it a suboptimal
choice.

--
Robert Haas
EDB: http://www.enterprisedb.com