通过 PostgreSQL 中的视图和触发器跟踪当前用户

我有一个 PostgreSQL (9.4) 数据库，它根据当前用户限制对记录的访问，并跟踪用户所做的更改。这是通过视图和触发器实现的，并且在大多数情况下效果很好，但是我遇到了需要INSTEAD OF触发器的视图的问题。我试图减少问题，但我提前道歉，这仍然很长。

情况

与数据库的所有连接都是通过单个帐户从 Web 前端进行的dbweb。连接后，角色更改SET ROLE为对应于使用 Web 界面的人员，并且所有此类角色都属于组角色dbuser。（有关详细信息，请参阅此答案）。假设用户是alice.

我的大多数表都放置在一个模式中，在这里我将调用private它并属于dbowner. 这些表不能直接访问dbuser，而是由另一个角色访问dbview。例如：

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;

当前用户对特定行的可用性alice由其他视图确定。一个简化的示例（可以减少，但需要以这种方式完成以支持更一般的情况）将是：

-- 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;

然后通过角色可访问的视图提供对行的访问，dbuser例如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;

请注意，因为FROM子句中只出现一个关系，所以这种视图是可更新的，无需任何额外的触发器。

对于日志记录，存在另一个表来记录更改了哪个表以及谁更改了它。精简版是：

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;

这是通过放置在我希望跟踪的每个关系上的触发器填充的。例如，private.incident仅限于插入的示例是：

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();

所以现在如果alice插入，审计中会出现public.incident一条记录。('incident','alice')

问题

INSTEAD OF当视图变得更复杂并且需要触发器来支持插入时，这种方法会遇到问题。

假设我有两个关系，例如表示涉及一些多对一关系的实体：

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;

假设我不想公开除名称之外的详细信息private.driver，因此有一个视图可以连接表并投影我想要公开的位：

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;

为了alice能够插入此视图，必须提供触发器，例如：

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();

这样做的问题是SECURITY DEFINER触发器函数中的选项导致它以current_user设置为运行dbowner，因此如果alice将新记录插入到视图中，则private.audit记录中的相应条目是作者dbowner。

那么，有没有办法在current_user不让dbuser组角色直接访问模式中的关系的情况下保留private？

部分解决方案

正如 Craig 所建议的，使用规则而不是触发器可以避免更改current_user. 使用上面的示例，可以使用以下内容代替更新触发器：

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));               
   )

这保留了current_user. 不过，支持RETURNING性条款可能有点麻烦。此外，我找不到安全的方法来使用规则同时插入两个表以处理对driver_id. 最简单的方法是WITH在 (CTE) 中使用子句INSERT，但这些子句不允许与NEW(error: rules cannot refer to NEW within WITH query)一起使用lastval()，因此强烈建议不要使用该子句。