PostgreSQL查询所有用户并识别非Active Directory关联用户的方案咨询

前置准备 #

提前同步Active Directory（以下简称AD）中的合法用户清单到PostgreSQL侧，创建专用的管控表存储，定期更新（同步频率可匹配企业AD用户更新周期，推荐每日同步一次）：

-- 创建AD合法用户存储表
CREATE TABLE IF NOT EXISTS valid_ad_users (
    username varchar(64) PRIMARY KEY,
    update_time timestamp DEFAULT now()
);

后续所有用户校验都基于该表的数据比对。

方案1：基于事件触发器的实时告警（推荐） #

PostgreSQL的事件触发器可以捕获DDL操作，实时检测用户创建动作，适合对告警时效性要求高的场景：

• 触发器监听CREATE ROLE、CREATE USER两类DDL操作，用户创建动作触发后自动执行校验逻辑
• 校验逻辑自动排除PostgreSQL内置系统用户，将新增用户名和valid_ad_users表比对，匹配失败则触发告警
• 告警可对接企业内部通知渠道，比如邮件、企业微信/钉钉/飞书webhook，也可配合监控系统触发告警通知

示例代码：

-- 创建校验触发器函数
CREATE OR REPLACE FUNCTION check_new_user_is_ad_user()
RETURNS event_trigger AS $$
DECLARE
    new_username text;
BEGIN
    -- 提取新创建的用户名
    SELECT obj_identity INTO new_username
    FROM pg_event_trigger_ddl_commands()
    WHERE object_type = 'role';
    
    -- 校验逻辑，可自行调整内置用户白名单
    IF new_username NOT IN ('postgres', 'pg_monitor', 'pg_read_all_settings', 'pg_read_all_stats', 'pg_stat_scan_tables')
    AND NOT EXISTS (SELECT 1 FROM valid_ad_users WHERE username = new_username) THEN
        -- 发送通知事件，后台可监听该事件触发告警
        RAISE NOTICE '检测到非AD关联的新用户创建: %', new_username;
        PERFORM pg_notify('non_ad_user_created', new_username);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 绑定事件触发器
CREATE EVENT TRIGGER trigger_check_new_user
ON ddl_command_end
WHEN TAG IN ('CREATE ROLE', 'CREATE USER')
EXECUTE FUNCTION check_new_user_is_ad_user();

方案2：定时巡检告警（适合低时效性需求场景） #

不需要修改数据库配置，用定时脚本定期巡检比对即可：

• 编写shell/Python脚本，按业务需求设置定时任务（推荐每小时执行一次）
• 脚本逻辑：拉取pg_user中所有非系统用户，和valid_ad_users表的AD合法用户比对，找出差异用户
• 存在非AD用户时直接触发告警通知

shell脚本示例片段：

#!/bin/bash
# 拉取PostgreSQL所有非系统用户
PG_USERS=$(psql -U postgres -t -c "SELECT usename FROM pg_user WHERE usename NOT IN ('postgres', 'pg_monitor', 'pg_read_all_settings', 'pg_read_all_stats', 'pg_stat_scan_tables')")
# 拉取AD合法用户列表
AD_USERS=$(psql -U postgres -t -c "SELECT username FROM valid_ad_users")
# 比对找出非AD用户
NON_AD_USERS=$(comm -23 <(echo "$PG_USERS" | sort) <(echo "$AD_USERS" | sort))
if [ -n "$NON_AD_USERS" ]; then
    # 替换为实际告警逻辑，比如发邮件、调用内部通知接口
    echo "检测到非AD关联用户: $NON_AD_USERS" | mail -s "PostgreSQL非AD用户告警" your@email.com
fi

注意事项 #

• 内置系统用户白名单可根据企业实际使用情况调整，避免误告警
• 要保证AD用户清单同步及时，否则会出现新AD用户被误判为非法用户的情况
• 事件触发器需要PostgreSQL超级用户权限才能创建
• 如果企业PostgreSQL已经对接AD做身份认证，可以直接修改pg_hba.conf配置，禁止非AD用户登录，从根源上杜绝非AD用户的使用

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