修复 PostgreSQL 15 中“public 模式权限被拒绝”错误

问题描述

在 PostgreSQL 15 中，当非超级用户（如普通用户 admin）尝试在 public 模式中创建表时，会遇到以下错误：

permission denied for schema public

这个问题通常发生在以下操作后：

1. 创建新数据库（如 mydb）
2. 尝试以非所有者身份在 public 模式中创建表
3. 即使已执行 GRANT USAGE, CREATE ON SCHEMA public 授权语句，问题仍然存在

典型错误示例如下：

mydb=> CREATE TABLE test (id serial);
ERROR:  permission denied for schema public

原因分析

PostgreSQL 15 安全策略变更

PostgreSQL 15 引入了重大安全变更：默认撤销所有用户在 public 模式的 CREATE 权限。现在只有数据库所有者才能在 public 模式创建对象。

关键问题点：

1. 数据库隔离特性：每个数据库都有自己的 public 模式，在 postgres 数据库授予的权限不会自动应用到 mydb
2. 错误认知：GRANT ALL ON DATABASE 只授予数据库级权限（如删除数据库），不包含模式内对象创建权限
3. 创建顺序问题：如果在创建数据库前授予权限，这些权限不会被继承到新数据库

解决方案

✅ 方法一：创建数据库时直接指定所有者（推荐）

-- 创建数据库时即指定用户为所有者
CREATE DATABASE mydb OWNER admin;

优点：一步完成所有权配置，后续该用户可在 public 模式自由创建对象。

✅ 方法二：修改现有数据库的所有者

若数据库已创建但无权操作：

-- 连接到目标数据库
\c mydb

-- 修改数据库所有者
ALTER DATABASE mydb OWNER TO admin;

验证语句：

SELECT pg_catalog.has_schema_privilege('admin', 'public', 'CREATE') AS create_priv;
-- 返回 true 即表示成功

✅ 方法三：单独授予 public 模式权限（适合非所有者）

如果用户不应成为数据库所有者，需切换到目标数据库后授权：

-- 重要！必须先切换到目标数据库
\c mydb

-- 在目标数据库中执行授权
GRANT USAGE, CREATE ON SCHEMA public TO admin;

重点提示

授权操作必须在目标数据库环境中执行。在 postgres 或其他数据库中运行 GRANT 不会影响到新数据库的权限！

权限验证方法

检查用户权限：

-- 连接到目标数据库后执行
\dn+ public

期望结果应包含用户的 UC 权限：

 Access privileges
----------------------
 admin=UC/admin

最佳实践总结

1. 所有权优先：将数据库所有者设为需要创建对象的用户（通过 CREATE DATABASE ... OWNER）
2. 跨数据库权限需单独配置：每个数据库的 public 模式权限相互独立
3. 安全策略变更：PostgreSQL 15+ 默认更严格的安全策略，需显式授权
4. 生产环境推荐：使用专用模式而非 public 模式，遵循安全模式实践

关键原则：数据库所有者自动获得 public 模式全部权限，非所有者用户需显式授权且必须在目标数据库上下文执行

常见错误排查

若按上述方法操作后问题仍存在：

1. 检查当前会话是否连接到正确的数据库：SELECT current_database();
2. 确认授权语句是否在目标数据库执行（使用 \c mydb 切换）
3. 验证用户权限：\dn+ public 确认用户名在权限列表中

通过遵循以上步骤，即可解决 PostgreSQL 15 中 permission denied for schema public 错误，确保非超级用户正常操作数据库对象。