Re: replacing role-level NOINHERIT with a grant-level option

On 13.06.22 20:00, Robert Haas wrote:
> I don't think this creates any more of a conflict than we've already
> got. In fact, I'd go so far as to say it resolves a problem that we
> currently have. As far as I can see, we are stuck with a situation
> where we have to support both the INHERIT behavior and the NOINHERIT
> behavior. Removing either one would be a pretty major compatibility
> break. And even if some people were willing to endorse that, it seems
> clear from previous discussions that there are people who like the
> NOINHERIT behavior and would object to its removal, and other people
> (like me!) who like the INHERIT behavior and would object to removing
> that. If you think it's feasible to get rid of either of these
> behaviors, I'd be interested in hearing your thoughts on that, but to
> me it looks like we are stuck with supporting both. From my point of
> view, the question is how to make the best of that situation.

I think we want to keep both.

> Consider a user who in general prefers the NOINHERIT behavior but also
> wants to use predefined roles. Perhaps user 'peter' is to be granted
> both 'paul' and 'pg_execute_server_programs'. If role 'peter' is set
> to INHERIT, Peter will be sad, because his love for NOINHERIT probably
> means that he doesn't want to exercise Paul's privileges
> automatically. However, he needs to inherit the privileges of
> 'pg_execute_server_programs' or they are of no use to him. Peter
> presumably wants to use COPY TO/FROM program to put data into a table
> owned by 'peter', not a table owned by 'pg_execute_server_programs'.
> If so, being able to SET ROLE to 'pg_execute_server_programs' is of no
> use to him at all, but inheriting the privilege is useful.

That's because our implementation of SET ROLE is bogus.  We should have 
a SET ROLE that is separate from SET SESSION AUTHORIZATION, where the 
current user can keep their current user-ness and additionally enable 
(non-inherited) roles.

> I don't think I'm proposing to break anything or go in a totally
> opposite direction from anything, and to be honest I'm kind of
> confused as to why you think that what I'm proposing would have that
> effect. As far as I can see, the changes that I'm proposing are
> upward-compatible and would permit easy migration to new releases via
> either pg_dump or pg_upgrade with no behavior changes at all. Some
> syntax would be a bit different on the new releases and that would
> unlock some new options we don't currently have, but there's no
> behavior that you can get today which you wouldn't be able to get any
> more under this proposal.

I'm mainly concerned that (AAIU), you propose to remove the current 
INHERIT/NOINHERIT attribute of roles.  I wouldn't like that.  If you 
want a feature that allows overriding that per-grant, maybe that's okay.