agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Joe Conway <mail@joeconway.com>
To: Tim Dudgeon <tdudgeon.ml@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: question on row level security
Date: Wed, 30 Dec 2015 09:32:49 -0800
Message-ID: <56841541.6080409@joeconway.com> (raw)
In-Reply-To: <56840D1A.8030203@gmail.com>
References: <56840D1A.8030203@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Attachments:
[application/pgp-signature] signature.asc (835B, ../56841541.6080409@joeconway.com/2-signature.asc)
download
view thread (8+ messages) latest in thread
Message-ID: <56841541.6080409@joeconway.com>
Permalink: ../56841541.6080409@joeconway.com/
Also on: postgresql.org/message-id/56841541.6080409@joeconway.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, tdudgeon.ml@gmail.com
Subject: Re: question on row level security
In-Reply-To: <56841541.6080409@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox