印刻数据库

外观

PostgreSQL 权限审计安全 ​

核心概念 ​

权限审计是数据库安全管理的重要组成部分，用于监控和记录数据库中的权限变更和访问行为。PostgreSQL的权限审计主要涉及以下核心概念：

  • 权限审计：监控和记录数据库对象的权限变更
  • 访问审计：记录用户对数据库对象的访问行为
  • 审计日志：存储审计记录的日志文件
  • 审计策略：定义需要审计的事件和对象
  • 审计分析：对审计日志进行分析，发现安全问题

权限审计配置 ​

1. 使用pgAudit扩展 ​

pgAudit是PostgreSQL的一个审计扩展，提供了细粒度的权限审计功能。

bash
# 安装pgAudit扩展（RHEL/CentOS）
yum install -y postgresql14-contrib postgresql14-pgaudit

# 编辑postgresql.conf，添加pgAudit到shared_preload_libraries
vim /var/lib/pgsql/14/data/postgresql.conf
shared_preload_libraries = 'pgaudit'

# 重启PostgreSQL服务
systemctl restart postgresql-14

# 在数据库中创建pgAudit扩展
psql -d mydb -c "CREATE EXTENSION pgaudit;"

2. 配置审计策略 ​

sql
-- 配置审计日志级别
-- READ：审计SELECT、COPY FROM操作
-- WRITE：审计INSERT、UPDATE、DELETE、TRUNCATE、COPY TO操作
-- FUNCTION：审计函数调用
-- ROLE：审计角色和权限变更
-- DDL：审计数据定义语言操作
-- MISC：审计其他操作
-- MISC_SET：审计SET命令
ALTER SYSTEM SET pgaudit.log = 'READ, WRITE, FUNCTION, ROLE, DDL';

-- 配置审计日志详细程度
-- off：不记录参数
-- on：记录参数
-- 1：记录参数，但对某些类型进行脱敏
ALTER SYSTEM SET pgaudit.log_parameter = 'on';

-- 配置是否审计系统目录
ALTER SYSTEM SET pgaudit.log_catalog = 'on';

-- 配置审计日志格式
-- csv：CSV格式
-- json：JSON格式
-- 默认为csv
ALTER SYSTEM SET pgaudit.log_format = 'csv';

-- 配置审计日志包含的列
ALTER SYSTEM SET pgaudit.log_relation = 'on';

-- 应用配置更改
SELECT pg_reload_conf();

3. 审计特定对象 ​

sql
-- 创建审计策略，只审计特定表
CREATE POLICY audit_orders_policy ON orders
    FOR ALL
    USING (true);

-- 配置pgAudit只审计特定模式
ALTER SYSTEM SET pgaudit.log_schema = 'public';

-- 配置pgAudit只审计特定角色
ALTER SYSTEM SET pgaudit.log_role = 'app_user';

权限审计日志分析 ​

1. 查看审计日志 ​

bash
# 默认审计日志位置
ls -la /var/lib/pgsql/14/data/log/

# 实时查看审计日志
tail -f /var/lib/pgsql/14/data/log/postgresql-2025-01-24.log | grep AUDIT

2. 使用pgBadger分析审计日志 ​

pgBadger是一个PostgreSQL日志分析工具，可以用于分析审计日志。

bash
# 安装pgBadger
yum install -y pgbadger

# 使用pgBadger分析审计日志
pgbadger /var/lib/pgsql/14/data/log/postgresql-2025-01-24.log -o audit_report.html

# 查看生成的报告
open audit_report.html

3. 自定义审计日志查询 ​

sql
-- 查询权限变更记录
SELECT * FROM pg_log WHERE message LIKE '%AUDIT: SESSION%ROLE%' ORDER BY log_time DESC;

-- 查询表访问记录
SELECT * FROM pg_log WHERE message LIKE '%AUDIT: SESSION%READ%orders%' ORDER BY log_time DESC;

-- 查询DDL操作记录
SELECT * FROM pg_log WHERE message LIKE '%AUDIT: SESSION%DDL%' ORDER BY log_time DESC;

权限审计最佳实践 ​

生产环境配置建议 ​

  1. 合理配置审计级别：根据实际需求选择合适的审计级别，避免过度审计影响性能
  2. 定期分析审计日志：建立定期审计日志分析机制，及时发现安全问题
  3. 配置日志轮换：设置合理的日志轮换策略，避免日志文件过大
  4. 保护审计日志：确保审计日志的安全性，防止被篡改或删除
  5. 结合监控系统：将审计日志与监控系统集成，实现实时告警

性能优化建议 ​

sql
-- 配置审计日志缓冲区大小
ALTER SYSTEM SET pgaudit.log_buffer_size = '64kB';

-- 配置审计日志刷新间隔
ALTER SYSTEM SET pgaudit.log_flush_interval = '1s';

-- 只审计特定数据库
ALTER SYSTEM SET pgaudit.log_database = 'mydb';

-- 减少审计日志的详细程度
ALTER SYSTEM SET pgaudit.log_parameter = 'off';

安全建议 ​

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

# 配置审计日志远程存储
# 编辑postgresql.conf
vim /var/lib/pgsql/14/data/postgresql.conf
log_destination = 'syslog'

# 配置syslog将日志发送到远程服务器
vim /etc/rsyslog.conf
*.* @remote-log-server:514

# 重启rsyslog服务
systemctl restart rsyslog

权限审计常见问题 ​

1. 审计日志不生成 ​

bash
# 检查pgAudit是否正确加载
psql -c "SHOW shared_preload_libraries;" | grep pgaudit

# 检查pgAudit扩展是否已创建
psql -c "SELECT * FROM pg_extension WHERE extname = 'pgaudit';"

# 检查审计配置
psql -c "SHOW pgaudit.log;"

2. 审计日志过多 ​

sql
-- 减少审计级别
ALTER SYSTEM SET pgaudit.log = 'ROLE, DDL';

-- 只审计特定对象
ALTER SYSTEM SET pgaudit.log_schema = 'public';

-- 减少审计日志详细程度
ALTER SYSTEM SET pgaudit.log_parameter = 'off';

3. 审计日志性能影响 ​

sql
-- 配置审计日志异步写入
ALTER SYSTEM SET pgaudit.log_async = 'on';

-- 增加审计日志缓冲区大小
ALTER SYSTEM SET pgaudit.log_buffer_size = '128kB';

-- 调整审计日志刷新间隔
ALTER SYSTEM SET pgaudit.log_flush_interval = '5s';

常见问题（FAQ） ​

Q1：如何查看pgAudit的版本？ ​

A1：使用以下命令查看pgAudit的版本：

sql
SELECT extname, extversion FROM pg_extension WHERE extname = 'pgaudit';

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

A2：可以使用以下方法审计特定用户的操作：

sql
-- 方法1：使用pgAudit的log_role参数
ALTER SYSTEM SET pgaudit.log_role = 'app_user';

-- 方法2：使用日志过滤
SELECT * FROM pg_log WHERE username = 'app_user' AND message LIKE '%AUDIT%';

Q3：如何审计权限变更历史？ ​

A3：使用以下查询查看权限变更历史：

sql
SELECT * FROM pg_log WHERE message LIKE '%AUDIT: SESSION%ROLE%' 
ORDER BY log_time DESC;

Q4：如何备份审计日志？ ​

A4：可以使用以下方法备份审计日志：

bash
# 使用rsyslog将日志发送到远程服务器
# 配置定期备份本地日志
0 0 * * * tar -czf /backup/audit-logs/audit-$(date +%Y%m%d).tar.gz /var/lib/pgsql/14/data/log/

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

A5：可以使用以下方法清理旧的审计日志：

bash
# 使用logrotate配置日志轮换
vim /etc/logrotate.d/postgresql
/var/lib/pgsql/14/data/log/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 0600 postgres postgres
    sharedscripts
    postrotate
        /bin/kill -HUP $(cat /var/run/postgresql/14-main.pid 2>/dev/null) 2>/dev/null || true
    endscript
}