PostgreSQL的扩展（extensions）-常用的扩展之pgAudit

PostgreSQL的扩展（extensions）-常用的扩展之pgAudit

基础信息
OS版本：Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：16.2
pg软件目录：/home/pg16/soft
pg数据目录：/home/pg16/data
端口：5777

pgAudit 是 PostgreSQL 的一个关键安全扩展，提供详细的数据库审计功能，能够记录数据库中的所有操作，满足合规性要求（如 SOX、PCI DSS、HIPAA 等）。

一 pgAudit 核心功能

1.1 审计能力概览

• 对象级审计：跟踪特定表/视图的操作
• 会话级审计：记录所有会话的SQL语句
• 语句级审计：按语句类型(DDL/DML等)审计
• 参数化审计：记录带参数的语句

1.2 审计日志内容

• 执行的操作类型(SELECT/INSERT/UPDATE/DELETE等)
• 操作对象(表名、视图名等)
• 执行时间戳
• 执行用户
• 客户端连接信息
• 语句文本(可选)

二 安装 pgAudit

pgAudit 的安装通常涉及到扩展的编译和加载。以下是安装过程的大概步骤（可能会根据您的操作系统和 PostgreSQL 的版本有所不同）：

1.1 安装

1. 下载 pgAudit 扩展源码：

下载网址：https://github.com/pgaudit/pgaudit/releases

2. 编译扩展

[pg16@test resource]$ unzip pgaudit-16.0.zip 
[pg16@test resource]$ cd pgaudit-16.0/
[pg16@test pgaudit-16.0]$ make install USE_PGXS=1

3. 在 postgresql.conf 文件中配置：

   加载 pgAudit 扩展需要在 postgresql.conf 文件中进行配置。将 pgaudit 添加到 shared_preload_libraries 配置项中：

–修改postgresql.conf 文件

shared_preload_libraries = 'pgaudit,pg_stat_statements,auto_explain'    # (change requires restart)

–系统上修改，并重启pg

postgres=# alter system set shared_preload_libraries=pgaudit,pg_stat_kcache,pg_stat_statements,auto_explain;
ALTER SYSTEM
postgres=# \q
[pg16@test ~]$ pg_ctl restart

4. 创建扩展：

使用 CREATE EXTENSION 命令在你的数据库中创建 pgAudit 扩展。

postgres=# \c white postgres
You are now connected to database "white" as user "postgres".
white=# 
white=# CREATE EXTENSION pgaudit;
CREATE EXTENSION
white=# select * from pg_extension;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 14270 | plpgsql            |       10 |           11 | f              | 1.0        |           | 
 16726 | pg_repack          |       10 |         2200 | f              | 1.5.0      |           | 
 16975 | pg_stat_statements |       10 |         2200 | t              | 1.10       |           | 
 17048 | pgaudit            |       10 |         2200 | t              | 16.0       |           | 
(4 rows)

1.2 主要配置参数(postgresql.conf)

# 基本设置
shared_preload_libraries = 'pgaudit'  # 必须设置

# 日志输出控制
pgaudit.log = 'all'                  # 审计所有语句
pgaudit.log_client = on              # 输出到客户端
pgaudit.log_level = log              # 日志级别
pgaudit.log_parameter = on           # 记录参数值
pgaudit.log_relation = on            # 记录对象级操作

三 审计策略配置

3.1 全局审计设置

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

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

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

3.2 对象级审计

-- 审计特定表的所有操作
ALTER TABLE sensitive_data SET pgaudit.log = 'all';

-- 审计表的写操作
ALTER TABLE users SET pgaudit.log = 'write';

-- 查看当前审计设置
SELECT relname, reloptions FROM pg_class 
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
AND reloptions IS NOT NULL;

3.3 角色级审计

-- 审计管理员角色的所有操作
ALTER ROLE admin SET pgaudit.log = 'all';

-- 审计特定用户的所有连接
ALTER ROLE auditor SET pgaudit.log_client = on;

四 日志分析与解读

4.1 典型审计日志条目

2025-04-20 14:25:33 UTC [user=admin db=mydb] LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE audit_test(id serial primary key, data text),<not logged>
2025-04-20 14:26:45 UTC [user=app_user db=mydb] LOG:  AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.sensitive_data,INSERT INTO sensitive_data VALUES(1, 'confidential'),<not logged>

2. 日志字段说明

字段	说明
AUDIT	标识审计条目
SESSION/OBJECT	审计级别
会话ID	唯一会话标识
子会话ID	子事务标识
操作类型	DDL/READ/WRITE等
命令标签	SQL命令类型
对象类型	TABLE/VIEW等
对象名	schema.object格式
语句文本	实际执行的SQL
参数	参数值(如果启用)

五 高级功能与技巧

5.1 自定义日志格式

# 在postgresql.conf中配置
log_line_prefix = '%m [user=%u db=%d app=%a] '
pgaudit.log = 'all'
pgaudit.log_relation = on
pgaudit.log_statement_once = off

5.2 与系统日志集成

# 配置syslog输出
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

5.3 性能优化建议

# 减少日志量配置示例
pgaudit.log = 'ddl, write'
pgaudit.log_relation = off
pgaudit.log_parameter = off
pgaudit.log_statement_once = on

六 实际应用场景

6.1 合规性审计

-- 审计所有敏感数据访问
ALTER TABLE patients SET pgaudit.log = 'all';
ALTER TABLE financial_records SET pgaudit.log = 'all';

-- 审计所有权限变更
ALTER SYSTEM SET pgaudit.log = 'role';

6.2 安全事件调查

-- 查找特定表的可疑操作
SELECT log_time, session_user_name, database_name, statement 
FROM pg_log 
WHERE command_tag = 'UPDATE' 
AND object_name = 'public.users'
AND log_time > '2023-08-01';

6.3 用户行为分析

-- 分析用户活动模式
SELECT session_user_name, command_tag, count(*)
FROM pg_log 
WHERE log_time > current_date - interval '7 days'
GROUP BY 1, 2
ORDER BY 3 DESC;

pgAudit 是 PostgreSQL 生态中最强大的审计解决方案之一，通过合理配置可以满足从基本安全监控到严格合规性要求的各种审计需求。建议结合日志分析工具（如 ELK Stack）构建完整的审计工作流。

谨记：心存敬畏，行有所止。