On Mon, Oct 25, 2021 at 3:25 PM Robert Haas <robertmh...@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
