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.
