Azure PostgreSQL で DB 所有者なのに permission denied for schema public になる

症状

構成

アプリごとに DB とユーザーを 1 つ作り、DB の所有者をそのユーザーにしている。

CREATE USER <user_name> WITH PASSWORD '<password>';
CREATE DATABASE <db_name> OWNER <user_name>;

アプリからは、サーバー管理者ではなく <user_name> で接続し、マイグレーションを流す。

postgresql://<user_name>:<password>@<server_name>.postgres.database.azure.com:5432/<db_name>

エラー内容

DB 所有者ロールで接続しているのにマイグレーションが落ちる。

psycopg.errors.InsufficientPrivilege: permission denied for schema public
LINE 2: CREATE TABLE alembic_version (

原因

前提: PostgreSQL 15 の 2 つの変更

1. public スキーマの CREATE 権限が PUBLIC ロールから剥がされた
2. その緩和策として、public スキーマの所有者が pg_database_owner になった
   • pg_database_owner は「そのデータベースの所有者」を暗黙のメンバーとする特殊ロール
   • DB 所有者だけは今まで通り public にテーブルを作れる

nspowner = pg_database_owner
nspacl   = {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}

ACL の読み方:

• 書式は 被付与者=権限/付与者
• =U/... は被付与者が空。これは PUBLIC(全ロール)を意味する
• U = USAGE、C = CREATE
• PG15 が剥がしたのは CREATE だけで、USAGE は PUBLIC に残っている

Azure での実際

PostgreSQL 15 以降では、パブリック スキーマの所有権が新しい pg_database_owner ロールに変更されました。（中略）ただし、Azure Database for PostgreSQL では、この変更は適用されません。パブリック スキーマは、サポートされているすべての PostgreSQL バージョンの azure_pg_admin ロールによって所有されます。

(Azure Database for PostgreSQL フレキシブル サーバーでのアクセス管理 | Microsoft Learn)

nspowner = azure_pg_admin
nspacl   = {azure_pg_admin=UC/azure_pg_admin,=U/azure_pg_admin}
             └ azure_pg_admin に UC          └ PUBLIC には USAGE のみ

pg_database_owner への CREATE 権限の付与がない。

• CREATE DATABASE <db_name> OWNER <user_name> で DB 所有者にしても public スキーマには USAGE しか付かない
• DB 所有者であっても public にテーブルを作成できない

サーバー管理者は azure_pg_admin のメンバーなので、この事象は起きない。

確認方法

この事象が発生しているかは、対象 DB にアプリと同じロール (<user_name>) で接続して、以下のクエリを流すことで確認できる。

select
  current_user,
  pg_get_userbyid(d.datdba)                                as db_owner,
  n.nspowner::regrole                                      as public_owner,
  pg_has_role(current_user, 'pg_database_owner', 'USAGE')  as is_db_owner,
  has_schema_privilege(current_user, 'public', 'CREATE')   as can_create
from pg_database d, pg_namespace n
where d.datname = current_database() and n.nspname = 'public';

 current_user |  db_owner   |  public_owner  | is_db_owner | can_create
--------------+-------------+----------------+-------------+------------
 <user_name>  | <user_name> | azure_pg_admin | t           | f
(1 row)

DB 所有者である (is_db_owner = t) にも関わらず、public スキーマにテーブルを作れない (can_create = f) ことがわかる。

対処法

対処法 A: public に権限を付ける

grant create on schema public to <user_name>;

USAGE 権限は付与済み (=U/azure_pg_admin) なので、CREATE のみ付与すればよい。
素の PostgreSQL 15 なら pg_database_owner 経由で得ていた権限を付け直す操作のため、権限を緩めることにはならない。

ALTER SCHEMA public OWNER TO pg_database_owner では直せない。
サーバー管理者はスーパーユーザー(azuresu)ではないため、public スキーマの所有者を変更できない。

対処法 B: ユーザー名と同名の専用スキーマを作る

create schema if not exists <user_name> authorization <user_name>;

既定の search_path は "$user", public で、$user は接続ユーザー名 (<user_name>) に解決される。
名前を揃えておけば専用スキーマが自動で先頭になるため、ORM 側の設定は不要。

PostgreSQL 公式も複数ユーザーの相互隔離の実装で同じパターンを挙げている。
ここでは、それを所有者差し替えの回避に応用している。

create a schema with the same name as that user, for example CREATE SCHEMA alice AUTHORIZATION alice. (Recall that the default search path starts with $user, which resolves to the user name. Therefore, if each user has a separate schema, they access their own schemas by default.)

(Usage Patterns | PostgreSQL Documentation)

どちらを選ぶか

正直どちらでもよい。強いて言えば、次のような棲み分けはできる。

• 1 DB 1 アプリ (今回の構成) → public が事実上アプリの私有スキーマで、専用スキーマを作っても名前が変わるだけなので、対処法 A
• 1 DB を複数ユーザーで共有 → 名前空間を分ける実利があるので、対処法 B