Re: question on row level security - Mailing list pgsql-sql

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