不少公司會要求相當程度重要性的軟體系統要在資料庫設定活動稽核紀錄（Audit Logging）以備意外發生時保留可追蹤的依據。不過稽核日誌通常會紀錄「一大堆」的活動，有時候會讓人困擾要怎麼收納。
不過在資料庫裡面，有時候不見得要全部活動都紀錄，而只要針對重要資料的活動作紀錄就可以了。
在 Oracle 內有 table-level auditing（上網查好像叫做 DBA_AUDIT_TRAIL package 還是 Fine-Grained Auditing 的樣子。。？）可以針對指定表格的存取作稽核紀錄。不過 EDB/PGSQL 過往都不太便於設置指定表格作稽核紀錄的設置。
不過在 EDB 14 開始，也支援類比功能～不過實作機制沒有模仿 Oracle，但其實設定上還蠻簡單的。
這邊就做一點紀錄。
新功能設置上很容易：
1) 先設定 audit log 群組策略，設定上有兩個符號搭配群組標籤作控制
符號 @ 是指這群組要作這類稽核紀錄，正向表列
符號 - 是指這群組不要作這類稽核紀錄，負向表列
另外，這邊的群組標籤跟 DB 內的 role 無關
ALTER SYSTEM SET edb_audit_statement = 'all@群組1, select-群組2';
2) 在 DB 內用以下指令來掛紀錄群組
ALTER TABLE 表格 SET edb_audit_group = '群組';
3) 這樣表格稽核功能就生效了～
不過原則上建議不要太多瑣碎規則，畢竟這些功能總是會分掉一點計算資源。
以下就開始實際演練
啟用 EDB audit log 功能與指定適當的 audit log 放置目錄（預設放到 $PGDATA 內，盡量避免）。以下順便驗明正身
edb=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.4 (EnterpriseDB Advanced Server 14.4.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
(1 row)
edb=#
edb=# ALTER SYSTEM SET edb_audit = 'csv' ;
edb=# ALTER SYSTEM SET edb_audit_directory = '../edb_audit' ;
[root@edb14 ~]# service edb-as-14 restart
再來設定三種稽核策略：
- 全部 DDL 與錯誤 SQL 紀錄
- 指定表格僅紀錄 SELECT 的群組 tbselect
- 指定表格紀錄 SELECT 與 DML（INSERT/UPDATE/DELETE）的群組 tbcrud。
edb=# ALTER SYSTEM SET edb_audit_statement = 'ddl, error, select@tbcrud@tbselect, insert@tbcrud-tbselect, update@tbcrud-tbselect, delete@tbcrud-tbselect'; edb=# SELECT pg_reload_conf();
接著分別建三個表格作對比測試，其中一個是控制組：
CREATE TABLE public.rec_select (id INT);
ALTER TABLE public.rec_select SET (edb_audit_group = 'tbselect');
CREATE TABLE public.rec_crud (id INT);
ALTER TABLE public.rec_crud SET (edb_audit_group = 'tbcrud');
CREATE TABLE public.rec_controlgroup (id INT);
這些設定可以在系統表裡面觀察
edb=# SELECT relname,reloptions FROM pg_class WHERE relname like 'rec_%';
relname | reloptions
------------------+----------------------------
rec_controlgroup | (NULL)
rec_crud | {edb_audit_group=tbcrud}
rec_select | {edb_audit_group=tbselect}
(3 rows)
Time: 3.655 ms
edb=#
此時先查看 audit log：裡面紀錄了上面的 DDL
[enterprisedb@edb14 ~]$ cat ~/as14/edb_audit/audit-20220221_141406.csv 2022-02-21 14:14:07.172 UTC,,,31243,,62139e2f.7a0b,1,,2022-02-21 14:14:07 UTC,4/6,0,AUDIT,00000,"statement: (null)",,,,,"SQL statement ""DELETE FROM sys.callback_queue_table qt WHERE qt.msgid IN (SELECT qt2.msgid FROM sys.callback_queue_table qt2 WHERE qt2.state = 2 AND qt2.q_name = 'callback_queue' AND qt2.next_event_time <= clock_timestamp() FOR UPDATE SKIP LOCKED LIMIT $1) """,,,,"","dbms_aq worker[edb]",,0,"DELETE","","delete" 2022-02-21 14:14:09.175 UTC,,,31245,,62139e31.7a0d,1,,2022-02-21 14:14:09 UTC,4/15,0,AUDIT,00000,"statement: (null)",,,,,"SQL statement ""DELETE FROM sys.callback_queue_table qt WHERE qt.msgid IN (SELECT qt2.msgid FROM sys.callback_queue_table qt2 WHERE qt2.state = 2 AND qt2.q_name = 'callback_queue' AND qt2.next_event_time <= clock_timestamp() FOR UPDATE SKIP LOCKED LIMIT $1) """,,,,"","dbms_aq worker[postgres]",,0,"DELETE","","delete" 2022-02-21 14:15:02.367 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,1,"idle",2022-02-21 14:14:53 UTC,4/23,0,AUDIT,00000,"statement: ALTER SYSTEM SET edb_audit_statement = 'ddl, error, select@tbcrud@tbselect, insert@tbcrud-tbselect, update@tbcrud-tbselect, delete@tbcrud-tbselect';",,,,,,,,,"psql","client backend",,0,"ALTER SYSTEM","","alter" 2022-02-21 14:15:09.580 UTC,,,31232,,62139e2d.7a00,1,,2022-02-21 14:14:05 UTC,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,"","postmaster",,0,"","","general" 2022-02-21 14:15:17.738 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,2,"idle",2022-02-21 14:14:53 UTC,4/25,0,AUDIT,00000,"statement: CREATE TABLE public.rec_select (id INT);",,,,,,,,,"psql","client backend",,0,"CREATE TABLE","","create" 2022-02-21 14:15:23.766 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,3,"idle",2022-02-21 14:14:53 UTC,4/26,0,AUDIT,00000,"statement: ALTER TABLE public.rec_select SET (edb_audit_group = 'tbselect');",,,,,,,,,"psql","client backend",,0,"ALTER TABLE","","alter" 2022-02-21 14:15:28.598 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,4,"idle",2022-02-21 14:14:53 UTC,4/27,0,AUDIT,00000,"statement: CREATE TABLE public.rec_crud (id INT);",,,,,,,,,"psql","client backend",,0,"CREATE TABLE","","create" 2022-02-21 14:15:33.713 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,5,"idle",2022-02-21 14:14:53 UTC,4/28,0,AUDIT,00000,"statement: ALTER TABLE public.rec_crud SET (edb_audit_group = 'tbcrud');",,,,,,,,,"psql","client backend",,0,"ALTER TABLE","","alter" 2022-02-21 14:15:38.885 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,6,"idle",2022-02-21 14:14:53 UTC,4/29,0,AUDIT,00000,"statement: CREATE TABLE public.rec_controlgroup (id INT);",,,,,,,,,"psql","client backend",,0,"CREATE TABLE","","create" [enterprisedb@edb14 ~]$
以下開始測試
(a) 塞資料到 public.rec_select：照理說只紀錄查詢：中間的 INSERT/UPDATE/DELETE 事件都不會紀錄
edb=# select * from rec_select; id ---- (0 rows) edb=# insert into rec_select values (1); INSERT 0 1 edb=# select * from rec_select; id ---- 1 (1 row) edb=# update rec_select set id = 2 where id = 1; UPDATE 1 edb=# select * from rec_select; id ---- 2 (1 row) edb=# delete from rec_select where id = 2; DELETE 1 edb=#
[enterprisedb@edb14 ~]$ tail -6 ~/as14/edb_audit/audit-20220221_141406.csv 2022-02-21 14:15:28.598 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,4,"idle",2022-02-21 14:14:53 UTC,4/27,0,AUDIT,00000,"statement: CREATE TABLE public.rec_crud (id INT);",,,,,,,,,"psql","client backend",,0,"CREATE TABLE","","create" 2022-02-21 14:15:33.713 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,5,"idle",2022-02-21 14:14:53 UTC,4/28,0,AUDIT,00000,"statement: ALTER TABLE public.rec_crud SET (edb_audit_group = 'tbcrud');",,,,,,,,,"psql","client backend",,0,"ALTER TABLE","","alter" 2022-02-21 14:15:38.885 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,6,"idle",2022-02-21 14:14:53 UTC,4/29,0,AUDIT,00000,"statement: CREATE TABLE public.rec_controlgroup (id INT);",,,,,,,,,"psql","client backend",,0,"CREATE TABLE","","create" 2022-02-21 14:20:46.279 UTC,"enterprisedb","edb",31313,"[local]",62139fb6.7a51,1,"SELECT",2022-02-21 14:20:38 UTC,4/50,0,AUDIT,00000,"statement: select * from rec_select;",,,,,,,,,"psql","client backend",,0,"SELECT","","select" 2022-02-21 14:20:56.455 UTC,"enterprisedb","edb",31313,"[local]",62139fb6.7a51,2,"SELECT",2022-02-21 14:20:38 UTC,4/52,0,AUDIT,00000,"statement: select * from rec_select;",,,,,,,,,"psql","client backend",,0,"SELECT","","select" 2022-02-21 14:21:11.632 UTC,"enterprisedb","edb",31313,"[local]",62139fb6.7a51,3,"SELECT",2022-02-21 14:20:38 UTC,4/54,0,AUDIT,00000,"statement: select * from rec_select;",,,,,,,,,"psql","client backend",,0,"SELECT","","select" [enterprisedb@edb14 ~]$
(b) 塞資料到 public.rec_crud：全部都會紀錄
edb=# select * from rec_crud; id ---- (0 rows) edb=# insert into rec_crud values (1); INSERT 0 1 edb=# select * from rec_crud; id ---- 1 (1 row) edb=# update rec_crud set id = 2 where id = 1; UPDATE 1 edb=# select * from rec_crud; id ---- 2 (1 row) edb=# delete from rec_crud where id = 2; DELETE 1 edb=#
[enterprisedb@edb14 ~]$ tail -6 ~/as14/edb_audit/audit-20220221_141406.csv 2022-02-21 14:25:07.663 UTC,"enterprisedb","edb",31325,"[local]",6213a0bb.7a5d,1,"SELECT",2022-02-21 14:24:59 UTC,4/73,0,AUDIT,00000,"statement: select * from rec_crud;",,,,,,,,,"psql","client backend",,0,"SELECT","","select" 2022-02-21 14:25:13.214 UTC,"enterprisedb","edb",31325,"[local]",6213a0bb.7a5d,2,"INSERT",2022-02-21 14:24:59 UTC,4/74,0,AUDIT,00000,"statement: insert into rec_crud values (1);",,,,,,,,,"psql","client backend",,0,"INSERT","","insert" 2022-02-21 14:25:25.772 UTC,"enterprisedb","edb",31325,"[local]",6213a0bb.7a5d,3,"SELECT",2022-02-21 14:24:59 UTC,4/75,0,AUDIT,00000,"statement: select * from rec_crud;",,,,,,,,,"psql","client backend",,0,"SELECT","","select" 2022-02-21 14:25:31.529 UTC,"enterprisedb","edb",31325,"[local]",6213a0bb.7a5d,4,"UPDATE",2022-02-21 14:24:59 UTC,4/76,0,AUDIT,00000,"statement: update rec_crud set id = 2 where id = 1;",,,,,,,,,"psql","client backend",,0,"UPDATE","","update" 2022-02-21 14:25:37.793 UTC,"enterprisedb","edb",31325,"[local]",6213a0bb.7a5d,5,"SELECT",2022-02-21 14:24:59 UTC,4/77,0,AUDIT,00000,"statement: select * from rec_crud;",,,,,,,,,"psql","client backend",,0,"SELECT","","select" 2022-02-21 14:25:46.343 UTC,"enterprisedb","edb",31325,"[local]",6213a0bb.7a5d,6,"DELETE",2022-02-21 14:24:59 UTC,4/78,0,AUDIT,00000,"statement: delete from rec_crud where id = 2;",,,,,,,,,"psql","client backend",,0,"DELETE","","delete" [enterprisedb@edb14 ~]$
(c) 對照組操作：相當於 INSERT/UPDATE/DELETE 事件的 @ 全下
edb=# select * from rec_controlgroup; id ---- (0 rows) edb=# insert into rec_controlgroup values (1); INSERT 0 1 edb=# select * from rec_controlgroup; id ---- 1 (1 row) edb=# update rec_controlgroup set id = 2 where id = 1; UPDATE 1 edb=# select * from rec_controlgroup; id ---- 2 (1 row) edb=# delete from rec_controlgroup where id = 2; DELETE 1 edb=#
[enterprisedb@edb14 ~]$ grep rec_controlgroup ~/as14/edb_audit/audit-20220221_141406.csv 2022-02-21 14:15:38.885 UTC,"enterprisedb","edb",31299,"[local]",62139e5d.7a43,6,"idle",2022-02-21 14:14:53 UTC,4/29,0,AUDIT,00000,"statement: CREATE TABLE public.rec_controlgroup (id INT);",,,,,,,,,"psql","client backend",,0,"CREATE TABLE","","create" 2022-02-21 14:29:42.990 UTC,"enterprisedb","edb",31338,"[local]",6213a1c4.7a6a,1,"INSERT",2022-02-21 14:29:24 UTC,4/96,0,AUDIT,00000,"statement: insert into rec_controlgroup values (1);",,,,,,,,,"psql","client backend",,0,"INSERT","","insert" 2022-02-21 14:30:03.277 UTC,"enterprisedb","edb",31338,"[local]",6213a1c4.7a6a,2,"UPDATE",2022-02-21 14:29:24 UTC,4/98,0,AUDIT,00000,"statement: update rec_controlgroup set id = 2 where id = 1;",,,,,,,,,"psql","client backend",,0,"UPDATE","","update" 2022-02-21 14:30:14.266 UTC,"enterprisedb","edb",31338,"[local]",6213a1c4.7a6a,3,"DELETE",2022-02-21 14:29:24 UTC,4/100,0,AUDIT,00000,"statement: delete from rec_controlgroup where id = 2;",,,,,,,,,"psql","client backend",,0,"DELETE","","delete" [enterprisedb@edb14 ~]$ [enterprisedb@edb14 ~]$ tail -6 ~/as14/edb_audit/audit-20220221_141406.csv 2022-02-21 14:25:31.529 UTC,"enterprisedb","edb",31325,"[local]",6213a0bb.7a5d,4,"UPDATE",2022-02-21 14:24:59 UTC,4/76,0,AUDIT,00000,"statement: update rec_crud set id = 2 where id = 1;",,,,,,,,,"psql","client backend",,0,"UPDATE","","update" 2022-02-21 14:25:37.793 UTC,"enterprisedb","edb",31325,"[local]",6213a0bb.7a5d,5,"SELECT",2022-02-21 14:24:59 UTC,4/77,0,AUDIT,00000,"statement: select * from rec_crud;",,,,,,,,,"psql","client backend",,0,"SELECT","","select" 2022-02-21 14:25:46.343 UTC,"enterprisedb","edb",31325,"[local]",6213a0bb.7a5d,6,"DELETE",2022-02-21 14:24:59 UTC,4/78,0,AUDIT,00000,"statement: delete from rec_crud where id = 2;",,,,,,,,,"psql","client backend",,0,"DELETE","","delete" 2022-02-21 14:29:42.990 UTC,"enterprisedb","edb",31338,"[local]",6213a1c4.7a6a,1,"INSERT",2022-02-21 14:29:24 UTC,4/96,0,AUDIT,00000,"statement: insert into rec_controlgroup values (1);",,,,,,,,,"psql","client backend",,0,"INSERT","","insert" 2022-02-21 14:30:03.277 UTC,"enterprisedb","edb",31338,"[local]",6213a1c4.7a6a,2,"UPDATE",2022-02-21 14:29:24 UTC,4/98,0,AUDIT,00000,"statement: update rec_controlgroup set id = 2 where id = 1;",,,,,,,,,"psql","client backend",,0,"UPDATE","","update" 2022-02-21 14:30:14.266 UTC,"enterprisedb","edb",31338,"[local]",6213a1c4.7a6a,3,"DELETE",2022-02-21 14:29:24 UTC,4/100,0,AUDIT,00000,"statement: delete from rec_controlgroup where id = 2;",,,,,,,,,"psql","client backend",,0,"DELETE","","delete" [enterprisedb@edb14 ~]$
(d) 查一查系統表：沒有紀錄下來
edb=# select * from pg_stat_activity limit 1;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
-------+---------+-------+------------+----------+---------+------------------+-------------+-----------------+-------------+----------------------------------+------------+-------------+--------------+-----------------+----------------+-------+-------------+--------------+----------+-------+---------------------
| | 31238 | | | | | | | | 21-FEB-22 14:14:06.154339 +00:00 | | | | Activity | AutoVacuumMain | | | | | | autovacuum launcher
(1 row)
edb=#
[enterprisedb@edb14 ~]$ grep pg_stat_activity ~/as14/edb_audit/audit-20220221_141406.csv [enterprisedb@edb14 ~]$ tail -1 ~/as14/edb_audit/audit-20220221_141406.csv 2022-02-21 14:30:14.266 UTC,"enterprisedb","edb",31338,"[local]",6213a1c4.7a6a,3,"DELETE",2022-02-21 14:29:24 UTC,4/100,0,AUDIT,00000,"statement: delete from rec_controlgroup where id = 2;",,,,,,,,,"psql","client backend",,0,"DELETE","","delete" [enterprisedb@edb14 ~]$
以上就是針對指定表格的稽核設置範例～
針對紀錄 DDL 的加碼補充：變更 DB 帳號的密碼的 ALTER USER 指令也是一種 DDL，而改密碼時必然得填密碼字串。
為了避免修改密碼寫到稽核日誌裡面，可以作以下黃底設定把密碼從 audit log 遮掉。
ALTER SYSTEM SET shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq, $libdir/edb_filter_log' ; ALTER SYSTEM SET edb_filter_log.redact_password_command = true;
參考資料：
沒有留言:
張貼留言