Awesome! Though having read the explanation I can't think of a secure way to use this in a multi-tenanted app, as once the DB user has permission to execute "SET ROLE", it's got the same security issues with accessing other user data as the current "WHERE company_id=3" style of separation? Would it help this use-case, and does it add security?
DB users can only SET ROLE to roles they belong to. If each tenant has its own role it isn't possible to switch between them.
# CREATE ROLE bob;
CREATE ROLE
# CREATE ROLE alice;
CREATE ROLE
# SET SESSION AUTHORIZATION bob;
SET
> SET ROLE alice;
ERROR: permission denied to set role "alice"
The biggest annoyance is with connection pooling and the inability (to my knowledge) to prevent RESET SESSION AUTHORIZATION at the DB-level: when pooling connections you'll want to have a "base" role which is a NOINHERIT member of all "effective" (tenant) roles, so it can only SET ROLE/SET SESSION AUTHORIZATION but can't do anything (so it can't be exploited), the first thing you do when you get a connection from the pool is SSA to the effective user you want, and the last thing you do when you put the connection back in the pool is a RSA.
# CREATE TABLE foo ( id serial, c1 varchar, c2 varchar );
# GRANT SELECT (id, c1, c2) ON foo TO public;
# GRANT UPDATE (c1) ON foo TO bob;
# GRANT UPDATE (c2) ON foo TO alice;
# INSERT INTO foo (c1, c2) VALUES ('a', 'b');
# SELECT * FROM foo;
id | c1 | c2
----+----+----
1 | a | b
(3 rows)
# SET SESSION AUTHORIZATION base;
> UPDATE foo SET c1 = 'd' WHERE id = 1;
ERROR: permission denied for relation foo
> SET SESSION AUTHORIZATION bob;
> UPDATE foo SET c1 = 'd' WHERE id = 1;
UPDATE 1
> SELECT * FROM foo;
id | c1 | c2
----+----+----
1 | d | b
(3 rows)
It's been discussed on the pg mailing lists but nothing has happened so far