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

> On Jan 31, 2022, at 10:50 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
> Supporting that through ADMIN is one option, another would be a
> 'DROPROLE' attribute, though we'd want a way to curtail that from being
> able to be used for just any role and that does lead down a path similar
> to ownership or just generally the concept that some roles have certain
> rights over certain other roles (whether you create them or not...).

I've been operating under the assumption that I have a lot more freedom to create new features than to change how
existingfeatures behave, for two reasons: backwards compatibility and sql-spec compliance. 

Changing how having ADMIN on a role works seems problematic for both those reasons.  My family got me socks for
Christmas,not what I actually wanted, a copy of the SQL-spec.  So I'm somewhat guessing here.  But I believe we'd have
problemsif we "fixed" the part where a role can revoke ADMIN from others on themselves.  Whatever we have, whether we
callit "ownership", it can't be something a role can unilaterally revoke. 

As for a 'DROPROLE' attribute, I don't think that gets us anywhere.  You don't seem to think so, either.  So that
leavesus with "ownership", perhaps by another word?  I only chose that word because it's what we use elsewhere, but if
wewant to call it "managementship" and "manager" or whatever, that's fine.  I'm not to the point of debating the
terminologyjust yet.  I'm still trying to get the behavior nailed down. 

> I do think there's a lot of value in being able to segregate certain
> rights- consider that you may want a role that's able to create other
> roles, perhaps grant them into some set of roles, can lock those roles
> (prevent them from logging in, maybe do a password reset, something like
> that), but which *isn't* able to drop those roles (and all their
> objects) as that's dangerous and mistakes can certainly happen, or be
> able to become that role because the creating role simply doesn't have
> any need to be able to do that (or desire to in many cases, as we
> discussed in the landlord-vs-tenant sub-thread).

I'm totally on the same page.  Your argument upthread about wanting any malfeasance on the part of a service provider
showingup in the audit logs was compelling.  Even for those things the "owner"/"manager" has the rights to do, we might
wantto make them choose to do it explicitly and not merely do it by accident. 

> Naturally, you'd want *some* role to be able to drop that role (and one
> that doesn't have full superuser access) but that might be a role that's
> not able to create new roles or take over accounts.

I think it's important to go beyond the idea of a role attribute here.  It's not that role "bob" can drop roles.  It's
that"bob" can drop *specific* roles, and for that, there has to be some kind of dependency tracked between "bob" and
thoseother roles.  I'm calling that "ownership".  I think that language isn't just arbitrary, but actually helpful
(technically,not politically) because REASSIGN OWNED should treat this kind of relationship exactly the same as it
treatsownership of schemas, tables, functions, etc. 

> Separation of concerns and powers and all of that is what we want to be
> going for here, more generically, which is why I was opposed to the
> blanket "owners have all rights of all roles they own" implementation.

I'm hoping to bring back, in v9, the idea of ownership/managership.  The real sticking point here is that we (Robert,
Andrew,I, and possibly others) want to be able to drop in a non-superuser-creator-role into existing systems that use
superuserfor role management.  We'd like it to be as transparent a switch as possible. 

With a superuser creating a role, that superuser can come back and muck with the role afterward, and the role can't
revokethe superuser's right to do so.  It's not enough that a non-superuser-creator-role (henceforth, "manager") can
grantitself ADMIN on the created role.  It also needs to be able to set passwords, transfer object ownerships to/from
therole, grant the role into other roles or other roles into it, etc.  All of that has to be sandboxed such that the
"manager"can't touch stuff outside the manager's sandbox, but within the sandbox, it shouldn't make any practical
differencethat the manager isn't actually a superuser. 

I think what I had in v7 was almost right.  I'm hoping that we just need to adjust things like the idea that managers
alwayshave implicit membership in and ADMIN on roles they manage.  I think that needs to be optional, and the audit
logscould show if the manager granted themselves such things, as it might violate policy and be a red flag in the audit
log.

> That approach doesn't support the ability to have a relatively
> unprivileged role that's able to create other roles, which seems like a
> pretty important use-case for us to be considering.

I think we have that ability. It's just that the creator role isn't "relatively unprivileged" vis-a-vis the created
role. But that could be handled by creating the role and then transferring the ownership to some other role, or
specifyingin the CREATE ROLE command that the creator doesn't want those privileges, etc.  That requires some tinkering
withthe design, though, because the permission to perform the ownership transfer to that other role would need to be
circumscribedto not give away other privileges, like the right to become that other role, or the specification that the
creatordisavows certain privileges over the created role might need to be something the creator could get back by force
withsome subsequent GRANT command, or ...? 

> The terminology seems to also be driving us in a certain direction and I
> don't know that it's necessarily a good one.  That is- the term 'owner'
> implies certain things and maybe that's where some of the objection to
> my argument that owners shouldn't necessarily have all rights of the
> roles they 'own' comes from

I think it does follow pretty closely the concept of ownership of objects, though.  So closely, in fact, that I don't
reallysee any daylight between the two concepts. 

> (ok- I'll also put out there for general
> consideration that since we're talking about roles, and login roles are
> generally associated with people, that maybe 'owner' isn't a great term
> to use for this anyway ...).

Technically, we're talking about roles within computers owning other roles within computers, not about people owning
people. We already have a command called REASSIGN OWNED, and if we don't call this ownership, then that command gets
reallysquirrelly.  Does it also reassign "managed"? 

On the other hand, I'm not looking to create offense, so if this language seems unacceptable, perhaps you could propose
somethingelse? 

>  I feel like the 'owner' concept came from
> the way we have table owners and function owners and database owners
> today rather than from a starting point of what do we wish to
> specifically enable.

Let's compare this to the idea of owning a table.  Can the owner of a table revoke SELECT from themselves? Yes, they
can. They can also give it back to themselves: 

CREATE ROLE michael;
SET ROLE michael;
CREATE TABLE michael_table (i INTEGER);
REVOKE SELECT ON michael_table FROM PUBLIC, michael;
SELECT * FROM michael_table;
ERROR:  permission denied for table michael_table
GRANT SELECT ON michael_table TO michael;
SELECT * FROM michael_table;
 i
---
(0 rows)

So I'm curious if we can have the same idea for ADMIN of a role?  The owner can revoke the role from themselves, and
theycan also grant it back.  Would that be acceptable? 

> Perhaps instead of starting from the 'owner' concept, we start from the
> question about the kinds of things we want roles to be able to do and
> perhaps that will help inform the terminology.
>
> - Create new roles
> - Drop an existing role
> - Drop objects which belong to a role
> - Lock existing roles
> - Change/reset the PW of existing roles
> - Give roles to other roles
> - Revoke access to some roles from other roles
> - Give select role attributes to a role
> - Revoke role attributes from a role
> - Traditional role-based access control (group memberships, SET ROLE)

I agree we want the ability to do these things, and not as a single CREATEROLE privilege, but separable.  The pre-v8
patchwas separating only one who the role owner was, but v8 is attempting to separate these further, and I think that's
theright way to go. 

> Certain of the above are already covered by the existing role membership
> system and with the admin option, though there's definitely an argument
> to be made as to if that is as capable as we'd like it to be (there's no
> way to, today at least, GRANT *just* the admin option, for example, and
> maybe that's something that it would actually be sensible to support).

I think the ADMIN stuff *would* be the way to go, but for it's weird self-administration feature.  That to me seems to
killthe idea.  What do you think? 

> Perhaps there is a need to have a user who has all of the above
> capabilities and maybe that would be an 'owner' or 'manager', but as I
> tried to illustrate above, there's definitely use-cases for giving
> a role only some of the above capabilities rather than all of them
> together at once.

I'm using the terms "owner"/"manager" without regard for whether they have all those abilities or just some of them.
However,I think these terms don't apply for just the traditional ADMIN option on the role.  In that case, calling it
"ownership"or "managership" is inappropriate. 

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