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：赋予更新数据库对象中列存储值的能力。大多数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 的授权控制扩展到可以由其所有者管理的单个对象。
这种安排允许单个用户控制自己的数据库对象。他们可以授予和撤销访问权限，以及将某些管理功能委托给其他角色。此外，相同的安排用于实现角色成员资格，以简化权限管理。
