PostgreSQL / 认证与授权
在 PostgreSQL 中使用 grant 和 revoke 管理权限
简介
控制每个用户在数据库中被允许做什么，是管理数据库集群的重要组成部分。PostgreSQL 提供了一套工具来控制认证和授权。
在认证方面，pg_hba.conf 文件控制人们如何连接到数据库。这包括允许访问服务器的确切用户、数据库、连接方法和认证方法组合。授权组件始于 PostgreSQL 的角色和角色属性概念，它定义了系统中的用户实体并控制其全局权限。
除了角色属性提供的授权之外，还有更深层次的授权。管理特定数据库对象的拥有权和授权是控制哪些角色可以管理、修改和查看数据库、表、序列等的主要方式。本指南将介绍如何使用 PostgreSQL 的授权和撤销机制来精确地规定哪些角色可以访问每个数据库对象。
什么是 PostgreSQL 对象权限？
在关于角色和角色属性的文章中，我们介绍了为角色定义系统级权限的概念。通过使用角色属性，管理员可以定义角色是否可以创建或修改数据库、管理角色，甚至登录系统本身。这些类型的权限在整个数据库集群中都有效，因此它们在控制对数据库中单个对象的访问时没有粒度。
相反，PostgreSQL 使用一套互补的系统，即对特定数据库实体进行单独授权或赋予权限。这允许数据库对象的所有者确定哪些角色可以执行哪些类型的操作。这种额外的灵活性和粒度使得多用户和多租户部署既可能又实用。
数据库授权或权限定义了各种已认证角色可用的特定操作集或访问级别。PostgreSQL 的授权系统遵循“允许列表”模型，这意味着角色除了被明确授予的权限外，对数据库对象没有任何访问权限。
对象所有权和角色成员资格如何影响对象权限？
该系统的基础是对象所有权和角色成员资格的概念。在 PostgreSQL 中，每个数据库对象都只有一个所有者，只有他（以及superuser角色）才拥有更改、删除和管理对象本身的独特能力。对象所有者通过授予权限来管理对象对其他角色的权限。数据库对象上授予的每个权限最终都可以由对象所有者控制。
角色成员资格是一种系统，它赋予角色其所属于的角色的权限。具有INHERIT属性的角色，如果它们是其他角色的成员，将自动获得其权限，而无需使用SET ROLE更改当前角色。
例如，假设一个 salesadmin 角色具有修改 sales 数据库中数据的能力。如果 sally 角色设置了 INHERIT 角色属性，那么将 sally 添加到 salesadmin 角色将自动使 sally 能够修改 sales 数据库中的数据。继承特性的能力通过按“功能”角色分组属性和访问，然后根据需要将实际“用户”角色添加到这些功能角色中，从而实现灵活的访问管理风格。
系统上的每个角色默认都是 PUBLIC 角色的成员。这使得它在定义权限时与“所有人”同义。
可用对象权限概述
PostgreSQL 有许多权限可以授予角色以启用特定功能。
权限仅适用于有意义的数据库对象子集。例如，对数据库执行“执行”操作是没有意义的。有关哪些权限对哪些数据库对象有效，请参阅 PostgreSQL 文档中的ACL 权限缩写表和访问权限摘要表。
以下是每个权限名称及其功能的列表。您可以在 PostgreSQL 文档中找到每个权限的完整解释。
SELECT：SELECT权限赋予角色从数据库对象中选择或读取的能力。此权限对于任何引用现有列值的UPDATE和DELETE操作也是必需的。INSERT：提供向表、视图或列添加新行数据的能力。UPDATE：赋予更新数据库对象中列存储值的能力。大多数UPDATE操作也需要SELECT权限。DELETE：允许角色从表或视图中删除行。与UPDATE类似，大多数DELETE操作也需要SELECT权限才能定位到正确的行。TRUNCATE：TRUNCATE权限允许角色清空表或视图中的所有数据。REFERENCES：为角色提供创建引用表或表列的外键的能力。TRIGGER：允许角色在表或视图上定义触发器。CREATE：允许角色创建数据库、模式或表空间的子实体。例如，在数据库上，CREATE权限允许角色创建新的模式，而在模式上，它允许角色创建新的数据库。CONNECT：允许角色连接到数据库。这在连接时检查。TEMPORARY：允许角色在数据库中创建临时表。EXECUTE：赋予角色调用函数或存储过程的权限。USAGE：允许角色对对象执行基本功能。例如，模式上的USAGE允许角色查找其中的对象，而序列上的USAGE允许角色调用currval和nextval函数。ALL PRIVILEGES：一个简写，赋予指定角色对指定对象的所有权限。
使用 GRANT 命令
GRANT 命令用于角色管理中的两个独立但相关的目的
- 向角色授予对数据库对象的特定权限
- 将角色添加为其他角色的成员
这两个功能体现在命令语法的两种结构中。
要将特定数据库对象的权限授予给定角色，请使用以下形式
可选的 WITH GRANT OPTION 子句还赋予接收角色将此能力传递给其他角色的能力。例如，如果 adam 获得了使用 WITH GRANT OPTION 从 customers 中 DELETE 数据的能力，那么他可以选择将该能力授予 delores。实际上，这使您能够让角色管理特定对象上的某些能力。
另一种用于将一个角色添加到另一个角色的语法如下所示。在这种情况下，<role_member> 获得了 <provider_role> 的权限
如果 <role_member> 设置了 INHERIT 属性，它将立即拥有 <provider_role> 的权限。如果缺少此属性，<role_member> 可以通过使用 SET ROLE 更改当前角色来访问 <provider_role> 的权限。
与另一种语法中的 WITH GRANT OPTION 子句类似，在授予角色成员资格时，您可以选择添加 WITH ADMIN OPTION 子句。此子句还赋予角色成员添加新成员的能力。
示例
授予 sam 对 books 数据库的 CRUD 权限
允许 sam 委托对 books 数据库的 CRUD 行为
让 jasmine 对 customers 表拥有完全权限
允许 keisha 执行 order_count 函数
将 calin 添加到 admin 角色
将 sofie 添加到 salesperson 角色，并允许她管理成员资格
使用 REVOKE 命令
REVOKE 命令从数据库对象上的角色中撤销权限。在大多数情况下，它与 GRANT 命令的语法相似，有两种格式用于涵盖两种用例。
要从给定角色撤销数据库对象上的特定权限，请使用以下格式
在这种情况下，可以添加可选的 GRANT OPTION FOR 子句，以移除指定角色传递给定权限的能力。当尝试从已将权限传递给另一个角色的角色撤销 GRANT OPTION 时，可能会发生冲突。在这种情况下，从第一个角色移除 GRANT OPTION 将打破赋予辅助角色其权限的授权链。
例如，如果 ada 使用 WITH GRANT OPTION 授予 pete 在 records 数据库上更新内容的能力，那么 pete 就可以再将更新内容的能力授予 simone。如果 ada 从 pete 撤销 GRANT OPTION，那么传递给 simone 的更新权限会发生什么就不清楚了。
可选的 CASCADE 或 RESTRICT 子句通过明确指定 REVOKE 在此场景中应如何操作来解决此冲突。默认行为是 RESTRICT，如果权限已传递给其他角色，它将导致 REVOKE GRANT OPTION FOR 命令失败。CASCADE 选项将此行为更改为除了指定角色之外，还从任何“下游”角色撤销权限，通过移除断裂的链来解决冲突。
另一种用于撤销角色成员资格的语法如下所示
在这种情况下，除非通过其他途径获得授权，否则 <member_role> 将不再拥有授予 <provider_role> 的权限。
示例
撤销 eddy 从 logs 表中删除行的能力
移除 jerry 对 finances 的所有访问权限
撤销 alice 向其他角色授予对 snacks 表的 DELETE 权限的能力。请记住，执行此命令后，alice 仍将拥有 DELETE 权限，但将不再能够传递该权限
将 natasha 从 moderators 角色中移除
撤销 tony 管理 hr 角色成员资格的能力。请记住，tony 仍将是 hr 的成员
结论
PostgreSQL 的授权和权限系统允许您为特定数据库对象上的单个角色定义精细的权限。授权系统将 PostgreSQL 的授权控制扩展到可以由其所有者管理的单个对象。
这种安排允许单个用户控制他们自己的数据库对象。他们可以授予和撤销访问权限，并向其他角色委托某些管理功能。此外，相同的安排也用于实现角色成员资格，以简化权限管理。
