ハッシュ連鎖によるPostgreSQLの改ざん検知可能な監査トレイル

通常の監査ログが争点になりやすい理由

監査トレイルは、何かおかしいと感じたときに頼る記録です：不審な返金、誰も覚えていない権限変更、あるいは「消えた」顧客レコード。監査トレイル自体が編集できるなら、それは証拠ではなく誰かが書き換えられる可能性のある別のデータになります。

多くの「監査ログ」は単なる通常のテーブルです。行が更新・削除できれば、物語も更新・削除されてしまいます。

重要な区別は、編集を阻止することと、編集を検出可能にすることは同じではない、という点です。権限で変更を減らすことはできますが、十分な権限を持つ誰か（あるいは盗まれた管理者資格情報）なら履歴を変えられます。改ざん検知はその現実を受け入れます。すべての変更を防げないかもしれませんが、変更が明確な痕跡を残すようにできます。

通常の監査ログが争点になる理由は予測可能です。特権ユーザーが事後にログを「修正」できる。侵害されたアプリアカウントが通常のトラフィックに見える信じられるエントリを書ける。タイムスタンプを遡って埋めて遅延変更を隠せる。あるいは最も不利な行だけを削除することもできます。

「改ざん検知可能」とは、監査トレイルを設計して、ほんの小さな編集（フィールドを1つ変える、行を1つ削除する、イベントの順序を入れ替える）が後で検出可能になるようにすることを意味します。魔法を約束するわけではありません。「このログが本物であることをどう証明するのか？」と聞かれたときに、ログが触れられているかどうかを示すチェックを実行できる、ということを約束します。

何を証明する必要があるかを決める

改ざん検知可能な監査トレイルは、後で直面する質問に答えられる場合にのみ有用です：誰が何をしたのか、いつそれをしたのか、何が変わったのか。

まず、ビジネス上重要なイベントから始めます。データ変更（作成・更新・削除）は基礎ですが、調査はセキュリティやアクセスに関する出来事に依存することが多いです：ログイン、パスワードリセット、権限変更、アカウントロックアウト。支払い、返金、クレジット、支払い処理を扱う場合は、マネーの移動を副次的な更新ではなく第一級のイベントとして扱ってください。

次に、イベントが信用できると見なされるための要素を決めます。監査人は通常、アクター（ユーザーまたはサービス）、サーバー側のタイムスタンプ、実行されたアクション、影響を受けたオブジェクトを期待します。更新については、変更前と変更後の値（少なくとも敏感なフィールド）を保存し、複数の小さなデータベース変更を1つのユーザー操作に結びつけるためのリクエストIDや相関IDを持たせます。

最後に、システム内で「不変」が何を意味するかを明確にします。最も単純なルールは：監査行を更新したり削除したりせず、挿入だけを行うことです。何かが間違っていたら、古いイベントを訂正・上書きする新しいイベントを書き、元のイベントは可視のままにします。

append-onlyの監査テーブルを作る

監査データは通常のテーブルとは分離しておきましょう。専用のauditスキーマは偶発的な編集を減らし、権限の設計を簡単にします。

目標は簡単です：行は追加できるが、変更や削除はできない。PostgreSQLでは権限（誰が何をできるか）とテーブル設計のいくつかの安全策でこれを強制します。

実用的な開始テーブルの例：

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE audit.events (
  id            bigserial PRIMARY KEY,
  entity_type   text        NOT NULL,
  entity_id     text        NOT NULL,
  event_type    text        NOT NULL CHECK (event_type IN ('INSERT','UPDATE','DELETE')),
  actor_id      text,
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  request_id    text,
  before_data   jsonb,
  after_data    jsonb,
  notes         text
);

調査時に特に役立ついくつかのフィールド：

• occurred_at は DEFAULT now() を使い、時刻をクライアントではなくデータベース側で刻む。
• entity_type と entity_id により、1つのレコードの変更履歴を追える。
• request_id により、1つのユーザー操作を複数の行で追跡できる。

ロールで締め付けましょう。アプリケーション用ロールは audit.events に対して INSERT と SELECT はできるが、UPDATE や DELETE はできないようにします。スキーマ変更や強い権限は、アプリで使われない管理者ロールに限定します。

トリガーで変更を捕捉する（シンプルで予測可能）

改ざん検知可能な監査トレイルを作るなら、変更を捕捉する最も信頼できる場所はデータベースです。アプリケーションログはスキップされたり、フィルタされたり、書き換えられたりする可能性があります。トリガーは、どのアプリ、スクリプト、管理ツールがテーブルに触れても発火します。

トリガーは冗長にしないでください。役割は一つだけ：重要なテーブルの各INSERT、UPDATE、DELETEに対して1つの監査イベントを追加することです。

実用的な監査レコードには通常、テーブル名、操作タイプ、主キー、変更前後の値、タイムスタンプ、関連づけに使える識別子（トランザクションIDや相関ID）が含まれます。

相関IDがあると「20行が更新された」ではなく「これは1回のボタンクリックだった」と説明できます。アプリはリクエストごとに一度相関IDをセットでき（例えばDBセッション設定で）、トリガーがそれを読みます。相関IDが無い場合でも txid_current() を保存しておけばグルーピングが可能です。

以下は、監査テーブルに対して挿入だけ行うことで予測可能性を保つ単純なトリガーパターンです（スキーマ名や列名は環境に合わせて調整してください）：

CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger AS $$
DECLARE
  corr_id text;
BEGIN
  corr_id := current_setting('app.correlation_id', true);

  INSERT INTO audit_events(
    occurred_at, table_name, op, row_pk,
    old_row, new_row, db_user, txid, correlation_id
  ) VALUES (
    now(), TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id),
    to_jsonb(OLD), to_jsonb(NEW), current_user, txid_current(), corr_id
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

トリガーで余計なことをしたくなる誘惑に抵抗してください。追加のクエリやネットワーク呼び出し、複雑な分岐は避けましょう。小さなトリガーはテストしやすく、実行が速く、レビュー時に争点になりにくいです。

編集が指紋を残すようにハッシュ連鎖を追加する

append-onlyテーブルだけでも効果はありますが、十分な権限を持つ誰かは過去の行を書き換えられる可能性があります。ハッシュ連鎖はその種の改ざんを可視化します。

各監査行に2つの列を追加します：prev_hash と row_hash（chain_hash と呼ばれることもあります）。prev_hash は同じチェーン中の前の行のハッシュを保存し、row_hash は現在の行のデータと prev_hash から計算したハッシュを保存します。

何をハッシュするかが重要です。同じ行が常に同じハッシュを生むよう、安定した再現可能な入力を用意したいです。

実用的な方法は、固定列（タイムスタンプ、アクター、アクション、エンティティID）から作る正規化された文字列、正規化されたペイロード（多くは jsonb、キー順が一貫するため）と prev_hash を組み合わせてハッシュ化することです。

空白やJSONキー順、ロケール依存の書式など、意味のない差分に注意してください。型を一貫させ、1つの予測可能な方法でシリアライズします。

全データベース単位ではなくストリームごとにチェーンする

全イベントを1つのグローバルな連続でチェーンすると書き込みがボトルネックになり得ます。多くのシステムはテナントごと、エンティティタイプごと、業務オブジェクトごとのように「ストリーム」単位でチェーンします。

新しい行は自分のストリームの最新 row_hash を参照してそれを prev_hash に保存し、そこから自分の row_hash を計算します。

-- Requires pgcrypto
-- digest() returns bytea; store hashes as bytea
row_hash = digest(
  concat_ws('|',
    stream_key,
    occurred_at::text,
    actor_id::text,
    action,
    entity,
    entity_id::text,
    payload::jsonb::text,
    encode(prev_hash, 'hex')
  ),
  'sha256'
);

チェーンのヘッドをスナップショットする

レビューを速くするために、最新の row_hash（「チェーンヘッド」）をストリームごとに定期的に小さなスナップショットテーブルに保存します（たとえば日次）。調査時には全履歴を一度に走査する代わりに各スナップショットまでチェーンを検証できます。スナップショットはエクスポートを比較して不審なギャップを見つけるのにも便利です。

チェーンを壊さずに同時実行と順序を扱う

実運用下ではハッシュ連鎖は厄介です。2つのトランザクションが同時に監査行を書き、両方が同じ prev_hash を使うとフォークが発生する可能性があります。それは単一のクリーンなシーケンスを証明する力を弱めます。

まず、チェーンが何を表しているのかを決めてください。グローバルチェーンは説明が最も簡単ですが競合が最も激しくなります。複数チェーンは競合を減らしますが、各チェーンが何を証明するかを明確にしておく必要があります。

どのモデルを採るにせよ、単調増加するイベントID（通常はシーケンスで付与されるID）で厳密な順序を定義してください。タイムスタンプだけでは衝突しやすく、操作で操作され得るため十分ではありません。

prev_hash を計算する際の競合を避けるには、各ストリームで「最後のハッシュを取得 + 次の行を挿入」の操作を直列化します。一般的な手法は、ストリームヘッドを表す単一行をロックするか、ストリームIDをキーにしたアドバイザリロックを使うことです。目的は、同一ストリームの2人のライターが同じ最後のハッシュを読めないようにすることです。

パーティションやシャーディングは「最後の行」がどこにあるかに影響します。監査データをパーティションする予定があるなら、ストリームキーと同じパーティションキーを使って各チェーンを1つのパーティション内に完全に保持してください（例：テナントID）。そうすれば、テナントチェーンは後でサーバー間を移動しても検証可能なままです。

調査時にチェーンを検証する方法

ハッシュ連鎖は、誰かが尋ねたときにチェーンが保持されていることを証明できて初めて役に立ちます。最も安全な方法は読み取り専用の検証クエリ（またはジョブ）で、各行のハッシュを格納データから再計算し、記録されたハッシュと比較することです。

オンデマンドで実行できるシンプルな検証器

検証器は、各行の期待されるハッシュを再構築し、各行が前の行にリンクしているかを確認し、何か問題があればフラグを立てるべきです。

以下はウィンドウ関数を使った一般的なパターンです。列名は自環境に合わせて調整してください。

WITH ordered AS (
  SELECT
    id,
    created_at,
    actor_id,
    action,
    entity,
    entity_id,
    payload,
    prev_hash,
    row_hash,
    LAG(row_hash) OVER (ORDER BY created_at, id) AS expected_prev_hash,
    /* expected row hash, computed the same way as in your insert trigger */
    encode(
      digest(
        coalesce(prev_hash, '') || '|' ||
        id::text || '|' ||
        created_at::text || '|' ||
        coalesce(actor_id::text, '') || '|' ||
        action || '|' ||
        entity || '|' ||
        entity_id::text || '|' ||
        payload::text,
        'sha256'
      ),
      'hex'
    ) AS expected_row_hash
  FROM audit_log
)
SELECT
  id,
  created_at,
  CASE
    WHEN prev_hash IS DISTINCT FROM expected_prev_hash THEN 'BROKEN_LINK'
    WHEN row_hash IS DISTINCT FROM expected_row_hash THEN 'HASH_MISMATCH'
    ELSE 'OK'
  END AS status
FROM ordered
WHERE prev_hash IS DISTINCT FROM expected_prev_hash
   OR row_hash IS DISTINCT FROM expected_row_hash
ORDER BY created_at, id;

「壊れているかどうか」以外にも、ギャップ（IDの範囲に欠落がないか）、順序の乱れ、実際のワークフローと一致しない疑わしい重複などをチェックする価値があります。

検証結果を不変イベントとして記録する

クエリを実行して結果をチケットに埋めるだけではなく、検証結果は別のappend-onlyテーブル（例：audit_verification_runs）にランタイム、検証器のバージョン、実行者、検査した範囲、壊れたリンク数やハッシュ不一致数などとともに保存してください。

これにより二重のトレイルができます：監査ログが無傷であるだけでなく、それを定期的にチェックしていることを示せます。

実用的な頻度としては、監査ロジックに触れるデプロイ後、アクティブなシステムでは夜間、そして計画された監査の前には必ず実行する、などが考えられます。

改ざん検知を壊す一般的な失敗

ほとんどの失敗はハッシュアルゴリズム自体ではなく、例外やギャップによって人々が反論の余地を持つ点にあります。

信頼を失う最速の方法は監査行の更新を許すことです。一度でも「今回はこれだけ」と許すと、前例と履歴を書き換えるための実際の道筋を作ってしまいます。修正が必要なら、新しい監査イベントを追加してその修正を説明し、元の行は残してください。

ハッシュ連鎖は不安定なデータをハッシュすると失敗します。JSONはよくある落とし穴です。JSON文字列をハッシュすると、キー順や空白、数値の書式など無害な差分でハッシュが変わり、検証がノイジーになります。正規化された形（フィールドの正規化、jsonb、または一貫したシリアライズ）を好んでください。

防御可能なトレイルを損なう他のパターン：

• ペイロードだけをハッシュしてコンテキスト（タイムスタンプ、アクター、オブジェクトID、アクション）を省く。
• 変更をアプリ側でしか捕捉せず、データベースが常に一致すると仮定する。
• ビジネスデータを書き込み、同時に監査履歴も変更できる1つのデータベースロールを使う。
• チェーン内で prev_hash を NULL 許容にして明確なルールを文書化していない。

職務分離は重要です。同じロールが監査イベントを挿入でき、かつそれを変更できると、改ざん検知は制御ではなく約束になってしまいます。

防御可能な監査トレイルのためのクイックチェックリスト

防御可能な監査トレイルは変更しにくく、検証しやすいべきです。

アクセス制御から始めましょう：監査テーブルは実務上append-onlyにすること。アプリケーションロールは挿入（通常は読み取りも）できるが更新や削除はできないようにし、スキーマ変更は厳しく制限します。

各行が調査者の質問に答えられるようにします：誰が行ったか、いつ（サーバー側タイムスタンプ）、何が起きたか（明確なイベント名と操作）、何が影響を受けたか（エンティティ名とID）、どうつながるか（request/correlation idとtransaction id）。

次に整合性レイヤーを検証します。簡単なテストはセグメントを再生し、各 prev_hash が前行のハッシュと一致するか、保存されたハッシュが再計算されたものと一致するかを確認することです。

運用面では、検証を通常のジョブとして扱ってください：

• 定期的な整合性チェックを実行し、合否結果と範囲を保存する。
• 不一致、ギャップ、壊れたリンクをアラート化する。
• 保持期間をカバーするためにバックアップを十分に保持し、監査履歴が早期に「クリーンアップ」されないよう保持ポリシーをロックダウンする。

例：コンプライアンスレビューで疑わしい編集を見つける

よくあるケースは返金の争いです。顧客が$250の返金を承認されたと主張するが、システム上は$25になっている。サポートは承認が正しいと言い、コンプライアンスは説明を求めます。

まず相関ID（注文ID、チケットID、または refund_request_id）と時間窓で検索範囲を絞ります。その相関IDの監査行を取り出し、承認時間の前後を範囲に入れます。

探すべきは、リクエスト作成、返金承認、返金額の設定、そしてその後の更新のフルセットです。改ざん検知設計があれば、シーケンスが完全に保たれているかもチェックします。

シンプルな調査フロー：

• 相関IDで監査行を時間順に全て取り出す。
• 各行のハッシュを保存されたフィールド（prev_hash を含む）から再計算する。
• 再計算したハッシュと保存されたハッシュを比較する。
• 最初に異なる行を特定し、その後の行も失敗しているかを確認する。

もし誰かが1行を編集して（たとえば金額を250から25に変えた）いた場合、その行のハッシュは一致しなくなります。次の行は前のハッシュを含めるため、不一致は通常前方へ連鎖します。この連鎖が指紋です：事後に監査記録が改ざんされたことを示します。

チェーンが教えてくれること：編集が発生したこと、チェーンが最初に壊れた場所、影響を受けた行の範囲。チェーンだけでは教えてくれないこと：誰が編集したか、上書きされた場合の元の値、他のテーブルが同様に変更されたかどうか。

次のステップ：安全に展開し、メンテナブルに保つ

監査トレイルを他のセキュリティコントロールと同様に扱ってください。小さいステップでロールアウトし、動作を確認してから範囲を拡大します。

まず、争点になったときに最も害が大きい操作をカバーしましょう：権限変更、支払い、返金、データエクスポート、手動オーバーライド。これらをカバーしたら、コア設計を変えずに低リスクなイベントを追加してください。

監査イベントの契約を書き出してください：どのフィールドを記録するか、各イベントタイプの意味、ハッシュの計算方法、検証の実行方法。これらのドキュメントをデータベースマイグレーションの横に置き、検証手順を再現可能に保ちます。

リストアの演習は重要です。調査はしばしばライブシステムではなくバックアップから始まるため、定期的にテストデータベースに復元し、チェーンの端から端まで検証してください。復元後に同じ検証結果を再現できない場合、改ざん検知は正当性を主張しにくくなります。

内部ツールや管理ワークフローをAppMaster (appmaster.io) で構築しているなら、サーバー側処理で監査イベントの書き込みを標準化することで、イベントスキーマと相関IDが機能間で一貫するようになり、検証や調査がずっと簡単になります。

このシステムのためにメンテナンス時間を確保してください。チームが新機能を出す際にイベント追加やハッシュ入力の更新、検証ジョブやリストア演習の継続を忘れると、監査トレイルは静かに失敗します。