Re: [GENERAL] Recursive row level security policy

Hello again

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Freitag, 16. Dezember 2016 07:41
> To: 'Simon Charette' <charette.s@gmail.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Recursive row level security policy
>
> Hello Simon
>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Simon
> > Charette
> > Sent: Freitag, 16. Dezember 2016 07:02
> > To: Charles Clavadetscher <clavadetscher@swisspug.org>
> > Cc: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] 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@swisspug.org>:
> > > Hello
> > >
> > >> -----Original Message-----
> > >> From: pgsql-general-owner@postgresql.org
> > >> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Simon
> > >> Charette
> > >> Sent: Freitag, 16. Dezember 2016 06:15
> > >> To: pgsql-general@postgresql.org
> > >> Subject: [GENERAL] 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@postgresql.org) To make changes to your subscription:
> > >> http://www.postgresql.org/mailpref/pgsql-general
> > >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general