Belajar SQL PostgreSQL - Roles, Security Management & Row Level Security (RLS)

#Pendahuluan

Selamat datang di episode 16 series Belajar SQL PostgreSQL! Sejauh ini kita fokus pada bagaimana membuat database bekerja cepat dan benar. Tapi ada satu pertanyaan yang belum kita jawab serius: siapa yang boleh mengakses apa? Aplikasi yang memperlakukan semua koneksi database dengan hak yang sama adalah aplikasi yang menunggu bencana — satu bug di aplikasi, satu kredensial bocor, dan seluruh data bisa diakses.

Keamanan database itu berlapis. Di episode 12 kita belajar menyembunyikan kolom dengan view. Hari ini kita menambah lapisan yang jauh lebih kuat: manajemen role untuk mengontrol siapa yang bisa melakukan apa, dan Row Level Security (RLS) untuk mengontrol baris mana yang bisa dilihat siapa — jantung keamanan aplikasi multi-tenant.

Di episode ini, kita akan membahas konsep roles yang menggabungkan user dan group, perintah CREATE ROLE, CREATE USER, GRANT, dan REVOKE, hierarki privilege pada database, schema, tabel, dan sequence, lalu Row Level Security dengan policy CREATE POLICY, dan ditutup dengan pengamanan file konfigurasi pg_hba.conf.

#Manajemen Hak Akses & Roles

#Konsep Roles: User dan Group Menjadi Satu

Di kebanyakan database, user dan group adalah dua konsep berbeda. Di PostgreSQL, keduanya digabung menjadi satu entitas: role. Sebuah role bisa bertindak sebagai user (yang login) sekaligus sebagai group (yang bisa memiliki member).

CREATE ROLE app_user LOGIN PASSWORD 'rahasia123';

CREATE ROLE read_only_role;

CREATE USER hanyalah alias dari CREATE ROLE ... LOGIN. Perbedaan LOGIN inilah yang menentukan apakah sebuah role bisa masuk ke database atau hanya menjadi wadah permission.

#GRANT dan REVOKE

Setelah role dibuat, beri permission dengan GRANT dan cabut dengan REVOKE:

GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;
 
REVOKE DELETE ON orders FROM app_user;

Perhatikan hierarkinya: role perlu CONNECT ke database, USAGE pada schema, lalu privilege pada tabel. Tabel dan view memakai privilege SELECT/INSERT/UPDATE/DELETE, sedangkan sequence (untuk auto-increment) memakai USAGE.

#Prinsip Least Privilege

Aturan emas keamanan: setiap role hanya diberi privilege yang benar-benar dibutuhkan. Role aplikasi tidak perlu CREATE TABLE; role analyst tidak perlu DELETE; dan SUPERUSER tidak pernah untuk koneksi aplikasi. Kredensial superuser hanya untuk DBA.

CREATE ROLE app_role;
GRANT CONNECT ON DATABASE shop TO app_role;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_role;
 
CREATE ROLE service_worker LOGIN PASSWORD 'rahasia456';
GRANT app_role TO service_worker;

#Row Level Security (RLS)

View dan GRANT mengontrol akses di level tabel/kolom. Tapi ada kebutuhan yang lebih halus: membatasi baris yang bisa dilihat/diubah. Inilah pekerjaan Row Level Security (RLS) — dan inilah fondasi keamanan aplikasi multi-tenant di mana satu database melayani banyak organisasi.

#Mengaktifkan RLS

Pertama, aktifkan RLS pada tabel, lalu set policy:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

ENABLE mengaktifkan RLS untuk koneksi normal. FORCE memperketatnya: bahkan owner tabel pun tunduk pada policy (tanpa FORCE, pemilik tabel bisa melewati RLS).

#Menulis RLS Policy

Policy menggunakan ekspresi USING yang menentukan baris mana yang boleh diakses. Contoh klasik multi-tenant: setiap user hanya melihat data tenant-nya sendiri.

SELECT set_config('app.current_tenant', 'tenant-a', FALSE);

CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.current_tenant'));

Sekarang setiap query SELECT * FROM orders hanya mengembalikan baris yang tenant_id-nya sama dengan nilai app.current_tenant pada sesi tersebut. User tenant lain tidak melihat satu baris pun — keamanan dijamin di level database, bukan di aplikasi.

CREATE POLICY tenant_insert ON orders
FOR INSERT
WITH CHECK (tenant_id = current_setting('app.current_tenant'));
 
CREATE POLICY tenant_update ON orders
FOR UPDATE
USING (tenant_id = current_setting('app.current_tenant'))
WITH CHECK (tenant_id = current_setting('app.current_tenant'));

WITH CHECK memvalidasi baris baru yang akan ditulis — user tidak bisa meng-insert atau mengubah data milik tenant lain.

#Cara Aplikasi Menetapkan Tenant

Pola implementasi yang umum: aplikasi membuka satu koneksi per request, menjalankan SELECT set_config('app.current_tenant', <tenant_dari_autentikasi>, FALSE), lalu semua query berjalan dengan isolasi otomatis. Karena set_config dengan argumen ketiga FALSE hanya berlaku untuk sesi saat ini, tidak ada risiko bocor antar sesi.

#Pengamanan File Konfigurasi

Keamanan role dan RLS tidak ada artinya jika autentikasi jaringan tidak dijaga. Dua file konfigurasi utama:

• pg_hba.conf (Host-Based Authentication): mengatur siapa yang boleh connect dari mana, dengan metode autentikasi apa.
• postgresql.conf: parameter utama server, termasuk jaringan dan logging.

local   all             all                                     trust
host    all             all             127.0.0.1/32            scram-sha-256
host    all             app_user        10.0.0.0/8              scram-sha-256
host    all             all             0.0.0.0/0               reject

Aturan dibaca dari atas ke bawah; yang pertama cocok menang. Konfigurasi contoh di atas: koneksi lokal (unix socket) memakai trust, koneksi loopback memakai password scram-sha-256, hanya user app_user yang boleh dari jaringan 10.0.0.0/8, dan semua koneksi lain ditolak. Pola reject terakhir ini sangat disarankan — jangan biarkan PostgreSQL terbuka tanpa autentikasi ke seluruh internet.

#Kesalahan Umum

#Penutup

Di episode 16 ini kita sudah memperkuat keamanan database: konsep roles yang menyatukan user dan group, perintah CREATE ROLE, GRANT, dan REVOKE, hierarki privilege dari database hingga sequence, Row Level Security dengan policy multi-tenant, serta pengamanan pg_hba.conf dan postgresql.conf.

Inti yang harus dibawa pulang:

• Role di PostgreSQL adalah user sekaligus group — gabungkan dengan GRANT role TO user.
• Prinsip least privilege: role hanya punya permission yang dibutuhkan.
• RLS mengisolasi baris per tenant di level database — pertahanan terakhir yang tidak bergantung pada kode aplikasi.
• USING menentukan baris yang boleh dilihat; WITH CHECK menentukan baris yang boleh ditulis.
• pg_hba.conf mengatur autentikasi jaringan — jangan pernah pakai trust untuk koneksi non-lokal.

Di episode 17 selanjutnya, kita masuk ke pencarian dan kecerdasan: Full-Text Search (FTS) & Vector Extension (pgvector) — mulai dari mengubah teks menjadi searchable vector dengan to_tsvector dan to_tsquery, ranking hasil dengan ts_rank, hingga menjadikan PostgreSQL vector database untuk AI embeddings dengan pgvector.