> On Jan 31, 2022, at 10:50 AM, Stephen Frost <sfr...@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 existing features 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
problems if we "fixed" the part where a role can revoke ADMIN from others on
themselves. Whatever we have, whether we call it "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 leaves us with "ownership", perhaps by
another word? I only chose that word because it's what we use elsewhere, but
if we want to call it "managementship" and "manager" or whatever, that's fine.
I'm not to the point of debating the terminology just 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 showing up in the audit logs was
compelling. Even for those things the "owner"/"manager" has the rights to do,
we might want to 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
those 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
treats ownership 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
superuser for 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 revoke the superuser's right to do so.
It's not enough that a non-superuser-creator-role (henceforth, "manager") can
grant itself ADMIN on the created role. It also needs to be able to set
passwords, transfer object ownerships to/from the role, 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 difference that 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 always have implicit membership in
and ADMIN on roles they manage. I think that needs to be optional, and the
audit logs could 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
specifying in the CREATE ROLE command that the creator doesn't want those
privileges, etc. That requires some tinkering with the design, though, because
the permission to perform the ownership transfer to that other role would need
to be circumscribed to not give away other privileges, like the right to become
that other role, or the specification that the creator disavows certain
privileges over the created role might need to be something the creator could
get back by force with some 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 really see 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 really squirrelly. 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 something else?
> 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 they can 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 patch was separating only one who the
role owner was, but v8 is attempting to separate these further, and I think
that's the right 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 kill the 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
