Subject: Re: question on row level security
To: Tim Dudgeon <tdudgeon.ml@gmail.com>, pgsql-sql@postgresql.org
References: <56840D1A.8030203@gmail.com>
From: Joe Conway <mail@joeconway.com>
Message-ID: <56841541.6080409@joeconway.com>
Date: Wed, 30 Dec 2015 09:32:49 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.4.0
MIME-Version: 1.0
In-Reply-To: <56840D1A.8030203@gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="WPf3B52PkH25O1lIBUrDPoueJMlf7IaJL"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--WPf3B52PkH25O1lIBUrDPoueJMlf7IaJL
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

On 12/30/2015 08:58 AM, Tim Dudgeon wrote:
> e.g. conceptually:
>=20
> set app_user 'john';
> select * from foo;
>=20
> 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.
>=20
> 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 =3D
current_setting('app_name.app_user'));
GRANT SELECT ON t1 TO application;

SET SESSION AUTHORIZATION application;

regression=3D> SET app_name.app_user =3D 'bob';
SET
regression=3D> SELECT * FROM t1;
 id | f1 | app_user
----+----+----------
  1 | a  | bob
(1 row)

regression=3D> SET app_name.app_user =3D 'alice';
SET
regression=3D> SELECT * FROM t1;
 id | f1 | app_user
----+----+----------
  2 | b  | alice
(1 row)

regression=3D> SET app_name.app_user =3D 'none';
SET
regression=3D> SELECT * FROM t1;
 id | f1 | app_user
----+----+----------
(0 rows)

8<--------------------------

HTH,

Joe

--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


--WPf3B52PkH25O1lIBUrDPoueJMlf7IaJL
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (GNU/Linux)

iQIcBAEBAgAGBQJWhBVBAAoJEDfy90M199hlIpoQAJigs5M/KTyMXgvu0JUWwEBN
rO35Jzve1vg5u+npRxyxvEvfmxpwXnqOZaaL/Y04YItxTWLNoHFk42OLz7S4eFIa
4adOsfHNr5z4lev1u0QH3gtUckSNAXQ5zuHUp2x5tiTDkRCDcMAMFe2l6+kPe11/
xio+bP0OD5+xPO8t0qAWGqeJAuCT9VA/b+xOiG/hGamPUYQOOPZvJT7cI+QcYzaj
3WHbyNQRQxaJ5Ko4L4+9VEV0YUHfwBh5ti4n0vjLRh0ke+lFt3sMsyrX8KjmJhl+
4Y/DV/XHVPok/bbRc3lrBpcU4SiWrolMU42V1giySINw+Y+Qceo39WMxd3fSu9pZ
FocfS9RxPTBL+E8hSvt9Kx3H+fsIX5kmYbwji8w0QzBkhmBoGFrK6K/54tTXPUgu
xYWUpWwkif2E+jBTR/1YxRcVEMffG/84GdKe0s27yRrNlLPptz6lD67kn+Md5xB9
pHlRMrRWpWmCmJ0wPPvFRkBLmlsM5KasgWdkud3Sq0gbkpvda96VTsYssjhlhRoJ
XF4tUwwxfPXcRzeqXIp99aEflVapziCmWM+uOinX9Dgb3/gCOofFNeGUOOXM2qHB
1EcL4QYFlqSvQoF380Zz+aUzlggXMkslvXloNjhOeIM/XFu3j6s1iROfKLy/ZobP
1TuZupGntU+PSLOYg1Lv
=fhuU
-----END PGP SIGNATURE-----

--WPf3B52PkH25O1lIBUrDPoueJMlf7IaJL--