Re: [PATCHES] Roles - SET ROLE Updated

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

Essentially the union behavior is what the spec seems to say- except
that only one or the other is valid inside a SECURITY DEFINER, as I
understand it. So, you make everything do the union, but when you go
into a SECURITY DEFINER function you set the one-not-set to NULL and
handle that correctly in the union.

I'm not advocating allowing SET ROLE inside a function, no. Again, this
is more about the spec than an actual use-case that I have for it, so we
can ignore it until someone with a more concrete problem with it comes
along.

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

Right, I would expect it to drop privileges when rolinherit = true. The
second issue is one reason I don't particularly care for locking it into
the catalog- it means we're building the system in such a way as to be
unable to support what Oracle (at least) does today. If we end up
needing to support it later, or wanting to, perhaps because the spec
follows Oracle's lead and adds SET ROLE ALL, then we've got alot that
would need to be changed because things have become dependent on the
catalog directly.

Otherwise, I think your proposal is fine. :)

Thanks,

Stephen