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

Bryn Jeffries <bryn.jeffries@sydney.edu.au> writes:

I have a question about preventing SET ROLE from being reset within a session.

You can't; per SQL standard, SET ROLE NONE is supposed to do exactly that.

I think you might be able to do something with invoking untrusted code
inside a SECURITY DEFINER function. That context disables both SET ROLE
and SET SESSION AUTHORIZATION. I would not want to bet a lot on that
being bulletproof, however.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane wrote

I have a question about preventing SET ROLE from being reset within a session.

You can't; per SQL standard, SET ROLE NONE is supposed to do exactly that.

I think you might be able to do something with invoking untrusted code
inside a SECURITY DEFINER function. That context disables both SET ROLE
and SET SESSION AUTHORIZATION. I would not want to bet a lot on that
being bulletproof, however.

Me neither. But out of interest, do you know what the intent was for the SESSION and LOCAL modifiers for SET ROLE?
Both seem to do nothing more than regular SET ROLE, but are PostgreSQL extensions.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson wrote:

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.

Unfortunately that's not really compatible with the approach I need, which is
akin to Proxy Authentication (see http://dba.stackexchange.com/questions/77704/proxy-authentication-for-postgesql) whereby all connections are created a single account, which has no access to any tables:
CREATE ROLE webuser NOINHERIT LOGIN PASSWORD 'webuserpass';
GRANT CONNECT ON DATABASE mydb TO webuser;

User accounts must inherit from role to which access to all views and accessible tables is granted:
CREATE ROLE mydbuser NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mydbuser;

All users have accounts of the form:
CREATE ROLE userX NOLOGIN INHERIT IN ROLE mydbuser ROLE webuser;

So a connection is made as webuser (using the corresponding password) and
then the role is changed by immediately calling:
SET ROLE userX;

What I'd like is to be able to prevent userX from switching role, e.g
SET ROLE userY;

Bryn Jeffries <bryn.jeffries@sydney.edu.au> writes:

Tom Lane wrote

I think you might be able to do something with invoking untrusted code
inside a SECURITY DEFINER function. That context disables both SET ROLE
and SET SESSION AUTHORIZATION. I would not want to bet a lot on that
being bulletproof, however.

Me neither. But out of interest, do you know what the intent was for the SESSION and LOCAL modifiers for SET ROLE?
Both seem to do nothing more than regular SET ROLE, but are PostgreSQL extensions.

Those are just there because they're there for any SET <variable> command.
LOCAL means "set just for the duration of this transaction". SESSION is
only a noise word IIRC, ie, it lets you explicitly specify the default
behavior.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general