One IT Thing

IT業界を楽しむ為の学習系雑記

PostgreSQL security

pgcryptoの有効化と共通鍵暗号の動作確認

投稿日:2019年8月9日 更新日:

PostgreSQLで「pgcrypto」拡張機能を有効にすると、PostgreSQLに保存するデータを暗号化出来るようになります。

INSERTする際、共通鍵暗号はpgp_sym_encrypt関数、公開鍵暗号はpgp_pub_encrypt関数を使って、カラムデータ単位で暗号化します。

他のRDBにこれらの関数は無いのでSQLにPostgreSQLの方言が入ることになりますが、SQL標準仕様(SQL99)に暗号化仕様は無いので、どのRDBMSを使った暗号化でも結局は方言になります。致し方ないですね。

今回はPostgreSQL11にpgcryptoを入れ、AES256で共通鍵暗号化したデータをINSERT、復号してSELECTしてみます。

検証環境

  • CentOS 7.6
  • PostgreSQL 11.4

以下の記事でPostgreSQL11.4のインストール、ユーザ作成を行っている状態です。

PostgreSQLのcontribパッケージをインストール

pgcryptoが内包されるcontribパッケージがOSに入っているか確認します。

[root@spock ~]# rpm -qa | grep postgres
postgresql11-11.4-1PGDG.rhel7.x86_64
postgresql11-libs-11.4-1PGDG.rhel7.x86_64
postgresql11-server-11.4-1PGDG.rhel7.x86_64

入っていないのでインストールします。

[root@spock ~]# yum install postgresql11-contrib

    (snip)

Running transaction
  インストール中          : postgresql11-contrib-11.4-1PGDG.rhel7.x86_64                                                                1/1
  検証中                  : postgresql11-contrib-11.4-1PGDG.rhel7.x86_64                                                                1/1

インストール:
  postgresql11-contrib.x86_64 0:11.4-1PGDG.rhel7

postgresql11-contribが入りました。インストール完了です。

pgcryptoを有効化

PostgreSQLの拡張パッケージは有効化しないと使えません。
postgresユーザで有効化します。
現在有効かどうか確認するにはpsqlから\dxを実行します。

[root@spock ~]# su - postgres
-bash-4.2$ /usr/pgsql-11/bin/psql
psql (11.4)
"help" でヘルプを表示します。

// 拡張パッケージを確認
postgres=# \dx
                    インストール済みの拡張一覧
  名前   | バージョン |  スキーマ  |             説明
---------+------------+------------+------------------------------
 plpgsql | 1.0        | pg_catalog | PL/pgSQL procedural language
(1 行)

// pgcrypto拡張を有効化!
postgres=# create extension pgcrypto;
CREATE EXTENSION
postgres=# \dx
                    インストール済みの拡張一覧
   名前   | バージョン |  スキーマ  |             説明
----------+------------+------------+------------------------------
 pgcrypto | 1.3        | public     | cryptographic functions
 plpgsql  | 1.0        | pg_catalog | PL/pgSQL procedural language
(2 行)

pgcrypto 1.3がpublicスキーマで使えるようになりました。

試し打ちしてみる

共通鍵で暗号化するにはpgp_sym_encrypt関数を使います。詳細は公式サイトを参照。

“暗号化する文字列”という文字列を、

  • AES256アルゴリズムで、
  • “password”というパスワードを使い、
  • zip圧縮しながら

暗号化してみます。

postgres=# select pgp_sym_encrypt('暗号化する文字列', 'password', 'compress-algo=1, cipher-algo=aes256');
                                                                                            pgp_sym_encrypt

--------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
 \xc30d040903021e01465cfe5516d265d251017fa6946f593416046162b481271ee3302448810b8cf6d41adf5ffdc84b4a54c791ce934c01a14d3cd95115e1b7dffc066d2ba
26ea4dc696c5e8dfeff961cee5fe5bbff1bfeb485f8c3fdc7a12eb03072
(1 行)

正常に暗号化されているのか、復号して確かめます。
共通鍵暗号の復号にはpgp_sym_decrypt関数を使用します。

postgres=# select pgp_sym_decrypt(pgp_sym_encrypt('暗号化する文字列', 'password', 'compress-algo=1, cipher-algo=aes256'), 'password');
 pgp_sym_decrypt
------------------
 暗号化する文字列
(1 行)

“暗号化する文字列”に戻りました。大丈夫そうです。

テーブルに保存する

PostgreSQLのBLOB型はbytea型です。

postgres=# create table crypt_test(id serial, crypted bytea);
CREATE TABLE

cryptedカラム値を暗号化しながら2レコード追加。

postgres=# insert into crypt_test(crypted) values (pgp_sym_encrypt('暗号化する文字列1', 'password'));
INSERT 0 1
postgres=# insert into crypt_test(crypted) values (pgp_sym_encrypt('暗号化する文字列1', 'password'));
INSERT 0 1
postgres=# select * from crypt_test;
 id |                                                                                           crypted

----+---------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
  1 | \xc30d04070302048da71fd7fb1ffb65d24c01e01b1c1268f8329757ee270b603037b8651de687c104312903531cd9f3bd01c950279a53926a5c6107b679a76c202131
e9fcd4cc94b8676b11e3febb970fd32417bfd519670648e3ebe3b4
  2 | \xc30d0407030206d9bb3ea974627963d24c01d9ccab4142eff516ba99aaa054e720adbfe593161c80c3ac4247d55f0cfa3296c00c2236b82a4e6b603fd0c30ba5013d
7a7f8e01b037cced1fd779094982f5c7000844194487629f9ac203
(2 行)

復号、出来ましたね。

postgres=# select id, pgp_sym_decrypt(crypted, 'password') from crypt_test where id = 1;
 id |  pgp_sym_decrypt
----+--------------------
  1 | 暗号化する文字列1
(1 行)

postgres=# select id, pgp_sym_decrypt(crypted, 'password') from crypt_test where id = 2;
 id |  pgp_sym_decrypt
----+--------------------
  2 | 暗号化する文字列1
(1 行)

同じデータを同じアルゴリズムで暗号化してるのにどうして結果が違うの?

先程、”暗号化する文字列1”という文字列を2件暗号化してINSERTしましたが暗号化した結果が異なっていました。

  1 | \xc30d04070302048da71fd7fb1ffb65d24c01e01b1c1268f8329757ee270b603037b8651de687c104312903531cd9f3bd01c950279a53926a5c6107b679a76c202131
e9fcd4cc94b8676b11e3febb970fd32417bfd519670648e3ebe3b4
  2 | \xc30d0407030206d9bb3ea974627963d24c01d9ccab4142eff516ba99aaa054e720adbfe593161c80c3ac4247d55f0cfa3296c00c2236b82a4e6b603fd0c30ba5013d
7a7f8e01b037cced1fd779094982f5c7000844194487629f9ac203

同じアルゴリズム、同じパスワードなのに何故なんでしょうか。

AESはCBCというモードで暗号化します。CBCは暗号化するデータを固定長ブロックに分け、AES暗号した結果を次のブロックとXORし、XOR結果をAES暗号し・・・を繰り返していきます。

となると、一番最初のブロックは前の暗号化結果が無いのでXORが取れず暗号化出来ません。そこでダミーの先頭データを提供する「IV(Initial Vector)」の登場です。

IVをランダムにすることで最初のXOR値が毎回変わります。この結果この後のXOR値も全て異なることになり、毎回同じデータを同じアルゴリズムで暗号化しても違う結果になる、という仕組みになっています。

IVが毎回同じ値であれば暗号結果も毎回同じになります。pgcryptoは自動でランダムな値を設定してくれますが、自分でIVを設定するようなライブラリの場合はランダムにしないとIVの恩恵が受けられないので毎回変えるようにしましょう。

SQLでパスワード丸見えなんだけど

共通鍵暗号はパスワードがバレたら終わりなのでSQL直書きはNGですね。パスワードを変える時も大変になります。

PostgreSQLの前段にいるJavaやPython等のプログラムからうまいこと渡すか、postgresql.confで外部パラメータとして外出しにするのが一般的かと思います。

/var/lib/pgsql/11/data/postgresql.confの末尾にパスワードを追加して外出しにしてみます。

    (snip)

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

# 追加
aes.password='password'

rootユーザでpostgresql.conf設定ファイルをポスグレに再読み込みさせます。

# systemctl reload postgresql-11

postgresql.confに設定したキーバリュー値はcurrent_setting()関数で持ってこれます。

postgres=# select current_setting('aes.password')
;
 current_setting
-----------------
 password
(1 行)

後はこれをSQLに流用します。

postgres=# select pgp_sym_decrypt(pgp_sym_encrypt('暗号化する文字列', current_setting('aes.password'), 'compress-algo=1, cipher-algo=aes256'), current_setting('aes.password'));
 pgp_sym_decrypt
------------------
 暗号化する文字列
(1 行)

SQLからパスワードが「password」であることは分からなくなりました。

まとめ

PostgreSQLに暗号化されたデータを保存、復号して表示出来るようになりました。

ただ共通鍵よりも公開鍵の方が鍵を物理的に分散出来、利用シーンも多めだと思いますのでまた次回公開鍵暗号、アプリからの連携をやっていきたいと思います。

-PostgreSQL, security
-,

執筆者:

関連記事

主要ブラウザに保存させたパスワードの確認方法を比較してみる(Firefoxをメインブラウザにしない理由)

アクセスしたサイトのパスワードをブラウザに覚えてもらったけどなんて入れたっけ? なんてこと結構あるんじゃないかと思います。 各種ブラウザとも覚えさせたパスワードは後から確認が出来るのでそれぞれの方法を …

ER図自動生成ツール「SchemaSpy」でdvdrentalを可視化

SchemaSpyを使うとDBからデータを吸い上げてテーブルの相関をビジュアライズしてくれます。 本来ER図からDDLを生成出来るようにしておくべきで、出来れば使わない方がいいチート系のツールですがC …

pgcryptoで公開鍵暗号の動作確認

共通鍵暗号で暗号化されたデータはパスワードが漏洩すると復号される危険が高まるのに対し、公開鍵暗号で暗号化されたデータは秘密鍵とパスワードの二つが漏洩しないと復号できません。 APサーバとDBサーバ通信 …

開発用のRSA鍵ペア(バイナリ、テキスト)をopensslで作っておく

目次1 環境2 秘密鍵3 公開鍵4 まとめ 環境 CentOS7.6に付属のopenssl 1.0.2k。 $ cat /etc/redhat-release CentOS Linux release …

開発用PostgreSQLをCentOSにインストールしてSQLを流すまで

開発や勉強に使う為のPostgreSQLをCentOSにインストールします。 インストールから基本操作を体験して、0からデータベースを構築する経験を積んでおきます。 目次1 環境2 インストール手順を …


shingo nakanishi。東京で消耗中の職歴20年越え中年ITエンジニアです。「生涯現役プログラマを楽しむ」ことができる働き方探しをライフワークにしています。

19歳(1996年)から書き始めた個人日記が5,000日を超え、残りの人生は発信をして行きたいと思い、令和元日からこのサイトを開始しました。勉強と試行錯誤をしながら、自分が経験したIT関連情報を投稿しています。