テーブルとカラム単位でのデータ暗号化の例

データベースに保存されてる情報って、会社にとってめっちゃ大事なものが多いよね。でも残念ながら、悪いヤツらにとっても魅力的だったりする。だからこそ、暗号化についてちゃんと考えるのが大事なんだ。これはデータを他人の目から守る方法の一つだよ。

暗号化は、パスワードやクレジットカード番号、個人情報みたいな機密データを守るのに役立つんだ。それに、GDPRやHIPAAみたいな法律の要件を守るのにも使えるし、もし情報漏洩が起きても、暗号化されてればダメージをかなり減らせるよ。

PostgreSQLには対称暗号化用の便利な関数があるよ。pgp_sym_encrypt(data, key)でデータを暗号化して、同じキーを使ってpgp_sym_decrypt(encrypted_data, key)で復号できる。シンプルだし、安全！

データ暗号化の例

ステップ1: テーブルの作成

usersテーブルを作って、暗号化された電話番号を保存するカラムを追加しよう：

-- 暗号化データ用カラム付きテーブルの作成
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    phone_encrypted BYTEA -- ここに暗号化された電話番号を保存するよ
);

ステップ2: 暗号化してデータを追加

今度は、ユーザーを追加しつつ電話番号を暗号化してみよう：

-- 暗号化しながらデータを挿入
INSERT INTO users (username, phone_encrypted)
VALUES ('john_doe', pgp_sym_encrypt('123-456-7890', 'my_secret_key'));

pgp_sym_encrypt関数の使い方に注目してね。my_secret_keyは対称キーだよ。実際にはもっと複雑でしっかり守られたキーを使おう！

ステップ3: 復号してデータを取り出す

データにアクセスしたいときは、こうやって復号できる：

-- 復号しながらデータを取得
SELECT 
    username, 
    pgp_sym_decrypt(phone_encrypted, 'my_secret_key') AS phone
FROM users;

キーが正しければ、元の電話番号が見えるよ。

レベルアップ：既存テーブルに暗号化を追加する

もし既にテーブルがあって、その中のカラムだけ暗号化したくなったら？例を見てみよう。

ステップ1: 新しいカラムを作る

例えばcustomersテーブルがあって、クレジットカード番号のカラムを暗号化したい場合：

-- 暗号化データ用の新しいカラムを追加
ALTER TABLE customers ADD COLUMN card_number_encrypted BYTEA;

ステップ2: データを暗号化カラムに移す

既存データを暗号化して新しいカラムに移そう：

-- データを暗号化して新カラムに移動
UPDATE customers
SET card_number_encrypted = pgp_sym_encrypt(card_number, 'my_other_secret_key');

ステップ3: 暗号化してないカラムを削除

データの暗号化が終わったら、古いカラムは消してOK：

-- 古い暗号化してないカラムを削除
ALTER TABLE customers DROP COLUMN card_number;

これでデータは暗号化で守られて、キーがないとアクセスできなくなるよ。

暗号化データの扱いの注意点

暗号化カラムを使うときに気をつけたいポイントがいくつかあるよ：

データ型：

• 暗号化された値はバイナリ形式（BYTEA）で保存されて、読める形じゃないよ。
• クエリするときは復号関数を使う必要がある。

検索・フィルタ：

• 暗号化データでは直接検索できない。例えば：

  SELECT * FROM users WHERE phone_encrypted = '123-456-7890'; -- 動かないよ！

• 代わりに、復号してから絞り込もう：

  SELECT *
  FROM users
  WHERE pgp_sym_decrypt(phone_encrypted, 'my_secret_key') = '123-456-7890';

パフォーマンス：

暗号化や復号はクエリを遅くすることがあるから、本当に必要なとこだけ使おう。

実践シナリオ：パスワードの保護

パスワードの保存は暗号化で一番よくあるケースだよ。パスワードをそのまま保存するのは絶対ダメで、ハッシュ化しよう！

pgcryptoを使ったパスワードのハッシュ化

パスワードを安全にハッシュ化するにはcrypt()関数を使うよ：

-- レコード挿入時にパスワードをハッシュ化
INSERT INTO users (username, phone_encrypted)
VALUES ('alice', crypt('my_secure_password', gen_salt('bf')));

ここでgen_salt('bf')はパスワードハッシュ用のソルトを作ってる。

パスワードをチェックするには、ハッシュを比較しよう：

-- ハッシュ化パスワードの比較
SELECT username
FROM users
WHERE crypt('my_secure_password', phone_encrypted) = phone_encrypted;

セキュリティのコツ

1. キーは別で保存しよう：

暗号化データと同じデータベースに対称キーを保存しちゃダメだよ。

2. 複雑なキーを使おう：

"123"みたいな単純なキーはすぐバレるからダメ！

3. キーは定期的に更新しよう：

情報漏洩を防ぐために、定期的にキーを変えて、そのたびにデータも再暗号化しよう。