PostgreSQL 11审计触发器优化：区分应用与数据库更新的用户记录

核心判断逻辑 #

应用更新时会主动修改last_modified_by字段为当前登录用户，而直接数据库更新通常不会改动该字段（或沿用旧值）。基于此，我们可以通过对比NEW.last_modified_by和OLD.last_modified_by的值，结合当前数据库用户上下文区分场景：

• 当NEW.last_modified_by <> OLD.last_modified_by时，判定为应用发起的更新，使用NEW.last_modified_by填充userid
• 当NEW.last_modified_by = OLD.last_modified_by时，判定为直接数据库更新，使用当前数据库用户（current_user）或空值填充userid

修改触发器函数示例 #

假设原触发器函数名为employee_audit_trigger_func，修改后的函数代码如下：

CREATE OR REPLACE FUNCTION employee_audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        -- 插入审计记录，区分更新场景填充userid
        INSERT INTO employee_audit (
            employee_id,
            change_type,
            old_data,
            new_data,
            userid,
            change_time
        ) VALUES (
            NEW.id,
            'UPDATE',
            row_to_json(OLD),
            row_to_json(NEW),
            -- 核心判断逻辑
            CASE
                WHEN NEW.last_modified_by <> OLD.last_modified_by THEN NEW.last_modified_by
                ELSE current_user -- 若需空值可替换为 NULL
            END,
            NOW()
        );
    ELSIF TG_OP = 'INSERT' THEN
        -- 保留原有INSERT逻辑（若存在）
        INSERT INTO employee_audit (
            employee_id,
            change_type,
            new_data,
            userid,
            change_time
        ) VALUES (
            NEW.id,
            'INSERT',
            row_to_json(NEW),
            NEW.last_modified_by,
            NOW()
        );
    ELSIF TG_OP = 'DELETE' THEN
        -- 保留原有DELETE逻辑（若存在）
        INSERT INTO employee_audit (
            employee_id,
            change_type,
            old_data,
            userid,
            change_time
        ) VALUES (
            OLD.id,
            'DELETE',
            row_to_json(OLD),
            current_user, -- 可根据需求调整删除场景的userid填充规则
            NOW()
        );
    END IF;
    RETURN NULL; -- AFTER触发器无需返回值
END;
$$ LANGUAGE plpgsql;

补充说明 #

1. 边界场景优化：如果存在应用更新时last_modified_by恰好与旧值相同的情况（比如用户修改其他字段但未变更登录用户），可结合application_name辅助判断——应用连接通常会设置特定的application_name，而直接客户端（如psql）的默认名称可识别。示例逻辑：

   CASE
       WHEN NEW.last_modified_by <> OLD.last_modified_by THEN NEW.last_modified_by
       WHEN current_setting('application_name') IN ('your-app-service', 'mobile-app-client') THEN NEW.last_modified_by
       ELSE current_user
   END
   

2. 权限兜底：若需限制直接数据库更新，可通过数据库角色权限设置减少这类场景，但触发器逻辑仍需作为兜底方案。
3. 验证步骤：
   • 应用端更新employee表，检查audit表userid是否为应用登录用户
   • 用psql等客户端直接执行UPDATE employee SET ...（不修改last_modified_by），检查audit表userid是否为当前数据库用户或空值

内容的提问来源于stack exchange，提问作者franco