SQL Academy · レッスン

トリガーによるテーブル監査

AFTER INSERT/UPDATE/DELETEトリガーでaudit_logテーブルに記録する監査証跡を構築します。

「トリガーによるテーブル監査」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン4/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

監査する理由

監査ログによって、「誰が、いつ、何を変更したか」を確認できます。コンプライアンス（HIPAA、SOX、GDPR における消去権の調査）に必要であり、運用上のフォレンジックにも役立ちます。

監査テーブル

1 つの中央テーブルですべての変更を記録します。

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  user_name TEXT NOT NULL DEFAULT CURRENT_USER,
  table_name TEXT NOT NULL,
  action TEXT NOT NULL,             -- INSERT, UPDATE, DELETE
  row_id TEXT,
  old_data JSONB,
  new_data JSONB
);

監査トリガー関数

多くのテーブルで再利用できる 1 つの関数です。

CREATE OR REPLACE FUNCTION audit_row()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, row_id, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id::TEXT, OLD.id::TEXT),
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

テーブルにアタッチする

AFTER INSERT/UPDATE/DELETE のトリガーを設定します。

CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_row();

to_jsonb(NEW)

to_jsonb を使う方法では、列ごとのコードを記述せずに行全体を汎用的に記録できます。id 列を持つ任意のテーブルで動作します。

実行ユーザーの追跡

アプリが単一の DB ユーザーで実行される場合、CURRENT_USER だけでは不十分です。セッション GUC を介してアプリのユーザーを渡してください。

-- App sets:
SET LOCAL app.user_id = '42';

-- Trigger reads:
INSERT INTO audit_log (... actor_id ...) VALUES (..., current_setting('app.user_id', true)::BIGINT);

特定の列の監査

選択的に監査するには、トリガーの WHEN 句を使用します。

CREATE TRIGGER trg_audit_role_change
AFTER UPDATE OF role ON users
FOR EACH ROW WHEN (OLD.role IS DISTINCT FROM NEW.role)
EXECUTE FUNCTION audit_role_change();

テーブルごとの監査テーブル

別の方法として、実テーブルごとに監査テーブル（例：users_audit）を 1 つ作成し、同じスキーマに監査メタデータを追加します。クエリは簡単になりますが、保守する DDL が増えます。

差分のみの監査

変更された列だけを保存します。

INSERT INTO audit_log (table_name, action, changes)
VALUES (
  TG_TABLE_NAME, TG_OP,
  (SELECT jsonb_object_agg(key, value)
   FROM jsonb_each(to_jsonb(NEW))
   WHERE NEW.* IS DISTINCT FROM OLD.* AND to_jsonb(NEW)->key IS DISTINCT FROM to_jsonb(OLD)->key)
);

監査テーブルのパフォーマンス

負荷の高いシステムでは、監査テーブルが急速に大きくなります。時間でパーティション分割してください。

CREATE TABLE audit_log (...) PARTITION BY RANGE (ts);
CREATE TABLE audit_log_2024_q1 PARTITION OF audit_log
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

パフォーマンス上のトレードオフ

各 insert/update/delete で、追加の行が 1 つ書き込まれるようになります。書き込み負荷が非常に高いテーブルでは、I/O が 2 倍になる可能性があります。テストと監視を行い、このトレードオフに見合うか判断してください。

DB トリガーを使わない場合

イベントバスとの統合が必要な場合は、トリガーではなく NOTIFY または LISTEN でイベントをキューに入れるか、論理レプリケーションや CDC ツール（Debezium）を使用してください。

まとめ

監査トリガーは、履歴を記録する最も簡単な方法です。

to_jsonb(NEW/OLD) を使う汎用関数を 1 つ用意する
監査対象のすべてのテーブルにアタッチする
監査テーブルを時間でパーティション分割する
GUC を介してアプリの実行ユーザーを明示的に追跡する

クイックチェック

監査トリガー関数内で、新しい行を汎用的に JSONB として取得するにはどうしますか。

よくある質問

「トリガーによるテーブル監査」レッスンは無料ですか？

はい。「トリガーによるテーブル監査」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「トリガーによるテーブル監査」で何を学びますか？

AFTER INSERT/UPDATE/DELETEトリガーでaudit_logテーブルに記録する監査証跡を構築します。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン4/4です。

「トリガーによるテーブル監査」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る