Monday, November 26, 2007

PostgreSQL Trigger Audit..

Wihartoyo     Monday, November 26, 2007    

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...


Recommended