本記事では、PostgreSQL 監査ロギングツール「pgAudit」のアドオンとして使用する「pgAudit Log to File」のインストール方法、監査ログ取得方法について紹介します。
pgAudit Log to File とは
pgAudit Log to File とは PostgreSQL で監査ログを取得して PostgreSQL ログファイルに書き出す pgAudit のアドオンで、監査ログ専用ファイルを PostgreSQL ログファイルとは別の独立したファイルに分離して書き出してくれるソフトウェアです。
公式サイトはこちらです。分かりやすいドキュメント(README.md)とともに公開されています。
pgAudit のブログ記事はこちらになります。
pgAudit Log to File サポート対象バージョン
pgAudit Log to File がサポートする PostgreSQL / pgAudit のバージョンは特にドキュメントに記載はありませんので、使用する時点の最新バージョンを使用すればよいと思います。
現時点(2022年11月2日)の GitHub での最新バージョンは v1.5.8 で、GitHubのリリースページからダウンロード可能です。
また、PostgreSQL 公式リポジトリでは pgAudit Log to File が pgauditlogtofile_<PostgreSQLメジャーバージョン> というファイル名で rpm ファイルが配布されており、PostgreSQL 10,11,12,13,14,15 用バージョンが v1.5.6 となっています。
pgAudit Log to File インストール方法(rpmパッケージ)
pgAudit Log to File のインストール方法は、大きく分けてソースコードからのインストールと、PostgreSQL公式リポジトリの rpm からのインストールがあります。
今回は rpm からのインストールをご紹介します。
下記のインストールの実行例では Rocky Linux release 8.6, PostgreSQL 14.5, pgAudit v1.6.2, pgAudit Log to File v1.5.6 を使用して説明します。
Rocky Linux から直接インターネット接続が可能な環境を使用しています。
Rocky Linux に PostgreSQL 公式 Yum リポジトリを設定し、PostgreSQL 14をインストール
Rocky Linux から PostgreSQL 公式 Yum リポジトリのパッケージを dnf でインストール可能とするために repo ファイルを導入します。
PostgreSQL ダウンロードページの Packages and Installers で Linux と Red Hat/Rocky/CentOS を選択するとこのページが表示されます。PostgreSQL Yum Repository で使用する PostgreSQL バージョン(14)と OS のバージョン(Red Hat Enterprise, Rocky, or Oracle version 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-28 @commandline 13 k トランザクションの概要 ================================================================================ インストール 1 パッケージ 合計サイズ: 13 k インストール後のサイズ: 14 k パッケージのダウンロード: トランザクションの確認を実行中 トランザクションの確認に成功しました。 トランザクションのテストを実行中 トランザクションのテストに成功しました。 トランザクションを実行中 準備 : 1/1 インストール中 : pgdg-redhat-repo-42.0-28.noarch 1/1 検証 : pgdg-redhat-repo-42.0-28.noarch 1/1 インストール済み: pgdg-redhat-repo-42.0-28.noarch 完了しました! #
次に、PostgreSQL公式サイトの PostgreSQL が導入できるように OS 組み込みの PostgreSQL を無効にします。これを実行しないと PostgreSQL 公式サイトの PostgreSQL が見つからないと言われてしまいます。
# dnf install -y postgresql14-server すべての検出結果は引数のモジュラーフィルタリングに一致しません (filter out): postgresql14-server エラー: 一致するものが見つかりません: postgresql14-server # # dnf -qy module disable postgresql # dnf install -y postgresql14-server 依存関係が解決しました。 ================================================================================ パッケージ Arch バージョン Repo サイズ ================================================================================ インストール: postgresql14-server x86_64 14.5-1PGDG.rhel8 pgdg14 5.7 M 依存関係のインストール: postgresql14 x86_64 14.5-1PGDG.rhel8 pgdg14 1.5 M postgresql14-libs x86_64 14.5-1PGDG.rhel8 pgdg14 278 k トランザクションの概要 ================================================================================ インストール 3 パッケージ ダウンロードサイズの合計: 7.5 M インストール後のサイズ: 31 M パッケージのダウンロード: (1/3): postgresql14-libs-14.5-1PGDG.rhel8.x86_6 113 kB/s | 278 kB 00:02 (2/3): postgresql14-14.5-1PGDG.rhel8.x86_64.rpm 307 kB/s | 1.5 MB 00:05 (3/3): postgresql14-server-14.5-1PGDG.rhel8.x86 196 kB/s | 5.7 MB 00:29 -------------------------------------------------------------------------------- 合計 258 kB/s | 7.5 MB 00:29 トランザクションの確認を実行中 トランザクションの確認に成功しました。 トランザクションのテストを実行中 トランザクションのテストに成功しました。 トランザクションを実行中 準備 : 1/1 インストール中 : postgresql14-libs-14.5-1PGDG.rhel8.x86_64 1/3 scriptletの実行中: postgresql14-libs-14.5-1PGDG.rhel8.x86_64 1/3 インストール中 : postgresql14-14.5-1PGDG.rhel8.x86_64 2/3 scriptletの実行中: postgresql14-14.5-1PGDG.rhel8.x86_64 2/3 scriptletの実行中: postgresql14-server-14.5-1PGDG.rhel8.x86_64 3/3 インストール中 : postgresql14-server-14.5-1PGDG.rhel8.x86_64 3/3 scriptletの実行中: postgresql14-server-14.5-1PGDG.rhel8.x86_64 3/3 検証 : postgresql14-14.5-1PGDG.rhel8.x86_64 1/3 検証 : postgresql14-libs-14.5-1PGDG.rhel8.x86_64 2/3 検証 : postgresql14-server-14.5-1PGDG.rhel8.x86_64 3/3 インストール済み: postgresql14-14.5-1PGDG.rhel8.x86_64 postgresql14-libs-14.5-1PGDG.rhel8.x86_64 postgresql14-server-14.5-1PGDG.rhel8.x86_64 完了しました! # dnf install -y postgresql14-contrib 依存関係が解決しました。 ================================================================================ パッケージ Arch バージョン Repo サイズ ================================================================================ インストール: postgresql14-contrib x86_64 14.5-1PGDG.rhel8 pgdg14 726 k トランザクションの概要 ================================================================================ インストール 1 パッケージ ダウンロードサイズの合計: 726 k インストール後のサイズ: 2.5 M パッケージのダウンロード: postgresql14-contrib-14.5-1PGDG.rhel8.x86_64.rp 203 kB/s | 726 kB 00:03 -------------------------------------------------------------------------------- 合計 203 kB/s | 726 kB 00:03 トランザクションの確認を実行中 トランザクションの確認に成功しました。 トランザクションのテストを実行中 トランザクションのテストに成功しました。 トランザクションを実行中 準備 : 1/1 インストール中 : postgresql14-contrib-14.5-1PGDG.rhel8.x86_64 1/1 scriptletの実行中: postgresql14-contrib-14.5-1PGDG.rhel8.x86_64 1/1 検証 : postgresql14-contrib-14.5-1PGDG.rhel8.x86_64 1/1 インストール済み: postgresql14-contrib-14.5-1PGDG.rhel8.x86_64 完了しました! #
これで PostgreSQL 公式サイトの rpm から PostgreSQL 14.5 が導入出来ました。DBの初期化も実施しておきましょう。
# export PGSETUP_INITDB_OPTIONS="--encoding=UTF-8 --no-locale"
# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK
# systemctl enable postgresql-14
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → /usr/lib/systemd/system/postgresql-14.service.
# systemctl start postgresql-14
# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vend>
Active: active (running) since Wed 2022-11-02 05:19:19 JST; 4s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 8619 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGD>
Main PID: 8624 (postmaster)
Tasks: 8 (limit: 4783)
Memory: 19.9M
CGroup: /system.slice/postgresql-14.service
tq8624 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
tq8626 postgres: logger
tq8628 postgres: checkpointer
tq8629 postgres: background writer
tq8630 postgres: walwriter
tq8631 postgres: autovacuum launcher
tq8632 postgres: stats collector
mq8633 postgres: logical replication launcher
11月 02 05:19:19 rockey86 systemd[1]: Starting PostgreSQL 14 database server...
11月 02 05:19:19 rockey86 postmaster[8624]: 2022-11-02 05:19:19.463 JST [8624] >
11月 02 05:19:19 rockey86 postmaster[8624]: 2022-11-02 05:19:19.463 JST [8624] >
11月 02 05:19:19 rockey86 systemd[1]: Started PostgreSQL 14 database server.
#
# su - postgres
$ psql
psql (14.5)
"help"でヘルプを表示します。
postgres=#
無事 PostgreSQL 14 が起動しました。
pgaudit と pgauditlogtofile のインストール
PostgreSQL 公式リポジトリに含まれる pgaudit と pgauditlogtofile の rpm パッケージ名を調べてみます。PostgreSQL 14 用の pgaudit のパッケージは pgaudit16_14、pgauditlogtofile のパッケージは pgauditlogtofile_14 もしくは pgauditlogtofile_14-llvmjit (JITコンパイル対応)となっています。
# dnf list | grep pgaudit pgaudit16_14.x86_64 1.6.2-1.rhel8 @pgdg14 pgauditlogtofile_14.x86_64 1.5.6-1.rhel8 @pgdg14 pgaudit12_10.x86_64 1.2.4-1.rhel8 pgdg10 pgaudit13_11.x86_64 1.3.4-1.rhel8 pgdg11 pgaudit14_12.x86_64 1.4.3-1.rhel8 pgdg12 pgaudit15_13.x86_64 1.5.2-1.rhel8 pgdg13 pgaudit17_15.x86_64 1.7.0-1.rhel8 pgdg15 pgaudit_analyze.x86_64 1.0.7-1.rhel8 pgdg-common pgaudit_analyze.noarch 1.0.8-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_10.x86_64 1.5.6-1.rhel8 pgdg10 pgauditlogtofile_11.x86_64 1.5.6-1.rhel8 pgdg11 pgauditlogtofile_11-llvmjit.x86_64 1.5.6-1.rhel8 pgdg11 pgauditlogtofile_12.x86_64 1.5.6-1.rhel8 pgdg12 pgauditlogtofile_12-llvmjit.x86_64 1.5.6-1.rhel8 pgdg12 pgauditlogtofile_13.x86_64 1.5.6-1.rhel8 pgdg13 pgauditlogtofile_13-llvmjit.x86_64 1.5.6-1.rhel8 pgdg13 pgauditlogtofile_14-llvmjit.x86_64 1.5.6-1.rhel8 pgdg14 pgauditlogtofile_15.x86_64 1.5.6-2.rhel8 pgdg15 pgauditlogtofile_15-llvmjit.x86_64 1.5.6-2.rhel8 pgdg15 #
今回は pgaudit と pgauditlogtofile のパッケージを導入します。
# dnf install -y pgaudit16_14 pgauditlogtofile_14 依存関係が解決しました。 ================================================================================ パッケージ Arch バージョン リポジトリー サイズ ================================================================================ インストール: pgaudit16_14 x86_64 1.6.2-1.rhel8 pgdg14 56 k pgauditlogtofile_14 x86_64 1.5.6-1.rhel8 pgdg14 18 k トランザクションの概要 ================================================================================ インストール 2 パッケージ ダウンロードサイズの合計: 74 k インストール後のサイズ: 124 k パッケージのダウンロード: (1/2): pgauditlogtofile_14-1.5.6-1.rhel8.x86_64 13 kB/s | 18 kB 00:01 (2/2): pgaudit16_14-1.6.2-1.rhel8.x86_64.rpm 37 kB/s | 56 kB 00:01 -------------------------------------------------------------------------------- 合計 49 kB/s | 74 kB 00:01 トランザクションの確認を実行中 トランザクションの確認に成功しました。 トランザクションのテストを実行中 トランザクションのテストに成功しました。 トランザクションを実行中 準備 : 1/1 インストール中 : pgaudit16_14-1.6.2-1.rhel8.x86_64 1/2 インストール中 : pgauditlogtofile_14-1.5.6-1.rhel8.x86_64 2/2 scriptletの実行中: pgauditlogtofile_14-1.5.6-1.rhel8.x86_64 2/2 検証 : pgaudit16_14-1.6.2-1.rhel8.x86_64 1/2 検証 : pgauditlogtofile_14-1.5.6-1.rhel8.x86_64 2/2 インストール済み: pgaudit16_14-1.6.2-1.rhel8.x86_64 pgauditlogtofile_14-1.5.6-1.rhel8.x86_64 完了しました! #
これで pgaudit と pgauditlogtofile がインストールされました。
postgresql.conf の shared_preload_libraries の設定と有効化確認
shared_preload_libraries に共有ライブラリの pgaudit,pgauditlogtofile を設定して PostgreSQL を再起動し、新規に db1 データベースを作成後、pgaudit が有効になっていることを確認します。
$ cd $PGDATA $ pwd /var/lib/pgsql/14/data $ vi postgresql.conf shared_preload_libraries = 'pgaudit, pgauditlogtofile' # systemctl restart postgresql-14.service $ createdb db1 $ psql db1 psql (14.5) "help"でヘルプを表示します。 db1=# show shared_preload_libraries; shared_preload_libraries --------------------------- pgaudit, pgauditlogtofile (1 行)
CREATE EXTENSION を実行してエクステンションに登録
使用したいデータベースに psql で接続して、pgaudit と pgauditlogtofile で CREATE EXTENSION を実行します。エクステンションが登録されていることを確認します。
db1=# CREATE EXTENSION pgaudit; CREATE EXTENSION db1=# CREATE EXTENSION pgauditlogtofile; CREATE EXTENSION db1=# \dx インストール済みの拡張一覧 名前 | バージョン | スキーマ | 説明 ------------------+------------+------------+------------------------------------------------------------ pgaudit | 1.6.2 | public | provides auditing functionality pgauditlogtofile | 1.5 | public | pgAudit addon to redirect audit log to an independent file plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (3 行) db1=#
これで pgAudit と pgAudit Log to File のインストールは完了しました。
セッション監査ロギングを有効にして分離した監査ログファイルを確認
監査ログをセッション単位で取得するセッション監査ロギングを試して監査ログファイルが分離されることを確認します。postgresql.conf ファイルに pgaudit.log = ‘ALL’ を追加し、PostgreSQL に設定を読み込ませます。
$ cd $PGDATA $ vi postgresql.conf pgaudit.log = 'ALL' # systemctl reload postgresql-14.service $ cd $PGDATA/log $ ls postgresql-Wed.log $ tail -3 postgresql-Wed.log 2022-11-02 05:30:01.537 JST [9842] LOG: database system is ready to accept connections 2022-11-02 05:37:08.831 JST [9842] LOG: received SIGHUP, reloading configuration files 2022-11-02 05:37:08.831 JST [9842] LOG: parameter "pgaudit.log" changed to "ALL"
table1 を作成してSELECT 文を実行したところ、無事 PostgreSQL ログとは別に新規に作成された監査ログファイル(audit-YYYYMMDD_0000.log)に記録されました。
$ psql db1 db1=# CREATE TABLE table1 (i1 integer, t1 text); CREATE TABLE db1=# INSERT INTO table1 VALUES (1, 'aaa'),(2, 'bbb'); INSERT 0 2 db1=# SELECT * FROM table1; i1 | t1 ----+----- 1 | aaa 2 | bbb (2 行) db1=# \q $ cd $PGDATA/log $ ls -tl 合計 8 -rw-------. 1 postgres postgres 651 11月 2 05:39 audit-20221102_0000.log -rw-------. 1 postgres postgres 1972 11月 2 05:37 postgresql-Wed.log $ cat audit-20221102_0000.log 2022-11-02 05:39:32.716 JST,postgres,db1,10095,[local],636183eb.276f,1,CREATE TABLE,2022-11-02 05:39:07 JST,3/24,737,00000,SESSION,1,1,DDL,CREATE TABLE,TABLE,public.table1,"CREATE TABLE table1 (i1 integer, t1 text);",<not logged>,,,,,,,,,psql 2022-11-02 05:39:43.309 JST,postgres,db1,10095,[local],636183eb.276f,2,INSERT,2022-11-02 05:39:07 JST,3/25,0,00000,SESSION,2,1,WRITE,INSERT,,,"INSERT INTO table1 VALUES (1, 'aaa'),(2, 'bbb');",<not logged>,,,,,,,,,psql 2022-11-02 05:39:49.228 JST,postgres,db1,10095,[local],636183eb.276f,3,SELECT,2022-11-02 05:39:07 JST,3/26,0,00000,SESSION,3,1,READ,SELECT,,,SELECT * FROM table1;,<not logged>,,,,,,,,,psql $
pgaudit.log_connections, pgaudit.log_disconnections について
postgresql.conf ファイルで log_connections = on と log_disconnections = on を設定した上で pg_audit.log_connections = on と pg_audit.log_disconnections = on と設定して PostgreSQL に設定を再読み込みさせると、監査ログファイルにログイン情報とログアウト情報が記録されます。
$ cd $PGDATA $ vi postgresql.conf log_connections = on log_disconnections = on pg_audit.log_connections = on pg_audit.log_disconnections = on # systemctl reload postgresql-14.service $ cd $PGDATA/log $ tail -3 postgresql-Wed.log 2022-11-02 06:12:30.368 JST [9842] LOG: received SIGHUP, reloading configuration files 2022-11-02 06:12:30.368 JST [9842] LOG: parameter "log_connections" changed to "on" 2022-11-02 06:12:30.368 JST [9842] LOG: parameter "log_disconnections" changed to "on" 2022-11-02 06:12:30.368 JST [9842] LOG: parameter "pg_audit.log_connections" changed to "on" 2022-11-02 06:12:30.368 JST [9842] LOG: parameter "pg_audit.log_disconnections" changed to "on"
現在のバージョンでは、ログインが成功すると、監査ログファイルにのみ情報が記録されますが、ログインが失敗した場合は、ログインに失敗したこと自体は監査ログに記録されずに PostgreSQL ログのみに記録される点には注意が必要です。
[ログイン/ログアウト成功時] $ psql db1 db1=# \q $ tail -f audit-20221102_0000.log 2022-11-02 06:13:15.781 JST,,,11664,[local],63618beb.2d90,1,,2022-11-02 06:13:15 JST,,0,00000,connection received: host=[local],,,,,,,,, 2022-11-02 06:13:15.782 JST,postgres,db1,11664,[local],63618beb.2d90,2,authentication,2022-11-02 06:13:15 JST,3/140,0,00000,connection authenticated: identity="postgres" method=peer (/var/lib/pgsql/14/data/pg_hba.conf:85),,,,,,,,, 2022-11-02 06:13:15.782 JST,postgres,db1,11664,[local],63618beb.2d90,3,authentication,2022-11-02 06:13:15 JST,3/140,0,00000,connection authorized: user=postgres database=db1 application_name=psql,,,,,,,,, 2022-11-02 06:13:22.678 JST,postgres,db1,11664,[local],63618beb.2d90,4,idle,2022-11-02 06:13:15 JST,,0,00000,disconnection: session time: 0:00:06.896 user=postgres database=db1 host=[local],,,,,,,,,psql [ログイン失敗時] $ psql db1 -U nouser psql: エラー: ソケット"/var/run/postgresql/.s.PGSQL.5432"上でのサーバーへの接続に失敗しました: FATAL: Peer authentication failed for user "nouser" $ tail -f audit-20221102_0000.log 2022-11-02 06:14:29.051 JST,,,11676,[local],63618c35.2d9c,1,,2022-11-02 06:14:29 JST,,0,00000,connection received: host=[local],,,,,,,,, 2022-11-02 06:14:29.052 JST,nouser,db1,11676,[local],63618c35.2d9c,2,authentication,2022-11-02 06:14:29 JST,3/145,0,00000,connection authenticated: identity="postgres" method=peer (/var/lib/pgsql/14/data/pg_hba.conf:85),,,,,,,,, $ tail -f postgresql-Wed.log 2022-11-02 06:14:29.052 JST [11676] LOG: provided user name (nouser) and authenticated user name (postgres) do not match 2022-11-02 06:14:29.052 JST [11676] FATAL: Peer authentication failed for user "nouser" 2022-11-02 06:14:29.052 JST [11676] DETAIL: Connection matched pg_hba.conf line 85: "local all all peer" $
終わりに
pgAudit 単体では監査の記録が PostgreSQL ログと混在していましたが、pgAudit Log To File を追加で導入することで、監査の記録を PostgreSQL ログとは別の独立した監査ログファイルに記録することが出来るため、PostgreSQL ログに混在した監査ログの分離が不要となり PostgreSQL ログの扱いがこれまでと同様に出来るため非常に便利です。
pgAudit を新規に導入する際は pgAudit Log to File もセットで導入、pgAudit が既に導入されている環境には pgAudit Log to File を追加で導入することをお勧めします。