🔍 登录 注册

PL/pgSQL 审计触发器实战,把数据变更变成可查证据链

半 半山听雨 2026-11-21 👁 0 ⏱ 6min

PL/pgSQL 审计触发器实战,把数据变更变成可查证据链

数据库层的审计触发器是合规系统的地基:谁在什么时候把金额从多少改成多少——应用日志可以绕过(直连工具改数据),触发器不能。本文给出一套可落地的 PL/pgSQL 审计方案:通用审计表结构、变更捕获触发器、批量操作的边界处理,以及审计数据的保留与清理策略。

一、审计表结构:记录"什么变了"

CREATE TABLE audit_log (
  id           BIGSERIAL PRIMARY KEY,
  table_name   TEXT NOT NULL,            -- 变更的表
  op           CHAR(1) NOT NULL,         -- I=INSERT / U=UPDATE / D=DELETE
  row_pk       BIGINT,                   -- 业务主键(便于按行检索历史)
  old_data     JSONB,                    -- 变更前快照(U/D 有)
  new_data     JSONB,                    -- 变更后快照(I/U 有)
  changed_cols TEXT[],                   -- 变更的字段清单(快速过滤)
  app_user     TEXT,                     -- 应用层用户(session 注入,见下)
  db_user      TEXT DEFAULT current_user,
  client_addr  INET DEFAULT inet_client_addr(),
  txid         BIGINT DEFAULT txid_current(),  -- 事务 ID:同一事务的变更可关联
  created_at   TIMESTAMPTZ DEFAULT now()
);

-- 检索索引:按表+主键+时间
CREATE INDEX idx_audit_row ON audit_log (table_name, row_pk, created_at DESC);
CREATE INDEX idx_audit_user ON audit_log (app_user, created_at DESC);
-- JSONB GIN 索引:按变更内容搜索
CREATE INDEX idx_audit_new ON audit_log USING gin (new_data jsonb_path_ops);

设计决策的三个要点:

  1. JSONB 快照优于逐字段明细表:一张 audit_log 服务所有业务表(通用触发器),字段增减不需要改审计结构;按行取历史一条 SQL 搞定;
  2. txid 把"同一事务的多次变更"串成链:转账场景借记与贷记两笔变更同 txid,审计时原子级还原;
  3. app_user 与 db_user 分离:应用都用同一个数据库账号连接,db_user 无区分度——应用层用户身份靠 session 变量注入(下一节)。

二、通用触发器函数:一份代码服务全部表

CREATE OR REPLACE FUNCTION fn_audit() RETURNS trigger AS $$
DECLARE
  v_old JSONB;
  v_new JSONB;
  v_pk  BIGINT;
  v_changed TEXT[];
BEGIN
  -- 老快照与新快照
  v_old := CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END;
  v_new := CASE WHEN TG_OP IN ('UPDATE','INSERT') THEN to_jsonb(NEW) END;

  -- 主键假设:所有被审计表都有 id 列(约定优于配置)
  v_pk := CASE WHEN TG_OP = 'DELETE' THEN (to_jsonb(OLD)->>'id')::bigint
               ELSE (to_jsonb(NEW)->>'id')::bigint END;

  -- UPDATE 时算出变更列
  IF TG_OP = 'UPDATE' THEN
    SELECT array_agg(key) INTO v_changed
    FROM jsonb_each(v_new) n
    JOIN jsonb_each(v_old) o USING (key)
    WHERE n.value IS DISTINCT FROM o.value;
  END IF;

  INSERT INTO audit_log (table_name, op, row_pk, old_data, new_data,
                         changed_cols, app_user)
  VALUES (TG_TABLE_NAME,
          left(TG_OP,1),
          v_pk, v_old, v_new, v_changed,
          current_setting('app.user_name', true));   -- 应用层身份注入

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

应用层身份注入(每个连接/请求设置一次):

-- 应用在事务开始时执行(连接池归还前 RESET)
SET LOCAL app.user_name = 'zhang.san';
// Node 应用侧(psycopg 类似)
await client.query("BEGIN");
await client.query("SET LOCAL app.user_name = $1", [currentUser]);
await client.query("UPDATE accounts SET balance = balance - 100 WHERE id = $1", [aid]);
await client.query("COMMIT");

SET LOCAL 的妙处:作用域是事务,事务结束自动清除,连接池复用连接时不会串身份——比 SET(会话级)安全得多。审计触发器读不到该变量时 app_user 为 NULL,这本身就是一个审计信号(绕过应用层的直连操作)。

三、挂载触发器与批量操作边界

-- 挂载(一张 DDL 搞定)
CREATE TRIGGER trg_audit_accounts
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION fn_audit();

批量操作的代价必须先算账:

FOR EACH ROW:批量 UPDATE 10 万行 = 触发器执行 10 万次 = audit_log 增 10 万行
代价账本:
  - 审计表写入是业务事务的一部分(同步开销,事务变慢)
  - audit_log 表自身膨胀(15 章的膨胀问题在这里同样发生)
  - JSONB 快照宽表(列多行宽)时单条审计记录可达 KB 级

边界策略三条:

  1. 选择性审计:只审计敏感表(资金、权限、订单),不要"全库审计"——写入放大与膨胀会让全库方案失控;
  2. 批量任务的审计豁免:内部批处理任务(对账回填)用 SESSION REPLICATION ROLE 或专用开关跳过,改走"批次级审计"(任务日志);
  3. FOR EACH STATEMENT 辅助:对纯"记录发生了什么"(不需要行级快照)的场景用语句级触发器记账,成本一个数量级更低。

四、审计数据的保留与清理

audit_log 自身的生命周期管理:

-- 按月分区(继承第十章分区思路,清理即 DROP PARTITION)
CREATE TABLE audit_log_2026_11 PARTITION OF audit_log
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- 保留策略:合规要求决定(常见:财务类 10 年,一般操作 1 年)
-- 分区方案下"清理"= 卸载分区到归档存储,秒级完成且不产生 vacuum 压力

与 15 章 vacuum 篇的衔接:audit_log 是插入型表(无 UPDATE),膨胀风险低但增长快——分区 + 时间索引 + 定期归档是它的三件套。另一个合规要点:审计表本身要防篡改(DBA 账号绕过怎么办)——进阶方案是把审计记录同步写入 WORM 存储(对象存储合规模式)或追加哈希链(每条记录含前一条的哈希,篡改可检测)。

五、坑清单

坑 现象 应对
全库无差别审计 写入放大、审计表膨胀失控 只审计敏感表;批量豁免
SET(会话级)注身份 连接池串身份 SET LOCAL 事务级
触发器抛错阻断业务 审计失败导致业务失败 审计函数内 EXCEPTION 兜底(记录失败仍返回)
大字段全量快照 单条审计记录过大 大 TEXT 列截断或只存哈希
主键约定不统一 row_pk 取不到 强制被审计表含 id;触发器内 NULL 检查
pg_dump 备份漏审计表 审计链断裂 审计表纳入备份清单与 PITR 范围

第三坑的两难要正面回答:审计失败要不要阻断业务?合规严格的系统(资金)选"阻断"(fail-closed,宁可不写库);一般系统选"兜底继续 + 告警"(fail-open)。选择必须显式写进审计函数的 EXCEPTION 分支,而不是让它偶然发生。

小结

PL/pgSQL 审计触发器的三件套:通用 JSONB 审计表(txid 串事务链)、一份函数服务全表(SET LOCAL 注入应用层身份)、批量边界按代价设计(选择性审计 + 豁免机制)。 audit_log 自身用分区管理生命周期,进阶防篡改走哈希链。审计的价值在"证据链完整性":谁能改、改了什么、谁绕过了应用层——一条 SQL 都能查出来。这恰是 u5 审计专栏常说的"IT 审计"的数据库侧落地。

读完了?给作者一点支持

本文采用 CC BY 4.0 协议转载 · © 半山听雨

评论(0)

登录 后参与评论。

  • 还没有评论,来抢沙发。

🔗 相关文章

审计人的职业规划地图:事务所、企业内审、监管与自由执业的四条路线

"做了三年审计接下来去哪"是行业论坛上的永恒问题。2026 年的就业市场给出了新的答案坐标:AI 压缩了基础核算与核对类岗位,同时抬高了风险判断、内控体系、数据审计类岗位的身价。本文把审计人的出路整理成四条主路线——事务所纵深、企业内审/风控、监管与准则机构、自由执业/专家证人——每条给进入条件、典型轨迹、5 年收入区间感与适配画像,帮你做一次结构化的自我定位。

4 天前👍 1

MySQL 9.x VECTOR 类型实战,向量检索的原生时代

MySQL 9.x Innovation 线(当前最新 9.7.4,2026-10-20 发布,release notes 里 9.7.4.1 已列为待发布状态)引入了 VECTOR 原生类型,把"向量检索"从外挂方案(应用层算相似度)变成了 SQL 原生能力。本文讲清 VECTOR 类型的语法、距离函数、与 MySQL 生态(HeatWave/应用层)的配合方式,以及它与专业向量库的选型边界。

6 小时前👍 1

Spring Cloud 2026.0.0-RC2 Paddington 发布:版本列车迁移指南

2026 年 11 月 5 日,Spring 官方宣布 Spring Cloud 2026.0.0-RC2(代号 Paddington)发布——这是继 2025.1.x(Oakwood)之后的下一个版本列车,对应 Spring Boot 4.2.x 世代。对还在 Oakwood 上的团队,这是规划迁移的最佳窗口。本文基于官方项目页与博客的权威信息(2026-11-19 抓取),给出版本列车的现状、迁移核对清单与回滚预案。

2 天前👍 1