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.

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
>