尧图网页设计 
在 PostgreSQL 里执行\du我能看到一屏幕角色但真到生产环境排查权限的时候这个命令往往不太够用。我经常被问到这几个问题为什么pg_user里少了好几个用户CREATE ROLE和CREATE USER到底有什么区别明明在 psql 里能看到全部角色换到 pgAdmin 里就不知道去哪查了这篇文章就把 PostgreSQL 里“列出所有用户”这件事彻底讲透用什么命令、查哪些系统表、怎么区分普通用户和角色、怎么过滤系统内置账号顺便整理一下我在实际运维中踩过的坑。适合刚接触 PostgreSQL 的开发者也适合需要做数据库巡检、账号清理、权限交接的运维同学。1. 角色和用户先搞清楚 PostgreSQL 的用户模型1.1 CREATE USER 和 CREATE ROLE 差在哪PostgreSQL 里没有传统意义上的“用户表”它把所有账号统一叫作角色Role。角色分成两类能登录的LOGIN和不能登录的NOLOGIN。能登录的角色就是我们通常说的“用户”不能登录的角色一般用来做权限组比如readonly_group、app_write_group把多个账号归到一个组里统一授权这样权限管理会清爽很多。两者的关系直接在创建语法上就能看出来CREATE USER alice; CREATE ROLE bob;默认情况下CREATE USER等价于CREATE ROLE alice WITH LOGIN;而CREATE ROLE bob创建出来的 bob 是NOLOGIN它不能直接登录数据库。换句话说用户就是带 LOGIN 属性的角色。打个比方一个公司里每个人都有工牌但有的工牌能刷开机房的门有的只能进办公区。机房权限就是LOGIN普通工牌就是NOLOGIN的角色。实际工作中我建议把“账号”和“权限组”分开建先创建几个NOLOGIN的角色用于分组授权再创建真正能登录的账号并把账号塞进对应的组里CREATE ROLE readonly_group NOLOGIN; CREATE USER alice PASSWORD strong_password; GRANT readonly_group TO alice;这样做的好处是以后 alice 离职了直接DROP ROLE alice或者撤销组成员关系就行不需要去改一堆表的权限。如果直接给每个用户单独授权账号一多就乱套了。1.2 用户信息藏在哪几张系统表里PostgreSQL 的用户信息存放在系统目录System Catalog里核心是下面这几张表/视图表/视图名作用可见性pg_authid存储角色属性和密码哈希是底层表仅超级用户可读pg_roles在pg_authid之上的公开视图不含密码所有用户可读pg_auth_members存储角色成员关系也就是“谁属于哪个组”所有用户可读pg_shadowpg_authid的视图但 passwd 字段永远为空所有用户可读pg_user只显示有LOGIN属性的角色所有用户可读这里最需要注意的是pg_authid。它里面有真实的密码哈希值如果泄露出去别人虽然不能直接还原出明文密码但可以用哈希值做离线撞库攻击所以 PostgreSQL 默认只让超级用户读这张表。普通用户执行SELECT * FROM pg_authid;会直接报权限不足这是正常的保护机制不用慌。pg_roles是平时用得最多的一张视图它把角色的各种属性都暴露出来了同时又隐藏了密码任何用户都能查非常适合做账号梳理。它和pg_user的区别在于pg_roles包含所有角色包括NOLOGIN的组角色和系统内置角色而pg_user只显示能登录的那部分。很多新手在pg_user里查不到刚建的角色就是因为那个角色没有LOGIN属性它不是“用户”而是“组”。1.3 为什么说角色是实例级的还有一点特别容易踩坑角色不是数据库级对象而是整个 PostgreSQL 实例集群级别的对象。这句话的意思是不管你现在连的是postgres库、appdb库还是testdb库执行\du或者查询pg_roles拿到的结果都一样。我第一次接触 PG 时也有点懵因为 MySQL 里SELECT user FROM mysql.user也是实例级的但 PG 的 psql 提示符会显示当前数据库名字很容易让人误以为\du的结果跟着数据库走。实际完全不相关。理解这一点对排查问题非常重要。如果你在一台服务器上装了多个 PostgreSQL 实例比如端口 5432 和 5433 各跑一个连接端口不同看到的用户列表就不同。很多“用户突然不见了”的假象其实是连错了实例。2. 列出用户的三种姿势\du、SQL查询、系统视图2.1 psql 元命令 \du 与 \du先记住最简单的在 psql 里执行\du直接列出所有角色。效果大概是这样的postgres# \du List of roles Role name | Attributes | Member of ---------------------------------------------------------------------------------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} alice | | {readonly_group} readonly_group | Cannot login | {}\du输出三列信息角色名、属性、成员属于关系。Attributes列显示这个角色有没有Superuser、Create role、Create DB等权限Cannot login表示NOLOGIN。Member of列显示这个角色属于哪些权限组。比如 alice 的Member of是{readonly_group}说明她继承了readonly_group的权限。如果想知道更多细节比如角色描述、连接限制等可以用\du它会额外显示Description一列。前提是你在创建角色时给角色加过注释COMMENT ON ROLE否则这里大多是空的。我实际用下来\du最大的优势是快适合临时看一眼缺点是输出结果不好往下游工具传递。到了自动化巡检、写脚本、做权限审计的场景我基本不用\du而是直接用 SQL 查询。2.2 SELECT 查询 pg_roles如果要写脚本最标准的方法是查询pg_roles视图。基础的查询语句SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolreplication FROM pg_roles ORDER BY rolname;字段的含义大致如下字段名含义rolname角色名称rolsuper是否为超级用户rolcreatedb是否能创建数据库rolcreaterole是否能创建其他角色rolcanlogin是否能登录rolreplication是否有流复制权限rolconnlimit连接数限制-1 表示无限制rolbypassrls是否绕过行级安全策略只查看能登录的“真用户”SELECT rolname FROM pg_roles WHERE rolcanlogin true;只查看超级用户SELECT rolname FROM pg_roles WHERE rolsuper true;这里有个好处是pg_roles不区分权限任何能连上数据库的用户都能查不需要超级用户权限。所以日常排查、写自动化脚本时完全可以放心用。2.3 查看 pg_user 与 pg_authid如果想用 PostgreSQL 预置的“用户”语义来查可以用pg_user视图SELECT usename, usesysid, usecreatedb, usesuper, userepl FROM pg_user;pg_user的输出字段比pg_roles更接近我们印象中的“用户表”但它只包含能登录的角色。如果某个账号是NOLOGIN的权限组它不会出现在pg_user里。这个视图其实就是pg_shadow的一个子集pg_shadow又是pg_authid的视图。至于pg_authid我要再强调一下它的rolpassword字段存储的是密码哈希MD5 或 SCRAM 格式普通用户没有权限读超级用户也要小心别把查询结果贴到日志、工单或者聊天记录里。说实话我平时根本不会去查pg_authid因为列用户这件事根本用不到密码信息。只有一种情况例外——当我怀疑某个账号的认证方式有问题需要确认它是md5还是scram-sha-256时才会用超级用户身份谨慎地查一眼。2.4 information_schema 里为什么没有用户表用过 SQL Server 或者 MySQL 的朋友习惯性地会去information_schema里找用户相关的表比如information_schema.USERS之类的。但在 PostgreSQL 里你会扑个空。原因很简单PostgreSQL 的用户体系基于“角色”概念这是 PG 自己的实现不属于 SQL 标准规范里的内容。SQL 标准只定义了表、视图、列等对象没有定义“数据库用户”这种面向实现的实体。所以 PG 把这些信息全部放在pg_catalog系统目录里也就是pg_roles、pg_user、pg_auth_members这一大家子。以后别再花时间去information_schema里找了记住一句话PG 的系统表和视图才是查用户的正道。3. 实操从连接数据库到第一次跑出用户列表3.1 用 psql 连接并执行 \du很多刚在 Windows 上装完 PostgreSQL 的朋友会在“开始菜单”里看到两个东西pgAdmin 4和SQL Shell (psql)。SQL Shell 就是命令行工具双击打开后会依次提示输入服务器地址、端口、数据库名、用户名、密码。我习惯直接在一体化终端里敲命令尤其是在 Windows 上如果安装时勾选了添加到 PATH可以直接这样连psql -h localhost -p 5432 -U postgres -d postgres参数含义很简单-h是主机地址本地就用 localhost-p是端口默认 5432-U是用户名-d是要连接的数据库。连接后执行\du就能看到用户列表了。如果连接时报错比如“Connection refused”或者“服务未启动”先别急着怀疑密码很大概率是 PostgreSQL 的 Windows 服务没跑起来。在 Windows 服务管理器里找到名字类似postgresql-x64-16的服务确认它的状态是“正在运行”。也可以执行net start | findstr postgresql或者直接重新启动服务net start postgresql-x64-16Windows 安装的 PostgreSQL 默认把 psql 放在C:\Program Files\PostgreSQL\16\bin目录下如果你在任意路径下敲psql提示找不到命令就把这个目录加到系统 PATH 环境变量里以后用起来会顺手很多。3.2 在 pgAdmin 的 Query Tool 里执行 SQL如果你用的是 pgAdmin 4不想切到命令行那就打开左侧服务器树选择任意一个数据库推荐postgres右键点击选择Query Tool或者从顶部菜单Tools - Query Tool打开。在查询编辑器里输入SELECT rolname, rolsuper, rolcanlogin FROM pg_roles ORDER BY rolname;按 F5 执行结果就会显示在下方的 Data Output 面板里。有一个小坑需要提一下pgAdmin 的 Query Tool 只接受 SQL不接受 psql 元命令。也就是说你在查询工具里敲\du它会报语法错误。很多新手在这里卡住以为 PG 出了问题。解决方式无非两种要么用 psql 的 SQL Shell 执行\du要么把\du对应的信息用SELECT语句在 Query Tool 里查。3.3 把常用查询保存成“用户清单”脚本列用户这个动作虽然简单但遇到几十个账号的实例每次手动敲命令还是太累。我的做法是保存一套 SQL 脚本随用随跑。比如下面这条是我最常用的一条“用户总览”查询可以一次性列出用户名、是否超级用户、能否登录、属于哪些权限组SELECT r.rolname AS role_name, r.rolsuper AS is_superuser, r.rolcanlogin AS can_login, COALESCE(array_agg(m.rolname) FILTER (WHERE m.rolname IS NOT NULL), {}) AS member_of FROM pg_roles r LEFT JOIN pg_auth_members am ON am.member r.oid LEFT JOIN pg_roles m ON m.oid am.roleid GROUP BY r.rolname, r.rolsuper, r.rolcanlogin ORDER BY r.rolname;第一次看到这条查询可能会觉得有点复杂其实逻辑很简单pg_auth_members是成员关系表am.member指向成员角色的 OIDam.roleid指向被加入的组角色的 OID。用LEFT JOIN把每个角色和它所属的组连接起来再用array_agg把多个组名聚合成一个数组这样输出结果非常紧凑。把这条 SQL 保存成一个.sql文件比如list_users.sql以后一条命令就能复现psql -h localhost -U postgres -d postgres -f list_users.sql如果需要输出到 CSV 文件加上-o参数或者让 psql 走\copy就行。这样做的意义在于查询是可追溯、可复现的而不是依赖某个人脑子里的命令记忆。4. 进阶玩法过滤真用户、查成员关系、梳理权限4.1 如何只列出真正能登录的应用账号PostgreSQL 从 14 版本开始\du里会出现一堆pg_开头的角色比如pg_read_all_data、pg_write_all_data、pg_signal_backend等。这些是系统预定义角色它们本身提供了特定的内置权限但对大多数业务场景来说它们属于“噪音”。要只列出业务相关的“真用户”推荐加两个过滤条件能登录并且名字不以pg_开头。SELECT rolname FROM pg_roles WHERE rolcanlogin true AND rolname NOT LIKE pg_% ORDER BY rolname;注意一点postgres超级用户也满足这两个条件它不会被过滤掉这是正常的。真正做账号清理时要额外留意那些rolsuper true的账号超级用户数量越少越好。还要提醒一句别看到某个角色是Cannot login就觉得它没用直接删。很多NOLOGIN的角色是权限组比如readonly_group业务账号通过它继承了读权限。误删这种组角色可能导致一大片应用账号的权限瞬间失效。4.2 用 pg_auth_members 查角色成员关系排障的时候光看用户列表通常不够。你经常会遇到这样的问题某个账号能读某张表但你不记得给这个账号直接授权过。答案往往藏在角色成员关系里。想查某个用户属于哪些组执行SELECT r.rolname AS group_role FROM pg_auth_members am JOIN pg_roles r ON r.oid am.roleid WHERE am.member alice::regrole;alice::regrole这种写法会把角色名直接转成 OID简洁又安全。想反着查看某个组里有哪些成员SELECT m.rolname AS member_role FROM pg_auth_members am JOIN pg_roles m ON m.oid am.member WHERE am.roleid readonly_group::regrole;pg_auth_members表里的关键字段是roleid组角色和member成员角色。它是理解 PG 权限继承的枢纽表比单独查用户名有用得多。实际工作中我排查权限问题时八成时间都在查这张表。4.3 从“列出用户”到“梳理权限”还需要什么用户列表只回答了“数据库里有哪些身份”但很多权限问题还要继续往下挖。比如“alice 对orders表有什么权限”这就要查授权信息了。常用的查询目标表级权限information_schema.role_table_grants使用权限information_schema.role_usage_grants数据库、表空间pg_database、pg_tablespace默认权限pg_default_acl一个快速查看业务表授权情况的查询SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants WHERE table_schema NOT IN (pg_catalog, information_schema) ORDER BY grantee, table_name;这里要特别提醒pg_default_acl的存在。如果你在某个 schema 上设置了默认权限那么之后新建的表会自动带上这套权限但这些权限并不会出现在role_table_grants里。换句话说查完role_table_grants觉得“用户没权限”不代表用户真的没权限必须连同pg_default_acl一起看。这个坑我踩过不止一次每次都是排查到最后发现是默认权限在起作用。5. 常见问题与排查速查5.1 一张表说清常见问题问题现象可能原因解决办法\du里能看到用户pg_user里查不到该角色是NOLOGIN不属于“用户”改用pg_roles查询查询pg_authid提示权限不足普通用户无权读取密码哈希表改用pg_roles或由超级用户谨慎操作新建CREATE ROLE后“用户”不见了CREATE ROLE默认不带LOGIN属性创建时加LOGIN或用ALTER ROLE ... LOGIN切换数据库后\du结果不变角色是实例级对象与当前数据库无关这是正常现象不用怀疑用户列表里出现一堆pg_开头角色PG14 开始显示系统预定义角色查询时过滤rolname NOT LIKE pg_%忘记密码还想确认账号是否存在列出用户不需要密码连接任意数据库执行\du即可在 pgAdmin 的 Query Tool 里敲\du报语法错误Query Tool 不支持 psql 元命令改用 SQL 查询或使用 psql SQL Shellpsql 连接时报连接失败服务未启动、端口不对、认证失败检查 Windows 服务、端口、pg_hba.conf想知道当前登录身份连接后未确认 session 用户执行SELECT current_user; SELECT session_user;5.2 我在实际工作里的几个习惯列用户这件事看似简单但真正做得顺手需要养成几个习惯。第一能用 SQL 就不用交互命令。\du虽然方便但它的输出是为了人眼阅读设计的不好接进自动化脚本。我日常把所有账号梳理类查询都写成 SQL 文件统一放到一个目录里每次巡检直接psql -f执行结果可留档、可对比、可交接。第二不需要看密码哈希。除非是排查认证方式、重置密码等极少数场景否则不碰pg_authid。查pg_roles加上pg_auth_members已经能覆盖绝大多数账号管理需求。这既是对自己安全的保护也是为了避免敏感信息出现在数据库日志或终端历史里。第三分三层检查账号。每次做账号梳理我习惯分成三拨来看能登录的业务账号、超级用户账号、NOLOGIN的权限组。逐个确认这些账号是否仍然需要存在、权限是否合理。分开看不容易遗漏也比一锅端清晰很多。第四执行任何查询前先确认连的是哪个实例。尤其是本地开发环境同时跑了好几个 PostgreSQL 服务端口一搞混查出来的用户列表全是错的。我吃过这个亏后来每次连接前都先执行一下SELECT current_user, current_database();确认无误再做后续操作。5.3 一条几乎每天都用的查询语句最后分享一条我个人几乎每天都会用到的基础查询。如果你只想记住一条命令除开\du那就是这条SELECT rolname, rolsuper, rolcanlogin, rolcreatedb FROM pg_roles WHERE rolname NOT LIKE pg_% ORDER BY rolsuper DESC, rolname;它能把业务相关的角色一次性列全同时把超级用户排在前面方便一眼看出哪些账号权限最高。默认过滤掉了pg_开头的系统角色输出干净配合psql -f也能直接用于巡检留痕。等你把这条用熟了再回去看\du会觉得命令行输出的信息密度实在太低了。 PostgreSQL列出所有用户：\du、pg_roles与角色区分实践
在 PostgreSQL 里执行\du我能看到一屏幕角色但真到生产环境排查权限的时候这个命令往往不太够用。我经常被问到这几个问题为什么pg_user里少了好几个用户CREATE ROLE和CREATE USER到底有什么区别明明在 psql 里能看到全部角色换到 pgAdmin 里就不知道去哪查了这篇文章就把 PostgreSQL 里“列出所有用户”这件事彻底讲透用什么命令、查哪些系统表、怎么区分普通用户和角色、怎么过滤系统内置账号顺便整理一下我在实际运维中踩过的坑。适合刚接触 PostgreSQL 的开发者也适合需要做数据库巡检、账号清理、权限交接的运维同学。1. 角色和用户先搞清楚 PostgreSQL 的用户模型1.1 CREATE USER 和 CREATE ROLE 差在哪PostgreSQL 里没有传统意义上的“用户表”它把所有账号统一叫作角色Role。角色分成两类能登录的LOGIN和不能登录的NOLOGIN。能登录的角色就是我们通常说的“用户”不能登录的角色一般用来做权限组比如readonly_group、app_write_group把多个账号归到一个组里统一授权这样权限管理会清爽很多。两者的关系直接在创建语法上就能看出来CREATE USER alice; CREATE ROLE bob;默认情况下CREATE USER等价于CREATE ROLE alice WITH LOGIN;而CREATE ROLE bob创建出来的 bob 是NOLOGIN它不能直接登录数据库。换句话说用户就是带 LOGIN 属性的角色。打个比方一个公司里每个人都有工牌但有的工牌能刷开机房的门有的只能进办公区。机房权限就是LOGIN普通工牌就是NOLOGIN的角色。实际工作中我建议把“账号”和“权限组”分开建先创建几个NOLOGIN的角色用于分组授权再创建真正能登录的账号并把账号塞进对应的组里CREATE ROLE readonly_group NOLOGIN; CREATE USER alice PASSWORD strong_password; GRANT readonly_group TO alice;这样做的好处是以后 alice 离职了直接DROP ROLE alice或者撤销组成员关系就行不需要去改一堆表的权限。如果直接给每个用户单独授权账号一多就乱套了。1.2 用户信息藏在哪几张系统表里PostgreSQL 的用户信息存放在系统目录System Catalog里核心是下面这几张表/视图表/视图名作用可见性pg_authid存储角色属性和密码哈希是底层表仅超级用户可读pg_roles在pg_authid之上的公开视图不含密码所有用户可读pg_auth_members存储角色成员关系也就是“谁属于哪个组”所有用户可读pg_shadowpg_authid的视图但 passwd 字段永远为空所有用户可读pg_user只显示有LOGIN属性的角色所有用户可读这里最需要注意的是pg_authid。它里面有真实的密码哈希值如果泄露出去别人虽然不能直接还原出明文密码但可以用哈希值做离线撞库攻击所以 PostgreSQL 默认只让超级用户读这张表。普通用户执行SELECT * FROM pg_authid;会直接报权限不足这是正常的保护机制不用慌。pg_roles是平时用得最多的一张视图它把角色的各种属性都暴露出来了同时又隐藏了密码任何用户都能查非常适合做账号梳理。它和pg_user的区别在于pg_roles包含所有角色包括NOLOGIN的组角色和系统内置角色而pg_user只显示能登录的那部分。很多新手在pg_user里查不到刚建的角色就是因为那个角色没有LOGIN属性它不是“用户”而是“组”。1.3 为什么说角色是实例级的还有一点特别容易踩坑角色不是数据库级对象而是整个 PostgreSQL 实例集群级别的对象。这句话的意思是不管你现在连的是postgres库、appdb库还是testdb库执行\du或者查询pg_roles拿到的结果都一样。我第一次接触 PG 时也有点懵因为 MySQL 里SELECT user FROM mysql.user也是实例级的但 PG 的 psql 提示符会显示当前数据库名字很容易让人误以为\du的结果跟着数据库走。实际完全不相关。理解这一点对排查问题非常重要。如果你在一台服务器上装了多个 PostgreSQL 实例比如端口 5432 和 5433 各跑一个连接端口不同看到的用户列表就不同。很多“用户突然不见了”的假象其实是连错了实例。2. 列出用户的三种姿势\du、SQL查询、系统视图2.1 psql 元命令 \du 与 \du先记住最简单的在 psql 里执行\du直接列出所有角色。效果大概是这样的postgres# \du List of roles Role name | Attributes | Member of ---------------------------------------------------------------------------------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} alice | | {readonly_group} readonly_group | Cannot login | {}\du输出三列信息角色名、属性、成员属于关系。Attributes列显示这个角色有没有Superuser、Create role、Create DB等权限Cannot login表示NOLOGIN。Member of列显示这个角色属于哪些权限组。比如 alice 的Member of是{readonly_group}说明她继承了readonly_group的权限。如果想知道更多细节比如角色描述、连接限制等可以用\du它会额外显示Description一列。前提是你在创建角色时给角色加过注释COMMENT ON ROLE否则这里大多是空的。我实际用下来\du最大的优势是快适合临时看一眼缺点是输出结果不好往下游工具传递。到了自动化巡检、写脚本、做权限审计的场景我基本不用\du而是直接用 SQL 查询。2.2 SELECT 查询 pg_roles如果要写脚本最标准的方法是查询pg_roles视图。基础的查询语句SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolreplication FROM pg_roles ORDER BY rolname;字段的含义大致如下字段名含义rolname角色名称rolsuper是否为超级用户rolcreatedb是否能创建数据库rolcreaterole是否能创建其他角色rolcanlogin是否能登录rolreplication是否有流复制权限rolconnlimit连接数限制-1 表示无限制rolbypassrls是否绕过行级安全策略只查看能登录的“真用户”SELECT rolname FROM pg_roles WHERE rolcanlogin true;只查看超级用户SELECT rolname FROM pg_roles WHERE rolsuper true;这里有个好处是pg_roles不区分权限任何能连上数据库的用户都能查不需要超级用户权限。所以日常排查、写自动化脚本时完全可以放心用。2.3 查看 pg_user 与 pg_authid如果想用 PostgreSQL 预置的“用户”语义来查可以用pg_user视图SELECT usename, usesysid, usecreatedb, usesuper, userepl FROM pg_user;pg_user的输出字段比pg_roles更接近我们印象中的“用户表”但它只包含能登录的角色。如果某个账号是NOLOGIN的权限组它不会出现在pg_user里。这个视图其实就是pg_shadow的一个子集pg_shadow又是pg_authid的视图。至于pg_authid我要再强调一下它的rolpassword字段存储的是密码哈希MD5 或 SCRAM 格式普通用户没有权限读超级用户也要小心别把查询结果贴到日志、工单或者聊天记录里。说实话我平时根本不会去查pg_authid因为列用户这件事根本用不到密码信息。只有一种情况例外——当我怀疑某个账号的认证方式有问题需要确认它是md5还是scram-sha-256时才会用超级用户身份谨慎地查一眼。2.4 information_schema 里为什么没有用户表用过 SQL Server 或者 MySQL 的朋友习惯性地会去information_schema里找用户相关的表比如information_schema.USERS之类的。但在 PostgreSQL 里你会扑个空。原因很简单PostgreSQL 的用户体系基于“角色”概念这是 PG 自己的实现不属于 SQL 标准规范里的内容。SQL 标准只定义了表、视图、列等对象没有定义“数据库用户”这种面向实现的实体。所以 PG 把这些信息全部放在pg_catalog系统目录里也就是pg_roles、pg_user、pg_auth_members这一大家子。以后别再花时间去information_schema里找了记住一句话PG 的系统表和视图才是查用户的正道。3. 实操从连接数据库到第一次跑出用户列表3.1 用 psql 连接并执行 \du很多刚在 Windows 上装完 PostgreSQL 的朋友会在“开始菜单”里看到两个东西pgAdmin 4和SQL Shell (psql)。SQL Shell 就是命令行工具双击打开后会依次提示输入服务器地址、端口、数据库名、用户名、密码。我习惯直接在一体化终端里敲命令尤其是在 Windows 上如果安装时勾选了添加到 PATH可以直接这样连psql -h localhost -p 5432 -U postgres -d postgres参数含义很简单-h是主机地址本地就用 localhost-p是端口默认 5432-U是用户名-d是要连接的数据库。连接后执行\du就能看到用户列表了。如果连接时报错比如“Connection refused”或者“服务未启动”先别急着怀疑密码很大概率是 PostgreSQL 的 Windows 服务没跑起来。在 Windows 服务管理器里找到名字类似postgresql-x64-16的服务确认它的状态是“正在运行”。也可以执行net start | findstr postgresql或者直接重新启动服务net start postgresql-x64-16Windows 安装的 PostgreSQL 默认把 psql 放在C:\Program Files\PostgreSQL\16\bin目录下如果你在任意路径下敲psql提示找不到命令就把这个目录加到系统 PATH 环境变量里以后用起来会顺手很多。3.2 在 pgAdmin 的 Query Tool 里执行 SQL如果你用的是 pgAdmin 4不想切到命令行那就打开左侧服务器树选择任意一个数据库推荐postgres右键点击选择Query Tool或者从顶部菜单Tools - Query Tool打开。在查询编辑器里输入SELECT rolname, rolsuper, rolcanlogin FROM pg_roles ORDER BY rolname;按 F5 执行结果就会显示在下方的 Data Output 面板里。有一个小坑需要提一下pgAdmin 的 Query Tool 只接受 SQL不接受 psql 元命令。也就是说你在查询工具里敲\du它会报语法错误。很多新手在这里卡住以为 PG 出了问题。解决方式无非两种要么用 psql 的 SQL Shell 执行\du要么把\du对应的信息用SELECT语句在 Query Tool 里查。3.3 把常用查询保存成“用户清单”脚本列用户这个动作虽然简单但遇到几十个账号的实例每次手动敲命令还是太累。我的做法是保存一套 SQL 脚本随用随跑。比如下面这条是我最常用的一条“用户总览”查询可以一次性列出用户名、是否超级用户、能否登录、属于哪些权限组SELECT r.rolname AS role_name, r.rolsuper AS is_superuser, r.rolcanlogin AS can_login, COALESCE(array_agg(m.rolname) FILTER (WHERE m.rolname IS NOT NULL), {}) AS member_of FROM pg_roles r LEFT JOIN pg_auth_members am ON am.member r.oid LEFT JOIN pg_roles m ON m.oid am.roleid GROUP BY r.rolname, r.rolsuper, r.rolcanlogin ORDER BY r.rolname;第一次看到这条查询可能会觉得有点复杂其实逻辑很简单pg_auth_members是成员关系表am.member指向成员角色的 OIDam.roleid指向被加入的组角色的 OID。用LEFT JOIN把每个角色和它所属的组连接起来再用array_agg把多个组名聚合成一个数组这样输出结果非常紧凑。把这条 SQL 保存成一个.sql文件比如list_users.sql以后一条命令就能复现psql -h localhost -U postgres -d postgres -f list_users.sql如果需要输出到 CSV 文件加上-o参数或者让 psql 走\copy就行。这样做的意义在于查询是可追溯、可复现的而不是依赖某个人脑子里的命令记忆。4. 进阶玩法过滤真用户、查成员关系、梳理权限4.1 如何只列出真正能登录的应用账号PostgreSQL 从 14 版本开始\du里会出现一堆pg_开头的角色比如pg_read_all_data、pg_write_all_data、pg_signal_backend等。这些是系统预定义角色它们本身提供了特定的内置权限但对大多数业务场景来说它们属于“噪音”。要只列出业务相关的“真用户”推荐加两个过滤条件能登录并且名字不以pg_开头。SELECT rolname FROM pg_roles WHERE rolcanlogin true AND rolname NOT LIKE pg_% ORDER BY rolname;注意一点postgres超级用户也满足这两个条件它不会被过滤掉这是正常的。真正做账号清理时要额外留意那些rolsuper true的账号超级用户数量越少越好。还要提醒一句别看到某个角色是Cannot login就觉得它没用直接删。很多NOLOGIN的角色是权限组比如readonly_group业务账号通过它继承了读权限。误删这种组角色可能导致一大片应用账号的权限瞬间失效。4.2 用 pg_auth_members 查角色成员关系排障的时候光看用户列表通常不够。你经常会遇到这样的问题某个账号能读某张表但你不记得给这个账号直接授权过。答案往往藏在角色成员关系里。想查某个用户属于哪些组执行SELECT r.rolname AS group_role FROM pg_auth_members am JOIN pg_roles r ON r.oid am.roleid WHERE am.member alice::regrole;alice::regrole这种写法会把角色名直接转成 OID简洁又安全。想反着查看某个组里有哪些成员SELECT m.rolname AS member_role FROM pg_auth_members am JOIN pg_roles m ON m.oid am.member WHERE am.roleid readonly_group::regrole;pg_auth_members表里的关键字段是roleid组角色和member成员角色。它是理解 PG 权限继承的枢纽表比单独查用户名有用得多。实际工作中我排查权限问题时八成时间都在查这张表。4.3 从“列出用户”到“梳理权限”还需要什么用户列表只回答了“数据库里有哪些身份”但很多权限问题还要继续往下挖。比如“alice 对orders表有什么权限”这就要查授权信息了。常用的查询目标表级权限information_schema.role_table_grants使用权限information_schema.role_usage_grants数据库、表空间pg_database、pg_tablespace默认权限pg_default_acl一个快速查看业务表授权情况的查询SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants WHERE table_schema NOT IN (pg_catalog, information_schema) ORDER BY grantee, table_name;这里要特别提醒pg_default_acl的存在。如果你在某个 schema 上设置了默认权限那么之后新建的表会自动带上这套权限但这些权限并不会出现在role_table_grants里。换句话说查完role_table_grants觉得“用户没权限”不代表用户真的没权限必须连同pg_default_acl一起看。这个坑我踩过不止一次每次都是排查到最后发现是默认权限在起作用。5. 常见问题与排查速查5.1 一张表说清常见问题问题现象可能原因解决办法\du里能看到用户pg_user里查不到该角色是NOLOGIN不属于“用户”改用pg_roles查询查询pg_authid提示权限不足普通用户无权读取密码哈希表改用pg_roles或由超级用户谨慎操作新建CREATE ROLE后“用户”不见了CREATE ROLE默认不带LOGIN属性创建时加LOGIN或用ALTER ROLE ... LOGIN切换数据库后\du结果不变角色是实例级对象与当前数据库无关这是正常现象不用怀疑用户列表里出现一堆pg_开头角色PG14 开始显示系统预定义角色查询时过滤rolname NOT LIKE pg_%忘记密码还想确认账号是否存在列出用户不需要密码连接任意数据库执行\du即可在 pgAdmin 的 Query Tool 里敲\du报语法错误Query Tool 不支持 psql 元命令改用 SQL 查询或使用 psql SQL Shellpsql 连接时报连接失败服务未启动、端口不对、认证失败检查 Windows 服务、端口、pg_hba.conf想知道当前登录身份连接后未确认 session 用户执行SELECT current_user; SELECT session_user;5.2 我在实际工作里的几个习惯列用户这件事看似简单但真正做得顺手需要养成几个习惯。第一能用 SQL 就不用交互命令。\du虽然方便但它的输出是为了人眼阅读设计的不好接进自动化脚本。我日常把所有账号梳理类查询都写成 SQL 文件统一放到一个目录里每次巡检直接psql -f执行结果可留档、可对比、可交接。第二不需要看密码哈希。除非是排查认证方式、重置密码等极少数场景否则不碰pg_authid。查pg_roles加上pg_auth_members已经能覆盖绝大多数账号管理需求。这既是对自己安全的保护也是为了避免敏感信息出现在数据库日志或终端历史里。第三分三层检查账号。每次做账号梳理我习惯分成三拨来看能登录的业务账号、超级用户账号、NOLOGIN的权限组。逐个确认这些账号是否仍然需要存在、权限是否合理。分开看不容易遗漏也比一锅端清晰很多。第四执行任何查询前先确认连的是哪个实例。尤其是本地开发环境同时跑了好几个 PostgreSQL 服务端口一搞混查出来的用户列表全是错的。我吃过这个亏后来每次连接前都先执行一下SELECT current_user, current_database();确认无误再做后续操作。5.3 一条几乎每天都用的查询语句最后分享一条我个人几乎每天都会用到的基础查询。如果你只想记住一条命令除开\du那就是这条SELECT rolname, rolsuper, rolcanlogin, rolcreatedb FROM pg_roles WHERE rolname NOT LIKE pg_% ORDER BY rolsuper DESC, rolname;它能把业务相关的角色一次性列全同时把超级用户排在前面方便一眼看出哪些账号权限最高。默认过滤掉了pg_开头的系统角色输出干净配合psql -f也能直接用于巡检留痕。等你把这条用熟了再回去看\du会觉得命令行输出的信息密度实在太低了。