印刻数据库

外观

PostgreSQL 审计日志配置 ​

pgaudit 扩展 ​

pgaudit 是 PostgreSQL 最常用的审计扩展，提供了细粒度的审计日志功能。

1. 安装与配置 ​

bash
# 在 Ubuntu/Debian 上安装 pgaudit
apt-get install postgresql-15-pgaudit

# 在 RedHat/CentOS 上安装 pgaudit
yum install postgresql15-pgaudit
sql
-- 修改 postgresql.conf 配置文件
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'read,write'
pgaudit.log_catalog = on
pgaudit.log_parameter = on
pgaudit.log_level = log
pgaudit.log_client = off

# 重启 PostgreSQL 服务
sudo systemctl restart postgresql

2. 启用扩展 ​

sql
-- 创建 pgaudit 扩展
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- 验证扩展安装
SELECT extname, extversion FROM pg_extension WHERE extname = 'pgaudit';

审计策略配置 ​

1. 基本审计配置 ​

sql
-- 审计所有读取操作
ALTER SYSTEM SET pgaudit.log = 'read';

-- 审计所有写入操作
ALTER SYSTEM SET pgaudit.log = 'write';

-- 审计所有函数执行
ALTER SYSTEM SET pgaudit.log = 'function';

-- 审计所有 DDL 操作
ALTER SYSTEM SET pgaudit.log = 'ddl';

-- 审计所有 DML 操作
ALTER SYSTEM SET pgaudit.log = 'dml';

-- 审计所有操作
ALTER SYSTEM SET pgaudit.log = 'all';

-- 组合审计类型
ALTER SYSTEM SET pgaudit.log = 'read,write,function';

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

2. 审计规则配置 ​

sql
-- 配置审计角色
CREATE ROLE audit_role;

-- 授予审计权限
GRANT SELECT ON pg_catalog.pg_audit_log TO audit_role;

-- 创建审计策略，仅审计特定用户
ALTER SYSTEM SET pgaudit.role = 'audit_role';

-- 配置审计日志格式
ALTER SYSTEM SET pgaudit.log_format = 'json';

-- 配置审计日志级别
ALTER SYSTEM SET pgaudit.log_level = 'log';

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

3. 细粒度审计 ​

sql
-- 审计特定数据库
ALTER DATABASE mydb SET pgaudit.log = 'all';

-- 审计特定用户
ALTER ROLE myuser SET pgaudit.log = 'all';

-- 审计特定表的操作
-- 创建审计策略，仅审计 sensitive_data 表的操作
CREATE POLICY audit_sensitive_data ON sensitive_data
  FOR ALL
  USING (true);

审计日志查询与分析 ​

1. 查看审计日志 ​

bash
# 查看 PostgreSQL 日志文件
tail -f /var/log/postgresql/postgresql-15-main.log

# 使用 grep 过滤审计日志
grep -i audit /var/log/postgresql/postgresql-15-main.log

2. 使用 pg_audit 视图 ​

sql
-- 查看审计日志视图
SELECT * FROM pg_audit_log;

-- 过滤特定操作类型的审计日志
SELECT * FROM pg_audit_log WHERE command_tag = 'SELECT';

-- 过滤特定用户的审计日志
SELECT * FROM pg_audit_log WHERE session_user_name = 'myuser';

-- 按时间范围查询审计日志
SELECT * FROM pg_audit_log 
WHERE statement_timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-01-02 00:00:00';

3. 审计日志分析工具 ​

bash
# 使用 pgAuditAnalyzer 分析审计日志
git clone https://github.com/pgaudit/pgaudit-analyzer.git
cd pgaudit-analyzer
python pgaudit_analyzer.py -f /var/log/postgresql/postgresql-15-main.log -o audit_report.html

审计日志性能优化 ​

1. 日志级别优化 ​

sql
-- 仅审计关键操作，减少日志量
ALTER SYSTEM SET pgaudit.log = 'ddl,write';

-- 禁用目录操作审计
ALTER SYSTEM SET pgaudit.log_catalog = off;

-- 禁用参数审计
ALTER SYSTEM SET pgaudit.log_parameter = off;

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

2. 日志存储优化 ​

bash
# 配置日志轮转
# 修改 /etc/logrotate.d/postgresql-common
/var/log/postgresql/postgresql-15-main.log {
    weekly
    rotate 10
    compress
    delaycompress
    missingok
    notifempty
    create 0640 postgres adm
    sharedscripts
    postrotate
        /usr/lib/postgresql/15/bin/pg_ctl reload -D /var/lib/postgresql/15/main > /dev/null 2>&1
    endscript
}

# 配置日志归档
# 修改 postgresql.conf
archive_mode = on
archive_command = 'cp %p /pgbackup/archive/%f'

3. 审计策略优化 ​

sql
-- 仅审计敏感表
CREATE TABLE sensitive_data (
    id SERIAL PRIMARY KEY,
    data TEXT
);

-- 使用行级安全策略实现细粒度审计
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY audit_sensitive_data ON sensitive_data
  FOR ALL
  USING (true);

审计日志最佳实践 ​

1. 审计策略设计 ​

  • 明确审计目标：根据合规要求确定需要审计的操作类型
  • 最小审计原则：仅审计必要的操作，避免过度审计
  • 分层审计：根据数据敏感度设计不同的审计策略
  • 定期审查：定期审查审计策略的有效性

2. 日志管理 ​

  • 安全存储：确保审计日志的完整性和保密性
  • 定期备份：定期备份审计日志，防止日志丢失
  • 日志轮转：配置合理的日志轮转策略，避免日志文件过大
  • 长期归档：根据合规要求配置日志的长期归档

3. 合规性考虑 ​

  • GDPR：审计数据访问和处理操作
  • PCI DSS：审计支付卡数据的访问和修改
  • HIPAA：审计医疗数据的访问和处理
  • SOX：审计财务数据的访问和修改

4. 监控与告警 ​

bash
# 使用 pgBadger 分析审计日志
pgbadger /var/log/postgresql/postgresql-15-main.log -o pgbadger_report.html

# 使用 ELK Stack 集中管理审计日志
# 配置 Filebeat 收集 PostgreSQL 日志
filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /var/log/postgresql/postgresql-15-main.log
  fields:
    log_type: postgresql_audit

常见问题（FAQ） ​

Q1：pgaudit 会影响数据库性能吗？ ​

A1：是的，pgaudit 会带来一定的性能开销，特别是在高并发环境下。建议：

  • 仅审计必要的操作类型
  • 避免过度审计
  • 配置合理的日志级别
  • 定期清理审计日志

Q2：如何审计特定表的操作？ ​

A2：可以结合使用 pgaudit 和行级安全策略，或者使用触发器实现细粒度审计。

Q3：如何确保审计日志的完整性？ ​

A3：

  • 配置日志文件权限，仅允许 root 和 postgres 用户访问
  • 使用日志签名或哈希确保日志不被篡改
  • 定期备份审计日志到安全位置
  • 考虑使用外部日志管理系统

Q4：如何查询历史审计日志？ ​

A4：

  • 使用 pg_audit_log 视图查询当前日志
  • 使用日志分析工具查询归档日志
  • 考虑使用集中式日志管理系统

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

A5：

  • 使用 logrotate 配置日志轮转
  • 设置合理的保留期限（如 90 天或 180 天）
  • 定期归档旧日志到长期存储
  • 根据合规要求调整保留期限

Q6：如何审计 DDL 操作？ ​

A6：

  • 配置 pgaudit.log = 'ddl'
  • 或者配置 pgaudit.log = 'all' 审计所有操作
  • 可以结合使用 event triggers 实现更细粒度的 DDL 审计