​作者简介

Hans-Jürgen Schönig cybertec公司工程师。

译者简介

王志斌，从事数据库产品相关工作，主要致力于postgresql数据库高可用解决方案及云端产品化工作。

校对者简介

崔鹏，PostgreSQL爱好者,海能达PostgreSQL高级DBA。

PostgreSQL提供了一个高度复杂和强大的安全权限系统。它允许您定义用户（=角色）、组等。然而如果没有图形用户界面，通常要想知道分配给哪个角色有点棘手。下面的博客文章解释了如何做到这一点。了解如何检索PostgreSQL中角色和角色成员身份的信息。

准备用户和角色

为了展示如何分析用户，我们首先创建几个用户和角色：

CREATE USER a;
CREATE USER b;
CREATE ROLE c LOGIN;
CREATE ROLE d LOGIN;
CREATE ROLE e LOGIN;
CREATE ROLE f LOGIN;

这里需要注意的重要一点是，“用户”和“角色”基本上是一样的。主要区别在于角色总是NOLOGIN，而“用户”是LOGIN。但是，如果希望某个角色能够登录，只需将其标记为登录即可。否则二者之间没有区别。在后台，角色和用户都是一样的。

现在，我们可以使用简单的GRANT语句将角色分配给其他角色（=用户）：

GRANT c TO a;
GRANT d TO c;
GRANT e TO c;
GRANT f TO d;

现在的目标是弄清楚如何嵌套角色以及将哪个角色分配给其他角色。在查看最终查询之前，先检查一下系统目录并了解数据的存储方式。我们先来看一下角色：

test=# SELECT oid, rolname, rolcanlogin
FROM pg_authid
WHERE oid > 16384;
     oid | rolname | rolcanlogin
---------+---------+-------------
 1098572 | a       | t
 1098573 | b       | t
 1098574 | c       | t
 1098575 | d       | t
 1098576 | e       | t
 1098577 | f       | t
(6 rows)

pg_authid包含所有角色的列表，以及一些其他信息（可以登录，超级用户是/否，等等）。这里值得注意的是，每个用户都有一个内部编号（=对象ID），用于标识角色。

此处的第二个重要系统表是pgauthmembers。它基本上知道将哪个角色分配给哪个其他角色。这是一个简单的角色/成员列表，其中包含用于标识我们用户的对象ID。以下查询显示了我的系统表包含的内容：

test=# SELECT *
FROM pg_auth_members
WHERE roleid > 16384;
 roleid  | member  | grantor | admin_option
---------+---------+---------+--------------
 1098574 | 1098572 | 10      | f
 1098575 | 1098574 | 10      | f
 1098576 | 1098574 | 10      | f
 1098577 | 1098575 | 10      | f
(4 rows)

在PostgreSQL中，所有低于16384的对象ID均保留用于系统对象。因此我将它们排除在外，因为我们只对自己创建的用户感兴趣。要使系统目录更具可读性，需要进行一些合并，如您将在下一部分中看到的。

解决PostgreSQL中的用户和角色成员资格

在PostgreSQL中，角色/用户可以嵌套。嵌套可以无限深，这就是为什么有必要编写递归的原因，请参见下文：

test=# WITH RECURSIVE x AS
(
  SELECT member::regrole,
         roleid::regrole AS role,
         member::regrole || ' -> ' || roleid::regrole AS path
  FROM pg_auth_members AS m
  WHERE roleid > 16384
  UNION ALL
  SELECT x.member::regrole,
         m.roleid::regrole,
         x.path || ' -> ' || m.roleid::regrole
 FROM pg_auth_members AS m
    JOIN x ON m.member = x.role
  )
  SELECT member, role, path
  FROM x
  ORDER BY member::text, role::text;
 member | role | path
--------+------+------------------
      a | c    | a -> c
      a | d    | a -> c -> d
      a | e    | a -> c -> e
      a | f    | a -> c -> d -> f
      c | d    | c -> d
      c | e    | c -> e
      c | f    | c -> d -> f
      d | f    | d -> f
(8 rows)

该查询显示所有角色及其嵌套方式的列表。诀窍主要是“regrole”数据类型，它使我们可以将对象ID直接转换为用户名，以便我们更轻松地读取它，而无需其他联接即可解析ID。

点击文章底部“阅读原文”查看原文内容！

PostgreSQL中文社区欢迎广大技术人员投稿

投稿邮箱：press@postgres.cn