Resolving 'permission denied for database postgres' during migrations

如果你在迁移过程中遇到 permission denied for database postgres 错误，通常是在 SET ROLE 语句之后执行 CREATE SCHEMA 或类似命令时发生的。

🌐 If you encounter a permission denied for database postgres error during migrations, it typically occurs when executing CREATE SCHEMA or similar commands after a SET ROLE statement.

为什么会这样 #

🌐 Why this happens

Postgres 15 及以后的版本从 PUBLIC 角色中移除了默认的 CREATE 权限。这意味着自定义角色不会自动拥有在数据库中创建对象的权限，即使会话是由 postgres 用户启动的。

🌐 Postgres 15 and later versions remove default CREATE privileges from the PUBLIC role. This means custom roles do not automatically have permission to create objects in the database, even if the session was initiated by the postgres user.

如何解决这个问题 #

🌐 How to resolve the issue

要解决这个问题，你必须在数据库上授予特定角色 CREATE 权限，该角色在你的迁移脚本中使用。在 SQL 编辑器 中运行以下命令：

🌐 To resolve this, you must grant the CREATE privilege on the database to the specific role used in your migration script. Run the following command in the SQL editor:

1
GRANT CREATE ON DATABASE postgres TO example_role;

在迁移过程中，将 example_role 替换为所扮演角色的名称（例如，你的模式的所有者角色）。

🌐 Replace example_role with the name of the role being assumed during the migration (for example, the owner role of your schemas).

相关 #

🌐 Related

如果这是通过 REST/GraphQL 请求发生的，而不是直接连接或迁移脚本，请改看 数据库 API 42501 错误 — 那篇指南涵盖了 Data API 特有的不同原因。

🌐 If this happened via a REST/GraphQL request instead of a direct connection or migration script, see Database API 42501 errors instead — that guide covers a different set of causes specific to the Data API.