PostgreSQL：获取成员角色和权限

飞象数据 2022-07-30

767

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 包含所有角色的系统表，以及一些附加信息（可以登录，是超级用户是/否等）。这里值得注意的是，每个用户都有一个内部编号（= OID）来标识角色。

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

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 的OID 都保留给系统对象。因此我排除了那些，因为我们只对我们自己创建的用户感兴趣。使系统目录更具可读性,需要一些连接操作，您将在下一节中看到一样。

解析 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 直接转换为用户名，以便我们可以更轻松地阅读它。不需要额外的连接来关联OID得到用户名。

最后 …

如果您对一般安全性感兴趣，我想推荐“ PostgreSQL TDE ”：https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/，这是一个更安全的 PostgreSQL 版本，能够加密磁盘上的数据。它可以从我们的网站下载。

postgresql

文章转载自飞象数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL：获取成员角色和权限

准备用户和角色

解析 PostgreSQL 中的用户和角色成员关系

最后 …

评论