Aurora PostgreSQLでpgAuditを有効にして監査ログを取得する

Aurora PostgreSQLで監査ログを取得する場合

• PostgreSQL ネイティブのツールを利用 : pgAudit
• AWS の仕組み活用 : Database Activity Stream

の2パターンがあります。

本ブログでは、前者の pgAudit を利用して監査ログを取得する方法を紹介します。

後者はAWSの知識がある程度要求されるのに対し、前者は pgAudit(PostgreSQL) の既存のノウハウを活かしやすいというメリットがあります。

内容的には、次のナレッジセンターの手順をベースに、S3へのアーカイブ処理を追加しています。

• PostgreSQL を実行している Amazon RDS DB インスタンスを pgaudit 拡張機能を使用して監査するにはどうすればよいですか?

Database Activity Stream 方式は次の記事を参照ください。

検証環境

• Aurora PostgreSQL 13.6

1. ロールの作成

PostgreSQL に接続し pgAudit 用ロール(rds_pgaudit)を作成します。

postgres=> CREATE ROLE rds_pgaudit;
CREATE ROLE

2. クラスターパラメーターグループの変更

pgAudit を利用するには、パラメーターグループの変更が必要です。

デフォルトとは異なるパラメーターグループに対して、以下の変更を実施してください。

• pgaudit.log : ログ出力するクラス。allやddlなど
• pgaudit.role : rds_pgaudit
• shared＿preload_libraries : pgaudtを追加

3. Aurora PostgreSQL の設定変更

Aurora PostgreSQL クラスターに以下の変更を行います。

• 先程作成したクラスターパラメーターグループを適用
• CloudWatch Logsへのログ出力を有効化

4. Auroraクラスターの再起動

パラメーターグループの変更を反映させるため、クラスターを再起動します。

5. 監査用エクステンション を有効化

監査用エクステンション(pgaudit)を作成します。

postgres=> CREATE EXTENSION pgaudit;
CREATE EXTENSION

6. CloudWatch Logsの確認

pgaudit.log で指定したクラスに対応する SQL(create table foo(a int); など) を実行し、CloudWatch Logsにログ出力されるか確認します。

pgAudit のログは、次のログのようにログメッセージが :LOG: AUDIT: で接頭しています。

2022-03-25 13:41:29 UTC:172.31.40.77(53806):postgres@test:[3152]:LOG:  AUDIT: SESSION,7,1,DDL,CREATE TABLE,TABLE,public.foo,create table foo(a int);,<not logged>

7. 監査ログアーカイブ用S３バケットの作成

監査ログを長期保存し、Athenaなどから問い合わせられるよう、S3に保存します。

そのための S3 バケットを作成します。

8. Amazon Kinesis Data Firehoseの作成

Amazon Kinesis Data Firehoseはストリームデータを S3、Amazon OpenSearch Service等様々なサービスにリアルタイム配信するフルマネージドサービスです。

CloudWatch Logsのサブスクリプションフィルターを利用し、CloudWatch Logs ログをKinesis Firehose経由でS3に保存します。

そのための Firehose ストリームを作成します。

合わせて、FirehoseがS3にオブジェクトを保存するためのIAMポリシーも必要です。

必要なIAM ポリシーは次のページにまとまっています。

https://docs.aws.amazon.com/firehose/latest/dev/controlling-access.html#using-iam-s3

今回のケースでは、ミニマムとして

• S3 への更新権限(特に s3:PutObject)
• CloudWatch Logs への Firehose の実行ログ出力権限(特に logs:PutLogEvents)

などが必要です。

9. CloudWatch Logsのサブスクリプションフィルターの作成

CloudWatch LogsからはFirehoseサブスクリプションフィルター経由でS3にログ転送します。

まず、CloudWatch LogsがFirehoseにデータ送信するでためのIAMポリシーも必要です。

具体的には

• firehose:PutRecord
• firehose:PutRecordBatch

などが必要です。

次に、PostgreSQLのログが出力されているロググループを選択し、「Create Kinesis Firehose subscription filter」から サブスクリプションフィルターを作成します。

• Choose destination : 作成した Firehose ストリームを指定
• Grant permission : 作成した IAM ロールを指定
• Configure log format and filters
  • Log format : Other
  • Subscription filter pattern : ":LOG: AUDIT:"
  • Subscription filter name : 任意

pgAudit のログは ":LOG: AUDIT:" で接頭しているため、フィルターパターンにこの文字列を指定します。

Test pattern からフィルターパターンを検証し、 pgAudit ログだけにフィルターされていることを確認してください。

10. S3 ログの確認

再度 pgAudit 対象のSQLを実行します。

test=> create table bar(b int);
CREATE TABLE

CloudWatch Logs には次のように出力されました。

2022-03-25 13:52:29 UTC:172.31.40.77(53806):postgres@test:[3152]:LOG:  AUDIT: SESSION,8,1,DDL,CREATE TABLE,TABLE,public.bar,create table bar(b int);,<not logged>

サブスクリプションフィルターにより、 監査ログだけが S3 に出力されていることを確認します。

$ aws s3 cp \
  s3://BUCKET/pgaudit/2022/03/25/13/PUT-S3-E78KX-1-2022-03-25-13-51-44-xxx - |
  gzip -d | jq .
{
  "messageType": "CONTROL_MESSAGE",
  "owner": "CloudwatchLogs",
  "logGroup": "",
  "logStream": "",
  "subscriptionFilters": [],
  "logEvents": [
    {
      "id": "",
      "timestamp": 1648216304378,
      "message": "CWL CONTROL MESSAGE: Checking health of destination Firehose."
    }
  ]
}
{
  "messageType": "DATA_MESSAGE",
  "owner": "123456",
  "logGroup": "/aws/rds/cluster/pg/postgresql",
  "logStream": "pg-instance-1.0",
  "subscriptionFilters": [
    "pgaudit"
  ],
  "logEvents": [
    {
      "id": "36756452831101423841029483126659836349813264428266815488",
      "timestamp": 1648216349000,
      "message": "2022-03-25 13:52:29 UTC:172.31.40.77(53806):postgres@test:[3152]:LOG:  AUDIT: SESSION,8,1,DDL,CREATE TABLE,TABLE,public.bar,create table bar(b int);,<not logged>"
    }
  ]
}

S3のログ形式を変更したい場合

• Firehose がS3にPUTしたログをデータ変換
• Firehose のプリプロセスフェーズで Lambda でデータ変換

などが可能です。

参考

• CloudWatch LogsからKinesis Data Fireshoseサブスクリプションフィルターを使ってS3へログを転送した時のログ形式を、オリジナルのログ形式に戻す | DevelopersIO

最後に

Aurora PostgreSQL のpgAudit を有効にし、監査ログを CloudWatch Logs、及び、S3 に出力する方法を紹介しました。

Database Activity Stream 方式に比べて求められる　AWS の知識が少ないため、PostgreSQL の知見を活かしたい場合におすすめです。

参考

• Use the pgaudit Extension to Audit an Amazon RDS DB Instance That Is Running PostgreSQL
• pgAudit (PostgreSQL 監査ロギングツール)
• Common DBA tasks for PostgreSQL - Amazon Relational Database Service