客服微信
无论是在数据库、应用程序还是更大的 IT 系统中，信息安全都基于身份验证和授权原则，通常分别称为 AuthN 和 AuthZ。
同样，PostgreSQL 安全涉及创建允许访问服务器资源的角色 (AuthN)，然后适当地向数据库对象授予权限 (AuthZ)。了解这些组件如何协同工作对于有效管理 PostgreSQL 集群的安全性至关重要。
|
验证 |
授权 |
|
验证用户是否是他们声称的人。这可以通过密码、信任或其他一些联合登录（如 Kerberos） |
作为经过身份验证的用户，可以在系统内做什么？ |
在我们开始之前，让我们建立一些术语：
PostgreSQL中只有一种认证主体，角色，存在于集群级别。按照约定，允许登录的一个ROLE被认为是一个用户，而一个不允许登录的角色是一个组。请注意，虽然CREATE USER和CREATE GROUP命令仍然存在，但它们只是CREATE ROLE的别名。
任何可以在 PostgreSQL 集群中创建或访问的东西都被称为对象。数据库、模式、表、视图、过程、函数等都可以针对任何角色应用不同的权限。
可以授予数据库对象角色的访问类型。通常这些将在数据库或模式级别应用，但将特定访问应用到单个对象的能力始终可用。分配给一个角色的特权可以授予其他角色，通常是通过将组角色（无法登录的角色）授予用户角色（可以登录的角色）以协助权限管理来完成的。
根据您以前在信息安全方面的经验（在数据库、操作系统或应用程序中），这些组件在 PostgreSQL 中协同工作的方式可能与您预期的不同。
在整篇文章中，您将看到对 PostgreSQL 集群（cluster）的引用。如果您是 PostgreSQL 的新手，这个术语可能会让您感到困惑。PostgreSQL 集群是 PostgreSQL 引用正在运行和托管（一组）数据库的单个服务器/实例的方式，并不意味着在多节点环境中设置了多个服务器。
另一个要讨论的关键思想是最小特权原则 (PoLP)，这是一种信息安全方法，规定用户只应被授予访问完成其工作或任务所需的最少信息量的权限。他们拥有的文件或数据以外的任何访问权限都必须专门授予他们。
尽管没有在文档中具体说明，但 PostgreSQL 安全性的许多细微差别以及角色和权限如何协同工作，是在考虑 PoLP 的情况下实现的。
例如，只有数据库对象（模式、表、函数等）的所有者才能使用或更改它，除非已将特定访问权限授予其他角色。
例如，大多数用户可能期望具有相同数据库或模式权限（例如SELECT）的两个角色，默认情况下应该能够从任何表中进行查询。在没有一些干预的情况下，这不是它在 PostgreSQL 中的工作方式。
相反，对象总是由一个角色（并且只有一个角色）拥有，在大多数情况下，该角色必须为其他角色设置权限。可以修改此行为，以便角色创建的新对象自动将特定权限授予其他角色，但开箱即用，对象所有权是在 PostgreSQL 数据库中创建角色和其他对象时需要理解的关键概念。
我们将在有关对象所有权和特权的后续文章中对此进行更多讨论。
在深入研究创建角色和授予权限之前，要讨论的最后一件事是超级用户的概念。在 PostgreSQL 中，超级用户是一个可以在系统中执行任何操作的角色，类似于Linux 中的root用户或SQL Server 中的sa帐户。
当用户被授予超级用户后，当他们执行 SQL 代码 (DDL/DML) 或管理集群时，不会执行任何权限检查。执行的唯一检查是是否允许他们登录并连接到集群。被指定为超级用户会绕过所有其他检查，包括行级安全性 (RLS) 之类的检查。此行为不同于某些其他数据库系统。
为了正常运行，每个 PostgreSQL 集群都需要至少有一个超级用户来执行一些管理任务。最初这些任务可能是创建数据库和其他角色，但从 PostgreSQL 15 开始，有一些任务（安装一些扩展、修改一些服务器参数）仍然需要超级用户。
在 PostgreSQL 中，默认调用初始超级用户postgres，它与运行 PostgreSQL 集群进程的系统用户名相匹配。initdb可以使用命令更改此初始超级用户以实例化您自己的 PostgreSQL 集群。该讨论超出了本文的范围，但通常不建议在没有更多经验的情况下进行。
最后一点。如果您的 PostgreSQL 数据库由 AWS RDS 或 Azure Postgres 等服务托管，您可能无法访问超级用户角色。相反，为您创建的初始角色拥有管理用户以及创建数据库和对象所需的大部分权限。即使您托管自己的 PostgreSQL 集群并有权访问超级用户角色，建议（和记录的最佳实践）是至少创建一个具有CREATE USER和CREATE DATABASE权限但不是超级用户的角色。使用这些特定角色可以创建新用户（或另一个数据库），但不能绕过其他安全检查，例如从他们无权访问的表中查询数据。这将允许执行几乎所有管理任务，而无需在数据库或集群中工作时绕过所有权限检查。
回想一下，在 PostgreSQL 中，用户和组在技术上都是角色。这些总是在集群级别创建，并授予对其中的数据库和其他对象的特权。根据您的数据库背景，您可能会惊讶地发现角色并未创建为每个数据库内部的主体。现在，请记住角色（用户和组）是作为集群主体创建的，它（可能）拥有数据库中的对象，并且拥有一个对象提供的额外特权，我们将在本文后面探讨这一点。
出于本文的目的，所有示例用户角色都将使用密码身份验证创建。还可以使用其他身份验证方法，包括 GSSPI、SSPI、Kerberos、证书等。然而，设置这些替代方法超出了我们需要讨论的对象所有权和特权的范围。
要在 PostgreSQL 中创建用户角色，请以具有“CREATEROLE”权限的用户身份执行以下 DDL。如上所述，这通常是您的PostgreSQL托管解决方案提供的超级用户或管理用户。
CREATE ROLE dev1 WITH LOGIN PASSWORD ‘supersecretpw’;
或者，PostgreSQL 仍然支持旧CREATE USER命令，但它只是CREATE ROLE的别名，理论上它会在某个时候被弃用，所以用户应该倾向于CREATE ROLE。
-- This still works as of PostgreSQL 15. Same as above, -- but implicitly adds LOGINCREATE USER dev1 WITH PASSWORD ‘supersecretpw’;
在许多用户角色之间维护独特的权限可能很麻烦且容易出错。因此，最好创建充当组的角色，以便用户可以根据需要继承组权限。例如，考虑下图：
如果角色是reader角色的成员，则他们对该public.table_name对象具有SELECT特权。如果在creator角色中，那么INSERT特权也一样，因为每个角色都被明确授予了对资源的特权。角色reader_and_creator没有被明确授予public角色之外的任何特权，但确实拥有SELECT和INSERT特权，因为public.table_name已被授予reader和creator。通过以非常谨慎的方式制作角色，可以通过继承来管理用户角色，而不是向每个用户授予单独的权限。这一点非常重要，因为用户角色会在不同的环境中发生变化（例如，可以修改 DEV 的人甚至可能无法登录 PROD 集群。）
要在 PostgreSQL 中创建组角色，请创建一个不允许登录的角色。如前所述，这只是将角色表示为一个组的约定。
CREATE ROLE devgrp WITH NOLOGIN;
与用户角色一样，PostgreSQL 仍然支持旧CREATE GROUP命令，尽管它是CREATE ROLE的直接别名，因为所有角色都是默认创建的NOLOGIN，正如我们所讨论的，这意味着角色作为一个组使用。使用CREATE GROUP没有任何优势，并且在某些时候可能会被弃用。
还有许多其他角色属性可以在创建或通过ALTER ROLE时应用。让我强调一些额外的角色属性及其默认值。
这个角色也是超级用户吗？超级用户角色是有效运行 PostgreSQL 集群所必需的，但应该很少见并且使用有限。超级用户绕过所有其他权限和角色属性检查。（即超级用户可以在系统中做任何事情！）
只有超级用户才能创建另一个超级用户。
角色可以被授予其他角色的成员资格。如果一个角色可以从另一个角色继承，那么他们可以自动使用特权而无需切换角色，类似于特权在操作系统和文件系统中的工作方式。
如果用户没有INHERIT“父”组的权限，则他们必须切换到该角色 ( SET ROLE) 才能利用该组的特定权限。
角色属性选项（默认粗体）
描述
SUPERUSER/NOSUPERUSER
CREATEDB/NOCREATEDB
这个角色可以在给定的 PostgreSQL 集群上创建数据库吗？
CREATEROLE/ NOCREATEROLE
是否允许此角色在 PostgreSQL 集群上创建其他角色（用户或组）？
INHERIT/NOINHERIT
LOGIN/NOLOGIN
这个角色可以验证并登录到集群吗？LOGIN必须特别指定以允许新角色进行身份验证。
有关所有可能的属性和更详细的更多信息，请参阅文档。此外，可以随时使用ALTER ROLE命令修改任何这些角色属性（包括超级用户）。
现在我们有了角色，其中一个可以登录（我们的用户），另一个不能登录（我们的组），我们需要深入研究如何分配和应用权限。
每个 PostgreSQL 集群都有另一个隐式角色，被称为PUBLIC，不能删除。默认情况下，所有其他角色始终被授予PUBLIC成员资格，并继承当前分配给它的任何特权。除非另有修改，授予PUBLIC角色的权限如下。
PostgreSQL 14 及以下版本
PostgreSQL 15 及以上
这里要注意的主要一点是，PUBLIC角色始终具有CONNECT默认授予的权限，这可以方便地允许所有角色连接到新创建的数据库。如果没有连接到数据库的权限，我们新创建的角色基本无法做事情。
与 PostgreSQL 数据库集群的连接总是对特定的数据库开放，而不仅仅是集群。请记住，我们在本文开始时讨论的是身份验证和授权。要打开到 PostgreSQL 集群的连接，用户首先需要进行身份验证（为角色提供WITH LOGIN凭据），然后获得连接到数据库的授权。因为每个角色都被授予PUBLIC角色的成员资格，并且默认情况下该角色具有特权CONNECT，所以所有可以进行身份验证的角色也被允许连接访问数据库。
这些默认值中的许多都可以修改。作为数据库管理员，您可以撤销PUBLIC角色CONNECT的能力，然后将其单独授予每个角色，但是管理这种能力的复杂性很少值得这样做。
需要明确的是，此权限仅允许用户连接和查看public模式中的高级对象（例如表）。但是，未经进一步许可，他们不能从表中查询或修改任何内容。我们稍后会讨论这个。
最后一件事我想在上表中指出，它列出了PUBLIC角色的默认权限。
请注意，默认权限在 PostgreSQL 15 及更高版本中略有变化。以前，PUBLIC角色被允许用于数据库public模式中创建对象，并且默认情况下每个数据库都有一个public 模式。为了向后兼容非常旧的 PostgreSQL 版本，此权限多年前授予 public 角色，但也存在安全漏洞。随着 DBaaS 服务的兴起，新角色可能会在public模式中创建表、触发器和函数，这意味着他们可能会执行代码，这些代码稍后会导致他们的特权提升（或更糟）。
因此，从 PostgreSQL 15 开始，public 角色不能再默认在任何模式中创建任何东西。虽然这对管理员来说意味着更多的工作，但长期以来，撤销CREATE特权一直是推荐的最佳做法。
使用我们创建的新用户角色dev1，我们可以使用psql命令登录到 PostgreSQL 集群。在以下示例中，PostgreSQL 集群在我的笔记本电脑上（本地托管），是使用默认端口 5432 的 Docker。您的连接详细信息（包括数据库名称）可能不同，应根据需要进行调整。
--使用标志，您将被提示输入密码为 --没有特定的“密码”标志 psql -h localhost -U dev1 -d postgres --交替使用PostgreSQL连接URI psql postgres://dev1:supersecretpw@localhost:5432/postgres
如果用户postgres没有通过 URI 提供密码连接到数据库，将提示输入密码。它之所以有效，是因为角色 dev1被自动授予PUBLIC角色的成员资格，而该角色又有权连接到postgres数据库。
通过密码身份验证和一个 DDL 语句，我们创建了一个角色并登录到 PostgreSQL 实例。不太难，对吧？
那么现在，让我们开始为新创建的dev1帐户开发一项新功能吧！
作为开发人员用户，我的首要任务是开始创建支持我正在开发新功能所需的表。该功能需要一个表来存储每个用户的社交帐户。我不确定我们计划支持多少个社交帐户句柄，因此我将从一个基本表开始，该表将每个句柄存储在单独的列中。
根据所连接的PostgreSQL版本的不同，这可能很容易，也可能不容易。记住：PostgreSQL 14及以下版本默认允许PUBLIC角色(' dev1 '是其中的成员)在PUBLIC模式中创建（create）。然而，PostgreSQL 15及以上版本不允许这样做。
以 `dev1` 身份登录后，我们尝试创建下表。
CREATE TABLE user_social ( user_id INT NOT NULL, twitter_handle TEXT NULL, facebook_handle TEXT NULL );在 PostgreSQL 14 及以下版本中，考虑到我们目前所做的设置，这可能会在大多数安装中成功。然而，在 PostgreSQL 15 及更高版本中，我们的 dev1 用户很可能会收到以下错误。
ERROR: permission denied for schema public LINE 1: CREATE TABLE user_social (让我重申一下，这只适用于PostgreSQL 14及以下版本，因为PUBLIC角色默认被授予在PUBLIC模式中创建对象的能力。如果我们尝试在数据库中创建新模式之类的操作，也会收到类似的错误，因为PUBLIC没有被授予整个数据库的create权限，只有PUBLIC模式的create权限。
作为参考，CREATE特权可以应用于PostgreSQL中的三个领域(数据库、模式和表空间)，并允许根据您拥有CREATE权限的位置创建不同的对象，如文档中的特权页面所述。
无论我们需要创建表还是新模式，我们如何确保新的开发人员角色可以做到这一点？
我们必须授予（grant）他们特权。
PostgreSQL内置了一组强大的特权，所有这些都在文档中进行了概述。对于下面的示例，我们将重点关注在PostgreSQL 15中为dev1用户授予CREATE权限。
-- As a superuser or role that can grant this -- privilege to others GRANT CREATE ON SCHEMA public TO dev1;现在dev1用户已经授予CREATE权限，我们可以尝试在PostgreSQL 15 中再次创建表。我们可以保持多个会话打开（在psql中），但另一种选择是使用SET ROLE，如果您以超级用户身份登录或您是其成员，则可以“切换”到另一个角色。这有助于在创建时设置正确的对象所有权，以及像我们这样测试权限。
-- temporarily set role in the current session to a different --role. Only superusers or members of the role can do this. SET ROLE dev1; -- Create the table as dev1 given the new permissions CREATE TABLE user_social ( user_id INT NOT NULL, twitter_handle TEXT NULL, facebook_handle TEXT NULL ); -- set the role back to the session initiated role SET ROLE NONE;成功！我们距离将此新功能添加到我们的应用程序又近了一步。
不幸的是，我们很快就遇到了另一个问题。要开发此功能，我们的开发人员需要在 `user` 表中查询数据，但尝试这样做会导致另一个错误：
-- set our role back again if connected through -- another user session SET ROLE dev1; -- execute a select query against another table SELECT * FROM “user” u INNER JOIN user_social usoc USING(user_id);这将返回：
ERROR: permission denied for table user正如您可能猜到的那样，解决方案是为dev1角色授予另一个特权。作为superuser角色具有授予这些特权的能力：
-- In this example, grant SELECT on ALL tables in the schema. -- We could be more selective if needed GRANT SELECT ON ALL TABLES IN SCHEMA public TO dev1;如果我们的开发人员知道完成工作所需的所有权限，可以一次添加多个权限。例如，如果开发人员需要SELECT、INSERT、UPDATE和DELETE模式public的表中的数据，我们可以在一个语句中完成。
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public TO dev1;
不过，老实说吧。从一个数据库到另一个数据库，一次管理每个角色的权限将是令人头疼的维护问题。一旦我们为团队中的下一位开发人员创建了一个帐户dev2，我们就必须重新开始整个过程。
当然有更好的方法来管理它。
PostgreSQL 提供了将一个角色的权限授予另一个角色的能力。具体来说，组角色（无法登录的角色）是一次向许多用户应用一组权限的完美机制。
之前我们创建了一个名为devgrp的组角色。它不允许登录，我们还没有授予它任何权限。我们可以授予此角色想要提供给所有开发人员的特权，然后再将角色devgrp授予其它用户角色。然后，任何时候我们需要调整开发人员权限时，我们只需修改（GRANT或REVOKE）、 devgrp 角色的权限。
-- As a superuser or role that has the ability to grant
-- this privilege to others
GRANT CREATE ON SCHEMA public TO devgrp;
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public TO devgrp;
现在，我们授予成员资格的任何角色都将默认继承这些权限。
GRANT devgrp TO dev1;此时dev1继承所有devgrp拥有的特权。每当新开发人员加入团队并需要访问数据库时，我们都可以创建一个角色并授予他们该devgrp角色的成员资格。可以随时使用上面的GRANT语句完成，或者如果组角色已经存在，则可以在角色创建期间完成。
-- This will create the role and automatically add it as
-- a member of the devgrp role
CREATE ROLE dev2 WITH LOGIN PASSWORD ‘supersecretpw2’
IN ROLE devgrp;
PostgreSQL 有全面的角色和权限系统，可以非常灵活地管理对服务器对象和资源的访问。在本文中，我们了解了角色，如何有效地使用它们来管理用户和组，以及如何为我们创建的新角色授予一些初始权限。
我们还讨论了超级用户角色的重要性，并且大多数 DBaaS 服务不会为您提供超级用户，因为它会绕过所有权限检查。相反，最佳做法是创建一个或多个具有日常管理权限（CREATEROLE、CREATEDB）的管理用户。如果您使用像 AWS 或 Azure 这样的服务，他们提供的管理用户可能拥有这些特权，而不是完全的超级用户。
一旦我们创建了新角色，我们就简要讨论了如何单独和通过继承向新角色授予特权，以便我们的新角色可以完成开发新应用程序功能所需的工作。
