Re: Irreversible SET ROLE

>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(dot)jeffries(at)sydney(dot)edu(dot)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.