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

> On Aug 23, 2021, at 1:46 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> I'd much rather we go down the path that Robert had suggested where we
> find a way to make a connection between the tenant role and everything
> that they create, and leave everything that is outside of that box on
> the other side of the 'wall'.

I am coming around to this way of thinking. The main difficulty here stems (as you know) from how CREATEROLE was implemented. You and Tom had conversations about that back in 2005 [1], and Tom even suggested perhaps roles have owners:

> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:

> Possibly for 8.2 we could invent a notion of roles having owners.
> Offhand I don't see any harm in letting non-CREATEROLE users create
> non-login roles, and manipulate the membership of roles they have
> created (or that have been assigned to them by a superuser). On the
> other hand, it could be that the WITH ADMIN OPTION feature is already
> sufficient for this. This really needs some thought ...

Making roles owners of roles they create, and giving them the power to manipulate objects which belong to roles they own (recursively), seems to solve most of our problems we have been discussing. The remaining problem is that roles without createrole or superuser cannot create other roles. We don't want tenants to need either of those things, at least not as they are currently defined. We could either modify the createrole privilege to be far less powerful, or create a new privilege.

If role owners can alter and drop roles they own (and ones those roles own, etc.) then we could redefine CREATEROLE to really just mean the ability to create new roles. The ability to alter or drop roles would not stem from having CREATEROLE, but rather from owning the role. For setups where one admin role has CREATEROLE and creates all other roles (except the superuser which created the admin) nothing changes. In setups with multiple admins, where none own the others, each admin would have its own fiefdom, managing everything downstream from itself, but having no special privilege over the other fiefdoms. I think that setup wasn't implemented for 8.1 more for lack of time than because it was unwanted.

Alternately, we could just create a new privilege parallel to CREATEROLE, but that seems confusing more than helpful.

Thoughts?

[1] https://www.postgresql.org/message-id/17554.1120258001%40sss.pgh.pa.us
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company