PostgreSQL如何获取当前用户对表的所有查询用于审计？

方法1：修改PostgreSQL核心日志配置 #

这是原生的直接方案，无需额外扩展，通过调整日志参数捕获所有SQL语句：

1. 编辑PostgreSQL配置文件postgresql.conf（路径通常为/var/lib/postgresql/<版本>/main/postgresql.conf或/usr/local/pgsql/data/postgresql.conf），修改以下参数：

   • log_statement = 'all'：记录所有SQL语句（包括SELECT、ALTER、INSERT等全类型操作）
   • log_min_duration_statement = 0：取消耗时过滤，记录所有执行语句
   • log_connections = on：记录用户连接事件
   • log_disconnections = on：记录用户断开事件
   • log_line_prefix = '%t [%p]: [%c-%l] user=%u,db=%d,app=%a '：定义日志前缀，强制包含时间戳、用户、数据库等关键审计字段

2. 重启PostgreSQL服务使配置生效：

   # Systemd系统
   sudo systemctl restart postgresql
   # Init.d系统
   sudo service postgresql restart
   

3. 查看日志文件（默认路径为/var/log/postgresql/postgresql-<版本>-main.log），日志条目会呈现为：

   2024-05-20 14:30:00 UTC [1234]: [1-1] user=user1,db=mydb,app=psql LOG: statement: SELECT * FROM table1;
   2024-05-20 14:30:10 UTC [1234]: [2-1] user=user1,db=mydb,app=psql LOG: statement: ALTER TABLE table1 ADD COLUMN new_col INT;

   可通过grep快速筛选目标记录：

   grep 'user=user1' /var/log/postgresql/postgresql-14-main.log
   grep 'table1' /var/log/postgresql/postgresql-14-main.log
   

方法2：使用pg_stat_statements扩展 #

该扩展可捕获并统计历史执行的SQL语句，适合回溯查询记录的场景：

1. 编辑postgresql.conf，添加扩展到预加载库：

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all
   track_activity_query_size = 4096  # 增大SQL语句捕获长度，按需调整
   

2. 重启PostgreSQL后，在目标数据库中创建扩展：

   CREATE EXTENSION pg_stat_statements;
   

3. 查询pg_stat_statements视图获取结构化审计数据，例如筛选用户user1涉及table1的语句：

   SELECT
     query_start AS 执行时间,
     usename AS 用户,
     query AS 执行语句
   FROM pg_stat_statements
   JOIN pg_user ON pg_stat_statements.userid = pg_user.usesysid
   WHERE usename = 'user1' AND query LIKE '%table1%';
   

方法3：使用pgAudit扩展（精细化审计） #

如果需要更精准的审计控制（如仅审计特定表、特定操作类型），可使用专业审计扩展pgAudit：

1. 安装pgAudit（以Debian/Ubuntu为例）：

   sudo apt install postgresql-<版本>-pgaudit
   

2. 编辑postgresql.conf配置：

   shared_preload_libraries = 'pgaudit'
   pgaudit.log = 'ddl, write, read'  # 指定审计操作类型：DDL、写入、读取
   pgaudit.log_parameter = on  # 记录SQL语句中的参数值
   pgaudit.log_relation = on  # 强制记录涉及的表名
   

3. 重启PostgreSQL后，日志会生成结构化的审计条目，包含完整的用户、时间、操作对象和语句信息。

内容的提问来源于stack exchange，提问作者flamixx