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

Diğer veritabanları ve SQL komutlarıyla ilgli sorularınızı sorabilirsiniz. Delphi tarafındaki sorularınızı lütfen Programlama forumunda sorunuz.
Cevapla
3ddark
Üye
Mesajlar: 65
Kayıt: 14 Eyl 2010 09:55
Konum: İstanbul

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

Mesaj gönderen 3ddark » 29 Tem 2019 11:05

Bu paylaşımda diğer kardeş forumda yazmıştım buraya da bilgi olması adına ekliyorum.

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

Kod: Tümünü seç

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

Kod: Tümünü seç

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.

Kod: Tümünü seç

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.

Kod: Tümünü seç

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")'
PostgreSQL - Delphi - Linux :!:

Cevapla