From: | "Charles Clavadetscher" <clavadetscher(at)swisspug(dot)org> |
---|---|
To: | "'Simon Charette'" <charette(dot)s(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Recursive row level security policy |
Date: | 2016-12-16 05:57:10 |
Message-ID: | 026701d25761$44a1e2f0$cde5a8d0$@swisspug.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Charette | 2016-12-16 06:02:18 | Re: Recursive row level security policy |
Previous Message | Murtuza Zabuawala | 2016-12-16 05:45:42 | Re: CRM where pg is a first class citizen? |