📁 🦁 🚣🏽 PostgreSQLデータ変更監査 🧛🏿 🧛 🗼

既存のシステムのデータ変更を監査する必要がありました。

要件：

接続の容易さ/個々のテーブルのロギングを無効にします。
既存のデータベース機能への変更を最小限にします。
パフォーマンスの低下を最小限に抑えます。

最初に考えたのは、_user、_create_date、_delete_dateフィールドをログに記録されたテーブルに追加することでした。

INSERT、UPDATE、DELETE操作では、これらのフィールドで機能するトリガーをハングさせます。

エントリを追加するときに、_userおよび_create_dateフィールドに入力します。

更新する代わりに、更新された行のコピーを（値を変更して）作成し、更新された行自体の_delete_dateフィールドに入力します。

エントリを削除する代わりに、_delete_dateフィールドに入力します。

このようなテーブルにアクセスする場合、_delete_date IS NULLをWHEREブロックに追加する必要があります。

このメカニズムは、最初にデータベースアーキテクチャに組み込まれていれば機能する可能性がありましたが、ロギングが導入されるまでに、3,000を超える関数を作成し、それぞれを変更する必要がありました。

次に、データとは別にログを保存するというアイデアが生まれました。アイデアは次のとおりです。

ログスキーマは、テーブル構造のコピーといくつかのサービスフィールドを作成します。

ログに記録された各テーブルでトリガーがハングし、変更されたデータを保存するすべてのダーティな作業が行われます。

トリガーコード

CREATE OR REPLACE FUNCTION logs.tf_log_table() RETURNS trigger AS $BODY$ DECLARE query text; safe_table_name text; BEGIN SELECT quote_ident(nspname||'.'||relname) FROM pg_class cl INNER JOIN pg_namespace nsp ON (cl.relnamespace=nsp.oid) WHERE cl.oid=TG_RELID INTO safe_table_name; query='INSERT INTO logs.'||safe_table_name||' SELECT ($1).*, now(),$2,session_user;'; IF (TG_OP = 'DELETE') THEN EXECUTE query using OLD,'D'; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN EXECUTE query using OLD,'U'; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN EXECUTE query using NEW,'I'; RETURN NEW; END IF; /*           ,   ,      */ EXCEPTION WHEN SQLSTATE '42P01' OR SQLSTATE '42801' OR SQLSTATE '42804' THEN PERFORM logs.create_log_tables(TG_RELID::regclass); IF (TG_OP = 'DELETE') THEN EXECUTE query using OLD,'D'; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN EXECUTE query using OLD,'U'; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN EXECUTE query using NEW,'I'; RETURN NEW; END IF; /*  - ,      */ WHEN OTHERS then IF (TG_OP = 'DELETE') THEN RETURN OLD; ELSE RETURN NEW; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

このトリガーには、plpgsqlに固有の構造がいくつかあります。それらをより詳細にペイントしてみます。

SELECT quote_ident（nspname || '。' || relname）

FROM pg_class cl INNER JOIN pg_namespace nsp ON（cl.relnamespace = nsp.oid）

WHERE cl.oid = TG_RELID INTO safe_table_name;

TG_RELIDは、トリガー関数がトリガーされたときにのみ存在する特別な変数であり、トリガーを呼び出したテーブルの識別子を格納します。

その助けを借りて、ログが書き込まれるテーブルの名前を生成します。

query = 'INSERT INTO logs。' || safe_table_name || ' SELECT（$ 1）。*、Now（）、$ 2、session_user; ';

動的SQLは、データ自体を挿入するために使用されます。

変数$ 1の代わりに、トリガーが動作した行からのデータが置換されます（行全体がそこに挿入され、個別のフィールドに展開する必要があります-これは（ROW）構成によって行われます。

now（）は、トランザクションの開始時間を返す関数です。

session_user-現在のセッションのユーザー名

IF（TG_OP = 'DELETE'）

その後

EXECUTEクエリUSING OLD、 'D';

TG_OP-トリガー関数にのみ存在する別の変数。トリガーが起動された操作の名前（INSERT、UPDATE、DELETE、またはTRUNCATE）を格納します

OLD、NEW-文字列の古いバージョンと新しいバージョンがこれらの変数に保存されます。

さらに、何かがうまくいかない場合には、かなり単純なエラー処理が提供されます：

例外

SQLSTATE '42P01'またはSQLSTATE '42801'またはSQLSTATE '42804'の場合

PERFORM logs.create_log_tables（TG_RELID :: regclass）;

テーブルの構造が変更された場合、または何らかの理由でログを含むテーブルが作成されていない場合は、テーブルが再作成され、ログの書き込みが試行されます。

他のすべての場合、エラーが発生すると、ログ記録手順は無視されます。

ロギングを接続する機能コード

 CREATE OR REPLACE FUNCTION logs.create_log_tables(table_oid oid) RETURNS int AS $BODY$ DECLARE log_namespace oid=(SELECT oid from pg_namespace WHERE nspname='logs'); p_relname text; new_tbl_name text; safe_table_name text; BEGIN SELECT relname FROM pg_class WHERE oid=table_oid INTO p_relname; SELECT quote_ident(nspname||'.'||relname) FROM pg_class cl inner join pg_namespace nsp ON (cl.relnamespace=nsp.oid) where cl.oid=table_oid INTO safe_table_name; /*    */ SELECT safe_table_name||'_'||(now()::date)::text||'('||i||')' FROM generate_series(1,10) a(i) WHERE safe_table_name||'_'||(now()::date)::text||'('||i||')' not in(select relname from pg_class where relnamespace=log_namespace and relpersistence='p') ORDER BY i LIMIT 1 INTO new_tbl_name; /*    */ EXECUTE 'ALTER TABLE IF EXISTS logs.'||safe_table_name|| ' RENAME TO '||quote_ident(new_tbl_name)||';'; /*     ,  ,   */ EXECUTE 'create table logs.'||safe_table_name||' (like '||table_oid::regclass||');'; EXECUTE 'ALTER TABLE logs.'||safe_table_name||' ADD COLUMN "'||p_relname||'_timestamp" timestamp with time zone;'; EXECUTE 'ALTER TABLE logs.'||safe_table_name||' ADD COLUMN "'||p_relname||'_operation" char;'; EXECUTE 'ALTER TABLE logs.'||safe_table_name||' ADD COLUMN "'||p_relname||'_user" text;'; /* */ EXECUTE ' DROP TRIGGER IF exists tr_log_table ON '||table_oid::regclass::text||'; CREATE TRIGGER tr_log_table BEFORE UPDATE OR DELETE OR INSERT ON '||table_oid::regclass::text||' FOR EACH ROW EXECUTE PROCEDURE logs.tf_log_table();'; RETURN 0; end; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

ロギング接続アルゴリズムは非常に簡単です。まず、ログに記録されたテーブルの名前に基づいてクローンが作成され（同じ名前のテーブルが既に存在する場合、古いテーブルの名前が変更されます）、必要なサービスフィールドがこのクローンに追加され、トリガーがログに記録されたテーブルに接続されます。

このオプションの利点：

既存の機能を変更する必要はありません。
サンプルリクエストのパフォーマンスは低下しません。
ログに記録されたテーブルの構造が変更されると、ログのあるテーブルが自動的に再作成されます。
古いテーブルを削除することにより、ログをすばやく消去できます。

PostgreSQLデータ変更監査

要件：

このオプションの利点：

More articles: