From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Tim Dudgeon <tdudgeon(dot)ml(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: question on row level security |
Date: | 2015-12-30 17:32:49 |
Message-ID: | 56841541.6080409@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2015-12-30 17:37:15 | Re: question on row level security |
Previous Message | Adrian Klaver | 2015-12-30 17:28:31 | Re: question on row level security |