PostgreSQL 审计功能实现：跟踪数据库变更的最佳实践

wangpeibao

2025-10-11

数据库

数据库审计是企业数据管理中不可或缺的一环，尤其在需要合规性和数据安全的场景下。本文将介绍如何在 PostgreSQL 中实现一个灵活且强大的审计功能，帮助你跟踪数据库中的所有变更操作。

为什么需要数据库审计？

在企业应用中，数据库审计具有以下几个关键作用：

• 合规要求：满足对数据操作记录留痕的安全要求

• 安全追踪：发现可疑操作，追溯数据泄露源头

• 变更历史：记录数据的完整变更历史，支持数据恢复

• 问题排查：帮助开发团队排查数据异常问题

PostgreSQL 审计功能实现

PostgreSQL 提供了强大的触发器机制，我们可以利用它来实现全面的审计功能。下面介绍一个通用的审计触发器函数，它可以为任意表创建审计功能。

审计功能设计

我们的审计系统将记录以下信息：

• 操作类型（INSERT/UPDATE/DELETE）

• 操作时间

• 操作用户

• 更新前的数据（仅UPDATE操作时有值）

• 原表的所有字段数据

实现步骤

1. 创建专用的审计 schema

2. 创建通用审计触发器函数

3. 为需要审计的表应用该函数

核心SQL实现

首先，我们需要创建一个通用的审计触发器创建函数：

复制

-- 创建审计schema（如果已存在可以跳过） CREATE SCHEMA IF NOT EXISTS audit; -- 创建通用审计触发器函数 CREATE OR REPLACE FUNCTION audit.create_audit_trigger( origin_schema TEXT, -- 原始表所在schema origin_table TEXT, -- 原始表名 audit_schema TEXT DEFAULT audit -- 审计表所在schema，默认为audit ) RETURNS void AS $$ DECLARE backup_table TEXT; trigger_name TEXT; trigger_func_name TEXT; BEGIN -- 构造备份表名 backup_table := origin_table || _bak; -- 构造触发器名 trigger_name := origin_table || _audit; -- 构造触发器函数名 trigger_func_name := origin_table || _audit_func; -- 创建备份表 EXECUTE format( DROP TABLE IF EXISTS %I.%I; CREATE TABLE %I.%I AS SELECT ::text as operation, now()::timestamp operation_time, ::text user_name, ::text old_content, * FROM %I.%I WHERE 1=0; , audit_schema, backup_table, audit_schema, backup_table, origin_schema, origin_table); -- 创建触发器函数 EXECUTE format( CREATE OR REPLACE FUNCTION %I.%I() RETURNS TRIGGER AS $func$ BEGIN IF (TG_OP = DELETE) THEN INSERT INTO %I.%I SELECT TG_OP, now(), current_user, , OLD.*; RETURN OLD; ELSIF (TG_OP = UPDATE) THEN INSERT INTO %I.%I SELECT TG_OP, now(), current_user, row_to_json(OLD.*), NEW.*; RETURN NEW; ELSIF (TG_OP = INSERT) THEN INSERT INTO %I.%I SELECT TG_OP, now(), current_user, , NEW.*; RETURN NEW; END IF; RETURN NULL; END; $func$ LANGUAGE plpgsql; , audit_schema, trigger_func_name, audit_schema, backup_table, audit_schema, backup_table, audit_schema, backup_table); -- 创建触发器 EXECUTE format( DROP TRIGGER IF EXISTS %I ON %I.%I; CREATE TRIGGER %I AFTER INSERT OR UPDATE OR DELETE ON %I.%I FOR EACH ROW EXECUTE PROCEDURE %I.%I(); , trigger_name, origin_schema, origin_table, trigger_name, origin_schema, origin_table, audit_schema, trigger_func_name); END; $$ LANGUAGE plpgsql;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.

代码解析

这个函数实现了以下功能：

1. 动态创建备份表：为每个需要审计的表创建对应的备份表，表名为原表名加上"_bak"后缀

2. 创建触发器函数：针对每个表创建专用的触发器函数，处理不同类型的操作

3. 创建触发器：将触发器关联到原表，监听 INSERT、UPDATE 和 DELETE 操作

触发器函数的核心逻辑是：

• 对于 DELETE 操作：记录被删除的数据

• 对于 UPDATE 操作：记录更新前的数据（以 JSON 格式）和更新后的数据

• 对于 INSERT 操作：记录新插入的数据

使用示例

使用这个函数非常简单，只需要调用它并传入相应的参数：

复制

-- 为 example.test_info 表创建审计 SELECT audit.create_audit_trigger(example, test_info); -- 为 other_schema.employee 表创建审计，并指定审计表存放在 custom_audit schema中 SELECT audit.create_audit_trigger(other_schema, employee, custom_audit);1.2.3.4.5.

审计数据的查询与分析

一旦设置了审计触发器，所有对原表的操作都会被记录到对应的审计表中。你可以通过查询审计表来获取各种有用的信息：

复制

-- 查询最近的操作记录 SELECT operation, operation_time, user_name, id, name FROM audit.test_info_bak ORDERBY operation_time DESC LIMIT 100; -- 查询特定用户的操作 SELECT*FROM audit.test_info_bak WHERE user_name =postgres ORDERBY operation_time DESC; -- 查询特定记录的变更历史 SELECT operation, operation_time, user_name, old_content, name, status FROM audit.test_info_bak WHERE id =123 ORDERBY operation_time;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.