Anatomi Masalah: Log DoS Saat Lonjakan Data Insiden

Lonjakan volume log audit selama insiden keamanan sering kali memicu kerentanan sekunder berupa Denial of Service (DoS) internal pada basis data. Ketika tim insiden atau API forensik mengeksekusi filter pencarian berbasis teks atau rentang waktu yang longgar, PostgreSQL terpaksa memproses jutaan baris data baru. Hasilnya adalah saturasi CPU, disk spill akibat penggunaan memori tak terkontrol, serta connection exhaustion yang melumpuhkan layanan aplikasi utama.

Mengacu pada argumen Alex Gaynor dalam You Can't Bugfix Your Way Out of the Vulnpocalypse, memperbaiki query lambat secara reaktif setiap kali muncul insiden bukanlah strategi defensif yang memadai. Penanganan celah keamanan tipe keandalan dan sumber daya menuntut pendekatan sistemik: mengeliminasi kelas kegagalan secara menyeluruh melalui pembatasan blast radius dan penataan query plan hardening secara deterministik pada tingkat basis data.

Skema Data Audit Log Awal

Pertimbangkan tabel audit log tipikal yang menyimpan peristiwa autentikasi dan otorisasi berikut:

CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    severity VARCHAR(16) NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    payload JSONB
);

Optimalisasi Indexing: Composite dan Partial Index

Pola pencarian audit log hampir selalu difilter berdasarkan identitas tenant, rentang waktu, dan tingkat keparahan. Index tunggal pada created_at tidak memadai karena database tetap harus membaca ribuan heap pages untuk memfilter baris milik tenant terkait.

1. Composite B-Tree Indexing

Untuk mendukung query filter multikolom secara deterministik, letakkan kolom selektif beroperator kesetaraan (=) di urutan pertama, disusul oleh kolom beroperator rentang (>=, <=):

CREATE INDEX idx_audit_logs_tenant_created_at 
ON audit_logs (tenant_id, created_at DESC, id DESC);

Menambahkan kolom id di urutan akhir mengantisipasi timestamps duplikat dan menjamin kepastian urutan eksekusi pada pagination tanpa sorting tambahan di memori.

2. Partial Index untuk Log Kritis

Sebagian besar log audit berstatus non-kritis (misalnya INFO). Indeks penuh yang mencakup semua severity menghabiskan buffer cache. Gunakan partial index untuk mempercepat pencarian peringatan kritis yang sering dipantau saat terjadi serangan aktif:

CREATE INDEX idx_audit_logs_unresolved_alerts 
ON audit_logs (tenant_id, created_at DESC)
WHERE severity IN ('CRITICAL', 'ALERT');

Partial index ini memiliki ukuran fisik jauh lebih kecil, sepenuhnya muat dalam RAM, dan mengurangi frekuensi I/O acak selama analisis investigasi.

Mengganti Skema OFFSET dengan Keyset Pagination

Pendekatan paginasi konvensional menggunakan OFFSET memiliki kompleksitas waktu linear O(N). Saat tim forensik membuka halaman audit log yang jauh (misalnya halaman 5.000), basis data membaca dan membuang ratusan ribu tuple:

-- ANTI-PATTERN: Membaca puluhan ribu row sebelum dibuang
SELECT id, created_at, event_type 
FROM audit_logs 
WHERE tenant_id = 'c0a80101-0000-0000-0000-000000000001'
ORDER BY created_at DESC, id DESC 
LIMIT 50 OFFSET 50000;

Ganti dengan keyset pagination (cursor-based pagination). Pendekatan ini mengeksploitasi composite index secara optimal dengan kompleksitas konstan O(1):

-- PATTERN: Pencarian deterministik menggunakan boundary condition
SELECT id, created_at, event_type 
FROM audit_logs 
WHERE tenant_id = 'c0a80101-0000-0000-0000-000000000001'
  AND (created_at, id) < ('2026-03-30T10:15:30Z', 8540192)
ORDER BY created_at DESC, id DESC 
LIMIT 50;

Hardening Eksekusi Query dan Pembatasan Blast Radius

Index yang dirancang dengan benar tetap dapat dilewati oleh query investigasi yang tidak terduga atau eksploitasi parameter API. Mitigasi struktural dilakukan dengan membatasi durasi eksekusi dan memori kerja per sesi.

1. Penerapan statement_timeout

Cegah query liar mengunci koneksi dan saturasi I/O dengan menyetel batas waktu maksimal di level peranan (role) API atau pengguna dashboard:

-- Membatasi eksekusi maksimal 3 detik untuk role query publik/dashboard
ALTER ROLE readonly_auditor SET statement_timeout = '3000ms';

2. Pengendalian work_mem

Alokasi work_mem yang terlalu tinggi dapat menyebabkan Out of Memory (OOM) killer mematikan PostgreSQL ketika banyak query analitik dijalankan secara paralel. Sebaliknya, alokasi terlalu rendah memaksa operasi sorting meluap (spill) ke disk.

Tetapkan batas aman default secara global, lalu naikkan secara spesifik hanya pada sesi investigasi berizin khusus:

-- Global default di postgresql.conf yang konservatif
-- work_mem = 16MB

-- Eksplisit pada sesi analitik forensik terkendali
BEGIN;
SET LOCAL work_mem = '64MB';
SELECT event_type, count(*)
FROM audit_logs
WHERE created_at >= NOW() - INTERVAL '6 hours'
GROUP BY event_type;
COMMIT;

Verifikasi Query Plan

Validasi bahwa query planner tidak lagi menggunakan Seq Scan pada dataset besar dengan perintah EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, event_type
FROM audit_logs
WHERE tenant_id = 'c0a80101-0000-0000-0000-000000000001'
  AND created_at >= NOW() - INTERVAL '1 hour'
ORDER BY created_at DESC, id DESC
LIMIT 50;

Pastikan eksekusi menghasilkan Index Scan atau Bitmap Index Scan tanpa indikasi Batches: Spill to disk pada node pemilahan.

Kesimpulan

Menangani kerentanan Log DoS bukan sekadar urusan menambal satu query yang lambat. Dengan membangun index composite yang selaras dengan filter akses, memanfaatkan partial index, mengimplementasikan keyset pagination, serta menegakkan batasan statement_timeout dan work_mem, sistem basis data terlindungi secara arsitektural dari kegagalan operasional di tengah insiden keamanan.