在上一篇 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
\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;
关于对象所有权，您需要了解三个要点：
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
现在让我们通过观察这个开发团队开始实施新功能来探索如何克服 PostgreSQL 中的几个常见安全问题。
问题 #1：改变对象
-- 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
这将返回：
SET ROLE dev2;
ALTER TABLE user_social ADD COLUMN mastodon_handle TEXT NULL;
ERROR: must be owner of table user_social
大多数开始使用 PostgreSQL 并为每个开发人员分配角色的开发团队在迁移和日常开发过程中都会遇到这个问题。因为没有可以设置的权限允许其他角色更改对象，所以需要采取不同的方法。
-- 使用超级用户 '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 中使用多个登录时遇到的第二个常见问题——默认对象权限。
问题 #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应该能够查询的另一个新表。
1 \ddp
在我的演示服务器上，这显示没有默认访问权限。
接下来，我们将安全上下文设置为您要为其设置默认权限的组，当他们在某些模式中创建新对象时将应用这些权限。
-- 作为将创建对象的角色，创建
-- 默认权限
SET ROLE devgrp;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO rptusr;
SET ROLE none;
1 \ddp
-- 作为将拥有该表的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;
不幸的是，我们只解决了这个名为 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
对于只读用户，PostgreSQL 14+ 确实提供了新的默认角色以更轻松地管理对表数据的访问。在多租户环境中正确设置它有足够的细微差别，可以在将来单独写一篇文章。
大规模管理所有权和特权
在前两篇文章中，我们已经介绍了很多关于 PostgreSQL 角色和安全性的基础知识。
2、按照惯例，用户角色可以登录，而组角色不能。
3、超级用户绕过所有权限检查，可以访问或修改任何对象和集群设置。
4、除了对象所有者或超级用户之外，每个角色都必须专门授予（或撤销）对数据和对象的访问权限。
5、角色的权限可以通过继承的方式被授予其他角色。
6、所有角色都具有自动的、不可撤销的PUBLIC角色成员资格
7、对象的所有者（或超级用户）是唯一可以访问ALTER该DROP对象的角色。
8、每个角色都可以为他们创建的任何对象（如表）创建默认访问权限，这样其他角色就可以自动访问，而不是在每次创建对象时都应用权限。
在下一篇文章中，我们将了解如何在每次创建新数据库时将所有这些放在一起以创建一组权限模板。这将确保您拥有跨数据库和角色的一致、可重现的访问权限。
结论
PostgreSQL 的安全和所有权模型可能会让新用户感到困惑。了解角色的创建方式、权限的分配方式以及随着时间的推移如何为一致的访问准备数据库将帮助您有效地使用 PostgreSQL 并让您的团队顺畅地工作。
点击此处阅读原文
↓↓↓
本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
