アプリ開発ときどきアウトドア

主にJavaを使ったアプリ開発やトラブルシューティング等のノウハウ、キャンプや登山の紹介や体験談など。

1. システムエンジニアリング Linux postgresql トラブルシューティング 基盤技術

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

投稿日:2018年6月11日 更新日:


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

事象

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

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

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つのスキーマに配置する必要がある。

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

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のオブジェクトを配置する必要があります。

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

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

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 行)



(adsbygoogle = window.adsbygoogle || []).push({});


(adsbygoogle = window.adsbygoogle || []).push({});

-1. システムエンジニアリング, Linux, postgresql, トラブルシューティング, 基盤技術

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

開発・検証用のFTPサーバ(IIS)を構築

FTPで外部連携するためのプログラムを開発する際に、接続先のFTPサーバの準備に困る場合があります。 ここでは、Widnwos10上に開発・検証用のFTPサーバを構築するための手順を説明します。 「I …

ASP.NET Core: 変更ページを実行環境に反映

ASP.Net Core(3.0)の開発で、ページ(cshtml)を編集しながらページデザインを確認したい。 既定ではページを変更しても実行環境に反映れずサーバの再起動が必要となり開発効率が悪い。 サ …

slf4jとlog4j2を使たデバッグログの出力方法

Webアプリやスタンドアロンアプリの開発でデバッグログやトレースログを出したい場合があります。 とりあえず、ロガーのログレベルをdebugやtraceに下げればいいや、と設定してもログが出力されない場 …

WebサイトのSSL化

次のようなGoogleウェブマスター向け公式ブログでの推奨や、リモートか管理の安全性向上の向上のためにSSL(TLS)を有効にする。 概要 Googleさんの考え 保護されたウェブの普及を目指して(2 …

SLF4Jの仕組みと使用方法

何気に使用しているSLF4Jの仕様やその仕組みを整理したいと思います。 前提 元ネタはSLF4J Manualサイトです。 2019年1月での安定版の最新である1.7.25を使用した例を記載します。 …

プロフィール ゆっきーです。
都内でシステムエンジニアをやっています。
もっと詳細を見る