企业官网建设流程全解析

很多刚从 MySQL、SQL Server 转过来的同学，第一件事就是习惯性去找“用户表”，然后对着 PostgreSQL 一脸懵：\du输出的是什么？pg_user和pg_roles有什么区别？为什么有些账号根本登录不了却也在列表里？这篇文章就把“列出 PostgreSQL 所有用户”这件事从头拆到尾，聊清楚用户/角色的权限模型、三条最常用的查询路径、进阶的组合查询方案，以及我在实际运维中踩过的几个坑。适合刚上手 PostgreSQL 的开发、DBA，也适合要做安全巡检或账号交接的朋友，读完你不仅能抄到现成的 SQL，还能明白每条查询背后到底查的是什么。

1. 先把概念拧过来：PostgreSQL 的用户本质是角色

1.1 用户就是角色，角色不一定是用户

PostgreSQL 的权限模型可以浓缩成一句话：一切皆 role（角色）。你创建的登录账号是一个 role，你创建的权限组是一个 role，你用来分组管理的角色还是一个 role，甚至集群初始化时自动生成的超级用户 postgres 也是一个 role。两者唯一的区别是：创建时有没有给 LOGIN 属性。

这个设计初看有点绕，但真正理解后会发现它非常优雅。比如需要给一组开发同事相同的库权限，标准做法是先建一个不带 LOGIN 的 role 叫dev_group，把权限授予这个组，再把每个开发账号GRANT dev_group TO 张三。以后权限调整只动组，不用挨个改人。这套机制也解释了为什么你用\du列出来的列表里，既有真正意义上的“用户”，也有大量根本登录不了的“角色”。

新手的困惑通常就在这里：MySQL 的mysql.user表里每个条目都是能登录的账号，PostgreSQL 却把账号和角色混在一起。所以在执行查询前，要先把需求定义清楚——你是想看“所有能登录的账号”，还是“所有角色（含组角色）”？这两个答案对应完全不同的查询，后面我会分别给出对应的办法。

1.2 CREATE USER 与 CREATE ROLE 到底差在哪

很多人背过“CREATE USER 相当于 CREATE ROLE ... LOGIN”，但没细想过为什么。其实从命令语义就能看出来：CREATE USER是面向“人”的语法糖，默认带 LOGIN；CREATE ROLE是面向“角色组”的通用语法，默认不带 LOGIN。看两个例子：

CREATE USER alice; -- 等价于 CREATE ROLE alice LOGIN; CREATE ROLE dev_group; -- 不能直接登录 CREATE ROLE bob LOGIN; -- 显式指定可登录

实际工作中，“一个账号不能登录”通常有两种可能：要么角色本身没有 LOGIN 属性，要么角色有 LOGIN 属性但被CONNECTION LIMIT 0或认证配置拦住了。排查时建议两条线都看，别只盯着rolcanlogin一个字段。

1.3 四个核心对象，一张表理清

我把自己经常查的对象整理成一张对照表，新手照着用就行：

这张表我建议直接收藏。以后遇到“为什么查不到密码字段”“为什么提示权限不足”，多半就是没分清该用哪个对象。记住一个原则：日常列名单用pg_roles，只看登录账号用pg_user，涉及认证细节才碰pg_authid。

2. 三种最常用的列用户方案：\du、pg_user、pg_roles

2.1 psql 元命令 \du 和 \dg：交互场景首选

如果你就是想在 psql 终端里快速看一眼都有谁，\du最直接。默认输出三列：角色名、角色属性、成员关系。角色属性里会标出Superuser、Create role、Create DB、Replication、Bypass RLS等关键字；成员关系列则显示该角色继承了哪些角色组。

\dg与\du输出结构基本一致，\dg更偏“角色组”视角，但实际操作中两者差别极小，选哪个都行。要注意的是，这两个元命令本质上是封装了对pg_roles的查询，所以只有进了 psql 才能用；自动化脚本或远程排查时还是直接写 SQL 更稳。我自己的习惯是：本地调试随手\du，写巡检脚本一律 SQL，避免脚本里依赖 psql 的交互环境。

还有一个容易忽略的细节：\du默认只显示当前用户权限范围内能看到的信息。非超级用户执行时，某些属性（比如是否超户）可能显示不出来，这不一定代表数据库出问题，切换到有权限的账号再看就能确认。

2.2 查询 pg_user：只要“能登录”的账号

如果你的需求就是“给我一份能登录的账号清单”，直接查pg_user最干净。这个视图在pg_roles基础上自动过滤了rolcanlogin = true，省得你手动加条件。

SELECT usename, usesysid, usecreatedb, usesuper, userepl, usebypassrls, valuntil FROM pg_user ORDER BY usename;

字段含义我给逐个解释一下：

• usename：账号名。
• usesysid：角色内部 OID，可以理解为数据库里的用户 ID。
• usecreatedb：是否允许建库。
• usesuper：是否为超级用户。
• userepl：是否可用于流复制。
• usebypassrls：是否绕过行级安全策略。
• valuntil：密码过期时间，空值表示永不过期。

注意pg_user是一个历史命名的视图，字段名里带use前缀，读起来不太现代，但它是官方稳定视图，跨版本兼容性很好。如果你觉得列名别扭，完全可以用pg_roles里的新命名代替。实际使用中我最常用的是最后两个字段，valuntil能直接暴露哪些账号密码已经过期，这是很多管理工具不会主动告诉你的信息。

2.3 进阶：pg_roles 和 pg_authid 的定位

pg_roles是公开可读的安全视图，字段全是rol*前缀，更贴近角色模型本身。我最常用的字段是rolname、rolsuper、rolcanlogin、rolconnlimit、rolvaliduntil和rolbypassrls。普通查询用它完全够，而且不用超户权限，业务账号也能执行，这也是绝大多数列用户脚本的底层数据来源。

pg_authid则不一样，它是最底层的认证表，含有rolpassword哈希字段。从 14 版本开始，PostgreSQL 默认的密码认证方式是scram-sha-256，所以你在pg_authid里看到的哈希串形如SCRAM-SHA-256$4096:...开头；如果是老库升级上来的账号，可能还保留md5开头的旧哈希。写安全巡检脚本时，我习惯把所有非 scram 哈希的账号单独筛出来，这些基本都属于需要推动改密的存量账号。

注意：普通用户查pg_authid会直接报permission denied for table pg_authid。这是权限设计，不是 Bug。业务系统不要尝试去读这张表，正确的做法是让 DBA 定期出报表，把认证信息同步到内部安全平台上。

3. 进阶组合查询：让用户列表从名单变成体检报告

3.1 一次查出权限来源：带出所属角色组

列用户光看账号本身价值有限，真正的 DBA 关心的是“这个账号到底有什么权限”。PostgreSQL 的角色权限是叠加的：用户自身权限 + 所属组的权限。所以要完整判断权限，必须把pg_auth_members（成员关系表）一起关联。

SELECT r.rolname AS 账号, r.rolsuper AS 是否超户, r.rolcanlogin AS 可登录, ARRAY( SELECT g.rolname FROM pg_auth_members m JOIN pg_roles g ON m.roleid = g.oid WHERE m.member = r.oid ) AS 所属角色组 FROM pg_roles r ORDER BY r.rolcanlogin DESC, r.rolname;

这是我日常巡检的主查询。ARRAY()子查询把该用户所有的上级角色组合并成一个数组输出，一眼扫过去就能判断这个账号是“裸奔”的还是被正确分组管理。如果某个可登录账号的“所属角色组”为空，那它的权限完全来自自身授权，这类账号通常是要重点审计的对象，因为权限变更时很难追溯来源。

3.2 区分系统角色和业务账号的过滤手法

新装一个 PostgreSQL，你用\du会看到十几二十个角色，但真正的账号可能只有一两个。那些pg_read_all_data、pg_write_all_data、pg_monitor、pg_signal_backend等是系统内置角色，从 10 版本开始引入，用于按功能拆分权限。区分它们的通用规则是看名字是否以pg_开头，因为系统不允许普通用户创建pg_前缀的角色。

想只保留业务账号，加一条过滤即可：

SELECT rolname FROM pg_roles WHERE rolcanlogin = true AND rolname NOT LIKE 'pg\_%';

这里我用了转义\_，因为pg_后面的下划线在 LIKE 里是通配符，不转义的话会把单个任意字符也匹配进去。虽然大部分场景不会踩中，但严谨一点没坏处。如果你还要排除掉默认的超级用户 postgres，可以再加一条AND rolsuper = false，不过这个要看你的实际需要，别一刀切。

3.3 关联连接数：谁在占资源

很多时候列用户不是为了看名单，而是排查“连接数告警了，谁占的？”。把角色表跟pg_stat_activity关联就能直观看到：

SELECT r.rolname AS 用户, r.rolconnlimit AS 连接上限, count(a.pid) AS 当前连接数 FROM pg_roles r LEFT JOIN pg_stat_activity a ON a.usename = r.rolname WHERE r.rolcanlogin = true GROUP BY r.rolname, r.rolconnlimit ORDER BY count(a.pid) DESC;

这里rolconnlimit的值要会看：-1 表示不限制，0 表示禁止新连接，正整数是连接上限。如果你发现某个用户当前连接数是上限的好几倍，优先去查应用侧连接池配置，而不是在数据库里干瞪眼。连接池配置错误、连接未释放、空闲事务过多，都是常见原因。

4. 高频场景实战：盘点账号、下线僵尸号、版本对比

4.1 场景一：新接手一个库，快速盘点账号

新项目交接时，我习惯先输出一组统计数字，把整体盘子摸清楚，再逐账号细看。一条 SQL 就能把关键指标都算出来：

SELECT count(*) AS 角色总数, count(*) FILTER (WHERE rolcanlogin) AS 可登录数, count(*) FILTER (WHERE rolsuper) AS 超户数, count(*) FILTER (WHERE rolvaliduntil IS NOT NULL AND rolvaliduntil < now()) AS 密码已过期数 FROM pg_roles;

这四个数字基本上决定了安全加固的优先级：先处理超户账号过多的问题，再清理密码过期账号，最后排查废弃角色。如果可登录数明显多于实际团队人数，那大概率有僵尸账号。这里我特别想强调一下FILTER语法，它是 PostgreSQL 9.4 之后引入的条件聚合写法，比CASE WHEN简洁得多，写统计 SQL 时强烈推荐。

4.2 场景二：安全下线“疑似废弃”账号的稳妥流程

数据库账号的“废弃”是业务概念，数据库本身没有直接指标。我的经验是用两个信号综合判断：一是该账号近期是否出现在pg_stat_activity中；二是是否有业务方确认不再使用。经过确认的废弃账号，别急着删，先用两步走：

1. 禁止新连接：

ALTER ROLE 旧账号 CONNECTION LIMIT 0;

2. 观察一个业务周期（比如一周），确认没有报障后再删除：

DROP ROLE 旧账号;

如果账号被删后才发现还有程序在用，恢复成本很高。先禁止连接是“软下线”，随时能回滚，这是我强烈推荐的稳妥流程。当然，CONNECTION LIMIT 0只能挡住新连接，对已建立的连接不会主动断开，需要配合pg_terminate_backend或等它自然释放。实际执行时我会先查pg_stat_activity看有没有残留连接，有的话先通知业务方再终止。

4.3 场景三：版本差异与选型怎么影响列用户

热词里一直有人问 “postgresql 下载哪个版本” “postgresql 16 便携版”，说明大家正在选型或升级。从“列用户”这个角度看，版本差异不算大，但有几个细节影响脚本兼容性：

• PostgreSQL 10 之前没有内置角色体系，升级老库后会出现一批pg_开头的系统角色，这是正常现象。
• PostgreSQL 14 开始默认密码认证改为 scram-sha-256，老账号的 md5 哈希不会自动升级，需要重建密码才会切换。
• PostgreSQL 15 开始，public模式不再默认允许所有用户建表，这在理解“新建账号的权限”时容易踩坑。
• PostgreSQL 16 及以后的版本，pg_roles、pg_user的字段结构保持稳定，基于这些视图的脚本基本不用改。

所以如果团队有大量基于系统视图的自动化脚本，选型时优先考虑 14 以上的版本，安全默认值更合理，认证模型也更现代。至于 16 便携版，适合本地开发和测试，但生产环境我不建议用非官方打包的分发方式，毕竟安全补丁和生态兼容性都跟不上。

5. 常见坑与排查实录：权限不足、密码哈希、连接失败

5.1 \du 看不到某些用户？

先确认当前会话身份。如果你之前执行过SET ROLE切换到别的角色，\du展示的就是切换后的视野。另外，普通用户看pg_roles的某些敏感属性会被自动省略，这是行级安全策略在起作用。用超户或者有pg_read_all_settings权限的角色复查一次就能确认。还有一种情况是连接池工具的干扰，某些中间件会用固定账号接管认证，你看到的可能不是真实业务账号，排查时要结合应用架构判断。

5.2 pg_authid 里能看到明文密码吗？

看不到。rolpassword存的是哈希值，PostgreSQL 设计上就不支持逆向还原。任何声称能“取出密码”的管理工具，实际上都是拿到了连接串明文或拦截了认证报文，不是从库里解出来的。需要重置密码就走ALTER ROLE ... PASSWORD，这是唯一正道。我自己做安全审计时，也会明确告诉业务方：数据库侧只能验证密码强度或轮换周期，无法提供明文。

5.3 Windows 上服务启动失败，跟账号有什么关系？

Windows 安装 PostgreSQL 时，服务默认以postgresql或postgres账号运行。如果你在安装后手动改了系统用户密码，或者初始化时指定的超级用户密码在pg_hba.conf中匹配不上，就会表现成“服务起来了但连接报password authentication failed”。排查路径是：先暂停服务，临时把pg_hba.conf对应条目改成trust，启动后重置密码，再恢复认证配置。整个过程涉及的仍然是角色管理，跟列用户是同一套体系。Windows 用户还要注意服务账号的“作为服务登录”权限不能被误改，否则服务根本拉不起来。

5.4 批量巡检脚本怎么避免密码泄露？

很多脚本喜欢直接写psql -U postgres -d dbname -c "SELECT ..."，密码明文出现在命令行里，这在 Linux 上会暴露在ps输出中。推荐做法是用~/.pgpass文件管理密码，并设置 0600 权限；或者用环境变量PGPASSWORD；更规范的是通过连接池或密钥管理服务注入。每次写完巡检脚本，我都会自查一遍是不是把敏感信息硬编码进去了，这个习惯能避免很多低级事故。

5.5 常见错误速查表

最后说点个人体会。列用户这件事，表面上就是一条 SELECT 的事，但它背后牵扯权限模型、版本差异、安全边界，反而比很多“高级操作”更容易翻车。我踩过最大的坑是刚接手一个库时，以为pg_user就是全部账号，结果漏掉了一个带超户权限的角色，后来安全审计时才暴露。现在我的习惯是：日常用\du当快照，季度用pg_roles联合查询做全面体检，真正排查认证问题才上pg_authid。这套思路比背命令重要得多，也希望能帮你少走几步弯路。