PostgreSQLでpgauditによるデータベース監査をマスターする：全変更の追跡とシステムセキュリティの強化 – ITFROMZERO

Table of Contents

なぜPostgreSQLのデフォルトログだけでは不十分なのか？

DevOpsエンジニアやデータベース管理者（DBA）なら、朝出勤して重要なusersテーブルが消えていたり、注文データが勝手に書き換えられていたりするのに、システムログを確認しても抽象的な情報しか残っていないという、笑えない状況を経験したことがあるでしょう。PostgreSQLにはlog_statementというパラメータがありますが、これをallに設定すると、システム内部のクエリまですべて記録されるため、わずか数時間でログファイルが数十GBに達してしまうことがあります。

問題は、詳細さが必要である一方で、選択的であるべきだということです。誰がテーブルを削除したのか、いつ実行したのか、誠にどのレコードに影響を与えたのかを正確に把握する必要があります。これは、監査（Audit）や、PCI-DSS、SOC2といった厳しいセキュリティ基準を満たすために必須の要件です。そこで、代替案として私が選んだのがpgaudit（PostgreSQL Audit Extension）です。

pgaudit — データベースの隅々まで監視する「神の目」

通常のロギングメカニズムとは異なり、pgauditはPostgreSQLの標準ロギングインフラを介して、データベース上のアクションを非常に詳細に記録できます。私の導入経験では、pgauditによるパフォーマンスのオーバーヘッドは通常5〜7%程度であり、得られるセキュリティ上のメリットを考えれば十分に許容できる範囲です。

理解しておくべき2つの主要なモードがあります：

セッション監査ロギング（Session Audit Logging）: セッション中の特定のユーザーの全活動を記録します。これは、特権アカウント（Superuser）を監視する最も一般的な方法です。
オブジェクト監査ロギング（Object Audit Logging）: 機密性の高いテーブルに焦点を当てます。例えば、credit_cardsテーブルを誰が触ったかだけを追跡し、他の些末なログテーブルは無視して負荷を抑えるといった使い方が可能です。

大きなメリットは、pgauditが既存のログメカニズムを置き換えるのではなく並行して動作するため、ELKなどのツールを使用したログの集中管理が非常にシンプルになることです。

サーバーへのpgauditのインストール手順

以下は、Ubuntu 22.04とPostgreSQL 15で行った手順です。他のバージョンの場合は、バージョン番号を適宜読み替えてください。

1. パッケージのインストール

まず、OSの公式リポジトリからこの拡張機能をダウンロードします：

sudo apt update
sudo apt install postgresql-15-pgaudit

2. システム設定への登録

インストールしただけでは、pgauditはすぐには動作しません。PostgreSQLの起動時にロードされるように、postgresql.conf（通常は/etc/postgresql/15/main/postgresql.conf）を編集する必要があります。

shared_preload_librariesの行を探し、pgauditを追加します：

shared_preload_libraries = 'pgaudit'

その後、設定を反映させるためにサービスを再起動します。再起動後にサービスのステータスを確認するのを忘れないでください：

sudo systemctl restart postgresql

3. データベース内での拡張機能の有効化

最後のステップとして、psqlにログインし、対象のデータベースに対して拡張機能を作成します：

CREATE EXTENSION pgaudit;

\dxコマンドを入力して、インストール済み拡張機能リストにpgauditが表示されていることを確認してください。

「正しい人、正しいアクション」を監視するための詳細設定

これはログの氾濫を防ぐための最も重要なセクションです。pgauditはコマンドを複数のグループに分類しています：READ、WRITE、FUNCTION、ROLE、DDL、MISCなど。

私が実務でよく使うケースは、ディスク容量を節約するために構造変更とデータ変更のコマンドのみをログに記録することです：

-- DDL（テーブル作成/削除）、WRITE（挿入/更新）、ROLE（ユーザー作成）のコマンドのみをログに記録
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';

-- PostgreSQLに新しい設定を適用するためにリロードを忘れずに
SELECT pg_reload_conf();

注意：毎分何百万ものクエリが発生するテーブルに対してREADを有効にすると、ログファイルが猛烈な勢いで肥大化します。慎重に検討してください！

ちょっとしたヒント： 膨大なログを処理する場合や、クエリのエラーを確認するために顧客データをCSVからJSONに変換する必要がある場合、私はよくtoolcraft.app/ja/tools/data/csv-to-jsonにあるツールを使用します。ブラウザ上で完結するため、機密データがサーバーに送信されることがなく、DBエンジニアにとって非常に安全です。

「カメラ」が動いているかテストしてみる

テーブルを作成して削除し、ログに何が表示されるか確認してみましょう：

CREATE TABLE secret_info (id serial, content text);
INSERT INTO secret_info (content) VALUES ('お客様の機密データ');
DROP TABLE secret_info;

/var/log/postgresql/postgresql-15-main.logにあるログファイルを開くと、次のような「お宝」のような行が見つかるはずです：

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.secret_info,CREATE TABLE secret_info (id serial, content text);,<not logged>
AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.secret_info,INSERT INTO secret_info (content) VALUES ('お客様の機密データ');,<not logged>
AUDIT: SESSION,3,1,DDL,DROP TABLE,TABLE,public.secret_info,DROP TABLE secret_info;,<not logged>

情報は非常に透明です。どのコマンドがsecret_infoテーブルを削除したのか、そしてそれがいつなのかが明確にわかります。もう言い逃れはできません！

パフォーマンスとストレージに関するサバイバル術

セキュリティを重視するあまり、サーバーをダウンさせてはいけません。最適化の方法を知らなければ、連続的なログ記録はディスクI/Oを使い果たしてしまいます：

スマートなログフィルタリング: pgaudit.log = 'all'は、短時間の深刻なエラーデバッグが必要な場合のみ有効にしてください。
ログローテーションの設定: PostgreSQLが日付やサイズ（例：100MB）に応じて自動的にログファイルを分割するように設定してください。そうしないと、一晩でディスクがいっぱいになり、サーバーが停止してしまいます。
ログの外部転送: 本番環境では、rsyslogやFluentdを使用して、ELKやGrafana Lokiにログを集約してください。これにより、データベースサーバーに負荷をかけずに迅速な検索が可能になります。

まとめ

pgauditの導入は、PostgreSQLシステムのセキュリティをアップグレードするためのプロフェッショナルな一歩です。曖昧さを排除し、デフォルトのログを漁るよりも何倍も速くトラブルシューティングを行うことができます。

この記事がデータベース監査に取り組む皆さんの自信につながれば幸いです。インストール時にエラーが発生した場合は、お気軽にコメントを残してください。すぐにお答えします！