Hello again > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Charles > Clavadetscher > Sent: Freitag, 16. Dezember 2016 07:41 > To: 'Simon Charette' <charette.s@xxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Recursive row level security policy > > 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... Forget this. It would not work anyway. > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
