Search Postgresql Archives

Re: Irreversible SET ROLE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>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.jeffries@xxxxxxxxxxxxx> 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux