PostgreSQL 15 publicスキーマ権限エラー

PostgreSQL 15の重要なセキュリティ変更

PostgreSQL バージョン15では、デフォルトのpublicスキーマに対する権限モデルが変更されました。非スーパーユーザーがpublicスキーマにオブジェクトを作成しようとするとpermission denied for schema publicエラーが発生します。

問題の詳細

PostgreSQL 15以降で以下の現象が発生します:

• 新規作成したデータベースで非スーパーユーザーがテーブルを作成できない
• CREATE TABLEコマンド実行時にERROR: permission denied for schema publicが表示される
• 一見正しい権限を付与しているように見えるが操作が失敗する

公開された権限情報:

postgres=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres        +| 
        |          | admin=UC/postgres    | 
(1 row)

変更の背景

PostgreSQL 15ではセキュリティ強化のため、publicスキーマのCREATE権限がデータベース所有者のみに制限されました（公式リリースノート参照）。

解決策: 効果的な権限設定方法

方法1: データベース作成時にオーナーを指定

データベース新規作成時

CREATE DATABASE mydb OWNER admin;

既存データベースのオーナー変更

ALTER DATABASE mydb OWNER TO admin;

この方法でadminユーザーは:

• publicスキーマでのテーブル作成が可能に
• データベース全体の完全な制御権を取得

方法2: 明示的な権限付与（オーナー変更しない場合）

特定のスキーマに細かい権限を付与：

-- 対象データベースに接続して実行
\c mydb

GRANT USAGE, CREATE ON SCHEMA public TO admin;

重要な注意点

権限付与コマンドは対象となるデータベース内で実行する必要があります。デフォルトのpostgresデータベースで実行しても他のデータベースには影響しません。

方法3: 代替スキーマの使用実践

セキュアなスキーマ使用パターン（公式ドキュメント推奨）:

CREATE SCHEMA private_schema;
GRANT USAGE, CREATE ON SCHEMA private_schema TO admin;
ALTER ROLE admin SET search_path TO private_schema;

よくある間違いと修正例

❌ 間違った操作順序

権限付与 → データベース作成:

-- postgres DBで実行 (無効!)
GRANT USAGE, CREATE ON SCHEMA public TO admin;  
CREATE DATABASE mydb;

✅ 正しい操作順序

データベース作成 → 権限付与:

CREATE DATABASE mydb;

-- 作成したDBに接続してから実行
\c mydb
GRANT USAGE, CREATE ON SCHEMA public TO admin;

❌ 誤ったGRANTコマンド

GRANT ALL ON DATABASE mydb TO admin;  -- テーブル作成権限は付与されない

✅ 不足権限の確認

SELECT 
  pg_catalog.has_schema_privilege('admin', 'public', 'USAGE') AS has_usage,
  pg_catalog.has_schema_privilege('admin', 'public', 'CREATE') AS has_create;

権限問題のトラブルシューティング

1. 接続データベースの確認
   • \cコマンドで対象DBに接続してから権限操作
2. 権限の確認
   • \dn+でスキーマ権限を確認（対象DB内で実行）
3. エラーログの確認
   • サーバーログに詳細なエラー情報が記録される場合あり

永続的な解決策

PostgreSQLインストール後の初期設定:

sudo -u postgres psql -c "ALTER SCHEMA public OWNER TO postgres;"

この設定により、以後作成する全データベースでpublicスキーマが適切に所有されます。

まとめ

PostgreSQL 15の権限モデル変更に対処するには:

1. データベース作成時に適切なオーナーを指定する

   CREATE DATABASE newdb OWNER custom_user;

2. 既存データベースの場合は明示的に権限付与

   \c existing_db
   GRANT USAGE, CREATE ON SCHEMA public TO custom_user;

3. セキュアな代替スキーマの使用を検討する

DBオーナー変更が最もシンプルな解決策ですが、より細かい権限制御が必要な場合は対象データベース内での明示的権限付与を必ず実施してください。