Re: [GENERAL] Recursive row level security policy

Thanks a lot Joe, that seems to work!

I suppose this works because PostgreSQL cannot introspect the
get_owner_id procedure to detect it's querying the "accounts" table
and thus doesn't warn about possible infinite recursion?

Simon

2016-12-16 9:36 GMT-05:00 Joe Conway <mail@joeconway.com>:
> On 12/16/2016 01:02 AM, Simon Charette wrote:
>> 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.
>
> Perhaps:
>
> 8<--------------------------
> 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 FUNCTION get_owner_id(luser text) RETURNS integer AS $$
>  SELECT id FROM accounts WHERE name = luser
> $$ LANGUAGE sql STRICT STABLE SECURITY DEFINER;
>
> CREATE POLICY account_ownership ON accounts FOR SELECT
>     USING (owner_id = get_owner_id(current_user));
>
> CREATE ROLE foo;
> SET ROLE foo;
>
> SELECT * FROM accounts;
>  id | name | owner_id
> ----+------+----------
>   1 | foo  |        1
>   2 | bar  |        1
> (2 rows)
> 8<--------------------------
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>