Re: [GENERAL] Recursive row level security policy - Mailing list pgsql-general

From Joe Conway
Subject Re: [GENERAL] Recursive row level security policy
Date
Msg-id 09803781-be99-3993-962a-aeaf3af06f5d@joeconway.com
Whole thread Raw
In response to Re: [GENERAL] Recursive row level security policy  (Simon Charette <charette.s@gmail.com>)
Responses Re: [GENERAL] Recursive row level security policy
List pgsql-general
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


Attachment

pgsql-general by date:

Previous
From: pillul@freenet.de
Date:
Subject: [GENERAL] Postgres runs under other user/group, update crashs installation
Next
From: Thomas.Deboben.ext@rohde-schwarz.com
Date:
Subject: Re: [GENERAL] Windows installation - could not connect to server:Connection refused (0x0000274D/10061)