🔍 登录 注册

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

半 半山听雨 2026-11-21 👁 2 ⏱ 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)

登录 后参与评论。

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

🔗 相关文章

Go 1.27 全面解读:泛型方法、内置 uuid 包与 encoding/json/v2

Go 1.27(2026 年 8 月发布)是近几代语言层面变化最大的一版:泛型方法落地、struct literal 键支持嵌套字段选择器、新标准库 uuid 包、encoding/json/v2 成为 v1 的实现底座。对生产代码的影响不在"能不能用新语法",而在哪些既有模式可以被替换。本文按升级决策顺序解读这版的关键变化与迁移注意。

8 小时前👍 0

Go 并发原语演进:structured concurrency 讨论、errgroup 模式与 sync 包新变化

Go 并发的哲学一直是"CSP 原语 + 手动纪律"。但社区对结构化并发(structured concurrency)的诉求持续升温——提案在 Go 官方仓库长期高关注,工具链里 golang.org/x/sync/errgroup 已是事实标准。同时 sync 包在近几版有低调但实用的变化(sync.Map 性能演进、OnceFunc/OnceValue/MustFunc 家族)。本文梳理 2026 年 Go 并发实践的正确形态:goroutine 生命周期管理模板、errgroup 的高级用法、以及"等提案不如立纪律"的取舍。

8 小时前👍 0

N-API 插件开发实战,从 C++ 到稳定 ABI 的完整路径

Node 的原生插件生态有一个常被误解的设计:N-API 从诞生起就承诺 ABI 稳定——同一份编译产物可以在不同 Node 版本间直接复用,不需要随版本重新编译。本文用一个完整的 C++ 示例走一遍 N-API 开发全流程,再对比 node-addon-api 封装层,最后给出分发策略的选型建议。

8 小时前👍 1