PostgreSQLのデフォルトスキーマをJDBC接続で指定する方法

このサイトはアフィリエイト広告(Amazonアソシエイト含む)を掲載しています。
JDBC接続URLのcurrentSchemaで、スキーマ名を省略したSQLの参照先をapp_schemaに指定する図

PostgreSQLのJDBC接続でデフォルトスキーマを指定するには接続URLへcurrentSchemaを追加します。例えばこんな感じです。

jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema

この設定はSELECT * FROM test; のようにスキーマ名を省略したSQLへ適用されます。public.testのようにスキーマ名を直接書いているSQLは切り替わりません。既存システムのSQLを修正せず接続先のスキーマだけを変更したい場合に使えます。


設定を試すためのPostgreSQL環境が必要ならDocker Composeで起動し、psqlやGUIツールから接続する手順を確認できます。

目次

なぜスキーマを一括指定したいのか

PostgreSQLでスキーマ名を省略するとsearch_pathに指定された順番でテーブルが検索されます。デフォルトは"$user", publicです。接続ユーザーと同名のスキーマが存在しない環境では通常はpublic内のテーブルが参照されます。

SQL
SELECT * FROM test;

この条件でpublic.testが存在する場合、上のSQLは次のテーブルを参照します。

SQL
SELECT * FROM public.test;

リプレース時によくある要件

実務でのリプレース案件だとこんな要件が出てきますよね。

  • デフォルトのpublicスキーマではなく専用スキーマ(例:app_schema)を用意したい。
  • テーブルやビューの構成は既存と全く同じにする。
  • 既存のSQL文は変更せずにそのまま流用したい。

SQLファイルが数百・数千あるケースで手動スキーマ名追記は現実的ではありませんし、修正漏れのリスクも高いですよね。

そこで使えるのがJDBC接続時のスキーマ指定です。

JDBC接続文字列で解決する方法

currentSchemaパラメータとは

PostgreSQL公式のJDBCドライバーにはcurrentSchemaというパラメータが用意されています。

currentSchema (String) Default null Specify the schema (or several schema separated by commas) to be set in the search-path. This schema will be used to resolve unqualified object names used in statements over this connection.

Initializing the Driver | pgJDBC

要約すると「指定したスキーマが、スキーマ名を省略したオブジェクト名の解決に使われる」ということです。

接続時にcurrentSchema=app_schemaを指定すると、その接続ではapp_schemaを検索パスに設定します。スキーマ名を省略したSQLでapp_schema内のテーブルやビューを参照できます。

currentSchemaを指定しない場合は接続ユーザーやデータベースなどに設定されたsearch_pathが使われます。

適用されるケース・されないケース

公式ドキュメントにある非修飾オブジェクト名の解決という点が重要です。SQL文でスキーマ名をハードコーディングしている場合はcurrentSchemaの指定に関係なく、そちらが優先されます。

ケース説明適用
スキーマ名なしのSQLSELECT * FROM test;✅ 適用される
スキーマ名ありのSQL(public)SELECT * FROM public.test;❌ 適用されない
スキーマ名ありのSQL(別スキーマ)SELECT * FROM app_schema.test;❌ 適用されない

設定方法(jdbc.diconの例)

S2JTAやSeasar2を利用している場合jdbc.diconなどの接続設定ファイルを以下のように変更します。

jdbc.dicon – 変更前
jdbc:postgresql://localhost:5432/mydb
jdbc.dicon – 変更後
jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema

接続URLにすでにパラメータがある場合は&でcurrentSchemaを追加します。

&で追加
jdbc:postgresql://localhost:5432/mydb?sslmode=require¤tSchema=app_schema

この例のsslmode=requireは既存パラメータの一例です。現在のSSL設定はそのまま使ってください。jdbc.diconのようなXMLファイルの通常のテキスト部分へ書く場合、区切りの&は&と記述します。

&と書く場合
jdbc:postgresql://localhost:5432/mydb?sslmode=require¤tSchema=app_schema

動作イメージ

設定後にアプリケーションから下記のSQLを発行すると、

SQL
SELECT * FROM test;

PostgreSQL内部では以下のように解釈されます。

SQL
SELECT * FROM app_schema.test;

既存のSQL文を一切変更せずにスキーマ切り替えが完了します。

currentSchemaとsearch_pathの違い

currentSchemaはJDBC接続向けの設定です。psqlで一時的にスキーマを切り替えたい場合や接続ユーザーごとに同じ設定を継続して使いたい場合はSET search_pathやALTER ROLEを使います。

どの方法もスキーマ名を省略したテーブルやビューを検索する順番を指定するものです。設定が適用される範囲とタイミングが異なります。

方法適用範囲設定例向いているケース
JDBCのcurrentSchemaJDBC接続ごと?currentSchema=app_schemaアプリケーション単位で切り替えたい
SET search_path現在のセッションSET search_path TO app_schema, public;psqlでの一時確認や処理単位で切り替えたい
ALTER ROLE ... SET対象ユーザーの新しい接続ALTER ROLE app_user SET search_path TO app_schema, public;ユーザー単位で継続して適用したい
ALTER DATABASE ... SET対象データベースへの新しい接続ALTER DATABASE mydb SET search_path TO app_schema, public;データベース単位で継続して適用したい

JDBC接続だけ切り替えるならcurrentSchema

JavaやSpring BootなどJDBCを使うアプリケーションだけ参照先を変えたい場合はcurrentSchemaが分かりやすいです。

JDBC設定
jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema

この設定はJDBC接続を作成するときに検索パスを指定します。PostgreSQL全体やpsqlから作成した別の接続には影響しません。

アプリケーションごとに接続URLを分けられるため、既存SQLを変更せず特定のアプリだけ参照スキーマを切り替えたい場合に向いています。

psqlで一時的に切り替えるならSET search_path

psqlで接続した後一時的に参照スキーマを変更する場合はSET search_pathを実行します。

SQL
SET search_path TO app_schema, public;

この設定はSQLを実行した現在のセッションにだけ適用されます。psqlを終了して接続し直すと通常は元の設定へ戻ります。

設定結果は次のSQLで確認できます。

SQL
SHOW search_path;
SELECT current_schema();
SELECT current_schemas(false);

SHOW search_pathは設定された検索パス、current_schema()はその中で最初の有効なスキーマを返します。current_schemas(false)では存在しないスキーマや USAGE 権限のないスキーマを除いた一覧を確認できます。

current_schema()だけでは特定のテーブルがどのスキーマから見つかったかまでは分かりません。後ろのスキーマにあるテーブルが参照される場合もあります。

ユーザー単位で継続するならALTER ROLE

特定のデータベースユーザーが接続したとき、毎回同じ検索パスを使いたい場合はALTER ROLEで設定します。

SQL
ALTER ROLE app_user
SET search_path TO app_schema, public;

設定後app_userが新しく接続するとapp_schema、publicの順でオブジェクトを検索します。

この設定は新しい接続から適用されます。接続済みのセッションには反映されないため、設定後に再接続してください。接続プールを使っている場合はプール内の既存接続も作り直します。

同じユーザーを複数のアプリケーションで共有している場合、すべてに設定が影響します。特定のアプリケーションだけ切り替えたい場合はALTER ROLEよりcurrentSchemaの方が安全です。

データベース単位で継続するならALTER DATABASE

対象データベースへ接続するユーザーに共通の検索パスを設定したい場合はALTER DATABASEを使います。

SQL
ALTER DATABASE mydb
SET search_path TO app_schema, public;

この設定も新しく作成された接続から適用されます。データベースを利用する複数のユーザーへ影響するため、アプリケーションごとに参照先を分けたい環境には向きません。

アプリとユーザーの設定が重なる場合

ユーザーやデータベースにそれぞれsearch_pathを設定している場合、優先順位は次のとおりです。上にある設定が優先されます。

  • ALTER ROLE app_user IN DATABASE mydb SET search_path ...
  • ALTER ROLE app_user SET search_path ...
  • ALTER DATABASE mydb SET search_path ...

特定のユーザーとデータベースの組み合わせに限定する場合は次のように設定します。app_userとmydbは対象のユーザー名とデータベース名へ置き換えてください。

SQL
ALTER ROLE app_user IN DATABASE mydb
SET search_path TO app_schema, public;

JDBC接続でcurrentSchemaを明示した場合は接続時に指定した値が使われます。その後、同じ接続でSET search_pathを実行すると検索パスはさらに変更されます。

接続プールやフレームワークが接続後に設定を変更する場合もあるため、最終的な値はアプリケーションが実際に使う接続で確認してください。

SQL
SHOW search_path;

複数スキーマを検索パスに含める場合

currentSchemaパラメータはカンマ区切りで複数のスキーマを指定できます。

jdbc.dicon
jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema,public

この設定ではまずapp_schemaを検索し、見つからなければpublicスキーマを参照します。公式のサンプルデータや拡張機能などをpublicに残しておきたい場合に便利ですね。

注意点とトラブルシューティング

スキーマ名の大文字・小文字について

大文字を含む引用符付きスキーマ名は接続URL上の指定方法やURLエンコードで詰まりやすいため、原則として小文字のスキーマ名を推奨です。既存環境で大文字を使っている場合はSHOW search_pathとcurrent_schema()で接続後の値を確認してください。

接続プールを使用している場合

接続URLを変更しても接続プール内に残っている既存接続には新しい設定が反映されません。変更後のURLを読み込ませたうえで既存の物理接続を作り直してください。

アプリケーションの再起動はその方法の一つです。接続プールの再作成などで対応できる場合もあるため、利用している製品の手順に従ってください。

権限エラーが出る場合

スキーマとテーブルの権限は別々に必要です。スキーマ名を明示したSQLではUSAGE権限がないと次のようなエラーが出ます。

SQL
ERROR: permission denied for schema app_schema

スキーマ名を省略したSQLではUSAGE権限のないスキーマが検索対象から外れます。そのため、テーブルが存在していてもrelation "test" does not existになる場合があります。

権限を付与する場合は対象オブジェクトの所有者など、付与権限を持つユーザーで実行してください。次はapp_userにapp_schema.testの参照を許可する例です。ユーザー名・スキーマ名・テーブル名は実際の名前へ置き換えます。

SQL
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT ON TABLE app_schema.test TO app_user;

更新処理が必要な場合は実際に使うテーブルへINSERT、UPDATE、DELETEなどの権限を追加します。

ON ALL TABLES IN SCHEMAを使うとその時点で存在するスキーマ内の全テーブルなどが対象になります。後から作成するテーブルには自動適用されません。また、serial列などで使うシーケンスの権限は別途必要になる場合があります。


ロールやシーケンス、リレーションなど設定やエラーの説明に出てくる用語の意味はPostgreSQLの用語・機能一覧で確認できます。

迷いやすいポイント

Spring BootなどのSpringアプリでも同じ方法で使える?

はい、使えます。application.propertiesやapplication.ymlのJDBC URL部分に同様にcurrentSchemaを追加するだけです。

application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/mydb?currentSchema=app_schema

currentSchemaとsearch_pathは同じ?

currentSchemaはJDBC接続時にPostgreSQLのsearch_pathを指定するための接続パラメータです。

currentSchemaは接続時にsearch_pathを設定します。接続後にSQLで変更する場合はSET search_pathを使います。

一時設定、ユーザー単位、データベース単位の違いは「currentSchemaとsearch_pathの違い」で解説しています。

スキーマが存在しない場合はどうなる?

存在しないスキーマは検索パスから無視されます。currentSchemaに指定してもスキーマは自動作成されません。

後ろにpublicなどを指定している場合はそこにある同名テーブルが参照されることがあります。検索対象のどのスキーマにもテーブルが見つからない場合は、SQL実行時にrelation "xxx" does not existが発生します。

確認には前述のSHOW search_pathとcurrent_schemas(false)を使います。JDBCの設定を確認するときはアプリケーションと同じ接続で実行してください。別途起動したpsqlではそのJDBC接続の設定を確認できません。

currentSchemaは接続後も永続化される?

currentSchemaはそのJDBC接続に適用されます。PostgreSQL全体の設定を書き換えるものではありません。ユーザー単位やデータベース単位で新しい接続へ継続適用したい場合はALTER ROLE ... SET search_pathまたはALTER DATABASE ... SET search_pathを検討します。

PostgreSQLのSQLや接続設定、Dockerでの開発環境構築に進む場合はデータベースまとめから目的別に探せます。

まとめ

  • JDBC接続URLのcurrentSchemaでスキーマ名を省略したSQLの検索先を指定できるよ。
  • スキーマ名を省略していないSQL(ハードコーディングあり)には適用されないので注意だよ。
  • カンマ区切りで複数スキーマの検索パスを設定できるよ。
  • 接続ユーザーへのスキーマUSAGE権限とテーブルアクセス権限を忘れずに付与してね。
  • 接続プール環境では、設定変更後に既存の接続を作り直してね。
  • URLをコピーしました!
目次