PostgreSQL如何禁止指定ROLE在PUBLIC schema中创建表

你之前的权限回收操作没有命中控制schema建表行为的核心权限点：

• PostgreSQL中，用户能否在指定schema下创建表，判断依据是用户是否持有该schema本身的CREATE权限，和用户对schema内已有表的权限、数据库级别的全局权限没有直接关系。
• 你执行的REVOKE ALL ON ALL TABLES IN SCHEMA public FROM test_role仅能回收test_role对public下已存在表的操作权限（查询、增删改、删表等），完全不影响新建表的权限判断。
• PostgreSQL默认会给内置公共角色PUBLIC（所有新建用户默认继承该角色的权限）授予public schema的CREATE权限，你没有回收这部分权限的话，所有能连接到数据库的用户默认都能在public下建表。

以管理员账号执行以下命令，回收公共角色在public schema的建表权限：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

执行完成后，仅public schema的所有者、超级用户、被单独授予该schemaCREATE权限的角色可以在public下建表。如果需要给指定角色开放建表权限，单独授权即可：

-- 仅给需要建表的角色单独授权，按需执行
GRANT CREATE ON SCHEMA public TO <允许建表的角色名>;

你提到当前环境是托管版PostgreSQL，public schema所有者为云厂商内置的超管账号_rdb_superadmin，且你使用的管理员账号无最高权限，按以下步骤排查处理即可：

1. 先查询当前哪些角色持有public schema的CREATE权限，执行以下SQL：

   SELECT
     nspname AS schema_name,
     rolname AS role_name,
     has_schema_privilege(rolname, nspname, 'CREATE') AS has_create_permission
   FROM pg_namespace, pg_roles
   WHERE nspname = 'public'
     AND has_schema_privilege(rolname, nspname, 'CREATE') = true;
   

2. 根据查询结果，回收非授权角色的建表权限，注意不要回收_rdb_superadmin等云厂商运维内置账号的权限，避免影响实例正常运维：

   -- 如果查询结果显示PUBLIC角色仍持有权限，执行该语句
   REVOKE CREATE ON SCHEMA public FROM PUBLIC;
   -- 如果查询结果显示test_role或test_user被单独授予了权限，执行对应语句回收
   REVOKE CREATE ON SCHEMA public FROM test_role;
   REVOKE CREATE ON SCHEMA public FROM test_user;
   

3. 额外注意：托管PostgreSQL通常会内置一批默认服务角色（比如pg_write_all_data、厂商自定义的rds管理角色等），如果查询发现这类角色持有public的CREATE权限，而你的test_user又恰好继承了这类角色的权限，要么将test_user从不必要的高权限角色中移除，要么按需回收这类角色在public schema的CREATE权限。

权限回收完成后，使用test_user登录数据库执行建表操作，会返回permission denied for schema public报错，即达到禁止该用户在public schema建表的预期。

操作截图

内容的提问来源于stack exchange，提问作者Esben Eickhardt