> On Jan 31, 2022, at 8:53 AM, Stephen Frost <sfr...@snowman.net> wrote:
>
> Yeah, we do need to have a way to determine who is allowed to drop
> roles and role ownership seems like it's one possible approach to that.
Which other ways are on the table? Having ADMIN on a role doesn't allow you to
do that, but maybe it could? What else?
>> The main idea here is that having CREATEROLE doesn't give you ADMIN on
>> roles, nor on role attributes. For role attributes, the syntax has been
>> extended. An excerpt from the patch's regression test illustrates some of
>> that concept:
>>
>> -- ok, superuser can create a role that can create login replication users,
>> but
>> -- cannot itself login, nor perform replication
>> CREATE ROLE regress_role_repladmin
>> CREATEROLE WITHOUT ADMIN OPTION -- can create roles, but cannot give
>> it away
>> NOCREATEDB WITHOUT ADMIN OPTION -- cannot create db, nor give it away
>> NOLOGIN WITH ADMIN OPTION -- cannot log in, but can give it away
>> NOREPLICATION WITH ADMIN OPTION -- cannot replicate, but can give it
>> away
>> NOBYPASSRLS WITHOUT ADMIN OPTION; -- cannot bypassrls, nor give it away
>>
>> -- ok, superuser can create a role with CREATEROLE but restrict give-aways
>> CREATE ROLE regress_role_minoradmin
>> NOSUPERUSER -- WITHOUT ADMIN OPTION is implied
>> CREATEROLE WITHOUT ADMIN OPTION
>> NOCREATEDB WITHOUT ADMIN OPTION
>> NOLOGIN WITHOUT ADMIN OPTION
>> NOREPLICATION -- WITHOUT ADMIN OPTION is implied
>> NOBYPASSRLS -- WITHOUT ADMIN OPTION is implied
>> NOINHERIT WITHOUT ADMIN OPTION
>> CONNECTION LIMIT NONE WITHOUT ADMIN OPTION
>> VALID ALWAYS WITHOUT ADMIN OPTION
>> PASSWORD NULL WITHOUT ADMIN OPTION;
>
> Right, this was one of the approaches that I was thinking could work for
> managing role attributes and it's very similar to roles and the admin
> option for them. As I suggested at least once, another possible
> approach could be to have login users not be able to create roles but
> for them to be able to SET ROLE to a role which is able to create roles,
> and then, using your prior method, only allow the attributes which that
> role has to be able to be given to other roles.
I'm not sure how that works. If I have a group named "administrators" which as
multiple attributes like BYPASSRLS and such, and user "stephen" is a member of
"administrators", then stephen can not only give away bypassrls to new users
but also has it himself. How is that an improvement? (I mean this as a
question, not as criticism.)
> That essentially makes
> a role be a proxy for the per-attribute admin options. There's pros and
> cons for each approach and so I'm curious as to which you feel is the
> better approach? I get the feeling that you're more inclined to go with
> the approach of having an admin option for each role attribute (having
> written this WIP patch) but I'm not sure if that is because you
> contempltaed both and felt this was better for some reason or more
> because I wasn't explaining the other approach very well, or if there
> was some other reason.
I need more explanation of the other option you are contemplating. My
apologies if I'm being thick-headed.
>> -- fail, having CREATEROLE is not enough to create roles in privileged roles
>> SET SESSION AUTHORIZATION regress_role_minoradmin;
>> CREATE ROLE regress_nosuch_read_all_data IN ROLE pg_read_all_data;
>> ERROR: must have admin option on role "pg_read_all_data"
>
> I would say not just privileged roles, but any roles that the user
> doesn't have admin rights on.
Yes, that's how it works. But this portion of the test is only checking the
interaction between CREATEROLE and built-in privileged roles, hence the comment.
>> Whether "WITH ADMIN OPTION" or "WITHOUT ADMIN OPTION" is implied hinges on
>> whether the role is given CREATEROLE. That hackery is necessary to preserve
>> backwards compatibility. If we don't care about compatibility, I could
>> change the patch to make "WITHOUT ADMIN OPTION" implied for all attributes
>> when not specified.
>
> Given the relative size of the changes we're talking about regarding
> CREATEROLE, I don't really think we need to stress about backwards
> compatibility too much.
Yeah, I'm leaning pretty strongly that way, too.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
