PostgreSQLの監査はpgauditで！DBユーザ単位のセッション監査を使ってみた！

# はじめに
この記事は「NTTテクノクロス Advent Calendar 2023」の10日目の記事です。

こんにちは！NTTテクノクロスの中村です。

この記事は、pgauditを使ってPostgreSQLのデータベース監査を実施しようと考えているけど、具体的にどう使えばいいのか指針が欲しい！というニーズにお応えする記事です。
pgauditを使う際の注意点についても触れていきます。

# データベース監査ってなに？
データべース監査とは、データベースに対する不正なアクセスがないかをチェックする運用を指します。
具体的には、
- ①定期的に不正なアクセスが発生していないか、その予兆がないかをチェックし、
- ②問題の検出および対策、もしくは予防措置を行います。

チェック自体は自動化もしくは人の手によって行いますが、<font color="red">監査を行うためには「どのDBユーザが」「いつ」「何に対して」「どんな操作を行ったか」の情報が必要になります</font>ので、これらの情報を収集、蓄積する必要があります。

pgauditはこれらの「情報の収集と蓄積」をサポートする拡張機能です。
オンプレだけでなく、「Amazon Aurora PostgreSQL」や、「Amazon RDS for PostgreSQL」、「Azure Database for PostgreSQL」などのクラウドでも利用可能です。

# pgauditを使ったセッション監査まとめ
それでは最初に、pgauditのセッション監査についてまとめていきます。
セッション監査とは、データベースに接続してから実施した「操作内容」を記録することを指します。操作内容は主にSQL文を指しますが、SET句やPostgreSQLのコマンド群も対象にできます。

- <b>セッション監査はDBユーザ単位に設定できる</b>
pgauditのインストール直後はデフォルトで監査はoff（postgresql.conf のパラメータ pgaudit.log = none）になっていますので、<font color="red">ALTER ROLE文を使ってDBユーザ単位に監査の設定を行います。</font>
- <b>セッション監査では監査対象のコマンドを設定できる</b>
以下「監査対象のコマンド」に記載の通り、SELECTやDDLなど、監査の対象としたいコマンドを指定できます。ただし、コマンドごとではなく、<font color="red">いくつかのまとまりの単位で指定する必要があります。</font>
- <b>セッション監査の結果はPostgreSQLのサーバログに出力される</b>
セッション監査のログはPostgreSQLのサーバログに他のログと一緒に出力されます。<font color="red">ログには「AUDIT」という文字列が含まれています</font>ので、ユーザがログを分類することは比較的容易です。



## 監査対象のコマンド
監査対象のコマンドは、以下のようなまとまりで指定することができます。

|監査種別|記録する内容|
|--------|----------|
|READ|SELECT、（テーブルからデータを吸い出す）COPY|
|WRITE|INSERT、DELETE、UPDATE、TRUNCATE、（テーブルにデータを入れる）COPY|
|FUNCTION|関数呼び出し、DOブロック|
|ROLE|GRANT、REVOKE、CREATE ROLE、DROP ROLE、ALTER ROLE|
|DDL|ROLE関係を除く全DDL|
|MISC|VACUUM、SET、CHECKPOINT、FETCH、DISCARD|
|MISC_SET|その他のSET|
|ALL|全操作|
|NONE|記録しない（デフォルト）|

pgauditの公式ドキュメントは以下になります。

https://github.com/pgaudit/pgaudit

# pgauditのセッション監査の設定方針
ここから本番になりますが、セッション監査はDBユーザ単位に設定することが可能なため、以下のようにユーザの用途によって監査種別を使い分けるようにしましょう。

|ユーザ種別（用途）|監査種別|考え方|
|--------------------------|--------|------------------------|
|スーパーユーザ|ALL|全操作を記録|
|管理者ユーザ|ROLE、DDL、MISC|VACUUMなどメンテナンス系も記録|
|アプリケーションユーザ|ROLE、DDL|通常あまり実施しない操作を記録|
|参照専用ユーザ|NONE|記録しない|

留意するのは「どのコマンドを監査したいのかを明確にしておくこと」です。
ユーザ種別ごとに必要な記録だけを対象にします。アプリケーションユーザでも頻繁にDDLを実行するケースもありますので、適宜調整しましょう。
指定する監査種別の"くくり"は結構おおまかなので、思ったよりログが大量に出てしまう、ということが良くあります。

例えば、参照専用ユーザの場合、そもそもDMLやDDLは実行してもエラーになるようにオブジェクトの権限を与えないように設定しておくことで、DMLやDDLの監査を不要にすることができます。（<font color="red">個人情報を含むデータが存在する場合は、該当情報に対しては参照権限も与えない、またはSELECTも監査の対象にする、といったことが求められるケースもあります</font>）
というわけで、監査の設定はユーザ設計とも密接にかかわっています。

# 具体的な設定例
それでは具体的な設定例を見ていきましょう。
pgauditはRPMのインストール後、postgresql.conf の shrared_preload_libraries='pgaudit' としてPostgreSQLを再起動し、利用するデータベース上でCREATE EXTENSION pgaudit;を行うことで利用可能になります。

## postgresql.conf の pgauditパラメータは全部デフォルトで！
以下に、全ての設定パラメータとそのデフォルト値を記載します。
まずはデフォルトで設定して、出力されたログを確認してからチューニングする方法でよいと思います。

|パラメータ名|デフォルト値|備考|
|------------------------------|-----|-----------------------------------|
|pgaudit.log|none|監査対象は全体で設定せず、DBユーザ単位で設定するので「none」|
|pgaudit.log_catalog|on|pg_xxxなどのカタログも監査の対象とする|
|pgaudit.log_client|off|デバッグ用なのでoff|
|pgaudit.log_level|log|pgaudit.log_client=on の時のみ有効になる|
|pgaudit.log_parameter|off|SQLに渡されたパラメータは出力しない。する場合onにする|
|pgaudit.log_parameter_max_size|0|パラメータ情報の出力サイズ制限はしないので0|
|pgaudit.log_relation|off|テーブルごとに監査ログを分けて出力しない|
|pgaudit.log_rows|off|SQLによって取得または更新された行を出力しない|
|pgaudit.log_statement|on|実行されたSQL文を出力する|
|pgaudit.log_statement_once|off|一つのSQL文で複数の監査ログが出力される場合でも、SQL文は毎回出力する|
|pgaudit.role|-|オブジェクト監査用|

なお、pgaudit のパラメータはインストールしただけでは postgresql.conf には自動で追加されません。わかりやすくするため、デフォルト値にコメントを付ける形で、postgresql.conf に追記しておくことをオススメします。

## ALTER ROLE 文でDBユーザごとの監査設定を行う
それでは、それぞれのDBユーザごとに監査設定を行う例を記載します。
操作は全て postgres ユーザで実施しています。

※動作確認はオンプレの以下の環境で実施しています。
|種別|バージョン|
|----|---------|
|OS|RHEL9.1|
|PostgreSQL|14.5|
|pgaudit|1.6.2|

- スーパーユーザ「postgres」の場合
```sql
postgres=# ALTER ROLE postgres SET pgaudit.log='ALL';
ALTER ROLE
```
- 管理者ユーザ「adminuser」の場合
```sql
postgres=# ALTER ROLE adminuser SET pgaudit.log='DDL,ROLE,MISC';
ALTER ROLE
```
- アプリケーションユーザ「appuser」の場合
```sql
postgres=# ALTER ROLE appuser SET pgaudit.log='DDL,ROLE';
ALTER ROLE
```
- 読み取り専用ユーザ「readuser」の場合
```sql
postgres=# ALTER ROLE readuser SET pgaudit.log='NONE';
ALTER ROLE
```

設定の確認は、以下のように実施できます。
```sql
postgres=# SELECT rolname,rolconfig FROM pg_roles;
 rolname  |           rolconfig
----------+-------------------------------
 postgres | {pgaudit.log=ALL}
 adminser | {"pgaudit.log=DDL,ROLE,MISC"}
 appuser  | {"pgaudit.log=DDL,ROLE"}
 readuser | {pgaudit.log=NONE}
(4 rows)
```

## ログ出力例
- DBユーザ「postgres」でSELECTコマンドを実行
「READ」の監査対象としてAUDITログが出力されます。
```sql
postgres=# select * from pg_class limit 1;
```
```
AUDIT: SESSION,2,1,READ,SELECT,,,select * from pg_class limit 1;,<not logged>
```

- DBユーザ「adminuser」でVACUUMコマンドを実行
「MISC」の監査対象としてAUDITログが出力されます。
```sql
postgres=> vacuum t_test;
```
```
AUDIT: SESSION,3,1,MISC,VACUUM,,,vacuum t_test;,<not logged>
```

- DBユーザ「appuser」でCREATEコマンドを実行
「DDL」の監査対象としてAUDITログが出力されます。
```sql
postgres=> create table t_test2 (col1 int);
```
```
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.t_test2,create table t_test2 (col1 int);,<not logged>
```

# まとめ
pgaudit のセッション監査をDBユーザ単位に行う方法について解説しました。
監査設定は、DBユーザ設計と大きく関わってきますので、監査を行うか否か、行うとしたらDBユーザの使い分けは整理されているか、という点に留意するといいですね。

本記事は以上となります。

NTTテクノクロスのWEBサイトでも PostgreSQLの技術記事を掲載しておりますので、ぜひこちらもご覧ください。


https://www.ntt-tx.co.jp/column/postgresql_blog.html

引き続き、NTTテクノクロス Advent Calendar 2023 をお楽しみください。