Re: [PATCHES] Roles - SET ROLE Updated

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> 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).

Indeed, when I was looking through the information_schema views more
closely I was starting to realize something like this was going on.

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

Yeah, myself, and at least one other person that I recall asking after
this stuff, felt it was the opposite.

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

It is, and it isn't...  It's correct for checking role-privileges, just
not for user-privileges.  That is to say, is_member_of_role works for
when CURRENT_ROLE is set, and should be started based off of whatever
CURRENT_ROLE is set to.  If CURRENT_ROLE is not set then I don't think
it can be used.
Thanks,
    Stephen