Konuyu Oyla:
  • Derecelendirme: 5/5 - 1 oy
  • 1
  • 2
  • 3
  • 4
  • 5
PostgreSQL Audit Yedekleme (Tablolardaki yapılan işlemlerin kayıt altına alınması)
#1
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")'
PostgreSQL - Linux - Delphi, Poliüretan
WWW
Cevapla


Konu ile Alakalı Benzer Konular
Konular Yazar Yorumlar Okunma Son Yorum
Photo PostgreSQL, Windows 11 ve Türkçe yeni kurulum hatası mustafaozpinar 2 2.394 17-12-2024, Saat: 18:37
Son Yorum: nguzeller
  POSTGRESQL Otomatik artan sıra no nasıl veriliyor? COMMANDX 8 6.604 23-06-2022, Saat: 16:42
Son Yorum: COMMANDX
  PostgreSQL trigger hatası 41linea41 3 2.587 07-01-2022, Saat: 22:36
Son Yorum: 3ddark
  postgresql hakkında erdal51 9 8.300 28-09-2021, Saat: 14:20
Son Yorum: mcuyan
  Postgresql date alan ekleme sorunu barissagir 7 5.169 22-01-2021, Saat: 00:00
Son Yorum: barissagir



Konuyu Okuyanlar: 1 Ziyaretçi