在上一篇 PostgreSQL 基础知识：角色和特权文章中，讨论了如何根据需要在 PostgreSQL 中创建和管理角色（用户和组）。根据您的权限背景，特别是在其他数据库产品中，权限工作方式中的一些细微差别可能令人惊讶。
理解角色和特权在 Postgres 中如何工作，是理解下一个更令人困惑的安全部分：对象所有权的关键。尽管在 Postgres 中可以分配许多特权，但对象所有权具有特定级别的特权，不能转移给其他角色。随着时间的推移，了解其含义对于管理数据库架构以及访问其中包含的对象至关重要。
一、谁拥有数据库对象？
在 PostgreSQL 中，创建对象（表、视图、函数等）的角色成为所有者。它可以事后更改，但最初，创建者是所有者。我们可以使用交互式psql终端或查询pg_catalog对象类型对应的表来查看数据库中对象的所有者。
SET ROLE user1; --impersonate user1 CREATE TABLE public.example_tbl ( id INT NOT NULL, notes TEXT NULL ); SET ROLE none; 在 psql 中，使用“describe”元命令： 1 \d
在一个干净的数据库上，将显示表对象的拥有者（如果你的数据库中有其他对象，你可能会得到额外的输出行。）：
正如我们所见，表的所有者被设置为user1，因为该角色创建了它。
\d中的元命令是在幕后psql 执行以下查询，向我们展示关系列表（包括我们创建的表）以及谁拥有每个关系。
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 't' THEN 'TOAST table'
WHEN 'f' THEN 'foreign table'
WHEN 'p' THEN 'partitioned table'
WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_am am
ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
提示：如您所见，PostgreSQL 中的目录包含很多有用的信息。然而，学习如何有效地查询它们需要时间和经验。这就是psql元命令特别有用并且在 PostgreSQL 开发人员中非常流行的原因。
我已经能听到你在想什么了。
“你一直说对象所有者很重要，但你没有说为什么这很重要！当然，对同一模式具有适当权限的任何其他角色都可以使用该表。对吗？”
关于对象所有权，您需要了解三个要点：
1、只有超级用户（superuser）或对象(表、函数、过程、序列等)的所有者才能ALTER/DROP对象。
2、只有超级用户（superuser）或对象的所有者才能更改该对象的所有权。
3、只有对象的所有者可以为他们创建的对象定义默认权限。
让我们看看详情，以更好地描述所有权和特权如何一起发挥作用，以及您可以做些什么来在 PostgreSQL 中主动管理它。
二、设置用于演示的用户和组
对于以下示例，我们将假设您的 Postgres 实例具有名称为postgres的标准超级用户主体。然后，我们将从上一篇文章中得到启发，设置两个开发用户和一个开发组，以便更轻松地管理权限。
--NOTE: don’t execute test code like this on a cluster that --has personal information on it, especially if the cluster --can be accessed by the Internet CREATE ROLE devgrp WITH NOLOGIN; CREATE ROLE dev1 WITH LOGIN PASSWORD 'secretpw' IN ROLE devgrp; CREATE ROLE dev2 WITH LOGIN PASSWORD 'secretpw' IN ROLE devgrp; -- This will allow our developers to create objects -- in the public schema GRANT CREATE ON SCHEMA public TO devgrp; -- For example purposes only. You should be selective on -- privileges based on your needs GRANT ALL ON SCHEMA public TO devgrp; GRANT ALL ON ALL TABLES IN SCHEMA public TO devgrp; 现在用 psql 检查用户是否属于该组 1 \du
这将返回：
运行这条SQL后，数据库有两个可以登录数据库的开发人员，每个都是devgrp组角色的成员。我们已授予允许成员在公共模式中创建对象的组权限，并且他们拥有所有表的所有基本 DML 权限。
现在让我们通过观察这个开发团队开始实施新功能来探索如何克服 PostgreSQL 中的几个常见安全问题。
1. 问题 #1：改变对象
第一位开发人员已准备好深入研究新项目，跟踪各种网络的社交媒体用户句柄。首先，他们创建了一个表来存储 Twitter 和 Facebook 句柄。
-- as the 'postgres' super user we can set the -- session to impersonate any role SET ROLE dev1; CREATE TABLE user_social ( user_id INT NOT NULL, twitter_handle TEXT NULL, facebook_handle TEXT NULL );
在 psql 中，使用“describe”元命令：
1 \d
这将返回：
正如预期的那样，该表已创建并归 dev1 所有。随着开发人员着手开发该功能，他们很快意识到一个新的社交网络越来越受欢迎，他们需要为此跟踪用户句柄。dev2 提议添加新列以保持进展。
SET ROLE dev2; ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL;
这将导致出现以下错误：
ERROR: must be owner of table user_social
希望这第一个简单的示例有助于阐明为什么对象所有权在您的 PostgreSQL 模式中如此重要。没有可以授予第二个开发人员允许他们对表进行修改的特权。更改对象是为所有者（或超级用户）保留的固有特权。
大多数开始使用 PostgreSQL 并为每个开发人员分配角色的开发团队在迁移和日常开发过程中都会遇到这个问题。因为没有可以设置的权限允许其他角色更改对象，所以需要采取不同的方法。
最常见的解决方案是：将所有对象的所有权设置为一致的角色，然后将该角色的成员资格授予需要修改对象的用户。在我们的示例设置中，合理的选择是角色devgrp，因为所有开发人员都是该角色的成员。在更复杂的环境和开发团队结构中，您可能需要创建更多的组来适当地管理所有权和访问权。我将提供一个关于如何管理组的起始模板，这是本文的结尾。
为了提供一个使用我们小型开发团队的示例，我们可以将此表的所有者更改为所有开发人员都是其成员的组，在我们的例子中是角色devgrp。更改所有者后，dev2应该能够更改它，因为他们是该组的成员。
-- 使用超级用户 'postgres' 或者对象的所有者 ALTER TABLE user_social OWNER TO devgrp; SET ROLE dev2; ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL; SELECT * FROM user_social;
此查询的输出是：
或者，您可以在创建对象之前临时将会话的角色设置为公共所有者角色（假设您是该角色的成员）。创建的任何对象都将由创建时有效的角色拥有。为了演示，我将删除表并再次尝试相同的过程，但这次在创建表之前设置角色。
-- 以dev1 或超级用户 'postgres' DROP TABLE user_social; -- 作为dev1或'postgres'超级用户，我们可以设置会话来模拟devgrp角色 SET ROLE devgrp; CREATE TABLE user_social ( user_id INT NOT NULL, twitter_handle TEXT NULL, facebook_handle TEXT NULL );
在 psql 中，使用“describe”元命令：
1 \d
现在你会看到：
现在，作为第二个开发用户
SET ROLE dev2; ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL; SELECT * FROM user_social; SET ROLE none; --否则，很容易忘记你的角色背景
这将返回：
这里的关键是所有权是维护和更改 PostgreSQL 数据库中对象的重要组成部分。无论您是在创建对象之前设置角色还是在事后更改所有权，只有拥有对象的角色成员才能更改对象。
吸取教训，我们现在可以继续讨论许多团队在 PostgreSQL 中使用多个登录时遇到的第二个常见问题——默认对象权限。
2. 问题 #2：默认对象权限
我们通过将表所有者设置为所有开发人员都是其成员的角色来解决第一个问题。本质上，对象的所有者类似于该对象的超级用户。
但是，当我们向数据库添加一个将用于报告或只读目的的新角色时会发生什么？
开发团队已决定需要一个新角色来促进此新功能（将生成的某些数据的报告功能）。
-- 作为超级用户或具有CREATEROLE属性的角色 CREATE ROLE rptusr WITH LOGIN PASSWORD 'secretpw'; -- 将会话设置为新角色 SET ROLE rptusr; --统计拥有Mastodon句柄的用户数量 SELECT count(*) FROM user_social WHERE mastodon_handle IS NOT NULL;
这会导致错误：
ERROR: permission denied for table user_social
鉴于我们目前所了解的情况，这应该不足为奇。新rptusr角色是在表存在之后创建的，并且没有被授予对该表的任何权限。对象的超级用户或所有者必须明确授予必要的权限。
-- 作为所需对象的超级用户或所有者 GRANT SELECT ON TABLE user_social TO rptusr; -- 将会话设置为rptusr 角色 SET ROLE rptusr; -- 统计拥有Mastodon句柄的用户数量 SELECT count(*) FROM user_social WHERE mastodon_handle IS NOT NULL; SET ROLE none;
这将返回：
在本系列的第一篇文章中，我们将确保用户仅拥有最低限度必要权限的过程称为最小权限原则。一个对象一个对象地设置权限很快就会成为一项令人厌烦的任务。
添加组角色也没有帮助，因为同样的问题仍然存在。特权只授予GRANT时存在的对象。换句话说，GRANT不是前瞻性的行动。相反，我们需要一种方法让PostgreSQL在每次创建对象时都应用特权。
输入默认权限。
每个角色都可以创建一组默认访问权限，只要他们在特定数据库中创建对象，就会应用这些权限。这为每个角色提供了完全控制，确保每次都使用正确的权限创建对象。
为了说明这一点，让我们先创建新的默认访问权限，然后再创建rptuser应该能够查询的另一个新表。
首先，使用 psql 检查是否没有默认访问权限：
1 \ddp
在我的演示服务器上，这显示没有默认访问权限。
接下来，我们将安全上下文设置为您要为其设置默认权限的组，当他们在某些模式中创建新对象时将应用这些权限。
-- 作为将创建对象的角色，创建 -- 默认权限 SET ROLE devgrp; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rptusr; SET ROLE none;
再次检查是否创建了默认权限：
1 \ddp
这将返回：
我们可以看到创建了默认访问权限，该权限将为在模式中创建的任何表授予SELECT（读取）角色权限。为了验证它是否有效，我们现在可以创建一个新表并尝试从中查询，因为无需使用grant语句进行额外干预。
-- 作为将拥有该表的devgrp角色 SET ROLE devgrp; CREATE TABLE rpt_log ( id int NOT NULL, rpt_date timestamptz NOT NULL, notes TEXT null ); SET ROLE rptusr; -- 从表中选择以验证该特权 -- 正确应用 SELECT * FROM rpt_log;
这将返回：
成功!devgrp能够创建表，新的rptusr能够从表中进行选择，而不会出现错误。接下来，只要devgrp创建并拥有表(我们的示例对象)，rptusr就能够从中进行选择。
不幸的是，我们只解决了这个名为 rptusr 的“只读”角色的问题。一旦另一个只读用户需要访问数据库对象，我们就必须授予现有表的权限，然后为将来的操作创建另一个默认访问权限。这不是很可持续，只是强调了我们在上一篇文章中讨论的内容。
一种常见的处理方法是：创建只读组角色并为其设置默认访问权限。然后，当在数据库中创建新的只读用户时，他们可以被授予只读组角色的成员资格，从而继承相同的权限。
首先，使用以下psql命令检查当前的默认访问权限：
1 \ddp
这将返回：
现在我们将在创建新配置之前撤消当前配置。
-- REVOKE当前单个用户的默认访问权限 ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM rptusr;
检查访问权限是否已删除：
1 \ddp
这将返回：
现在创建新组和安全设置：
-- 创建一个只读组角色 CREATE ROLE read_only WITH NOLOGIN; -- 在公共模式public所有表上授予查询权限 -- 请记住:这只是针对当前表，而不是未来的表 GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only; --授予read_only角色成员资格 GRANT read_only TO rptusr; -- 现在为创建对象的角色创建相同的默认访问权限 SET ROLE devgrp; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;
再次检查是否创建了默认权限：
1 \ddp
这将返回以下内容：
此时，devgrp用户创建的任何表都将应用此默认访问权限，并且该read_only角色的所有成员都将能够查询数据。
对于只读用户，PostgreSQL 14+ 确实提供了新的默认角色以更轻松地管理对表数据的访问。在多租户环境中正确设置它有足够的细微差别，可以在将来单独写一篇文章。
三、大规模管理所有权和特权
在前两篇文章中，我们已经介绍了很多关于 PostgreSQL 角色和安全性的基础知识。
1、PostgreSQL 有角色。用户和组是同义词。
2、按照惯例，用户角色可以登录，而组角色不能。
3、超级用户绕过所有权限检查，可以访问或修改任何对象和集群设置。
4、除了对象所有者或超级用户之外，每个角色都必须专门授予（或撤销）对数据和对象的访问权限。
5、角色的权限可以通过继承的方式被授予其他角色。
6、所有角色都具有自动的、不可撤销的PUBLIC角色成员资格
7、对象的所有者（或超级用户）是唯一可以访问ALTER该DROP对象的角色。
8、每个角色都可以为他们创建的任何对象（如表）创建默认访问权限，这样其他角色就可以自动访问，而不是在每次创建对象时都应用权限。
如果对PostgreSQL安全性来说您是新手，那么需要考虑和管理的信息很多，这使得在更大的团队中管理 PostgreSQL 权限变得具有挑战性。尽早制定计划或启动模板来管理 PostgreSQL 权限和对象所有权是很有帮助的第一步。很多次，我看到团队在不了解和没有安全计划的情况下开始一个项目，通常是因为他们没有完全理解我们一直在讨论的事情。
也就是说，这是您开始时就要考虑的示例角色和安全设置。使用您对我们目前讨论的内容（以及官方文档）的了解，结合您组织的要求，修改和制定适合的方案。
在下一篇文章中，我们将了解如何在每次创建新数据库时将所有这些放在一起以创建一组权限模板。这将确保您拥有跨数据库和角色的一致、可重现的访问权限。
四、结论
PostgreSQL 的安全和所有权模型可能会让新用户感到困惑。了解角色的创建方式、权限的分配方式以及随着时间的推移如何为一致的访问准备数据库将帮助您有效地使用 PostgreSQL 并让您的团队顺畅地工作。
有疑问加站长微信联系（非本文作者）
