Re: Role incompatibilities

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > My concern here is to arrive at a standards conforming role system. Clearly
> > we've established that the current one doesn't do it. Moreover, I'm now
> > convinced that some aspects of the current implementation arose from an
> > attempt to implement the standard but failed because of misunderstandings.
>
> No, the current implementation is a compromise between exact standards
> compatibility and backwards compatibility with our historical "groups"
> behavior. I'm not really prepared to toss the latter overboard.

I think what Peter would want is for us to track CURRENT_USER (the role
who logged in) and to always add the CURRENT_USER to the list of roles
available after a 'SET ROLE'. That would at least get us a little
closer to the spec though I'm not sure if it matches what other DBs do.
I'll try to check on that sometime this weekend.

I know that Oracle, at least, gives you all roles which have been
granted to you on login on at least the system I've got access to. If
you then 'set role' to a given role you *are* dropping privileges, not
adding them. I don't know if there's a way to configure Oracle for the
standards-compliant method. I'm also not sure what other databases do.

The noinherit for 'user' roles is more difficult, of course. We can
tell people to set noinherit on the roles that can log in but I'm not
sure Peter's happy with that. We could have a server option of
"sql_user_noinherit" or some such which automatically set noinherit for
roles which have the 'login' attribute and inherit for those without.
This would create the artificial distinction between users and roles
which the specification has but makes for some very odd problems- do you
then disallow roles with 'login' from being granted to others? What
about grants done prior to the option being set, ignore them? What if
the admin sets 'nologin' for such a role, do the old grants suddenly
come back?

Anyhow, I'm really not sure it's the best approach in this case to try
to follow the spec to the letter and not just because it breaks
backwards compatibility for us but also because I don't think any
actually implemented database follows it exactly either and as such the
spec hasn't been that well thought out in this area.

Thanks,

Stephen