2026年终极指南：如何在 PostgreSQL 中高效列出与管理用户

2026-02-04 07:42:55 0条评论 2次阅读 0人点赞

作为与 PostgreSQL 打交道的开发者或数据库管理员，我们都知道数据库管理不仅仅是关于存储和查询数据，更核心的在于如何安全、高效地控制“谁能访问数据”。无论你是为了进行日常的权限审计，还是为了排查棘手的连接问题，掌握一种清晰、快速的方法来列出数据库中的所有用户（在 PostgreSQL 中通常被称为“角色”）都是一项必备的技能。

随着我们步入 2026 年，数据安全和合规性要求变得比以往任何时候都更加严格。在一个充满微服务和云原生架构的世界里，数据库往往承载着来自多个服务的并发请求。我们经常接手遗留的数据库项目，第一步总是：“这里面到底有哪些账号？谁拥有超级用户权限？有没有被遗忘的僵尸账号？”

在这篇文章中，我们将作为你的实战指南，不仅教你如何列出用户，还会带你深入理解这背后的权限体系。我们将一起探索最简单快捷的命令行方法，以及最灵活的 SQL 查询技巧，并融入现代 AI 辅助开发和“Vibe Coding”的理念，看看如何用 2026 年的视角来审视这些基础操作。

在开始之前，让我们先达成一个共识：在 PostgreSQL 的世界里，用户 和 角色 在本质上是同一个概念。理解这一点对于后续的学习至关重要。通过列出用户，我们可以实现以下关键的管理目标：

  • 安全审计：快速识别谁拥有数据库的访问权限，确保没有多余的钥匙落在错误的人手中。
  • 权限管理：清晰地查看现有的角色分配，以便更合理地规划 角色继承 和 权限委派。
  • 维护合规性：定期检查账户状态，执行严格的 审计 流程，确保符合安全标准。
  • 清理与优化：识别 不活跃 或 不必要的 账户，及时清理以降低潜在的安全风险。

方法一：使用 psql 命令行工具快速查看

对于绝大多数开发者来说，最直接、最常用的方法莫过于使用 PostgreSQL 自带的交互式终端——psql。这里有一个“魔法命令”，可以让你瞬间获取所有角色的概览。

#### 理解 \du 命令

在 psql 界面中，\du 是一个元命令，专门用于显示所有的数据库角色。它的全称其实是 "Display users"（显示用户）。这是检索所有用户列表最简单的方法，不需要编写任何 SQL 语句即可执行。

# 这是 PostgreSQL 中列出用户最常用的快捷命令
\du

#### 实战演练：一步步带你操作

让我们通过一个实际的场景，来看看如何在终端中完成这个操作。假设我们刚刚登录到服务器，准备检查数据库状态。

步骤 1：登录 PostgreSQL 数据库

首先，我们需要打开操作系统的命令行（终端）并登录到 PostgreSQL。通常我们会使用 postgres 超级用户身份进行登录，具体命令如下：

# 使用 psql 工具以 postgres 用户身份登录
# 系统会提示你输入密码
psql -U postgres

输入此命令后，终端会询问数据库密码。一旦你成功验证，你将进入 psql 的交互式 shell，看到类似于 postgres=# 的提示符，这意味着你已经准备好接受命令了。

步骤 2：执行 \du 获取用户列表

现在，我们只需键入以下命令并按回车键：

\du

一旦执行此命令，终端会立即向我们返回一个格式化的表格。这个表格不仅列出了用户名，还直观地展示了他们的属性列表。这比通过复杂的 SQL 查询要快得多。

解读输出结果：

你会看到类似如下的表格（具体内容取决于你的数据库）：

Role Name

Attributes

Member of —

—

— postgres

Superuser, Create role, Create DB, Replication, Bypass RLS

{} admin_user

Create role, Create DB

{} readonly {reportinggroup}
  • rolname (Role Name)：这是用户或角色的名称，例如 INLINECODE9e4861d7 或 INLINECODEfead4bf3。
  • Attributes (属性)：这里显示了该用户的权限级别，例如：

* Superuser：超级用户，拥有无限制的权限。

* Create role：该用户有权创建新的角色（用户）。

* Create DB：该用户有权创建新的数据库。

* Replication：该用户可以发起流复制。

  • Member of：显示该用户属于哪个组角色。

步骤 3：优雅地退出

查看完信息后，为了安全起见，我们应该退出数据库会话。只需在提示符下键入：

\q

#### 高级技巧：更详细的输出

有时，标准的 INLINECODEb902d55b 可能无法提供足够详细的信息（比如它没有显示用户 ID）。如果你想看更多细节，我们可以使用 INLINECODE35c8d7b7 命令：

# 显示更详细的角色信息，包括用户ID和连接限制等
\du+

方法二：使用 SQL 查询深入底层

虽然 \du 命令很方便，但如果我们需要在应用程序中获取用户列表，或者想要对用户数据进行更复杂的筛选和格式化，直接查询底层的系统表是更专业的方法。

在 PostgreSQL 中，所有的用户信息并不存储在一个普通的表中，而是存储在系统目录视图 INLINECODEc99ae9ba 或 INLINECODE33d87ad0 中。

#### 基础 SQL 查询示例

让我们从一个最简单的查询开始，获取所有用户的名称：

-- 从系统目录中查询所有用户的名称
SELECT usename 
FROM pg_catalog.pg_user;

#### 高级 SQL 查询：带权限逻辑判断

在实际的生产环境中，我们通常不仅仅需要名字，还需要知道这些用户的具体权限组合。由于 PostgreSQL 的权限是位图形式存储的，直接看字段（如 INLINECODE7f3da3b4, INLINECODE07e01a6b）全是 INLINECODE48140c0e 或 INLINECODE48ace892，不够直观。

我们可以使用 CASE WHEN 语句将这些布尔值转化为人类可读的文本。下面是一个非常实用的查询脚本，建议你保存到你的工具箱中：

SELECT 
  usename AS role_name,
  -- 使用 CASE 逻辑将权限状态转换为易读的字符串描述
  CASE
    WHEN usesuper AND usecreatedb THEN
      CAST(‘超级用户, 可建库‘ AS pg_catalog.text)
    WHEN usesuper THEN
      CAST(‘超级用户‘ AS pg_catalog.text)
    WHEN usecreatedb THEN
      CAST(‘可建库‘ AS pg_catalog.text)
    ELSE
      CAST(‘普通用户‘ AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name DESC;

代码详解：

  • rolename：我们使用 INLINECODEc616a786 字段作为角色的显示名称。
  • roleattributes：这里我们通过逻辑判断合并了 INLINECODE7783433d（是否为超级用户）和 usecreatedb（是否可建库）两个布尔字段。如果一个用户同时拥有这两个权限，他会显示为“超级用户, 可建库”；如果是普通用户，则显示“普通用户”。
  • ORDER BY：为了让结果更整洁，我们按照角色名称降序排列。

#### 查询包含连接限制的用户

有时候，我们需要查看哪些用户设置了连接数限制，以防止某个用户耗尽数据库资源。我们可以通过查询 pg_user 结合连接属性来实现这一点：

-- 查询所有用户的连接数限制和有效期设置
SELECT 
    usename AS username,
    useconnlimit AS max_connections, -- 连接限制，-1 表示无限制
    valuntil AS password_expiration   -- 密码过期时间
FROM 
    pg_catalog.pg_user 
WHERE 
    useconnlimit != -1;  -- 只筛选出有连接限制的用户

2026 深度实战：构建动态权限视图与僵尸账户检测

随着我们步入 2026 年，静态的列表往往无法满足现代 DevOps 的需求。我们需要更动态、更结构化的数据来驱动自动化工具。让我们来看看如何将这种查询升级为一种“即插即用”的数据服务。

#### 构建包含组成员关系的完整视图

PostgreSQL 的权限体系非常强大，支持角色继承。一个用户可以属于多个组，而这些组决定了他的最终权限。为了审计这一点，我们需要查询 pg_auth_members 系统表。

让我们编写一个能够“透视”用户所属组的 SQL 脚本。这是我们管理多租户 SaaS 应用的核心脚本之一：

-- 高级查询：透视用户及其所属的组角色
-- 这对于排查“为什么这个用户有删除权限？”非常有用
SELECT 
    r.rolname AS user_name,
    ARRAY_agg(g.rolname) AS member_of_groups,
    r.rolsuper AS is_super,
    r.rolcreaterole AS can_create_role,
    r.rolcreatedb AS can_create_db,
    r.rolcanlogin AS can_login,
    r.rolconnlimit AS connection_limit,
    r.rolvaliduntil AS password_expiry
FROM 
    pg_catalog.pg_roles r
-- 左连接成员关系表，找出该用户所属的所有组
LEFT JOIN 
    pg_catalog.pg_auth_members m ON m.member = r.oid
LEFT JOIN 
    pg_catalog.pg_roles g ON m.roleid = g.oid
WHERE 
    r.rolname NOT LIKE ‘pg_%‘ -- 排除系统内置角色
GROUP BY 
    r.rolname, r.rolsuper, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit, r.rolvaliduntil
ORDER BY 
    user_name ASC;

这段代码的实战意义：

  • ARRAYagg(g.rolname)：这是一个非常优雅的聚合函数使用。它将一个用户所属的多个组合并成一个数组。在输出结果中，你可以直观地看到 INLINECODEc6ce2cee 和 admin_group 被列在一起。
  • JOIN 策略：我们使用 LEFT JOIN 确保即使是不属于任何组的孤立用户也会被显示出来。这对于安全审计至关重要——孤立的用户往往是安全隐患。
  • 过滤系统噪音：WHERE r.rolname NOT LIKE ‘pg_%‘ 这一行帮助我们过滤掉 PostgreSQL 内部生成的复杂系统角色，让我们专注于业务用户。

#### AI 辅助审计与僵尸账户猎杀

作为现代开发者，我们已经进入了“Vibe Coding”（氛围编程）的时代。这意味着我们不仅是代码的编写者，更是 AI 工具的指挥官。当我们需要列出用户并进行审计时，为什么不利用 AI 来帮助我们编写那些繁琐的 SQL 脚本呢？

在我们的最新实践中，我们通常不会手动去写那些复杂的 CASE WHEN 逻辑。相反，我们会使用像 Cursor 或 GitHub Copilot 这样的 AI IDE 工具。你可以这样输入提示词：“生成一个 PostgreSQL 查询，列出所有非超级用户，并且他们的密码将在 30 天内过期。” AI 会瞬间为你生成精准的 SQL 代码。

为了满足企业级合规性要求（这在 2026 年尤为重要），我们需要一个更健壮的解决方案。下面的脚本不仅列出用户，还会检查潜在的“僵尸账户”——即那些拥有登录权限但长期未活动的账户。

我们可以结合 INLINECODEe8b6b453（查看当前活动）和 INLINECODEda80703a 来生成一份报告。

-- 企业级审计查询：识别潜在的不活跃特权账户
-- 这个查询试图找出拥有高权限但可能不再被使用的角色
SELECT 
    r.rolname, 
    r.rolsuper, 
    r.rolcreaterole, 
    r.rolcreatedb, 
    r.rolcanlogin, 
    r.rolconnlimit, 
    r.rolvaliduntil,
    -- 检查该角色是否有任何活跃的会话（基于 pg_stat_activity 的快照）
    COALESCE((SELECT count(*) FROM pg_stat_activity WHERE usename = r.rolname), 0) as active_connections,
    -- 计算账户自创建以来的天数（假设 oid 大致排序，实际应使用额外审计表）
    (SELECT age(oid) FROM pg_roles WHERE oid = r.oid) as account_age_estimated
FROM 
    pg_roles r
WHERE 
    r.rolcanlogin = true -- 只关注可以登录的用户
    AND r.rolname NOT LIKE ‘pg_%‘ -- 排除系统角色
ORDER BY 
    active_connections ASC, -- 把没有连接的账户排在前面
    r.rolvaliduntil ASC; -- 快过期的排在前面

深入解析这段代码的决策逻辑：

  • 安全左移：我们不再等到被攻击才发现异常账号，而是通过脚本主动扫描。r.rolcanlogin = true 保证了我们只关注那些实际具有登录风险的账号。
  • 资源优化：通过 INLINECODE9cf33b85 结合 INLINECODEb0387ec4，我们不仅能看到权限，还能实时看到资源占用。如果一个拥有 INLINECODEe6112071 权限的账号 INLINECODE7631d293 长期为 0，它就是一个待查的“僵尸账户”候选者。
  • 时间维度：rolvaliduntil 是关键。在 2026 年，短期的凭证过期策略是标配，我们按这个字段排序，确保首先处理即将过期的关键权限账户。

进阶场景：云原生与边缘计算中的权限管理

在云原生和边缘计算的架构下，数据库往往运行在动态变化的容器或边缘节点上。这给传统的用户管理带来了新的挑战。

#### 处理连接池与伪装用户

你可能遇到过这样的情况：应用层使用了 PgBouncer 或外部连接池。这时，你在数据库中看到的 INLINECODEd61d2df9 可能全是 INLINECODE0667fb6f 这个用户，而不是真实的业务用户。这会让审计变得复杂。

为了解决这个问题，现代 PostgreSQL 应用通常会在应用层设置 application_name。我们可以利用这一点来更智能地列出“用户”

-- 现代应用审计：通过 application_name 追踪真实服务
SELECT 
    usename,
    application_name,
    client_addr,
    state,
    backend_start
FROM 
    pg_stat_activity
WHERE 
    state != ‘idle‘ -- 排除空闲连接，只看活跃事务
    AND usename != ‘pgbouncer‘; -- 排除连接池用户本身

通过这种方式，我们实际上是在“透视”背后的服务流量，而不仅仅是在看静态的用户列表。

#### Serverless 环境下的冷启动考虑

在 Serverless 数据库（如 AWS Aurora Serverless v2 或 Google Cloud AlloyDB）中，频繁的 SELECT * FROM pg_user 查询通常成本极低，因为它们只访问共享的内存中的系统目录。但是，如果你编写了复杂的、涉及大量 joins 的审计脚本，需要注意它们可能会触发更多的磁盘 I/O。在 2026 年，即使是系统查询，我们也应该关注其计算成本，坚持“最轻量级”原则。

常见问题与最佳实践

在与 PostgreSQL 用户管理的日常工作中，我们经常会遇到一些误区。这里分享几点见解，希望能帮助你避开坑洼。

1. 用户 vs 角色：概念混淆

你可能会注意到，在创建用户时，PostgreSQL 通常提示 "CREATE ROLE"。这是因为在 PostgreSQL 中，CREATE USER 和 CREATE ROLE 几乎是完全一样的命令，唯一的区别在于 CREATE USER 默认会赋予 LOGIN 权限（即可以登录数据库），而 CREATE ROLE 默认没有。所以，当你列出用户时，你实际上是在列出所有拥有 LOGIN 权限的角色。

2. 避免硬编码权限检查

在编写应用程序代码时，不要试图自己解析 pg_user 表来判断一个用户是否有某个权限。最好使用 PostgreSQL 提供的内置函数，例如：

-- 检查当前用户是否为超级用户
SELECT current_user, session_user;

3. 性能考量

查询 INLINECODEf3f5b100 和 INLINECODE606f6899 视图是非常轻量级的操作，因为它们直接访问内存中的系统缓存。你不必担心在高并发的生产环境中频繁运行这些查询会造成性能瓶颈。

结语

在 PostgreSQL 中列出用户是一项看似简单但至关重要的数据库管理任务。正如我们所见，我们既可以使用 \du 命令在终端中快速获取信息，也可以编写强大的 SQL 查询 来集成到我们的自动化脚本或应用程序中。更重要的是，结合 2026 年的 AI 辅助开发理念和云原生架构思维，我们可以将这些基础技能提升到一个新的高度，实现自动化的安全审计和更智能的权限治理。掌握这些技巧，将让你对数据库的访问控制了如指掌，从而构建更安全、更稳定的数据管理系统。希望这篇指南能帮助你更自信地管理你的 PostgreSQL 数据库！

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。如需转载，请注明文章出处豆丁博客和来源网址。https://shluqu.cn/24835.html
点赞
0.00 平均评分 (0% 分数) - 0 票

相关文章美国1G带宽/1T流量高速vps $17.99/年