PostgreSQLのSEARCH_PATHの落とし穴

この記事は、2024年Advent Calendarの12月6日の記事その1です。

私はターミナルページャーを作っていて、psqlでも便利に使えるようになっています。
そこで、その2の記事も書くことにしました。psqlのページャーにovを！

こちらはそれとは関係なく、PostgreSQLのSEARCH_PATHについて書きます。

SCHEMA（スキーマ）

PostgreSQLには、SCHEMA（スキーマ）という概念があります。SCHEMAは、データベース内に作られるテーブルやビュー、関数、型などのデータベースオブジェクトを含む名前空間です。
テーブルはSCHEMAに属していて、テーブルにアクセスするにはSCHEMA名.テーブル名という形式で指定します。

例）

SELECT * FROM schema_name.table_name;

つまり、階層構造として、データベース名 > スキーマ名 > テーブル名という形になります。標準SQLにならい、その形式でも指定できます。

例）

SELECT * FROM database_name.schema_name.table_name;

ただし、PostgreSQLでは、接続したデータベース以外にはアクセスできません。そのため、実行しているデータベースの間違い防止ぐらいの意味しかありません。通常はデータベース名は省略します。

そして、SCHEMA名も省略できます。省略した場合には、SEARCH_PATHによって解決される...のですが、そう単純でもないなというのが、この記事の主旨です。

SEARCH_PATH

SEARCH_PATHはUnix系のPATHと同じようにSCHEMAを登録しておけば、SEARCH_PATHに登録してあるテーブル等はSCHEMA名を省略してアクセスできる機能です。

SCHEMAは複数作成でき、違うSCHEMAに同じ名前のテーブル等を作成できます。同じ名前のテーブルを作成しておいて、アクセスするときに違うSCHEMAを使用することで、実際には違うテーブルを参照できるというのは意図した動作です。

例えば、PostgreSQLのデフォルトのSEARCH_PATHでは'$user', publicとなっています。'$user'はユーザー名のSCHEMAとなります。
デフォルトではpublicというSCHEMAが作成されていて、ユーザー名のSCHEMAは作成されていません。そのため、SCHEMAを指定しない場合はpublicが使われます。
SEARCH_PATHは、左側が優先です。

ユーザー名のSCHEMAを利用すると、以下のようなことができます。

ユーザー用のテーブルをユーザー名のSCHEMAに作成し、共通のテーブルをpublicのSCHEMAに作成することで、ログインしたユーザー向けの結果を返すことができます。

SELECT * FROM user_name.table_usre LEFT JOIN public.table_public ON user_name.table_user.id = public.table_public.id;

を

SELECT * FROM table_user LEFT JOIN table_public ON table_user.id = table_public.id;

と書けることになります。記述が少なくなるだけでなく同じSQLでユーザー毎の結果を返すことができます。

SEARCH_PATHの設定

そして、SEARCH_PATHは、Unixの環境変数のように設定できます。設定できる場所はいくつかあります。

設定ファイル（postgresql.conf）

全体で設定（デフォルトが設定されていて変更がない場合はコメントになっています）。

postgresql.conf

search_path = '"$user", public'	# schema names

ユーザー毎の設定

ALTER ROLEで設定できます。

ALTER ROLE user_name SET search_path = 'schema_name';

pg_rolesのrolconfigで確認できます。

SELECT rolconfig FROM pg_roles WHERE rolname = 'user_name';
         rolconfig         
---------------------------
 {search_path=schema_name}
(1 row)

関数作成時に設定

ユーザーを切り替えて関数を所有するユーザーの権限で実行できるため。

マニュアルを参照してください。PostgreSQL: Documentation: 16: CREATE FUNCTION

SET SEARCH_PATHで設定

ログインしてからSET SEARCH_PATHで設定できます。

# CREATE SCHEMA test;
CREATE SCHEMA
# SET SEARCH_PATH = test;
SET

SEARCH_PATHの確認

そして最終的に設定していあるSEARCH_PATHは、SHOW SEARCH_PATHで確認できます。

# show SEARCH_PATH;
 search_path 
-------------
 test
(1 row)

最近のPostgreSQLでは、デフォルトのpublic SCHEMAの権限が見直されたり、使用についての説明が詳細にされていたりするのでマニュアルを参照してください。PostgreSQL: Documentation: 16: 5.7. スキーマ#5.9.6. 使用パターン

探すのはSEARCH_PATHだけではない

SEARCH_PATHが設定されたSCHEMAだけが使われるというシンプルな世界であれば良いのですが、実際にはそう単純ではありません。
その要因はpg_catalog SCHEMAとpg_temp SCHEMAです。

pg_catalog SCHEMAは、マニュアルから引用すると

このスキーマにはシステムテーブルと全ての組み込みデータ型、関数および演算子が含まれています。pg_catalogは常に検索パスに含まれています。

となっています。
これが、もしSEARCH_PATHにpg_catalogが含まれていない場合は、組み込み関数がSELECT pg_catalog.count(*) FROM test;のように指定しないと使えなくなってしまいますので、必要な措置です。

さらにややこしいのがpg_temp SCHEMAです。PostgreSQLでは、一時テーブルを作成するときには、通常のテーブルが作成されるSCHEMAとは違って、

CREATE TEMP TABLE test (id int);

とすると、pg_temp SCHEMAにtestテーブルが作成されます。
このままだとpg_temp.testとしてアクセスする必要があるため、自動でpg_temp SCHEMAが検索パスに追加されます。それにより、

SELECT * FROM test;

と書けるようになります。そしてpg_temp SCHEMAは、通常の検索パスより優先されるため、通常のtestテーブルがあってもpg_temp.testが参照されます。これは通常テーブルと同じ名前の一時テーブルを作成しても、一時テーブルが参照されるようにするという使い方をするためだと理解できます。

SEARCH_PATHの優先順位

ということで、SCHEMAを省略した場合はSEARCH_PATHに加えてpg_tempとpg_catalogが探されるということになります。
上で書いたようにSCHEMAが違えば同じ名前のテーブルを作成できるため、SCHEMAを省略した場合は、優先順位が重要になります。

デフォルトでは、pg_temp、pg_catalog、SEARCH_PATHに書いた順（SERCH_PATHのデフォルトは'$user', public）となります。

しかーし、SEARCH_PATHにpg_tempやpg_catalogを加えると優先順位を変えることができ、例えばpublicを優先することができます。

つまり関数のオーバーライドができるということです。

（この機構を悪用できる問題があり対応が必要な場合があります。A Guide to CVE-2018-1058: Protect Your Search Path）

まず通常のSQL関数を作ります。max()は集約関数ですが、今回は単純な文字列を返す関数にしてみます。これはSCHEMAを指定していないため、デフォルトのpublicに作成されます。

# CREATE function max(integer) RETURNS text AS $$
SELECT 'TORA TORA TORA';
$$ LANGUAGE SQL IMMUTABLE;

通常のSEARCH_PATHで実行。「表示されていないため」pg_caatalogが優先されます。

# SHOW SEARCH_PATH; 
   search_path
-----------------
 "$user", public
(1 row)

SELECT max(1);
max 
-----
   1
(1 row)

publicを優先するようにSEARCH_PATHを変更して実行。

# SET search_path = public, pg_catalog;
SET
# SHOW search_path;
    search_path     
--------------------
 public, pg_catalog
(1 row)

# SELECT max(3);
      max       
----------------
 TORA TORA TORA
(1 row)

作成したmax()関数が実行されました。

pg_tempの特殊性

さて、上記で書いたデフォルトの優先順位を思い出して下さい。
pg_temp、pg_catalog、SEARCH_PATHの順です。つまり、pg_tempに同じ名前の関数を作成すると、pg_tempの関数が実行されることになりませんか？

# CREATE function pg_temp.max(integer) RETURNS text AS $$
SELECT 'Give me a Shake';
$$ LANGUAGE SQL IMMUTABLE;

SEARCH_PATHをデフォルトに戻して実行するとpg_temp、pg_catalog、SEARCH_PATHの順のはず...

# SHOW search_path ;
   search_path   
-----------------
 "$user", public
(1 row)

SELECT max(1);
max 
-----
   1
(1 row)

ですが、安心して下さい。なりません。

PostgreSQLのマニュアル から引用すると

同様に、現在のセッションの一時テーブルスキーマpg_temp_nnnも、存在すれば常に検索されます。 これはpg_tempという別名を使用してパスに明示的に列挙させることができます。 パスに列挙されていない場合、最初に（pg_catalogよりも前であっても）検索されます。 しかし、一時スキーマはリレーション（テーブル、ビュー、シーケンスなど）とデータ型名に対してのみ検索されます。 関数や演算子名に対してはまったく検索されません。

となっていて、pg_tempに関数を作成してもSCHEMAを指定しない限り実行されないということです。

SHOW SEARCH_PATHでは気づかない

関数や演算子名に関しては、まあ良いことにしてもSEARCH_PATHの優先順位はややこしいです。
さらに、SEARCH_PATHのセットにミスっても誰も指摘してくれません。環境変数と同じで、そういうものなのですが。

# SET search_path = 'public, pg_catalog, pg_temp'
SET
# SHOW search_path;
          search_path          
-------------------------------
 "public, pg_catalog, pg_temp"
(1 row)

と一見問題になさそうですが、実際にはSCHEMAの設定がされていないので、pg_temp, pg_catalogの順で検索されてしまいます。

そのため、SEARCH_PATHを変更するときは、身を清め、白装束に着替え、一点に集中してミスの無いようにセットする必要があります（ウソです）。

真のSEARCH_PATHを確認

SEARCH_PATHがほんとのところはどうなっているか確認するには、current_schemas()関数を使います。

SELECT current_schemas(true);
        current_schemas        
-------------------------------
 {pg_temp_1,pg_catalog}
(1 row)

となって、search_pathに'public, pg_catalog, pg_temp'という文字列で渡してしまったことに気づけます（実際にはSET search_patth = public, pg_catalog, pg_tempと書く必要があった）。

pg_temp_1は、実際のスキーマ名です。pg_tempはセッションごとに異なりますが、サーバー側で名前が一意になるようにされるため、pg_temp_1のような名前がつけられ、pg_tempとしてもアクセスできるようになります。

ということで、ほんとのところはcurrent_schemas(true)関数を使って確認してから、リレーション（テーブル、ビュー、シーケンス）は探す、関数や演算子名はpg_temp_*を除外して探す...ということになります。

メンテナンスコマンドのSERACH_PATHが変更

pg_tempの関数が実行されないといっても、テーブルをオーバーライドされると想定していた動作と違って困る場合があります。
PostgreSQLの17では、メンテナンスコマンド中はSEARCH_PATHが変更されるようになりました。

17.0 Release Notes

SERACH_PATHのところは、セキュリティ問題もあり、意図しない動作の要因にもなるため、今後もまた変わるかもしれないですね。

まとめ

入口はシンプルそうに見えて、中はシンプルではない世界でした。