PostgreSQL 权限审计

核心概念

PostgreSQL 权限审计是确保数据库安全的重要机制，用于记录和监控数据库中的权限变更和访问行为。权限审计涉及以下核心概念：

• 审计日志：记录数据库中发生的权限相关事件，包括用户登录、权限变更、对象访问等
• 审计策略：定义需要审计的事件类型、对象范围和用户范围
• 审计上下文：包含审计事件的详细信息，如时间、用户、操作类型、对象名称等
• 审计分析：对审计日志进行分析，识别潜在的安全风险和违规行为

审计日志配置

1. 基础审计配置

PostgreSQL 提供了多种审计日志配置选项，以下是基础的审计配置方法：

-- 开启审计日志记录
ALTER SYSTEM SET log_connections = 'on';       -- 记录客户端连接事件
ALTER SYSTEM SET log_disconnections = 'on';    -- 记录客户端断开连接事件
ALTER SYSTEM SET log_statement = 'ddl';       -- 记录DDL语句（创建、修改、删除对象）
ALTER SYSTEM SET log_commands = 'on';         -- 记录所有命令（生产环境谨慎使用）

-- 配置审计日志格式
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';

-- 重新加载配置使其生效
SELECT pg_reload_conf();

2. 权限变更审计

针对权限变更的专门审计配置：

-- 记录权限变更语句
ALTER SYSTEM SET log_statement = 'mod';       -- 记录DDL和权限变更语句

-- 或更详细的配置
ALTER SYSTEM SET log_statement = 'all';       -- 记录所有语句（生产环境谨慎使用）

-- 记录超级用户操作
ALTER SYSTEM SET log_superuser_commands = 'on';

-- 重新加载配置
SELECT pg_reload_conf();

3. 使用 pgaudit 扩展（推荐）

pgaudit 是 PostgreSQL 的官方审计扩展，提供更全面的审计功能：

-- 安装 pgaudit 扩展
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- 配置 pgaudit
ALTER SYSTEM SET pgaudit.log = 'READ,WRITE,DML,DDL';  -- 审计读写、DML和DDL操作
ALTER SYSTEM SET pgaudit.log_catalog = 'on';           -- 审计系统表操作
ALTER SYSTEM SET pgaudit.log_relation = 'on';         -- 审计关系级别的操作
ALTER SYSTEM SET pgaudit.log_parameter = 'on';        -- 审计语句参数
ALTER SYSTEM SET pgaudit.log_level = 'notice';        -- 审计日志级别

-- 重新加载配置
SELECT pg_reload_conf();

4. 配置基于角色的审计

-- 为特定角色配置审计
ALTER ROLE audited_role SET pgaudit.log = 'ALL';

-- 创建审计专用角色
CREATE ROLE auditor NOLOGIN;
GRANT SELECT ON pg_audit_log TO auditor;

审计策略创建

1. 基于对象的审计策略

-- 使用 pgaudit 创建对象级审计策略
-- 审计特定表的所有操作
SELECT pgaudit.create_policy(
    policy_name := 'audit_important_table',
    object_type := 'TABLE',
    object_name := 'important_table',
    statement_types := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE'],
    roles := ARRAY['public']
);

2. 基于语句类型的审计策略

-- 审计所有 DDL 语句
ALTER SYSTEM SET pgaudit.log = 'DDL';

-- 审计特定类型的 DDL 语句
ALTER SYSTEM SET pgaudit.log = 'CREATE,ALTER,DROP';

-- 审计权限变更语句
ALTER SYSTEM SET pgaudit.log = 'ROLE';

审计日志查询与分析

1. 查询审计日志

-- 查询审计日志（通过 pgAudit 扩展）
SELECT * FROM pg_audit_log 
WHERE action_tstamp > NOW() - INTERVAL '1 hour' 
ORDER BY action_tstamp DESC;

-- 查询系统日志中的审计信息
SELECT * FROM pg_log 
WHERE message LIKE '%audit%' 
ORDER BY log_time DESC;

2. 审计日志分析示例

-- 分析用户登录失败次数
SELECT 
    client_addr, 
    count(*) as failed_logins
FROM pg_log 
WHERE message LIKE '%authentication failed%' 
  AND log_time > NOW() - INTERVAL '24 hours'
GROUP BY client_addr 
ORDER BY failed_logins DESC;

-- 分析权限变更操作
SELECT 
    username, 
    action_tstamp, 
    statement
FROM pg_audit_log 
WHERE class IN ('ROLE', 'DDL') 
  AND action_tstamp > NOW() - INTERVAL '7 days'
ORDER BY action_tstamp DESC;

权限审计最佳实践

1. 生产环境配置建议

1. 分层审计策略：

   • 对所有用户：审计登录/登出、权限变更
   • 对敏感数据：审计所有读写操作
   • 对普通数据：审计修改操作

2. 合理配置审计级别：

   • 生产环境避免使用 log_statement = 'all'，以免影响性能
   • 优先使用 pgaudit 扩展，提供更细粒度的审计控制

3. 审计日志管理：

   • 配置合适的日志轮换策略
   • 将审计日志存储在安全位置，防止篡改
   • 定期备份审计日志

4. 监控与告警：

   • 监控审计日志中的异常事件
   • 对频繁的登录失败、权限变更等事件配置告警

2. 常见问题处理

• 问题1：审计日志过大 解决方法：

  -- 调整日志轮换策略
  ALTER SYSTEM SET log_rotation_age = '1d';
  ALTER SYSTEM SET log_rotation_size = '100MB';
  
  -- 只审计必要的事件
  ALTER SYSTEM SET pgaudit.log = 'READ,WRITE,DML,DDL';

• 问题2：审计性能影响 解决方法：

  -- 调整审计级别，减少审计事件数量
  ALTER SYSTEM SET pgaudit.log = 'DDL,ROLE';
  
  -- 关闭不必要的审计选项
  ALTER SYSTEM SET pgaudit.log_parameter = 'off';

审计日志分析工具

除了手动查询审计日志外，还可以使用以下工具进行审计日志分析：

1. pgBadger：生成可视化的审计日志报告

   pgbadger -f stderr postgresql.log -o audit_report.html

2. ELK Stack：将审计日志导入 Elasticsearch，使用 Kibana 进行可视化分析

3. Splunk：企业级日志管理和分析平台，支持 PostgreSQL 审计日志分析

4. Graylog：开源日志管理平台，可用于集中管理和分析 PostgreSQL 审计日志

常见问题（FAQ）

Q1：如何启用详细的权限审计？

解决方案：

使用 pgaudit 扩展可以实现详细的权限审计：

-- 安装 pgaudit 扩展
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- 配置全面的审计
ALTER SYSTEM SET pgaudit.log = 'READ,WRITE,DML,DDL,ROLE';
ALTER SYSTEM SET pgaudit.log_catalog = 'on';
ALTER SYSTEM SET pgaudit.log_relation = 'on';
SELECT pg_reload_conf();

Q2：如何审计特定用户的所有操作？

解决方案：

可以通过角色级别的审计配置或使用 pgaudit 策略实现：

-- 方法1：为特定用户设置审计配置
ALTER ROLE specific_user SET pgaudit.log = 'ALL';

-- 方法2：使用 pgaudit 策略
SELECT pgaudit.create_policy(
    policy_name := 'audit_specific_user',
    object_type := 'ROLE',
    object_name := 'specific_user',
    statement_types := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'DDL'],
    roles := ARRAY['specific_user']
);

Q3：如何审计敏感数据的访问？

解决方案：

针对敏感表配置专门的审计策略：

-- 审计敏感表的所有操作
SELECT pgaudit.create_policy(
    policy_name := 'audit_sensitive_data',
    object_type := 'TABLE',
    object_name := 'sensitive_table',
    statement_types := ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE'],
    roles := ARRAY['public']
);

-- 或使用系统配置
ALTER SYSTEM SET pgaudit.log = 'READ,WRITE';
ALTER SYSTEM SET pgaudit.log_relation = 'on';
SELECT pg_reload_conf();

Q4：如何防止审计日志被篡改？

解决方案：

1. 将审计日志存储在只读位置
2. 配置日志签名机制
3. 使用外部日志管理系统（如 ELK Stack）集中存储审计日志
4. 定期备份审计日志到安全位置
5. 限制对审计日志的访问权限

-- 限制对审计日志表的访问
REVOKE ALL ON pg_audit_log FROM public;
GRANT SELECT ON pg_audit_log TO auditor;

Q5：如何清理旧的审计日志？

解决方案：

-- 使用 pgaudit 清理旧日志
SELECT pgaudit.purge_audit_log(
    older_than := NOW() - INTERVAL '90 days'
);

-- 或通过系统日志轮换机制自动清理
ALTER SYSTEM SET log_rotation_age = '7d';
ALTER SYSTEM SET log_rotation_size = '100MB';
ALTER SYSTEM SET log_truncate_on_rotation = 'on';
SELECT pg_reload_conf();