PostgreSQL 15からはデフォルトでpublicスキーマにテーブルを作れない
PostgreSQL 15 のサーバーに一般のユーザで接続して、いつものように create table したら「permission denied for schema public」と怒られました。14 までは、どのユーザでも public スキーマにテーブルを作れていたので、びっくりしました、、。
sooni >> psql -h localhost -d udondb -U sooni
ユーザー sooni のパスワード:
psql (14.7、サーバー 15.3 (Debian 15.3-1.pgdg120+1))
警告: psql のメジャーバージョンは 14 ですが、サーバーのメジャーバージョンは 15 です。
psql の機能の中で、動作しないものがあるかもしれません。
"help"でヘルプを表示します。
udondb=> create table ex01 (
udondb(> execseq serial
udondb(> ,completion boolean default false
udondb(> ,rid integer
udondb(> ,name character varying (30)
udondb(> ,memo character varying (30)
udondb(> ,created timestamp default current_timestamp
udondb(> ,last_modification timestamp default current_timestamp
udondb(> );
ERROR: permission denied for schema public
行 1: create table ex01 (
^
-- publicスキーマへのアクセス権限を確認する
--
udondb=> select n.nspname as "schema",pg_catalog.pg_get_userbyid(n.nspowner) as "owner"
udondb-> ,n.nspacl
udondb-> from pg_catalog.pg_namespace n
udondb-> where n.nspname !~ '^pg_' and n.nspname <> 'information_schema'
udondb-> order by 1;
schema | owner | nspacl
--------+-------------------+---------------------------------------------------------------
public | pg_database_owner | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}
(1 行)
udondb=>nspacl の中身は、次のように読みます。
| 書き方 | 意味 |
|---|---|
| pg_database_owner=UC/… | データベースの所有者は、U（使う）と C（作る）の両方ができる |
| =U/… | 「＝」の左が空なのは「全員（PUBLIC）」のこと。全員は U（使う）だけ |
つまり、データベースの所有者でなければ、public スキーマにテーブルを作れません。
-- postgres ユーザにてsooniユーザに対し、create権限を付与する
--
udondb=# grant create on schema public to sooni;
GRANT
udondb=#-- 付与した権限を確認する
--
udondb=> \g
schema | owner | nspacl
--------+-------------------+-----------------------------------------------------------------------------------------
public | pg_database_owner | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner,sooni=C/pg_database_owner}
(1 行)
-- 再度create tableの実行
--
udondb=> create table ex01 (
udondb(> execseq serial
udondb(> ,completion boolean default false
udondb(> ,rid integer
udondb(> ,name character varying (30)
udondb(> ,memo character varying (30)
udondb(> ,created timestamp default current_timestamp
udondb(> ,last_modification timestamp default current_timestamp
udondb(> );
CREATE TABLE
udondb=>
-- 今度は無事作成できました。
なぜこうなったのか
PostgreSQL 15 で、誰でも public スキーマにオブジェクトを作れる権限がなくなりました。public スキーマの所有者も、データベースの所有者（pg_database_owner）に変わっています。
理由はセキュリティです。誰でも public スキーマに関数などを作れると、悪意のあるユーザが、ほかのユーザが使う関数と同じ名前の関数を置いて、すり替えることができてしまいます。
※ 14 まではどのユーザでも作れたこと、変更の理由は、PostgreSQL 15 のリリースノートなどで調べたものです。手元の 14 では確かめていません。
もう一つのやり方：ユーザ名と同じスキーマを作る
GRANT で public スキーマに権限を付ける代わりに、ユーザ名と同じ名前のスキーマを作る方法もあります。search_path の初期値が "$user", public なので、スキーマ名を付けずにテーブルを作ると、ユーザ名と同じスキーマに作られます。
-- postgres ユーザにて CREATE SCHEMA sooni AUTHORIZATION sooni; -- sooni ユーザにて SHOW search_path; -- search_path -- ----------------- -- "$user", public CREATE TABLE ex01 (a int); SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'ex01'; -- schemaname | tablename -- ------------+----------- -- sooni | ex01
（PostgreSQL 15 で確認）ユーザごとにテーブルを分けられるので、Oracle の「ユーザ＝スキーマ」に近い使い方になります。
