はじめに

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

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

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

データベース監査ってなに？

データべース監査とは、データベースに対する不正なアクセスがないかをチェックする運用を指します。
具体的には、

①定期的に不正なアクセスが発生していないか、その予兆がないかをチェックし、
②問題の検出および対策、もしくは予防措置を行います。

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

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

pgauditを使ったセッション監査まとめ

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

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

監査対象のコマンド

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

監査種別	記録する内容
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の公式ドキュメントは以下になります。

pgauditのセッション監査の設定方針

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

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

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

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

具体的な設定例

それでは具体的な設定例を見ていきましょう。
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」の場合

postgres=# ALTER ROLE postgres SET pgaudit.log='ALL';
ALTER ROLE

管理者ユーザ「adminuser」の場合

postgres=# ALTER ROLE adminuser SET pgaudit.log='DDL,ROLE,MISC';
ALTER ROLE

アプリケーションユーザ「appuser」の場合

postgres=# ALTER ROLE appuser SET pgaudit.log='DDL,ROLE';
ALTER ROLE

読み取り専用ユーザ「readuser」の場合

postgres=# ALTER ROLE readuser SET pgaudit.log='NONE';
ALTER ROLE

設定の確認は、以下のように実施できます。

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ログが出力されます。

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ログが出力されます。

postgres=> vacuum t_test;

AUDIT: SESSION,3,1,MISC,VACUUM,,,vacuum t_test;,<not logged>

DBユーザ「appuser」でCREATEコマンドを実行
「DDL」の監査対象としてAUDITログが出力されます。

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の技術記事を掲載しておりますので、ぜひこちらもご覧ください。

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

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