holly's wiki

※srcからインストールした場合前提で
ソースディレクトリのcontribまで移動

cd $PGSRC/contrib/

extensionインストール。pgcryptoの場合

cd pgcrypto
make
make install

psqlで接続

psql -U postgres

CREATE EXTENSION

CREATE EXTENSION pgcrypto;

確認

select * from pg_available_extensions;
   name   | default_version | installed_version |                     comment
----------+-----------------+-------------------+--------------------------------------------------
 plpgsql  | 1.0             | 1.0               | PL/pgSQL procedural language
 pgcrypto | 1.0             | 1.0               | cryptographic functions

暗号化。本格的な暗号化を行いたいのならCPUがいいサーバにしておいたほうがいい。http://www.postgresql.jp/document/9.0/html/pgcrypt...

暗号化するデータのフィールドはbyteaにする

create table t1 (
   id  serial primary key,
   secret bytea not null
);

秘密鍵を使った暗号化をする場合は事前に作業しておくこと

# 作成
gpg --gen-key
# 鍵一覧
gpg --list-keys
/home/postgres/.gnupg/pubring.gpg
---------------------------------
pub   1024D/6D34EAF5 2011-08-01
uid                  postgres <postgres@localhost>
sub   2048g/5346D13C 2011-08-01

公開鍵/秘密鍵情報を取得する

# 公開鍵
gpg -a --export postgres@localhost > public.key

# 秘密鍵
gpg -a --export-secret-keys postgres@localhost > secret.key

鍵情報登録用テーブル

create table gpg_keys (
  id int primary key,
  public_key text not null,
  private_key text not null
);

insert into gpg_keys(id, public_key, private_key) values(1, 'public.keyの内容', 'private.keyの内容');

pgp_pub_encrypt/pgp_pub_decryptを使う場合はこれらのデータを使用して暗号化/複合化を行う

単純な暗号化。アルゴリズムはどっちかのみ

• bf Blowfish
• aes AES (Rijndael-128)

-- encrypt(data bytea, key bytea, type text) returns bytea なので、convert_toが必要
insert into t1(secret) values(encrypt(convert_to('たろう', 'UTF8'), 'password'::bytea, 'aes'));

取り出す場合は以下のようにする

select convert_from(decrypt(secret, 'password'::bytea, 'aes'), 'UTF8') as name from t1;
  name
--------
 たろう
(1 row)

高機能、暗号化強度も強いが、オーバーヘッド大

-- pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
insert into t1(secret) values(pgp_sym_encrypt('たろう', 'password'));

取り出し

select pgp_sym_decrypt(secret, 'password') as name from t1;
  name
--------
 たろう
(1 row)

鍵を使って暗号化。暗号強度は大きい

登録

-- pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea  [key = 公開鍵]
insert into t1(secret) values( pgp_pub_encrypt('ひみつ', dearmor((select public_key from gpg_keys))) );

取得

-- pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns bytea　[key = 秘密鍵]
select pgp_pub_decrypt(secret, dearmor((select private_key from gpg_keys)), '<PASS>') as name from t1;

このままではパスワードを毎回指定しないといけないので、パスワードをpostgresql.confで管理できるようにする

custom_variable_classes = 'myapp'               # list of custom variable class names
myapp.secret = 'xxxxxxx'

もしくは起動オプションなど

# 環境変数にも指定可能(動的に設定できる)
export PGOPTIONS="-c myapp.secret=xxxxxxx"

設定情報はcurrent_setting関数で取得可能

 select current_setting('myapp.secret');
 current_setting
-----------------
 xxxxxxx
(1 row)

さらに取得、更新時にややこしいSQLを発行しないでいいようにする。とりあえず今回は更新可能viewを定義してやってみた（triggerじゃうまくいかんかった）

viewを定義。元のテーブルとフィールド名をあわしておいたほうがよい

-- view. selectはこれでいい
create or replace view v_t1_decrypted as select id, pgp_pub_decrypt(secret, dearmor((select private_key from gpg_keys)), current_setting('myapp.secret')) as secret from t1;

ruleを使ってinsert/update可能なviewにする。function作って、trigger設定するより理解はしやすいかも

 create or replace rule rule_v_t1_decryped_insert as on insert to v_t1_decrypted do instead
    insert into t1(secret) values( pgp_pub_encrypt(new.secret, dearmor((select public_key from gpg_keys))) );
  
  create or replace rule rule_v_t1_decryped_update as on update to v_t1_decrypted do instead
    update t1 set secret = pgp_pub_encrypt(new.secret, dearmor((select public_key from gpg_keys))) where id = old.id;

これでviewに対してinsert/updateすればテーブル内に暗号化してデータがはいる

  insert into v_t1_decrypted(secret) values ('ひみつ');
  update v_t1_decrypted set secret = 'ひみつのあたいをへんこう' where id = 1;

digest関数を使えば、sha512（sha1でもいいんだけど）などを実装することができる。plpgsqlで実装するとこんな感じ

create or replace function sha512(text) returns text as $$
  declare
    src alias for $1;
    dest text;
  begin
    select encode(digest(src, 'sha512'), 'hex') into dest;
    return dest;
  end;
$$ language plpgsql strict;