如何使用 pgaudit 擴充功能來稽核執行 PostgreSQL 的 Amazon RDS 資料庫執行個體？

解決方案

存在您可以設定來記錄 PostgreSQL 資料庫執行個體上活動的其他參數。若要稽核不同的資料庫、角色、表格或資料欄，您可以使用 pgaudit 擴充功能。啟動 pgaudit 擴充功能之後，您可以設定 pgaudit.log 參數以稽核特定的資料庫、角色、表格和資料欄。

在執行 PostgreSQL 的 Amazon RDS 執行個體上啟動 pgaudit 擴充功能

1.    執行下列命令，建立名為 rds_pgaudit 的特定資料庫角色：

CREATE ROLE rds_pgaudit;
CREATE ROLE

2.    在與資料庫執行個體相關聯的自訂資料庫參數群組中修改以下參數：
新增或附加 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，因此 test_database 是唯一在 RDS 資料庫執行個體中稽核的資料庫。

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 ** 不包含在物件稽核記錄中。如果您在想要稽核的表格上，將操作 (例如 SELECT、DELETE、INSERT 或 UPDATE) 的存取權授予 rds_pgaudit 角色，則任何授予稽核會記錄相應的陳述式。下列範例會授予 rds_pgaudit 角色存取 SELECT 和 DELETE，以便稽核 test_table 上的所有 SELECT 和 DELETE 陳述式。

1.    執行下列命令，將 SELECT 和 DELETE 的存取權授予 rds_pgaudit 角色：

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 擴充功能稽核資料欄

您也可以在資料欄層級設定特定表格的稽核。例如，敏感資料僅存在於某一個資料欄時。在下列範例命令中，即會建立薪資表格，並且該表格具有敏感資料欄，其中包含必須稽核的薪資資料：

create table payroll
(    
    name text,
    salary text
);

1.    將薪資資料欄上的 SELECT 存取權授予 rds_pgaudit 角色，以便稽核此資料欄上的任何 SELECT：

grant select (salary)  on payroll to rds_pgaudit;

2.    SELECT 表格中的所有資料欄，包括薪資資料欄：

select * from payroll;

在下列範例輸出中，即會稽核包含薪資資料欄的任何 SELECT。但是，不會稽核不包含薪資資料欄的 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 任務