PostgreSQL如何记录用户/角色增删、权限变更等操作审计日志

原有方案不可行的原因 #

• pg_roles、pg_user 是基于系统目录封装的视图，PostgreSQL 不支持在视图上创建行级 BEFORE/AFTER 触发器，因此第一次创建触发器会直接报错。
• pg_authid 是 PostgreSQL 存储角色认证信息的核心系统目录表，数据库对系统目录有强制保护机制，即便是超级用户也不允许直接在系统目录上创建触发器、直接修改数据，避免破坏数据库核心元数据，因此第二次创建触发器会报权限错误。

可行实现方案 #

方案1：使用原生事件触发器（无需额外扩展，直接落表） #

PostgreSQL 的事件触发器专门用于捕获全局 DDL 操作，用户/角色的新增、删除、权限修改都属于 DDL 范畴，可以通过事件触发器实现全量记录，直接写入自定义审计表。

1. 首先创建审计日志存储表：

CREATE TABLE role_operation_audit (
    op_time timestamptz DEFAULT now(),
    op_user text DEFAULT current_user,
    client_addr inet DEFAULT inet_client_addr(),
    op_tag text,
    object_type text,
    object_name text,
    op_detail_sql text DEFAULT current_query()
);

2. 创建事件触发器的处理函数：

CREATE OR REPLACE FUNCTION audit_role_operation()
RETURNS event_trigger AS $$
DECLARE
    dropped_obj record;
BEGIN
    -- 处理角色/用户删除操作
    FOR dropped_obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        IF dropped_obj.object_type IN ('role', 'user') THEN
            INSERT INTO role_operation_audit(op_tag, object_type, object_name)
            VALUES ('DROP', dropped_obj.object_type, dropped_obj.object_identity);
        END IF;
    END LOOP;

    -- 处理角色/用户创建、修改、权限授予/回收操作
    IF tg_tag IN ('CREATE ROLE', 'CREATE USER', 'ALTER ROLE', 'ALTER USER', 'GRANT', 'REVOKE', 'DROP ROLE', 'DROP USER') THEN
        INSERT INTO role_operation_audit(op_tag, object_type)
        VALUES (tg_tag, 'role/user');
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

3. 绑定事件触发器，生效审计规则：

-- 捕获常规DDL执行完成事件
CREATE EVENT TRIGGER etg_audit_role_ddl
ON ddl_command_end
EXECUTE FUNCTION audit_role_operation();

-- 捕获对象删除事件，补全删除对象的详细信息
CREATE EVENT TRIGGER etg_audit_role_drop
ON sql_drop
EXECUTE FUNCTION audit_role_operation();

配置完成后，所有用户/角色相关的变更操作都会自动记录到role_operation_audit表中，包含操作时间、操作人、客户端地址、操作类型、执行的SQL原文，满足数据安全监控要求。该方案需要超级用户权限创建事件触发器。

方案2：使用pgAudit审计扩展（官方标准审计方案） #

pgAudit 是 PostgreSQL 官方维护的专业审计扩展，相比自定义事件触发器，审计记录更严谨、抗绕过能力更强，适合等保、合规类审计场景：

1. 安装与数据库大版本匹配的pgAudit扩展包，修改postgresql.conf配置，将pgAudit加入预加载库列表：

   shared_preload_libraries = 'pgaudit'
   pgaudit.log = 'role,ddl'
   log_line_prefix = '%m [%p] %u@%d client_addr=%h '
   

2. 重启数据库生效配置，在需要开启审计的数据库中执行CREATE EXTENSION pgaudit;即可。
3. 所有角色新增、删除、权限变更操作都会被详细记录到PostgreSQL运行日志中，如果需要将日志存入数据表，可以通过file_fdw外部表映射日志文件，或者配合日志采集工具同步到业务库。

方案3：直接开启原生DDL日志（最轻量，无需写代码） #

如果只需要将操作记录到日志文件、不需要落自定义表，可以直接修改数据库配置开启DDL全量记录，无需重启数据库：

# 记录所有DDL语句，自然包含所有角色、权限变更操作
log_statement = 'ddl'
# 配置日志前缀，携带溯源必要字段：时间、操作人、客户端IP、数据库名
log_line_prefix = '%m [%p] %u@%d from %h '

执行SELECT pg_reload_conf();重载配置后立即生效，所有相关操作都会写入PostgreSQL运行日志文件。

内容的提问来源于stack exchange，提问作者Hari