あっつんブログ

  1. Home
  2. ＩＴ関連
  3. Aurora PostgreSQL pgaudit オブジェクト監査の挙動

Aurora PostgreSQL pgaudit オブジェクト監査の挙動

  • Audit
  • PostgreSQL
  • RDS
  • SQL
    •

    オブジェクト監査とは

    前回「セッション監査の挙動」の続きです。

    Aurora PostgreSQL には以下の大きく分けて3つの監査機能があります。

    • ログイン監査（標準機能）
    • セッション監査（pgaudit）
    • オブジェクト監査（pgaudit）

    このうち「オブジェクト監査（pgaudit）」についての挙動を詳しく見ていきたいと思います。

    オブジェクト監査

    セッション監査は、データベースレベル、ロールレベルで設定することができる監査機能でした。

    オブジェクト監査は、テーブル単位で select, insert, update, delete を監視するための設定です。

    個人情報を格納するテーブルに関しては、全てのDML操作を監査ログに記録しておくことがセキュリティ要件とされています。

    また、情報漏洩などのセキュリティインシデントが発生した場合、どのデータが抜かれたのか調査出来るようにしておく必要もあります。

    特定のテーブルに対してのみ監査設定をしたい場合は、オブジェクト監査が有効です。

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

    初期状態

    Aurora PostgreSQL をデフォルトのパラメータで構築したばかりの状態では以下のような結果となります。

    testdb01=> show shared_preload_libraries; shared_preload_libraries ----------------------------- rdsutils,pg_stat_statements (1 row) testdb01=> select name,setting,source from pg_settings where name='pgaudit.log'; name | setting | source ------+---------+-------- (0 rows) testdb01=> show pgaudit.role; ERROR: unrecognized configuration parameter "pgaudit.role"

    手順1 shared_preload_libraries

    まずは、shared_preload_libraries に pgaudit を追加し、再起動します。

    postgresql-audit-03

    以下のように設定追加されていることが確認できます。

    testdb01=> show shared_preload_libraries; shared_preload_libraries ------------------------------------- rdsutils,pg_stat_statements,pgaudit (1 row) testdb01=> select name,setting,source from pg_settings where name='pgaudit.log'; name | setting | source -------------+---------+--------- pgaudit.log | none | default (1 row) testdb01=> show pgaudit.role; pgaudit.role -------------- (1 row)

    pgaudit.role は null です。

    手順2 rds_pgrole を設定

    オブジェクト監査では、監査用のロールを作成する必要があります。

    testdb01=> create ROLE rds_pgaudit; CREATE ROLE

    この監査用のロールにテーブルへの DML 権限を付与すると、その権限の範囲が監査対象となります。

    ちなみに、Aurora PostgreSQL では pgaudit.role に許可された値は rds_pgaudit のみです。つまり、Aurora PostgreSQL では監査用ロールは rds_pgaudit 固定です。

    postgresql-audit-04

    dynamic に更新されますので繋いでいるセッションのまま、pgaudit.role を確認してみましょう。

    postgres=> show pgaudit.role; pgaudit.role -------------- rds_pgaudit (1 row)

    手順3 create EXTENSION

    pgaudit 拡張機能を有効化します。

    testdb01=> create EXTENSION pgaudit; CREATE EXTENSION testdb01=> select * from pg_extension; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition ---------+----------+--------------+----------------+------------+-----------+-------------- plpgsql | 10 | 11 | f | 1.0 | | pgaudit | 10 | 2200 | t | 1.3 | | (2 rows)

    手順4 grant to rds_pgaudit

    では、rds_pgaudit に監査対象テーブルの select 権限を与えて監査ログが出力されるか確認します。

    1. テーブルのアクセス権を確認 testdb01=> \z Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+----------+-------+-------------------+-------------------------- public | employee | table | | (1 row) 2. rds_pgaudit に監査対象テーブルの select 権限を付与 testdb01=> grant select on employee to rds_pgaudit; GRANT 3. 再度、テーブルのアクセス権を確認 testdb01=> \z Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+----------+-------+---------------------------+-------------------------- public | employee | table | postgres=arwdDxt/postgres+| | | | rds_pgaudit=r/postgres | (1 row) 4. 同一セッションで select を実行 testdb01=> select * from employee; family_id | first_name | last_name -----------+------------+------------ 1 | atsushi | koizumi 2 | emiri | moromizato (2 rows) 5. sample log 2020-07-11 16:45:21 UTC:10.112.1.139(36580):postgres@testdb01:[5073]:LOG: AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.employee,select * from employee;,

    "AUDIT: OBJECT" の部分がオブジェクト監査のログ出力であることを示しています。

    また、オブジェクト監査は rds_pgaudit に権限付与した瞬間から即時反映されるようです。

    オブジェクト監査の抜け道

    それでは、オブジェクト監査の抜け道があるか、見ていきましょう。

    オブジェクト監査を外してから select を行います。

    1. rds_pgaudit から select 権限をはく奪 testdb01=> revoke select on employee from rds_pgaudit; REVOKE 2. select 実行 testdb01=> select * from employee; family_id | first_name | last_name -----------+------------+------------ 1 | atsushi | koizumi 2 | emiri | moromizato (2 rows) 3. sample log なし

    ログに出ません。オブジェクト監査を外したこと（rds_pgaudit から select 権限をはく奪）が監査ログに出力されません。

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

    これを解決するには、セッション監査で revoke 文（DDL）を監査する必要があります。

    DDL のセッション監査は以下の方法で行えます。

    • パラメータグループで pgaudit.log= ddl を設定
    • alter database testdb01 set pgaudit.log='ddl';
    • alter role postgres set pgaudit.log='ddl';

    rds_pgaudit が与えられている権限の確認方法

    \z

    テーブルに grant されている権限を確認する方法あいくつかあります。

    その1つ目が \z です。

    testdb01=> \z Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+----------+-------+------------------------------+-------------------------- public | customer | table | postgres=arwdDxt/postgres +| | | | rds_pgaudit=rwd/postgres | public | employee | table | postgres=arwdDxt/postgres | public | family | table | postgres=arwdDxt/postgres +| | | | rds_pgaudit=arwdDxt/postgres | (3 rows)

    role_table_grants

    2つ目が information_schema にある role_table_grants です。

    権限を付与されている対象が grantee ですので、where 句で grantee='rds_pgaudit' を指定してます。

    testdb01=> select grantee,table_schema,table_name,privilege_type testdb01-> from information_schema.role_table_grants testdb01-> where grantee='rds_pgaudit'; grantee | table_schema | table_name | privilege_type -------------+--------------+------------+---------------- rds_pgaudit | public | family | INSERT rds_pgaudit | public | family | SELECT rds_pgaudit | public | family | UPDATE rds_pgaudit | public | family | DELETE rds_pgaudit | public | family | TRUNCATE rds_pgaudit | public | family | REFERENCES rds_pgaudit | public | family | TRIGGER rds_pgaudit | public | customer | SELECT rds_pgaudit | public | customer | UPDATE rds_pgaudit | public | customer | DELETE (10 rows)

    has_table_privilege

    あまり知られていないのがこちらの has_table_privilege です。

    has_table_privilege の詳しい使用方法はこちらを参照ください。

    実際にやってみます。

    1. rds_pgaudit の oid を参照 testdb01=> select rolname,oid from pg_roles; rolname | oid ---------------------------+------- pg_monitor | 3373 ... rds_pgaudit | 24586 rdsadmin | 10 (16 rows) 2. public スキーマにあるテーブルの oid を参照 testdb01=> select c.oid,c.relname,n.nspname testdb01-> from pg_class c testdb01-> left outer join pg_namespace n on c.relnamespace=n.oid testdb01-> where n.nspname in ('public') testdb01-> ; oid | relname | nspname -------+----------+--------- 24587 | employee | public 28687 | family | public 32778 | customer | public (3 rows) 3. rds_pgaudit(24586) が employee(24587) に "SELECT" 権限があるか確認 testdb01=> select has_table_privilege(24586,24587,'SELECT'); has_table_privilege --------------------- f (1 row)

    PostgreSQL で本当に欲しい情報を探すときは pg_catalog や information_schema を参照していく必要があります。

    ドキュメントとにらめっこしながら欲しい情報を探すのは苦行ですが。。。

    has_table_privilege 応用版

    role_table_grants の結果をテーブル単位で横もちのデータに変換するでも良いですが、今回は has_table_privilege を使って rds_pgaudit の持っている権限を見やすく抽出してみました。

    rds_pgaudit_privilege.sql
    SELECT r.rolname AS "RoleName", n.nspname AS "Schema", c.relname AS "Table", case WHEN c.relkind = 'r' THEN 'Table' WHEN c.relkind = 'v' THEN 'View' WHEN c.relkind = 'S' THEN 'Sequence' WHEN c.relkind = 'm' THEN 'Materialized View' WHEN c.relkind = 'p' THEN 'Partitioned Table' WHEN c.relkind = 'f' THEN 'Foreign Table' WHEN c.relkind = 't' THEN 'TOAST Table' WHEN c.relkind = 'I' THEN 'Partitioned Index' WHEN c.relkind = 'c' THEN 'Composite Type' END AS "Type", case WHEN has_table_privilege(r.oid, c.oid, 'SELECT') AND has_table_privilege(r.oid, c.oid, 'INSERT') AND has_table_privilege(r.oid, c.oid, 'UPDATE') AND has_table_privilege(r.oid, c.oid, 'DELETE') AND has_table_privilege(r.oid, c.oid, 'TRUNCATE') AND has_table_privilege(r.oid, c.oid, 'REFERENCES') AND has_table_privilege(r.oid, c.oid, 'TRIGGER') THEN 1 ELSE 0 END AS "ALL", case WHEN has_table_privilege(r.oid, c.oid, 'SELECT') THEN 1 ELSE 0 END AS "SEL", case WHEN has_table_privilege(r.oid, c.oid, 'INSERT') THEN 1 ELSE 0 END AS "INS", case WHEN has_table_privilege(r.oid, c.oid, 'UPDATE') THEN 1 ELSE 0 END AS "UPD", case WHEN has_table_privilege(r.oid, c.oid, 'DELETE') THEN 1 ELSE 0 END AS "DEL", case WHEN has_table_privilege(r.oid, c.oid, 'TRUNCATE') THEN 1 ELSE 0 END AS "TRU", case WHEN has_table_privilege(r.oid, c.oid, 'REFERENCES') THEN 1 ELSE 0 END AS "REF", case WHEN has_table_privilege(r.oid, c.oid, 'TRIGGER') THEN 1 ELSE 0 END AS "TRI" FROM pg_roles r, pg_class c, pg_namespace n WHERE c.relnamespace = n.oid AND r.rolname = 'rds_pgaudit' /* オブジェクト監査実行ロールを指定 */ AND n.nspname NOT IN ('pg_toast','pg_catalog','information_schema') /* 監査対象外のスキーマ名を指定 */ AND (has_table_privilege(r.oid, c.oid, 'SELECT') or has_table_privilege(r.oid, c.oid, 'UPDATE') or has_table_privilege(r.oid, c.oid, 'DELETE') or has_table_privilege(r.oid, c.oid, 'TRUNCATE') or has_table_privilege(r.oid, c.oid, 'REFERENCES') or has_table_privilege(r.oid, c.oid, 'TRIGGER') ) ORDER BY "RoleName","Schema","Table","Type" ;

    試しに実行してみます。

    [emily@ip-10-112-1-139 login]$ psql -h koizumi-postgresql-instance.eu-west-1.rds.amazonaws.com -U postgres -p 5432 -d testdb01 -f rds_pgaudit_privilege.sql RoleName | Schema | Table | Type | ALL | SEL | INS | UPD | DEL | TRU | REF | TRI -------------+--------+----------+-------+-----+-----+-----+-----+-----+-----+-----+----- rds_pgaudit | public | customer | Table | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 rds_pgaudit | public | family | Table | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 (2 rows)

    まとめ

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

    Aurora PostgreSQL のセッション監査とオブジェクト監査の使い方を詳しく見てきました。

    セッション監査でDDLを監視して、オブジェクト監査で対象のテーブルを監視するなど、きめ細やかな監査設定が行えます。

    どのような監査要件にも耐えられそうですので、便利かなと思います。

    オススメの監査設定

    やはり、オススメの監査設定はこちらですね。テーブル単位で監視するとなると、テーブルが増える度に設定変更が必要になり運用が大変です。

    監査対象のデータベース testdb01 に対しては全操作を監査し、alter role, alter database を監査するために クラスターレベルで role, ddl を設定します。

    設定レベル 設定内容
    クラスターレベル pgaudit.log = role,ddl
    インスタンスレベル 設定なし
    データベースレベル alter database testdb01 set pgaudit.log='ALL';
    ロールレベル 設定なし

    ただ、ミッションクリティカルなシステムではパフォーマンスの観点から、上記の設定ではなく、最小限の監査設定に抑える必要があります。

    いつか時間のあるときに監査機能の On/Off でベンチマークを取ってみようと考えています。

  • Audit
  • PostgreSQL
  • RDS
  • SQL
    •