货代可以从哪些网站开发客户,大数据营销方式有哪些,中国基建人才培训网证书查询,重庆seo招聘目录结构 注#xff1a;提前言明 本文借鉴了以下博主、书籍或网站的内容#xff0c;其列表如下#xff1a; 1、参考书籍#xff1a;《PostgreSQL数据库内核分析》 2、参考书籍#xff1a;《数据库事务处理的艺术#xff1a;事务管理与并发控制》 3、PostgreSQL数据库仓库… 目录结构 注提前言明 本文借鉴了以下博主、书籍或网站的内容其列表如下 1、参考书籍《PostgreSQL数据库内核分析》 2、参考书籍《数据库事务处理的艺术事务管理与并发控制》 3、PostgreSQL数据库仓库链接点击前往 4、日本著名PostgreSQL数据库专家 铃木启修 网站主页点击前往 5、参考书籍《PostgreSQL中文手册》 6、参考书籍《PostgreSQL指南内幕探索》点击前往 7、参考书籍《事务处理 概念与技术》 1、本文内容全部来源于开源社区 GitHub和以上博主的贡献本文也免费开源可能会存在问题评论区等待大佬们的指正 2、本文目的开源共享 抛砖引玉 一起学习 3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关 4、大家可以根据需要自行 复制粘贴以及作为其他个人用途但是不允许转载 不允许商用 写作不易还请见谅 5、本文内容基于PostgreSQL master源码开发而成 PostgreSQL数据库数据库角色的使用及预定义角色的原理 文章快速说明索引功能使用背景说明数据库角色角色属性角色成员关系删除角色预定义角色函数和触发器安全性 预定义角色深入相关系统表和视图pg_read_all_stats新增预定义角色 文章快速说明索引
学习目标
做数据库内核开发久了就会有一种 少年得志年少轻狂 的错觉然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在每每想到于此皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发近段时间 将着重于学习分享Postgres的基础知识和实践内幕。 学习内容详见目录
1、PostgreSQL数据库数据库角色的使用及预定义角色的原理 学习时间
2024年04月30日 21:32:22 学习产出
1、PostgreSQL数据库基础知识回顾 1个 2、CSDN 技术博客 1篇 3、PostgreSQL数据库内核深入学习 注下面我们所有的学习环境是Centos8PostgreSQL master Oracle19CMySQL8.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功能使用背景说明
数据库角色
首先看一下官方文档的说明如下 PostgreSQL使用角色的概念管理数据库访问权限。一个角色可以被看成是一个数据库用户或者是一个数据库用户组这取决于角色被怎样设置。角色可以拥有数据库对象例如表和函数并且能够把那些对象上的权限赋予给其他角色来控制谁能访问哪些对象。此外还可以把一个角色中的成员资格授予给另一个角色这样允许成员角色使用被赋予给另一个角色的权限 角色的概念把“用户”和“组”的概念都包括在内。在PostgreSQL版本 8.1 之前用户和组是完全不同的两种实体但是现在只有角色。任意角色都可以扮演用户、组或者两者 数据库角色在概念上已经完全与操作系统用户独立开来。事实上可能维护一个对应关系会比较方便但是这并非必需。数据库角色在一个数据库集簇安装范围内是全局的而不是独立数据库内。要创建一个角色可使用CREATE ROLE SQL 命令
CREATE ROLE name;name遵循 SQL 标识符的规则或是未经装饰没有特殊字符或是用双引号包围实际上你将总是给该命令要加上额外选项例如LOGIN。更多细节可见下文。要移除一个已有的角色使用相似的DROP ROLE命令
DROP ROLE name;为了方便createuser和dropuser程序被提供作为这些 SQL 命令的包装器它们可以从 shell 命令行调用
createuser name
dropuser name要决定现有角色的集合检查pg_roles系统目录例如
[postgreslocalhost:~/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 connectionsdone
server started
[postgreslocalhost:~/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 | | 10pg_database_owner | f | t | f | f | f | f | -1 | ******** | | f | | 6171pg_read_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6181pg_write_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6182pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200pg_checkpoint | f | t | f | f | f | f | -1 | ******** | | f | | 4544pg_maintain | f | t | f | f | f | f | -1 | ******** | | f | | 9256pg_use_reserved_connections | f | t | f | f | f | f | -1 | ******** | | f | | 4550pg_create_subscription | f | t | f | f | f | f | -1 | ******** | | f | | 6304
(16 rows)postgres#psql程序的\du元命令也可以用来列出现有角色如下
[postgreslocalhost:~/test/bin]$ ./psql
psql (17devel)
Type help for help.postgres# \duList of rolesRole name | Attributes
-----------------------------------------------------------------------postgres | Superuser, Create role, Create DB, Replication, Bypass RLSpostgres#为了引导数据库系统一个刚刚被初始化好的系统总是包含一个预定义角色。这个角色总是一个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_read_all_data读所有数据(表视图序列)如同在那些对象上有 SELECT 权限在所有模式上有USAGE权限即使没有显式拥有它。这个角色没有角色属性 BYPASSRLS 集。如果使用了RLS管理员可能希望设置角色上的被GRANTED给该角色的 BYPASSRLSpg_write_all_data写全部数据(表视图序列)如果在那些对象上有 INSERT、UPDATE和DELETE权限以及在全部模式上有USAGE权限即使没有显式拥有它。这个角色没有角色属性 BYPASSRLS 集。如果使用了RLS管理员可能希望设置角色上的被GRANTED给该角色的 BYPASSRLSpg_read_all_settings读取所有配置变量甚至是那些通常只对超级用户可见的变量pg_read_all_stats读取所有的pg_stat_*视图并且使用与扩展相关的各种统计信息甚至是那些通常只对超级用户可见的信息pg_stat_scan_tables执行可能会在表上取得 ACCESS SHARE 锁的监控函数可能会持锁很长时间pg_monitor读取/执行各种不同的监控视图和函数。这角色是 pg_read_all_settingspg_read_all_stats和pg_stat_scan_tables的成员pg_database_owner无 成员构成隐式的当前数据库的所有者pg_signal_backend发信号到其他后端以取消查询或中止它的会话pg_read_server_files允许使用COPY以及其他文件访问函数从服务器上该数据库可访问的任意位置读取文件pg_write_server_files允许使用COPY以及其他文件访问函数在服务器上该数据库可访问的任意位置中写入文件pg_execute_server_program允许用运行该数据库的用户执行数据库服务器上的程序来配合COPY和其他允许执行服务器端程序的函数
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_authidTable pg_catalog.pg_authidColumn | 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_globalpg_authid_rolname_index UNIQUE CONSTRAINT, btree (rolname), tablespace pg_global
Tablespace: pg_global
Access method: heappostgres#
postgres# \d pg_db_role_settingTable pg_catalog.pg_db_role_settingColumn | 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: heappostgres#postgres# \d pg_userView pg_catalog.pg_userColumn | 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,useconfigFROM pg_shadow;postgres# \d pg_roles View pg_catalog.pg_rolesColumn | 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.oidFROM pg_authidLEFT JOIN pg_db_role_setting s ON pg_authid.oid s.setrole AND s.setdatabase 0::oid;postgres#
postgres# \d pg_shadowView pg_catalog.pg_shadowColumn | 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 useconfigFROM pg_authidLEFT JOIN pg_db_role_setting s ON pg_authid.oid s.setrole AND s.setdatabase 0::oidWHERE pg_authid.rolcanlogin;postgres#pg_read_all_stats
下面我们以pg_read_all_stats为例来看一下预定义角色的内部使用、实现逻辑如下
[postgreslocalhost:~/test/bin]$ ./psql
psql (17devel)
Type help for help.postgres# \dxList of installed extensionsName | Version | Schema | Description
-----------------------------------------------------------------------------------------------------------------pg_stat_statements | 1.11 | public | track planning and execution statistics of all SQL statements executedplpgsql | 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
[postgreslocalhost:~/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
[postgreslocalhost:~/test/bin]$示例如下
[postgreslocalhost:~/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 privilege16388 | | insufficient privilege16389 | 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 r216389 | 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 $416388 | -4138155974000909952 | select * from pg_stat_statements_reset()16388 | 4672340091951134412 | select userid, queryid, query from pg_stat_statements($1)16388 | 8467575322926743907 | reset role16389 | 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应该属于不成熟 不会合入的。但是作为今天学习的材料来说还是非常好的。有兴趣的小伙伴们可以自行前去查看如下
[PATCH] New predefined role pg_manage_extensions点击前往 其目的是向 Postgres 添加一个新的预定义角色 pg_manage_extensions。这个想法是它允许超级用户将创建、更新或删除扩展的权限委托给其他角色即使这些扩展不受信任或者这些用户不是数据库所有者。 支持者我同意扩展创建是人们需要超级用户访问权限的主要原因之一我认为尝试减少这种情况是有益的。但我不确定这样的 pg_manage_extensions 角色在实践中是否会比超级用户拥有更少的权限。据我所知许多未标记为可信的扩展是不可信的因为如果它们是可信的它们将允许相当微不足道的权限升级到超级用户。 反对者请注意仅仅能够创建扩展并不能授予使用它的全面权限。我对我认为可能有问题的东西例如 adminpack 或 plpython3u做了一些检查并且不允许 pg_manage_extensions 用户调用这些函数或使用不受信任的语言。虽然这可能是真的或者说我们应该谨慎行事但我认为更多的理由是它们披露的数据库服务器信息比我们希望向普通用户披露的信息要多或者说它们允许访问文件系统等。我认为如果我们在 contrib 中有扩展只需安装即可轻松地允许非超级用户成为超级用户那么这应该是一个错误并且可以通过使普通用户无法在不被授予对它们的某些访问权限的情况下使用这些扩展来修复 。毕竟由于用户需求而对 DBA 进行社会工程以安装扩展无论如何都是一件事即使大多数 DBA 可能会拒绝它并且至少 DBA 应该意识到特定扩展的特定风险 接下来我们只关注其内部的实现如下
[postgreslocalhost:~/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][postgreslocalhost:~/postgres → master]$
[postgreslocalhost:~/postgres → master]$
[postgreslocalhost:~/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
[postgreslocalhost:~/postgres → master]$
[postgreslocalhost:~/postgres → master]$
[postgreslocalhost:~/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.banckcredativ.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.sgmlb/doc/src/sgml/user-manag.sgml-693,6 693,11 DROP ROLE doomed_role;database to issuelink linkendsql-createsubscriptioncommandCREATE SUBSCRIPTION/command/link./entry/rowrowentrypg_manage_extensions/entryentryAllow creating, removing or updating extensions, even if theextensions 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.cb/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)));elseereport(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.datb/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[postgreslocalhost:~/postgres → master]$ git apply 0001-Add-new-pg_manage_extensions-predefined-role.patch
[postgreslocalhost:~/postgres → master]$使用superuser才可以安装的插件如下 其使用如下
[postgreslocalhost:~/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
[postgreslocalhost:~/test/bin]$ [postgreslocalhost:~/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
[postgreslocalhost:~/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
[postgreslocalhost:~/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 trueextension_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 extensions control file says its 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;
}原因如下
[postgreslocalhost:~/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,postgresCTc/postgres}4 | template0 | 10 | 6 | c | t | f | f | -1 | 730 | 1 | 1663 | zh_CN.UTF-8 | zh_CN.UTF-8 | | | | {c/postgres,postgresCTc/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,postgresCTc/postgres,r4C/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,postgresCTc/postgres}4 | template0 | 10 | 6 | c | t | f | f | -1 | 730 | 1 | 1663 | zh_CN.UTF-8 | zh_CN.UTF-8 | | | | {c/postgres,postgresCTc/postgres}
(3 rows)postgres
