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

Superfish Menginjeksi Laman-Laman Google

Beberapa bulan lalu pengguna lenovo sempat dibuat gerah dengan penggunaan software superfish yang sengaja disisipkan oleh Lenovo. Penggunaan adware yang sebenarnya dimaksudkan untuk memudahkan pengguna yang suka berbelanja ternyata justeru menjadi duri dalam daging bagi pengguna. Adware ini, mampu membongkar enkripsi web pengguna komputer dan akhirnya membuat komputer yang bersangkutan menjadi rentan terhadap serangan cyber. Dan yang paling ‘menggemaskan’ adalah adware ini bisa menyuntikkan iklan-iklan yang tidak diinginkan (bahkan sering menampilkan gambar perempuan dengan pakaian minim) ketika pengguna membuka web apapun. Meskipun Lenovo telah berjanji untuk menghentikan penggunaan Superfish, namun ternyata bahaya masih mengintai para pengguna internet. Google sebagai peramban paling populer saat ini berpotensi menyebarkan Ad Injection melalui ekstensyen nya. Berdasar riset dari Google, 4% laman google telah di-inject oleh superfish dengan iklan-iklan yang gak jelas. Dan 5....
Read more

Masih Pagi??

Ingat saat di sekolah S3 (SD,SMP,SMA) segala sesuatu yang menyangkut hasrat dan keinginan ada kalanya mentog kepada satu frasa: masih pagi. Berpikir tentang rezeki katanya masih terlalu pagi untuk memikirkan hal yang gak-gak gitu. Mengangankan tentang hari nanti (mungkin adalah saat ini), juga dibilang masih terlalu pagi apalagi jika sifatnya pesimistis. Terima kasih untuk semua yang mengatakan itu karena setidaknya aku bisa berfikir saat ini, bahwa memang masih terlalu pagi (saat itu), tetapi terlalu pagi itu bukan artinya untuk berhenti memulai. Tetapi terlalu pagi yang berarti bahwa memang sudah seharusnya dimulai saat itu. Kita tidak pernah tahu apa yang akan kita dapatkan nanti, dan karena itu kita harus tahu apa yang harus kita kerjakan saat ini. Merencanakan adalah kata yang sangat indah untuk diucapkan. Perencanaan adalah sesuatu yang sangat mudah untuk dituliskan manakala pelaksanaan tidak pernah terpikirkan. Semua bermuara pada satu kata : tangggung jawab . Tanggung jawab...
Read more

Snort dan Suricata IDPS

Sugeng pagi, Mas! Pagi, gimana kabarnya? Baik, Mas. Gini, Mas! Saya lagi pusing ngurusin jaringan di kantor. Pusing kenapa? Itu lo, Mas, virus tau-tau nongol tanpa permisi.  Bikin jaringan jadi lemot. Yang lebih repotnnya lagi, kita gak tau dari sebelah mana penyebarannya. Lah, gitu aja kok pusing.  Kamu punya alat monitoring macem IDS gak? Apa itu IDS, Mas? IDS itu, singkatan coro londo Intrussion Detection System.  Maksudnya sistem buat mendeteksi aktifitas penyusupan. La, itu kan bukan antivirus, Mas! Lagian mana ada sih yang nyusup di kantor saya.  Apalagi kantor banyakan mainnya di jaringan private. La ini, yang salah! Jangan beranggapan bahwa setiap penyusupan itu datangnya dari luar. Ancaman kebocoran itu paling besar justeru datangnya dari dalam. Dan satu hal lagi penyusupan tidak harus terjadi dalam satu saat.  Seperti proses penyusupan yang dilakukan dengan menggunakan kuda trojan.  Para penyusup justeru anteng dulu di dalam kuda trojan....
Read more