PostgreSQL / 身份验证和授权
使用 grant 和 revoke 在 PostgreSQL 中管理权限
简介
控制每个用户在数据库集群中被允许执行的操作是管理数据库集群的重要组成部分。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：允许更新数据库对象中列存储的值。SELECT权限对于大多数UPDATE操作也是必需的。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 被授予 DELETE customers 中数据的能力，并带有 WITH GRANT OPTION，那么他可以反过来选择性地将该能力授予 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 将更新 records 数据库上内容的能力授予 pete，则 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 权限，但将不再能够传递该权限
从 moderators 角色中删除 natasha
从 tony 撤销管理 hr 角色的成员资格的能力。请记住，tony 仍然是 hr 的成员
结论
PostgreSQL 的授权和权限系统允许您为特定数据库对象上的各个角色定义细粒度的权限。授权系统将 PostgreSQL 的授权控制扩展到可以由其所有者管理的各个对象。
这种安排允许各个用户对其自己的数据库对象行使控制权。他们可以授予和撤销访问权限，以及将某些管理职能委派给其他角色。此外，相同的安排用于实施角色成员资格，以简化权限管理。
