Belajar PostgreSQL: Audit Data Tabel - Part 4

Dalam sebuah aplikasi yg kompleks, audit adalah hal wajib dimiliki karena segala proses data harus di cek utk menghindari kesalahan ataupun utk tujuan tertentu. Misalkan kita ingin mengetahui siapa, apa dan kapan dilakukannya sesuatu terhadap data dalam sistem, salah satu cara utk melakukannya adalah dengan membuat log data pada tabel tertentu.

Kita akan membahas mengenai proses audit dengan menggunakan PostgreSQL. Pada kasus ini setidaknya ada 2 cara yg dapat dilakukan utk proses audit data:

- Audit data dengan trigger

- Memberikan akses tabel hanya melalui sebuah fungsi dan melakukan audit dari fungsi tersebut

Kita coba buat contoh terkait kedua cara diatas. Pertama kita buat sebuah tabel terlebih dahulu.

CREATE TABLE tb_penggajian(
  nama_pegawai text PRIMARY KEY,
  nominal_gaji integer NOT NULL
);

CREATE TABLE tb_log_gaji(
  diganti_oleh text DEFAULT CURRENT_USER,
  waktu_ganti timestamp DEFAULT CURRENT_TIMESTAMP,
  jenis_input text,
  nama_pegawai text,
  gaji_lama integer,
  gaji_baru integer
);

REVOKE ALL ON tb_log_gaji FROM PUBLIC;
GRANT ALL ON tb_log_gaji TO managers;

Pada 2 query terakhir diatas, dimisalkan kita membuat hak akses dimana user biasa tidak seharusnya dapat mengakses tabel log utk audit dan hanya bisa diakses oleh manager. Lalu kita akan buat fungsi utk proses mencatat ataupun membuat log utk transaksi yg dilakukan.

CREATE OR REPLACE FUNCTION log_perubahan_gaji () RETURNS TRIGGER AS
$$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO tb_log_gaji(jenis_input, nama_pegawai, gaji_baru)
    VALUES (TG_OP, NEW.nama_pegawai, NEW.nominal_gaji);
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO tb_log_gaji(jenis_input, nama_pegawai, gaji_lama, gaji_baru)
    VALUES (TG_OP, NEW.nama_pegawai, OLD.nominal_gaji, NEW.nominal_gaji);
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO tb_log_gaji(jenis_input, nama_pegawai, gaji_lama)
    VALUES (TG_OP, NEW.nama_pegawai, OLD.nominal_gaji);
  END IF;

  RETURN NEW;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_audit_perubahan_gaji
AFTER INSERT OR UPDATE OR DELETE ON tb_penggajian
FOR EACH ROW EXECUTE PROCEDURE log_perubahan_gaji();

Selanjutnya, kita buat contoh data tb_penggajian utk mencoba fungsi trigger yg telah kita buat.

INSERT INTO tb_penggajian values('John', 1500000);
UPDATE tb_penggajian set nominal_gaji = 1700000 where nama_pegawai = 'John';
INSERT INTO tb_penggajian values('Jenny', 1500000);
UPDATE tb_penggajian set nominal_gaji = nominal_gaji + 200000;
SELECT * FROM tb_penggajian;
nama_pegawai | nominal_gaji
----------+---------
John | 1900000
Jenny | 1700000
(2 rows)

Dari query diatas kita sudah melakukan proses insert dan update data, tentunya hal ini sudah di record kedalam tabel log yg sudah kita buat. Jika kita cek tabel log maka akan tampil log data.

SELECT * FROM tb_log_gaji;
diganti_oleh | waktu_ganti                | jenis_input | nama_pegawai | gaji_lama | gaji_baru
-------------+----------------------------+-------------+--------------+-----------+-----------
postgres     | 2018-10-07 18:27:04.128319 | INSERT      | John         |           | 1500000
postgres     | 2018-10-07 18:27:04.151736 | UPDATE      | John         | 1500000   | 1700000
postgres     | 2018-10-07 18:27:04.154109 | INSERT      | Jenny        |           | 1500000
postgres     | 2018-10-07 18:27:04.156283 | UPDATE      | John         | 1700000   | 1900000
postgres     | 2018-10-07 18:27:04.156283 | UPDATE      | Jenny        | 1500000   | 1700000
(5 rows)

Kita sudah dapatkan hasil log yg sesuai. Sedikit tambahan sekiranya kita tidak ingin ada user lain yg dapat mengakses tabel tb_penggajian, kita dapat mengatur hak aksesnya dengan query berikut:

REVOKE ALL ON tb_penggajian FROM PUBLIC;

Dan juga kita dapat memberikan akses user hanya pada 2 macam fungsi, misalnya utk setiap user hanya dapat melihat data tabel tb_penggajian dan setiap user lainnya dapat merubah data tabel tb_penggajian, yaitu seorang manager.

Fungsi yg telah kita buat tadi memiliki klausa SECURITY DEFINER yg berarti fungsi tersebut hanya akan dijalankan melalui user yg membuatnya.

Kita lanjutkan dengan membuat fungsi mencari gaji.

CREATE OR REPLACE FUNCTION cari_gaji(text)
RETURNS INTEGER AS
$$
  -- Jika anda melihat gaji orang lain, maka akan dicatat
  INSERT INTO tb_log_gaji(jenis_input, nama_pegawai, gaji_baru)
  SELECT 'SELECT', nama_pegawai, nominal_gaji FROM tb_penggajian
  WHERE upper(nama_pegawai) = upper($1)
  AND upper(nama_pegawai) != upper(CURRENT_USER); -- Jangan catat utk gaji sendiri
  -- tampilkan gaji yg diinginkan
  SELECT nominal_gaji FROM tb_penggajian
  WHERE upper(nama_pegawai) = upper($1);
$$
LANGUAGE SQL SECURITY DEFINER;

Fungsi diatas merupakan contoh sederhana yg mana kita dapat melihat gaji orang lain dan tentunya hal ini harus dilakukan atas tanggungjawab pekerjaan, ya kasus seperti ini hanya diperuntukkan kepada orang tertentu semisal seorang manager. Dan juga bila dilihat kembali fungsi diatas kita buat hanya menggunakan bahasa SQL dan bukan menggunakan PL/pgSQL, karena memang PostgreSQL mendukung banyak jenis bahasa pemrograman utk membuat sebuah fungsi.

Kita lanjutkan dengan fungsi terakhir yaitu mengatur gaji (set nominal_gaji). Fungsi yg akan kita buat ini berfungsi utk memeriksa apakah pegawai ditemukan dalam data, jika tidak ada maka akan dibuat secara otomatis. Ketika mengatur gaji pegawai menjadi 0, makan pegawai tersebut akan dihapus datanya dari tabel tb_penggajian.

CREATE OR REPLACE FUNCTION atur_penggajian(input_nama_pegawai text, input_nominal_gaji int)
RETURNS TEXT AS
$$
DECLARE
  gaji_lama integer;
BEGIN
  SELECT nominal_gaji INTO gaji_lama
  FROM tb_penggajian
  WHERE upper(nama_pegawai) = upper(input_nama_pegawai);

  IF NOT FOUND THEN
    INSERT INTO tb_penggajian VALUES(input_nama_pegawai, input_nominal_gaji);
    INSERT INTO tb_log_gaji(jenis_input,nama_pegawai,gaji_baru)
    VALUES ('INSERT',input_nama_pegawai,input_nominal_gaji);
    RETURN 'INSERTED USER ' || input_nama_pegawai;
  ELSIF input_nominal_gaji > 0 THEN
    UPDATE tb_penggajian SET nominal_gaji = input_nominal_gaji
    WHERE upper(nama_pegawai) = upper(input_nama_pegawai);
    INSERT INTO tb_log_gaji (jenis_input,nama_pegawai,gaji_lama,gaji_baru)
    VALUES ('UPDATE',input_nama_pegawai,gaji_lama,input_nominal_gaji);
    RETURN 'UPDATED USER ' || input_nama_pegawai;
  ELSE -- set gaji 0
    DELETE FROM tb_penggajian
    WHERE upper(nama_pegawai) = upper(input_nama_pegawai);
    INSERT INTO tb_log_gaji (jenis_input,nama_pegawai,gaji_lama)
    VALUES ('DELETE',input_nama_pegawai,gaji_lama);
    RETURN 'DELETED USER ' || input_nama_pegawai;
  END IF;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

Sekarang kita perlu utk menghapus trigger yg telah kita buat karena kita sudah tidak membutuhkannya lagi (jika tidak dihapus maka data log akan tersimpan 2x) dan kita akan menggunakan fungsi baru yg kita buat diatas.

DROP TRIGGER trigger_audit_perubahan_gaji ON tb_penggajian;

SELECT atur_penggajian('Jack', 1300000);
     atur_penggajian
--------------------
INSERTED USER Jack
(1 row)

SELECT atur_penggajian('Bob', 1000000);
     atur_penggajian
-------------------
INSERTED USER Bob
(1 row)


SELECT * FROM tb_penggajian;
nama_pegawai | nominal_gaji
-------------+---------
John        | 1900000
Jenny        | 1700000
Jack        | 1300000
Bob        | 1000000
(4 rows)

SELECT atur_penggajian('Jenny', 0);
     atur_penggajian
--------------------
DELETED USER Jenny
(1 row)

SELECT * FROM tb_log_gaji;
diganti_oleh  | waktu_ganti                | jenis_input | nama_pegawai | gaji_lama | gaji_baru
--------------+----------------------------+-------------+--------------+-----------+-----------
postgres      | 2018-10-07 18:27:04.128319 | INSERT      | John         |           | 1500000
postgres      | 2018-10-07 18:27:04.151736 | UPDATE      | John         | 1500000   | 1700000
postgres      | 2018-10-07 18:27:04.154109 | INSERT      | Jenny        |           | 1500000
postgres      | 2018-10-07 18:27:04.156283 | UPDATE      | John         | 1700000   | 1900000
postgres      | 2018-10-07 18:27:04.156283 | UPDATE      | Jenny        | 1500000   | 1700000
postgres      | 2018-10-07 19:56:15.075756 | INSERT      | Jack         |           | 1300000
postgres      | 2018-10-07 19:56:20.144901 | INSERT      | Bob          |           | 1000000
postgres      | 2018-10-07 19:57:06.802919 | DELETE      | Jenny        | 1700000   |
(8 rows)

Data yg kita dapatkan sangat rapi bukan, memang diawal proses terlihat sangat sulit (meskipun sebenarnya memang sulit sih :D ) tapi utk jangka panjang akan sangat bermanfaat. Sedikit tambahan pada data diatas nama pegawai yg kita miliki tidak memiliki konsistensi terhadap inputan, jadi kita bisa input data dengan huruf kapital maupun tidak. Maka dari itu konsistensi data sangatlah diperlukan dan utk kasus begini akan lebih baik kita simpan dalam bentuk huruf kapital semua. Caranya cukup mudah hanya dengan menambahkan constraint CHECK seperti yg sudah pernah kita pakai di part sebelumnya.

CHECK (nama_pegawai = upper(nama_pegawai))

Tetapi kita akan menggunakan trigger utk mengatasi hal ini. Mari kita langsung buat trigger nya.

CREATE OR REPLACE FUNCTION uppercase_name() RETURNS TRIGGER AS
$$
BEGIN
  NEW.nama_pegawai = upper(NEW.nama_pegawai);
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER uppercase_nama_pegawai
BEFORE INSERT OR UPDATE OR DELETE ON tb_penggajian
FOR EACH ROW
EXECUTE PROCEDURE uppercase_name();

Saat fungsi atur_penggajian() digunakan kembali nantinya maka semua nama pegawai yg diinput akan disimpan dalam huruf kapital.

SELECT atur_penggajian('arnold', 800000);
SELECT * FROM tb_penggajian;
nama_pegawai | nominal_gaji
-------------+---------
John         | 1900000
Jack         | 1300000
Bob          | 1000000
ARNOLD       | 800000
(4 rows)

Lalu utk mengatasi nama pegawai yg sebelumnya kita cukup meng-update datanya dan menambahkan constraint CHECK.

UPDATE tb_penggajian SET nama_pegawai = upper(nama_pegawai)
WHERE NOT
nama_pegawai = upper(nama_pegawai);

ALTER TABLE tb_penggajian ADD CONSTRAINT nama_pegawai_must_be_uppercase
CHECK (nama_pegawai = upper(nama_pegawai));

Itulah beberapa contoh kasus dalam penerapan sistem audit dengan PostgreSQL. Semua contoh diatas hanyalah utk kasus sederhana, tetapi setidaknya bisa dimengerti utk penerapan pada kasus aplikasi yg nyata. Jangan lupa selalu isi kolom komentar jika ingin bertanya dan koreksi yg harus saya dilakukan.