Re: [PATCHES] Roles - SET ROLE Updated

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> Am Donnerstag, 21. Juli 2005 22:55 schrieb Tom Lane:
> > 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.
>
> In some circles, this is considered the standard behavior of role security
> models. (There is a NIST standard somewhere.) It allows (with additional
> work) dynamic separation of concerns, namely that you could be a member of
> roles A and B but cannot use both at the same time. This is admittedly a
> fairly advanced feature, but should nevertheless be kept in mind.

It sounds like this is essentially if 'SET ROLE all;' is allowed or not.
If you disallow 'SET ROLE all;' (and therefore not do it on session
start) then you would get this behaviour. I certainly see that as a
reasonable option though I think we'd want to allow 'SET ROLE all;' for
backwards compatibility to group-based systems.

This doesn't change that even after a SET ROLE <role>; you would have
the permissions available via CURRENT_USER.

Thinking about it a bit more, in our context a 'SET ROLE all;' is really
a 'SET ROLE <login-role-name>;', which I'd think would therefore mean
that upon login in a 'default' setup we'd have CURRENT_USER and
CURRENT_ROLE set to the same thing.

My patch would need to be modified to add CURRENT_USER to the role-cache
when CURRENT_USER != CURRENT_ROLE, *after* the rest of the resolution,
of course. We'd then need an option for if the 'SET ROLE all;' is done
on connect or not, perhaps seperately an option saying if it's allowed
at all, and syntax modifications to all for 'SET ROLE all;', etc.

Thanks,

Stephen