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);
设计决策的三个要点:
- JSONB 快照优于逐字段明细表:一张 audit_log 服务所有业务表(通用触发器),字段增减不需要改审计结构;按行取历史一条 SQL 搞定;
- txid 把"同一事务的多次变更"串成链:转账场景借记与贷记两笔变更同 txid,审计时原子级还原;
- 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 级
边界策略三条:
- 选择性审计:只审计敏感表(资金、权限、订单),不要"全库审计"——写入放大与膨胀会让全库方案失控;
- 批量任务的审计豁免:内部批处理任务(对账回填)用
SESSION REPLICATION ROLE或专用开关跳过,改走"批次级审计"(任务日志); - 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 审计"的数据库侧落地。
评论(0)
登录 后参与评论。