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