Hello Charles, Unfortunately this will only return accounts matching the current_user's name. I would expect "SET ROLE foo; SELECT name FROM accounts" to return "foo" and "bar" and not only "foo" like your proposed solution would do. Simon 2016-12-16 0:57 GMT-05:00 Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx>: > Hello > >> -----Original Message----- >> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Simon Charette >> Sent: Freitag, 16. Dezember 2016 06:15 >> To: pgsql-general@xxxxxxxxxxxxxx >> Subject: Recursive row level security policy >> >> Hello there, >> >> I'm not sure I'm posting to the appropriate mailing list so don't hesitate to redirect me to the appropriate one. >> >> I've been trying to setup a policy that allows "accounts" table rows to only be seen by their owner by using the >> current_user to compare them by name. >> >> Unfortunately it looks like I'm either missing something or there's a limitation in the current row level security >> implementation that prevents me from doing this. >> >> Here's the actual SQL to reproduce the issue: >> >> CREATE TABLE "accounts" ( >> "id" integer NOT NULL PRIMARY KEY, >> "name" varchar(50) NOT NULL UNIQUE, >> "owner_id" integer NOT NULL >> ); >> >> INSERT INTO accounts(id, name, owner_id) >> VALUES (1, 'foo', 1), (2, 'bar', 1), (3, 'baz', 3); >> >> GRANT SELECT ON accounts TO PUBLIC; >> >> ALTER TABLE accounts ENABLE ROW LEVEL SECURITY; >> >> CREATE POLICY account_ownership ON accounts FOR SELECT >> USING (owner_id = (SELECT id FROM accounts WHERE name = current_user)); > > I think that should be: > > CREATE POLICY account_ownership ON accounts FOR SELECT > USING (name = current_user); > > Regards > Charles > >> >> CREATE ROLE foo; >> SET ROLE foo; >> SELECT * FROM accounts; >> -- ERROR: infinite recursion detected in policy for relation "accounts" >> >> Is there any way to alter the "account_ownership" policy's USING clause to avoid this infinite recursion or a way to >> model my schema to prevent this from happening? >> >> Thank you for your time, >> Simon >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
