* Tom Lane ([EMAIL PROTECTED]) wrote: > Peter Eisentraut <[EMAIL PROTECTED]> 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
signature.asc
Description: Digital signature