Re: [PATCHES] Roles - SET ROLE Updated

[ getting back to this thread... ]

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> I think a better answer is to have a "rolinherit" flag in pg_authid,
>> which people can set "off" for spec compatibility or "on" for backwards
>> compatibility to the GROUP feature. In either setting, the permissions
>> given to a particular authid are clear from pg_authid and don't vary
>> depending on magic SET variables.

> This is nonstandard and not done in practice. Authorization changes
> being allowed by 'SET ROLE' is what the spec calls for. Not supporting
> that ability would be unfortunate and it seems there'd be no point to
> having 'SET ROLE' at all.

I think maybe you misunderstood what I was suggesting. The function of
the flag as I imagine it is:

* rolinherit = false: role does not automatically have the privileges of
roles it is a member of. It must do SET ROLE to gain the privileges of
a role it is a member of. (This emulates the spec behavior for users.)

* rolinherit = true: role has the privileges of all roles it is a member
of, without needing to do SET ROLE. (This handles the spec behavior for
roles, and is also needed for users when backwards compatibility with our
old behavior for groups is wanted, and also provides an approximate
equivalent to Oracle's SET ROLE ALL.)

If users have rolinherit = false and roles have rolinherit = true,
everything behaves per spec, except that I don't want to support the
aspect of the spec that says you can SET ROLE at the outer level and
still have the privileges of the SESSION_USER. I think SET ROLE should
effectively drop the SESSION_USER's privileges (except that subsequent
SET ROLE commands will be checked against the SESSION_USER's role
memberships, not the current effective role).

If both users and roles have rolinherit = true, we have a good emulation
of the old group-based behavior. For backwards compatibility we
probably have to have CREATE USER defaulting to rolinherit = true.
Is it sufficient to say "if you want the spec-compatible behavior you
always have to say CREATE USER ... NOINHERIT"? Since the spec doesn't
actually define a CREATE USER command, this is not a spec violation in a
technical sense. But people who are migrating towards using SET ROLE
might wish it defaulted to NOINHERIT. We could (either now or in a
future release) add a GUC variable to control the default, I suppose.

regards, tom lane