Re: [PATCHES] Roles - SET ROLE Updated

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> 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).

> I don't particularly like deviating from the spec in this regard (since
> I don't think it'd be all that hard to implement what the spec calls
> for), but it doesn't bother me that much.

The problem I have with the spec's way is that it creates a disconnect
between the privilege environment seen at the outer level and the
environment seen within SECURITY DEFINER functions --- unless you want
to allow SET ROLE to have the union behavior within SECURITY DEFINER
functions too, which I don't want to support (and it's not legal per
spec anyway to do SET ROLE inside a function).

> While I agree that this is what Oracle's SET ROLE ALL does initially,
> it's possible for a user to 'SET ROLE <a>' and drop the permissions
> given by the other roles in which the user is in. Will that still be
> possible with your proposed solution, or will doing 'SET ROLE <a>' have
> no effect when 'rolinherit = true'? That's really my main concern.

According to my proposal "SET ROLE x" would drop the user's privileges
and thus be a privilege restriction operation, never a privilege
addition operation, if the user has rolinherit = true. If we don't say
that SET ROLE drops the session user's privileges then indeed SET ROLE
would be a no-op when the session user has rolinherit = true...

regards, tom lane