Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)

> On Oct 27, 2021, at 1:10 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> This is effectively the same thing as Mark's proposal, but just using
> a new kind of object (TENANT) where Mark used an existing kind of
> object (ROLE). And it addresses Noah's concern, perhaps, because with
> the approach the tenant administrator isn't a member of every role,
> but rather just gets the privileges of all the roles as if they were.
> You might argue that's a distinction without a difference, but I don't
> think so: the superuser is in effect a member of every role as things
> stand, and the whole idea of this project is to all for
> quasi-superusers who can administer a subset of the users in the
> system, so something of this kind seems like it has to exist for the
> proposal to achieve its object. But it need not be role membership per
> se, and maybe shouldn't be.

It feels to me that the traditional concept of users and groups could map, one-to-one, onto users and roles, but we've mapped both users and groups, many-to-one, onto roles, leaving no distinct concept of groups, and now we're proposing adding a concept called "tenant" that means something like "group". I find that simultaneously helpful and pretty confusing.

Compare that to the help and confusion created by my proposal. The idea that roles can own roles, just as roles can own tables, indexes, etc., doesn't seem confusing to me, but perhaps it does to others. If you accept that roles can own roles, then the idea that roles can drop roles that they own, or change characteristics of roles that they own, is entirely analogous to roles being able to drop or alter any other sort of object that they own. To me, that is perfectly consistent and unsurprising, but again, perhaps not to others.

Noah's concern, as I understood it, was not about roles owning roles, but about role membership being what controls if an event trigger fires. If anything, that concern stems from the lack of role ownership, not the existence of it, because I wrote the event trigger patch set to not depend on the role ownership patch set. Once you have a concept of role ownership, it is perfectly natural that the trigger could fire based on whether the trigger owner is the owner of (or the same as) the role performing the action. That completely sidesteps the concern about the event trigger role needing to be a member of any log-in role, because you no longer need the event trigger owner to be a member of the log-in role.

There are semantic details to be worked out with role ownership, such as whether a role owner automatically has the privileges of roles it owns, whether such privilege, if any, should behave à la INHERIT or NOINHERIT, whether superusers should own roles they create or whether there should be a special rule that superuser created roles should belong to the bootstrap superuser, etc. The patch set has taken a position on each of these, because it cannot be implemented without some choice being made, but many of these decisions could be changed if they are the source of confusion. If, on the other hand, having parallel concepts "role A owns role B" and "role C is a member of role D" is too confusing for people to ever keep straight, then perhaps we need something like "tenant" to help lessen the confusion.

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