PostgreSQLのUUID型とpgcryptoモジュールを使って会員パスワード認証を実装してみる

はじめまして、堀井と申します。

このたびTECHSCORE BLOGに記事を書かせていただくことになりました。よろしくお願いします。

普段の仕事ではデータベースは専らMySQLを使っていますが、この機会をお借りして、久しぶりにPostgreSQLを触ってみました。（Ver7系が主流だった頃以来なので多分10年ぶりくらい？）

会員制Webサイトという想定でパスワード認証を実装するという基礎的な内容ですが、せっかくなのでこれまで未経験の機能を使ってみます。

以下、動作を確認した環境です。

Windows 7
PostgreSQL 9.5.0

SQLはSQL Shell(psql)から実行しました。データベースやロールの作成については割愛します。

pgcryptoモジュールを有効にする

まずはpgcryptoをインストールします。（インストール対象データベースへのスーパーユーザ権限が必要です）

CREATE EXTENSION pgcrypto;

SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;

   name   | default_version | installed_version |           comment
----------+-----------------+-------------------+------------------------------
 pgcrypto | 1.2             | 1.2               | cryptographic functions
 plpgsql  | 1.0             | 1.0               | PL/pgSQL procedural language

CREATE EXTENSION pgcrypto;

SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;

name | default_version | installed_version | comment

----------+-----------------+-------------------+------------------------------

pgcrypto | 1.2 | 1.2 | cryptographic functions

plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language

pgcryptoが有効になりました。

gen_random_uuid()関数でUUIDを生成する

普段のMySQLの仕事ではレコードのIDには大抵の場合AUTO INCREMENTを利用するのですが、今回はPostgreSQLならではのUUID型を使ってみることにします。

PostgreSQL 9.4.5文書 / UUID型
http://www.postgresql.jp/document/9.4/html/datatype-uuid.html

PostgreSQLの標準機能ではUUIDを生成する関数は提供されていませんが、pgcryptoにはランダムなUUID(UUID ver4)を生成するgen_random_uuid()関数が用意されていますので、これを利用します。

こんな会員管理用のテーブルを作成してみます。

CREATE TABLE t_member (
    member_id     UUID        NOT NULL DEFAULT gen_random_uuid(),
    member_name   TEXT        NOT NULL,
    login_id      VARCHAR(64) NOT NULL DEFAULT gen_random_uuid(),
    password_hash TEXT,
    PRIMARY KEY (member_id),
    UNIQUE (login_id)
);

CREATE TABLE t_member (

member_id UUID NOT NULL DEFAULT gen_random_uuid(),

member_name TEXT NOT NULL,

login_id VARCHAR(64) NOT NULL DEFAULT gen_random_uuid(),

password_hash TEXT,

PRIMARY KEY (member_id),

UNIQUE (login_id)

);

member_idがレコードのIDで、gen_random_uuid()関数でUUIDを自動発行します。

login_idの方は会員自身が設定するIDですが、同様に初期値としてUUIDを利用することにしました。

会員のレコードをまとめて登録してみます。

INSERT INTO t_member (member_name) VALUES
('織田信秀'),
('織田信長'),
('織田信忠'),
('織田信雄'),
('織田信孝');

SELECT * FROM t_member;

              member_id               | member_name |               login_id               | password_hash
--------------------------------------+-------------+--------------------------------------+---------------
 fd97b15b-59ef-46bd-a9ae-dd7a90d68242 | 織田信秀    | db7a3928-9024-454c-9582-ecca6534bab3 |
 7f48257c-522a-4030-a20e-82d1fc883714 | 織田信長    | 65df3641-a3e2-4b21-a90d-73a4bb666a2e |
 5d7b3afc-7a52-4eed-b0e6-ebd1e96b1e53 | 織田信忠    | 5dd2ad3e-2d53-41ec-a547-7f9511b1f540 |
 facf5603-9015-41db-b74d-b13adf68894e | 織田信雄    | 1c6272a3-82be-48fa-8fff-6370d3e17aa2 |
 3599e841-5630-4dc2-b8a4-82cb638a1aa9 | 織田信孝    | 32fb6191-b0a6-4b3b-b105-2f642b5a6485 |

INSERT INTO t_member (member_name) VALUES

('織田信秀'),

('織田信長'),

('織田信忠'),

('織田信雄'),

('織田信孝');

SELECT * FROM t_member;

member_id | member_name | login_id | password_hash

--------------------------------------+-------------+--------------------------------------+---------------

fd97b15b-59ef-46bd-a9ae-dd7a90d68242 | 織田信秀 | db7a3928-9024-454c-9582-ecca6534bab3 |

7f48257c-522a-4030-a20e-82d1fc883714 | 織田信長 | 65df3641-a3e2-4b21-a90d-73a4bb666a2e |

5d7b3afc-7a52-4eed-b0e6-ebd1e96b1e53 | 織田信忠 | 5dd2ad3e-2d53-41ec-a547-7f9511b1f540 |

facf5603-9015-41db-b74d-b13adf68894e | 織田信雄 | 1c6272a3-82be-48fa-8fff-6370d3e17aa2 |

3599e841-5630-4dc2-b8a4-82cb638a1aa9 | 織田信孝 | 32fb6191-b0a6-4b3b-b105-2f642b5a6485 |

期待通り、UUIDが自動で発行されました。これは楽ちんですね。

以後、織田信長さんのデータを操作していきますが、UUIDを毎回入力するのは大変なので、まずはログインIDを変更しておきます。

UPDATE t_member SET login_id = 'nobunaga' WHERE member_name = '織田信長';

1	UPDATE t_member SET login_id = 'nobunaga' WHERE member_name = '織田信長';

crypt()関数でパスワードをハッシュ化

次はこの会員テーブルを使ったパスワード認証を実装してみます。

パスワードのハッシュ化のために設計されたという、crypt()関数とgen_salt()関数を利用します。

PostgreSQL 9.4.5文書 / pgcrypto / F.25.2. パスワードハッシュ化関数
http://www.postgresql.jp/document/9.4/html/pgcrypto.html#AEN161582

ドキュメントに記載されているcrypt()関数のポイントとしては、実行結果にソルトやアルゴリズムの種類など生成時の処理内容が含まれているため、異なるアルゴリズムで生成した値が同じテーブルに混在していても問題なく処理できることでしょうか。

更に「適応型」のアルゴリズムを利用した場合は、将来的にコンピュータの性能向上によってアルゴリズムを変更せざるを得なくなったとしても、互換性の問題が起きないとのこと。

アルゴリズムはdes, xdes, md5, bfが利用できますが、desとxdesはパスワードの最大長が8バイト、MD5は衝突耐性の問題がありますし適応型でもありません。Blowfishベースというbfが最大長72で適応型でもあり、今回の用途ではこれが良さそうです。

以下のSQLで、パスワードをハッシュ化して保存します。

UPDATE t_member SET password_hash = crypt('Mitsuhide-Honnouji@1582', gen_salt('bf'))
WHERE login_id = 'nobunaga';

SELECT login_id, password_hash FROM t_member WHERE login_id = 'nobunaga';

 login_id |                        password_hash
----------+--------------------------------------------------------------
 nobunaga | $2a$06$ObzinNCG.DOuTUhvaMIl4u.6CUWtD.ztvODeYHmwJQXAzqiaby.kS

UPDATE t_member SET password_hash = crypt('Mitsuhide-Honnouji@1582', gen_salt('bf'))

WHERE login_id = 'nobunaga';

SELECT login_id, password_hash FROM t_member WHERE login_id = 'nobunaga';

login_id | password_hash

----------+--------------------------------------------------------------

nobunaga | $2a$06$ObzinNCG.DOuTUhvaMIl4u.6CUWtD.ztvODeYHmwJQXAzqiaby.kS

こんな値が入りました。

格納されているハッシュ値をソルトとしてcrypt()関数を実行し、その結果が格納されているハッシュ値と一致するかどうかで、パスワードの正当性を検証できます。

SELECT (password_hash = crypt('Mitsuhide-Honnouji@1582', password_hash)) AS matched
FROM t_member WHERE login_id = 'nobunaga';

 matched
---------
 t

SELECT (password_hash = crypt('Mitsuhide-Honnouji@1582', password_hash)) AS matched

FROM t_member WHERE login_id = 'nobunaga';

matched

---------

認証に成功しました。

汎用ハッシュ関数でパスワードにソルトを加えつつハッシュ値をストレッチング

crypt()関数は便利ですが、それだけに頼る方法だと、データベースまでの経路では生のパスワードを扱うことになります。

また、経路を暗号化できない場合の対策として、クライアント側でパスワードのハッシュ値を生成する方式を採用することがありますが、その場合クライアント側とサーバ側でハッシュ値の生成手順を合わせなければいけません。

そこで、pgcryptoモジュールで提供されている汎用ハッシュ関数を利用し、多くの言語環境でサポートされているSHA-2ファミリのアルゴリズムにより、パスワードにソルトを加えてハッシュ化し、更に任意回数のストレッチングを行うこととします。

PostgreSQL 9.4.5文書 / pgcrypto / F.25.1. 汎用ハッシュ関数
http://www.postgresql.jp/document/9.4/html/pgcrypto.html#AEN161546

ハッシュ生成手順を以下の様な関数として定義します。

CREATE OR REPLACE FUNCTION stretch_password_hash(text, text, text, integer) RETURNS text AS $$
DECLARE
    password   ALIAS FOR $1;
    algorithm  ALIAS FOR $2;
    salt       ALIAS FOR $3;
    stretching ALIAS FOR $4;
    password_hash text;
    i integer;
BEGIN
    password_hash = '';
    i := 0;
    WHILE i < stretching LOOP
        password_hash := encode(digest(password_hash || password || salt, algorithm), 'hex');
        i := i + 1;
    END LOOP;
    RETURN password_hash;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION stretch_password_hash(text, text, text, integer) RETURNS text AS $$

DECLARE

password ALIAS FOR $1;

algorithm ALIAS FOR $2;

salt ALIAS FOR $3;

stretching ALIAS FOR $4;

password_hash text;

i integer;

BEGIN

password_hash = '';

i := 0;

WHILE i < stretching LOOP

password_hash := encode(digest(password_hash || password || salt, algorithm), 'hex');

i := i + 1;

END LOOP;

RETURN password_hash;

END;

$$ LANGUAGE plpgsql;

第1引数が生のパスワード、第2引数がアルゴリズム、第3引数がソルト、第4引数がストレッチング回数です。

digest()はバイナリハッシュを計算する関数で、標準のアルゴリズムとして md5, sha1, sha224, sha256, sha384, sha512 に対応しています。そして、これを16進数文字列に変換するために標準の文字列関数encode()も併用しています。

会員テーブルのpassword_hashカラムには、この関数で生成したハッシュ値を更にcrypt()関数をかけて保存します。

ソルトについては、ユーザ毎に異なり、推測困難で、ある程度の長さのある値が適している……ということで、UUID ver4が適任と思われますので、member_idをそのまま利用します。

先ほど定義したstretch_password_hash()関数を利用して、アルゴリズムはsha256、ストレッチング回数は1000回として、パスワードハッシュを生成してみます。

SELECT stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha256', member_id::text, 1000)
FROM t_member WHERE login_id = 'nobunaga';

                      stretch_password_hash
------------------------------------------------------------------
 816b26959a82b7c340f80ba253c6c7c567cb8eb9ef0ccf57bb5ad127091b007a

SELECT stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha256', member_id::text, 1000)

FROM t_member WHERE login_id = 'nobunaga';

stretch_password_hash

------------------------------------------------------------------

816b26959a82b7c340f80ba253c6c7c567cb8eb9ef0ccf57bb5ad127091b007a

こんな値になりました。

念のため、PHPで同じ処理を実装して結果を確認してみます。（実は私は普段PHPばかり書いている人です）

function stretch_password_hash($password, $algorithm, $salt, $stretching) {
    $password_hash = '';
    for ($i = 0; $i < $stretching; $i++) {
        $password_hash = hash($algorithm, $password_hash . $password . $salt, false);
    }
    return $password_hash;
};
echo stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha256', '7f48257c-522a-4030-a20e-82d1fc883714', 1000);
// 816b26959a82b7c340f80ba253c6c7c567cb8eb9ef0ccf57bb5ad127091b007a

function stretch_password_hash($password, $algorithm, $salt, $stretching) {

$password_hash = '';

for ($i = 0; $i < $stretching; $i++) {

$password_hash = hash($algorithm, $password_hash . $password . $salt, false);

}

return $password_hash;

};

echo stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha256', '7f48257c-522a-4030-a20e-82d1fc883714', 1000);

// 816b26959a82b7c340f80ba253c6c7c567cb8eb9ef0ccf57bb5ad127091b007a

同じ値になりました。

これを踏まえて、もう一度crypt()関数を通してパスワードハッシュを保存し直します。

UPDATE t_member
SET password_hash = crypt(stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha256', member_id::text, 1000), gen_salt('bf'))
WHERE login_id = 'nobunaga';

UPDATE t_member

SET password_hash = crypt(stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha256', member_id::text, 1000), gen_salt('bf'))

WHERE login_id = 'nobunaga';

先ほどPHPで算出した値をcrypt()関数に通して、正当性を検証してみます。

SELECT (password_hash = crypt('816b26959a82b7c340f80ba253c6c7c567cb8eb9ef0ccf57bb5ad127091b007a', password_hash)) AS matched
FROM t_member WHERE login_id = 'nobunaga';

 matched
---------
 t

SELECT (password_hash = crypt('816b26959a82b7c340f80ba253c6c7c567cb8eb9ef0ccf57bb5ad127091b007a', password_hash)) AS matched

FROM t_member WHERE login_id = 'nobunaga';

matched

---------

認証に成功しました。

パスワードハッシュのアルゴリズムとストレッチング回数をデータベースで管理する

SHA-1の衝突耐性の問題が指摘されて以来、SHA-2への移行が急務となっている昨今ですが、将来的には更にSHA-3への移行が推奨される状況になるはずです。

そこで、ハッシュ生成時に指定したアルゴリズムとストレッチング回数をパスワードハッシュのバージョンと捉えて、データベースで管理してみます。

今回は深く考えず、会員テーブルにそれぞれのカラムを追加します。

ALTER TABLE t_member ADD COLUMN algorithm  VARCHAR(16) NOT NULL DEFAULT 'sha256';
ALTER TABLE t_member ADD COLUMN stretching INTEGER NOT NULL DEFAULT floor(500 + random() * (1000 + 1 - 500));

1 2	ALTER TABLE t_member ADD COLUMN algorithm VARCHAR(16) NOT NULL DEFAULT 'sha256'; ALTER TABLE t_member ADD COLUMN stretching INTEGER NOT NULL DEFAULT floor(500 + random() * (1000 + 1 - 500));

アルゴリズムは初期値をsha256に固定、ストレッチング回数はレコード登録時に500-1000の間で自動生成しておきます。

SELECT member_id, login_id, algorithm, stretching FROM t_member;

              member_id               |               login_id               | algorithm | stretching
--------------------------------------+--------------------------------------+-----------+------------
 fd97b15b-59ef-46bd-a9ae-dd7a90d68242 | db7a3928-9024-454c-9582-ecca6534bab3 | sha256    |        656
 5d7b3afc-7a52-4eed-b0e6-ebd1e96b1e53 | 5dd2ad3e-2d53-41ec-a547-7f9511b1f540 | sha256    |        581
 facf5603-9015-41db-b74d-b13adf68894e | 1c6272a3-82be-48fa-8fff-6370d3e17aa2 | sha256    |        979
 3599e841-5630-4dc2-b8a4-82cb638a1aa9 | 32fb6191-b0a6-4b3b-b105-2f642b5a6485 | sha256    |        746
 7f48257c-522a-4030-a20e-82d1fc883714 | nobunaga                             | sha256    |        835

SELECT member_id, login_id, algorithm, stretching FROM t_member;

member_id | login_id | algorithm | stretching

--------------------------------------+--------------------------------------+-----------+------------

fd97b15b-59ef-46bd-a9ae-dd7a90d68242 | db7a3928-9024-454c-9582-ecca6534bab3 | sha256 | 656

5d7b3afc-7a52-4eed-b0e6-ebd1e96b1e53 | 5dd2ad3e-2d53-41ec-a547-7f9511b1f540 | sha256 | 581

facf5603-9015-41db-b74d-b13adf68894e | 1c6272a3-82be-48fa-8fff-6370d3e17aa2 | sha256 | 979

3599e841-5630-4dc2-b8a4-82cb638a1aa9 | 32fb6191-b0a6-4b3b-b105-2f642b5a6485 | sha256 | 746

7f48257c-522a-4030-a20e-82d1fc883714 | nobunaga | sha256 | 835

カラムの定義を追加すると、こんな感じで初期値が入りました。

この設定で織田信長さんのパスワードハッシュを保存してみます。アルゴリズムはsha256、ストレッチング回数は835回のはず。

UPDATE t_member
SET password_hash = crypt(stretch_password_hash('Mitsuhide-Honnouji@1582', t.algorithm, t.member_id::text, t.stretching), gen_salt('bf'))
FROM (SELECT * FROM t_member) t
WHERE t_member.member_id = t.member_id AND t_member.login_id = 'nobunaga';

UPDATE t_member

SET password_hash = crypt(stretch_password_hash('Mitsuhide-Honnouji@1582', t.algorithm, t.member_id::text, t.stretching), gen_salt('bf'))

FROM (SELECT * FROM t_member) t

WHERE t_member.member_id = t.member_id AND t_member.login_id = 'nobunaga';

先程のPHPの関数でハッシュ値を算出します。

echo stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha256', '7f48257c-522a-4030-a20e-82d1fc883714', 835);
// 07b7c2d96702c389d92671d1211436dc4f9546a9a2effcbf5dc5585808c6b1af

1 2	echo stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha256', '7f48257c-522a-4030-a20e-82d1fc883714', 835); // 07b7c2d96702c389d92671d1211436dc4f9546a9a2effcbf5dc5585808c6b1af

この値をcrypt()関数に通して、正当性を検証してみます。

SELECT (password_hash = crypt('07b7c2d96702c389d92671d1211436dc4f9546a9a2effcbf5dc5585808c6b1af', password_hash)) AS matched
FROM t_member WHERE login_id = 'nobunaga';

 matched
---------
 t

SELECT (password_hash = crypt('07b7c2d96702c389d92671d1211436dc4f9546a9a2effcbf5dc5585808c6b1af', password_hash)) AS matched

FROM t_member WHERE login_id = 'nobunaga';

matched

---------

認証に成功しました。

次はアルゴリズムをsha512に、ストレッチング回数を100回に変更してパスワードを保存し直し、もう一度認証してみます。

UPDATE t_member
SET algorithm = 'sha512',
    stretching = 100,
    password_hash = crypt(stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha512', member_id::text, 100), gen_salt('bf'))
WHERE login_id = 'nobunaga';

UPDATE t_member

SET algorithm = 'sha512',

stretching = 100,

password_hash = crypt(stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha512', member_id::text, 100), gen_salt('bf'))

WHERE login_id = 'nobunaga';

先程のPHPの関数でハッシュ値を算出します。

echo stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha512', '7f48257c-522a-4030-a20e-82d1fc883714', 100);
// 77458cb726cc0ba46e7af96a055d4053bfdc302c7efd806c6f88743b04602e8c7321769ed07d2d4fcd1b1b19c9c5acb7e1e4ad644d36f66c8e95488cee6beb83

1 2	echo stretch_password_hash('Mitsuhide-Honnouji@1582', 'sha512', '7f48257c-522a-4030-a20e-82d1fc883714', 100); // 77458cb726cc0ba46e7af96a055d4053bfdc302c7efd806c6f88743b04602e8c7321769ed07d2d4fcd1b1b19c9c5acb7e1e4ad644d36f66c8e95488cee6beb83

SHA-512なので先程より長くなりました。この値をcrypt()関数に通して、正当性を検証してみます。

SELECT (password_hash = crypt('77458cb726cc0ba46e7af96a055d4053bfdc302c7efd806c6f88743b04602e8c7321769ed07d2d4fcd1b1b19c9c5acb7e1e4ad644d36f66c8e95488cee6beb83', password_hash)) AS matched
FROM t_member WHERE login_id = 'nobunaga';

 matched
---------
 t

SELECT (password_hash = crypt('77458cb726cc0ba46e7af96a055d4053bfdc302c7efd806c6f88743b04602e8c7321769ed07d2d4fcd1b1b19c9c5acb7e1e4ad644d36f66c8e95488cee6beb83', password_hash)) AS matched

FROM t_member WHERE login_id = 'nobunaga';

matched

---------

認証に成功しました。

今回の例ではハッシュ化のアルゴリズムとストレッチング回数を会員毎に保持しましたが、通常の認証ではそれらを頻繁に変更することはないため、アルゴリズムとストレッチング回数の組をパスワードハッシュのバージョンとして、会員に紐付けて別途管理しても良さそうです。

また、チャレンジ／レスポンス方式を採用する場合は、発行したチャレンジには有効期限を設けるべきですし、認証完了時に無効化しないとリプレイ攻撃への対策にはなりませんので、それらを会員に紐付くトランザクションデータとして別途管理する必要があると思います。

以上、あまり目新しさのない内容ですが、アプリケーション言語で実装されることが多いであろうパスワードのハッシュ化をPostgreSQLの機能で実装してみました。