POC #2 - FINDINGS
Verdict: GO - RLS is a viable tenant-isolation floor behind pgbouncer transaction pooling, conditional on carrying tenant context with SET LOCAL inside the transaction that runs the query. The naive session SET is catastrophic.
Setup: postgres 16, claim = 1,000,000 rows across 1,000 tenants, indexed on tenant_id. pgbouncer pool_mode=transaction. Harness: Go, through pgbouncer.
1. Isolation (the catastrophic-leak gate)
50,000 randomized checkouts @ 200 concurrency, default_pool_size=20 (heavy multiplexing):
| Pattern | correct | leaked | starved | errors |
|---|---|---|---|---|
unsafe - session SET + separate autocommit SELECT | 68 | 49,907 | 25 | 0 |
safe - BEGIN; SET LOCAL; SELECT; COMMIT | 50,000 | 0 | 0 | 0 |
Unsafe leaks on 99.8% of requests - two autocommit statements land on different server backends; the session GUC set by one client is read by another. Safe pattern: the whole tenant-scoped unit is one transaction → one backend → GUC scoped and auto-cleared. Zero cross-tenant rows. ✅ meets "0 leaks over ≥50k".
2. Sargability ✅
EXPLAIN (ANALYZE) - RLS predicate pushes into the index condition, same plan as a hand-written WHERE:
RLS : Bitmap Index Scan on idx_claim_tenant
Index Cond: (tenant_id = (NULLIF(current_setting('app.tenant_id',true),''))::integer)
Execution Time: 3.99 ms (Planning 1.23 ms)
no-RLS : Bitmap Index Scan on idx_claim_tenant
Index Cond: (tenant_id = 42)
Execution Time: 3.15 ms (Planning 0.55 ms)The RLS predicate costs ~0.8 ms and never seq-scans.
3. Overhead - depends entirely on round-trips, not RLS
overhead = full tenant-scoped read vs a bare no-RLS WHERE, p50/p95/p99 (ms):
| Pattern (RLS) | round-trips | c=50 Δp50 | c=200 Δp50 | c=200 Δp99 |
|---|---|---|---|---|
| 4 statements (BEGIN/SET LOCAL/SELECT/COMMIT, lib/pq) | 4 | +78% | +72% | +38% |
| pipelined batch (pgx, 1 flush) | 1 flush / 4 stmts | +67% | +68% | +36% |
SECURITY INVOKER function, 1 statement | 1 | +6.7% | +9.7% | −3.0% |
The RLS predicate is ~free (§2); the overhead is the transaction scaffolding needed to carry the GUC safely. Pushing set_config(local) server-side into a one-statement function (SELECT … FROM claim_list($tenant)) collapses it to <10% p50, ~0% at the tail ✅. Even the unoptimised 4-round-trip safe pattern adds only ~2 ms (c=50) to ~7 ms p99 (c=200) in absolute terms - inside the +50 ms whole-stack budget.
4. Build directives (mandatory for the design)
- Set tenant context with
SET LOCAL(orset_config(...,true)) inside the same transaction as the query. Never a sessionSET. SessionSETunder txn pooling leaks 99.8% of the time. - Prefer one statement. A
SECURITY INVOKERfunction that doesset_config(local)then the query keeps overhead <10%. Otherwise pipeline the batch. The plain 4-round-trip pattern is correct but pays ~2-7 ms. - Fail-closed predicate must be
NULLIF(current_setting('app.x',true),'')::int. A recycled pgbouncer backend presents an unset custom GUC as'', so a bare::intcast errors instead of returning zero rows. ENABLE+FORCE ROW LEVEL SECURITY; policyTO appuser; the app role must not own the table and must not haveBYPASSRLS.
5. Scorecard
| Threshold | Result |
|---|---|
| 0 cross-tenant rows over ≥50k checkouts @ ~200 | ✅ 0 / 50,000 (safe) |
| RLS overhead < +10% vs no-RLS same query | ✅ +6.7-9.7% p50 (function); predicate itself ~0.8 ms |
| index scan preserved with RLS predicate | ✅ same Bitmap Index Scan |
6. Not tested / watch-items for build
- Writes (
INSERT/UPDATE/DELETERLS withWITH CHECK) - onlySELECTcovered here. - Prepared statements under txn pooling - pgx worked, but production must use a pgbouncer (≥1.21) that supports protocol-level prepared statements, or set pgx exec mode appropriately; server-side named prepared statements don't survive backend switches.
- Multiple tables / joins - single table here; joins are POC #1's territory.
- Connection setup of the GUC namespace -
app.*custom GUCs need no config in PG≥9.2, confirmed.
Reproduce / source files
cd /root/rls-poc && docker compose up -d # then run the harness (see README)Source (raw):