以前から自分が欲しかったサービスである、安否確認サービスをリリースしました。

私にもしもの事があったとき、確かな方に必要な情報を引き継ぐサービスです。

題して、デジタルエンディングノート「人生金庫」

そのなかで、引き継ぐ情報をデータベース内では暗号化して保管するために、今回PostgreSQL9.4の暗号化をしてみました。

yumでPostgreSQLをインストールしていたので、postgresql94-contribがインストールされている必要があります。

■これはダメでした。。。

下記の方法が書かれていたんで、やってみたんですがエラーが出ました。。

-bash-4.1$ psql -f /usr/pgsql-9.4/share/extension/pgcrypto--1.0--1.1.sql -d xx_db
Use "ALTER EXTENSION pgcrypto UPDATE TO '1.1'" to load this file.
life_db > ALTER EXTENSION pgcrypto UPDATE TO ‘1.1';
ERROR:  extension "pgcrypto" does not exist

■この方法はOKでした。。

1)インストールされているか??

 $ psql -d postgres -c "select * from pg_available_extensions"
         name        | default_version | installed_version |                               comment
 --------------------+-----------------+-------------------+----------------------------------------------------------------------
  pgcrypto           | 1.1             |                   | cryptographic functions

あり

2)関数が組込まれているか?

 xx_db=> \df *pgp_sym*
                         関数一覧
  スキーマ | 名前 | 結果のデータ型 | 引数のデータ型 | 型
 ----------+------+----------------+----------------+----
 (0 行)

なし

3)関数を組込みます

 # su - postgres:スーパーユーザーになる必要があります。
 $ psql xx_db
 # CREATE EXTENSION pgcrypto;
 CREATE EXTENSION
 # \df *pgp_sym*
                                         関数一覧
>  スキーマ |         名前          | 結果のデータ型 |  引数のデータ型   |       型
> ----------+-----------------------+----------------+-------------------+----------------
>  public   | pgp_sym_decrypt       | text           | bytea, text       | normal(通常)
>  public   | pgp_sym_decrypt       | text           | bytea, text, text | normal(通常)
>  public   | pgp_sym_decrypt_bytea | bytea          | bytea, text       | normal(通常)
>  public   | pgp_sym_decrypt_bytea | bytea          | bytea, text, text | normal(通常)
>  public   | pgp_sym_encrypt       | bytea          | text, text        | normal(通常)
>  public   | pgp_sym_encrypt       | bytea          | text, text, text  | normal(通常)
>  public   | pgp_sym_encrypt_bytea | bytea          | bytea, text       | normal(通常)
>  public   | pgp_sym_encrypt_bytea | bytea          | bytea, text, text | normal(通常)
> (8 行)

■Perlでの使い方

 xx_db => tbl_xx
                                      テーブル "public.tbl_xx"
       列       |             型              |                       修飾語
 ---------------+-----------------------------+-----------------------------------------------------
  id            | integer                     | not null default nextval('tbl_xx_id_seq'::regclass)
  data_hash     | bytea                       |

my $sqlUpd = "UPDATE tbl_xx SET data_hash=pgp_sym_encrypt(?,’pass')";
my $sqlSel = "SELECT pgp_sym_decrypt( data_hash, ‘pass' ) FROM tbl_xx";

テーブルはバイナリー形式で設定しています。
text形式での場合は、読み出すときデータとパスワードをそれぞれキャストする必要が出てるそうです。


my $sqlSel = "SELECT pgp_sym_decrypt( data_hash::bytea, ‘pass'::text ) FROM tbl_xx";