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;