|
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; |
