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

On Fri, Jun 10, 2022 at 4:36 PM Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
> I think this would create a conflict with what role-based access control
> normally means (outside of PostgreSQL specifically).  A role is a
> collection of privileges that you dynamically enable (e.g., with SET
> ROLE).  That corresponds to the NOINHERIT behavior.  It's also what the
> SQL standard specifies (which in term references other standards for
> RBAC).  The INHERIT behavior basically emulates the groups that used to
> exist in PostgreSQL.

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.

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. What is
really needed here is to have the grant of 'paul' be NOINHERIT and the
grant of 'pg_execute_server_programs' be INHERIT, but there's no way
to do that presently. My proposal fixes that problem.

If you don't agree with that analysis, I'd like to know which part of
it seems wrong to you. To me, it seems like an open and shut case: a
grant of a predefined role, or of a group, should have the INHERIT
behavior. A grant of a login role could plausibly have either one,
depending on user preferences. As long as the INHERIT property is a
property of the role to which the permission is granted, you have
trouble as soon as you try to do one grant that should have INHERIT
behavior and another that should have NOINHERIT behavior targeting the
same role.

> There are also possibly other properties you could derive from that
> distinction.  For example, you could  argue that something like
> pg_hba.conf should have group matching but not (noinherit-)role
> matching, since those roles are not actually activated all the time.
>
> There are also more advanced concepts like roles that are mutually
> exclusive so that you can't activate them both at once.

I don't see how my proposal makes any of that any harder than it is
today. Or any easier, either. Seems pretty much orthogonal.

> Now, what PostgreSQL currently implements is a bit of a mess that's a
> somewhat incompatible subset of all that.  But you can basically get
> those standard behaviors somehow.  So I don't think we should break this
> and go into a totally opposite direction.

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.

-- 
Robert Haas
EDB: http://www.enterprisedb.com