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@xxxxxxxxxxxxx>: > 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
