POSTGRESQL: 获取成员角色和权限

PostgreSQL中文社区

作者简介

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, rolcanloginFROM pg_authidWHERE 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），用于标识角色。

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

test=# SELECT *FROM pg_auth_membersWHERE 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

继续滑动看下一个