PostgreSQL Audit Yedekleme (Tablolardaki yapılan işlemlerin kayıt altına alınması)

Sırayı karışık yazdım. Önce trigger fonksiyonları tanımla daha sonra audit tablosu daha sonrada tablolardaki trigger çağırma kodlarını tanımlarsan doğru olacaktır.

Öncelikle audit yani log tablosunda hangi bilgileri tutacaksan buraya ekleyeceksin. Ben aşağıda yazdığım bilgileri*tuttuyorum.

• username (veri tabanı*bağlantısı kurulan kullanıcının adı),*
• ip (bağlantının sağlandığı*IP adresi),
• table_name (işlem yapılan tablonun adı),*
• row_id (tablodaki tanımlı*ID bilgisi*Tüm tablolarında tek bir isimde tanımlı olmak zorunda aksi halde hata alabilirsin),
• access_type (erişim tipi yani insert, update, delete burada belirtiliyor),
• time_of_change (işlemin yapıldığı tarih saat),
• old_val (update işleminde update yapılmadan önceki*değeri belirtir veya delete işleminde silinen bilgiyi gösterir),
• new_val (insert işleminde girilen kaydın bilgilerini verir, update işleminde girilen yeni değeri verir)

audit tablosu aşağıda
CREATE TABLE public.audit
(
*id serial,
*username character varying(16) NOT NULL,
*ip character varying(32) NOT NULL,
*table_name character varying(36) NOT NULL,
*row_id integer NOT NULL,
*access_type character varying(16) NOT NULL,
*time_of_change timestamp without time zone NOT NULL,
*old_val text,
*new_val text,
*CONSTRAINT audit_pkey PRIMARY KEY (id)
)WITH (OIDS=FALSE);
ALTER TABLE public.audit OWNER TO postgres;

--aşağıdaki kod ile de eğer audit tablosundan silme güncelleme işlemi yapılırsa onunda kaydının tutulması sağlanıyor. Yani loglara hiç bir şekilde müdahale edilmesin.
CREATE TRIGGER shield BEFORE UPDATE OR DELETE ON public.audit FOR EACH ROW EXECUTE PROCEDURE public.shield();

Audit trigger fonksiyonun
CREATE OR REPLACE FUNCTION public.audit()
*RETURNS trigger AS
$BODY$
DECLARE
old_data TEXT;
new_data TEXT;
BEGIN
IF (TG_OP = 'DELETE') THEN
old_data := ROW(OLD.*);
INSERT INTO audit (username, ip, table_name, row_id, access_type, time_of_change, old_val) VALUES (upper(session_user), inet_client_addr(), TG_TABLE_NAME, OLD.id, TG_OP, now(), old_data);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
old_data := ROW(OLD.*);
new_data := ROW(NEW.*);
IF old_data new_data THEN
INSERT INTO audit (username, ip, table_name, row_id, access_type, time_of_change, old_val, new_val) VALUES (upper(session_user), inet_client_addr(), TG_TABLE_NAME, OLD.id, TG_OP, now(), old_data, new_data);
RETURN NEW;
END IF;
ELSIF (TG_OP = 'INSERT') THEN
new_data := ROW(NEW.*);
INSERT INTO audit (username, ip, table_name, row_id, access_type, time_of_change, new_val) VALUES(upper(session_user), inet_client_addr(), TG_TABLE_NAME, NEW.id, TG_OP, now(), new_data);
RETURN NEW;
END IF;

RETURN NULL;
END;
$BODY$
*LANGUAGE plpgsql VOLATILE SECURITY DEFINER
*COST 100;
ALTER FUNCTION public.audit() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.audit() TO postgres;

Audit için koruma trigger fonksiyonun
Eğer audit tablosundan silme güncelleme işlemi yapılırsa onunda kaydının tutulması sağlanıyor. Yani loglara hiç bir şekilde müdahale edilmesin.
CREATE OR REPLACE FUNCTION public.shield()
* RETURNS trigger AS
$BODY$
BEGIN
**IF (TG_OP = 'DELETE') THEN
* **INSERT INTO audit (username, ip, table_name, row_id, access_type, time_of_change, client_username)
* * VALUES(OLD.username,* OLD.ip, OLD.table_name, OLD.row_id, OLD.access_type, OLD.time_of_change, OLD.client_username);
* **RETURN OLD;
**ELSIF (TG_OP = 'UPDATE') THEN
* **INSERT INTO audit (username, ip, table_name, row_id, access_type, time_of_change, client_username)
* * VALUES(OLD.username,* OLD.ip, OLD.table_name, OLD.row_id, OLD.access_type, OLD.time_of_change, OLD.client_username);
* **RETURN NEW;
* END IF;
**RETURN NEW;
END;$BODY$
* LANGUAGE plpgsql VOLATILE SECURITY DEFINER
* COST 100;
ALTER FUNCTION public.shield()*OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.shield() TO postgres;


Buda xxx tablon ve tabloda en altta yazan trigger fonksiyonu var bu şekilde trigger aktif edilmiş ve log tutulmuş oluyor.
CREATE TABLE public.xxx_tablo
(
*id serial,
*bilgi varchar(32) not null,
*tarih date NOT NULL,
*PRIMARY KEY (id),
*UNIQUE (bilgi)
)WITH (OIDS=FALSE);
ALTER TABLE public.xxx_tablo OWNER TO postgres;
GRANT ALL ON TABLE public.xxx_tablo TO postgres;

CREATE TRIGGER audit AFTER INSERT OR UPDATE OR DELETE ON public.xxx_tablo FOR EACH ROW EXECUTE PROCEDURE public.audit();
1234;*'POSTGRES'; '192.168.xx.xxx/32'; 'xxx_tablo'; 22614;*'UPDATE'; '2019-07-29 10:56:46.364599';*'(3,"ALİ VELİ","2019-07-29")';'(3,"MEHMET FERHAT","2019-07-30")'