Jejak audit yang mendeteksi manipulasi di PostgreSQL dengan rantai hash

Mengapa log audit biasa mudah diperdebatkan

Jejak audit adalah catatan yang Anda andalkan ketika sesuatu terlihat salah: pengembalian dana yang aneh, perubahan izin yang tak diingat siapa pun, atau catatan pelanggan yang “menghilang”. Jika jejak audit bisa diedit, ia berhenti menjadi bukti dan berubah menjadi data lain yang bisa ditulis ulang.

Banyak “log audit” sebenarnya hanyalah tabel biasa. Jika baris bisa diupdate atau dihapus, cerita itu juga bisa diupdate atau dihapus.

Pembeda utama: mencegah pengeditan tidak sama dengan membuat pengeditan terdeteksi. Anda bisa mengurangi perubahan lewat hak akses, tapi siapa pun dengan akses cukup (atau kredensial admin yang dicuri) tetap bisa mengubah riwayat. Pendekatan yang mendeteksi manipulasi menerima realitas itu. Anda mungkin tidak mencegah setiap perubahan, namun Anda bisa membuat perubahan meninggalkan sidik yang jelas.

Log audit biasa diperdebatkan karena alasan yang bisa diprediksi. Pengguna dengan hak istimewa bisa “memperbaiki” log setelah kejadian. Akun aplikasi yang dikompromikan bisa menulis entri yang meyakinkan dan tampak seperti lalu lintas normal. Timestamp bisa diisi ulang untuk menyembunyikan perubahan terlambat. Atau seseorang hanya menghapus baris yang paling merugikan.

"Mendeteksi manipulasi" berarti Anda merancang jejak audit sehingga bahkan edit kecil (mengubah satu field, menghapus satu baris, mengubah urutan peristiwa) akan terdeteksi kemudian. Anda tidak menjanjikan hal ajaib. Anda menjanjikan bahwa saat seseorang bertanya, “Bagaimana kita tahu log ini asli?”, Anda bisa menjalankan pemeriksaan yang menunjukkan apakah log itu disentuh.

Tentukan apa yang perlu Anda buktikan

Jejak audit yang mendeteksi manipulasi hanya berguna jika menjawab pertanyaan yang akan diajukan nanti: siapa melakukan apa, kapan dilakukan, dan apa yang berubah.

Mulailah dengan peristiwa yang penting bagi bisnis Anda. Perubahan data (create, update, delete) adalah dasar, tapi penyelidikan sering bergantung juga pada keamanan dan akses: login, reset password, perubahan izin, dan penguncian akun. Jika Anda menangani pembayaran, pengembalian dana, kredit, atau payout, perlakukan perpindahan uang sebagai peristiwa utama, bukan sekadar efek samping dari baris yang diupdate.

Lalu putuskan apa yang membuat sebuah peristiwa kredibel. Auditor biasanya mengharapkan seorang pelaku (user atau service), timestamp sisi-server, aksi yang diambil, dan objek yang terpengaruh. Untuk update, simpan nilai sebelum dan sesudah (atau setidaknya field sensitif), plus request id atau correlation id sehingga Anda bisa mengaitkan banyak perubahan kecil di database ke satu aksi pengguna.

Terakhir, jelaskan apa arti “immutable” dalam sistem Anda. Aturan paling sederhana: jangan pernah mengupdate atau menghapus baris audit, hanya insert. Jika ada yang salah, tulis peristiwa baru yang memperbaiki atau menggantikan yang lama, dan biarkan yang asli tetap terlihat.

Bangun tabel audit append-only

Jaga data audit terpisah dari tabel normal Anda. Skema audit terdedikasi mengurangi kesalahan pengeditan dan mempermudah pengaturan hak akses.

Tujuannya sederhana: baris boleh ditambahkan, tetapi tidak boleh diubah atau dihapus. Di PostgreSQL, Anda menegakkan itu dengan privileges (siapa boleh apa) dan beberapa pengaman pada desain tabel.

Berikut tabel awal yang praktis:

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE audit.events (
  id            bigserial PRIMARY KEY,
  entity_type   text        NOT NULL,
  entity_id     text        NOT NULL,
  event_type    text        NOT NULL CHECK (event_type IN ('INSERT','UPDATE','DELETE')),
  actor_id      text,
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  request_id    text,
  before_data   jsonb,
  after_data    jsonb,
  notes         text
);

Beberapa field sangat berguna saat penyelidikan:

• occurred_at dengan DEFAULT now() sehingga waktu dicatat oleh database, bukan klien.
• entity_type dan entity_id agar Anda bisa mengikuti satu catatan melalui perubahan.
• request_id agar satu aksi pengguna bisa ditelusuri ke banyak baris.

Kunci dengan peran. Peran aplikasi Anda sebaiknya bisa INSERT dan SELECT pada audit.events, tetapi tidak UPDATE atau DELETE. Simpan perubahan skema dan hak akses kuat untuk peran admin yang tidak dipakai oleh aplikasi.

Tangkap perubahan dengan trigger (bersih dan dapat diprediksi)

Jika Anda ingin jejak audit yang mendeteksi manipulasi, tempat paling andal untuk menangkap perubahan adalah database. Log aplikasi bisa dilewati, disaring, atau ditulis ulang. Trigger akan terpanggil tidak peduli aplikasi, script, atau tool admin mana yang menyentuh tabel.

Buat trigger sederhana. Tugasnya satu: menambahkan satu event audit untuk setiap INSERT, UPDATE, dan DELETE pada tabel yang penting.

Rekam audit biasanya meliputi nama tabel, jenis operasi, primary key, nilai sebelum dan sesudah, timestamp, dan identifier yang memungkinkan Anda mengelompokkan perubahan terkait (transaction id dan correlation id).

Correlation id adalah pembeda antara “20 baris diupdate” dan “Ini satu klik tombol.” Aplikasi Anda bisa menetapkan correlation id sekali per request (misalnya dalam setting session DB), dan trigger bisa membacanya. Simpan juga txid_current(), sehingga Anda tetap bisa mengelompokkan perubahan saat correlation id tidak ada.

Berikut pola trigger sederhana yang tetap dapat diprediksi karena hanya melakukan insert ke tabel audit (sesuaikan nama dengan skema Anda):

CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger AS $$
DECLARE
  corr_id text;
BEGIN
  corr_id := current_setting('app.correlation_id', true);

  INSERT INTO audit_events(
    occurred_at, table_name, op, row_pk,
    old_row, new_row, db_user, txid, correlation_id
  ) VALUES (
    now(), TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id),
    to_jsonb(OLD), to_jsonb(NEW), current_user, txid_current(), corr_id
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Tahan diri dari godaan melakukan lebih banyak hal di trigger. Hindari query tambahan, panggilan jaringan, atau percabangan kompleks. Trigger yang kecil lebih mudah diuji, lebih cepat dijalankan, dan lebih sulit diperdebatkan saat tinjauan.

Tambahkan rantai hash supaya pengeditan meninggalkan sidik

Tabel append-only membantu, tapi seseorang dengan akses cukup masih bisa menulis ulang baris lama. Rantai hash membuat jenis pemalsuan itu terlihat.

Tambahkan dua kolom pada setiap baris audit: prev_hash dan row_hash (kadang disebut chain_hash). prev_hash menyimpan hash baris sebelumnya dalam rantai yang sama. row_hash menyimpan hash baris saat ini, yang dihitung dari data baris plus prev_hash.

Apa yang Anda hash penting. Anda ingin input yang stabil dan dapat diulang sehingga baris yang sama selalu menghasilkan hash yang sama.

Pendekatan praktis adalah meng-hash string kanonik yang dibangun dari kolom tetap (timestamp, actor, action, entity id), payload kanonik (sering jsonb, karena kunci disimpan secara konsisten), dan prev_hash.

Hati-hati dengan detail yang bisa berubah tanpa arti, seperti whitespace, urutan kunci JSON dalam teks biasa, atau format yang bergantung locale. Jaga tipe konsisten dan serialisasi dengan cara yang dapat diprediksi.

Rantai per stream, bukan per seluruh database

Jika Anda menghubungkan setiap event audit dalam satu urutan global, penulisan bisa menjadi bottleneck. Banyak sistem membuat rantai per “stream”, misalnya per tenant, per jenis entitas, atau per objek bisnis.

Setiap baris baru mencari row_hash terakhir untuk stream-nya, menyimpannya sebagai prev_hash, lalu menghitung row_hash sendiri.

-- Requires pgcrypto
-- digest() returns bytea; store hashes as bytea
row_hash = digest(
  concat_ws('|',
    stream_key,
    occurred_at::text,
    actor_id::text,
    action,
    entity,
    entity_id::text,
    payload::jsonb::text,
    encode(prev_hash, 'hex')
  ),
  'sha256'
);

Ambil snapshot head rantai

Untuk pemeriksaan lebih cepat, simpan row_hash terbaru (“chain head”) secara berkala, misalnya harian per stream, dalam tabel snapshot kecil. Saat penyelidikan, Anda bisa memverifikasi rantai sampai setiap snapshot daripada memindai seluruh riwayat sekaligus. Snapshot juga mempermudah membandingkan ekspor dan menemukan celah mencurigakan.

Konkurensi dan pengurutan tanpa merusak rantai

Rantai hash menjadi rumit di lalu lintas nyata. Jika dua transaksi menulis baris audit bersamaan dan keduanya memakai prev_hash yang sama, Anda bisa mendapatkan percabangan (fork). Itu melemahkan kemampuan Anda untuk membuktikan urutan tunggal yang bersih.

Pertama tentukan apa yang direpresentasikan rantai Anda. Satu rantai global paling mudah dijelaskan tetapi punya kontensi tertinggi. Banyak rantai mengurangi kontensi, tetapi Anda harus jelas tentang apa yang dibuktikan setiap rantai.

Model mana pun yang Anda pilih, definisikan urutan ketat dengan id peristiwa monotonik (biasanya id berbasis sequence). Timestamp tidak cukup karena bisa bertabrakan dan bisa dimanipulasi.

Untuk menghindari kondisi balapan saat menghitung prev_hash, serialisasi “ambil hash terakhir + insert baris berikutnya” untuk setiap stream. Pendekatan umum adalah mengunci satu baris yang merepresentasikan head stream, atau menggunakan advisory lock yang dikunci berdasarkan id stream. Tujuannya supaya dua penulis ke stream yang sama tidak bisa sama-sama membaca last hash yang sama.

Partisi dan sharding memengaruhi di mana “baris terakhir” berada. Jika Anda berencana mempartisi data audit, jaga setiap rantai sepenuhnya berada dalam satu partisi dengan menggunakan partition key yang sama dengan stream key (misalnya tenant id). Dengan begitu, rantai tenant tetap dapat diverifikasi walau tenant nanti dipindahkan antar server.

Cara memverifikasi rantai saat penyelidikan

Rantai hash hanya membantu jika Anda bisa membuktikan bahwa rantai masih valid ketika seseorang bertanya. Pendekatan paling aman adalah query verifikasi read-only (atau job) yang menghitung ulang hash setiap baris dari data yang tersimpan dan membandingkannya dengan yang dicatat.

Verifier sederhana yang bisa dijalankan on-demand

Verifier seharusnya: membangun ulang hash yang diharapkan untuk setiap baris, memastikan setiap baris terhubung ke sebelumnya, dan menandai apa pun yang mencurigakan.

Berikut pola umum menggunakan window function. Sesuaikan nama kolom dengan tabel Anda.

WITH ordered AS (
  SELECT
    id,
    created_at,
    actor_id,
    action,
    entity,
    entity_id,
    payload,
    prev_hash,
    row_hash,
    LAG(row_hash) OVER (ORDER BY created_at, id) AS expected_prev_hash,
    /* expected row hash, computed the same way as in your insert trigger */
    encode(
      digest(
        coalesce(prev_hash, '') || '|' ||
        id::text || '|' ||
        created_at::text || '|' ||
        coalesce(actor_id::text, '') || '|' ||
        action || '|' ||
        entity || '|' ||
        entity_id::text || '|' ||
        payload::text,
        'sha256'
      ),
      'hex'
    ) AS expected_row_hash
  FROM audit_log
)
SELECT
  id,
  created_at,
  CASE
    WHEN prev_hash IS DISTINCT FROM expected_prev_hash THEN 'BROKEN_LINK'
    WHEN row_hash IS DISTINCT FROM expected_row_hash THEN 'HASH_MISMATCH'
    ELSE 'OK'
  END AS status
FROM ordered
WHERE prev_hash IS DISTINCT FROM expected_prev_hash
   OR row_hash IS DISTINCT FROM expected_row_hash
ORDER BY created_at, id;

Di luar “rusak atau tidak,” ada baiknya juga memeriksa adanya celah (id yang hilang dalam rentang), tautan yang urutannya salah, dan duplikat mencurigakan yang tidak sesuai alur kerja nyata.

Catat hasil verifikasi sebagai event yang immutable

Jangan jalankan query lalu menyembunyikan hasilnya di tiket. Simpan hasil verifikasi dalam tabel append-only terpisah (misalnya, audit_verification_runs) dengan waktu run, versi verifier, siapa yang memicu, rentang yang diperiksa, dan jumlah broken link dan hash mismatch.

Itu memberi Anda jejak kedua: bukan hanya log audit utuh, tetapi Anda bisa menunjukkan bahwa Anda memang telah memeriksanya.

Kader praktis: jalankan setelah setiap deploy yang menyentuh logika audit, setiap malam untuk sistem aktif, dan selalu sebelum audit yang direncanakan.

Kesalahan umum yang merusak bukti manipulasi

Sebagian besar kegagalan bukan soal algoritma hash. Mereka soal pengecualian dan celah yang memberi orang ruang untuk berargumen.

Cara tercepat kehilangan kepercayaan adalah mengizinkan update pada baris audit. Bahkan jika itu “hanya sekali,” Anda telah menciptakan preseden dan jalur kerja untuk menulis ulang riwayat. Jika perlu memperbaiki sesuatu, tambahkan event audit baru yang menjelaskan koreksi dan simpan yang asli.

Rantai hash juga gagal ketika Anda meng-hash data yang tidak stabil. JSON adalah jebakan umum. Jika Anda meng-hash string JSON, perbedaan kecil (urutan kunci, whitespace, format angka) bisa mengubah hash dan membuat verifikasi berisik. Gunakan bentuk kanonik: field dinormalisasi, jsonb, atau serialisasi konsisten lain.

Polanya lain yang merusak jejak yang bisa dipertahankan:

• Meng-hash hanya payload dan melewatkan konteks (timestamp, actor, object id, action).
• Menangkap perubahan hanya di aplikasi dan menganggap database selalu cocok.
• Menggunakan satu role database yang bisa menulis data bisnis dan juga mengubah riwayat audit.
• Mengizinkan NULL untuk prev_hash dalam rantai tanpa aturan yang jelas dan terdokumentasi.

Pemisahan tugas penting. Jika peran yang sama bisa insert event audit dan juga memodifikasinya, bukti manipulasi menjadi janji bukannya kontrol.

Daftar periksa cepat untuk jejak audit yang dapat dipertahankan

Jejak audit yang dapat dipertahankan harus sulit diubah dan mudah diverifikasi.

Mulai dengan kontrol akses: tabel audit harus bersifat append-only dalam praktik. Peran aplikasi harus insert (dan biasanya read), tetapi tidak update atau delete. Perubahan skema harus sangat terbatas.

Pastikan setiap baris menjawab pertanyaan penyelidik akan ajukan: siapa melakukannya, kapan terjadi (sisi-server), apa yang terjadi (nama event jelas plus operasi), apa yang terkena (nama entitas dan id), dan bagaimana terhubung (request/correlation id dan transaction id).

Lalu validasi lapisan integritas. Tes cepat adalah memutar ulang segmen dan memastikan setiap prev_hash cocok dengan hash baris sebelumnya, dan setiap hash tersimpan cocok dengan hasil perhitungan ulang.

Secara operasional, perlakukan verifikasi seperti job biasa:

• Jalankan cek integritas terjadwal dan simpan hasil pass/fail serta rentang yang diperiksa.
• Alert pada mismatch, celah, dan broken link.
• Simpan backup cukup lama untuk menutupi jendela retensi Anda, dan kunci retensi sehingga riwayat audit tidak bisa “dibersihkan” lebih awal.

Contoh: menemukan edit mencurigakan saat review kepatuhan

Kasus uji yang umum adalah sengketa pengembalian dana. Pelanggan mengklaim mereka disetujui untuk refund $250, tetapi sistem sekarang menunjukkan $25. Support bersikeras persetujuan benar, dan kepatuhan ingin jawaban.

Mulailah dengan mempersempit pencarian menggunakan correlation id (order id, ticket id, atau refund_request_id) dan jendela waktu. Tarik baris audit untuk correlation id itu dan letakkan di sekitar waktu persetujuan.

Anda mencari rangkaian event penuh: permintaan dibuat, refund disetujui, jumlah refund ditetapkan, dan setiap update berikutnya. Dengan desain yang mendeteksi manipulasi, Anda juga memeriksa apakah urutan tetap utuh.

Alur penyelidikan sederhana:

• Tarik semua baris audit untuk correlation id dalam urutan waktu.
• Hitung ulang hash setiap baris dari field yang tersimpan (termasuk prev_hash).
• Bandingkan hash yang dihitung dengan hash yang tersimpan.
• Identifikasi baris pertama yang berbeda dan lihat apakah baris berikutnya juga gagal.

Jika seseorang mengedit satu baris audit (misalnya mengubah jumlah dari 250 menjadi 25), hash baris itu tidak akan cocok lagi. Karena baris berikutnya menyertakan hash sebelumnya, ketidakcocokan biasanya merambat ke depan. Gelombang ini adalah sidik: menunjukkan catatan audit diubah setelah fakta.

Apa yang rantai bisa katakan: sebuah edit terjadi, di mana rantai pertama kali rusak, dan cakupan baris yang terpengaruh. Apa yang tidak bisa dikatakannya sendirian: siapa membuat edit, apa nilai asli jika ditimpa, atau apakah tabel lain juga diubah.

Langkah selanjutnya: gulirkan dengan aman dan jaga agar mudah dirawat

Perlakukan jejak audit seperti kontrol keamanan lainnya. Terapkan bertahap, buktikan bekerja, lalu perluas.

Mulailah dengan tindakan yang paling merugikan jika diperdebatkan: perubahan izin, payout, refund, ekspor data, dan override manual. Setelah itu tercakup, tambahkan event berisiko lebih rendah tanpa mengubah desain inti.

Tuliskan kontrak untuk event audit Anda: field mana yang direkam, apa arti setiap tipe event, bagaimana hash dihitung, dan bagaimana menjalankan verifikasi. Simpan dokumentasi itu bersama migrasi database Anda, dan buat prosedur verifikasi dapat diulang.

Latihan pemulihan penting karena penyelidikan sering dimulai dari backup, bukan sistem hidup. Secara berkala pulihkan ke database uji dan verifikasi rantai end-to-end. Jika Anda tidak bisa mereproduksi hasil verifikasi yang sama setelah pemulihan, bukti manipulasi Anda akan sulit dipertahankan.

Jika Anda sedang membangun alat internal dan alur admin dengan AppMaster (appmaster.io), menstandarkan penulisan event audit melalui proses sisi-server yang konsisten membantu menjaga skema event dan correlation id seragam antar fitur, sehingga verifikasi dan penyelidikan jauh lebih sederhana.

Jadwalkan waktu pemeliharaan untuk sistem ini. Jejak audit biasanya gagal diam-diam ketika tim mengirim fitur baru tapi lupa menambahkan event, memperbarui input hash, atau menjaga job verifikasi dan latihan pemulihan tetap berjalan.