— 场景：在生产环境中安装和配置pgAudit，实现细粒度审计

— 实施步骤：

— 1. 安装pgAudit
— 从源码安装
$ git clone https://github.com/pgaudit/pgaudit.git
$ cd pgaudit
$ make
$ make install

— 2. 配置PostgreSQL
— 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf

— 添加pgAudit到shared_preload_libraries
shared_preload_libraries = ‘pgaudit’

— 配置审计级别
pgaudit.log = ‘ddl, dml, role, read’

— 配置审计日志格式
pgaudit.log_format = ‘json’

— 配置审计日志详细程度
pgaudit.log_level = ‘log’

— 配置审计参数
pgaudit.log_parameter = on
pgaudit.log_relation = on
pgaudit.log_statement_once = off

— 3. 重启PostgreSQL服务
$ systemctl restart postgresql-18

— 4. 创建pgAudit扩展
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb

fgedudb=# CREATE EXTENSION pgaudit;

— 5. 验证安装
fgedudb=# SELECT * FROM pg_extension WHERE extname = ‘pgaudit’;

— 6. 测试审计
— 执行DDL操作
fgedudb=# CREATE TABLE fgedu_audit_test (id SERIAL PRIMARY KEY, name VARCHAR(50));

— 执行DML操作
fgedudb=# INSERT INTO fgedu_audit_test (name) VALUES (‘test’);

— 执行SELECT操作
fgedudb=# SELECT * FROM fgedu_audit_test;

— 查看审计日志
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log

— 审计日志输出示例：
— 2026-04-07 12:34:56 UTC [12345]: [1-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.fgedu_audit_test,”CREATE TABLE fgedu_audit_test (id SERIAL PRIMARY KEY, name VARCHAR(50));”,
— 2026-04-07 12:34:57 UTC [12345]: [2-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: AUDIT: SESSION,1,2,DML,INSERT,TABLE,public.fgedu_audit_test,”INSERT INTO fgedu_audit_test (name) VALUES (‘test’);”,
— 2026-04-07 12:34:58 UTC [12345]: [3-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: AUDIT: SESSION,1,3,READ,SELECT,TABLE,public.fgedu_audit_test,”SELECT * FROM fgedu_audit_test;”,

— 场景：为敏感表配置详细审计，确保数据操作的可追溯性

— 实施步骤：

— 1. 创建敏感表
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb

fgedudb=# CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
role VARCHAR(20) NOT NULL
);

fgedudb=# CREATE TABLE fgedu_customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20) NOT NULL,
address TEXT,
credit_card VARCHAR(16) NOT NULL
);

— 2. 配置审计策略
— 审计所有DDL操作
fgedudb=# AUDIT DDL;

— 审计敏感表的所有操作
fgedudb=# AUDIT SELECT, INSERT, UPDATE, DELETE ON fgedu_users;
fgedudb=# AUDIT SELECT, INSERT, UPDATE, DELETE ON fgedu_customers;

— 审计权限管理操作
fgedudb=# AUDIT ROLE;

— 3. 测试审计策略
— 执行敏感表操作
fgedudb=# INSERT INTO fgedu_users (name, email, password, role) VALUES (‘Admin’, ‘admin@fgedu.net.cn’, ‘admin123’, ‘admin’);
fgedudb=# UPDATE fgedu_customers SET credit_card = ‘1234567812345678’ WHERE id = 1;
fgedudb=# SELECT * FROM fgedu_customers WHERE credit_card LIKE ‘1234%’;

— 执行权限操作
fgedudb=# CREATE ROLE fgedu_readonly;
fgedudb=# GRANT SELECT ON fgedu_users TO fgedu_readonly;

— 查看审计日志
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log

— 审计日志输出示例：
— 2026-04-07 12:34:56 UTC [12345]: [1-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: AUDIT: OBJECT,1,1,INSERT,TABLE,public.fgedu_users,”INSERT INTO fgedu_users (name, email, password, role) VALUES (‘Admin’, ‘admin@fgedu.net.cn’, ‘admin123’, ‘admin’);”,
— 2026-04-07 12:34:57 UTC [12345]: [2-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: AUDIT: OBJECT,1,2,UPDATE,TABLE,public.fgedu_customers,”UPDATE fgedu_customers SET credit_card = ‘1234567812345678’ WHERE id = 1;”,
— 2026-04-07 12:34:58 UTC [12345]: [3-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: AUDIT: OBJECT,1,3,SELECT,TABLE,public.fgedu_customers,”SELECT * FROM fgedu_customers WHERE credit_card LIKE ‘1234%’;”,
— 2026-04-07 12:34:59 UTC [12345]: [4-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: AUDIT: SESSION,1,4,ROLE,CREATE ROLE,,”CREATE ROLE fgedu_readonly;”,
— 2026-04-07 12:35:00 UTC [12345]: [5-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: AUDIT: SESSION,1,5,ROLE,GRANT,,”GRANT SELECT ON fgedu_users TO fgedu_readonly;”,

— 场景：分析审计日志，检测潜在的安全事件

— 实施步骤：

— 1. 收集审计日志
— 确保审计日志已启用并配置正确

— 2. 分析审计日志
— 使用pgBadger分析审计日志
$ pgbadger -o /var/www/html/audit_analysis.html /postgresql/fgdata/log/postgresql-*.log

— 3. 手动分析审计日志
— 提取敏感操作
$ grep -E “AUDIT.*(DELETE|UPDATE|GRANT|REVOKE)” /postgresql/fgdata/log/postgresql-*.log

— 提取特定用户的操作
$ grep -E “user=fgedu_admin.*AUDIT” /postgresql/fgdata/log/postgresql-*.log

— 提取特定表的操作
$ grep -E “TABLE,public.fgedu_customers.*AUDIT” /postgresql/fgdata/log/postgresql-*.log

— 4. 检测异常操作
— 检测大量DELETE操作
$ grep -c “AUDIT.*DELETE” /postgresql/fgdata/log/postgresql-2026-04-07_*.log

— 检测非工作时间的操作
$ grep -E “(2[23]|[01][0-9]):[0-5][0-9]:[0-5][0-9].*AUDIT” /postgresql/fgdata/log/postgresql-*.log

— 检测权限变更
$ grep -E “AUDIT.*(GRANT|REVOKE)” /postgresql/fgdata/log/postgresql-*.log

— 5. 创建审计分析脚本
$ vi /postgresql/scripts/audit_analysis.sh

#!/bin/bash
# audit_analysis.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

LOG_DIR=”/postgresql/fgdata/log”
OUTPUT_DIR=”/var/www/html/audit_reports/$(date +%Y-%m-%d)”

mkdir -p “$OUTPUT_DIR”

— 分析审计日志
pgbadger -o “$OUTPUT_DIR/audit_analysis.html” “$LOG_DIR”/postgresql-*.log

— 检测异常操作
echo “=== 异常操作检测 ===” > “$OUTPUT_DIR/anomalies.txt”
echo “\n1. 大量DELETE操作：” >> “$OUTPUT_DIR/anomalies.txt”
grep -c “AUDIT.*DELETE” “$LOG_DIR”/postgresql-$(date +%Y-%m-%d)_*.log >> “$OUTPUT_DIR/anomalies.txt”

echo “\n2. 非工作时间操作：” >> “$OUTPUT_DIR/anomalies.txt”
grep -E “(2[23]|[01][0-9]):[0-5][0-9]:[0-5][0-9].*AUDIT” “$LOG_DIR”/postgresql-$(date +%Y-%m-%d)_*.log >> “$OUTPUT_DIR/anomalies.txt”

echo “\n3. 权限变更：” >> “$OUTPUT_DIR/anomalies.txt”
grep -E “AUDIT.*(GRANT|REVOKE)” “$LOG_DIR”/postgresql-$(date +%Y-%m-%d)_*.log >> “$OUTPUT_DIR/anomalies.txt”

echo “\n4. 敏感表操作：” >> “$OUTPUT_DIR/anomalies.txt”
grep -E “TABLE,public.fgedu_customers.*AUDIT” “$LOG_DIR”/postgresql-$(date +%Y-%m-%d)_*.log >> “$OUTPUT_DIR/anomalies.txt”

— 设置定时任务
$ crontab -e

0 1 * * * /postgresql/scripts/audit_analysis.sh

— 6. 配置告警
— 创建告警脚本
$ vi /postgresql/scripts/audit_alert.sh

#!/bin/bash
# audit_alert.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

LOG_FILE=”/postgresql/fgdata/log/postgresql-$(date +%Y-%m-%d)_*.log”

— 检测大量DELETE操作
DELETE_COUNT=$(grep -c “AUDIT.*DELETE” “$LOG_FILE”)
if [ “$DELETE_COUNT” -gt 10 ]; then
echo “检测到大量DELETE操作：$DELETE_COUNT次” | mail -s “PostgreSQL审计告警” admin@fgedu.net.cn
fi

— 检测权限变更
PERMISSION_COUNT=$(grep -c “AUDIT.*(GRANT|REVOKE)” “$LOG_FILE”)
if [ “$PERMISSION_COUNT” -gt 0 ]; then
echo “检测到权限变更操作：$PERMISSION_COUNT次” | mail -s “PostgreSQL审计告警” admin@fgedu.net.cn
fi

— 设置定时任务
$ crontab -e

*/15 * * * * /postgresql/scripts/audit_alert.sh

— 1. 审计日志不记录问题
— 症状：
— – 预期的操作没有被记录到审计日志中

— 排查步骤：
— – 检查pgAudit是否已安装
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT * FROM pg_extension WHERE extname = ‘pgaudit’;”

— – 检查pgAudit是否已加载
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW shared_preload_libraries;”

— – 检查审计配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW pgaudit.log;”

— – 检查审计策略
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT * FROM pg_pgaudit_audit_log;”

— 2. 审计性能问题
— 症状：
— – 数据库性能下降
— – 审计日志过大

— 排查步骤：
— – 检查审计配置，减少审计范围
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW pgaudit.log;”

— – 检查审计日志大小
$ du -h /postgresql/fgdata/log/

— – 调整审计日志轮转设置
$ vi /postgresql/fgdata/postgresql.conf
log_rotation_size = 50MB

— 3. 审计日志分析问题
— 症状：
— – 审计日志难以分析
— – 无法提取有用信息

— 排查步骤：
— – 配置审计日志格式为json
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW pgaudit.log_format;”

— – 使用pgBadger分析审计日志
$ pgbadger -o audit_report.html /postgresql/fgdata/log/postgresql-*.log

— – 创建自定义分析脚本
$ vi /postgresql/scripts/audit_analyzer.py

— 1. 审计规划
— – 明确审计目标和范围
— – 制定审计策略和流程
— – 确定审计日志的存储和保留策略
— – 定期审查审计策略的有效性

— 2. 审计配置
— – 合理配置审计级别，避免过度审计
— – 使用基于对象的审计，精确控制审计范围
— – 配置适当的审计日志格式和详细程度
— – 定期更新审计配置，适应业务变化

— 3. 审计日志管理
— – 确保审计日志的安全存储
— – 配置合理的日志轮转和归档策略
— – 定期清理过期的审计日志
— – 备份审计日志，防止丢失

— 4. 审计日志分析
— – 定期分析审计日志，检测潜在的安全事件
— – 使用自动化工具分析审计日志
— – 建立审计日志分析的流程和规范
— – 及时处理审计日志中的异常情况

— 5. 审计响应
— – 建立审计事件的响应流程
— – 对异常审计事件进行及时处理
— – 定期 review 审计响应的有效性
— – 持续改进审计响应流程

— 6. 合规与审计
— – 确保审计策略满足相关合规要求
— – 定期进行合规审计
— – 记录审计过程和结果
— – 持续改进审计体系

— 7. 培训与意识
— – 对数据库管理员进行审计相关培训
— – 提高开发人员的审计意识
— – 定期更新审计知识和技能
— – 建立审计文化