Aurora PostgreSQLにて監査ログを取得する

Aurora PostgreSQLにて、監査ログを取得する設定を実施します。

環境情報

Aurora PostgerSQL 9.6.9

今回利用するpgauditを利用した監査ログ取得方法は、バージョン9.5以降でないと利用できません。

pgauditについて

RDSでも、Auroraでも、Postgreで監査ログを出力する方法は、オンプレのPostgreSQLと同じく pgaudit を利用することになります。

pgauditについては、こちらのslideshareの資料を参考。

監査要件を有するシステムに対する PostgreSQL 導入の課題と可能性 from Ohyama Masanori

www.slideshare.net

githubのページはこちら。

GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension

こんな感じに紹介されています。

The PostgreSQL Audit Extension (pgAudit) provides detailed session and/or object audit logging via the standard PostgreSQL logging facility.

The goal of the pgAudit is to provide PostgreSQL users with capability to produce audit logs often required to comply with government, financial, or ISO certifications.

設定方法

設定方法は、RDSでもAuroraでも同じです。公式に手順が記載されているので、それを参考にしつつ設定していきます。

PostgreSQL の一般的な DBA タスク - Amazon Relational Database Service

pgauditには、以下2つのログ出力モードがあります。併用できます。

Session Audit Logging
- 特定のSQLが実行された場合に、ログを出力
Object Auditing
- 特定のオブジェクトに対して処理がなされた場合に、ログを出力

Session Audit Logging

Session Audit Loggingの利用方法について。以下から全ての処理は、Auraro PostgreのRDSマスターユーザで実行しています。

まずは、pgauditで利用するDBロールを作成します。

sample=> CREATE ROLE rds_pgaudit;
CREATE ROLE
sample=> \du
                                                    List of roles
     Role name     |                         Attributes                         |              Member of
-------------------+------------------------------------------------------------+-------------------------------------
 masteruser        | Create role, Create DB                                    +| {rds_superuser}
                   | Password valid until infinity                              |
 pg_signal_backend | Cannot login                                               | {}
 rds_iam           | Cannot login                                               | {}
 rds_pgaudit       | Cannot login                                               | {}
 rds_replication   | Cannot login                                               | {}
 rds_superuser     | Cannot login                                               | {rds_replication,pg_signal_backend}
 rdsadmin          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                   | Password valid until infinity                              |

続いて、DBパラメータグループより、以下の値を変更します。

パラメータ名	デフォルト値	設定値
pgaudit.log	none	all, -misc
pgaudit.log_catalog	1	0
pgaudit.log_level	log	log
pgaudit.log_relation	0	1
pgaudit.log_statement_once	0	1
pgaudit.log_parameter	0	1
pgaudit.role		rds_pgaudit
shared_preload_libraries		pgaudit

pgaudit.log の値にて、出力する監査ログの種類を指定できます。「読み取りのみ」とか「更新のみ」とか、DDLのみとかより、複数を指定できたりします。今回は「all」から「vacuum等のメンテナンスの処理」のみを除外する設定を入れています。詳しくはgithubのサイトに記載されています。

GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension

pgaudit.log_catalog の値は、catalogテーブルへのアクセスをログと残すかどうか指定できます。これを有効としておくと、psqlやPgAdminでのアクセスが発生した都度に、ノイズなログがかなり出力されることになるので、無効(0)としておきます。詳しくはこちら。

GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension

pgaudit.log_level の値は、出力されるログレベルを指定できます。ERRORのみ出力とかできます。デフォルトの log は、INFORMATIONレベルのログを出力してくれるので、デフォルトのまま設定します。詳しくはこちら。

GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension

pgaudit.log_parameter の値は、SQL内で変数を利用された場合、その変数に代入された値までロギングしてくれる設定です。これも記録しておきたいので、有効(1)としておきます。詳しくはこちら。

GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension

pgaudit.log_relation の値は、操作した対象のオブジェクト名を分かりやすく出力させてくれる設定です。設定しておいた方が便利となること多いので、有効(1)としておきます。詳しくはこちら。

GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension

pgaudit.log_statement_once の値は、Session Audit LoggingとObject Auditingを併用時、同じSQLログを複数回出力させない設定です。複数回出てくるのは煩わしいので、有効(1)としておきます。詳しくはこちら。

GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension

pgaudit.role の値は、上記で作成したロール名を指定します。AWSの場合、この値は rds_pgaudit 限定となるようです。

shared_preload_libraries の値に、pgAuditを追加しておきます。pgAuditのライブラリを、共有メモリにロードするための設定になります。

パラメータを変更後、対象のクラスタなりインスタンスなりを再起動します。

再起動後、pgauditがちゃんとロードされていることを確認します。

sample=> show shared_preload_libraries;
 shared_preload_libraries
--------------------------
 rdsutils,pgaudit
(1 row)

pgauditの拡張機能を有効にします。

sample=> CREATE EXTENSION pgaudit;
CREATE EXTENSION
sample=> \dx
                    List of installed extensions
  Name   | Version |   Schema   |            Description
---------+---------+------------+------------------------------------
 pgaudit | 1.1.1   | public     | provides auditing functionality
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 sslinfo | 1.2     | public     | information about SSL certificates
(3 rows)

作成したロールに、pgauditがちゃんと紐付いていることを確認します。

sample=> show pgaudit.role;
 pgaudit.role
--------------
 rds_pgaudit
(1 row)

これで設定は完了です。ためしに、テーブルを作成して、データ挿入して、検索してみます。

sample=> create table audit_sample (id int);
CREATE TABLE
sample=> insert into audit_sample values (1);
INSERT 0 1
sample=> select id from public.audit_sample;
 id
----
  1
(1 row)

ログは、PostgreSQLのサーバログ postgresql.log に出力されます。コンソールから確認すると、以下のように出力されています。

2018-10-21 12:07:53 UTC:183.74.193.6(41253):masteruser@sample:[7667]:LOG: AUDIT: SESSION,2,1,DDL,CREATE TABLE,TABLE,public.audit_sample,create table audit_sample (id int);,

2018-10-21 12:08:00 UTC:183.74.193.6(41253):masteruser@sample:[7667]:LOG: AUDIT: SESSION,3,1,WRITE,INSERT,TABLE,public.audit_sample,insert into audit_sample values (1);,

2018-10-21 12:08:06 UTC:183.74.193.6(41253):masteruser@sample:[7667]:LOG: AUDIT: SESSION,4,1,READ,SELECT,TABLE,public.audit_sample,select id from public.audit_sample;,

出力されるログのフォーマットは、以下のgithubに書いてあります。

GitHub - pgaudit/pgaudit: PostgreSQL Audit Extension

Object Auditing

Object Auditingを用いると、監査対象(ログ出力)としたいオブジェクトを限定できます。

ログ出力したいオブジェクトを限定するには、pgaudit.roleに設定したDBロール(AWSの場合はrds_pgaudit)の、オブジェクトアクセス権限を変更してあげます。rds_pgauditが、アクセスできるオブジェクトの情報のみが、ログに出力されることになります。

例えば、以下のような権限をrds_pgauditに与えた場合、accountテーブルへのselectとdeleteの結果のみを、ログ出力するようになります。

grant select, delete on public.account to rds_pgaudit;

最後に...

尚、AWSにてPostgreSQLのログは、CloudwatchLogsやS3に簡単に連携できる設定がありません。MySQLやOracleではCloudwatchLogsに簡単に連携できるようになったのですが、PostgreSQLはまだできないのです。

そのため監査ログをちゃんと管理するには、PostgreSQLのサーバログをs3に連携してあげるようなLambdaプログラムを開発する必要があります。