PostgreSQL10で暗号化関数を使用するため拡張(pgcrypto)をインストールした際の出来事を記載します。
事象
pgcryptoの拡張をインストールしたり、インストールの確認を行うと、その拡張は特定のスキーマに存在しています。pgcryptの拡張は、スキーマ個別にインストール可だと認識していました。
開発用DBが構築され、開発ユーザ毎にスキーマを分ける運用になりました。
例えば、test1ユーザはtest1スキーマを使用、test2ユーザはtest2スキーマを使用する、のように。
test1のスキーマを作成し、pgcrypto拡張のインストールは成功します。
test2のスキーマを作成し、コマンドを実行してもエラーになります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | testdb=# \dn スキーマ一覧 名前 | 所有者 --------+---------- public | postgres (1 行) testdb=# create schema test1; CREATE SCHEMA testdb=# create extension pgcrypto with schema test1; CREATE EXTENSION testdb=# create schema test2; CREATE SCHEMA testdb=# create extension pgcrypto with schema test2; ERROR: extension "pgcrypto" already exists |
原因
create extensionのリファレンスを読むと次のように記載されています。
拡張自体が任意のスキーマの中にあるとみなされていないことを思い出してください。 拡張は修飾がない名前を持ちますので、データベース全体で一意でなければなりません。 しかし拡張に属するオブジェクトはスキーマの中に置くことができます。
拡張の名前はデータベースで一意である必要がある。拡張に紐づく関数等のオブジェクトはスキーマに配置される。結果として、同一データベース上の複数のスキーマに拡張のオブジェクトは配置できないので、どこか１つのスキーマに配置する必要がある。
念のため次のように確認したが、書いてある通りであった。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | testdb=# \dn スキーマ一覧 名前 | 所有者 --------+---------- public | postgres test1 | postgres test2 | postgres (3 行) testdb=# \dx インストール済みの拡張一覧 名前 | バージョン | スキーマ | 説明 ---------+------------+------------+------------------------------ plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (1 行) testdb=# create extension pgcrypto with schema test1; CREATE EXTENSION testdb=# \dx インストール済みの拡張一覧 名前 | バージョン | スキーマ | 説明 ----------+------------+------------+------------------------------ pgcrypto | 1.3 | test1 | cryptographic functions plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 行) testdb=# set search_path=test1; SET testdb=# select pgp_sym_encrypt('plain', 'pass'); pgp_sym_encrypt -------------------------------------------------------------------------------------------------------------------------------------------------- \xc30d04070302741e746491d6289266d23601974c81d280667c69b3e2a21dd1dc06b4318889ecd6c8b6a3ab7384a637446486b24e6d90b6ca1410b7316ff7fd564c844ad6b8044d (1 行) testdb=# create extension pgcrypto with schema test2; ERROR: extension "pgcrypto" already exists |
対応
同じような考えの人がいたので、参考にさせてもらいました。
どこか代表となるスキーマにpgcryptのオブジェクトを配置する必要があります。
- 案１：既定のpublicスキーマに配置する
- 案２：拡張を配置するためのスキーマを別途作成(例えば、extensions)
各スキーマ用のユーザが暗号化関数を使用する場合、自身のスキーマにpgcrypto拡張がないため、public.pgp_sym_encrypt()等のように修飾子を指定する必要があります。これは面倒なので、次のようにsearch_pathに元々のスキーマと拡張を配置したスキーマを指定して解決できます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | testdb=> \dn スキーマ一覧 名前 | 所有者 --------+---------- public | postgres test1 | postgres test2 | postgres (3 行) testdb=> \dx インストール済みの拡張一覧 名前 | バージョン | スキーマ | 説明 ----------+------------+------------+------------------------------ pgcrypto | 1.3 | public | cryptographic functions plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 行) testdb=> set search_path=test1; SET testdb=> select pgp_sym_encrypt('plain', 'pass'); ERROR: function pgp_sym_encrypt(unknown, unknown) does not exist 行 1: select pgp_sym_encrypt('plain', 'pass'); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. testdb=> select public.pgp_sym_encrypt('plain', 'pass'); pgp_sym_encrypt -------------------------------------------------------------------------------------------------------------------------------------------------- \xc30d04070302fa3ca57d19f2581266d23601dbb205979863f4086a4b2aa8062871a18b119f557c79286869f4dd6e60cc99b250f64f53aff21f6ab00735134e9b881606bf92090a (1 行) testdb=> set search_path=test1,public; SET testdb=> select pgp_sym_encrypt('plain', 'pass'); pgp_sym_encrypt -------------------------------------------------------------------------------------------------------------------------------------------------- \xc30d04070302f4251dacf3ee71ef66d2360124e062967eb5f948925fe9d18baeef2bf2de56db6d824c6b08fa9f5b04cbc79fb0a8e5c9c797f1abfc22cf6564aa0b8a0d9b38c230 (1 行) |