PostgreSQL表级触发器审计敏感操作

在PostgreSQL中，当我们需要追踪某张核心业务表（比如账户表、订单表）的敏感操作时，很多人第一时间想到的是应用层日志。但应用层日志容易被绕过，比如DBA直接登录数据库执行UPDATE或DELETE，或者某个有权限的后台脚本直接操作了数据，这些行为在应用日志里完全无迹可寻。更棘手的是，如果操作者修改了数据后立刻提交，想追溯是谁改了哪一行、改之前的值是什么，往往只能靠闪回查询或备份恢复，成本极高。表级触发器正是解决这类问题的硬核手段——它在数据库内核层面捕获变更，无论变更来自哪个应用、哪个工具、哪个直连会话，都能一网打尽。

PostgreSQL提供了行级触发器和表级触发器两种粒度。行级触发器在每一行受影响时触发，对于审计场景来说，它的开销是线性的：一次UPDATE影响10万行，触发器函数就要执行10万次，这在高并发写入时可能直接拖垮数据库。表级触发器则完全不同，它在一个SQL语句执行完成后只触发一次，不管这条语句影响了多少行。对于审计敏感操作而言，我们通常关心的是“谁在什么时间执行了什么操作”，而不是“每一行的变化细节”，表级触发器恰好匹配这个需求，而且对性能的影响要小得多。

另一个关键点是，表级触发器可以捕获到语句执行前后的完整状态，通过过渡表（transition table）一次性拿到所有受影响行的新旧数据。这在PostgreSQL 10及之后的版本中得到了良好支持，让我们可以在一次触发中完成批量审计数据的写入，效率远高于逐行处理。

整体设计分三层：一张审计日志表用来存储所有变更记录，一个审计触发器函数用来处理INSERT、UPDATE、DELETE三种操作，以及将触发器绑定到目标表上。审计日志表的设计直接决定了后续查询的便利性，建议至少包含以下字段：操作时间、操作类型、操作者（数据库角色和应用用户）、表名、事务ID、变更前后的数据快照。变更前后的数据用JSONB存储最为灵活，既能保留完整的行数据，又方便后续用JSONB操作符进行查询。

这里有一个容易被忽略的细节：应用用户和数据库角色的区分。很多系统使用连接池，所有应用共用同一个数据库账号，如果只记录session_user，你永远不知道是哪个业务用户做了操作。解决办法是在应用层设置一个会话变量，比如通过SET application_name或者自定义的临时配置参数，把业务用户ID传进来，触发器函数里通过current_setting函数读取。

先给出审计日志表的DDL，这张表的设计考虑了分区和索引策略，因为审计表通常会随着时间推移变得非常庞大。

CREATE TABLE audit_log (
    id BIGSERIAL,
    table_name TEXT NOT NULL,
    operation TEXT NOT NULL,
    operation_time TIMESTAMPTZ NOT NULL DEFAULT now(),
    transaction_id BIGINT NOT NULL DEFAULT txid_current(),
    database_user TEXT NOT NULL DEFAULT session_user,
    application_user TEXT,
    client_addr INET,
    old_data JSONB,
    new_data JSONB,
    statement_text TEXT,
    PRIMARY KEY (id, operation_time)
) PARTITION BY RANGE (operation_time);

这里把operation_time作为分区键，后续可以按月或按周创建分区，方便管理历史数据。old_data和new_data分别存放变更前和变更后的行数据，用row_to_json函数转换整行为JSONB。statement_text字段记录原始的SQL文本，这在追溯问题时非常关键——你能看到操作者到底执行了什么语句，而不仅仅是结果。

application_user字段就是前面提到的业务用户标识，客户端IP地址通过inet_client_addr()函数获取，这两个信息组合起来，基本可以定位到具体的操作人和来源机器。

触发器函数是整个方案的核心。我们需要处理三种操作类型，并且充分利用过渡表来批量写入审计日志。PostgreSQL 10开始支持在表级触发器中使用OLD TABLE和NEW TABLE引用过渡表，这让我们可以一次性处理所有受影响的行。

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_table_name TEXT;
    v_operation TEXT;
    v_app_user TEXT;
    v_client_addr INET;
    v_stmt_text TEXT;
BEGIN
    v_table_name := TG_TABLE_NAME;
    v_operation := TG_OP;
    v_client_addr := inet_client_addr();
    
    BEGIN
        v_app_user := current_setting('app.current_user', true);
    EXCEPTION WHEN OTHERS THEN
        v_app_user := NULL;
    END;
    
    v_stmt_text := current_query();
    
    IF v_operation = 'INSERT' THEN
        INSERT INTO audit_log (
            table_name, operation, database_user,
            application_user, client_addr,
            new_data, statement_text
        )
        SELECT
            v_table_name, 'INSERT', session_user,
            v_app_user, v_client_addr,
            row_to_json(NEW_TABLE.*)::JSONB,
            v_stmt_text
        FROM NEW_TABLE;
        
    ELSIF v_operation = 'UPDATE' THEN
        INSERT INTO audit_log (
            table_name, operation, database_user,
            application_user, client_addr,
            old_data, new_data, statement_text
        )
        SELECT
            v_table_name, 'UPDATE', session_user,
            v_app_user, v_client_addr,
            row_to_json(OLD_TABLE.*)::JSONB,
            row_to_json(NEW_TABLE.*)::JSONB,
            v_stmt_text
        FROM OLD_TABLE
        JOIN NEW_TABLE ON OLD_TABLE.ctid = NEW_TABLE.ctid;
        
    ELSIF v_operation = 'DELETE' THEN
        INSERT INTO audit_log (
            table_name, operation, database_user,
            application_user, client_addr,
            old_data, statement_text
        )
        SELECT
            v_table_name, 'DELETE', session_user,
            v_app_user, v_client_addr,
            row_to_json(OLD_TABLE.*)::JSONB,
            v_stmt_text
        FROM OLD_TABLE;
    END IF;
    
    RETURN NULL;
END;
$$;

这个函数有几个值得注意的设计点。第一，current_query()函数返回当前正在执行的SQL文本，它只在语句级别可用，而表级触发器恰好是语句级别的，所以这里可以安全使用。第二，UPDATE操作中通过ctid字段关联OLD_TABLE和NEW_TABLE，ctid是PostgreSQL中行的物理位置标识，在同一事务内是稳定的，可以精确匹配新旧行。第三，捕获current_setting可能抛出的异常做了容错处理，即使应用层没有设置app.current_user变量，触发器也不会失败。

关于ctid关联有一个重要提醒：如果UPDATE语句包含对同一行的多次更新（比如在同一个语句中通过子查询多次修改同一行），ctid可能会变化，导致关联不准确。但在绝大多数实际场景中，一条UPDATE语句对同一行只会修改一次，所以这个方案是可靠的。如果你的业务场景确实存在这种极端情况，可以考虑使用主键来关联，但这就要求审计函数必须知道目标表的主键列名，通用性会打折扣。

创建触发器的语法非常简洁，关键是要明确指定REFERENCING子句来启用过渡表，并且把触发时机设为STATEMENT级别。

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON accounts
REFERENCING NEW TABLE AS NEW_TABLE OLD TABLE AS OLD_TABLE
FOR EACH STATEMENT
EXECUTE FUNCTION audit_trigger_func();

这里的目标表是accounts，你可以替换成任何需要审计的表。AFTER触发器意味着审计日志的写入发生在原始操作成功之后，如果审计写入失败，原始操作不会回滚——这是一个权衡。如果你希望审计失败时也阻止原始操作，可以改成BEFORE触发器，但这样会增加主操作的延迟，一般不推荐。更好的做法是在审计日志写入失败时通过异常处理记录到数据库日志中，确保主业务不受影响。

表级触发器的性能优势在于批量处理，但审计日志表的写入仍然会带来额外开销。对于高并发写入的场景，有几个优化方向可以落地。首先是审计日志表的分区，按月分区是最常见的做法，既能控制单表大小，又方便定期归档或删除过期数据。创建分区的脚本可以做成定时任务自动执行。

CREATE TABLE audit_log_2025_01 PARTITION OF audit_log
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

其次是索引策略。审计日志表通常以写入为主，查询为辅，索引不宜过多。建议只在operation_time和table_name上建立联合索引，满足按时间范围和表名查询的常见需求。如果经常按application_user查询，可以再加一个索引。记住每个索引都会拖慢写入速度，需要根据实际查询模式做取舍。

另一个容易被忽略的优化点是过渡表的大小。如果一条UPDATE语句影响了数百万行，过渡表会占用大量内存，甚至可能触发磁盘临时文件。对于这种大规模批量操作，可以在应用层拆分成多个小批次执行，或者在触发器函数中加入行数阈值判断，超过阈值时改用更轻量的审计方式，比如只记录语句文本而不记录完整的新旧数据。

表级触发器有一个天然的盲区：它无法捕获TRUNCATE操作，因为TRUNCATE在PostgreSQL中不会触发常规的DML触发器。如果你的敏感操作定义里包含TRUNCATE，需要使用事件触发器（Event Trigger）来弥补。事件触发器在DDL层面工作，可以捕获TRUNCATE、DROP TABLE等操作。

CREATE OR REPLACE FUNCTION audit_ddl_func()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    obj RECORD;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF obj.command_tag IN ('TRUNCATE TABLE', 'DROP TABLE') THEN
            INSERT INTO audit_log (
                table_name, operation, operation_time,
                database_user, client_addr, statement_text
            ) VALUES (
                obj.object_identity,
                obj.command_tag,
                now(),
                session_user,
                inet_client_addr(),
                current_query()
            );
        END IF;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER audit_ddl_trigger
ON ddl_command_end
WHEN TAG IN ('TRUNCATE TABLE', 'DROP TABLE')
EXECUTE FUNCTION audit_ddl_func();

这样就把TRUNCATE和DROP TABLE也纳入了审计范围。注意事件触发器是在整个数据库级别生效的，如果你的审计需求只针对特定表，需要在函数内部做过滤判断。

审计系统本身也是需要保护的对象。如果操作者能修改或删除审计日志，那整个审计体系就形同虚设。建议将审计日志表放在单独的schema中，并且只授予INSERT权限给相关角色，禁止DELETE和UPDATE。更进一步，可以启用PostgreSQL的行级安全策略（Row Level Security）来加固，或者利用数据库的审计扩展如pgaudit做底层补充。

还有一个现实问题是审计日志表的膨胀。随着时间积累，审计数据可能达到TB级别。除了分区管理外，可以考虑定期将老旧分区转移到归档库，或者转换成压缩的列存格式（比如使用cstore_fdw或timescaledb的压缩功能）。如果不需要长期保留完整的新旧数据快照，可以设定保留策略，比如只保留最近6个月的详细数据，更早的数据只保留操作类型、操作者和时间戳的摘要信息。

审计日志不仅仅是合规的需要，它还能成为数据库运维和业务分析的有力工具。通过分析审计日志，你可以发现哪些表被频繁修改、哪些用户的操作模式异常、是否存在慢查询导致的锁等待。例如，定期扫描audit_log中statement_text字段，找出执行时间异常的DML语句，结合pg_stat_statements插件，可以构建一套完整的数据库操作画像系统。

对于敏感操作的实时告警，可以在触发器函数中加入条件判断，当满足特定条件时（比如修改了账户余额字段且金额超过阈值），通过LISTEN/NOTIFY机制发送异步通知，应用层监听这些通知并触发告警流程。这样就把被动的审计记录变成了主动的风险监控。

表级触发器审计方案的核心优势在于数据库原生支持、零额外依赖、对应用完全透明、性能开销可控。它特别适合那些需要强制审计、不能依赖应用层改造的场景。但也要清楚它的边界：过渡表的内存消耗在大批量操作时需要注意，TRUNCATE需要额外的事件触发器补充，审计日志表本身需要配套的分区和清理策略。如果你的系统已经在使用专门的审计工具或数据库审计网关，这套方案可以作为底层补充，形成纵深防御。

在实际落地时，建议先在非核心表上测试触发器的性能影响，确认过渡表的内存使用在可接受范围内，再逐步推广到核心业务表。审计策略的配置最好做成元数据驱动的方式，比如用一张配置表来管理哪些表需要审计、审计哪些操作类型，这样新增审计对象时不需要重复创建触发器和函数，维护成本会大幅降低。