Re: [PATCHES] Roles - SET ROLE Updated

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 authorization descriptors
whose grantee is that <authorization identifier> or PUBLIC together
with all other roles they contain.

The set of user privileges for a <user identifier> consists of all
privileges defined by the privilege descriptors whose 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 descriptors whose 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