Re: CREATEROLE and role ownership hierarchies - Mailing list pgsql-hackers

> On Feb 2, 2022, at 11:52 AM, Stephen Frost <sfrost@snowman.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,
butI 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
everyorganization will want the same level of privilege to be given to a creator, or even that all creators have equal
levelsof privilege.  So when the superuser creates a creator, the superuser needs to be able to configure what exactly
whatthat creator can do.  This includes which attributes the creator can give to new roles.  It *might* include whether
thecreator maintains a dependency link with the created role, called "ownership" or somesuch.  It *might* include
whetherthe creator can create roles into which the creator is granted membership/administership.  But there really
isn'tany 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
likesuperuser 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
creatorcreates.  To the extent that the creator has been granted the privilege to create yet more creators, this
recursesto 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
thedetails 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
superuserover the sandbox, while at the same time Stephen, acting as superuser, may want to create a creator who acts
asa 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
committedin time for postgres 15.  If not, then I'm probably going to stop working on this until next year, because at
thispoint, we don't have enough time to finish. 

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