PostgreSQL / 身份验证和授权
在 PostgreSQL 中使用 grant 和 revoke 管理权限
简介
控制每个用户在数据库中可以执行的操作是管理数据库集群的重要组成部分。PostgreSQL 提供了一套工具来控制身份验证和授权。
在身份验证方面，pg_hba.conf 文件控制用户如何连接到数据库。这包括允许访问服务器的确切用户、数据库、连接方法和身份验证方法组合。授权组件从 PostgreSQL 的角色和角色属性概念开始，该概念定义了系统内的用户实体并控制其全局权限。
除了角色属性提供的授权之外，还存在更深层次的授权。管理特定数据库对象的所有权和授权是控制哪些角色可以管理、修改和查看数据库、表、序列等的首要方法。本指南将介绍如何使用 PostgreSQL 的 grant 和 revoke 机制来精确地说明哪些角色可以访问每个数据库对象。
什么是 PostgreSQL 对象权限？
在关于角色和角色属性的文章中，介绍了为角色定义系统级权限的概念。使用角色属性，管理员可以定义角色是否可以创建或修改数据库、管理角色，甚至登录系统本身。这些类型的权限在整个数据库集群中都适用，因此在控制对数据库中单个对象的访问时，它们不提供粒度。
相反，PostgreSQL 使用一个互补系统，即对特定数据库实体的单独授权或权限。这允许数据库对象的所有者确定哪些类型的操作被哪些角色允许。这种额外的灵活性和粒度使得多用户和多租户部署成为可能和实用。
数据库授权或权限定义了可用于各种经过身份验证的角色的特定操作集或访问级别。PostgreSQL 的授权系统遵循“允许列表”模型，这意味着角色除了明确授予的权限外，无权访问数据库对象。
对象所有权和角色成员关系如何影响对象权限？
这个系统的基础是对象所有权和角色成员关系的概念。在 PostgreSQL 中，每个数据库对象都只有一个所有者，只有所有者以及superuser 角色才具有修改、删除和管理对象本身的独特能力。对象所有者通过授予权限来管理对象上其他角色的权限。在数据库对象上授予的每个权限最终都可以由对象所有者控制。
角色成员关系是一个系统，它赋予角色其所属角色的权限。具有 INHERIT 属性且属于其他角色的角色可以自动获得其权限，而无需使用 SET ROLE 来更改当前角色。
例如，假设一个 salesadmin 角色具有修改 sales 数据库中数据的能力。如果 sally 角色设置了 INHERIT 角色属性，那么将 sally 添加到 salesadmin 角色将自动使 sally 能够修改 sales 数据库中的数据。继承特性的能力通过按“功能”角色分组属性和访问，然后根据需要将实际“用户”角色添加到这些功能角色，从而实现灵活的访问管理风格。
系统上的每个角色默认都是 PUBLIC 角色的成员。这使得它在定义权限时与“每个人”同义。
可用对象权限概述
PostgreSQL 有许多权限可以授予角色以启用特定功能。
权限仅适用于数据库对象的一个子集，在这些子集中它们才有意义。例如，“执行”数据库是没有意义的。有关哪些权限对哪些数据库对象有效，请参阅 ACL 权限缩写表 和 访问权限摘要表，这些表来自 PostgreSQL 文档。
以下是每个权限名称及其功能的列表。您可以在 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 向 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 权限，但将不再能够传递该权限
从 moderators 角色中删除 natasha
从 tony 撤销管理 hr 角色的成员资格的能力。请记住，tony 仍将是 hr 的成员
结论
PostgreSQL 的 grant 和 privilege 系统允许您定义对特定数据库对象上各个角色的精细权限。grant 系统将 PostgreSQL 的授权控制扩展到可以由其所有者管理的单个对象。
这种安排允许个人用户对其自己的数据库对象行使控制权。他们可以授予和撤销访问权限，以及将某些管理功能委派给其他角色。此外，相同的安排用于实现角色成员资格，以简化权限管理。
