Search Postgresql Archives

Recursive row level security policy

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux