注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：
PostgreSQL数据库数据库角色的使用及预定义角色的原理
文章快速说明索引
学习目标：
做数据库内核开发久了就会有一种 少年得志，年少轻狂 的错觉，然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间 将着重于学习分享Postgres的基础知识和实践内幕。
学习内容：（详见目录）
1、PostgreSQL数据库数据库角色的使用及预定义角色的原理
学习时间：
2024年04月30日 21:32:22
学习产出：
1、PostgreSQL数据库基础知识回顾 1个
2、CSDN 技术博客 1篇
3、PostgreSQL数据库内核深入学习
注：下面我们所有的学习环境是Centos8+PostgreSQL master +Oracle19C+MySQL8.0
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)
postgres=#
#-----------------------------------------------------------------------------#
SQL> select * from v$version;
BANNER Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
BANNER_FULL Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
CON_ID 0
#-----------------------------------------------------------------------------#
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.06 sec)
mysql>
功能使用背景说明
数据库角色
首先看一下官方文档的说明，如下：
数据库角色在概念上已经完全与操作系统用户独立开来。事实上可能维护一个对应关系会比较方便，但是这并非必需。数据库角色在一个数据库集簇安装范围内是全局的（而不是独立数据库内）。要创建一个角色，可使用CREATE ROLE SQL 命令：
CREATE ROLE name;
name遵循 SQL 标识符的规则：或是未经装饰没有特殊字符，或是用双引号包围（实际上，你将总是给该命令要加上额外选项，例如LOGIN。更多细节可见下文）。要移除一个已有的角色，使用相似的DROP ROLE命令：
DROP ROLE name;
为了方便，createuser和dropuser程序被提供作为这些 SQL 命令的包装器，它们可以从 shell 命令行调用：
createuser name
dropuser name
要决定现有角色的集合，检查pg_roles系统目录，例如：
[postgres@localhost:~/test/bin]$ ./pg_ctl start -D test/
waiting for server to start....2024-04-30 00:44:52.507 PDT [27569] LOG: starting PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
2024-04-30 00:44:52.508 PDT [27569] LOG: listening on IPv6 address "::1", port 5432
2024-04-30 00:44:52.508 PDT [27569] LOG: listening on IPv4 address "127.0.0.1", port 5432
2024-04-30 00:44:52.512 PDT [27569] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-04-30 00:44:52.532 PDT [27572] LOG: database system was shut down at 2024-04-30 00:39:54 PDT
2024-04-30 00:44:52.537 PDT [27569] LOG: database system is ready to accept connections
done
server started
[postgres@localhost:~/test/bin]$ ./psql
psql (17devel)
Type "help" for help.
postgres=# select * from pg_roles ;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------
postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10
pg_database_owner | f | t | f | f | f | f | -1 | ******** | | f | | 6171
pg_read_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6181
pg_write_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6182
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569
pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570
pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
pg_checkpoint | f | t | f | f | f | f | -1 | ******** | | f | | 4544
pg_maintain | f | t | f | f | f | f | -1 | ******** | | f | | 9256
pg_use_reserved_connections | f | t | f | f | f | f | -1 | ******** | | f | | 4550
pg_create_subscription | f | t | f | f | f | f | -1 | ******** | | f | | 6304
(16 rows)
postgres=#
psql程序的\du元命令也可以用来列出现有角色，如下：
[postgres@localhost:~/test/bin]$ ./psql
psql (17devel)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
postgres=#
为了引导数据库系统，一个刚刚被初始化好的系统总是包含一个预定义角色。这个角色总是一个superuser，并且默认情况下（除非在运行initdb时修改）它的名字和初始化数据库集簇的操作系统用户相同。习惯上，这个角色将被命名为postgres。为了创建更多角色，你首先必须以初始角色的身份连接。
每一个到数据库服务器的连接都是使用某个特定角色名建立的，并且这个角色决定发起连接的命令的初始访问权限。要使用一个特定数据库连接的角色名由客户端指示，该客户端以一种应用相关的风格发起连接请求。例如，psql程序使用-U命令行选项来指定要以哪个角色连接。很多应用假定该名字默认是当前操作系统用户（包括createuser和psql）。因此在角色和操作系统用户之间维护一个名字对应关系通常是很方便的。
一个给定客户端连接能够用来连接的数据库角色的集合由该客户端的认证设置决定，这些在其他章中有解释（因此，一个客户端不止限于以匹配其操作系统用户的角色连接，就像一个人的登录名不需要匹配她的真实名字一样）。因为角色身份决定一个已连接客户端可用的权限集合，在设置一个多用户环境时要小心地配置权限。
角色属性
一个数据库角色可以有一些属性，它们定义角色的权限并且与客户端认证系统交互。
login privilege：只有具有LOGIN属性的角色才能被用于一个数据库连接的初始角色名称。一个带有LOGIN属性的角色可以被认为和一个 数据库用户 相同。要创建一个带有登录权限的角色，使用两者之一：
CREATE ROLE name LOGIN;
CREATE USER name;
CREATE USER和CREATE ROLE等效，除了CREATE USER默认假定有LOGIN，而CREATE ROLE不这样认为，如下：
// src/backend/parser/gram.y
/*****************************************************************************
*
* Create a new Postgres DBMS user (role with implied login ability)
*
*****************************************************************************/
CreateUserStmt:
CREATE USER RoleId opt_with OptRoleList
{
CreateRoleStmt *n = makeNode(CreateRoleStmt);
n->stmt_type = ROLESTMT_USER;
n->role = $3;
n->options = $5;
$$ = (Node *) n;
}
;
...
/*****************************************************************************
*
* Create a new Postgres DBMS role
*
*****************************************************************************/
CreateRoleStmt:
CREATE ROLE RoleId opt_with OptRoleList
{
CreateRoleStmt *n = makeNode(CreateRoleStmt);
n->stmt_type = ROLESTMT_ROLE;
n->role = $3;
n->options = $5;
$$ = (Node *) n;
}
;
这两者的区别，如下：
// src/backend/commands/user.c
/*
* CREATE ROLE
*/
Oid
CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
{
...
/* The defaults can vary depending on the original statement type */
switch (stmt->stmt_type)
{
case ROLESTMT_ROLE:
break;
case ROLESTMT_USER:
canlogin = true;
/* may eventually want inherit to default to false here */
break;
case ROLESTMT_GROUP:
break;
}
...
}
superuser status：一个数据库超级用户会绕开所有权限检查，除了登入的权利。这是一个危险的权限并且应该小心使用，最好用一个不是超级用户的角色来完成你的大部分工作。要创建一个新数据库超级用户，使用CREATE ROLE name SUPERUSER。你必须作为一个超级用户来完成这些。
database creation：一个角色必须被显式给予权限才能创建数据库（除了超级用户，因为它们会绕开所有权限检查）。要创建这样一个角色，使用CREATE ROLE name CREATEDB。
role creation：一个角色必须被显式给予权限才能创建更多角色（除了超级用户，因为它们会绕开所有权限检查）。要创建这样一个角色，使用CREATE ROLE name CREATEROLE。一个带有CREATEROLE权限的角色也可以修改和删除其他角色，还可以授予或回收角色中的成员关系。然而，要创建、修改、删除或修改一个超级用户角色的成员关系，需要以超级用户的身份操作。CREATEROLE不足以完成这一切。
initiating replication：一个角色必须被显式给予权限才能发起流复制（除了超级用户，因为它们会绕开所有权限检查）。一个被用于流复制的角色必须也具有LOGIN权限。要创建这样一个角色，使用CREATE ROLE name REPLICATION LOGIN。
password：只有当客户端认证方法要求用户在连接数据库时提供一个口令时，一个口令才有意义。password和md5认证方法使用口令。数据库口令与操作系统命令独立。在角色创建时指定一个口令：CREATE ROLE name PASSWORD ‘string’。
注：一个好习惯是创建一个具有CREATEDB和CREATEROLE权限的角色，而不是创建一个超级用户，并且然后用这个角色来完成对数据库和角色的例行管理。这种方法避免了在非必要时作为超级用户操作任务的风险。
在创建后可以用ALTER ROLE修改一个角色属性。一个角色也可以有角色相关的默认值。例如，如果出于某些原因你希望在每次连接时禁用索引扫描（提示：不是好主意），你可以使用：
ALTER ROLE myname SET enable_indexscan TO off;
这将保存设置（但是不会立刻设置它）。在这个角色的后续连接中，它就表现得像在会话开始之前执行过SET enable_indexscan TO off。你也可以在会话期间改变该设置，它将只是作为默认值。要移除一个角色相关的默认设置，使用ALTER ROLE rolename RESET varname。注意附加到没有LOGIN权限的角色的角色相关默认值相当无用，因为它们从不会被调用。
注：后面这个我会另起一篇博客去详解！
角色成员关系
把用户分组在一起来便于管理权限常常很方便：那样，权限可以被授予一整个组或从一整个组回收。在PostgreSQL中通过创建一个表示组的角色来实现，并且然后将在该组角色中的成员关系授予给单独的用户角色。
要建立一个组角色，首先创建该角色：
CREATE ROLE name;
通常被用作一个组的角色不需要有LOGIN属性，不过如果你希望你也可以设置它。一旦组角色存在，你可以使用GRANT 和 REVOKE 命令增加和移除成员：
GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;
你也可以为其他组角色授予成员关系（因为组角色和非组角色之间其实没有任何区别）。数据库将不会让你设置环状的成员关系。另外，不允许把一个角色中的成员关系授予给PUBLIC。
组角色的成员可以以两种方式使用角色的权限：
- 第一，一个组的每一个成员可以显式地做SET ROLE来临时 成为 组角色。在这种状态中，数据库会话可以访问组角色而不是原始登录角色的权限，并且任何被创建的数据库对象被认为属于组角色而不是登录角色
- 第二，有INHERIT属性的成员角色自动地具有它们所属角色的权限，包括任何组角色继承得到的权限。作为一个例子，假设我们已经有：
CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;
在作为角色joe连接后，一个数据库会话将立即拥有直接授予给joe的权限，外加任何授予给admin的权限，因为joe 继承了 admin的权限。然而，授予给wheel的权限不可用，因为即使joe是wheel的一个间接成员，但是该成员关系是通过带NOINHERIT属性的admin得到的。在：
SET ROLE admin;
之后，该会话将只拥有授予给admin的权限，但是没有授予给joe的权限。在执行：
SET ROLE wheel;
之后，该会话将只拥有授予给wheel的权限，但是没有授予给joe或admin的权限。初始的权限状态可以使用下面命令之一恢复：
SET ROLE joe;
SET ROLE NONE;
RESET ROLE;
注意：SET ROLE命令总是允许选择原始登录角色的直接或间接组角色。因此，在上面的例子中，在成为wheel之前不必先成为admin。
注意：在 SQL 标准中，用户和角色之间的区别很清楚，并且用户不会自动继承权限而角色会继承。这种行为在PostgreSQL中也可以实现：为要用作 SQL 角色的角色给予INHERIT属性，而为要用作 SQL 用户的角色给予NOINHERIT属性。不过，为了向后兼容 8.1 以前的发布（在其中用户总是拥有它们所在组的权限），PostgreSQL默认给所有的角色INHERIT属性。
角色属性LOGIN、SUPERUSER、CREATEDB和CREATEROLE可以被认为是一种特殊权限，但是它们从来不会像数据库对象上的普通权限那样被继承。要使用这些属性，你必须实际SET ROLE到一个有这些属性之一的特定角色。继续上述例子，我们可以选择授予CREATEDB和CREATEROLE给admin角色。然后一个以joe角色连接的会话将不会立即有这些权限，只有在执行了SET ROLE admin之后才会拥有。
要销毁一个组角色，使用DROP ROLE：
DROP ROLE name;
任何在该组角色中的成员关系会被自动撤销（但是成员角色不会受到影响）。
删除角色
由于角色可以拥有数据库对象并且能持有访问其他对象的特权，删除一个角色常常并非一次DROP ROLE就能解决。任何被该用户所拥有的对象必须首先被删除或者转移给其他拥有者，并且任何已被授予给该角色的权限必须被收回。
对象的拥有关系可以使用重新分配拥有的命令一次转移出去，例如：
ALTER TABLE bobs_table OWNER TO alice;
此外，重新分配拥有的命令可以被用来把要被删除的角色所拥有的所有对象的拥有关系转移给另一个角色。由于 重新分配拥有 不能访问其他数据库中的对象，有必要在每一个包含该角色所拥有对象的数据库中运行该命令（注意第一个这样的 重新分配拥有 将更改任何在数据库间共享的该角色拥有的对象的拥有关系，即数据库或者表空间）。
一旦任何有价值的对象已经被转移给新的拥有者，任何由被删除角色拥有的剩余对象就可以用DROP OWNED命令删除。再次，由于这个命令不能访问其他数据库中的对象，有必要在每一个包含该角色所拥有对象的数据库中运行该命令。还有，DROP OWNED将不会删除整个数据库或者表空间，因此如果该角色拥有任何还没有被转移给新拥有者的数据库或者表空间，有必要手工删除它们。
DROP OWNED 还负责删除授予目标角色的不属于目标角色的对象的任何权限。因为REASSIGN OWNED不会涉及这类对象，通常有必要运行REASSIGN OWNED(重新分配拥有的)和 DROP OWNED（按照这个顺序！）以完全地移除要被删除角色的从属物。
总之，移除曾经拥有过对象的角色的方法是：
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- 在集簇中的每一个数据库中重复上述命令
DROP ROLE doomed_role;
如果不是所有的拥有对象都被转移给了同一个后继拥有者，最好手工处理异常然后执行上述步骤直到结束。如果在依赖对象还存在时尝试了DROP ROLE，它将发出消息标识哪些对象需要被重新授予或者删除。
预定义角色
PostgreSQL提供了一组预定义角色，它们提供对特定的、通常需要的、需要特权的功能和信息的访问。管理员(包括具有CREATEROLE 权限的角色)可以把这些角色GRANT给其环境中的用户或者其他角色，让这些用户能够访问指定的功能和信息。
下表中描述了预定义的角色。注意由于额外功能的增加，每一种角色相关的权限可能会在未来被改变。管理员应该关注发行注记中提到的这方面的变化。
- pg_monitor、pg_read_all_settings、pg_read_all_stats和pg_stat_scan_tables角色的目的是允许管理员能为监控数据库服务器的目的很容易地配置角色。它们授予一组常用的特权，这些特权允许角色读取各种有用的配置设置、统计信息以及通常仅限于超级用户的其他系统信息
- pg_database_owner 角色有一个隐式 且 情况依赖的成员，也就是当前数据库的所有者。该角色开始没有传递任何特权。就像任何角色一样，它可以拥有对象或者接受访问权限的授予。因此，当pg_database_owner有模板数据库的权限，从该模板实例化的数据库的每个所有者都将运用这些权限。pg_database_owner不能成为任何角色的成员，并且它不能有非隐式成员
- pg_signal_backend角色想要允许管理员启用受信任的、但是非超级用户的、发送信号给其他后端的角色。当前，此角色允许发送信号以取消另一个后端上的查询或终止其会话。不过授予此角色的用户不能向属于超级用户的后端发送信号
- pg_read_server_files、pg_write_server_files以及pg_execute_server_program角色的目的是允许管理员有一些可信但不是超级用户的角色来访问文件以及以运行数据库的用户在数据库服务器上运行程序。由于这些角色能够访问服务器文件系统上的任何文件，因此在直接访问文件时它们会绕过任何数据库级别的权限检查并且它们可以被用来得到超级用户级别的访问，因此在把这些角色授予给用户时应当特别小心。
在授予这些角色时应当非常小心，以确保它们只被用在需要的地方，并且要理解这些角色会授予对特权信息的访问。管理员可以用GRANT命令把对这些角色的访问授予给用户，例如：
GRANT pg_signal_backend TO admin_user;
函数和触发器安全性
函数、触发器以及行级安全性策略允许用户在后端服务器中插入代码，其他用户不会注意到这些代码的执行。因此，这些机制允许用户相对容易地为其他人设置“特洛伊木马”。最强的保护是严格控制哪些人能定义对象。如果做不到，则编写查询时应该只引用具有可信任拥有者的对象。可以从search_path中去除public方案以及任何其他允许不可信用户创建对象的方案。
在后端服务器进程中运行的函数带有数据库服务器守护进程的操作系统权限。如果用于函数的编程语言允许非检查的内存访问，它就可能改变服务器的内部数据结构。因此，在很多其他事情中，这些函数可能绕开任何系统访问控制。允许这种访问的函数语言被认为是“不可信的”，并且PostgreSQL只允许超级用户创建用这些语言编写的函数。
预定义角色深入
相关系统表和视图
关于预定义角色的解释，上面已经说的非常清楚了 我们接下来看一些例子，深入研究一下：
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)
postgres=# select * from pg_authid ;
oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
------+-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
10 | postgres | t | t | t | t | t | t | t | -1 | |
6171 | pg_database_owner | f | t | f | f | f | f | f | -1 | |
6181 | pg_read_all_data | f | t | f | f | f | f | f | -1 | |
6182 | pg_write_all_data | f | t | f | f | f | f | f | -1 | |
3373 | pg_monitor | f | t | f | f | f | f | f | -1 | |
3374 | pg_read_all_settings | f | t | f | f | f | f | f | -1 | |
3375 | pg_read_all_stats | f | t | f | f | f | f | f | -1 | |
3377 | pg_stat_scan_tables | f | t | f | f | f | f | f | -1 | |
4569 | pg_read_server_files | f | t | f | f | f | f | f | -1 | |
4570 | pg_write_server_files | f | t | f | f | f | f | f | -1 | |
4571 | pg_execute_server_program | f | t | f | f | f | f | f | -1 | |
4200 | pg_signal_backend | f | t | f | f | f | f | f | -1 | |
4544 | pg_checkpoint | f | t | f | f | f | f | f | -1 | |
9256 | pg_maintain | f | t | f | f | f | f | f | -1 | |
4550 | pg_use_reserved_connections | f | t | f | f | f | f | f | -1 | |
6304 | pg_create_subscription | f | t | f | f | f | f | f | -1 | |
(16 rows)
postgres=#
这里区分一下相关的系统表和视图，如下：
## pg_authid包含关于数据库授权标识符（角色）的信息。角色把“用户”和“组”的概念包含在内
## pg_db_role_setting为每一个角色和数据库组合记录被设置到运行时配置变量的默认值
## 视图pg_user提供关于数据库用户的信息。这是pg_shadow的一个公共可读的视图，它消除了口令域
## 视图pg_roles提供了关于数据库角色的信息。这是pg_authid的一个公共可读视图，它隐去了口令域
## 视图pg_shadow的存在是为了向后兼容：它模拟了在PostgreSQL版本8.1之前的一个系统目录。它显示pg_authid中所有被标记为rolcanlogin的角色的属性。由于这个表包含口令，所以不能是公众可读的，这也是采用pg_shadow这个名字的原因。 而pg_user是pg_shadow上的一个公共可读视图，它屏蔽了口令域。
postgres=# \d+ pg_authid
Table "pg_catalog.pg_authid"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
oid | oid | | not null | | plain | | |
rolname | name | | not null | | plain | | |
rolsuper | boolean | | not null | | plain | | |
rolinherit | boolean | | not null | | plain | | |
rolcreaterole | boolean | | not null | | plain | | |
rolcreatedb | boolean | | not null | | plain | | |
rolcanlogin | boolean | | not null | | plain | | |
rolreplication | boolean | | not null | | plain | | |
rolbypassrls | boolean | | not null | | plain | | |
rolconnlimit | integer | | not null | | plain | | |
rolpassword | text | C | | | extended | | |
rolvaliduntil | timestamp with time zone | | | | plain | | |
Indexes:
"pg_authid_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
"pg_authid_rolname_index" UNIQUE CONSTRAINT, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"
Access method: heap
postgres=#
postgres=# \d+ pg_db_role_setting
Table "pg_catalog.pg_db_role_setting"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+--------+-----------+----------+---------+----------+-------------+--------------+-------------
setdatabase | oid | | not null | | plain | | |
setrole | oid | | not null | | plain | | |
setconfig | text[] | C | | | extended | | |
Indexes:
"pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Tablespace: "pg_global"
Access method: heap
postgres=#
postgres=# \d+ pg_user
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default | Storage | Description
--------------+--------------------------+-----------+----------+---------+----------+-------------
usename | name | | | | plain |
usesysid | oid | | | | plain |
usecreatedb | boolean | | | | plain |
usesuper | boolean | | | | plain |
userepl | boolean | | | | plain |
usebypassrls | boolean | | | | plain |
passwd | text | | | | extended |
valuntil | timestamp with time zone | | | | plain |
useconfig | text[] | C | | | extended |
View definition:
SELECT usename,
usesysid,
usecreatedb,
usesuper,
userepl,
usebypassrls,
'********'::text AS passwd,
valuntil,
useconfig
FROM pg_shadow;
postgres=# \d+ pg_roles
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default | Storage | Description
----------------+--------------------------+-----------+----------+---------+----------+-------------
rolname | name | | | | plain |
rolsuper | boolean | | | | plain |
rolinherit | boolean | | | | plain |
rolcreaterole | boolean | | | | plain |
rolcreatedb | boolean | | | | plain |
rolcanlogin | boolean | | | | plain |
rolreplication | boolean | | | | plain |
rolconnlimit | integer | | | | plain |
rolpassword | text | | | | extended |
rolvaliduntil | timestamp with time zone | | | | plain |
rolbypassrls | boolean | | | | plain |
rolconfig | text[] | C | | | extended |
oid | oid | | | | plain |
View definition:
SELECT pg_authid.rolname,
pg_authid.rolsuper,
pg_authid.rolinherit,
pg_authid.rolcreaterole,
pg_authid.rolcreatedb,
pg_authid.rolcanlogin,
pg_authid.rolreplication,
pg_authid.rolconnlimit,
'********'::text AS rolpassword,
pg_authid.rolvaliduntil,
pg_authid.rolbypassrls,
s.setconfig AS rolconfig,
pg_authid.oid
FROM pg_authid
LEFT JOIN pg_db_role_setting s ON pg_authid.oid = s.setrole AND s.setdatabase = 0::oid;
postgres=#
postgres=# \d+ pg_shadow
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default | Storage | Description
--------------+--------------------------+-----------+----------+---------+----------+-------------
usename | name | | | | plain |
usesysid | oid | | | | plain |
usecreatedb | boolean | | | | plain |
usesuper | boolean | | | | plain |
userepl | boolean | | | | plain |
usebypassrls | boolean | | | | plain |
passwd | text | C | | | extended |
valuntil | timestamp with time zone | | | | plain |
useconfig | text[] | C | | | extended |
View definition:
SELECT pg_authid.rolname AS usename,
pg_authid.oid AS usesysid,
pg_authid.rolcreatedb AS usecreatedb,
pg_authid.rolsuper AS usesuper,
pg_authid.rolreplication AS userepl,
pg_authid.rolbypassrls AS usebypassrls,
pg_authid.rolpassword AS passwd,
pg_authid.rolvaliduntil AS valuntil,
s.setconfig AS useconfig
FROM pg_authid
LEFT JOIN pg_db_role_setting s ON pg_authid.oid = s.setrole AND s.setdatabase = 0::oid
WHERE pg_authid.rolcanlogin;
postgres=#
pg_read_all_stats
下面我们以pg_read_all_stats为例，来看一下预定义角色的内部使用、实现逻辑，如下：
[postgres@localhost:~/test/bin]$ ./psql
psql (17devel)
Type "help" for help.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
pg_stat_statements | 1.11 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=#
postgres=# create role r1 superuser password '1' login;
CREATE ROLE
postgres=# create role r2 password '1' login;
CREATE ROLE
postgres=# create role r3 password '1';
CREATE ROLE
postgres=# \q
[postgres@localhost:~/test/bin]$ ./psql -U r3 -d postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "r3" is not permitted to log in
[postgres@localhost:~/test/bin]$
示例如下：
[postgres@localhost:~/test/bin]$ ./psql -U r1 -d postgres
psql (17devel)
Type "help" for help.
postgres=# select * from pg_stat_statements_reset();
pg_stat_statements_reset
------------------------------
2024-05-07 23:59:07.24913-07
(1 row)
postgres=# select userid, queryid, query from pg_stat_statements(true);
userid | queryid | query
--------+----------------------+------------------------------------------
16388 | -4138155974000909952 | select * from pg_stat_statements_reset()
(1 row)
postgres=# set role r2;
SET
postgres=> select userid, queryid, query from pg_stat_statements(true);
userid | queryid | query
--------+---------------------+--------------------------
16388 | | <insufficient privilege>
16388 | | <insufficient privilege>
16389 | 7964835675605767119 | set role r2
(3 rows)
postgres=> reset role ;
RESET
postgres=# grant pg_read_all_stats to r2;
GRANT ROLE
postgres=# set role r2;
SET
postgres=> select userid, queryid, query from pg_stat_statements(true);
userid | queryid | query
--------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------
16388 | 5989446405197177395 | grant pg_read_all_stats to r2
16389 | 4672340091951134412 | select userid, queryid, query from pg_stat_statements($1)
16389 | 5234989915205660419 | SELECT pg_catalog.lower(name) FROM pg_catalog.pg_settings WHERE context IN ($1, $2) AND pg_catalog.lower(name) LIKE pg_catalog.lower($3)+
| | LIMIT $4
16388 | -4138155974000909952 | select * from pg_stat_statements_reset()
16388 | 4672340091951134412 | select userid, queryid, query from pg_stat_statements($1)
16388 | 8467575322926743907 | reset role
16389 | 7964835675605767119 | set role r2
(7 rows)
postgres=>
下面来看一下缺少此权限的内部控制逻辑，如下：
也即：
// contrib/pg_stat_statements/pg_stat_statements.c
/* Common code for all versions of pg_stat_statements() */
static void
pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
bool showtext)
{
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
Oid userid = GetUserId();
bool is_allowed_role = false;
char *qbuffer = NULL;
Size qbuffer_size = 0;
Size extent = 0;
int gc_count = 0;
HASH_SEQ_STATUS hash_seq;
pgssEntry *entry;
/*
* Superusers or roles with the privileges of pg_read_all_stats members
* are allowed
* 允许具有 pg_read_all_stats 成员权限的超级用户或角色
*/
is_allowed_role = has_privs_of_role(userid, ROLE_PG_READ_ALL_STATS);
...
当然统计信息不止上面一处，如下：
新增预定义角色
我这里选择一个来源于社区的patch，目前该patch应该属于不成熟 不会合入的。但是作为今天学习的材料来说，还是非常好的。有兴趣的小伙伴们可以自行前去查看，如下：
接下来，我们只关注其内部的实现，如下：
[postgres@localhost:~/postgres → master]$ wget https://www.postgresql.org/message-id/attachment/154187/0001-Add-new-pg_manage_extensions-predefined-role.patch
--2024-04-30 02:56:24-- https://www.postgresql.org/message-id/attachment/154187/0001-Add-new-pg_manage_extensions-predefined-role.patch
Resolving www.postgresql.org (www.postgresql.org)... 2001:4800:3e1:1::230, 2a02:16a8:dc51::50, 2a02:c0:301:0:ffff::32, ...
Connecting to www.postgresql.org (www.postgresql.org)|2001:4800:3e1:1::230|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 3997 (3.9K) [text/x-diff]
Saving to: ‘0001-Add-new-pg_manage_extensions-predefined-role.patch’
0001-Add-new-pg_manage_extensions- 100%[================================================================>] 3.90K --.-KB/s in 0s
2024-04-30 02:56:30 (178 MB/s) - ‘0001-Add-new-pg_manage_extensions-predefined-role.patch’ saved [3997/3997]
[postgres@localhost:~/postgres → master]$
[postgres@localhost:~/postgres → master]$
[postgres@localhost:~/postgres → master]$ ls
0001-Add-new-pg_manage_extensions-predefined-role.patch config.log configure.ac doc HISTORY meson_options.txt
aclocal.m4 config.status contrib GNUmakefile Makefile README.md
config configure COPYRIGHT GNUmakefile.in meson.build src
[postgres@localhost:~/postgres → master]$
[postgres@localhost:~/postgres → master]$
[postgres@localhost:~/postgres → master]$ cat 0001-Add-new-pg_manage_extensions-predefined-role.patch
From 59497e825184f0de30a18573ffd7d331be3b233d Mon Sep 17 00:00:00 2001
From: Michael Banck <michael.banck@credativ.de>
Date: Fri, 12 Jan 2024 13:56:59 +0100
Subject: [PATCH] Add new pg_manage_extensions predefined role.
This allows any role that is granted this new predefined role to CREATE, UPDATE
or DROP extensions, no matter whether they are trusted or not.
---
doc/src/sgml/user-manag.sgml | 5 +++++
src/backend/commands/extension.c | 11 ++++++-----
src/include/catalog/pg_authid.dat | 5 +++++
3 files changed, 16 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 92a299d2d3..ebb82801ec 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -693,6 +693,11 @@ DROP ROLE doomed_role;
database to issue
<link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
</row>
+ <row>
+ <entry>pg_manage_extensions</entry>
+ <entry>Allow creating, removing or updating extensions, even if the
+ extensions are untrusted or the user is not the database owner.</entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 226f85d0e3..71481d9a73 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -882,13 +882,14 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
ListCell *lc2;
/*
- * Enforce superuser-ness if appropriate. We postpone these checks until
- * here so that the control flags are correctly associated with the right
+ * Enforce superuser-ness/membership of the pg_manage_extensions
+ * predefined role if appropriate. We postpone these checks until here
+ * so that the control flags are correctly associated with the right
* script(s) if they happen to be set in secondary control files.
*/
if (control->superuser && !superuser())
{
- if (extension_is_trusted(control))
+ if (extension_is_trusted(control) || has_privs_of_role(GetUserId(), ROLE_PG_MANAGE_EXTENSIONS))
switch_to_superuser = true;
else if (from_version == NULL)
ereport(ERROR,
@@ -897,7 +898,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
control->name),
control->trusted
? errhint("Must have CREATE privilege on current database to create this extension.")
- : errhint("Must be superuser to create this extension.")));
+ : errhint("Only roles with privileges of the \"%s\" role are allowed to create this extension.", "pg_manage_extensions")));
else
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
@@ -905,7 +906,7 @@ execute_extension_script(Oid extensionOid, ExtensionControlFile *control,
control->name),
control->trusted
? errhint("Must have CREATE privilege on current database to update this extension.")
- : errhint("Must be superuser to update this extension.")));
+ : errhint("Only roles with privileges of the \"%s\" role are allowed to update this extension.", "pg_manage_extensions")));
}
filename = get_extension_script_filename(control, from_version, version);
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 82a2ec2862..ac70603d26 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -94,5 +94,10 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '8801', oid_symbol => 'ROLE_PG_MANAGE_EXTENSIONS',
+ rolname => 'pg_manage_extensions', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
]
--
2.39.2
[postgres@localhost:~/postgres → master]$ git apply 0001-Add-new-pg_manage_extensions-predefined-role.patch
[postgres@localhost:~/postgres → master]$
使用superuser才可以安装的插件，如下：
其使用，如下：
[postgres@localhost:~/test/bin]$ ./psql
psql (17devel)
Type "help" for help.
postgres=# create role r1 superuser password '1' login;
CREATE ROLE
postgres=# create role r2 password '1' login;
CREATE ROLE
postgres=# \q
[postgres@localhost:~/test/bin]$
[postgres@localhost:~/test/bin]$ ./psql -U r1 -d postgres
psql (17devel)
Type "help" for help.
postgres=# create extension plperl;
CREATE EXTENSION
postgres=# drop extension plperl ;
DROP EXTENSION
postgres=# \q
[postgres@localhost:~/test/bin]$ ./psql -U r2 -d postgres
psql (17devel)
Type "help" for help.
postgres=> create extension plperl;
2024-05-08 01:37:30.248 PDT [125519] ERROR: permission denied to create extension "plperl"
2024-05-08 01:37:30.248 PDT [125519] HINT: Must have CREATE privilege on current database to create this extension.
2024-05-08 01:37:30.248 PDT [125519] STATEMENT: create extension plperl;
ERROR: permission denied to create extension "plperl"
HINT: Must have CREATE privilege on current database to create this extension.
postgres=> \q
[postgres@localhost:~/test/bin]$ ./psql
psql (17devel)
Type "help" for help.
postgres=# grant pg_manage_extensions to r2;
GRANT ROLE
postgres=# set role r2;
SET
postgres=> create extension plperl;
CREATE EXTENSION
postgres=>
上面校验权限的逻辑，如下：
最后来思考一个问题：为什么上面control->trusted = true，extension_is_trusted_ret仍然为假？
// src/backend/commands/extension.c
/*
* Policy function: is the given extension trusted for installation by a
* non-superuser?
*
* (Update the errhint logic below if you change this.)
*/
static bool
extension_is_trusted(ExtensionControlFile *control)
{
AclResult aclresult;
/* Never trust unless extension's control file says it's okay */
if (!control->trusted)
return false;
/* Allow if user has CREATE privilege on current database */
aclresult = object_aclcheck(DatabaseRelationId, MyDatabaseId, GetUserId(), ACL_CREATE);
if (aclresult == ACLCHECK_OK)
return true;
return false;
}
原因如下：
[postgres@localhost:~/test/bin]$ ./psql
psql (17devel)
Type "help" for help.
postgres=# create role r4 password '1' login;
CREATE ROLE
postgres=# set role r4;
SET
postgres=> create extension plperl;
2024-05-08 02:04:43.067 PDT [129938] ERROR: permission denied to create extension "plperl"
2024-05-08 02:04:43.067 PDT [129938] HINT: Must have CREATE privilege on current database to create this extension.
2024-05-08 02:04:43.067 PDT [129938] STATEMENT: create extension plperl;
ERROR: permission denied to create extension "plperl"
HINT: Must have CREATE privilege on current database to create this extension.
postgres=>
postgres=> reset role;
RESET
postgres=# select * from pg_database ;
oid | datname | datdba | encoding | datlocprovider | datistemplate | datallowconn | dathasloginevt | datconnlimit | datfrozenxid | datminmxid | dattablespace | datcollate | datctype | datlocale | daticurules | datcollversion | datacl
-----+-----------+--------+----------+----------------+---------------+--------------+----------------+--------------+--------------+------------+---------------+-------------+-------------+-----------+-------------+----------------+-------------------------------------
5 | postgres | 10 | 6 | c | f | t | f | -1 | 730 | 1 | 1663 | zh_CN.UTF-8 | zh_CN.UTF-8 | | | 2.28 |
1 | template1 | 10 | 6 | c | t | t | f | -1 | 730 | 1 | 1663 | zh_CN.UTF-8 | zh_CN.UTF-8 | | | 2.28 | {=c/postgres,postgres=CTc/postgres}
4 | template0 | 10 | 6 | c | t | f | f | -1 | 730 | 1 | 1663 | zh_CN.UTF-8 | zh_CN.UTF-8 | | | | {=c/postgres,postgres=CTc/postgres}
(3 rows)
postgres=#
于是：
postgres=# grant CREATE on database postgres to r4;
GRANT
postgres=# set role r4;
SET
postgres=> create extension plperl;
CREATE EXTENSION
postgres=> select * from pg_database ;
oid | datname | datdba | encoding | datlocprovider | datistemplate | datallowconn | dathasloginevt | datconnlimit | datfrozenxid | datminmxid | dattablespace | datcollate | datctype | datlocale | daticurules | datcollversion | datacl
-----+-----------+--------+----------+----------------+---------------+--------------+----------------+--------------+--------------+------------+---------------+-------------+-------------+-----------+-------------+----------------+----------------------------------------------------
5 | postgres | 10 | 6 | c | f | t | f | -1 | 730 | 1 | 1663 | zh_CN.UTF-8 | zh_CN.UTF-8 | | | 2.28 | {=Tc/postgres,postgres=CTc/postgres,r4=C/postgres}
1 | template1 | 10 | 6 | c | t | t | f | -1 | 730 | 1 | 1663 | zh_CN.UTF-8 | zh_CN.UTF-8 | | | 2.28 | {=c/postgres,postgres=CTc/postgres}
4 | template0 | 10 | 6 | c | t | f | f | -1 | 730 | 1 | 1663 | zh_CN.UTF-8 | zh_CN.UTF-8 | | | | {=c/postgres,postgres=CTc/postgres}
(3 rows)
postgres=>