Re: Role incompatibilities

* Peter Eisentraut (peter_e@gmx.net) wrote:
> Am Samstag, 25. März 2006 17:27 schrieb Stephen Frost:
> > 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.
>
> This is sort of but not really what I meant.  What I think would work rather
> elegantly is having both a current_user and a current_role and having the
> current_role set to current_user on login.  (The SQL standard would require
> the current role to be empty at login.)  Then you get exactly the traditional
> group behavior.  You can also use SET ROLE to set the current role (but not
> the current user) to some other permissible role.  This will give you the
> applicable privileges of your current user and that new role.  The nice thing
> about this is that this gives you both traditional PostgreSQL behavior by
> default and completely SQL conforming behavior if you ask for it.  (If we are
> really ambitious, we can let people put their preferred current_role into
> their session startup settings to give them exact SQL standard behavior by
> default without requiring SET ROLE NONE first.)  Note that we don't need any
> inherit feature at all for any of that, nor does this require separate user
> and role entities.

Well, at first blush it would seem like you could use GetSessionUserId()
for the 'CURRENT_USER' and modify roles_has_privs_of() to automatically
seed the roles_list with the 'CURRENT_USER'.  This has the initial issue
that SECURITY DEFINER functions would end up including the permissions
of the calling user unless some additional checking is done, I believe.
I'm also pretty sure the SQL spec considers 'SESSION_USER' different
from 'CURRENT_USER' in some situations though I'm not sure which in
Postgres our current SESSION/CURRENT_USER arguments are closer to.
You'd also have to make sure you don't recurse down from the
'CURRENT_USER' if it's from the seed because otherwise SET ROLE would
end up being a no-op.  I guess instead of seeding it, you could add it
on at the end provided it wasn't already in the list.  I'm also
concerned about questions along the lines of 'who has permissions on
what' and that being stable/sensible.

I certainly don't feel we should get rid of 'inherit' though as that's a
very useful feature.  I'm also not personally terribly interested in
trying to implement this, I'm mostly postulating some concrete changes
which could be made to give others the opportunity to find issues with
them.  I also feel that we really can't do what the SQL spec requires
without seperating users from roles as I don't beleve you're supposed
to be able to grant users to roles or set role to a user.  These kinds
of issues make me that much less interested in trying to implement the
spec verbatim.

I'd certainly be much happier to listen to concrete suggestions on
specific code changes or even better, patches to implement those
changes...  Just as I don't feel the spec has been entirely thought
through because it hasn't actually been fully implemented in this
regard, I don't feel these changes and behaviors are really being
defined well enough to discuss very well and consider the ramifications
of them.
Thanks,
    Stephen