Postgres Row Level Security (RLS) pattern

Multi-tenant SaaS gampang banget kena leak data — query lupa WHERE tenant_id = ? di satu route, semua data bocor. RLS pindah filter ke DB level. App tinggal SET current setting tenant, Postgres handle sisanya. Snippet ini setup tenant isolation untuk SaaS koperasi simpan pinjam.

Kode

-- ==========================================
-- Schema multi-tenant
-- ==========================================
CREATE TABLE koperasi (
    id   BIGSERIAL PRIMARY KEY,
    nama TEXT NOT NULL,
    kota TEXT NOT NULL
);

CREATE TABLE anggota (
    id           BIGSERIAL PRIMARY KEY,
    koperasi_id  BIGINT NOT NULL REFERENCES koperasi(id),
    nama         TEXT NOT NULL,
    nik          TEXT NOT NULL,
    saldo_simpanan BIGINT NOT NULL DEFAULT 0
);

CREATE INDEX idx_anggota_koperasi ON anggota(koperasi_id);

CREATE TABLE pinjaman (
    id          BIGSERIAL PRIMARY KEY,
    koperasi_id BIGINT NOT NULL REFERENCES koperasi(id),
    anggota_id  BIGINT NOT NULL REFERENCES anggota(id),
    pokok       BIGINT NOT NULL,
    status      TEXT NOT NULL
);

CREATE INDEX idx_pinjaman_koperasi ON pinjaman(koperasi_id);

-- ==========================================
-- Setup RLS — enable + policy per table
-- ==========================================
ALTER TABLE anggota ENABLE ROW LEVEL SECURITY;
ALTER TABLE pinjaman ENABLE ROW LEVEL SECURITY;

-- FORCE: bahkan owner table harus respect policy
-- Tanpa ini, superuser bypass RLS
ALTER TABLE anggota FORCE ROW LEVEL SECURITY;
ALTER TABLE pinjaman FORCE ROW LEVEL SECURITY;

-- Policy: hanya tampilkan row yang koperasi_id-nya match session setting
CREATE POLICY tenant_isolation_anggota ON anggota
    FOR ALL
    USING (koperasi_id = current_setting('app.koperasi_id')::BIGINT)
    WITH CHECK (koperasi_id = current_setting('app.koperasi_id')::BIGINT);

CREATE POLICY tenant_isolation_pinjaman ON pinjaman
    FOR ALL
    USING (koperasi_id = current_setting('app.koperasi_id')::BIGINT)
    WITH CHECK (koperasi_id = current_setting('app.koperasi_id')::BIGINT);

-- USING = filter SELECT/UPDATE/DELETE
-- WITH CHECK = validasi INSERT/UPDATE — gak boleh insert row tenant lain

-- ==========================================
-- Role + grant — pakai role berbeda dari superuser
-- ==========================================
CREATE ROLE app_user;
GRANT CONNECT ON DATABASE koperasi_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON anggota, pinjaman, koperasi TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Membuat user concrete dari role
CREATE USER api_koperasi WITH PASSWORD 'secret' IN ROLE app_user;

-- ==========================================
-- Setup tenant context per session
-- ==========================================
-- Sebelum query apa pun, set tenant ID dari user yang authenticated
-- SET LOCAL — apply hanya untuk transaction ini (transaction-scoped)
BEGIN;
SET LOCAL app.koperasi_id = '12';

-- Sekarang query apa pun otomatis ter-filter ke koperasi_id=12
SELECT id, nama, saldo_simpanan FROM anggota;
-- Postgres rewrite jadi:
-- SELECT id, nama, saldo_simpanan FROM anggota
-- WHERE koperasi_id = 12;

-- Insert: WITH CHECK validasi
INSERT INTO anggota (koperasi_id, nama, nik, saldo_simpanan)
VALUES (12, 'Asti Larasati', '3201010101010001', 500000);  -- OK

INSERT INTO anggota (koperasi_id, nama, nik, saldo_simpanan)
VALUES (99, 'Hacker', '3201010101010002', 0);  -- ERROR — koperasi_id != 12
-- ERROR: new row violates row-level security policy

COMMIT;

-- ==========================================
-- Pattern: admin role bypass RLS
-- ==========================================
CREATE ROLE app_admin;
GRANT app_user TO app_admin;

-- BYPASSRLS attribute — admin bisa lihat semua tenant
ALTER ROLE app_admin BYPASSRLS;

-- Atau pakai policy yang allow superadmin
CREATE POLICY admin_full_access ON anggota
    FOR ALL
    TO app_admin
    USING (true);

-- ==========================================
-- Pattern: read-only public table tanpa RLS
-- ==========================================
-- Tabel master (kategori, kota, dll) shared antar tenant
CREATE TABLE kota (
    id   BIGSERIAL PRIMARY KEY,
    nama TEXT NOT NULL UNIQUE
);

-- Tidak enable RLS — accessible semua user
INSERT INTO kota (nama) VALUES ('Jakarta'), ('Surabaya'), ('Tangerang');

Pemakaian

# Python pakai psycopg
import psycopg
from contextlib import contextmanager


@contextmanager
def tenant_session(conn: psycopg.Connection, koperasi_id: int):
    """Context manager — set tenant lalu auto-cleanup."""
    with conn.transaction():
        with conn.cursor() as cur:
            # SET LOCAL — scoped ke transaction
            cur.execute("SET LOCAL app.koperasi_id = %s", (str(koperasi_id),))
        yield conn


# Pemakaian di handler API
def get_anggota(conn, request_user):
    with tenant_session(conn, request_user.koperasi_id) as c:
        with c.cursor() as cur:
            # Tidak perlu WHERE koperasi_id — RLS otomatis filter
            cur.execute("SELECT id, nama, saldo_simpanan FROM anggota")
            return cur.fetchall()

// Go pakai pgx
import (
    "context"
    "github.com/jackc/pgx/v5"
)

func withTenant(ctx context.Context, db *pgx.Conn, koperasiID int64, fn func(pgx.Tx) error) error {
    tx, err := db.BeginTx(ctx, pgx.TxOptions{})
    if err != nil {
        return err
    }
    defer tx.Rollback(ctx)

    // SET LOCAL untuk scoping per-transaction
    if _, err := tx.Exec(ctx,
        fmt.Sprintf("SET LOCAL app.koperasi_id = '%d'", koperasiID),
    ); err != nil {
        return err
    }

    if err := fn(tx); err != nil {
        return err
    }
    return tx.Commit(ctx)
}

// Pemakaian
err := withTenant(ctx, db, user.KoperasiID, func(tx pgx.Tx) error {
    rows, err := tx.Query(ctx, "SELECT id, nama FROM anggota")
    // ...
    return nil
})

-- Test isolation
SET app.koperasi_id = '12';
SELECT COUNT(*) FROM anggota;  -- 47

SET app.koperasi_id = '99';
SELECT COUNT(*) FROM anggota;  -- 0 (kosong) atau jumlah yang berbeda

-- Test policy violation
SET app.koperasi_id = '12';
UPDATE anggota SET nama = 'Hack' WHERE koperasi_id = 99;
-- 0 rows updated (tidak match policy)

Kapan dipakai

• SaaS multi-tenant dengan customer isolation strict.
• Aplikasi healthcare / finansial yang compliance (HIPAA, OJK).
• App enterprise yang punya role-based data access.
• Audit trail — RLS pastikan user tidak bisa update row yang bukan miliknya.

Catatan

• FORCE ROW LEVEL SECURITY wajib — tanpa ini, table owner (biasanya postgres user) bypass policy. Backup tool yang pakai owner role bisa leak data.
• SET LOCAL vs SET — LOCAL transaction-scoped, aman untuk connection pooling. SET tanpa LOCAL persist sampai connection ditutup, riskan leak ke request berikutnya di pool.
• Index harus support policy — WHERE koperasi_id = ... butuh index pada koperasi_id. Tanpa itu, RLS bikin Seq Scan per query.
• Application bug masih possible — kalau set wrong tenant ID, RLS protect, tapi user lihat data tenant lain. Wrap setup tenant di middleware single-point.
• NULL handling — current_setting('app.koperasi_id', true) return NULL kalau tidak di-set. Policy koperasi_id = NULL::BIGINT selalu false → tidak ada row bocor. Tapi tetap perlu validasi di app: jangan lupa set tenant.
• BYPASSRLS untuk admin — gunakan dengan hati-hati. Log akses admin terpisah.

RLS bukan pengganti app-level authorization. RLS protect data dari leak akibat bug query, tapi tidak tahu mana action yang boleh user lakukan secara semantik (delete vs read). Combine layer.