如何识别PostgreSQL数据库中查询特定表的用户？

以下是几种实用方案，覆盖实时监控、长期审计等场景：

1. 实时查看当前活跃查询（pg_stat_activity） #

PostgreSQL自带的pg_stat_activity视图可实时展示所有正在运行的数据库连接与查询。要筛选访问特定表的用户，执行以下SQL：

SELECT usename AS 查询用户, 
       query AS 执行语句, 
       query_start AS 开始时间, 
       pid AS 进程ID
FROM pg_stat_activity
WHERE query LIKE '%public.mytable%'  -- 替换为你的表名和模式（如public.mytable）
  AND state = 'active';

注意：若查询使用了表别名，需调整LIKE匹配内容，确保命中目标表的访问语句。

2. 通过数据库日志记录所有访问 #

开启PostgreSQL日志功能，记录包含特定表的查询操作：

1. 编辑postgresql.conf配置文件，修改以下参数：

   log_statement = 'all'  # 记录所有SQL语句，也可按需设为'mod'等其他值
   log_min_duration_statement = 0  # 记录所有语句（0表示无耗时过滤）
   log_line_prefix = '%t [%p]: [%c-%l] user=%u,db=%d '  # 日志前缀包含用户、数据库等信息
   

2. 重启PostgreSQL服务使配置生效。
3. 查看数据库日志文件（默认路径通常为/var/log/postgresql/），筛选包含目标表名的条目即可找到对应访问用户。

3. 自定义触发器实现审计 #

创建触发器与审计表，主动记录对目标表的所有查询操作：

步骤1：创建审计表 #

CREATE TABLE table_access_audit (
    audit_id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    accessed_user TEXT NOT NULL,
    access_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    access_type TEXT NOT NULL
);

步骤2：创建审计触发器函数 #

CREATE OR REPLACE FUNCTION log_table_access()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO table_access_audit (table_name, accessed_user, access_type)
    VALUES (TG_TABLE_NAME, current_user, TG_OP);
    RETURN NULL;  -- SELECT触发器无需返回数据
END;
$$ LANGUAGE plpgsql;

步骤3：给目标表绑定触发器 #

CREATE TRIGGER audit_mytable_access
AFTER SELECT ON public.mytable  -- 替换为你的目标表
FOR EACH STATEMENT EXECUTE FUNCTION log_table_access();

之后所有对该表的SELECT操作都会被记录到table_access_audit表中，直接查询该表即可获取历史访问记录。

4. 使用pgAudit扩展（专业审计推荐） #

pgAudit是PostgreSQL官方支持的审计扩展，可精准控制审计范围：

1. 通过系统包管理器或源码编译安装pgAudit扩展。
2. 修改postgresql.conf启用扩展：

   shared_preload_libraries = 'pgaudit'  # 添加到已有的扩展列表中
   pgaudit.enabled = on
   

3. 重启PostgreSQL后，对目标表开启SELECT审计：

   ALTER TABLE public.mytable AUDIT SELECT;
   

此时所有对该表的SELECT操作都会被记录到数据库日志中，包含完整的用户、语句和时间信息。

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