Re: [PATCHES] Roles - SET ROLE Updated - Mailing list pgsql-hackers

I wrote:
> ... the implication of that is that you cannot drop any of
> your "login ID"'s privileges by doing SET ROLE, which surely is not
> the intended behavior (else you might as well not have SET ROLE at all;
> the only possible use of SET ROLE is to *restrict* your privileges,
> since any role you can become represents privileges you'd have anyway
> without SET ROLE).  So I'm pretty unconvinced that the spec is being
> self-consistent here.

After some further study I see where the disconnect is coming from:
what we've implemented isn't quite what the spec has in mind.  Look
at SQL99 4.31.4:
        4.31.4  Security model definitions
        The set of applicable roles for an <authorization identifier>        consists of all roles defined by the role
authorizationdescriptors        whose grantee is that <authorization identifier> or PUBLIC together        with all
otherroles they contain.
 
        The set of user privileges for a <user identifier> consists of all        privileges defined by the privilege
descriptorswhose grantee is        either that <user identifier> or PUBLIC.
 
        The set of role privileges for a <role name> consists of all        privileges defined by the privilege
descriptorswhose grantee is        either that <role name>, PUBLIC, or one of the applicable roles of        that <role
name>.

What this says is that when a role A is a member of another role B, A
automatically has all of B's privileges.  But when a user U is a member
of role R, U does *not* have R's privileges automatically.  What he has
is the right to do SET ROLE R, after which he has R's privileges in
addition to his own (see the rest of 4.31.4).

This is ... um ... a pretty bizarre way of looking at security.
U can in fact do whatever his roles allow him to do, he just needs to
say "Mother may I?" first.  I suppose the fact that the spec only allows
SET ROLE at the outer level (outside any transaction) provides some
veneer of security against Trojan-horse functions, but it sure looks
lame.

But anyway, it seems that the spec sees SET ROLE as an operation that
gets you additional privileges, not as an operation that restricts your
privileges.

I don't think we can possibly emulate this definition unless we make
some pretty fundamental changes in the way the ROLE patch works.
In particular, is_member_of_role isn't in general the right way to
check applicability of privileges.
        regards, tom lane