Re: CREATEROLE and role ownership hierarchies

> On Feb 2, 2022, at 11:52 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> The question that we need to solve is how to give
> users the ability to choose what roles have which of the privileges that
> we've outlined above and agreed should be separable.

Ok, there are really two different things going on here, and the conversation keeps conflating them. Maybe I'm wrong, but I think the conflation of these things is the primary problem preventing us from finishing up the design.

Thing 1: The superuser needs to be able to create roles who can create other roles. Let's call them "creators". Not every organization will want the same level of privilege to be given to a creator, or even that all creators have equal levels of privilege. So when the superuser creates a creator, the superuser needs to be able to configure what exactly what that creator can do. This includes which attributes the creator can give to new roles. It *might* include whether the creator maintains a dependency link with the created role, called "ownership" or somesuch. It *might* include whether the creator can create roles into which the creator is granted membership/administership. But there really isn't any reason that these things should be all-or-nothing. Maybe one creator maintains a dependency link with created roles, and that dependency link entails some privileges. Maybe other creators do not maintain such a link. It seems like superuser can define a creator in many different ways, as long as we nail down what those ways are, and what they mean.

Thing 2: The creator needs to be able to specify which attributes and role memberships are set up with for roles the creator creates. To the extent that the creator has been granted the privilege to create yet more creators, this recurses to Thing 1. But not all creators will have that ability.

I think the conversation gets off topic and disagreement abounds when Thing 1 is assumed to be hardcoded, leaving just the details of Thing 2 to be discussed.

It's perfectly reasonable (in my mind) that Robert, acting as superuser, may want to create a creator who acts like a superuser over the sandbox, while at the same time Stephen, acting as superuser, may want to create a creator who acts as a low privileged bot that only adds and removes roles, but cannot read their tables, SET ROLE to them, etc.

I don't see any reason that Robert and Stephen can't both get what they want. We just have to make Thing 1 flexible enough.

Do you agree at least with this much? If so, I think we can hammer out what to do about Thing 1 and get something committed in time for postgres 15. If not, then I'm probably going to stop working on this until next year, because at this point, we don't have enough time to finish.

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