On Tue, Oct 5, 2021 at 3:41 PM Mark Dilger <mark.dil...@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 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'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
