PostgreSQL数据库所有者无法授予Public模式SELECT权限问题排查

问题核心原因 #

1. GRANT ON ALL TABLES仅作用于已存在的表：你在创建表t之前执行了grant select on all tables in schema public to public;，此时public模式下没有任何表，这条授权命令没有实际生效，后续创建的表t不会继承这个未生效的权限。
2. 未设置默认权限：PostgreSQL默认不会为新创建的表自动赋予public角色SELECT权限，你没有配置默认权限规则，导致表t的权限仅对所有者db_owner开放。

正确配置步骤 #

-- 1. 以超级用户postgres登录，创建数据库所有者角色和目标数据库
\connect postgres postgres;
create role db_owner with createrole password 'passwd' login;
create database db with owner db_owner;

-- 2. 切换到目标数据库，以db_owner身份操作
\connect db db_owner;

-- 可选：将public模式的所有者改为db_owner，避免后续权限归属混淆（默认public所有者是postgres）
alter schema public owner to db_owner;

-- 3. 设置默认权限：后续在public模式创建的表，自动给public角色赋予SELECT权限
alter default privileges in schema public grant select on tables to public;

-- 4. 创建表，此时表会自动继承上述默认权限
create table t (i int);

-- 若需对已存在的表补授权，执行以下命令
grant select on all tables in schema public to public;

-- 5. 创建业务角色并配置专属模式
create role s1 with password 's1' login;
grant s1 to db_owner; -- 可选：将s1加入db_owner组，便于管理
create schema authorization s1; -- 创建s1的专属模式，s1自动拥有该模式的全部权限

补充说明 #

• 当你用postgres超级用户执行grant select on all tables in schema public to public;时，表t已经存在，这条命令会直接为t赋予public角色的SELECT权限，因此s1可以正常查询。
• create schema authorization s1会自动将模式的所有权赋予s1，s1默认拥有该模式下所有对象的全部权限，无需额外授权。

内容的提问来源于stack exchange，提问作者Clodoaldo Pinto