pgAudit (PostgreSQL 監査ロギングツール)

本記事では、PostgreSQL 監査ロギングツール「pgAudit」のインストール方法、監査ログ取得方法について紹介します。

pgAudit とは

pgAudit は PostgreSQL で監査ログを取得するためのオープンソースソフトウェアで、PostgreSQL の拡張機能として実装されています。監査する対象として設定した内容は、PostgreSQL ログに混在して出力されます。

公式サイトはこちらです。分かりやすいドキュメント(README.md)とともに公開されています。

pgAudit サポート対象バージョン

pgAudit がサポートする PostgreSQL のバージョンは PostgreSQL 9.5 からで、現時点では最新の PostgreSQL 13 までサポートされています。

pgAudit のバージョン体系は、対象とする PostgreSQL のメジャーバージョンで導入された新機能に対応するため、PostgreSQL のメジャーバージョンに対応したバージョンが独立して存在しています。一旦リリースされた後はバグ修正のみが実施される PostgreSQL と同様のリリースモデルを採用しています。

pgAudit のバージョンとサポート対象の PostgreSQL のバージョンの対応は以下の通りです。

pgAudit バージョン サポート対象 PostgreSQL バージョン
pgAudit v1.5.X PostgreSQL 13
pgAudit v1.4.X PostgreSQL 12
pgAudit v1.3.X PostgreSQL 11
pgAudit v1.2.X PostgreSQL 10
pgAudit v1.1.X PostgreSQL 9.6
pgAudit v1.0.X PostgreSQL 9.5

pgAudit インストール方法(rpmパッケージ)

pgAudit のインストール方法は、大きく分けてソースコードからのインストールと、PostgreSQL公式リポジトリの rpm からのインストールがあります。今回は rpm からのインストールをご紹介します。下記のインストールの実行例では CentOS Linux release 8.2.2004, PostgreSQL 13.0, pgAudit v1.5.0 を使用して説明します。CentOS から直接インターネット接続が可能な環境を使用しています。

CentOS に PostgreSQL 公式 Yum リポジトリを設定し、PostgreSQL 13をインストール

CentOS から PostgreSQL 公式 Yum リポジトリのパッケージを dnf でインストール可能とするために repo ファイルを導入します。PostgreSQL ダウンロードページの Packages and Installers で Linux と Red Hat/CentOS を選択したらこのページが表示されます。PostgreSQL Yum Repository で使用する PostgreSQL バージョン(13)と OS のバージョン(Red Hat/CentOS 8)と CPU アーキテクチャ(x86_64)を選択すれば、repo ファイルを導入して PostgreSQL サーバを導入するコマンドが表示されます。表示されたコマンドを参考にして、まず repo ファイルを導入します。

# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
:
依存関係が解決しました。
================================================================================
 パッケージ              Arch          バージョン     リポジトリー        サイズ
================================================================================
インストール中:
 pgdg-redhat-repo        noarch        42.0-14        @commandline         11 k

トランザクションの概要
================================================================================
インストール  1 パッケージ

合計サイズ: 11 k
インストール済みのサイズ: 11 k
パッケージのダウンロード:
トランザクションの確認を実行中
トランザクションの確認に成功しました。
トランザクションのテストを実行中
トランザクションのテストに成功しました。
トランザクションを実行中
  準備              :                                                       1/1
  インストール中    : pgdg-redhat-repo-42.0-14.noarch                       1/1
  検証              : pgdg-redhat-repo-42.0-14.noarch                                                                                1/1
Installed products updated.

インストール済み:
  pgdg-redhat-repo-42.0-14.noarch

完了しました!
#

次に、PostgreSQL公式サイトの PostgreSQL が導入できるように OS 組み込みの PostgreSQL を無効にします。これを実行しないと PostgreSQL 公式サイトの PostgreSQL が見つからないと言われてしまいます。

# dnf install -y postgresql13-server
All matches were filtered out by modular filtering for argument: postgresql13-server
エラー: 一致するものが見つかりません: postgresql13-server
# dnf -qy module disable postgresql
# dnf install -y postgresql13-server
依存関係が解決しました。
=========================================================================================================================================
 パッケージ                             アーキテクチャー          バージョン                             リポジトリー              サイズ
=========================================================================================================================================
インストール中:
 postgresql13-server                    x86_64                    13.0-1PGDG.rhel8                       pgdg13                    5.5 M
依存関係のインストール中:
 postgresql13                           x86_64                    13.0-1PGDG.rhel8                       pgdg13                    1.4 M
 postgresql13-libs                      x86_64                    13.0-1PGDG.rhel8                       pgdg13                    411 k

トランザクションの概要
=========================================================================================================================================
インストール  3 パッケージ

ダウンロードサイズの合計: 7.4 M
インストール済みのサイズ: 31 M
パッケージのダウンロード:
(1/3): postgresql13-libs-13.0-1PGDG.rhel8.x86_64.rpm                                                     163 kB/s | 411 kB     00:02
(2/3): postgresql13-13.0-1PGDG.rhel8.x86_64.rpm                                                          262 kB/s | 1.4 MB     00:05
(3/3): postgresql13-server-13.0-1PGDG.rhel8.x86_64.rpm                                                   466 kB/s | 5.5 MB     00:12
-----------------------------------------------------------------------------------------------------------------------------------------
合計                                                                                                     617 kB/s | 7.4 MB     00:12
警告: /var/cache/dnf/pgdg13-e81daebfc8b779ec/packages/postgresql13-13.0-1PGDG.rhel8.x86_64.rpm: ヘッダー V4 DSA/SHA1 Signature、鍵 ID 442df0f8: NOKEY
PostgreSQL 13 for RHEL/CentOS 8 - x86_64                                                                 1.6 MB/s | 1.7 kB     00:00
GPG 鍵 0x442DF0F8 をインポート中:
 Userid     : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
鍵のインポートに成功しました
トランザクションの確認を実行中
トランザクションの確認に成功しました。
トランザクションのテストを実行中
トランザクションのテストに成功しました。
トランザクションを実行中
  準備              :                                                                                                                1/1
  インストール中    : postgresql13-libs-13.0-1PGDG.rhel8.x86_64                                                                      1/3
  scriptlet の実行中: postgresql13-libs-13.0-1PGDG.rhel8.x86_64                                                                      1/3
  インストール中    : postgresql13-13.0-1PGDG.rhel8.x86_64                                                                           2/3
  scriptlet の実行中: postgresql13-13.0-1PGDG.rhel8.x86_64                                                                           2/3
  scriptlet の実行中: postgresql13-server-13.0-1PGDG.rhel8.x86_64                                                                    3/3
  インストール中    : postgresql13-server-13.0-1PGDG.rhel8.x86_64                                                                    3/3
  scriptlet の実行中: postgresql13-server-13.0-1PGDG.rhel8.x86_64                                                                    3/3
  検証              : postgresql13-13.0-1PGDG.rhel8.x86_64                                                                           1/3
  検証              : postgresql13-libs-13.0-1PGDG.rhel8.x86_64                                                                      2/3
  検証              : postgresql13-server-13.0-1PGDG.rhel8.x86_64                                                                    3/3
Installed products updated.

インストール済み:
  postgresql13-13.0-1PGDG.rhel8.x86_64     postgresql13-libs-13.0-1PGDG.rhel8.x86_64     postgresql13-server-13.0-1PGDG.rhel8.x86_64

完了しました!
# 

これで PostgreSQL 公式サイトの rpm から PostgreSQL 13.0 が導入出来ました。DBの初期化も実施しておきましょう。

# export PGSETUP_INITDB_OPTIONS="--encoding=UTF-8 --no-locale"
# /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK

# systemctl enable postgresql-13
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service → /usr/lib/systemd/system/postgresql-13.service.
# systemctl start postgresql-13
# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2020-11-10 11:42:49 JST; 7s ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 24991 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 24996 (postmaster)
    Tasks: 8 (limit: 4879)
   Memory: 23.7M
   CGroup: /system.slice/postgresql-13.service
           tq24996 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           tq24998 postgres: logger
           tq25000 postgres: checkpointer
           tq25001 postgres: background writer
           tq25002 postgres: walwriter
           tq25003 postgres: autovacuum launcher
           tq25004 postgres: stats collector
           mq25005 postgres: logical replication launcher

11月 10 11:42:49 c822004 systemd[1]: Starting PostgreSQL 13 database server...
11月 10 11:42:49 c822004 postmaster[24996]: 2020-11-10 11:42:49.736 JST [24996] LOG:  redirecting log output to logging collector process
11月 10 11:42:49 c822004 postmaster[24996]: 2020-11-10 11:42:49.736 JST [24996] HINT:  Future log output will appear in directory "log".
11月 10 11:42:49 c822004 systemd[1]: Started PostgreSQL 13 database server.
# su - postgres
[postgres@c822004 ~]$ psql
psql (13.0)
"help"でヘルプを表示します。

postgres=#

無事 PostgreSQL 13 が起動しました。

pgaudit のインストール

PostgreSQL 公式リポジトリに含まれる pgaudit の rpm パッケージ名を調べてみます。PostgreSQL 13 用の pgaudit のパッケージは pgaudit15_13 でした。

# dnf list | grep pgaudit
pgaudit10_95.x86_64                                  1.0.8-1.rhel8                                     pgdg95
pgaudit11_96.x86_64                                  1.1.3-1.rhel8                                     pgdg96
pgaudit12_10.x86_64                                  1.2.2-1.rhel8                                     pgdg10
pgaudit13_11.x86_64                                  1.3.2-1.rhel8                                     pgdg11
pgaudit14_12.x86_64                                  1.4.1-1.rhel8                                     pgdg12
pgaudit15_13.x86_64                                  1.5.0-1.rhel8                                     pgdg13
pgaudit_analyze.x86_64                               1.0.7-1.rhel8                                     pgdg-common
pgauditlogtofile-10.x86_64                           1.0-1.rhel8                                       pgdg10
pgauditlogtofile-11.x86_64                           1.0-1.rhel8                                       pgdg11
pgauditlogtofile-12.x86_64                           1.0-1.rhel8                                       pgdg12
pgauditlogtofile-13.x86_64                           1.0-1.rhel8                                       pgdg13
pgauditlogtofile-96.x86_64                           1.0-1.rhel8                                       pgdg96
#

pgaudit のパッケージを導入します。

# dnf install -y pgaudit15_13
依存関係が解決しました。
=========================================================================================================================================
 パッケージ                         アーキテクチャー             バージョン                           リポジトリー                 サイズ
=========================================================================================================================================
インストール中:
 pgaudit15_13                       x86_64                       1.5.0-1.rhel8                        pgdg13                        52 k

トランザクションの概要
=========================================================================================================================================
インストール  1 パッケージ

ダウンロードサイズの合計: 52 k
インストール済みのサイズ: 93 k
パッケージのダウンロード:
pgaudit15_13-1.5.0-1.rhel8.x86_64.rpm                                                                     26 kB/s |  52 kB     00:02
-----------------------------------------------------------------------------------------------------------------------------------------
合計                                                                                                      25 kB/s |  52 kB     00:02
トランザクションの確認を実行中
トランザクションの確認に成功しました。
トランザクションのテストを実行中
トランザクションのテストに成功しました。
トランザクションを実行中
  準備              :                                                                                                                1/1
  インストール中    : pgaudit15_13-1.5.0-1.rhel8.x86_64                                                                              1/1
  scriptlet の実行中: pgaudit15_13-1.5.0-1.rhel8.x86_64                                                                              1/1
  検証              : pgaudit15_13-1.5.0-1.rhel8.x86_64                                                                              1/1
Installed products updated.

インストール済み:
  pgaudit15_13-1.5.0-1.rhel8.x86_64

完了しました!
#

これで pgaudit がインストールできました。

postgresql.conf の shared_preload_libraries に pgaudit を追加して PostgreSQL を再起動、有効になっていることを確認

shared_preload_libraries に共有ライブラリの pgaudit を設定して PostgreSQL を再起動し、pgaudit が有効になっていることを確認します。

$ cd $PGDATA
$ vi postgresql.conf
shared_preload_libraries = 'pgaudit'
# systemctl restart postgresql-13
$ psql
psql (13.0)
"help"でヘルプを表示します。

postgres=# show shared_preload_libraries;
 shared_preload_libraries
--------------------------
 pgaudit
(1 行)

CREATE EXTENSION を実行してエクステンションに登録

使用したいデータベースに psql で接続して、CREATE EXTENSION を実行します。エクステンションが登録されていることを確認します。

postgres=# CREATE EXTENSION pgaudit;
CREATE EXTENSION
postgres=# \dx
                     インストール済みの拡張一覧
  名前   | バージョン |  スキーマ  |              説明
---------+------------+------------+---------------------------------
 pgaudit | 1.5        | public     | provides auditing functionality
 plpgsql | 1.0        | pg_catalog | PL/pgSQL procedural language
(2 行)

postgres=#

これで pgAudit のインストールは完了しました。

pgAudit 監査ログ取得方法

監査ログの取得方法は大きく分けて二種類あります。セッション監査ロギングとオブジェクト監査ロギングです。

セッション監査ロギング

セッションで接続した際に、pgAudit のパラメータ pgaudit.log に設定した文のクラスがすべて記録されます。文のクラスとして設定できる値は、READ / WRITE / FUNCTION / ROLE / DDL / MISC / MISC_SET とそれら全てを含む ALL があり、複数の値をカンマで区切って指定できます。例えば、READ を設定すると SELECT とソースがリレーションかクエリの場合の COPY が、WRITE を設定すると INSERT、UPDATE、DELETE、TRUNCATEと宛先がリレーションの場合の COPY が記録されます。またマイナス記号を使用すると、除外対象として設定可能です。例えば、’ALL, -MISC’ と設定すると、MISC 以外の内容がすべて監査ログとして記録されます。

オブジェクト監査ロギング

テーブル単位やカラム単位などオブジェクトに対する権限を pgAudit のパラメータ pgaudit.role に設定したロールに対して設定することで、ロールの操作がすべて記録されます。pgaudit.role に設定するロールはマスターロールと呼ばれます。セッション監査ログでは大量の監査ログが出力される可能性があるため、特定のテーブルだけを監査したい場合や特定のテーブルの特定のカラムに対する操作だけを監査したい場合など特定のリレーションに絞った詳細な監査ログを取得するのに向いています。

pgAudit を使ってみる(セッション監査ロギング編)

監査ログをセッション単位で取得するセッション監査ロギングを試してみます。postgresql.conf ファイルに pgaudit.log = ‘ALL’ を追加し、PostgreSQL に設定を読み込ませます。SELECT 文を実行したところ、無事 PostgreSQL ログに記録されました。

LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "pgaudit.log" changed to "ALL"
LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,SELECT * FROM test1;,<not logged>

pgAuditの監査ログは、AUDIT: で始まって、カンマ区切り形式で各情報が出力されます。監査ログのフォーマットは後で詳細を説明します。

pgaudit.log_catalog について

psql で \d を実行すると、psql が実行した pg_catalog.pg_class と pg_catalog.pg_namespace への SELECT 文が監査ログとして記録されます。これは、pgaudit.log_catalog のデフォルト値が ‘on’ になっているためです。psql がどのような DML 文で内容を検索しているのかの勉強になると思いますが、psql や pgAdmin4 などを使う時にはデフォルト値のままだと必要な監査ログが埋もれてしまう可能性があるので、こういった pg_catalog への参照をするようなツールを使う時に監査ログを取得する際は、pgaudit.log_catalog を ‘off’ にしてノイズを減らしたほうがよいと思います。

postgres=# \d
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | test1 | table | postgres
(1 row)

LOG:  AUDIT: SESSION,2,1,READ,SELECT,,,"SELECT n.nspname as ""Schema"",
          c.relname as ""Name"",
          CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as ""Type"",
          pg_catalog.pg_get_userbyid(c.relowner) as ""Owner""
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','p','v','m','S','f','')
              AND n.nspname <> 'pg_catalog'
              AND n.nspname <> 'information_schema'
              AND n.nspname !~ '^pg_toast'
          AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 1,2;",<not logged>

pgaudit.log_relation について

セッション監査ロギングで SELECT 文または DML文で参照される各リレーション(TABLE、VIEW など)の個別のログエントリを作成するかどうかを指定します。デフォルト値は ‘off’ でリレーションは記録されませんが、’on’ に設定すると、6番目にオブジェクトの型(TABLEなど)、7番目にオブジェクトの完全修飾名(public.table1など)とリレーションの情報が記録されます。

  • pgaudit.log_relation = ‘off’ (デフォルト値) の時
    LOG:  AUDIT: SESSION,1,1,READ,SELECT,,,SELECT * FROM table1;,<not logged>
    
  • pgaudit.log_relation = ‘on’ の時
    LOG:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.table1,SELECT * FROM table1;,<not logged>
    

pgaudit.log_relation が ‘on’ の時は SELECT と SELECT文との間の6番目と7番目にそれぞれ TABLE と public.table1 が記録されています。異なるスキーマに属するけれども同一のテーブル名のリレーションが複数存在する時もありますので、このパラメータ pgaudit.log_relation は ‘on’ に設定するのがよさそうに思います。

pgaudit.log_parameter について

pgaudit.log_parameter は渡されたパラメータを監査ログの最後のエントリに追記するかどうかを指定します。デフォルト値は ‘off’ でパラメータは記録されずに <not logged> と表示されます。’on’ に設定するとパラメータは記録されますが、パラメータが無い時は <none> と表示されます。

  • pgaudit.log_parameter = ‘off’ (デフォルト値)の時
    LOG:  AUDIT: SESSION,4,1,MISC,SHOW,,,SHOW pgaudit.log_client;,<not logged>
    
  • pgaudit.log_parameter = ‘on’ の時
    LOG:  AUDIT: SESSION,7,1,MISC,SHOW,,,SHOW pgaudit.log_client;,<none>
    

pgaudit.log_statement_once について

監査ログにおいて、文テキストとパラメータをステートメント(文)とサブステートメント(サブ文)の組み合わせを最初のエントリだけに含めるか、各エントリのすべてに含めるかを指定します。デフォルト値は ‘off’ で、各エントリのすべてに含められます。下記の例は、 pgaudit.log_relation = ‘on’ の時に table1 テーブルと table2 テーブルに対して SELECT 文で JOIN をした時の出力です。

  • pgaudit.log_statement_once = ‘off’ (デフォルト値)の時
    LOG:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.table1,"SELECT table1.id, table1.t1, table2.t1 FROM table1, table2 WHERE table1.id = table2.id;",<not logged>
    LOG:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.table2,"SELECT table1.id, table1.t1, table2.t1 FROM table1, table2 WHERE table1.id = table2.id;",<not logged>
    
  • pgaudit.log_statement_once = ‘on’ の時
    LOG:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.table1,"SELECT table1.id, table1.t1, table2.t1 FROM table1, table2 WHERE table1.id = table2.id;",<not logged>
    LOG:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.table2,<previously logged>,<previously logged>
    

リレーション出力がオンなので、二つのテーブルの完全修飾名である public.table1 と public.table2 が二行に出力されています。pgaudit.log_statement が ‘off’ の時は8番目のバックエンドで実行される SELECT 文と9番目のパラメータの情報が二行とも出力されていますが、pgaudit.log_statement が ‘on’ の時は8番目と9番目は <previously logged> と省略した表示がされています。

pgaudit.log_client と pgaudit.log_level について

pgaudit.log_client はログメッセージを psql などのクライアントプロセスに表示するかどうかを指定します。psql を使ってデバッグや動作確認をしたい時に便利な機能です。デフォルト値は ‘off’ で通常は表示されません。また pgaudit.log_client が ‘on’ の時だけ使用可能なパラメータ pgaudit.log_level があり、このパラメータでログエントリに使用されるログレベルが指定可能です。デフォルト値は log で、指定可能なパラメータは debug1 から debug5 / info / notice / warning / log です。error と fatal と panic は使用できません。

pgAudit を使ってみる(オブジェクト監査ロギング編)

オブジェクトに対する権限をロールに付与して、そのロールを監査対象とすることで監査を実施するオブジェクト監査ロギングを試してみます。

auditmroleマスターロールの作成

オブジェクト監査ロギングの対象ロールとして auditmrole マスターロールを作成します。

postgres=# CREATE ROLE auditmrole WITH LOGIN;
CREATE ROLE

pgaudit.role の設定

postgresql.conf ファイルに pgaudit.role = ‘ALL’ を追加し、PostgreSQL に設定を反映します。

$ cd $PGDATA
$ vi postgresql.conf
pgaudit.role = 'auditmrole'
$ pg_ctl reload

2020-11-02 07:22:08.263 JST [2107] LOG:  received SIGHUP, reloading configuration files
2020-11-02 07:22:08.264 JST [2107] LOG:  parameter "pgaudit.role" changed to "auditmrole"

マスターロールへの権限の付与

auditmrole マスターロールに table1 テーブルへの SELECT と INSERT の権限を与えます。

postgres=# CREATE TABLE table1 (id int, t1 text);
CREATE TABLE
postgres=# INSERT INTO table1 VALUES (1, 'aaa'),(2, 'bbb');
INSERT 0 2
postgres=# SELECT * FROM table1;
 id | t1
----+-----
  1 | aaa
  2 | bbb
(2 rows)

postgres=# GRANT SELECT,INSERT ON table1 TO auditmrole;
GRANT

マスターロールでの種々の操作と監査ログ

auditmrole でログインして、table1 テーブルに対して SELECT、INSERT、UPDATE、DELETE を実施します。

$ psql -U auditmrole postgres
psql (13.0)
Type "help" for help.

postgres=> SELECT * FROM table1;
 id | t1
----+-----
  1 | aaa
  2 | bbb
(2 rows)

postgres=> INSERT INTO table1 VALUES (99, 'postgres');
INSERT 0 1
postgres=> SELECT * FROM table1;
 id |    t1
----+----------
  1 | aaa
  2 | bbb
 99 | postgres
(3 rows)

postgres=> UPDATE table1 SET t1 = 'pgaudit' WHERE id = 99;
ERROR:  permission denied for table table1
postgres=> DELETE FROM table1 WHERE id = 99;
ERROR:  permission denied for table table1
postgres=>

与えられた権限の通り、SELECTとINSERTは実行できましたが、UPDATEとDELETEは権限がないためエラーとなりました。次に、各操作がどのようにPostgreSQLログに記録されているかを確認してみましょう。

2020-11-02 07:38:12.106 JST [3750] LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.table1,SELECT * FROM table1;,<not logged>
2020-11-02 07:38:57.614 JST [3750] LOG:  AUDIT: OBJECT,2,1,WRITE,INSERT,TABLE,public.table1,"INSERT INTO table1 VALUES (99, 'postgres');",<not logged>
2020-11-02 07:38:59.966 JST [3750] LOG:  AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.table1,SELECT * FROM table1;,<not logged>
2020-11-02 07:39:22.329 JST [3750] ERROR:  permission denied for table table1
2020-11-02 07:39:22.329 JST [3750] STATEMENT:  UPDATE table1 SET t1 = 'pgaudit' WHERE id = 99;
2020-11-02 07:39:52.833 JST [3750] ERROR:  permission denied for table table1
2020-11-02 07:39:52.833 JST [3750] STATEMENT:  DELETE FROM table1 WHERE id = 99;

pgAudit のログ出力形式

pgAuditの監査エントリは、標準のロギング機能に書き込まれ、カンマ区切り形式で以下の列が含まれます。各ログエントリのログ行のプレフィックス部分が削除された場合のみ、出力はCSV形式に準拠します。

順番 エントリ 説明
1 AUDIT_TYPE SESSION または OBJECT
2 STATEMENT_ID このセッションの一意の文ID。各文IDはバックエンド呼び出しを表します。一部の文がログに記録されていない場合でも、文IDは連続しています。複数のリレーションがログに記録されている場合、1つの文IDに対して複数のエントリが存在する可能性があります。
3 SUBSTATEMENT_ID メイン文内の各サブ文のシーケンシャル ID。例えば、クエリから関数を呼び出す場合など。一部のサブ文がログに記録されていなくてもサブ文IDは連続しています。複数のリレーションがログに記録される場合、サブ文IDに対して複数のエントリが存在する可能性があります。
4 CLASS 例:READ、ROLE など(pgaudit.log を参照)。
5 COMMAND 例:ALTER TABLE、SELECT など。
6 OBJECT_TYPE TABLE、INDEX、VIEWなど。SELECT と DML とほとんどの DDL 文で利用可能。セッション監査ロギングでは pgaudit.log_relation = ‘on’ の時のみ出力されます。
7 OBJECT_NAME 完全修飾されたオブジェクト名(例:public.account)。SELECT と DML とほとんどの DDL 文で利用可能。セッション監査ロギングでは pgaudit.log_relation = ‘on’ の時のみ出力されます。
8 STATEMENT バックエンドで実行される文(ステートメント)
9 PARAMETER pgaudit.log_parameter が設定されている場合、このフィールドには、引用符で囲まれた CSV の文パラメータ、パラメータがない場合は <none> が含まれます。pgaudit.log_parameter が設定されていない場合は <not logged> となります。

監査ログの要件を満たすために必要な他のフィールドを追加するには log_line_prefix を使用します。典型的なログ行プレフィックスは、’%m %u %d [%p]: ‘ で、各監査ログの日付/時刻、ユーザ名、データベース名、およびプロセス ID を提供します。

終わりに

pgAudit を導入して、セッション監査ロギングとオブジェクト監査ロギングの両方を実行してみました。また pgAudit の種々のパラメータの紹介と解説をしました。PostgreSQL で監査ログを取得する必要が出てきた時に、本記事が参考になれば幸いです。