Hello Simon > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Simon Charette > Sent: Freitag, 16. Dezember 2016 07:02 > To: Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Recursive row level security policy > > 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. True. I did oversee the real target. The problem is that the policy for select on the table will be cheked each time a select is performed. So having a select in the using condition will check the policy again, and so on. I am not sure how to solve this with policies and I assume that somebody else may come up with an idea. One thing I can think of is to check the condition in a security definer function where you temporarily disable row level security. But this is quite a nasty thing to do... A workaround would be the "old way" using views: CREATE VIEW public.v_accounts AS SELECT * FROM accounts WHERE owner_id = (SELECT owner_id FROM accounts WHERE name = CURRENT_USER); REVOKE SELECT ON accounts FROM public; GRANT SELECT ON v_accounts TO public; charles@charles=# set role foo; SET charles@charles=> SELECT * FROM accounts; ERROR: permission denied for relation accounts charles@charles=> select * from v_accounts ; id | name | owner_id ----+------+---------- 1 | foo | 1 2 | bar | 1 (2 rows) Instead of granting select on the table you only grant it on the view. Hope this helps. Bye Charles > > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
