> On Aug 23, 2021, at 1:46 PM, Stephen Frost <sfrost@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
withoutcreaterole or superuser cannot create other roles. We don't want tenants to need either of those things, at
leastnot as they are currently defined. We could either modify the createrole privilege to be far less powerful, or
createa new privilege.
If role owners can alter and drop roles they own (and ones those roles own, etc.) then we could redefine CREATEROLE to
reallyjust 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
(exceptthe 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
privilegeover the other fiefdoms. I think that setup wasn't implemented for 8.1 more for lack of time than because it
wasunwanted.
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
