使用150行SQL建立PostgreSQL通用審計解決方案 - supabase
資料審計是一個跟蹤表內容隨時間變化的系統。PostgreSQL 具有一組強大的功能,我們可以利用這些功能在 150 行 SQL 中建立通用審計解決方案。
審計對於歷史分析特別有用。
為了證明這一點,設想你有一個使用者表,跟蹤使用者何時線上。你可以新增一個狀態列,它可以有兩個值之一:線上和離線。
你將如何跟蹤一個使用者在整個月中線上的時間?
一個審計系統會用時間戳來跟蹤每一個變化,因此你可以測量每個時間戳之間的差異,並將它們彙總到整個月。
我們的審計解決方案的目標是。
- 低維護
- 易於使用
- 快速查詢
為了證明我們正在努力實現的目標,下面的例子顯示了我們在博文結束時的情況。
-- create a table create table public.members( id int primary key, name text not null ); -- Enable auditing on the new table select audit.enable_tracking('public.members'); |
製作一些記錄來審計:
-- create a new record insert into public.members(id, name) values (1, 'foo'); -- edit the record update public.members set name = 'bar' where id = 1; -- delete the record delete from public.members; |
審查審計日誌:
select * from audit.record_history |
id | record_id | old_record_id | op | ts | table_oid | table_schema | table_name | record | old_record ----+--------------------------------------+--------------------------------------+--------+-------------------------------------+-----------+--------------+------------+--------------------------+-------------------------- 2 | 1ecd5ff0-1b6b-5bc2-ad80-1cb19769c081 | | INSERT | Mon Feb 28 18:13:52.698511 2022 PST | 16452 | public | members | {"id": 1, "name": "foo"} | 3 | 1ecd5ff0-1b6b-5bc2-ad80-1cb19769c081 | 1ecd5ff0-1b6b-5bc2-ad80-1cb19769c081 | UPDATE | Mon Feb 28 18:13:52.698511 2022 PST | 16452 | public | members | {"id": 1, "name": "bar"} | {"id": 1, "name": "foo"} 4 | | 1ecd5ff0-1b6b-5bc2-ad80-1cb19769c081 | DELETE | Mon Feb 28 18:13:52.698511 2022 PST | 16452 | public | members | | {"id": 1, "name": "bar"} (3 rows) |
注意我們的record_id和old_record_id在我們更新行的時候保持不變,所以我們可以很容易地查詢到單行的歷史記錄。
以下得到上述結果的具體步驟:
- 名稱空間
首先,我們將建立一個單獨的模式audit來命名我們的審計實體。
create schema if not exists audit;
- 資料表
接下來,我們需要一個表來跟蹤插入、更新和刪除。
傳統上,審計表的模式反映了被審計的表,並附加了一些後設資料列,如提交的時間戳。該解決方案存在一些維護挑戰:
- 對錶啟用審計需要資料庫遷移
- 當源表的模式改變時,審計表的模式也必須改變
因此,我們將依靠 PostgreSQL 的無模式JSONB資料型別將每條記錄的資料儲存在單個列中。這種方法的另一個好處是允許我們將多個表的審計歷史儲存在一個審計表中。
create table audit.record_version( id bigserial primary key, -- auditing metadata record_id uuid, -- identifies a new record by it's table + primary key old_record_id uuid, -- ^ op varchar(8) not null, -- INSERT/UPDATE/DELETE/TRUNCATE ts timestamptz not null default now(), -- table identifiers table_oid oid not null, -- pg internal id for a table table_schema name not null, -- audited table's schema name e.g. 'public' table_name name not null, -- audited table's table name e.g. 'account' -- record data record jsonb, -- contents of the new record old_record jsonb -- previous record contents (for UPDATE/DELETE) ); |
- 查詢模式
如果查詢太慢,審計日誌對我們沒有多大幫助!我們認為有 2 種查詢模式是審計系統的賭注:
時間範圍內對錶的更改:
對於時間片,我們需要ts列上的索引。由於該表是僅追加的並且該ts列由插入日期填充,因此我們的值ts自然是按升序排列的。
PostgreSQL 的內建BRIN 索引可以利用值和物理位置之間的相關性來生成一個索引,該索引在規模上比預設值(BTREE 索引)小數百倍,並且查詢時間更快。
-- index ts for time range filtering create index record_version_ts on audit.record_version using brin(ts); |
對於表過濾,我們包含了一個table_oid跟蹤 PostgreSQL 內部數字表識別符號的列。我們可以向該列新增索引而不是table_schemaandtable_name列,從而最小化索引大小並提供更好的效能。
-- index table_oid for table filtering create index record_version_table_oid on audit.record_version using btree(table_oid); |
隨著時間的推移對記錄的更改:
儲存每一行資料的缺點之一jsonb是基於列值的過濾變得非常低效。如果我們想快速查詢一行的歷史記錄,我們需要為每一行提取和索引一個唯一識別符號。
對於全域性唯一識別符號,我們將使用以下結構:
[table_oid, primary_key_value_1, primary_key_value_2, ...]
並將該陣列雜湊為 UUID v5 以獲得有效的可索引 UUID 型別,以識別對資料更改具有魯棒性的行。
我們將使用一個實用函式來查詢記錄的主鍵列名:
create or replace function audit.primary_key_columns(entity_oid oid) returns text[] stable security definer language sql as $$ -- Looks up the names of a table's primary key columns select coalesce( array_agg(pa.attname::text order by pa.attnum), array[]::text[] ) column_names from pg_index pi join pg_attribute pa on pi.indrelid = pa.attrelid and pa.attnum = any(pi.indkey) where indrelid = $1 and indisprimary $$; |
另一個使用table_oid和主鍵,將結果轉換為記錄的 UUID。
create or replace function audit.to_record_id( entity_oid oid, pkey_cols text[], rec jsonb ) returns uuid stable language sql as $$ select case when rec is null then null -- if no primary key exists, use a random uuid when pkey_cols = array[]::text[] then uuid_generate_v4() else ( select uuid_generate_v5( 'fd62bc3d-8d6e-43c2-919c-802ba3762271', ( jsonb_build_array(to_jsonb($1)) || jsonb_agg($3 ->> key_) )::text ) from unnest($2) x(key_) ) end $$; |
最後,我們對包含這些唯一識別符號的record_id和列進行索引,以便快速查詢。old_record_id
-- index record_id for fast searching create index record_version_record_id on audit.record_version(record_id) where record_id is not null; -- index old_record_id for fast searching create index record_version_old_record_id on audit.record_version(record_id) where old_record_id is not null; |
- 觸發器註冊
現在我們有一個我們的審計資料的家,我們相信它可以被有效地查詢。現在我們如何填充它?
我們需要在終端使用者不對其事務進行任何更改的情況下填充審計表。因此,我們將設定一個觸發器以在資料更改時觸發。在這種情況下,我們將為每個插入/更新/刪除的行觸發一次觸發器。
create or replace function audit.insert_update_delete_trigger() returns trigger security definer language plpgsql as $$ declare pkey_cols text[] = audit.primary_key_columns(TG_RELID); record_jsonb jsonb = to_jsonb(new); record_id uuid = audit.to_record_id(TG_RELID, pkey_cols, record_jsonb); old_record_jsonb jsonb = to_jsonb(old); old_record_id uuid = audit.to_record_id(TG_RELID, pkey_cols, old_record_jsonb); begin insert into audit.record_version( record_id, old_record_id, op, table_oid, table_schema, table_name, record, old_record ) select record_id, old_record_id, TG_OP, TG_RELID, TG_TABLE_SCHEMA, TG_TABLE_NAME, record_jsonb, old_record_jsonb; return coalesce(new, old); end; $$; |
- 公共 API
最後,我們將在一個乾淨的、冪等的、面向使用者的 API 後面結束觸發器的建立和刪除過程。
我們將公開的用於對錶啟用審計的 API 是
select audit.enable_tracking('<schema>.<table>'::regclass);
並禁用跟蹤
select audit.disable_tracking('<schema>.<table>'::regclass);
在幕後,這些函式根據請求的表註冊我們的審計觸發器。
create or replace function audit.enable_tracking(regclass) returns void volatile security definer language plpgsql as $$ declare statement_row text = format(' create trigger audit_i_u_d before insert or update or delete on %I for each row execute procedure audit.insert_update_delete_trigger();', $1 ); pkey_cols text[] = audit.primary_key_columns($1); begin if pkey_cols = array[]::text[] then raise exception 'Table % can not be audited because it has no primary key', $1; end if; if not exists(select 1 from pg_trigger where tgrelid = $1 and tgname = 'audit_i_u_d') then execute statement_row; end if; end; $$; create or replace function audit.disable_tracking(regclass) returns void volatile security definer language plpgsql as $$ declare statement_row text = format( 'drop trigger if exists audit_i_u_d on %I;', $1 ); begin execute statement_row; end; $$; |
效能
審計表總是會降低插入、更新和刪除的吞吐量。在吞吐量低於每秒 1000 次寫入的情況下,開銷通常可以忽略不計。對於寫入頻率較高的表,請考慮使用pgAudit之類的工具記錄 SQL 之外的更改。
總結
對於在 PostgreSQL 中進行審計的交鑰匙解決方案,我們已經將此指令碼打包到一個擴充套件中,並帶有一些額外的好處,比如TRUNCATE支援。在https://github.com/supabase/supa_audit檢視。
相關文章
- supabase/supabase: 開源Firebase 替代方案
- 基於Vue構造器建立Form元件的通用解決方案VueORM元件
- 遠端庭審系統解決方案
- Startalk(星語)——通用通訊解決方案
- SQL Server 審計(Audit)SQLServer
- 通用模板解決方案,提升影片生產效率
- Dalvik下一代殼通用解決方案
- Java程式碼審計篇 - ofcms系統審計思路講解 - 篇2 - SQL隱碼攻擊漏洞審計JavaSQL
- sql 語言 groupBy 分組統計時間段解決方案SQL
- SQL Server查詢慢的解決方案SQLServer
- 一套通用的企業級中後臺前端設計解決方案前端
- SOFAMesh中的多協議通用解決方案x-protocol介紹系列(1) : DNS通用定址方案協議ProtocolDNS
- SOFAMesh中的多協議通用解決方案x-protocol介紹系列(1):DNS通用定址方案協議ProtocolDNS
- 分散式事務概述及大廠通用解決方案分散式
- 【AUDIT]Oracle審計配置及常用sqlOracleSQL
- Cobar SQL審計的設計與實現SQL
- 最全 Kubernetes 審計日誌方案
- Lumen 實時記錄 SQL 執行解決方案SQL
- Oracle RAC遷移至南大通用GBase 8c 解決方案Oracle
- PostgreSQL建立只讀使用者SQL
- mpvue使用sass的解決方案Vue
- mpvue 使用sass的解決方案Vue
- 建立結算清單時提示衝突解決方案
- Citus 分散式 PostgreSQL 叢集 - SQL Reference(SQL支援和變通方案)分散式SQL
- BottomNavigationView的通用修改記錄(新解決方案)NavigationView
- 打造基於 PostgreSQL/openGauss 的分散式資料庫解決方案SQL分散式資料庫
- Xamarin.FormsShell基礎教程(2)建立Shell解決方案ORM
- 使用NVRAM的簡單解決方案VR
- 血壓計方案定製設計軟硬體解決方案
- java 遇到NoSuchMethodError通用解決思路JavaError
- 解決問題通用方法論
- Java審計之SQL隱碼攻擊篇JavaSQL
- C# 資料庫併發的解決方案(通用版、EF版)C#資料庫
- 前端的批量介面如何快速響應?有沒有通用解決方案?前端
- Gundam-cli:前端專案建立釋出流程解決方案前端
- Java程式碼審計篇 - ofcms系統審計思路講解 - 篇4 - XXE漏洞審計Java
- SpringBoot使用外部Web容器的解決方案Spring BootWeb
- 新一代的資料庫SQL審計服務-SQL洞察資料庫SQL