postgresqlでの拡張のインストール

PostgreSQL10で暗号化関数を使用するため拡張(pgcrypto)をインストールした際の出来事を記載します。

事象

pgcryptoの拡張をインストールしたり、インストールの確認を行うと、その拡張は特定のスキーマに存在しています。pgcryptの拡張は、スキーマ個別にインストール可だと認識していました。

開発用DBが構築され、開発ユーザ毎にスキーマを分ける運用になりました。
例えば、test1ユーザはtest1スキーマを使用、test2ユーザはtest2スキーマを使用する、のように。
test1のスキーマを作成し、pgcrypto拡張のインストールは成功します。
test2のスキーマを作成し、コマンドを実行してもエラーになります。

原因

create extensionのリファレンスを読むと次のように記載されています。

拡張自体が任意のスキーマの中にあるとみなされていないことを思い出してください。 拡張は修飾がない名前を持ちますので、データベース全体で一意でなければなりません。 しかし拡張に属するオブジェクトはスキーマの中に置くことができます。

拡張の名前はデータベースで一意である必要がある。拡張に紐づく関数等のオブジェクトはスキーマに配置される。結果として、同一データベース上の複数のスキーマに拡張のオブジェクトは配置できないので、どこか1つのスキーマに配置する必要がある。

念のため次のように確認したが、書いてある通りであった。

対応

同じような考えの人がいたので、参考にさせてもらいました。
どこか代表となるスキーマにpgcryptのオブジェクトを配置する必要があります。

  • 案1:既定のpublicスキーマに配置する
  • 案2:拡張を配置するためのスキーマを別途作成(例えば、extensions)

各スキーマ用のユーザが暗号化関数を使用する場合、自身のスキーマにpgcrypto拡張がないため、public.pgp_sym_encrypt()等のように修飾子を指定する必要があります。これは面倒なので、次のようにsearch_pathに元々のスキーマと拡張を配置したスキーマを指定して解決できます。