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;
Note that because only the one relation appears in the FROM clause, this sort of view is updatable without any additional triggers.
For logging, another table exists to record which table is changed and who changed it. A reduced version is:
CREATE TABLE private.audit
(
audit_id serial PRIMATE KEY,
table_name text NOT NULL,
user_name text NOT NULL
);
GRANT INSERT ON TABLE private.audit TO dbuser;
This is populated via triggers placed on each of the relations that I wish to track. For instance, an example for private.incident limited to just inserts is:
CREATE OR REPLACE FUNCTION private.if_modified_func()
RETURNS trigger AS
$BODY$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO private.audit (table_name, user_name)
VALUES (tg_table_name::text, current_user::text);
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION private.if_modified_func() TO dbuser;
CREATE TRIGGER log_incident
AFTER INSERT ON private.incident
FOR EACH ROW
EXECUTE PROCEDURE private.if_modified_func();
So now if alice inserts into public.incident, a record ('incident','alice') appears in audit.
The Problem
This approach hits problems when the views become more complicated and need INSTEAD OF triggers to support inserts.
Let's say I have two relations, for instance representing entities involved in some many-to-one relationship:
CREATE TABLE private.driver
(
driver_id serial PRIMARY KEY,
driver_name text NOT NULL
);
GRANT ALL ON TABLE private.driver TO dbview;
CREATE TABLE private.vehicle
(
vehicle_id serial PRIMARY KEY,
incident_id integer REFERENCES private.incident,
make text NOT NULL,
model text NOT NULL,
driver_id integer NOT NULL REFERENCES private.driver
);
GRANT ALL ON TABLE private.vehicle TO dbview;
Assume that I don't want to expose the details other than the name of private.driver, and so have a view that joins the tables and projects the bits I want to expose:
CREATE OR REPLACE VIEW public.vehicle AS
SELECT vehicle_id, make, model, driver_name
FROM private.driver
JOIN private.vehicle USING (driver_id)
WHERE (incident_id IN ( SELECT incident_id
FROM usr_incident));
ALTER TABLE public.vehicle OWNER TO dbview;
GRANT ALL ON TABLE public.vehicle TO dbuser;
In order for alice to be able to insert into this view a trigger has to be provided, e.g.:
CREATE OR REPLACE FUNCTION vehicle_vw_insert()
RETURNS trigger AS
$BODY$
DECLARE did INTEGER;
BEGIN
INSERT INTO private.driver(driver_name) VALUES(NEW.driver_name) RETURNING driver_id INTO did;
INSERT INTO private.vehicle(make, model, driver_id) VALUES(NEW.make_id,NEW.model, did) RETURNING vehicle_id INTO NEW.vehicle_id;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION vehicle_vw_insert()
OWNER TO dbowner;
GRANT EXECUTE ON FUNCTION vehicle_vw_insert() TO dbuser;
CREATE TRIGGER vehicle_vw_insert_trig
INSTEAD OF INSERT ON public.vehicle
FOR EACH ROW
EXECUTE PROCEDURE vehicle_vw_insert();
The problem with this is that the SECURITY DEFINER option in the trigger function causes it to be run with current_user set to dbowner, so if alice inserts a new record into the view the corresponding entry in private.audit records the author to be dbowner.
So, is there a way to preserve current_user, without giving the dbuser group role direct access to the relations in schema private?
Partial Solution
As suggested by Craig, using rules rather than triggers avoids changing the current_user. Using the above example, the following can be used in place of the update trigger:
CREATE OR REPLACE RULE update_vehicle_view AS
ON UPDATE TO vehicle
DO INSTEAD
(
UPDATE private.vehicle
SET make = NEW.make,
model = NEW.model
WHERE vehicle_id = OLD.vehicle_id
AND (NEW.incident_id IN ( SELECT incident_id
FROM usr_incident));
UPDATE private.driver
SET driver_name = NEW.driver_name
FROM private.vehicle v
WHERE driver_id = v.driver_id
AND vehicle_id = OLD.vehicle_id
AND (NEW.incident_id IN ( SELECT incident_id
FROM usr_incident));
)
This preserves current_user. Supporting RETURNING clauses can be a bit hairy, though. Furthermore, I could find no safe way to use rules to simultaneously insert into both tables in order to handle the use of a sequence for driver_id. The easiest way would have been to use a WITH clause in an INSERT (CTE), but these aren't allowed in conjunction with NEW (error: rules cannot refer to NEW within WITH query), leaving one to resort to lastval() which is strongly discouraged.
