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:
signature.asc
Description: OpenPGP digital signature
