皆さんこんにちは！
今回はPostgreSQLの監査について簡単にまとめていきます。
そもそも監査とは
DBの動きの証拠を記録していく機能です。
この機能のおかげで何か問題が発生した際に対応がしやすくなります。
PostgreSQL の監査について
PostgreSQLの監査ログには以下のものがあります。
pgauditによる取得
-
セッション監査ロギング
pgAudit パラメータ pgaudit.log に設定したクラス(READ / WRITE / FUNCTION / ROLE / DDL / MISC / MISC_SET/ALL)の中から取得範囲を指定できます。 -
オブジェクト監査ロギング
pgAudit のパラメータである pgaudit.role に設定したロールに対して設定することで、そのロールの操作がすべて記録されます。すべて記録すると膨大な量の監査ログが取得されてしまいますが、特定のテーブル等に対する操作だけを監査することも可能です。
log_statementパラメータによる取得
postgresql.confにログの出力を制御する log_statementパラメータを設定することで、監査ログの取得を行うことが可能です。
ただし、取得したログは内容が不足しているため、監査を行い難いというデメリットもあるようです。
準備
PosgreSQL がインストールされている前提で進めます。
インストール方法についてはこちら
1.pgaudit のパッケージを検索
[root@postgresql yum.repos.d]# yum search pgaudit
サブスクリプション管理リポジトリーを更新しています。
コンシューマー識別子を読み込めません
このシステムは、エンタイトルメントサーバーに登録されていません。subscription-manager で登録できます。
メタデータの期限切れの最終確認: 2:39:34 時間前の 2022年11月06日 19時24分10秒 に実施しました。
==================================================== 名前 & 概要 一致: pgaudit =====================================================
pgauditlogtofile_11-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
pgauditlogtofile_12-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
pgauditlogtofile_13-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
pgauditlogtofile_14-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
pgauditlogtofile_15-llvmjit.x86_64 : Just-in-time compilation support for pgauditlogtofile
======================================================== 名前 一致: pgaudit ========================================================
pgaudit12_10.x86_64 : PostgreSQL Audit Extension
pgaudit13_11.x86_64 : PostgreSQL Audit Extension
pgaudit14_12.x86_64 : PostgreSQL Audit Extension
pgaudit15_13.x86_64 : PostgreSQL Audit Extension
pgaudit16_14.x86_64 : PostgreSQL Audit Extension
pgaudit17_15.x86_64 : PostgreSQL Audit Extension
pgaudit_analyze.x86_64 : PostgreSQL Audit Analyzer
pgaudit_analyze.noarch : PostgreSQL Audit Analyzer
pgauditlogtofile-10.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile-11.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile-12.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile-13.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_10.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_11.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_12.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_13.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_14.x86_64 : PostgreSQL Audit Log To File Extension
pgauditlogtofile_15.x86_64 : PostgreSQL Audit Log To File Extension
2.pgaudit のパッケージをインストール
[root@postgresql yum.repos.d]# yum install pgaudit17_15.x86_64
サブスクリプション管理リポジトリーを更新しています。
コンシューマー識別子を読み込めません
このシステムは、エンタイトルメントサーバーに登録されていません。subscription-manager で登録できます。
メタデータの期限切れの最終確認: 2:39:42 時間前の 2022年11月06日 19時24分10秒 に実施しました。
依存関係が解決しました。
====================================================================================================================================
パッケージ アーキテクチャー バージョン リポジトリー サイズ
====================================================================================================================================
インストール:
pgaudit17_15 x86_64 1.7.0-1.rhel8 pgdg15 56 k
トランザクションの概要
====================================================================================================================================
インストール 1 パッケージ
ダウンロードサイズの合計: 56 k
インストール後のサイズ: 98 k
これでよろしいですか? [y/N]: y
パッケージのダウンロード:
pgaudit17_15-1.7.0-1.rhel8.x86_64.rpm 35 kB/s | 56 kB 00:01
------------------------------------------------------------------------------------------------------------------------------------
合計 35 kB/s | 56 kB 00:01
トランザクションの確認を実行中
トランザクションの確認に成功しました。
トランザクションのテストを実行中
トランザクションのテストに成功しました。
トランザクションを実行中
準備 : 1/1
インストール中 : pgaudit17_15-1.7.0-1.rhel8.x86_64 1/1
scriptletの実行中: pgaudit17_15-1.7.0-1.rhel8.x86_64 1/1
検証 : pgaudit17_15-1.7.0-1.rhel8.x86_64 1/1
インストール済みの製品が更新されています。
インストール済み:
pgaudit17_15-1.7.0-1.rhel8.x86_64
完了しました!
3.postgresql.conf に shared_preload_libraries='pgaudit'を追記する
[postgres@postgresql ~]$ vi /var/lib/pgsql/15/data/postgresql.conf
[postgres@postgresql ~]$ cat /var/lib/pgsql/15/data/postgresql.conf | grep shared_preload
shared_preload_libraries = 'pgaudit' # (change requires restart)
4.PostgreSQL サービスを再起動
[root@postgresql pgsql]# systemctl restart postgresql-15.service
[root@postgresql pgsql]#
5.pgaudit をエクステンションに登録
[postgres@postgresql ~]$ psql -U postgres
psql (15.0)
"help"でヘルプを表示します。
postgres=# CREATE EXTENSION pgaudit;
CREATE EXTENSION
postgres=# \dx
インストール済みの拡張一覧
名前 | バージョン | スキーマ | 説明
---------+------------+------------+---------------------------------
pgaudit | 1.7 | public | provides auditing functionality
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 行)
セッション監査ロギング
1.監査対象に対して alter database set pgaudit.log='All'; を実行
db1に対して ALL のクラスを設定します。
postgres=# create database db1;
CREATE DATABASE
postgres=# alter database db1 set pgaudit.log='ALL';
ALTER DATABASE
2.監査ログの出力場所確認
特に出力場所を決めていないですが、以下のディレクトリ配下に監査ログがありました。
[postgres@postgresql log]$ cd /var/lib/pgsql/15/data/log
[postgres@postgresql log]$ ls
postgresql-Mon.log postgresql-Sun.log
3.テストデータの作成
postgres=# \c db1
データベース"db1"にユーザー"postgres"として接続しました。
db1=# create table ta_1(id integer,name varchar(10));
CREATE TABLE
db1=# insert into ta_1 values(1,'tanaka');
INSERT 0 1
4.監査ログの確認
db1に対する操作内容が監査されています。
[postgres@postgresql log]$ cat postgresql-Mon.log
2022-11-07 00:54:54.395 EST [4452] LOG: AUDIT: SESSION,5,1,DDL,CREATE TABLE,,,"create table ta_1(id integer,name varchar(10));",<not logged>
2022-11-07 00:55:13.718 EST [4452] STATEMENT: insert into ta_1(1,'tanaka');
2022-11-07 00:56:04.641 EST [4616] LOG: AUDIT: SESSION,1,1,WRITE,INSERT,,,"insert into ta_1 values(1,'tanaka');",<not logged>
オブジェクト監査ロギング
1.auditroleを作成
postgres=# CREATE ROLE auditrole;
CREATE ROLE
2.設定変更
[postgres@postgresql ~]$ vi /var/lib/pgsql/15/data/postgresql.conf
pgaudit.role = 'auditrole'
3.PostgreSQLを再起動
[postgres@postgresql ~]$ pg_ctl stop
サーバーは停止しました
[postgres@postgresql ~]$ pg_ctl start
サーバー起動完了
4.テストデータ作成
postgres=# create table ta_1(id integer,name varchar(10));
CREATE TABLE
postgres=# insert into ta_1 values(1,'tanaka');
INSERT 0 1
postgres=# select * from ta_1;
id | name
----+--------
1 | tanaka
(1 行)
5.name列に対する監視権限を auditrole に付与
postgres=# grant select(name) on ta_1 to auditrole;
GRANT
6.テーブル参照
postgres=# select * from ta_1;
id | name
----+--------
1 | tanaka
(1 行)
postgres=# select id,name from ta_1;
id | name
----+--------
1 | tanaka
(1 行)
postgres=# select id from ta_1;
id
----
1
(1 行)
postgres=# select name from ta_1;
name
--------
tanaka
(1 行)
7.監査ログの確認
出力された監査ログを見ると name 列に関するselect文のみ監視していることがわかります。
id に絞ったselect文は出力されていません。
[postgres@postgresql log]$ ll
合計 4
-rw-------. 1 postgres postgres 703 11月 9 01:51 postgresql-Wed.log
[postgres@postgresql log]$ tail -f postgresql-Wed.log
2022-11-09 01:51:10.499 EST [2716] LOG: starting PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
2022-11-09 01:51:10.499 EST [2716] LOG: listening on IPv6 address "::1", port 5432
2022-11-09 01:51:10.499 EST [2716] LOG: listening on IPv4 address "127.0.0.1", port 5432
2022-11-09 01:51:10.500 EST [2716] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-11-09 01:51:10.502 EST [2716] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-11-09 01:51:10.504 EST [2720] LOG: database system was shut down at 2022-11-09 01:50:53 EST
2022-11-09 01:51:10.507 EST [2716] LOG: database system is ready to accept connections
2022-11-09 01:52:20.996 EST [2727] LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.ta_1,select * from ta_1;,<not logged>
2022-11-09 01:52:26.582 EST [2727] LOG: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.ta_1,"select id,name from ta_1;",<not logged>
2022-11-09 01:52:36.394 EST [2727] LOG: AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.ta_1,select name from ta_1;,<not logged>
以上です！