如何查询PostgreSQL中各用户的最后连接日期/时间？

PostgreSQL本身没有内置存储用户历史连接时间的功能，pg_stat_activity只能查看当前活跃的连接，所以得靠你已经开启的连接日志，或者自定义追踪方案来实现需求。

方法1：利用已开启的连接日志分析 #

你已经打开了log_connections = on和log_disconnections = on，PostgreSQL会把连接/断开事件写入日志文件（默认在数据目录的pg_log文件夹下）。

用Shell命令快速提取 #

如果只是临时查询，可以直接过滤日志文件：

# 替换为你的日志路径，提取每个用户的最后连接记录
grep "connection authorized" /var/lib/postgresql/14/main/pg_log/*.log | awk '{print $1, $2, $NF}' | sort -k3,3 -k1,2r | awk '!seen[$3]++'

说明：connection authorized是连接成功时的日志关键字，这条命令会按用户分组，保留每个用户最近的一条连接记录。

导入日志到数据库查询（更灵活） #

如果日志是CSV格式（可以在postgresql.conf里设置log_destination = csvlog），可以把日志导入临时表做更复杂的查询：

-- 创建临时表存储日志数据
CREATE TEMP TABLE temp_connection_logs (
  log_time timestamp,
  user_name text,
  message text
);

-- 替换为你的日志文件路径，导入CSV日志
COPY temp_connection_logs FROM '/var/lib/postgresql/14/main/pg_log/postgresql-2024-05-20_000000.csv' WITH (FORMAT csv, HEADER);

-- 查询每个用户的最后连接时间
SELECT user_name, MAX(log_time) AS last_connection_time
FROM temp_connection_logs
WHERE message LIKE '%connection authorized%'
GROUP BY user_name;

方法2：自定义追踪表（长期维护方案） #

如果需要长期方便查询，建议建一个专门的表，用事件触发器自动记录用户连接事件：

1. 创建连接历史表 #

CREATE TABLE user_connection_history (
  user_name text NOT NULL,
  connection_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_name, connection_time)
);

2. 写触发器函数 #

CREATE OR REPLACE FUNCTION log_user_connection()
RETURNS event_trigger AS $$
BEGIN
  -- 插入当前连接用户的记录，避免同一连接重复插入
  INSERT INTO user_connection_history (user_name)
  VALUES (current_user)
  ON CONFLICT DO NOTHING;
END;
$$ LANGUAGE plpgsql;

3. 创建事件触发器（监听登录事件） #

CREATE EVENT TRIGGER track_user_logins
ON login
EXECUTE FUNCTION log_user_connection();

之后每次用户登录，都会自动把时间和用户名写入user_connection_history，查询最后连接时间就很简单：

SELECT user_name, MAX(connection_time) AS last_connection_time
FROM user_connection_history
GROUP BY user_name
ORDER BY last_connection_time;

清理长期未连接的用户 #

拿到最后连接时间后，就能筛选出超过阈值（比如90天）未连接的用户，然后删除：

-- 先查询确认要删除的用户（包括从未连接过的用户）
SELECT u.usename
FROM pg_user u
LEFT JOIN (
  SELECT user_name, MAX(connection_time) AS last_conn
  FROM user_connection_history -- 或者用日志导入的表
  GROUP BY user_name
) ch ON u.usename = ch.user_name
WHERE ch.last_conn < CURRENT_TIMESTAMP - INTERVAL '90 days'
   OR ch.last_conn IS NULL;

-- 确认无误后删除用户（注意：删除前要确保用户没有活跃连接，也没有拥有任何数据库对象）
DROP USER IF EXISTS inactive_user1, inactive_user2;

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