PostgreSQL-只授予模式中的用户读/写权限

Question

我想创建一个用户team，其写访问权限仅限于单个模式team，只读访问权限仅限public。

以下是我尝试过的：

-- create user and schema
CREATE SCHEMA IF NOT EXISTS team;
CREATE USER team WITH ENCRYPTED PASSWORD 'xxxxxxxx';
-- team schema write privileges
GRANT CREATE ON DATABASE analytics TO team;
GRANT USAGE ON SCHEMA team TO team;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA team TO team;
ALTER DEFAULT PRIVILEGES FOR USER team IN SCHEMA team GRANT ALL PRIVILEGES ON TABLES TO team;
-- public schema read only privileges
GRANT USAGE ON SCHEMA public TO team;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO team;
ALTER DEFAULT PRIVILEGES FOR USER team IN SCHEMA public GRANT SELECT ON TABLES TO team;

测试数据：

CREATE TABLE IF NOT EXISTS public.test_table AS SELECT 1 col1; -- should be denied for team user
CREATE TABLE IF NOT EXISTS team.test_table AS SELECT 2 col1; -- should be allowed for team user
SELECT * FROM team.test_table ; -- should be allowed for team user
SELECT * FROM public.existing_table ; -- should be allowed for team user

问题是，当作为team连接到数据库时，SELECT * FROM team.test_table引发了权限错误(对于该用户的public.test_table也是如此(。

我显然错过了什么，我不知道那是什么…

Answer 1

在授予默认权限时应提及表创建者。例如，假设您正在与用户XYZ一起创建表，则默认权限语句类似于

SCHEMA团队中角色XYZ的ALTER默认特权将表格上的所有内容授予团队；

对于公共只读，您可以撤销用户团队的权限

REVOKE CREATE ON SCHEMA public FROM团队；

将SCHEMA中所有表格上的所有内容从团队中公开；

在SCHEMA的所有表格上授予选择权，对团队公开；