Re: allowing for control over SET ROLE

Robert Haas:
> Beginning in
> e3ce2de09d814f8770b2e3b3c152b7671bcdb83f, the inheritance behavior of
> role-grants can be overridden for individual grants, so that some
> grants are inherited and others are not.

That's a great thing to have!

> However, there is no similar
> facility for controlling whether a role can SET ROLE to some other
> role of which it is a member. At present, if role A is a member of
> role B, then A can SET ROLE B, and that's it.
> 
> In some circumstances, it may be desirable to control this behavior.

+1

> rhaas=# grant oncall to oncallbot with inherit false, set false, admin true;

Looking at the syntax here, I'm not sure whether adding more WITH 
options is the best way to do this. From a user perspective WITH SET 
TRUE looks more like a privilege granted on how to use this database 
object (role). Something like this would be more consistent with the 
other GRANT variants:

GRANT SET ON ROLE oncall TO oncallbot WITH GRANT OPTION;

This is obviously not exactly the same as the command above, because 
oncallbot would be able to use SET ROLE directly. But as discussed, this 
is more cosmetic anyway, because they could GRANT it to themselves.

The full syntax could look like this:

GRANT { INHERIT | SET | ALL [ PRIVILEGES ] }
   ON ROLE role_name [, ...]
   TO role_specification [, ...] WITH GRANT OPTION
   [ GRANTED BY role_specification ]

With this new syntax, the existing

GRANT role_name TO role_specification [WITH ADMIN OPTION];

would be the same as

GRANT ALL ON role_name TO role_specification [WITH GRANT OPTION];

This would slightly change the way INHERIT works: As a privilege, it 
would not override the member's role INHERIT attribute, but would 
control whether that attribute is applied. This means:

- INHERIT attribute + INHERIT granted -> inheritance (same)
- INHERIT attribute + INHERIT not granted -> no inheritance (different!)
- NOINHERIT attribute  + INHERIT not granted -> no inheritance (same)
- NOINHERIT attribute  + INHERIT granted -> no inheritance (different!)

This would allow us to do the following:

GRANT INHERIT ON ROLE pg_read_all_settings TO seer_bot WITH GRANT OPTION;

seer_bot would now be able to GRANT pg_read_all_settings to other users, 
too - but without the ability to use or grant SET ROLE to anyone. As 
long as seer_bot has the NOINHERIT attribute set, they wouldn't use that 
privilege, though - which might be desired for the bot.

Similary, it would be possible for the oncallbot in the example above to 
be able to grant SET ROLE only - and not INHERIT.

I realize that there has been a lot of discussion about roles and 
privileges in the past year. I have tried to follow those discussions, 
but it's likely that I missed some good arguments against my proposal above.

Best

Wolfgang