本文目录导读：
- 方案一：使用官方扩展
pg_audit（推荐，最专业） - 方案二：使用触发器 + 审计表（自定义，灵活）
- 方案三：启用 PostgreSQL 自身日志（简单，粗粒度）
- 方案四：使用第三方工具（如 pgauditadmin 或 log_fdw）
- 最佳实践建议
PostgreSQL 的审计功能（记录谁在什么时间执行了什么操作）不像 MySQL 或 SQL Server 那样内置完善，它主要通过以下几种方式来实现，你可以根据安全合规要求和性能开销来选择合适的方案。
使用官方扩展 pg_audit（推荐，最专业）
这是 PostgreSQL 社区最推荐的审计方案，它基于 PostgreSQL 的日志系统，但提供了细粒度的控制，可以记录 DDL、DML、SELECT 等不同类型语句。
安装
- 如果使用 apt/yum 安装的 PostgreSQL：安装
postgresql-contrib包（通常附带pg_audit）。 - 从源码编译：在编译时加上
--with-pgaudit选项。 - 检查是否已安装：
SELECT * FROM pg_available_extensions WHERE name = 'pgaudit';
配置
需要修改 postgresql.conf 文件并重启或重新加载配置。
-
加载扩展：
shared_preload_libraries = 'pgaudit' # 必须重启
-
设置日志参数：
# 必须设置为 ddl 或以上级别，才能记录在日志中 log_statement = 'none' # pg_audit 会接管日志，建议设为 none，避免重复 logging_collector = on # 收集日志文件 log_directory = 'pg_log' # 日志存放目录 log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # pg_audit 参数 pgaudit.log = 'write, ddl' # 记录写入和DDL操作 pgaudit.log_level = 'notice' # 日志级别，通常为 notice pgaudit.log_catalog = off # 是否记录系统表操作 pgaudit.log_relation = on # 是否记录表名 pgaudit.log_client = on # 是否记录客户端信息 pgaudit.log_statement_once = off # 每条语句只记录一次
-
配置项解释：
pgaudit.log：核心配置，值可以是none,all,write,ddl,read,function,role,misc等的组合。'write, ddl, read'。pgaudit.log_relation：如果为on，会记录具体的表名（如INSERT INTO t1），否则只记录语句类型。
应用配置
编辑配置文件后,重启 PostgreSQL 服务或使用：
SELECT pg_reload_conf();
验证
执行一条 DDL 语句：
CREATE TABLE test_audit (id int);
查看 PostgreSQL 日志文件，你会看到类似以下的记录：
2025-01-20 14:30:00.123 UTC [12345] LOG: AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_audit (id int);,<not logged>这意味着审计已生效。
更精细的控制（对象级审计）
pg_audit 允许你创建“审计角色”，只审计特定对象的操作。
-- 创建一个专门的审计角色 CREATE ROLE auditor_role; COMMENT ON ROLE auditor_role IS '审计角色'; -- 只审计某些敏感表的操作 -- 需要结合行级安全策略或触发器来实现更复杂的对象过滤，pg_audit 本身不直接支持按表名过滤（仅支持按角色过滤）。
使用触发器 + 审计表（自定义，灵活）
适合需要长期存储审计记录,或者需要自定义审计字段（如应用程序上下文）的场景。
创建审计表
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name TEXT NOT NULL, -- 被修改的表
operation TEXT NOT NULL, -- INSERT, UPDATE, DELETE
old_row_data JSONB, -- 旧行数据
new_row_data JSONB, -- 新行数据
changed_by TEXT NOT NULL, -- 执行用户
client_addr INET, -- 客户端IP
session_user TEXT, -- 会话用户
application_name TEXT, -- 应用名称
change_time TIMESTAMPTZ DEFAULT NOW(),
query TEXT -- 完整SQL
);
创建审计函数
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (
table_name,
operation,
old_row_data,
new_row_data,
changed_by,
client_addr,
session_user,
application_name,
query
) VALUES (
TG_TABLE_NAME,
TG_OP,
CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD)::jsonb ELSE NULL END,
CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN row_to_json(NEW)::jsonb ELSE NULL END,
current_user,
inet_client_addr(),
session_user,
current_setting('application_name'),
current_query()
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
将触发器附加到敏感表
CREATE TRIGGER t_audit_employees
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION audit_trigger_function();
优点：非常灵活，可以自定义记录内容。
缺点：
- 对性能有影响（每条语句触发函数 + JSON 序列化）。
- 需要为每张被审计的表手动创建触发器。
- 无法审计
SELECT（SELECT不触发触发器）。
启用 PostgreSQL 自身日志（简单，粗粒度）
如果只是临时排查,不需要长期保存，可以使用 PostgreSQL 内置的日志功能。
配置 postgresql.conf
logging_collector = on log_statement = 'all' # 记录所有SQL，或者 'ddl' / 'mod' log_duration = on # 记录语句执行时间 log_line_prefix = '%m [%p] %u@%d %r ' # 包含时间、用户、数据库、主机名
查看日志
所有 SQL 语句会写入 pg_log/ 目录下的日志文件。
缺点：
- 所有语句都会记录,日志量巨大，不适合生产环境长期开启。
- 无法记录更新前后的数据值。
- 区分度差,无法仅审计特定表。
使用第三方工具（如 pgauditadmin 或 log_fdw）
- pgauditanalyze：一个开源工具，能解析
pg_audit生成的日志，生成更友好的报告。 - log_fdw：允许你在 PostgreSQL 中以外部表的形式查询数据库日志文件。
最佳实践建议
- 生产环境推荐：使用
pg_audit扩展，它是专门为审计设计的，性能开销可控，并且符合很多安全审计标准（如 SOX、PCI-DSS）。 - 何时用触发器：当你需要记录行级数据变更前的值（如更新前的数据），或者需要自定义审计字段（如“订单金额大于1000时记录”），并且数据量不大时。
- 何时用日志：仅用于临时调试，或作为补充记录（配合
pg_audit的记录要完整一些，但log_statement可以记录到更细的级别）。 - 注意事项：
- 审计日志文件会快速膨胀,需要配置日志轮转（
log_rotation_age和log_rotation_size）。 - 审计表本身也要考虑安全性：将其放在单独的表空间，并限制对
audit_log表的访问权限，防止审计记录被恶意删除或修改。 - 定期对审计表进行分区，清理过期记录。
- 审计日志文件会快速膨胀,需要配置日志轮转（
- 长期、合规审计 →
pg_audit扩展。 - 追踪数据变更（谁改了什么值） → 触发器 + 审计表。
- 临时排查问题 → 开启
log_statement = 'all'。
