Re: Irreversible SET ROLE
От | Bryn Jeffries |
---|---|
Тема | Re: Irreversible SET ROLE |
Дата | |
Msg-id | 7DAF466372B27747B8EA808BE5651FA561BB25A3@ex-mbx-pro-01 обсуждение исходный текст |
Ответ на | Re: Irreversible SET ROLE (Melvin Davidson <melvin6925@gmail.com>) |
Список | 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;
>> 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;
В списке pgsql-general по дате отправления: