Skip to main content

PostgreSQL Trigger Audit..

PostgreSQL adalah salah satu dan mungkin sampai saat ini adalah satu-satunya opensource database dengan fitur yang sangat lengkap. Salah satu kelengkapan fitur yang 'ngedab-edabi' adalah procedural language nya yang mendukung banyak bahasa, mulai dari pl milik postgreSQL sendiri, c, java, bahkan perl. Disamping tentunya SQL.

Dengan dukungan salah satu bahasa yang dipakai dalam precedural language nya, 'plperl', saya ingin berurun rembug dalam menyusun satu trigger yang akan merekam setiap transaksi yang dilakukan oleh user applikasi. Rekaman ini akan meliputi semua table yang ada di dalam database dan, tentunya, terinstall trigger, sebut saja trigger audit.

Trigger audit yang saya susun ini memerlukan kehadiran skema yang kita rancang sendiri untuk menampung rekaman aktifitas dan system object yang akan kita pakai untuk antara-lain mengidentifikasikan nama kolom dan nama table.

Table yang harus kita siapkan meliputi:

publication_dtl yang akan merekam seluruh aktifitas user (insert, delete, update)
publication_list adalah list lokasi mana aja suatu rekaman bakal di publish
subscriber_list adalah list subscriber yang mendeskripsikan suatu subscriber mensubsribe publication mana saja.
subscription_activity adalah list status aktivitas subscriber terhadap yang dia subscribe.
event_list optional, list yang berisi kode dan nama event. Di sini 1 adalah insert 2 adalah delete dan 3 adalah update.

DDL:

CREATE TABLE her.publication_dtl(action_time timestamp without time zone NOT NULL DEFAULT now(),table_id bigint NOT NULL,trans_id bigint NOT NULL DEFAULT nextval('publication_dtl_trans_id_seq'::regclass),no_urut bigint NOT NULL,column_name character varying(255),ispkey integer NOT NULL,nodeid bigint NOT NULL,col_type character varying(15),event_id bigint,old_value text,new_value text,isreplayed boolean,session_user_addr character varying(255) NOT NULL DEFAULT ((("session_user"())::text || '@'::text) || (inet_client_addr())::text),CONSTRAINT pk_subd PRIMARY KEY (trans_id),CONSTRAINT fk_evtd FOREIGN KEY (event_id)REFERENCES her.event_list (event_id) MATCH SIMPLEON UPDATE NO ACTION ON DELETE NO ACTION)WITHOUT OIDS;ALTER TABLE her.publication_dtl OWNER TO somuser;
CREATE UNIQUE INDEX action_timeON her.publication_dtlUSING btree(action_time, table_id, trans_id);
CREATE INDEX idx_pub_tbl_idON her.publication_dtlUSING btree(table_id);
CREATE INDEX idx_pub_tbl_id2ON her.publication_dtlUSING btree(table_id);ALTER TABLE her.publication_dtl CLUSTER ON idx_pub_tbl_id2;
CREATE TABLE her.publication_list(publication_id integer NOT NULL,publication_name character varying(255) NOT NULL,CONSTRAINT pk_pubnamelist PRIMARY KEY (publication_id))WITHOUT OIDS;ALTER TABLE her.publication_list OWNER TO somuser;
CREATE TABLE her.subscriber_list
(subscriber_id bigint NOT NULL,publication_id bigint NOT NULL,subscriber_name character varying(255) NOT NULL,CONSTRAINT pk_subscriber_list PRIMARY KEY (subscriber_id, publication_id))WITHOUT OIDS;ALTER TABLE her.subscriber_list OWNER TO somuser;
CREATE TABLE her.subscription_activity(subscriber_id bigint NOT NULL,publication_id bigint NOT NULL,trans_id bigint NOT NULL,replay_stat boolean NOT NULL DEFAULT false,CONSTRAINT pk_subscriber_activity PRIMARY KEY (subscriber_id, publication_id, trans_id),CONSTRAINT fk_subsciber_publication FOREIGN KEY (trans_id)REFERENCES her.publication_dtl (trans_id) MATCH SIMPLEON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT fk_subscriber_activity_subscriber FOREIGN KEY (subscriber_id,publication_id)REFERENCES her.subscriber_list (subscriber_id, publication_id) MATCH SIMPLEON UPDATE NO ACTION ON DELETE NO ACTION)WITHOUT OIDS;ALTER TABLE her.subscription_activity OWNER TO somuser;
Tadi aku sebutin bahwa kita butuh system object. Nah di sini system object yang kita butuhin adalah:

information_schema.key_column_usage dan pg_constraint yang bakal kita gunakan untuk mengidentifikasikan apakah suatu kolom itu masuk key column apa nggak
pg_attribute dan pg_type untuk menentukan nama kolom dan type data dari kolom berdasar suatu relationid (relid) yang bisa ditangkap saat trigger dijalankan.

Selain itu kita juga membutuhkan satu view bentukan yang akan memudahkan kita membaca informasi suatu table yang sedang dimainin sama user. View ini adalah sebagai berikut:

CREATE OR REPLACE VIEW her.constraint_list AS
SELECT a.constraint_schema, a.constraint_name, a.table_name, a.column_name, b.conname, b.contype
FROM information_schema.key_column_usage a, pg_constraint b
WHERE a.constraint_name::name = b.conname;

ALTER TABLE her.constraint_list OWNER TO someuser;

Nah kalo semua udah disiapkan maka kita bisa bentuk trigger yang akan kita pekerjakan untuk mencatat semua aktifitas insert/delete/update. Nah berikut ini adalah list trigger itu:

CREATE OR REPLACE FUNCTION her.ufp_audit_t()RETURNS "trigger" AS$BODY$my %hist = %{$_TD->{new}};my $stype;my $query;my $rv;my $urt;my $nodeid;my $ispkey;my $eventid;my $olds;my $newv;$urt = 1;if ($_TD->{event} eq 'INSERT'){ $eventid = 1;}elsif($_TD->{event} eq 'DELETE'){ $eventid = 2; %hist = %{$_TD->{old}};}elsif($_TD->{event} eq 'UPDATE'){ $eventid = 3;};$rv = spi_exec_query('select publication_id as node_id from her.publication_list',1);$nodeid = $rv->{rows}[0]->{node_id};while (($key,$value) = each %hist){ $query = 'select a.attname,b.typname,b.typowner from pg_attribute a,pg_type b where a.atttypid = b.oid and a.attname =\'' .$key.'\' and a.attrelid = '.$_TD->{relid}; $rv = spi_exec_query($query,1); $stype = $rv->{rows}[0]->{typname}; $query = 'select constraint_name from her.constraint_list where table_name = \''.$_TD->{relname}.'\' and column_name = \''.$key.'\'';
$rv = spi_exec_query($query,1); if ($rv->{processed}>0) { $ispkey = 1; }else{ $ispkey = 0; } $olds = $_TD->{old}{$key}; $newv = $_TD->{new}{$key}; $olds =~ s/\'/\'\'/g; $newv =~ s/\'/\'\'/g; $olds =~ s/\\/\\\\/g; $newv =~ s/\\/\\\\/g; if ( ( $olds != $newv ) || ( $olds ne $newv ) || ($key eq 'user_id') || ($ispkey == 1)) { $query = 'insert into her.publication_dtl(table_id,no_urut,column_name,ispkey,nodeid,col_type,event_id,old_value,new_value,isReplayed)values('.$_TD->{relid}.','.$urt.',\''.$key.'\','.$ispkey.','.$nodeid.',\''.$stype.'\','.$eventid.',\''.$olds.'\',\''.$newv.'\',FALSE)';
$rv = spi_exec_query($query);
}
$urt++;}return;# 'SKIP';$BODY$LANGUAGE 'plperl' VOLATILE;ALTER FUNCTION her.ufp_audit_t() OWNER TO somuser;

Kemudian untuk mendeploy trigger ini ke semua table kita gunakan function ini:

CREATE OR REPLACE FUNCTION her.update_traudit()
RETURNS void AS
$BODY$
my $rv = spi_exec_query('select table_name from her.artikellist');
my $nrows = $rv->{processed};
if ($nrows > 0) {
foreach my $rn (0 .. $nrows - 1){
my $row = $rv->{rows}[$rn];
my $tbl = $row->{table_name};
my $trl = "select * from pg_trigger where tgname ='tr_".$tbl."_au'";
my $rv = spi_exec_query($trl);
my $rrows = $rv->{processed};
if($rrows > 0 ){
my $drtr = 'DROP TRIGGER tr_'.$tbl.'_au ON '.$tbl;
my $rvv = spi_exec_query($drtr);
}
my $qry = 'CREATE TRIGGER tr_'.$tbl.'_au
BEFORE INSERT OR UPDATE OR DELETE ON '.$tbl.'
FOR EACH ROW
EXECUTE PROCEDURE her.ufp_audit_t()';
elog(INFO,$qry);
my $rv2 = spi_exec_query($qry);
}
}
$BODY$
LANGUAGE 'plperl' VOLATILE;
ALTER FUNCTION her.update_traudit() OWNER TO someuser;



Kalo diperhatiin maka nama-nama object bikinan tadi macem buat bikin replikasi. He he he, emang iya. Itu skema semua aku siapin buat replikasi tapi karena waktu gak ada ya udah buat audit aja. OK dah dulu ya..., Kelanjutan ceritanya bisa akupost nanti-nanti aja ya...
Labels:

Popular posts from this blog

Awal Perjalanan

Jalan di dusun Pedhudutan pagi ini masih sangat sepi. Meskipun adzan Shubuh telah berkumandang, dan jama’ah Shubuh telah kembali dari langgar, namun aktifitas penduduk masih belum terlihat bergeliat. Hanya beberapa penduduk yang terlihat telah mendahului pergi ke pasar menjemput pagi. Menjemput rezeki pagi ini. Wadasputih, lintasan pegunungan yang melingkupi dusun Pohkumbang, masih nampak hitam di ujung timur seakan mencanda mentari agar tetap dalam peraduannya meski semburat tangan sinarnya telah menggapai awan yang masih malas-malasan di atas sana. Padepokan Gagak Wulung, pagi ini, terasa sangat sepi. Tidak seperti hari-hari sebelumnya. Tidak ada suara cantrik mengalunkan pesan-pesan ilahi, maupun yang gladen, olah kanuragan. Bahkan dapur-dapur padepokan yang biasanya diisi oleh para simbok, istri-istri cantrik senior maupun para cantrik perempuan, pagi ini sepi. Hanya ada sedikit sisa asap pedhangan bekas menanak nasi tadi, sebelum subuhan. Semua cantrik berkumpul di depan pendo...
Read more

Analisis Interaktif Kerentanan PHP

Dasbor Analisis Kerentanan PHP 2025 Analisis Interaktif Kerentanan PHP CVE-2025-1735 (pgsql) & CVE-2025-6491 (SOAP) CVE-2025-1735 CVE-2025-6491 Ringkasan CVE-2025-1735: Ekstensi `pgsql` Kerentanan ini berasal dari penanganan kesalahan yang tidak memadai dalam ekstensi PostgreSQL PHP, yang berpotensi menyebabkan Injeksi SQL dan Penolakan Layanan (DoS). Bagian ini memvisualisasikan data kunci untuk memahami risikonya. Detail Kerentanan ...
Read more

Bincang Ringan, Misi Kristen dan Dakwah Islam (I)

Dalam sebuah diskusi dengan santri saya mengajukan pertanyaan, "Bolehkah orang Kristen menyiarkan agamanya ke orang Islam ?" Serentak mereka menjawab "Tidak boleh". Kemudian saya mengajukan pertanyaan susulan, "Rekan-rekan santri senang tidak, kalau ada orang yang masuk Islam karena didakwahi oleh Koh Hany," dan secara serempak juga mereka menjawab "Tentu senang ustadz." Kontan saya timpali, lha kok nggak adil, orang Kristen nggak boleh menyiarkan agama ke umat Islam, sementara umat Islam boleh mendakwahi orang Kristen. Saya jadi ingat kutipan dari Syaikh Ali Mahfuzh, guru besar Ilmu Da'wah wal Irsyad, Anggota Majelis Ulama dan Pembina Ilmu Dakwah pada Universitas Al Azhar dalam bukunya Widji Saksono, Mengislamkan Jawa, terbitan Mizan tahun 1995. "Barangsiapa memperhatikan dengan seksama, tahulah ia bahwa sesungguhnya dakwah kepada Allah itu adalah sendi kehidupan suatu agama, pangkal syi'ar suatu kepercayaan. Syahdan pad...
Read more