From: | Simon Charette <charette(dot)s(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Recursive row level security policy |
Date: | 2016-12-16 05:14:51 |
Message-ID: | CALgFm5hwf=HRFCcs7ZUyB6oNpmRRthVH0YmFm=Z3HxvYxCCFEQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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));
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
From | Date | Subject | |
---|---|---|---|
Next Message | Murtuza Zabuawala | 2016-12-16 05:45:42 | Re: CRM where pg is a first class citizen? |
Previous Message | John DeSoi | 2016-12-16 03:28:03 | Re: CRM where pg is a first class citizen? |