存取稽核與使用者行為日誌記錄

想像一下，你的資料庫就像是一間有超嚴格會員規則的俱樂部。只有自己人才可以進來，但我們還是想知道誰來過、什麼時候來、待了多久、做了什麼。這就是為什麼 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

這有什麼用？比如說，你可以在日誌裡看到你的經理凌晨三點用錯密碼連了兩個小時。XD

日誌分析：可以發現什麼？

設定好 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，活躍了兩分半鐘。

實戰範例：怎麼用稽核

我們來看幾個稽核和日誌記錄很有用的情境。

情境 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：查詢效能分析

想知道為什麼伺服器變慢？最簡單的方法就是短時間（比如一小時）開 log_statement = 'all'，收集日誌，然後看看伺服器都在忙什麼。

最佳實踐

不要什麼都記錄。 把 log_statement 設成只記錄重要的動作——DDL 和資料變更。

自動化日誌分析。 用 script 或工具定期讀取和分析日誌，像 grep、awk 或 ELK Stack（Elasticsearch, Logstash, Kibana）。

管理日誌大小。 用 PostgreSQL 參數或作業系統工具（像 Linux 的 logrotate）設定日誌輪替。

希望你現在已經是自己資料庫的 sheriff 了！日誌記錄和稽核不只是保護，也是搞懂系統的好方法。之後我們會看看這些知識怎麼用在實際專案裡！