印刻数据库

外观

PostgreSQL 审计日志管理 ​

PostgreSQL 审计日志管理是数据库安全运维的重要组成部分，用于记录数据库的各种操作活动，包括用户登录、DML 操作、DDL 操作等，便于安全审计和故障排查。

审计日志实现方式 ​

1. 内置审计功能 ​

PostgreSQL 内置了基础的审计功能，可以通过配置参数启用。

配置步骤 ​

sql
-- 1. 设置审计日志级别
-- none：不记录任何语句
-- ddl：记录所有 DDL 语句
-- mod：记录所有 DDL 和修改数据的 DML 语句
-- all：记录所有语句
ALTER SYSTEM SET log_statement = 'mod';

-- 2. 设置日志格式
ALTER SYSTEM SET log_destination = 'csvlog';
ALTER SYSTEM SET logging_collector = 'on';
ALTER SYSTEM SET log_directory = 'pg_log';
ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log';

-- 3. 设置日志轮转
ALTER SYSTEM SET log_rotation_age = '1d';
ALTER SYSTEM SET log_rotation_size = '100MB';

-- 4. 记录详细的连接信息
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';

-- 5. 重启数据库使配置生效

2. pgaudit 扩展（推荐） ​

pgaudit 是 PostgreSQL 的一个强大审计扩展，提供更细粒度的审计控制。

安装和配置 ​

sql
-- 1. 安装 pgaudit 扩展（以 PostgreSQL 14 为例）
-- 在 RHEL/CentOS 上：yum install postgresql14-contrib postgresql14-pgaudit
-- 在 Debian/Ubuntu 上：apt install postgresql-14-pgaudit

-- 2. 在 postgresql.conf 中启用 pgaudit
-- shared_preload_libraries = 'pgaudit'

-- 3. 重启数据库后，创建扩展
CREATE EXTENSION pgaudit;

-- 4. 配置 pgaudit 策略
-- 记录所有数据库的所有操作
ALTER SYSTEM SET pgaudit.log = 'all';

-- 记录特定数据库的操作
ALTER DATABASE mydb SET pgaudit.log = 'read, write';

-- 记录特定用户的操作
ALTER ROLE myuser SET pgaudit.log = 'ddl';

-- 5. 配置审计日志格式
ALTER SYSTEM SET pgaudit.log_format = 'csv';
ALTER SYSTEM SET pgaudit.log_catalog = 'on';

细粒度审计策略 ​

sql
-- 1. 创建审计规则
-- 记录特定表的所有操作
ALTER TABLE users SET (pgaudit.log = 'all');

-- 2. 审计特定语句类型
ALTER SYSTEM SET pgaudit.log = 'read, write, ddl, function';

-- 3. 审计角色权限变化
ALTER SYSTEM SET pgaudit.log = 'role';

-- 4. 审计系统管理操作
ALTER SYSTEM SET pgaudit.log = 'admin';

审计日志管理 ​

1. 日志存储与轮转 ​

bash
# 1. 配置 logrotate（推荐）
# 创建 /etc/logrotate.d/postgresql-audit 文件
cat > /etc/logrotate.d/postgresql-audit << EOF
/var/lib/pgsql/14/data/pg_log/postgresql-*.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 0600 postgres postgres
    su postgres postgres
}
EOF

# 2. 手动轮转日志
pg_ctl -D /var/lib/pgsql/14/data logrotate

2. 审计日志分析 ​

sql
-- 1. 查询审计日志（使用内置日志）
SELECT * FROM pg_log WHERE log_message LIKE '%AUDIT: session%' ORDER BY log_time DESC;

-- 2. 使用 pgaudit 扩展查询审计日志
-- pgaudit 日志直接写入 PostgreSQL 标准日志文件

3. 审计日志安全 ​

bash
# 1. 设置日志文件权限
chmod 600 /var/lib/pgsql/14/data/pg_log/*.log
chown postgres:postgres /var/lib/pgsql/14/data/pg_log/*.log

# 2. 配置日志加密传输（如果日志发送到远程服务器）
# 配置 rsyslog 使用 TLS 加密传输日志

最佳实践 ​

1. 生产环境配置建议 ​

  • 合理设置审计级别：根据业务需求选择适当的审计级别，避免过度审计影响性能
  • 使用 pgaudit 扩展：pgaudit 提供更细粒度的审计控制，适合生产环境
  • 配置日志轮转：定期轮转日志，避免日志文件过大
  • 设置日志保留期限：根据合规要求设置日志保留期限，一般建议至少保留 90 天
  • 分离审计日志：将审计日志存储在独立的磁盘或远程服务器上，防止日志被篡改

2. 性能优化 ​

sql
-- 1. 优化审计日志写入性能
ALTER SYSTEM SET synchronous_commit = 'off';

-- 2. 仅审计必要的操作
ALTER SYSTEM SET pgaudit.log = 'ddl, write';

-- 3. 配置日志缓冲区
ALTER SYSTEM SET log_buffer = '16MB';

3. 安全管理 ​

sql
-- 1. 限制审计日志访问权限
REVOKE ALL ON pg_log FROM PUBLIC;
GRANT SELECT ON pg_log TO audit_admin;

-- 2. 定期检查审计日志
-- 创建定期检查任务，扫描异常操作

常见问题处理 ​

1. 审计日志过大 ​

问题：审计日志增长过快，占用大量磁盘空间

解决方法：

  • 调整审计级别，减少不必要的审计
  • 配置更频繁的日志轮转
  • 增加日志压缩配置
  • 考虑使用外部日志管理系统（如 ELK Stack）

2. 审计日志丢失 ​

问题：审计日志不完整或丢失

解决方法：

  • 检查 logging_collector 参数是否开启
  • 检查 log_directory 目录权限
  • 确保日志磁盘有足够空间
  • 配置 synchronous_commit = 'on' 确保日志写入持久化

3. 审计性能影响 ​

问题：启用审计后数据库性能下降

解决方法：

  • 降低审计级别，仅审计必要操作
  • 优化日志写入性能（如使用 SSD 存储日志）
  • 配置异步日志写入
  • 考虑使用外部审计系统

常见问题（FAQ） ​

Q1：pgaudit 支持哪些 PostgreSQL 版本？ ​

A1：pgaudit 支持 PostgreSQL 9.5 及以上版本，不同版本的 pgaudit 扩展可能有不同的功能和配置选项。建议使用与 PostgreSQL 版本匹配的 pgaudit 扩展。

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

A2：可以使用 pgaudit 扩展的角色级配置：

sql
ALTER ROLE audit_user SET pgaudit.log = 'all';

Q3：如何审计 DDL 操作？ ​

A3：可以使用以下两种方法：

  1. 内置功能：ALTER SYSTEM SET log_statement = 'ddl';
  2. pgaudit 扩展：ALTER SYSTEM SET pgaudit.log = 'ddl';

Q4：如何查看审计日志？ ​

A4：可以通过以下方式查看审计日志：

  1. 直接查看日志文件：cat /var/lib/pgsql/14/data/pg_log/postgresql-2023-01-01_000000.log
  2. 使用 pgAdmin 等图形工具查看日志
  3. 配置日志发送到外部系统（如 ELK Stack）进行集中管理和分析

Q5：如何确保审计日志不被篡改？ ​

A5：可以采取以下措施：

  • 将审计日志存储在只读文件系统上
  • 配置日志发送到远程服务器
  • 使用数字签名验证日志完整性
  • 定期备份审计日志到离线存储

Q6：pgaudit 与内置审计功能有什么区别？ ​

A6：主要区别如下：

  • pgaudit 提供更细粒度的审计控制，可以按表、用户、语句类型等进行审计
  • pgaudit 生成结构化的审计日志，便于分析
  • 内置审计功能配置简单，但功能相对有限
  • pgaudit 支持更多的审计策略和规则

Q7：如何配置审计日志的保留期限？ ​

A7：可以通过以下方式设置：

  1. 使用 logrotate 配置保留期限：rotate 30 表示保留 30 天
  2. 配置外部日志管理系统的保留策略
  3. 定期手动清理过期日志

Q8：如何审计跨数据库操作？ ​

A8：可以使用 pgaudit 扩展的数据库级配置：

sql
ALTER DATABASE db1 SET pgaudit.log = 'all';
ALTER DATABASE db2 SET pgaudit.log = 'all';

或者在全局配置中设置：

sql
ALTER SYSTEM SET pgaudit.log = 'all';