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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > 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. 

I agree that those are concerns that need to be considered, though I'm
more concerned about the SQL compliance and less about backwards
compatibility in this case.  For one thing, I'm afraid that we're not as
compliant as we really should be and that should really drive us to make
change here anyway, to get closer to what the spec calls for.

> 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. 

Yeah, didn't mean to imply that those were great ideas or that I was
particularly advocating for them, but just to bring up some other ideas
to try and get more thought going into this.

> > 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. 

Glad to hear that.

> > 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'sthat "bob" can drop *specific* roles, and for that, there has to be some kind of dependency tracked between "bob"
andthose other 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. 

I agree that role attributes isn't a good approach and that we should be
moving away from it.

I'm less sure that the existance of REASSIGN OWNED for schemas and
tables and such should be the driver for what this capability of one
role being able to drop another role needs to be called.

> > 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. 

That description itself really makes me wonder about the sense of what
was proposed.  Specifically "existing systems that use superuser for
role management" doesn't make me picture a system where this manager
role has any need to run SELECT statements against the tables created by
the role that it created- SELECT'ing data from tables isn't in the
purview of 'role management' (and before someone complains that pg_dump
is part of this, I wouldn't call running pg_dump role management but
rather data export or, used very loosely, 'backup').  To the end, I push
back with: what exactly is this existing superuser doing that's role
management?  The specific use-case, not just 'role management'.  What
Joshua outlined was a reasonably defined use-case and that's what I'm
trying to get at here.

> 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 appreciate that there needs to be a role who has certain rights over
other roles and that those rights can't be revoked by the role.  The
right to grant ADMIN on a create role is, itself, a right and what I was
suggesting is that it could be one that the created role isn't able to
revoke.  I disagree that the only possible role that could create some
other role must necessarily be able to be essentially superuser when it
comes to that created role.  I pointed out exactly the use-case where
that isn't the case and nothing here has said anything to refute the
existance of that use-case but seems to instead just focus on this idea
that we must have a 'mini 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.

I'd like to also move in a direction where implicit membership in and
ADMIN rights on the role is optional and potentially not even something
that the creating role is able to grant themselves- though *some* role
would need that ability and, ideally, it would be one that can be
granted out individually without being a full superuser.

> > 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 ...? 

If it's not relatively unprivileged regarding the created role then it's
not the ability which I outlined and therefore doesn't solve the
described use-case.  I don't really feel that making it possible for the
creating role to give up those rights actually solves for the attack
vector that is someone gaining access to the creating role's access,
which is what we're talking about trying to address by having a separate
role whose only ability is to create roles which it then isn't able to
become or otherwise impact.

> > 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. 

Except that at least in the case we're contemplating, it's not desired
for the creator of the role to have absolute authority over the created
role.  That's a pretty big difference between roles and objects.  That
we aren't seeing the distinction here is part of what I'm getting at
with the above paragraph.

> > (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"? 

Technically we were talking about PostgreSQL clusters that are just data
files and processes within computers when it came to primaries and
replicas, but other terms were used previously and we generally agreed
that we should probably move away from those terms.  Today REASSIGN
OWNED only talks about tables and views and other things which are
quite distinct from individuals.

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

Manager might be one, but as I try to get at below, what I'm thinking
about is a set of privileges that roles have and there isn't a concept
of "owner" or "manager" but rather "role X has S, T, V privileges on
roles A, B, C".  Conceptually perhaps we can consider a role that has
ALL privileges over another role to be that role's 'manager' or 'owner'
but we don't really even need to go into that once we've broken down the
privileges.

> >  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? 

That might be acceptable for the ADMIN privilege of a role itself though
I'm not sure if that's really all that distinct from ADMIN.

> > 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. 

Right, these should be separable, and I don't mean just the role
attributes but rather the above as distinct privileges.  Whereby a role
could have the right to create other roles but *not* have the right to
drop roles (either the one they created, or perhaps any others, or maybe
even to have some distinct set of roles that they're able to drop that's
different from the roles they created), as an example.

> > 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
tokill the idea.  What do you think? 

I don't think the self-administration stuff that we have for role ADMIN
rights is actually sensible and, while I know it's a backwards
compatibility break, it's something we should fix.

> > 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. 

I don't think it's sensible to have one term that means "all" and then
use that same term to also mean "only some".  That strikes me as
confusing and I don't know that we need to even have an explicit name
for the role that has 'all' of the rights or that we need to provide a
name for one that only has 'some' of them- they're just roles that have
certain privileges.  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.

THanks,

Stephen