Re: question on row level security

On Wed, Dec 30, 2015 at 10:32 AM, Joe Conway <mail(at)joeconway(dot)com> 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
>
>
​I think an example like this, emphasizing the use of something other than
current_user, should be considered for the documentation @

http://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html

David J.