使用150行SQL建立PostgreSQL通用審計解決方案 - supabase

banq發表於2022-03-28
SQL

資料審計是一個跟蹤表內容隨時間變化的系統。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檢視。
 

相關文章