本系列关于 PostgreSQL 特权的前两篇文章中，我们回顾了如何创建角色、授予他们数据库对象的特权，以及对象所有权如何成为管理数据库访问和控制的一个重要方面。
在管理哪些角色可以访问或修改现有对象时，所有权是最终特权。因为 PostgreSQL 权限是根据最小权限原则工作的，对象（表、触发器、函数、过程等）的所有者需要授予其他角色权限。
我们讨论了如何在每次创建对象时使用命令手动完成GRANT操作，但是，管理起来很耗时并且容易遗漏细节。
相反，PostgreSQL 提供了一种设置默认权限的方法，这些权限是在创建数据库对象时，代表对象所有者授予的。使用默认权限，角色可以提前准备数据库，以确保应用一致的访问权限，同时随着时间的推移减轻管理负担。
但是，您如何着手创建一组角色和默认权限以提供正确级别的控制和访问权限呢？让我们再深入说明一下。
使用默认权限管理迁移
回想一下，默认权限是按角色（可以代表一个用户或一组用户）设置的。也就是说，每个角色都必须指定每当创建特定类型的对象时，它将授予其他角色哪些特权。考虑下面的数据库图像说明，其中包含许多对象，每个对象都由不同的角色拥有。
让我们看看如何开始在 PostgreSQL 数据库中建立这种安全设置。
创建高级组角色
在 PostgreSQL 中，角色可以被授予其他角色的成员资格并继承他们的特权，这是我们在第一篇文章中讨论过的。因此，挑战在于考虑您需要为数据库中的不同角色提供哪些特权，以及如何将它们抽象为更高级别的角色集。
请记住，角色是在实例级别创建的。因此，如果您创建一个 'read_only'角色，它可用于在每个数据库中管理只读访问 ('SELECT') 。可以使用从 DML （' INSERT'、' DELETE'、' UPDATE'）一直到 DDL （' CREATE'）的不同类型的权限来完成同样的操作。
然后，示例“模板”可以从在每个数据库实例中创建这三个组角色开始。您的需求可能更复杂，请根据需要添加和调整。
•以下脚本是Stack Overflow 答案的修改版本，用作模板。我去年偶然发现了它，并认为它很好地概述了设置 PostgreSQL 数据库权限的简单过程。（它也提到了最好的数据库 DevOps 工具，这并没有什么坏处！）
PostgreSQL 实例组角色
以下两个脚本需要在每个数据库实例上运行一次以创建适当的角色，然后将被授予每个数据库中的权限。
/**这个脚本应该在每个PostgreSQL实例中运行一次*创建组角色将可以在所有数据库中用于ddl/dml/read_only访问*在你环境中，可能需要创建更多的小组角色来提供更好的特权粒度*/CREATE ROLE ddl_grp WITH NOLOGIN;CREATE ROLE dml_grp WITH NOLOGIN;CREATE ROLE read_only_grp WITH NOLOGIN;
创建这些角色还没有做任何实质性的事情。相反，当我们创建数据库和模式对象时，这些角色成为一种管理其他特权的机制。即使您无法使用这些角色直接连接到数据库('WITH NOLOGIN'），这些角色仍然可以拥有对象并创建默认权限。
接下来，我们可以根据需要将这些组角色的成员资格授予其他角色。在下面的示例中，我们有三种类型的用户角色：
•对象管理员：允许这些角色在数据库中创建对象。创建对象是一个 DDL 操作，因此我们将角色命名为'ddl_grp'
•数据管理员：允许这些角色使用 DML 语句修改数据和关联对象，因此我们将角色命名为“dml_grp'
•只读：允许这些角色从特定模式或表中查询数据。我们将角色命名为 'read_only_grp'
示例用户角色（例如'dev_admin1', ' flyway', ' dev1', 'report_usr'等）需要在授予组角色成员资格之前存在。关键是任何被授予 'ddl_grp' 角色成员资格的角色都能够运行脚本创建或修改数据库' ddl_grp'中的对象。
/** 现在，根据每个角色工作职能的需要授予其访问权限。* 再说一次，这些只是关于如何开始的想法。* 可能需要更多的“组”角色。* 这些是可能被授予访问权限的示例角色。* 角色的一个很好的例子是由DevOps工具(如Flyway)用于运行迁移脚本的角色“ddl_grp”。* 这确保了所有对象都属于该组，并且所有用户都可以访问。*/GRANT ddl_grp TO dev_admin1, flyway;GRANT dml_grp TO dev1, dev2;GRANT read_only_grp TO report_usr1, report_usr2;
在所有角色就位并更新成员资格后，我们现在可以转向特权真正重要的单个数据库。
个人数据库权限
现在我们已经创建了数据库角色，我们可以专注于单独的数据库权限。在这种情况下，您有两种选择。
1.每次在集群中创建新数据库时，您都可以运行下面介绍的脚本。
2.使用超级用户角色，可以修改PostgreSQL 实例上的模板数据库（通常称为 'template1'）。模板数据库中应用的任何设置都会自动为从该模板创建的每个新数据库设置这些权限。
下面的脚本可以应用，但是，我将分开来讨论它每个部分的目的，以及它如何应用于数据库。
撤销 PUBLIC 的所有权限
对任何新数据库应该做的第一件事是撤销默认授予PUBLIC角色的所有特权。我们在第一篇文章中讨论了为什么。
/**在每个数据库上，以超级用户或具有CREATEROLE和其他GRANT权限的用户运行此脚本。*在像Amazon RDS这样的DBaaS服务中，这可能是分配给您进行管理的用户*/--适用于所有现代版本的PostgreSQL。--这将阻止到数据库的连接，直到明确授予CONNECTREVOKE ALL ON DATABASE mydatabase FROM PUBLIC;--在授予权限之前，禁止任何人在公共模式中创建对象。--在PG15+中默认REVOKE CREATE ON SCHEMA public FROM PUBLIC;
注意这里的一个细微差别。我们正在删除数据库级别ALL'的特权，而不是数据库的模式级别。如果我们也从'PUBLIC'模式中删除所有权限，那么在不重置额外权限的情况下，普通的 PostgreSQL 命令将对许多用户不起作用。
在数据库级别撤销'ALL'，可防止用户在没有在其他地方应用特定授权的情况下连接到数据库，如下所示。
授予基本权限
所有角色都需要能够连接到数据库和访问临时表。但是，如果没有USAGE和SELECT 等额外权限，这些角色实际上还无法在数据库中执行任何操作。
通常每个角色都会从该PUBLIC角色继承这些特权，但我们只是撤销了上面的这些特权，以便我们更好地控制我们想要访问每个数据库和公共模式的角色。
--授予所有角色的连接和访问权限;GRANT CONNECT, TEMPORARYON DATABASE mysuperdb TO ddl_grp, dml_grp, read_only_grp;
授予每个组角色使用权和其他特权
现在我们来看看好东西。对于每个数据库，我们可以决定哪些角色可以使用数据库对象('USAGE'）以及他们可以为每个模式做什么。如下所述，此示例仅显示如何授予对公共模式的特权。如果您的应用程序有其他模式（它可能有！），您将需要为每个模式授予适当的权限。
首先，我们'ddl_grp'（和所有成员）被允许在模式和任何序列中使用和创建对象。如果存在TABLE、SEQUENCE、TYPE、DOMAIN等现有对象，您确实需要每次都运行一条GRANT语句来适当地设置它们现有的权限，因为默认权限只影响新创建的对象。在下面的示例中，我展示了向现有序列和表授予权限。
/**下面，我们授予一个名为“myapp”的应用程序模式的使用权限。*如果您的应用程序有不同的模式，您需要适当地更新它。*多个模式名可以用逗号分隔*/--这将允许组中的任何人使用和创建模式中的新对象。--因为这个组会拥有对象，他们可以稍后修改它们。GRANT USAGE, CREATEON SCHEMA myapp TO ddl_grp;-- 修改现有表和序列的权限GRANT ALLON ALL TABLES IN SCHEMA myapp TO ddl_grp;GRANT ALLON ALL SEQUENCES IN SCHEMA myapp TO ddl_grp;
接下来，我们继续讨论将被允许在模式中使用对象的组角色，然后选择数据和序列值。和以前一样，如果您的数据库有多个模式，则需要为每个模式中的每个对象类型授予角色使用权。
--这将允许所有其它“group”角色连接到myapp模式并访问对象，如果他们有的话--被授予了这样做的特权。GRANT USAGE ON SCHEMA myapp TO dml_grp, read_only_grp;--修改现有表和序列的权限GRANT SELECTON ALL TABLES IN SCHEMA myapp TO dml_grp, read_only_grp;GRANT USAGE, SELECTON ALL SEQUENCES IN SCHEMA myapp TO dml_grp, read_only_grp;
最后，作为将用于创建模式对象的用户，为数据库中的其他角色设置默认权限。请注意以下示例中的两件事。
•我们只为表和序列设置默认权限。您应该考虑您的用户在创建对象时需要的其他权限，并将它们添加到您的脚本中。
•'ddl_grp'角色没有分配任何默认特权，也没有分配给任何属于它的成员的角色。这是因为组将是对象的所有者，任何其他成员角色将自动继承所有特权（不需要更多了）。
默认权限通常应用于执行语句的角色。因此，您的脚本必须暂时将角色设置为将来创建对象的所有者。这要求执行此脚本的角色是其他角色的成员。
或者，从长远来看更清楚一点，您可以将另一个角色的默认权限设置为ALTER DEFAULT PRIVILEGES语句的一部分，只要当前角色是该角色的成员即可。
在下面的两个示例中，执行 SQL 的角色必须是其他组角色的成员。第二种形式有点冗长，更容易在语句级别跟踪哪些特权被应用到谁身上。
选项 1：在分配权限之前设置角色
/** Finally, as the user that will run migration scripts (and is* a member of the DDL group, setup the default access privileges.*/SET ROLE ddl_grp;ALTER DEFAULT PRIVILEGES IN SCHEMA myappGRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dml_grp;ALTER DEFAULT PRIVILEGES IN SCHEMA myappGRANT SELECT ON TABLES TO read_only_grp;ALTER DEFAULT PRIVILEGES IN SCHEMA myappGRANT USAGE, SELECT ON SEQUENCES TO dml_grp, read_only_grp;ALTER DEFAULT PRIVILEGES IN SCHEMA myappGRANT UPDATE ON SEQUENCES TO dml_grp;
选项 2：为另一个角色分配权限
/**最后，作为在组角色中拥有成员资格的用户将运行迁移脚本设置默认访问权限。*/ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA myappGRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dml_grp;ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA myappGRANT SELECT ON TABLES TO read_only_grp;ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA myappGRANT USAGE, SELECT ON SEQUENCES TO dml_grp, read_only_grp;ALTER DEFAULT PRIVILEGES FOR ROLE ddl_grp IN SCHEMA myappGRANT UPDATE ON SEQUENCES TO dml_grp;
验证权限设置是否正确
最后一步是验证默认权限是否已设置并准备好执行其工作。查看工作结果的最简单方法是使用“psql”中的“\ddp”命令。
这有助于我们验证'ddl_grp'角色是否为公共模式中的所有表和序列设置了默认权限。
显然，您的应用程序和角色设置可能比此示例模板更复杂。但是，无论您需要管理多少角色和对象类型，这些原则都适用。以此为起点。
在迁移时将其整合在一起
一切就绪后，让我们看看如何在生产数据库上使用它。
在迁移管道中，我们设置迁移脚本的连接字符串，以使用我们的管道角色，该角色在上文中显示为“flyway”。理想情况下，该角色的密码应存储在密码保险库中，并在管道脚本中检索，以确保没有任何普通用户能够以该角色的身份认证。
然后，在任何迁移脚本的顶部，我们首先将角色设置为'ddl_grp'，以便任何 DDL创建都会导致对象归该组所有，并且默认权限将启动并正确应用！下面是一个简单的例子。
/** 首先将会话角色设置为高级所有权组角色*/SET ROLE ddl_grp;-- 创建对象CREATE TABLE test (col1 text null,col2 int null);
应用迁移后，您可以使用'\psql'中的'\dp'命令快速检查权限是否已正确应用。
成功！从现在开始，只要'ddl_grp'用于创建表和序列（或您为其设置默认权限的任何其他对象），其他角色将拥有您定义的访问权限，并且整体管理负担减轻了！
结论
考虑和管理 PostgreSQL 中的权限可能很复杂。了解如何向角色授予特权以及角色之间的成员资格可以快速减轻这种负担。然而，学习如何以有效的方式设置默认权限是管理数据库很重要的起点。
本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
