POSTGRESQL: 获取成员角色和权限__POSTGRESQL: 获取成员角色和权限__PostgreSQL中文社区: 世界上功能最强大的开源数据库....

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

POSTGRESQL: 获取成员角色和权限

原作者：Hans-Jürgen　　创作时间：2021-09-26 15:49:06+08　　

wangliyun 发布于2021-09-27 08:00:06　　

评论: 4　　

顶: 638　

踩: 620　

### **作者简介**

#### 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;
```
![CENTER_PostgreSQL_Community]( /images/news/2021/20210926_1554_图片1.--9_.26png_.png)

#### 现在的目标是弄清楚如何嵌套角色以及将哪个角色分配给其他角色。在查看最终查询之前，先检查一下系统目录并了解数据的存储方式。我们先来看一下角色：
```
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），用于标识角色。
#### 此处的第二个重要系统表是pg_auth_members。它基本上知道将哪个角色分配给哪个其他角色。这是一个简单的角色/成员列表，其中包含用于标识我们用户的对象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**

评论:4　　

顶: 638　

踩: 620　

请在登录后发表评论，否则无法保存。

1# __ xueshancheng 回答于 2024-01-05 16:19:38+08

牛，这个脚本不错，感谢楼主分享

2# __ dengyu 回答于 2023-08-26 20:48:38+08

666 写的不错啊

3# __ doudou586 回答于 2023-08-11 23:39:44+08