💽

Amazon RDS for PostgreSQL の監査ログを整理する

2025/01/26に公開

AWS

PostgreSQL

aurora

tech

概要

こんにちは🙌
今回は、Amazon RDS for PostgreSQL（Amazon Aurora PostgreSQL）での監査ログ設定について解説します。多くのシステムでは、コンプライアンスやセキュリティの要件として監査ログの取得が求められます。

監査ログの設定には、以下のような検討が必要です：

RDS設定の変更
パラメータグループの適切な設定
データベースユーザーごとの監査レベルの調整

この記事では、実際のユースケースに基づいて、必要な設定手順を詳しく見ていきます。

この記事で扱う内容について

監査ログ機能として、PostgreSQLの標準機能であるlog_statementと拡張機能のpgAuditを扱います
log_statementの出力も便宜上「監査ログ」と呼びます
SQLの詳細な説明は割愛し、監査ログの設定に焦点を当てています

監査ログ出力を考えた時に知っておきたいこと

実現したい要件を満たす設定を入れていく前に、監査ログを出力したいとなった場合に、事前に知っておくと良いことの説明をします。これを知っていないと、ログは出ているけど狙ったログになっているか、本当に必要とされているログまで出ているのか判断することが出来ません。

log_statement vs pgAudit

log_statementは、PostgreSQLに標準で備わっている操作ログ記録のための機能となります。
同じくpgAuditも操作ログを記録するための拡張機能となりますが、何が違うのでしょうか。

PostgreSQL自体がOSSなので、拡張機能であるpgauditももちろんリポジトリが公開されていて、READMEにその違いが書かれています。
https://github.com/pgaudit/pgaudit

Why pgAudit?セクションの部分を日本語訳をしたものを載せておきます。
後半で実際に、試してみながら確認してみたいと思います。

標準ログ機能（log_statement = all）は基本的な文のログ記録には対応していますが、監査に必要な詳細レベルを提供できません。データベースで実行された操作のリストだけでは不十分で、監査人が関心のある特定の文を見つけられる必要があります。標準ログは要求内容を示すのに対し、pgAuditはその要求を満たす過程での詳細に焦点を当てます。
例えば、特定のテーブルが計画されたメンテナンス時間内に作成されたことを確認したい場合を考えます。以下のような動的テーブル作成のコードがあった場合：

DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

標準ログの出力

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

テーブルを見つけるには動的生成のコードを理解する必要があり、理想的ではありません。
一方、pgAuditの出力：

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

DOブロックに加えて、サブステートメント2に文の種類、オブジェクトの種類、完全修飾名を含むCREATE TABLEの全文が記録され、検索が容易になります。
SELECTやDML文のログ記録時、pgAuditは文で参照される各リレーションに対して個別のエントリを記録できます。

つまり、以下のように整理することが出来ます。

log_statement
- ユーザーが実際に発行したSQL文をそのまま記録
- 動的SQLの場合は変数を含んだ状態で記録（例：'CREATE TABLE ' || table_name）
pgAudit
- 実際にデータベースで実行された内容であり、「実際に何が起きたのか」監査のための記録がサブステートメントとして記録される
- 動的SQLの場合、展開後の実際の値を記録（例：CREATE TABLE actual_table_name）

セッション監査 vs オブジェクト監査

セッションごとに、ユーザー別。セッションレベルでは、完全修飾コマンドテキストをキャプチャできます。
オブジェクトごとに、ユーザー別、データベース別。

とAWS公式ドキュメントに書かれていますが、改めて補足を追加します。
pgauditを用いた場合の設定を例として上げています。（log_statementでも、ほぼ同じような設定になるとは思います。）

オブジェクト監査

インスタンス、データベース、テーブル、カラムに対して設定をします。オブジェクトに対しての操作を記録しておくのが、オブジェクト監査となります。
例えば、特定のテーブルを参照した操作は全て記録したいといった場合に有効です。

例：インスタンスレベルで監査を設定する
DBパラメータグループのpgaudit.log値を変更することで設定します。

例：データベースレベルで監査を設定する

ALTER DATABASE <dbname> set pgaudit.log='All'; --Allは、全ての操作を意味します。

セッション監査

セッションに対して設定をする、つまりユーザー（ロール）レベルで設定して操作を記録することがセッション監査となります。
例えば、アプリケーションから利用されるシステムユーザーは、DDLだけを取得する。個人ユーザーについては、全ての操作を記録したいとなった場合に、セッション監査で実現することが可能です。

例：ユーザーレベルで監査を設定する

ALTER ROLE <role_name> SET pgaudit.log='all';

実際に試してみる

環境

Aurora PostgreSQL RDS
エンジンバージョン 16.2

[要件1] log_statementを使って、とにかく全ての操作ログをCloudWatchに出力したい。

全てのSQLを取得したいので、インスタンスに対して設定を入れます。
また、CloudWatchロググループへの出力を有効化したいので、RDSの設定にも変更を加えます。

DBパラメータグループから、log_statementを変更します。
このパラメータは、タイプの適用がDynamicになっているため、変更を保存を押したら再起動なしに即設定内容が反映されます。
RDSの設定を変更して、ログのエクスポートのPostgreSQLログを有効化します。
クエリを実行して、CloudWatchロググループを確認します。
ユーザー作成、データベース作成、作成したユーザーでSELECTを実行したものが全て出力されています。ユーザー作成時のパスワードまでマスクされずに出てしまっているのが気になりますね。

CREATE USER user_a WITH PASSWORD 'Pass123A';
CREATE DATABASE test_db;
SELECT 1;

2025-01-25 XX:XX:XX UTC:[local]:postgres@postgres:[2182]:LOG:  execute sqlx_s_2: CREATE USER user_a WITH PASSWORD 'Pass123A'
2025-01-25 XX:XX:XX UTC:[local]:postgres@postgres:[2182]:LOG:  execute sqlx_s_11: -- データベース作成
	CREATE DATABASE test_db
2025-01-25 XX:XX:XX UTC:[local]:user_a@test_db:[2556]:LOG:  execute sqlx_s_2: select 1

[要件2] pg_auditを使って、データベースの全ての操作ログをCloudWatchに出力したい。

こちらも全てのSQLを取得するため、データベースに対して設定を入れていきます。
既にCloudWatchロググループへの出力は有効化する必要がありますが、先程の要件1で実施した通りですので、省略します。
要件1から引き続き試す場合、ログが混同するため、パラメータlog_statementは、一旦noneにしておくことをおすすめします。

DBパラメータグループから、shared_preload_librariesに pgaudit を追加します。
shared_preload_librariesは、その名の通りですが、RDS for PostgreSQL DB インスタンスにプリロードする共有ライブラリを設定するパラメータとなります。
タイプの適用は、Staticのため再起動が必要となります。
RDSを再起動して、pgauditを読み込む準備が出来ていることを確認します。

SHOW shared_preload_libraries;
shared_preload_libraries 
--------------------------
rdsutils,pg_stat_statements,pgaudit,writeforward
(1 row) 

拡張を読み込みます。
これは現在接続しているデータベースに対して有効になります。

CREATE EXTENSION pgaudit;

DBパラメータグループから、pgaudit.logに all を設定します。
タイプの適用はDynamicなので、変更後はすぐに設定が反映されます。
クエリを実行して、CloudWatchロググループを確認します。
実際に実行したクエリと比較をしてみると、pgAuditでは何が起きたのかという点に着目しているログが出力されていることが分かります。

CREATE USER user_a2 WITH PASSWORD 'Pass123A';
CREATE DATABASE test_db2;
SELECT 1;

2025-01-25 XX:XX:XX UTC:[local]:postgres@test_db:[1481]:LOG:  AUDIT: SESSION,3,1,ROLE,CREATE ROLE,,,CREATE USER user_a2 WITH PASSWORD <REDACTED>,<not logged>
2025-01-25 XX:XX:XX UTC:[local]:postgres@test_db:[1481]:LOG:  AUDIT: SESSION,5,1,DDL,CREATE DATABASE,,,"
	CREATE DATABASE test_db2",<not logged>
2025-01-25 XX:XX:XX UTC:[local]:postgres@test_db:[1481]:LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,select 1,<not logged>

[要件3] pg_auditを使って、ユーザーごとに記録する操作を変えつつログをCloudWatchに出力したい。

ユーザーごとに変えたい場合には、セッション監査を用いることで実現が出来ます。
user_aは、DDLとDMLどちらも取得しておきたい。user_bは、DDLだけを取得したいとします。
要件2から引き続き試す場合、パラメータpgaudit.logは、一旦noneにします。

pgaudit拡張機能の読み込みまでは、要件2と同じであるため省略します。

user_a に対して、pgaudit.log を設定します。

ALTER ROLE user_a set pgaudit.log='DDL,READ,WRITE';

user_b に対して、pgaudit.log を設定します。

ALTER ROLE user_b set pgaudit.log='DDL';

それぞれのユーザーでクエリを実行して、CloudWatchロググループを確認します。
※シーケンス作成やプライマリキーインデックス作成のログも出ますが、ここでは載せていません。

user_a 実行クエリとログ

CREATE TABLE test_table_a (
    id SERIAL PRIMARY KEY,
    note TEXT
);
INSERT INTO test_table_a (note) VALUES ('Test note by user_a');

2025-01-25 XX:XX:XX UTC:[local]:user_a@test_db:[2756]:LOG:  AUDIT: SESSION,2,1,DDL,CREATE TABLE,TABLE,public.test_table_a,"CREATE TABLE test_table_a (
	    id SERIAL PRIMARY KEY,
	    note TEXT
	)",<not logged>
2025-01-25 XX:XX:XX UTC:[local]:user_a@test_db:[2756]:LOG:  AUDIT: SESSION,3,1,WRITE,INSERT,,,INSERT INTO test_table_a (note) VALUES ('Test note by user_a'),<not logged>

user_b 実行クエリとログ

CREATE TABLE test_table_b (
    id SERIAL PRIMARY KEY,
    data VARCHAR(100)
);
INSERT INTO test_table_b (data) VALUES ('Test data by user_b');

2025-01-25 XX:XX:XX UTC:[local]:user_b@test_db:[2949]:LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.test_table_b,"CREATE TABLE test_table_b (
	    id SERIAL PRIMARY KEY,
	    data VARCHAR(100)
	)",<not logged>

[要件4] pg_auditを使って、特定のカラムを読み取る操作があった時にだけ、ログをCloudWatchに出力したい。

テーブル、カラムに対して、オブジェクト監査を設定する場合は、監査ロールを作って、対象とするオブジェクトへのアクセス権限を渡してあげます。

pgaudit拡張機能の読み込みまでは、要件2と同じであるため省略します。

監査ロールを作成します。

CREATE ROLE rds_pgaudit;

DBパラメータグループから、pgaudit.roleに rds_pgaudit を追加します。
テーブル、データを作成していきます。

-- 監査対象のテーブル作成
CREATE TABLE employee_data (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    salary INTEGER,
    personal_number VARCHAR(20),
    department VARCHAR(100)
);
-- テストデータ挿入
INSERT INTO employee_data 
VALUES 
    (1, 'John Doe', 'john@example.com', 50000, 'PN001', 'IT'),
    (2, 'Jane Smith', 'jane@example.com', 60000, 'PN002', 'HR');

監査したいカラムに対してSELECTを実行するためのアクセス許可をrds_pgauditに付与します。

GRANT SELECT (salary) ON employee_data TO rds_pgaudit;

クエリを実行して、CloudWatchロググループを確認します。

-- 監査対象カラムを含むクエリ
SELECT name, salary FROM employee_data;

-- 監査対象カラムを含む更新
UPDATE employee_data 
SET salary = 55000 
WHERE id = 1;

-- 監査対象外のカラムのみの更新（ログは出ない）
UPDATE employee_data 
SET department = 'Engineering' 
WHERE id = 1;

-- 両方含むクエリ
SELECT name, department, salary, personal_number 
FROM employee_data 
WHERE id = 1;

2025-01-25 XX:XX:XX UTC:[local]:postgres@postgres:[4409]:LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.employee_data,"-- 監査対象カラムを含むクエリ
	SELECT name, salary FROM employee_data",<not logged>
2025-01-25 XX:XX:XX UTC:[local]:postgres@postgres:[4409]:LOG:  AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.employee_data,"
	-- 監査対象カラムを含む更新
	UPDATE employee_data 
	SET salary = 50000 
	WHERE id = 1",<not logged>
2025-01-25 XX:XX:XX UTC:[local]:postgres@postgres:[4409]:LOG:  AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.employee_data,"
	-- 両方含むクエリ
	SELECT name, department, salary, personal_number 
	FROM employee_data 
	WHERE id = 1",<not logged>

おわりに

監査ログの設定は、セキュリティとコンプライアンスの重要な要素です。この記事で紹介した手順を参考に、以下の点に注意して実装することをお勧めします

要件に応じて適切な監査方式（log_statement/pgAudit）を選択
セキュリティ要件を考慮したログレベルの設定

参考資料

Discussion

ログインするとコメントできます