建站
咨詢
售后
建站咨詢
新聞中心
數(shù)據(jù)審計是一個跟蹤表內容隨時間變化的系統(tǒng)，在現(xiàn)在安全合規(guī)方面數(shù)據(jù)審計是必須要的功能之一。PostgreSQL作為一個強大現(xiàn)代的開源關系數(shù)據(jù)庫，也有一個相關插件PGAudit可以提供審計功能。
關于PGAudit插件以后有機會可以詳細介紹，本文我們介紹一個簡單SQL語句實現(xiàn)的數(shù)據(jù)集審計功能。
概述
最終實現(xiàn)效果為：
創(chuàng)建一個示例表：
create extension supa_audit cascade;
create table public.account(
id int primary key,
name text not null
);
啟用審計：
select audit.enable_tracking('public.account'::regclass);增改刪操作：
insert into public.account(id, name)
values (1, 'Chongchong');
update public.account set name = 'CC' where id = 1;
delete from public.account where id = 1;
清空表：
truncate table public.account;
查看審計日志：
select * from audit.record_history
請注意，record_id和old_record_id在更新行時保持不變，這樣就可以輕松查詢單行的歷史記錄。
要關閉審計追蹤，只需執(zhí)行：
select audit.disable_tracking('public.account'::regclass);
實現(xiàn)
首先創(chuàng)建一個名為audit schema為審計用：
create schema if not exists audit;
記錄存儲
接下來，需要一個表來跟蹤插入、更新和刪除。
傳統(tǒng)上，使用audit schema并附加了一些元數(shù)據(jù)列，如提交的時間戳。
該解決方案存在一些維護挑戰(zhàn)：
- 對表啟用審計需要數(shù)據(jù)庫遷移
- 當源表的模式改變時，審計表的模式也必須改變
為此使用PostgreSQL的無模式JSONB數(shù)據(jù)類型將每條記錄的數(shù)據(jù)存儲在單個列中的。這種方法的另一個好處是允許將多個表的審計歷史存儲在一個審計表中。
create table audit.record_version(
id bigserial primary key,
record_id uuid,
old_record_id uuid,
op varchar(8) not null,
ts timestamptz not null default now(),
table_oid oid not null,
table_schema name not null,
table_name name not null,
record jsonb,
old_record jsonb
);
查詢和索引
查詢性能很重要，如果不能快速查詢日志，則該審計日志沒有多大實際意義。為了提高查詢的性能，需要對最常用的查詢涉及字段創(chuàng)建索引。
時間范圍內查詢
對于時間范圍，需要一個索引ts。 由于審計表僅用于插入記錄，其中ts列插入操作時間，其值ts自然是升序排列。PostgreSQL的內置BRIN索引可以利用值和物理位置之間的相關性來生成一個索引，該索引在規(guī)模上比默認值(BTREE索引)小數(shù)百倍，并且查找時間更快。
create index record_version_ts
on audit.record_version
using brin(ts);
對于表查詢，包含了一個 table_oid跟蹤PostgreSQL內部數(shù)字表標識符的列?？梢詾樵摿刑砑铀饕皇莟able_schema和 able_name列，最小化索引大小并提供更好的性能。
create index record_version_table_oid
on audit.record_version
using btree(table_oid);
記錄唯一標識
將每一行的數(shù)據(jù)存儲為的缺點之一jsonb是基于列值的過濾變得非常低效。如果想快速查找一行的歷史記錄，需要為每一行提取和索引一個唯一標識符。
對于全局唯一標識符，使用以下結構：
[table_oid, primary_key_value_1, primary_key_value_2, ...]
并將該數(shù)組散列為UUID v5以獲得有效的可索引UUID類型，以識別對數(shù)據(jù)更改具有魯棒性的行。
使用一個實用函數(shù)來查找記錄的主鍵列名：
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包含這些用于快速查詢的唯一標識符的列。
create index record_version_record_id
on audit.record_version(record_id)
where record_id is not null;
create index record_version_old_record_id
on audit.record_version(record_id)
where old_record_id is not null;
觸發(fā)器
為了讓審計功能真正起作用，需要在最終用戶不對其事務進行任何更改的情況下插入記錄給審計表。為此，設置一個觸發(fā)器在數(shù)據(jù)更改時觸發(fā)，為每個插入/更新/刪除的行為觸發(fā)一次觸發(fā)器。
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：
select audit.enable_tracking('. 