From: | "Charles Clavadetscher" <clavadetscher(at)swisspug(dot)org> |
---|---|
To: | "'Simon Charette'" <charette(dot)s(at)gmail(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Recursive row level security policy |
Date: | 2016-12-16 06:41:06 |
Message-ID: | 026a01d25767$669b56b0$33d20410$@swisspug.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello Simon
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Simon Charette
> Sent: Freitag, 16. Dezember 2016 07:02
> To: Charles Clavadetscher <clavadetscher(at)swisspug(dot)org>
> Cc: pgsql-general(at)postgresql(dot)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...
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(at)charles=# set role foo;
SET
charles(at)charles=> SELECT * FROM accounts;
ERROR: permission denied for relation accounts
charles(at)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(at)swisspug(dot)org>:
> > Hello
> >
> >> -----Original Message-----
> >> From: pgsql-general-owner(at)postgresql(dot)org
> >> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Simon
> >> Charette
> >> Sent: Freitag, 16. Dezember 2016 06:15
> >> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org) To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Charles Clavadetscher | 2016-12-16 06:46:04 | Re: Recursive row level security policy |
Previous Message | nidhi raina | 2016-12-16 06:07:39 | Is there a way to Send attachments with email using pgmail postgreSQl? |