Re: Irreversible SET ROLE

From: Bryn Jeffries <bryn(dot)jeffries(at)sydney(dot)edu(dot)au>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Irreversible SET ROLE
Date: 2014-12-02 00:52:00
Message-ID: 7DAF466372B27747B8EA808BE5651FA561BB25A3@ex-mbx-pro-01
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2014-12-02 01:39:22 Re: Irreversible SET ROLE
Previous Message Bryn Jeffries 2014-12-02 00:50:58 Re: Irreversible SET ROLE