RDBMS dengan PostgreSQL

Aplikasi Node.js yang sudah kita jalankan sebagai service systemd sejak Bab 15, lalu kita tempatkan di balik reverse proxy app.example.local pada Bab 16, sejauh ini belum benar-benar menyimpan data apa pun. Begitu proses node di-restart atau server reboot, seluruh state yang sempat ada di memori langsung hilang tanpa bekas. Pengembang yang mengembangkan aplikasi semacam ini cepat atau lambat pasti membutuhkan tempat penyimpanan data yang persisten, terstruktur, dan dapat diandalkan meski server dinyalakan ulang berkali-kali, kebutuhan yang dijawab oleh RDBMS (Relational Database Management System).

Bab ini membuka Bagian V dengan PostgreSQL, RDBMS open source yang sering disebut komunitasnya sendiri sebagai "the world's most advanced open source database". Reputasi itu bukan sekadar slogan pemasaran; PostgreSQL dikenal luas karena kepatuhannya yang ketat terhadap standar SQL, dukungan ACID (Atomicity, Consistency, Isolation, Durability) penuh untuk menjaga integritas data, serta ekosistem extension yang sangat kaya. Kita akan mulai dari instalasi PostgreSQL 18 di Ubuntu Server 26.04, memahami dua file konfigurasi paling penting yaitu postgresql.conf dan pg_hba.conf, mempraktikkan pembuatan database beserta role dan privilege dasarnya, lalu ditutup dengan mengaktifkan koneksi remote yang aman dari luar server itu sendiri.

18.1 Instalasi PostgreSQL 18

Rilis PostgreSQL 18 pada September 2025 membawa sejumlah peningkatan signifikan di level engine, di antaranya subsistem Asynchronous I/O (AIO) yang mempercepat operasi baca dari disk, serta fungsi uuidv7() bawaan untuk menghasilkan UUID yang urut berdasarkan waktu, berguna sebagai primary key yang lebih ramah terhadap performa index dibanding UUID v4 acak. Versi inilah yang menjadi paket default PostgreSQL di repository resmi Ubuntu 26.04 LTS, sehingga instalasinya dapat memakai APT biasa tanpa perlu menambah repository pihak ketiga milik PostgreSQL Global Development Group (PGDG), konsisten dengan filosofi instalasi paket yang sudah kita jalani sejak Bab 6.

18.1.1 Arsitektur Cluster PostgreSQL di Ubuntu

Ubuntu membungkus PostgreSQL melalui paket postgresql-common, sebuah lapisan manajemen yang memungkinkan beberapa versi major PostgreSQL terpasang berdampingan di satu server tanpa saling bentrok, mirip semangat multi-versi yang sudah kita kenal dari NVM dan fnm di Bab 15 untuk Node.js. Setiap instance PostgreSQL yang berjalan di Ubuntu disebut cluster, istilah yang di sini tidak ada hubungannya dengan cluster multi-server seperti pada Kubernetes, melainkan merujuk pada satu kumpulan database yang dikelola oleh satu proses postgres yang mendengarkan di satu port tertentu. Cluster default yang dibuat setelah instalasi diberi nama main, dengan data disimpan di /var/lib/postgresql/18/main dan seluruh file konfigurasinya, termasuk postgresql.conf dan pg_hba.conf yang akan kita bahas di Bagian 18.2, ditaruh terpisah di /etc/postgresql/18/main/. Pemisahan konfigurasi dari data ini berbeda dari kebiasaan upstream PostgreSQL di distro lain yang biasanya menaruh keduanya dalam satu direktori data, serta menjadi salah satu kekhasan packaging Debian/Ubuntu yang penting diketahui Sysadmin sejak awal.

18.1.2 Instalasi Paket dan Verifikasi Service

Paket postgresql di Ubuntu adalah meta-package yang otomatis menarik versi major terbaru yang tersedia, sedangkan postgresql-contrib menambahkan sejumlah extension tambahan yang sudah teruji dan sering dipakai di lapangan, seperti pgcrypto untuk fungsi enkripsi dan pg_stat_statements untuk analisis performa query.

Langkah Praktik

1. Perbarui daftar paket, lalu pasang PostgreSQL beserta paket contrib-nya.

   sudo apt update
   sudo apt install postgresql postgresql-contrib

2. Instalasi otomatis membuat cluster main, menjalankan service-nya, dan mengaktifkannya saat boot. Periksa statusnya melalui systemd.

   sudo systemctl status postgresql

3. Lihat daftar cluster PostgreSQL yang terpasang di server, termasuk versi, port, dan statusnya.

   pg_lsclusters

4. Konfirmasi versi PostgreSQL yang benar-benar berjalan.

   psql --version

Verifikasi dan Troubleshooting

• Output pg_lsclusters yang normal menampilkan satu baris dengan versi 18, nama cluster main, port 5432, dan status online. Jika statusnya down, jalankan sudo pg_ctlcluster 18 main start untuk menyalakannya secara manual.
• Berbeda dari MySQL atau MariaDB yang akan kita bahas di Bab 19 dan 20, instalasi PostgreSQL di Ubuntu tidak pernah meminta password root database saat proses apt install berjalan. Otentikasi awal justru diatur melalui mekanisme peer authentication yang akan kita bahas di Bagian 18.2.1.
• Nomor versi minor yang tampil di psql --version dapat berbeda tergantung kapan repository Ubuntu terakhir disinkronkan, sama seperti catatan versi PHP di Bagian 14.1.1 dan Certbot di Bagian 17.2.2.

18.2 Konfigurasi Akses: postgresql.conf dan pg_hba.conf

PostgreSQL memisahkan pengaturan perilaku engine dari pengaturan siapa yang boleh terhubung ke dalam dua file berbeda. postgresql.conf mengatur parameter runtime seperti port yang dipakai, alamat jaringan yang didengarkan, hingga alokasi memori, sedangkan pg_hba.conf (host-based authentication) menentukan aturan siapa boleh terhubung dari mana, ke database apa, dan melalui metode otentikasi seperti apa. Memahami perbedaan dan hubungan keduanya menjadi kunci sebelum kita membuka koneksi remote di Bagian 18.4, karena kesalahan paling umum yang ditemui Sysadmin baru justru berasal dari mengira satu file saja sudah cukup diedit padahal keduanya perlu disesuaikan bersamaan.

18.2.1 Memeriksa Aturan pg_hba.conf Bawaan

Aturan di dalam pg_hba.conf dievaluasi baris demi baris dari atas ke bawah, dan PostgreSQL berhenti begitu menemukan baris pertama yang cocok dengan tipe koneksi, database, user, dan alamat asal request, lalu menerapkan metode otentikasi pada baris tersebut apa adanya. Urutan baris oleh karena itu sangat menentukan, aturan yang lebih spesifik harus ditempatkan lebih dulu dibanding aturan yang lebih umum.

Langkah Praktik

1. Buka file pg_hba.conf bawaan cluster main.

   sudo nano /etc/postgresql/18/main/pg_hba.conf

   Baris aktif (bukan komentar) yang relevan biasanya terlihat seperti berikut.

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   local   all             postgres                                peer
   local   all             all                                     peer
   host    all             all             127.0.0.1/32            scram-sha-256
   host    all             all             ::1/128                 scram-sha-256

2. Coba masuk ke psql melalui Unix socket sebagai role sistem postgres, memakai sudo -u postgres untuk berpindah identitas OS terlebih dahulu.

   sudo -u postgres psql

3. Ketik \q untuk keluar, lalu coba cara kedua, terhubung melalui TCP ke alamat loopback dengan role yang sama.

   psql -h 127.0.0.1 -U postgres

Percobaan pertama berhasil masuk tanpa diminta password sama sekali, sedangkan percobaan kedua langsung menampilkan prompt Password for user postgres:. Inilah pg_hba.conf bekerja persis sesuai isinya: baris local ... peer cocok untuk koneksi melalui Unix socket dan memakai peer authentication, metode yang mengizinkan login tanpa password selama nama user OS di sistem sama persis dengan nama role PostgreSQL yang dituju. Baris host ... 127.0.0.1/32 ... scram-sha-256 cocok untuk koneksi TCP ke loopback dan mewajibkan password, diverifikasi melalui SCRAM-SHA-256, metode hashing password yang menjadi default PostgreSQL sejak versi 14, jauh lebih tahan terhadap serangan replay dibanding metode md5 lama yang masih sering dijumpai di tutorial usang.

Kedua metode di atas bukan satu-satunya opsi yang tersedia di kolom METHOD. Tabel berikut merangkum metode yang paling sering ditemui Sysadmin di lapangan, termasuk dua metode yang belum tampil di file bawaan ini.

Verifikasi dan Troubleshooting

• Percobaan kedua di atas akan gagal dengan pesan FATAL: password authentication failed karena role postgres tidak pernah diberi password melalui instalasi APT. Kegagalan ini normal dan sengaja diperlihatkan untuk membuktikan cara kerja pg_hba.conf, bukan langkah yang perlu diperbaiki di titik ini.
• Jika muncul pesan Peer authentication failed for user "postgres" saat menjalankan psql tanpa sudo -u postgres terlebih dahulu, hal itu berarti nama user OS yang sedang login (misalnya deploy, mengikuti user yang kita buat sejak Bab 3) tidak sama dengan nama role yang dituju, persis mekanisme peer authentication yang baru saja dijelaskan.
• Perintah \du di dalam psql menampilkan daftar seluruh role yang ada di server beserta atribut masing-masing, berguna sebagai referensi cepat sepanjang bab ini.

18.3 Membuat Role, Database, dan Privilege Dasar

Skenario di bagian ini mengikuti kebutuhan Developer yang sedang menyiapkan backend baru: sebuah database bernama webapp_db untuk menyimpan data aplikasi, beserta satu role khusus bernama webapp_user yang dipakai aplikasi untuk terhubung, terpisah dari role superuser postgres yang seharusnya hanya dipegang Sysadmin. Memisahkan role aplikasi dari role administratif seperti ini adalah praktik dasar least privilege yang akan kita perdalam lagi di Bab 34, sehingga kredensial yang bocor dari sisi aplikasi tidak otomatis memberi Attacker akses penuh ke seluruh server database.

18.3.1 Membuat Role dan Database dengan Ownership yang Tepat

PostgreSQL menyebut konsep user dan group sebagai satu entitas tunggal bernama role. Sebuah role bisa login langsung layaknya user (jika diberi atribut LOGIN) atau berfungsi sebagai group yang mewadahi role lain, tanpa perlu dua sistem terpisah seperti pada beberapa RDBMS lain.

Langkah Praktik

1. Masuk ke psql sebagai role postgres melalui Unix socket, mengikuti cara yang sudah terbukti berhasil di Bagian 18.2.1.

   sudo -u postgres psql

2. Buat role baru untuk aplikasi, lengkap dengan atribut LOGIN dan password. Ganti nilai password contoh ini dengan password kuat milik sendiri sebelum dipraktikkan di server sungguhan.

   CREATE ROLE webapp_user WITH LOGIN PASSWORD 'S4ngatRahasia!2026';

3. Buat database baru, jadikan webapp_user sebagai owner-nya langsung saat pembuatan.

   CREATE DATABASE webapp_db OWNER webapp_user;

4. Keluar dari sesi postgres, lalu uji login dengan role baru melalui TCP ke loopback, memanfaatkan rule scram-sha-256 yang sudah ada secara default sejak Bagian 18.2.1.

   \q
   psql -h 127.0.0.1 -U webapp_user -d webapp_db

Verifikasi dan Troubleshooting

• Login yang berhasil ditandai prompt berubah menjadi webapp_db=>. Jalankan \conninfo di dalam psql untuk memastikan sesi benar-benar terhubung sebagai webapp_user ke database webapp_db.
• Pesan FATAL: database "webapp_db" does not exist biasanya menandakan typo nama database di flag -d, sedangkan FATAL: role "webapp_user" does not exist menandakan perintah CREATE ROLE di langkah sebelumnya belum benar-benar tereksekusi, cek kembali dengan \du.

18.3.2 Memahami Privilege Dasar dan Perubahan Schema Public sejak PostgreSQL 15

Sebelum PostgreSQL 15, schema public di setiap database baru secara default memberi privilege CREATE kepada seluruh role melalui role semu bernama PUBLIC, artinya siapa pun yang berhasil login ke database tersebut, bahkan melalui role dengan privilege minim, bisa membuat table sembarangan di schema itu. Perilaku ini dianggap terlalu longgar dari sisi keamanan, sehingga sejak PostgreSQL 15 privilege CREATE pada schema public tidak lagi otomatis diberikan ke PUBLIC. Sebagai gantinya, database baru memiliki schema public yang dimiliki role semu pg_database_owner, yang secara otomatis mewakili siapa pun yang menjadi owner database tersebut. Karena langkah 18.3.1 di atas sudah membuat webapp_db dengan OWNER webapp_user sejak awal, webapp_user otomatis mewarisi hak penuh atas schema public di database itu tanpa perlu perintah GRANT tambahan apa pun, pola yang menjadi cara paling bersih untuk menghindari kerumitan privilege schema di PostgreSQL versi modern.

Buktikan langsung dengan mencoba membuat table sederhana memakai sesi webapp_user yang masih terbuka dari langkah sebelumnya.

CREATE TABLE notes (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO notes (content) VALUES ('Database webapp_db siap dipakai');
SELECT * FROM notes;

Ketiga perintah di atas harus berjalan mulus tanpa satu pun pesan permission denied. Untuk kebutuhan yang lebih kompleks, misalnya role tambahan yang hanya boleh membaca data tanpa bisa mengubahnya, seperti akun yang dipakai tool reporting atau dashboard BI, privilege granular bisa diberikan langsung ke table tertentu tanpa menyentuh ownership database sama sekali.

CREATE ROLE webapp_readonly WITH LOGIN PASSWORD 'B4caSaja!2026';
GRANT CONNECT ON DATABASE webapp_db TO webapp_readonly;
GRANT USAGE ON SCHEMA public TO webapp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO webapp_readonly;

Tiga baris GRANT ini masing-masing mewakili lapisan privilege berbeda: CONNECT mengizinkan role sekadar membuka koneksi ke database, USAGE pada schema mengizinkan role "melihat ke dalam" schema public (tanpa itu pun sudah cukup untuk memblokir akses meski privilege table sudah diberikan), dan SELECT pada table baru benar-benar mengizinkan pembacaan data. Ketiganya harus ada bersamaan, hilang salah satu saja koneksi tetap akan ditolak atau query akan gagal dengan permission denied.

Verifikasi dan Troubleshooting

• Perintah \dp notes di dalam psql menampilkan tabel access privileges untuk table notes, cara cepat mengaudit siapa punya privilege apa tanpa perlu membaca ulang seluruh riwayat perintah GRANT.
• Privilege GRANT ... ON ALL TABLES IN SCHEMA public hanya berlaku untuk table yang sudah ada saat perintah dijalankan. Table baru yang dibuat setelahnya tidak otomatis ikut ter-cover, kecuali dikombinasikan dengan ALTER DEFAULT PRIVILEGES, topik lanjutan yang baik untuk digali lebih jauh saat aplikasi produksi mulai punya banyak table.

18.4 Koneksi Remote yang Aman

Sejauh ini seluruh koneksi yang kita uji coba berasal dari server itu sendiri, baik melalui Unix socket maupun melalui alamat loopback 127.0.0.1. Di dunia nyata, database sering diakses dari mesin lain, misalnya laptop Developer yang sedang menulis kode aplikasi, atau server aplikasi terpisah yang memanggil database melalui jaringan internal. Membuka akses semacam ini butuh tiga perubahan sekaligus: PostgreSQL harus mau mendengarkan di interface jaringan (bukan cuma loopback), pg_hba.conf harus punya rule untuk alamat asal yang dimaksud, dan firewall di level OS harus mengizinkan traffic ke port database.

18.4.1 Mengaktifkan listen_addresses dan Menambah Rule pg_hba.conf

Langkah Praktik

1. Buka postgresql.conf, cari directive listen_addresses yang secara default bernilai localhost.

   sudo nano /etc/postgresql/18/main/postgresql.conf

   Ubah baris tersebut agar PostgreSQL mendengarkan di seluruh interface jaringan yang dimiliki server.

   listen_addresses = '*'

2. Directive listen_addresses tergolong parameter yang hanya dibaca saat proses PostgreSQL pertama kali start, berbeda dari rule pg_hba.conf yang cukup di-reload. Restart penuh cluster-nya agar perubahan ini benar-benar terpakai.

   sudo systemctl restart postgresql

3. Tambahkan rule baru di pg_hba.conf, mengizinkan koneksi dari seluruh network lab 192.168.1.0/24 yang sudah kita pakai konsisten sejak Bab 8, khusus ke database webapp_db dengan role webapp_user saja, bukan mengizinkan seluruh role ke seluruh database.

   sudo nano /etc/postgresql/18/main/pg_hba.conf

   host    webapp_db       webapp_user     192.168.1.0/24          scram-sha-256

   Tempatkan baris ini sebelum baris host all all ... yang sudah ada jika rule tersebut turut mencakup network yang sama, mengikuti prinsip evaluasi top-down yang sudah dibahas di Bagian 18.2.1.
4. Rule pg_hba.conf cukup di-reload tanpa restart penuh, sehingga koneksi yang sedang berjalan tidak terputus.

   sudo systemctl reload postgresql

Verifikasi dan Troubleshooting

• Pastikan PostgreSQL benar-benar mendengarkan di semua interface, bukan cuma loopback.

  ss -tlnp | grep 5432

  Output yang benar menampilkan alamat 0.0.0.0:5432, bukan 127.0.0.1:5432.
• Membuka listen_addresses ke * lalu mengizinkan rule pg_hba.conf dengan cakupan network sesempit mungkin, seperti 192.168.1.0/24 alih-alih 0.0.0.0/0, adalah kombinasi yang jauh lebih aman dibanding mengekspos database ke seluruh internet. Database production hampir tidak pernah punya alasan menerima koneksi langsung dari internet publik, kesalahan konfigurasi yang berulang kali jadi penyebab kebocoran data di berbagai insiden nyata.

18.4.2 Membuka Firewall UFW untuk Port PostgreSQL

PostgreSQL default berjalan di port 5432. Sama seperti port 443 yang baru kita buka khusus untuk kebutuhan HTTPS di Bagian 17.3.2, port database ini juga perlu dibuka secara eksplisit di UFW, dibatasi hanya untuk sumber dari network lab, bukan dibuka bebas untuk semua orang.

Langkah Praktik

1. Tambahkan rule UFW yang mengizinkan traffic ke port 5432 khusus dari network 192.168.1.0/24.

   sudo ufw allow from 192.168.1.0/24 to any port 5432 proto tcp

2. Pastikan rule tersebut benar-benar tercatat.

   sudo ufw status

Verifikasi dan Troubleshooting

• Output ufw status harus menampilkan baris 5432/tcp dengan action ALLOW dan sumber 192.168.1.0/24, bukan Anywhere.
• Jika UFW belum aktif sama sekali di server ini, aktifkan dulu dengan sudo ufw enable, tetapi pastikan rule SSH dari Bab 3 sudah ada lebih dulu supaya sesi remote yang sedang berjalan tidak ikut terputus.

18.4.3 Menguji Koneksi dari Client Lain di Jaringan

Langkah Praktik

1. Dari komputer lain di jaringan lab yang sama, misalnya laptop Developer, pasang PostgreSQL client saja tanpa server penuhnya.

   sudo apt install postgresql-client

2. Coba terhubung ke server database melalui IP 192.168.1.20, memakai role dan database yang sudah dibuat di Bagian 18.3.1.

   psql -h 192.168.1.20 -U webapp_user -d webapp_db

Verifikasi dan Troubleshooting

• Koneksi yang berhasil akan meminta password lalu menampilkan prompt webapp_db=>, identik dengan pengujian melalui loopback di Bagian 18.3.1, hanya kali ini benar-benar melewati jaringan.
• Pesan psql: error: connection to server ... failed: Connection refused menandakan PostgreSQL belum mendengarkan di interface jaringan atau firewall masih memblokir port 5432, kembali periksa Bagian 18.4.1 dan 18.4.2.
• Pesan FATAL: no pg_hba.conf entry for host "192.168.1.x", user "webapp_user", database "webapp_db" menandakan rule di pg_hba.conf belum cocok, entah karena subnet yang dituliskan salah atau baris tersebut tertimpa baris lain yang posisinya lebih atas.
• Percobaan role webapp_readonly dari Bagian 18.3.2 ke database webapp_db melalui jaringan akan gagal dengan pesan pg_hba.conf yang sama, karena rule di Bagian 18.4.1 sengaja hanya mencakup webapp_user. Menambah role lain ke akses remote berarti menambah baris pg_hba.conf baru secara sadar, bukan melebarkan rule yang sudah ada begitu saja.
• Koneksi melalui jaringan lab internal seperti ini masih berupa traffic TCP polos tanpa enkripsi, cukup aman selama network-nya benar-benar tepercaya. Untuk database yang diakses melalui jaringan yang lebih terbuka atau lintas data center, PostgreSQL mendukung koneksi TLS melalui directive ssl = on beserta pasangan sertifikat dan private key, memakai prinsip yang persis sama dengan TLS di Nginx dan Apache yang sudah kita bahas mendalam di Bab 17.

Sampai di sini, server sudah menjalankan PostgreSQL 18 lengkap dengan database webapp_db yang dimiliki role aplikasi tersendiri, terpisah dari role administratif postgres, dan bisa diakses dengan aman dari jaringan lab melalui rule pg_hba.conf serta firewall yang dibatasi ketat. Bab 19 melanjutkan Bagian V dengan RDBMS kedua, MySQL, membandingkan langsung cara kerjanya dengan PostgreSQL termasuk perbedaan filosofi otentikasi dan manajemen privilege antara keduanya.