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
