Thread: question on row level security
The new row level security feature in 9.5 looks great. I guess its designed around the need to restrict access based on the current database user (current_user) where this maps to a database user. But most applications now access the database using an application user and manages data for the applications multiple users (probably with each user being a row in a USERS table somewhere). Is there any way to "inject" the application user so that this can be used in a RLS check? e.g. conceptually: set app_user 'john'; select * from foo; where the select * is restricted by a RLS check that includes 'john' as the app_user. Of course custom SQL could be generated for this, but it would be safer if it could be handled using RLS. Any ways to do this? Tim
The new row level security feature in 9.5 looks great.
I guess its designed around the need to restrict access based on the current database user (current_user) where this maps to a database user.
But most applications now access the database using an application user and manages data for the applications multiple users (probably with each user being a row in a USERS table somewhere).
Is there any way to "inject" the application user so that this can be used in a RLS check?
e.g. conceptually:
set app_user 'john';
select * from foo;
where the select * is restricted by a RLS check that includes 'john' as the app_user.
Of course custom SQL could be generated for this, but it would be safer if it could be handled using RLS.
Any ways to do this?
Does this address your concerns?
"""
The session_user is normally the user who initiated the current database connection; but superusers can change this setting with SET SESSION AUTHORIZATION. The current_user is the user identifier that is applicable for permission checking. Normally it is equal to the session user, but it can be changed with SET ROLE. It also changes during the execution of functions with the attribute SECURITY DEFINER. In Unix parlance, the session user is the "real user" and the current user is the "effective user".
"""
RLS uses "current_user" when performing checks.
David J.
On 12/30/2015 08:58 AM, Tim Dudgeon wrote: > The new row level security feature in 9.5 looks great. > I guess its designed around the need to restrict access based on the > current database user (current_user) where this maps to a database user. > But most applications now access the database using an application user > and manages data for the applications multiple users (probably with each > user being a row in a USERS table somewhere). > Is there any way to "inject" the application user so that this can be > used in a RLS check? > e.g. conceptually: > > set app_user 'john'; > select * from foo; > > where the select * is restricted by a RLS check that includes 'john' as > the app_user. > Of course custom SQL could be generated for this, but it would be safer > if it could be handled using RLS. > > Any ways to do this? User name maps?: http://www.postgresql.org/docs/9.5/interactive/auth-username-maps.html This still results in an external user becoming a database user. From there you can set up users as members of larger roles, i.e accounting, hr, etc to manage access, or not. > > Tim > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 30/12/2015 17:19, David G. Johnston wrote:
It might, but does it mean that that user (the app_user in my original question) still has to be a regular database user (e.g. one who has a database account and can connect to the database)? This is what I want to avoid.
Tim
The new row level security feature in 9.5 looks great.
I guess its designed around the need to restrict access based on the current database user (current_user) where this maps to a database user.
But most applications now access the database using an application user and manages data for the applications multiple users (probably with each user being a row in a USERS table somewhere).
Is there any way to "inject" the application user so that this can be used in a RLS check?
e.g. conceptually:
set app_user 'john';
select * from foo;
where the select * is restricted by a RLS check that includes 'john' as the app_user.
Of course custom SQL could be generated for this, but it would be safer if it could be handled using RLS.
Any ways to do this ? Does this address your concerns? """The session_user is normally the user who initiated the current database connection; but superusers can change this setting with SET SESSION AUTHORIZATION. The current_user is the user identifier that is applicable for permission checking. Normally it is equal to the session user, but it can be changed with SET ROLE. It also changes during the execution of functions with the attribute SECURITY DEFINER. In Unix parlance, the session user is the "real user" and the current user is the "effective user"."""RLS uses "current_user" when performing checks.David J.
It might, but does it mean that that user (the app_user in my original question) still has to be a regular database user (e.g. one who has a database account and can connect to the database)? This is what I want to avoid.
Tim
On 12/30/2015 08:58 AM, Tim Dudgeon wrote:
> e.g. conceptually:
>
> set app_user 'john';
> select * from foo;
>
> where the select * is restricted by a RLS check that includes 'john' as
> the app_user.
> Of course custom SQL could be generated for this, but it would be safer
> if it could be handled using RLS.
>
> Any ways to do this?
Something like this:
8<--------------------------
CREATE USER application;
CREATE TABLE t1 (id int primary key, f1 text, app_user text);
INSERT INTO t1 VALUES(1,'a','bob');
INSERT INTO t1 VALUES(2,'b','alice');
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
CREATE POLICY P ON t1 USING (app_user =
current_setting('app_name.app_user'));
GRANT SELECT ON t1 TO application;
SET SESSION AUTHORIZATION application;
regression=> SET app_name.app_user = 'bob';
SET
regression=> SELECT * FROM t1;id | f1 | app_user
----+----+---------- 1 | a | bob
(1 row)
regression=> SET app_name.app_user = 'alice';
SET
regression=> SELECT * FROM t1;id | f1 | app_user
----+----+---------- 2 | b | alice
(1 row)
regression=> SET app_name.app_user = 'none';
SET
regression=> SELECT * FROM t1;id | f1 | app_user
----+----+----------
(0 rows)
8<--------------------------
HTH,
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
On Wed, Dec 30, 2015 at 05:28:13PM +0000, Tim Dudgeon wrote: > > The new row level security feature in 9.5 looks great. > > I guess its designed around the need to restrict access based on > > the current database user (current_user) where this maps to a > > database user. > > But most applications now access the database using an application > > user and manages data for the applications multiple users > > (probably with each user being a row in a USERS table somewhere). > > Is there any way to "inject" the application user so that this can > > be used in a RLS check? > > e.g. conceptually: > > > > set app_user 'john'; > > select * from foo; > > > > where the select * is restricted by a RLS check that includes > > 'john' as the app_user. > > Of course custom SQL could be generated for this, but it would be > > safer if it could be handled using RLS. > > > > Any ways to do this You could store a session cookie (say, the app_user) into a table and have the RLS policy refer to that, no ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 30/12/2015 17:32, Joe Conway wrote:
> On 12/30/2015 08:58 AM, Tim Dudgeon wrote:
>> e.g. conceptually:
>>
>> set app_user 'john';
>> select * from foo;
>>
>> where the select * is restricted by a RLS check that includes 'john' as
>> the app_user.
>> Of course custom SQL could be generated for this, but it would be safer
>> if it could be handled using RLS.
>>
>> Any ways to do this?
> Something like this:
>
> 8<--------------------------
> CREATE USER application;
>
> CREATE TABLE t1 (id int primary key, f1 text, app_user text);
> INSERT INTO t1 VALUES(1,'a','bob');
> INSERT INTO t1 VALUES(2,'b','alice');
> ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
> CREATE POLICY P ON t1 USING (app_user =
> current_setting('app_name.app_user'));
> GRANT SELECT ON t1 TO application;
>
> SET SESSION AUTHORIZATION application;
>
> regression=> SET app_name.app_user = 'bob';
> SET
> regression=> SELECT * FROM t1;
> id | f1 | app_user
> ----+----+----------
> 1 | a | bob
> (1 row)
>
> regression=> SET app_name.app_user = 'alice';
> SET
> regression=> SELECT * FROM t1;
> id | f1 | app_user
> ----+----+----------
> 2 | b | alice
> (1 row)
>
> regression=> SET app_name.app_user = 'none';
> SET
> regression=> SELECT * FROM t1;
> id | f1 | app_user
> ----+----+----------
> (0 rows)
>
> 8<--------------------------
>
> HTH,
>
> Joe
>
Looks like that's what I need.
I'll give it a try.
Thanks
Tim
On 12/30/2015 08:58 AM, Tim Dudgeon wrote:
> e.g. conceptually:
>
> set app_user 'john';
> select * from foo;
>
> where the select * is restricted by a RLS check that includes 'john' as
> the app_user.
> Of course custom SQL could be generated for this, but it would be safer
> if it could be handled using RLS.
>
> Any ways to do this?
Something like this:
8<--------------------------
CREATE USER application;
CREATE TABLE t1 (id int primary key, f1 text, app_user text);
INSERT INTO t1 VALUES(1,'a','bob');
INSERT INTO t1 VALUES(2,'b','alice');
ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
CREATE POLICY P ON t1 USING (app_user =
current_setting('app_name.app_user'));
GRANT SELECT ON t1 TO application;
SET SESSION AUTHORIZATION application;
regression=> SET app_name.app_user = 'bob';
SET
regression=> SELECT * FROM t1;
id | f1 | app_user
----+----+----------
1 | a | bob
(1 row)
regression=> SET app_name.app_user = 'alice';
SET
regression=> SELECT * FROM t1;
id | f1 | app_user
----+----+----------
2 | b | alice
(1 row)
regression=> SET app_name.app_user = 'none';
SET
regression=> SELECT * FROM t1;
id | f1 | app_user
----+----+----------
(0 rows)
8<--------------------------
HTH,
Joe
I think an example like this, emphasizing the use of something other than current_user, should be considered for the documentation @
David J.