Tracking current user through views and triggers in PostgreSQL

I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most part this works well, but I'm having problems with views that require INSTEAD OF triggers. I have tried to reduce the problem down, but I apologise in advance that this is still quite long.

The Situation

All connections to the database are made from a web front-end via a single account dbweb. Once connected, the role is changed via SET ROLE to correspond to the person using the web interface, and all such roles belong to the group role dbuser. (See this answer for details). Let's assume the user is alice.

Most of my tables are placed in a schema that here I'll call private and belong to dbowner. These tables are not directly accessible to dbuser, but are to another role dbview. E.g:

SET SESSION AUTHORIZATION dbowner;
CREATE TABLE private.incident
(
  incident_id serial PRIMARY KEY,
  incident_name character varying NOT NULL,
  incident_owner character varying NOT NULL
);
GRANT ALL ON TABLE private.incident TO dbview;

Availability of specific rows to the current user alice is determined by other views. A simplified example (which could be reduced, but needs to be done this way to support more general cases) would be:

-- Simplified case, but in principle could join multiple tables to determine allowed ids
CREATE OR REPLACE VIEW usr_incident AS 
 SELECT incident_id
   FROM private.incident
  WHERE incident_owner  = current_user;
ALTER TABLE usr_incident
  OWNER TO dbview;

Access to the rows is then provided through a view that is accessible to dbuser roles such as alice:

``
CREATE OR REPLACE VIEW public.incident AS
SELECT incident.*
FROM private.incident
WHERE (incident_id IN ( SELECT incident_id
FROM usr_incident));
ALTER TABLE public.incident
OWNER TO dbview;
GRANT ALL ON TABLE public.incident TO dbuser;


So, is there a way to preserve current_user, without giving the dbuser group role direct access to the relations in schema private?

You may be able to use a rule, rather than an INSTEAD OF trigger, to provide write access through the view. Views always act with the security rights of the view creator rather than the querying user, but I don't think current_user changes.

If your application connects directly as the user, you can check session_user instead of current_user. This also works if you connect with a generic user then SET SESSION AUTHORIZATION. It won't work if you connect as a generic user then SET ROLE to the desired user, though.

There is no way to obtain the immediately prior user from within a SECURITY DEFINER function. You can only get the current_user and session_user. A way to get the last_user or a stack of user identities would be nice, but is not currently supported.

StackExchange Database Administrators Q#110747, answer score: 6