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
