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

On Mon, Oct 25, 2021 at 3:25 PM Robert Haas <robertmhaas@gmail.com> wrote:
> But also, Noah writes: "Also, it's currently a best practice for only
> non-LOGIN roles to have members.  The proposed approach invites folks
> to abandon that best practice."
>
> The kind of mechanism you're proposing here doesn't seem to make that
> any less likely.

OK, I have a new idea. Any system for multi-tenancy necessarily
involves keeping track of the association between users and tenants -
i.e. for any given user, to which tenant do they belong? In Mark's
system, you do that by creating a role, and you make that role the
owner of all of the other roles that are part of that tenant (so that
it can drop them) and you also make it a member of all of those other
roles (so that it can use their privileges).

But the thing that defines a tenant need not be a role. It can be some
other kind of object. Suppose we invent a CREATE TENANT command. Every
user is associated with exactly one tenant, and can optionally be an
administrator for that tenant. If you are the administrator for a
tenant, you can create event triggers that affect every user
associated with that tenant. You also have the privileges of every
user associated with that tenant. If you have the CREATEROLE
privilege, you can create new users who will be associated with the
same tenant as you are, and you can drop existing users that are
associated with that tenant (but not roles associated with other
tenants).

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.

I don't know if this is a better idea than what Mark did, but I think
it has some appealing properties. One is that you don't need to (and
indeed can't) have people log in as the lead tenant role directly -
because that concept does not exist. If Google is your customer, then
in Mark's proposal, you have a high-privilege 'google' account and you
log into that when you want to do high-privilege things. But what if
you have multiple people who are entitled to administer the Google
tenant? Either they share access to that single account, or, well, I
don't know. A second user could have membership in every login and
non-login role for the tenant, but only one user can be the owner of
all of those roles, so maybe there's no other way to do it. If the
tenant is a separate concept that sits to one side of the role system,
you can just make multiple users administrators of the google tenant,
and that should be fine. And that way, each one has their own password
and can be separately disabled if that person leaves the company or
demoted to a non-administrator if they get moved to a different job.

There might well be problems with this idea, either on a grand scale
that make the whole thing a non-starter, or on a smaller scale that
mean that the definition of what it means to be a tenant administrator
needs fine-tuning ... but I don't know what they are, so I thought I'd
throw this out there and see what people think.

Thanks,

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