PostgreSQL如何审计操作

本文目录导读：

1. 方案一：使用官方扩展 pg_audit（推荐，最专业）
2. 方案二：使用触发器 + 审计表（自定义，灵活）
3. 方案三：启用 PostgreSQL 自身日志（简单，粗粒度）
4. 方案四：使用第三方工具（如 pgauditadmin 或 log_fdw）
5. 最佳实践建议

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 中以外部表的形式查询数据库日志文件。

最佳实践建议

1. 生产环境推荐：使用 pg_audit 扩展，它是专门为审计设计的，性能开销可控，并且符合很多安全审计标准（如 SOX、PCI-DSS）。
2. 何时用触发器：当你需要记录行级数据变更前的值（如更新前的数据），或者需要自定义审计字段（如“订单金额大于1000时记录”），并且数据量不大时。
3. 何时用日志：仅用于临时调试，或作为补充记录（配合 pg_audit 的记录要完整一些，但 log_statement 可以记录到更细的级别）。
4. 注意事项：
   • 审计日志文件会快速膨胀,需要配置日志轮转（log_rotation_age 和 log_rotation_size）。
   • 审计表本身也要考虑安全性：将其放在单独的表空间，并限制对 audit_log 表的访问权限，防止审计记录被恶意删除或修改。
   • 定期对审计表进行分区，清理过期记录。

• 长期、合规审计 → pg_audit 扩展。
• 追踪数据变更（谁改了什么值） → 触发器 + 审计表。
• 临时排查问题 → 开启 log_statement = 'all'。