PostgreSQL を実行している Amazon RDS DB インスタンスを pgaudit 拡張機能を使用して監査するにはどうすればよいですか?

解決策

PostgreSQL DB インスタンスのアクティビティをログに記録するために設定できるさまざまなパラメータがあります。さまざまなデータベース、ロール、テーブル、または列を監査するには、pgaudit エクステンションを使用できます。pgaudit エクステンションを有効にすると、特定のデータベース、ロール、テーブル、および列を監査するように pgaudit.log パラメータを設定できます。

PostgreSQL を実行している Amazon RDS インスタンスで pgaudit 拡張機能を有効にします

1.    次のコマンドを実行して、rds_pgaudit という専用のデータベースロールを作成します。

CREATE ROLE rds_pgaudit;
CREATE ROLE

2.    DB インスタンスに関連付けられているカスタム DB パラメータグループで、以下のパラメータを変更します。
pgaudit を shared_preload_libraries に追加する
pgaudit.role を rds_pgaudit (手順 1 で作成したロール) に設定する

3.    インスタンスを再起動して、パラメータグループに対する変更がインスタンスに適用されるようにします。

4.    次のコマンドを実行して、pgaudit が初期化されていることを確認します。

show shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)

5.    次のコマンドを実行して、pgaudit エクステンションを作成します。

CREATE EXTENSION pgaudit;
CREATE EXTENSION

6.    次のコマンドを実行して、pgaudit.role が rds_pgaudit に設定されていることを確認します。

show pgaudit.role;
pgaudit.role
------------------
rds_pgaudit

7.    pgaudit.log パラメータを設定して、次のいずれかを監査します。

• ALL は、次のコマンドを監査します。
• MISC は、DISCARD、FETCH、CHECKPOINT、VACUUM、SET など、さまざまなコマンドを監査します。
• DDL は、ROLE クラスに含まれていないすべてのデータ記述言語 (DDL) を監査します。
• ROLE は、GRANT、REVOKE、CREATE/ALTER/DROP ROLE など、ロールと特権に関連するステートメントを監査します。
• FUNCTION は、関数呼び出しと DO ブロックを監査します。
• WRITE は、宛先がリレーションである場合に、INSERT、UPDATE、DELETE、TRUNCATE、COPY を監査します。
• READ は、ソースがリレーションまたはクエリである場合に、SELECT と COPY を監査します。

監査対象に応じて、データベース、ロール、またはテーブルの pgaudit.log パラメータの値を設定します。

pgaudit エクステンションを使用してデータベースを監査する

1.    データベース、ロール、またはテーブルの pgaudit.log パラメータの値を設定するには、パラメータグループレベルで pgaudit.log パラメータを none に設定します。

> show pgaudit.log;
+---------------+
| pgaudit.log   |
|---------------|
| none          |
+---------------+
SHOW

2.次のコマンドを実行して、このデータベースでのみこのパラメータのシステム設定を上書きします。

ALTER DATABASE test_database set pgaudit.log='All';

これにより、pgaudit.log パラメータの値が All に変更され、RDS DB インスタンスで監査されるデータベースは test_database だけになります。

3.    test_database に接続して、次のクエリを実行します。

select * from test_table;

エラーログの出力は次のようになります。

2019-06-25 19:21:35 UTC:192.0.2.7(39330):testpar@test_database:[21638]:LOG: AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not logged>

pgaudit エクステンションを使用して****ロールを監査する

データベースレベルで pgaudit.log パラメータを設定する場合と同様に、ロールは pgaudit.log パラメータに対して異なる値を持つように変更されます。次のコマンド例では、ロール test1 と test2 が異なる pgaudit.log 設定になるように変更されます。

1.    次のコマンドを実行して、test1 と test2 の pgaudit.log パラメータに異なる値を設定します。

ALTER ROLE test1 set pgaudit.log='All';

ALTER ROLE test2 set pgaudit.log='DDL';

2.    次のクエリを実行して、変更がロールレベルで行われていることを確認します。

> select rolname,rolconfig from pg_roles where rolname in ('test1','
                  test2');
+-----------+----------------------+
| rolname   | rolconfig            |
|-----------+----------------------|
| test1     | [u'pgaudit.log=All'] |
| test2     | [u'pgaudit.log=DDL'] |
+-----------+----------------------+
SELECT 2
Time: 0.010s

3.    test1 と test2 の両方に対して、次のクエリを実行します。

CREATE TABLE test_table (id int);
CREATE TABLE

select * from test_table;
id
----
(0 rows)

test1 のログ出力は次のようになります。

...
2019-06-26 14:51:12 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>

2019-06-26 14:51:18 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not
logged>
...

test2 に対して同じクエリを実行すると、ログ出力は次のようになります。

...
2019-06-26 14:53:54 UTC:192.0.2.7(44772):test2@postgres:[5517]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>
...

注: test2 の pgaudit.log パラメータは DDL のみに設定されているため、SELECT クエリの監査エントリはありません。

pgaudit エクステンションを使用してテーブルを監査する

pgaudit.log パラメータを設定すると、特定のリレーションに影響するステートメントの監査とログ記録が行われます。pgaudit エクステンションによってログに記録できるのは、SELECT、INSERT、UPDATE、および DELETE コマンドのみです。TRUNCATE はオブジェクト監査ログに含まれません。rds_pgaudit ロールに、監査対象のテーブルに対する操作 (SELECT、DELETE、INSERT、UPDATE など) へのアクセス権を付与すると、権限付与監査では対応するステートメントがログに記録されます。次の例では、rds_pgaudit ロールに SELECT および DELETE へのアクセス権を付与しています。これにより、test_table に対するすべての SELECT ステートメントと DELETE ステートメントが監査されるようになります。

1.    次のコマンドを実行して、rds_pgaudit ロールに SELECT と DELETE へのアクセス権を付与します。

grant select, delete on test_table to rds_pgaudit;

2.    test_table に対して DELETE ステートメントを実行して、監査ログが正しく設定されていることをテストします。

Time: 0.008s
DELETE 1
>delete from test_table where pid=5050;

DELETE ステートメントの出力は、次のようになります。

2019-06-25 17:13:02
UTC:192.0.2.7(41810):postgresql104saz@postgresql104saz:[24976]:LOG:
AUDIT: OBJECT,3,1,WRITE,DELETE,TABLE,public.t1,delete from test_table where
pid=5050,<not logged>

pgaudit エクステンションを使用して列を監査する

特定のテーブルに対して列レベルで監査を設定することもできます。たとえば、機密データが 1 つの列にのみ存在する場合などです。次のコマンド例では、payroll テーブルが作成され、そのテーブルには監査が必要な給与データが入った機密列が含まれています。

create table payroll
(    
    name text,
    salary text
);

1.    rds_pgaudit ロールに、salary 列に対する SELECT へのアクセス権を付与します。これにより、この列に対するすべての SELECT が監査されるようになります。

grant select (salary)  on payroll to rds_pgaudit;

2.    salary 列を含む、テーブル内のすべての列に対して SELECT を実行します。

select * from payroll;

次の出力例では、salary 列を含むすべての SELECT が監査されます。ただし、salary 列を含まない SELECT は監査されません。

2019-06-25
 18:25:02
UTC:192.0.2.7(42056):postgresql104saz@postgresql104saz:[4118]:LOG:
AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.payroll,select * from
payroll,<not logged>

---

関連情報

PostgreSQL の一般的な DBA タスク