Computer広告を含む記事です

PostgreSQLで入力するデータを暗号化する

管理しているIDやパスワードが増えてきて、把握しきれなくなってきたので、全部データベースで管理することにします。ただ、パスワードをそのまま保存するのは危険なので、pgcryptoを使って入力するデータを暗号化します。

pgcryptoでは、いくつか暗号化するための関数がありますが、今回はPGP暗号化関数を使います。

pgcryptoの導入

pgcryptoをデータベースに導入します。

CREATE EXTENSION PGCRYPTO;

テーブルの作成

暗号化するカラムは、bytea型にしておく必要があります。今回は、login_passのカラムだけ暗号化します。

カラム名タイプ
nametext
login_idtext
login_passbytea
CREATE TABLE sample (
    name text,
    login_id text,
    login_pass bytea
);

データの暗号化

データの暗号化には、pgp_sym_encrypt()を使います。

pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea

カラム名入力するデータ
namedattesar.com
login_iddattesar
login_passdattesarpass

今回、暗号化するためのパスワードは、test_passに設定します。

INSERT INTO sample VALUES (
    'dattesar.com',
    'dattesar',
    pgp_sym_encrypt('dattesarpass', 'test_pass')
);

SELECTでどのように出力されるのか試してみます。

SELECT * FROM sample;

     name     | login_id |                                                                           login_pass                                                                           
--------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------
 dattesar.com | dattesar | \xc30d040703021d84a8be07182f1778d23d011e9c5d724da7e5aff87d0a5c0ca1d2a52278a7de02e0a305133fab7b4b1857ac1fb450de97b9ab4cc31cd89c19fe3bf9007a4a9b27b725dcd34a6e38

オプション

pgp_sym_encrypt()のデフォルトの暗号アルゴリズムは、aes128です。オプションを利用することで、cipher-algo (暗号アルゴリズム) と compress-algo (圧縮アルゴリズム) を変更することができます。

cipher-algo: bf, aes128 (デフォルト), aes192, aes256, 3des, cast5
compress-algo: 0 (非圧縮), 1 (ZIP圧縮), 2 (zlib圧縮)

オプションの利用例

pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256')

pgp_sym_encrypt(data, psw, 'cipher-algo=aes256')

データの復号

データの復号には、pgp_sym_decrypt()を使います。

pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text

SELECT
    name,
    login_id,
    pgp_sym_decrypt(login_pass,'test_pass')
FROM
    sample;

     name     | login_id | pgp_sym_decrypt 
--------------+----------+-----------------
 dattesar.com | dattesar | dattesarpass

暗号化と復号のパスワード管理

暗号化と復号をするためのパスワード（今回なら、test_pass）をSQLに直接書くのは、セキュリティ上好ましくないようです。何より、最初の目的が、管理しきれないパスワードをPostgreSQLを使って管理する、だったのに管理するパスワードが増えてしまうのは困ります。しかも、このあまり使いそうにないパスワードは、すぐに忘れてしまいそうです。

そこで、この暗号化と復号をするためのパスワードを、postgresql.confに記述して管理します。

postgresql.confの設定

まずは、postgresql.confファイルがどこにあるか調べます。

show config_file;

               config_file               
-----------------------------------------
 /etc/postgresql/11/main/postgresql.conf

これで場所が分かったので、一度データベースからログアウトして、 postgresql.confファイルを編集します。

sudo nano /etc/postgresql/11/main/postgresql.conf

postgresql.confファイル のどこに書いてもいいようですが、わかりやすいように最後に記述します。

sample_class.pass = 'test_pass'

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

# Add settings for extensions here

sample_class.pass = 'test_pass'
注意点

custom_variable_classes = 'sample_class'というように、クラス定義を記述する必要はありません。クラス定義を記述すると、エラーがでてデータベースに接続できなくなります。

保存したら、PostgreSQLを再起動します。

sudo systemctl restart postgresql

current_setting関数の利用

postgresql.confの設定が完了したら、current_setting関数でパスワードを呼び出して、データの暗号化と復号を行います。

暗号化

INSERT INTO sample VALUES (
    'dattesar.com',
    'dattesar',
    pgp_sym_encrypt('dattesarpass',current_setting('sample_class.pass'))
);

復号

SELECT
    name,
    login_id,
    pgp_sym_decrypt(login_pass,current_setting('sample_class.pass'))
FROM
    sample;

内容を表示

current_setting関数は、SELECT文で内容を表示できるので、これはセキュリティ上いいのかどうか、私にはよくわかりません。

SELECT current_setting('sample_class.pass');

 current_setting 
-----------------
 test_pass
(1 行)

SQLクライアントツール　DBeaver

pgcryptoを使って、入力するデータを暗号化することはできましたが、SQL文がかなり長くなってしまいました。IDやパスワードを楽に管理するのが目的だったのですが、これでは楽になったのか面倒になったのか、よくわかりません。

これまでは、練習のためにもコマンドラインでデータベースを操作していましたが、同じようなSQL文を何十回も書くのは面倒すぎます。しかも、PGP暗号化関数は、encryptとdecryptで’’の有無など、微妙に記述が違うので間違えやすいです。

そこで、SQLクライアントツールのDBeaverを導入します。DBeaverは、GUIで操作できるうえ、SQLで書いた複数のクエリを一気に実行してくれるので、ものすごく便利です。しかも、無料です。日本語対応もしており、Windows、Mac、Linux版もあります。高機能すぎて、私にはとても使いこなせませんが、暗号化したいデータの入力がすごく楽になりました。

引用：https://dbeaver.io