postgresql - 在 Postgres 13 中为未按预期工作的表设置默认权限

Question

设置:我正在使用 Google Cloud Platform 的托管 Postgres 13 实例，这是全新安装，没有现有的表或用户(postgres 管理员除外)。

目标:我想创建一个名为 my_db 的新数据库，并有两个新用户 steve 和 mike能够在 future 表上执行 DDL 和 DML 命令(例如，创建新表、插入数据、读取等)。这也意味着 steve 和 mike 应该能够修改和读/写彼此的表。

问题:即使当我创建用户并设置 default privileges in schema public grant all 到 my_db 中的表时，也只有创建者 (新表的 steve) 可以读/写表，而 mike 不能。此外，即使是 postgres 管理员也无法读取新表!

步骤:如何重新创建

1. 首先，我将创建新的数据库和用户，并授予他们修改/读取/写入my_db 中 future 表的宽松权限。

-- Logged in as user = postgres (Connection 1)
\c postgres

create database my_db;

-- connect to my_db and create the new users
\c my_db

-- steve user
CREATE USER steve WITH PASSWORD 'pass123';
GRANT connect ON DATABASE my_db TO steve;
alter default privileges in schema public grant all on tables to steve;

-- mike user
CREATE USER mike WITH PASSWORD 'pass456';
GRANT connect ON DATABASE my_db TO mike;
alter default privileges in schema public grant all on tables to mike;

如果我的理解是正确的，steve 和mike 现在可以在my_db 中创建和修改表。我们来测试一下。

1. 然后以 steve 的身份登录到新的连接 #2 并创建一个新的测试表:

-- Logged in as user = steve (Connection 2)
\c my_db

create table test_tbl ( id int4 ); -- success
select * from test_tbl; -- 0 records

1. 现在让我们看看 mike 是否可以从 test_tbl 中读取他应该给定的默认权限。我们为 mike 创建连接#3:

-- Logged in as user = mike (Connection 3)
\c my_db

select * from test_tbl; -- ERROR: steve does not have permissions to read test_tbl!

这是我的第一个困惑点，因为我认为默认权限会让 mike 读取由 steve 创建的 test_tbl。

1. 作为最后一个怪事，我决定回到 postgres 用户来测试阅读 test_tbl:

-- Logged in as user = postgres (Connection 1)
\c my_db

select * from test_tbl; -- ERROR: steve does not have permissions to read test_tbl!

GRANT SELECT, INSERT, UPDATE, delete ON ALL TABLES IN SCHEMA public TO mike; -- same error above!

所以即使是管理员用户 postgres 也不能读取这个新表，我也不能授予权限...

1. 唯一可行的是作为原始表创建者 steve 重新登录并授予 postgres 和 mike 权限:

-- Logged in as user = steve (Connection 2)
\c my_db

GRANT SELECT, INSERT, UPDATE, delete ON ALL TABLES IN SCHEMA public TO postgres; -- success
GRANT SELECT, INSERT, UPDATE, delete ON ALL TABLES IN SCHEMA public TO mike; -- success

这一切似乎都是倒退的。 schema public grant all 中的默认权限 应该允许用户修改 future 的表，对吧？我错过了什么？

提前致谢。

Answer 1

ALTER DEFAULT PRIVILEGES 没有 FOR ROLE 子句只影响运行 ALTER DEFAULT PRIVILEGES 语句的角色创建的对象。

你需要两个这样的语句来得到你想要的:

ALTER DEFAULT PRIVILEGES FOR ROLE mike GRANT ... TO steve;
ALTER DEFAULT PRIVILEGES FOR ROLE steve GRANT ... TO mike;

您想要的其他东西无法以直截了当的方式获得。只有所有者(或该角色的成员)和 super 用户可以ALTER 或DROP 一个对象。没有办法授予该特权。您唯一的解决方案是拥有一个共同的 table_owner 角色，并且让两个用户都是该角色的成员:

CREATE ROLE table_owner NOLOGIN;

GRANT CREATE ON SCHEMA myschema TO table_owner;

ALTER ROLE mike NOINHERIT;
ALTER ROLE steve NOINHERIT;

GRANT table_owner TO mike, steve;

现在两个用户都需要SET ROLE 来在模式中创建一个表:

SET ROLE table_owner;

CREATE TABLE myschema.atable (...);

然后该表由 table_owner 拥有，两个用户都可以ALTER 或 DROP 它。