>The problem for me is that SET ROLE can be reversed with SET ROLE >NONE or RESET ROLE, so a user could set the role to access rows that >they should not be able to see.
*This is only partially true. While they can do SET ROLE NONE & RESET ROLE, they Cannot SET ROLE to a role they have not been granted.* *EG: GRANT ROLE some_role to some_user; So the key is only granting a role or group to a user they belong to, and no other.* On Mon, Dec 1, 2014 at 4:14 PM, Bryn Jeffries <bryn.jeffr...@sydney.edu.au> wrote: > Hi, > > I have a question about preventing SET ROLE from being reset within a > session. I'll give some context for my question, but please note that the > question is not restricted to the technologies (XWiki, Groovy) that I'm > using. > > I'm working with a PostgreSQL 9.3 database that is interfaced via JDBC > from an XWiki web application. The database has a number of views that > restrict access to rows depending upon the current $user. The $user is set > by taking the login name from XWiki and calling SET ROLE to this name. This > approach follows the advice in > > http://dba.stackexchange.com/questions/25357/choice-of-authentication-approach-for-financial-app-on-postgresql > and > > http://dba.stackexchange.com/questions/78353/set-role-via-parameterized-query > > At present the queries are fixed, and called from within methods within > compiled Java code. However, I would like to make it possible for users to > construct and execute their own queries by incorporating their own Groovy > code into a wiki page that uses a provided JDBC connection. This connection > would come from a factory method that would SET ROLE appropriately before > returning the Connection object. > > The problem for me is that SET ROLE can be reversed with SET ROLE NONE or > RESET ROLE, so a user could set the role to access rows that they should > not be able to see. What I would like is to be able to prevent the role > being changed for the rest of the session. I wondered whether this was the > intent of the SESSION modifier given in the documentation ( > http://www.postgresql.org/docs/9.3/static/sql-set-role.html) but this > currently appears to have no effect. So is there another way? > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
