6.9 SQLでの監査ログの分析

一般的に、分析用のテーブルを作成し、そのテーブルに監査ログをロードすると、高速で容易に監査ログを分析することができます。
おそらく分析用のテーブルは更新されないので、そのテーブルをUNLOGGEDテーブルとして作成することを推奨します。ロードするときに先行書き込みログ(WAL)を出力せずレプリケートされなくなるので、他の業務を阻害せずに、より高速にロードできるからです。

ロードする監査ログファイルの特定方法

特定の時間帯の監査ログを分析するには、その時間帯の監査ログを含むすべてのファイルをロードしてください。
ただし、最新の更新日付の専用ログファイルにはアクセスしないでください。なぜならば、書き込み途中の不完全な監査ログデータが含まれるからです。

ロード方法

監査ログはcsv形式で出力されるため、COPY FROM文やpgx_loaderコマンドを使ってロードすることができます。
以下に専用ログファイルに出力したSession Audit Loggingをロードする利用例を示します。

テーブルの作成

監査ログの参照に必要なカラムを持つテーブルを定義します。

$ psql
=# CREATE UNLOGGED TABLE auditlog (
header text,
class text,
sql_start_time timestamp with time zone,
remote_host_name text,
backend_process_id integer,
application_name text,
session_user_name text,
database_name text,
virtual_transaction_id text,
statement_id integer,
substatement_id integer,
command_tag text,
sqlstate text,
object_type text,
object_name text,
error_message text,
sql text,
parameter text
);

インデックスの作成
例えばタイムスタンプに対してインデックスを作成することで、時系列での分析に役立ちます。
```
$ psql
=# CREATE INDEX auditlog_time_index ON auditlog (sql_start_time);
```
監査ログのロード
COPY FROM のFROM句に専用ログファイルを指定します。
```
$ psql
=# COPY auditlog FROM
'pgaudit-2024-02-02_150000.log' WITH CSV DELIMITER ',';
```
PROGRAM句を利用することで、複数の専用ログファイルをまとめてロードすることもできます。
以下の例では、ファイル名の時刻部分をワイルドカードで指定することで、1日分の監査ログをロードします。
```
$ psql
=# COPY auditlog FROM
PROGRAM 'cat pgaudit-2024-02-02_*' WITH CSV DELIMITER ',';
```
```
$ psql
=# COPY auditlog FROM
PROGRAM 'type pgaudit-2024-02-02_*' WITH CSV DELIMITER ',';
```

監査ログの分析

監査ログをロードしたテーブルにアクセスします。
ロードしたレコードは時系列順に並んでいませんが、タイムスタンプでソートすることが可能です

$ psql
=# SELECT * FROM auditlog ORDER BY sql_start_time;
 header        |  class |        sql_start_time     | remote_host_name |  ...
---------------+--------+---------------------------+------------------+ ...
AUDIT: SESSION | DDL    | 2024-02-02 15:00:49+09    | ::1              |  ...
AUDIT: SESSION | SYSTEM | 2024-02-02 15:00:58+09    |                  |  ...

スケーラブルな監査ログ機能利用時の注意点

スケーラブルな監査ログ機能を有効にしているときは、同じ時間帯の専用ログファイルがparallel_loggersに指定した数だけ存在するため、必要な専用ログファイルをすべてロードしてください。
log_rotation_ageを無効にしたときには、ターゲットとした時間帯前後の時間帯を分析しないでください。すべての監査ログがロードされていない可能性があるためです。
例えば、下図のように、11:00～12:00の監査ログを分析の対象としているときに、loggerプロセス0が10:00～13:00に出力したファイルと、loggerプロセス1が10:30～12:30に出力したファイルをロードしたとします。ロードしたテーブルには、10:00～13:00までの監査ログレコードが含まれているように見えますが、一部の監査ログ（loggerプロセス1が10:00～10:30と12:30～13:00に出力した監査ログ）はロードされていません。
log_rotation_ageを有効にしていると、専用ログファイルの切り替わりのタイミングがすべてのloggerプロセスで一致するため、このような誤った分析を予防することができます。