Re: [PATCHES] Roles - SET ROLE Updated

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> [ getting back to this thread... ]

Happy to, was getting worried you'd forgotten or ignored it. ;)

> * 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).

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.

> 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.

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.

For my systems I expect to want to do 'rolinherit = true' generally but
I really don't like the idea that 'SET ROLE <a>' has no effect then.

Thinking about it a bit more I suppose I could live with it since it's
per-role and I tend to set up unprivileged accounts, which is where I'd
really be more concerned about 'SET ROLE <a>' working.  We should
probably issue a warning or something if my hypothosis on 'SET ROLE'
above is correct in the 'rolinherit = true' case so that people don't
get the wrong idea that they've dropped privileges in cases when they
actually havn't.

> 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.

Being able to control the default would be nice but I don't believe it
would be a requirement.  I would actually like to have a variable to
control if SESSION_USER privileges are kept across a SET ROLE or not,
though primairly to conform to the spec than expectation that I'd
personally use it much.
Thanks,
    Stephen