アクセス監査とユーザーアクションのログ記録

イメージしてみて、君のデータベースは超厳しい会員制クラブみたいなもの。入れるのは身内だけ。でも、誰が来たのか、いつ来たのか、どれくらい滞在したのか、何をやったのか知りたいよね。まさにそのためにPostgreSQLの監査があるんだ。これを使うと：

1. ユーザーのアクションを追跡できる。 例えば、誰がいつ大事なクエリを実行したか分かる。

2. 怪しいアクティビティを見つけられる。 例えば、本来アクセスできないデータを読もうとしてる人がいるかも。

3. 法律や業界標準を守れる。 多くの業界（金融や医療とか）では、ユーザーアクションの詳細な監査が必要なんだ。

4. システムの中身を理解できる。 ログ記録で、どんなクエリがよく実行されてるか、どこがボトルネックか、パフォーマンスをどう最適化するか分かるよ。

ログ記録パラメータの設定

PostgreSQLでは、log_statementやlog_connectionsみたいな設定パラメータでアクション監査ができる。詳しく見てみよう。

1. log_statement: 何を記録する？

log_statementパラメータは、どのSQLクエリをログに記録するか決めるんだ。システムで何が起きてるか把握するのに超便利。

log_statementの値のバリエーション：

• none — 何も記録しない（スリルを味わいたい人向け）。
• ddl — データベース構造を変えるコマンドだけ記録（例：CREATE TABLE）。
• mod — データを変更するコマンドを記録（例：INSERT、UPDATE、DELETE）。
• all — ぜんぶ記録。

設定例： このパラメータを変えるにはpostgresql.confファイルを編集する必要があるよ：

# すべてのSQLクエリをログ記録する設定
log_statement = 'all'

その後、変更を保存してPostgreSQLサーバーを再起動しよう：

pg_ctl reload

これで君のクラブは、ドリンク注文からダンスムーブまで、すべてのアクションをログに残すようになるよ。

2. log_connections: 誰がクラブに入ってる？

log_connectionsパラメータは、データベースへの新しい接続ごとにログを記録するよ。さらに、log_disconnectionsっていう関連パラメータもあって、接続が切れたタイミングも記録できる。

設定例： またまたpostgresql.confファイルを編集しよう：

# 接続をログ記録
log_connections = on

# 切断もログ記録
log_disconnections = on

これで何が分かるかって？例えば、マネージャーが2時間も間違ったパスワードで接続しようとしてたのがログで分かる。しかも夜中の3時にね。

ログ分析：何が見つかる？

log_statementやlog_connectionsパラメータを設定したら、PostgreSQLはログを書き始める。例えばlog_statement = 'mod'が有効なときのログファイルはこんな感じ：

2023-11-01 12:45:01 UTC [12345] LOG:  connection authorized: user=admin database=university
2023-11-01 12:46:15 UTC [12345] STATEMENT:  INSERT INTO students (name, age) VALUES ('Alice', 22);
2023-11-01 12:47:30 UTC [12345] STATEMENT:  UPDATE students SET age = 23 WHERE name = 'Alice';
2023-11-01 12:48:45 UTC [12345] LOG:  disconnection: session time: 2:45 connection: 1/5

面白いポイント：

1. 誰が接続したか： user=admin database=university — これはうちのボス。
2. 何をしたか： Aliceって名前の行を挿入して、年齢を更新した。
3. いつ退出したか： 12:48:45、2分半のアクティビティの後。

実践例：監査をどう使う？

監査やログ記録が役立つシナリオをいくつか見てみよう。

シナリオ1：データ変更の追跡

誰かがstudentsテーブルのレコードを変更してる気がする。監査の設定方法はこう：

1. log_statement = 'mod'を設定して、すべての変更クエリを記録する。
2. ログを分析する：

cat /var/log/postgresql/postgresql.log | grep "UPDATE students"

これで誰がいつどんな変更をしたか分かるよ。

シナリオ2：怪しい接続の検出

ログにいろんなIPアドレスから頻繁に接続があるなら、ちょっと怪しいかも。分析には：

1. 接続ログ（log_connections）を使う。
2. IPアドレスでフィルタリング：

cat /var/log/postgresql/postgresql.log | grep "connection authorized"

シナリオ3：クエリパフォーマンスの分析

なんでサーバーが重いのか知りたい？簡単な方法は、短時間（例えば1時間だけ）log_statement = 'all'を有効にして、ログを集めて、どこでサーバーが一番時間を使ってるか見てみよう。

ベストプラクティス

全部ログしないこと。 log_statementは重要なアクションだけ（DDLやデータ変更）を記録するように設定しよう。

ログ分析を自動化しよう。 grep、awk、ELK Stack（Elasticsearch, Logstash, Kibana）みたいなツールやスクリプトで、定期的にログを読んで分析しよう。

ログサイズを管理しよう。 PostgreSQLのパラメータやOSのツール（Linuxならlogrotateとか）でログのローテーションを設定しよう。

これで君もデータベースのシェリフ気分だね。ログ記録と監査は守りだけじゃなく、システムの中身をもっとよく知る最高の方法でもあるよ。実際のプロジェクトでどう使うか、これから見ていこう！