はじめに

こんにちは！NTTテクノクロスでPostgreSQLの技術支援をやっています、中村です。

昨年のNTTテクノクロス Advent Calendar 2023で pgauditを使ったPostgreSQLのセッション監査について記事を書きました。
PostgreSQLの監査はpgauditで！DBユーザ単位のセッション監査を使ってみた！

今回は続編ということで、pgauditのオブジェクト監査について触れていきたいと思います。

pgauditを使ったオブジェクト監査まとめ

それでは今回も最初に、pgauditのオブジェクト監査について整理していきましょう。
オブジェクト監査とは、特定のオブジェクト（テーブル）に対して 誰が（どのDBユーザが）、どのような操作を行ったかを記録することを指します。

・オブジェクト監査はテーブル単位、列単位で設定できる
pgauditのオブジェクト監査では、全てのテーブルを監査の対象とすることもできますし、 監査の対象を特定テーブルの列単位にまで限定することも可能です。

・オブジェクト監査では監査対象の操作を指定できる
SELECTやUPDATEなど、監査の対象としたいSQLコマンドを指定できます。 これは、GRANT文で指定できるレベルと同じになります。
また、テーブルごとに異なる条件（tbl1テーブルはINSERTのみ、tbl2テーブルはUPDATEのみなど）の監査条件を指定可能です。

・オブジェクト監査はデフォルト無効
pgauditのインストール直後はデフォルトで監査はoff（postgresql.conf のパラメータ pgaudit.role = 設定なし） になっていますので、pgaudit.role = DBユーザ名 を設定して再起動することで、オブジェクト監査の設定を行います。
※設定するべきDBユーザ名については後述します。
　pgaudit.role に設定するDBユーザ名は、監査したいDBユーザ名を指定するのではないことだけ覚えておきましょう。

・オブジェクト監査の結果はPostgreSQLのサーバログに出力される
オブジェクト監査のログは、セッション監査のログと同様に、PostgreSQLのサーバログに一緒に出力されます。
ログには「AUDIT:」という文字列が含まれていますので、ユーザがログを分類することは比較的容易です。

セッション監査とオブジェクト監査の違い

セッション監査はDBユーザの操作を監査の対象としますが、 オブジェクト監査では、テーブルへの操作を監査の対象とします。

監査方法	監査の主体	監査の粒度	監査対象のDBユーザ
オブジェクト監査	テーブル	テーブル、列単位	全DBユーザ
セッション監査	DBユーザ	DBユーザ単位	監査設定を行ったDBユーザのみ

誰でもアクセスできるテーブル「tbl1」に対しての監査を設定したい場合、 セッション監査では、全DBユーザに対して監査の設定を行う必要がありますが、 オブジェクト監査では、対象テーブルに対しての監査設定を行うと、 全DBユーザの誰が操作しても監査対象になります。

オブジェクト監査の仕組み

オブジェクト監査では、まず、監査対象とするテーブルと、その監査条件とする操作（SELECTやINSERTなど）を設定します。 ただし、オブジェクト監査における条件設定はパラメータでは行いません。
（ここがオブジェクト監査のわかりづらいところです）

設定にパラメータを使用する代わりに、監査条件を保持するための監査用DBユーザを一つ用意し、 この監査用DBユーザを「オブジェクト監査のフィルタ」として使用します。

ほかの誰かがテーブルに対して何らかの操作を行った際、監査用DBユーザが 同様の操作を行う権限を持っていた場合、その操作内容をログに出力させます。

図：フィルタのイメージ

オブジェクト監査のためにユーザが実施する設定作業は以下になります。
①postgresql.conf の pgaudit.role パラメータへ、監査用DBユーザを指定して再起動する
②監査用DBユーザに対して、各テーブルへの操作権限を付与する

例えば、監査用DBユーザを「audit_master」という名称で作成する場合、 この「audit_master」ユーザに、tbl1テーブルへのINSERT権限とUPDATE権限を付与すると、 だれかが tbl1テーブルにINSERTまたはUPDATEを行った場合に、監査ログが出力されます。

このとき、INSERTやUPDATE以外の操作（SELECTやDELETEなど）を行っても、「audit_master」ユーザにはSELECT権限も DELETE権限もないので、監査ログは出力されません。

これと同様に、監査用DBユーザが操作権限を持たないオブジェクト（テーブル）は 監査の対象外となります。

また、監査用DBユーザ「audit_master」を作成した直後に、pgaudit.role = audit_master に設定したとしても、 そのままでは「audit_master」ユーザには何の権限もないため、 監査ログは出力されません。

■POINT■
・パラメータ pgaudit_role に設定するDBユーザは「オブジェクト監査用フィルタ」として使用する
・「オブジェクト監査用フィルタ」としたDBユーザが持つオブジェクト権限と同じ操作を行った場合、監査ログに出力される

pgauditのオブジェクト監査の設定方針

オブジェクト監査の場合、テーブル単位に監査方針を決められますので、 以下の例のように、テーブルの用途によって監査目的を明確にし、監査する操作を使い分けることが可能です。

テーブルの用途（例）	監査目的	監査対象	監査する操作
管理者が操作できるテーブル	全ての操作の追跡	全テーブル	ALL
通常更新されないテーブルなど	マスタ情報の改ざん追跡	特定テーブル	INSERT、UPDATE、DELETE
会員情報テーブルなど	個人情報が含まれる特定列の、参照の追跡	特定テーブルの列	SELECT

上記では、管理者はロールの管理やメンテナンス処理の運用目的でのみ使用すると規定している場合、オブジェクトへのアクセスは基本的にはないはずなので、全ての操作を記録するようにします。
また、マスタテーブルは定期的な更新は考えられるものの、通常の業務運用中に変更されない前提の場合であれば、更新処理を記録しておくことは監査としてはあり得ます。

このように、留意するのは「業務上、何を監査したいのかを明確にしておくこと」です。

オブジェクト監査の設定方法

それでは、オブジェクト監査の設定方法を具体的なコマンドを使って見ていきましょう。
監査用DBユーザを「audit_master」としたとき、各目的ごとに以下の操作を行います。

共通して実施する作業

viコマンドなどで postgresql.conf を編集し、pgaudit.role=audit_master に変更して PostgreSQLを再起動します。

目的によって異なる監査設定

目的ごとに、以下の操作を postgres スーパーユーザか、テーブルのオーナーのDBユーザなどで実施します。
今回は対象のテーブルを変えて、すべてを実施してみます。

（１）全てのテーブルの、全ての操作を監査したい場合

# GRANT ALL ON ALL TABLES IN SCHEMA public TO audit_master;

また、今後追加されるテーブルも監査対象としたい場合は、以下も実施します。

# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO audit_master;

（２）特定のテーブル「tbl1」に対する、INSERT、UPDATE、DELETE文だけを監査したい場合

# GRANT INSERT,UPDATE,DELETE ON public.tbl1 TO audit_master;

（３）特定のテーブル「table2」のカラム「col4」のUPDATE操作だけを監査したい場合

# GRANT SELECT(col4) ON public.table2 TO audit_master;

既存のテーブルに対して権限が付与されたことを確認する

上記（１）～（３）の設定を行った後の、テーブルのアクセス権限の状態を確認します。

testdb=# \dp
アクセス権限
スキーマ |  名前   |  タイプ  |       アクセス権限        |         列の権限          | ポリシー 
----------+---------+----------+---------------------------+---------------------------+----------
public   | table2  | テーブル |                           | col4:                    +| 
|         |          |                           |   audit_master=w/testuser | 
public   | tbl1    | テーブル | postgres=arwdDxt/postgres+|                           | 
|         |          | audit_master=awd/postgres |                           | 
(2 行)

「アクセス権限」の列を見ると、tbl1 テーブルに対して awd の権限が付与されており、table2 テーブルの col4列に対して w の権限が付与されていることがわかりますね。

上記コマンドのアクセス権限は以下の意味を持ちます。
a：追加（INSERT）
w：更新（UPDATE）
d：削除（DELETE）

また、今後作成されるテーブルに対して、権限が付与される設定となっているかは以下のコマンドで確認します。

testdb=# \ddp
デフォルトのアクセス権限
所有者  | スキーマ |  タイプ  |      アクセス権限       
----------+----------+----------+-------------------------
postgres | public   | テーブル | audit_master=w/postgres
(1 行)

こちらも、public スキーマ上のテーブルに対して、w の権限が付与されていることがわかります。

オブジェクト監査ログの例

「tbl1」テーブルに対し、1行 INSERT した際の監査ログを例示します。

2024-03-15 15:04:36.263 EDT [19164] LOG:  AUDIT: OBJECT,1,1,WRITE,INSERT,TABLE,public.tbl1,"insert into tbl1 values (102,'CCCC');",

それでは、上記のログを解析してみましょう。
・「AUDIT: OBJECT」という文字列がありますね。これがオブジェクト監査ログであることを示しています。
・「1,1」は、セッション内で一意なSQLのIDです。監査条件によっては同じSQLで複数のログが出力されますので、どの条件に該当する監査ログなのかを判断するために使用します。
・「WRITE,INSERT」が、監査ログを出力する要因となった処理の種別になります。
・「TABLE,public.tbl1」が監査されたオブジェクトの種別と、オブジェクト名になります。
・最後に、監査の契機となったSQL文が記録されます。

まとめ

今回は pgauditを使用したオブジェクト監査の設定方法について解説しました。
オブジェクト監査は設定方法が直感的ではないため少しわかりづらいですが、一度構造が理解できてしまえば そんなに難しくはありません。

DBAとしては、セッション監査とオブジェクト監査の特徴を比較し、業務要件にあった監査方法を適用できるようにしておきましょう。

それでは、本記事は以上となります！

PostgreSQLのことならNTTテクノクロスにおまかせください！

NTTテクノクロス株式会社ではPostgreSQLに関する各種のお問い合わせや、移行に関する対応を受け付けています。
オンプレだけではなく、Azure、AWSなどのクラウド上でのシステムの導入、開発、維持管理の際にもご活用ください。

移行サービスはこちら