I used to use a different approach:
1. Create auth() pl/python procedure as follows:
create or replace
function auth(auser_id integer) returns void as $$
GD['user_id'] = auser_id
$$ language plpythonu;
This procedure is supposed to be called after a sucesseful authorisation (in a database or on application side).
2. Create get_current_user() procedure:
create or replace
function get_current_user() returns integer as $$
return GD.get('user_id')
$$ language plpythonu stable security definer;
Now you can get current user id from every SQL query or stored procedure. It works fast because Python shared array GD is always present in memory.
There is a restriction on how many distinct GRANTs you can
issue against any one object --- performance will get bad if the ACL
list gets too large.
Any ballpark numbers here? Are we talking 50 or 8000?