> On Aug 23, 2021, at 1:46 PM, Stephen Frost <sfr...@snowman.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
