RLS policies · DB security deep-dive
RLS policies detalle
Postgres row-level security deep-dive · 6 principles foundation · 6 policy examples per-table reales · 6 testing regimens · 6 failure modes documented. Tenant isolation database-level · NO app-layer-only security theater.
6 RLS principles
Default deny · enable RLS all tables
ALTER TABLE ... ENABLE ROW LEVEL SECURITY · without policy = zero access · explicit allow only · zero-trust default
Per-operation policies (SELECT · INSERT · UPDATE · DELETE)
Cada operation independent policy · prevents accidental allow via shared logic · explicit per-action enforcement
USING vs WITH CHECK · read vs write distinction
USING filtra reads · WITH CHECK validates writes · ambos requeridos para tables write-able · prevents tenant data escape via UPDATE
Session variable tenant context
`current_setting('app.clinic_id')` reads from session set por backend · NEVER NULL · NEVER trust client claim sin JWT validation
service_role bypass governance
Solo internal admin (founder · admin scripts) usa service_role · audited · monitoring service_role queries · NO API exposed con service_role key
Policy naming convention
`<table>_<operation>_<scope>` ej. `messages_select_own_clinic` · grep-able · auditable · documented purpose
Policy examples · 6 tables clave
| Table | Policies (SELECT/INSERT/UPDATE/DELETE) |
|---|---|
| messages (patient conversations) | SELECT: USING (clinic_id = current_setting('app.clinic_id')::uuid) INSERT: WITH CHECK (clinic_id = current_setting('app.clinic_id')::uuid) UPDATE: USING + WITH CHECK same clinic_id match DELETE: USING admin role only AND same clinic_id |
| patient_profiles (PII) | SELECT: USING (clinic_id = current_setting('app.clinic_id')::uuid AND deleted_at IS NULL) INSERT: WITH CHECK clinic_id match AND consent_signed = true UPDATE: USING clinic_id match AND role IN ('admin','staff') DELETE: blocked policy (use soft-delete only) |
| appointments (Cal.com sync) | SELECT: USING clinic_id match INSERT: WITH CHECK clinic_id match UPDATE: USING clinic_id match AND status != 'completed' (locked once done) DELETE: USING clinic_id match AND status = 'cancelled' only |
| audit_logs (immutable) | SELECT: USING clinic_id match OR role = 'auditor' (limited time-bound) INSERT: WITH CHECK clinic_id match (any authenticated user) UPDATE: blocked (immutable principle) DELETE: blocked (immutable principle) |
| clinic_config (settings) | SELECT: USING clinic_id match INSERT: blocked (config seeded only via migration · NOT user) UPDATE: USING clinic_id match AND role = 'admin' DELETE: blocked |
| billing_invoices (Stripe sync) | SELECT: USING clinic_id match INSERT: WITH CHECK service_role only (Stripe webhook handler) UPDATE: WITH CHECK service_role only DELETE: blocked (financial records immutable) |
Testing regimen · 6 cadences
- Pre-commit · automated test suite · attempts cross-tenant SELECT/UPDATE/DELETE · expects 0 rows returned · build fails si breach
- Pre-deploy staging · run policy test suite against staging clone · validate identical results expected
- Daily · automated test in production hours · 1 random test query cross-tenant · alert if any rows returned
- Weekly · audit log review · service_role queries pattern analysis · investigate unusual scope
- Monthly · policy review · ensure no permissive drift · ADR si policy changes needed
- Trimestral · external review · independent verification policies match documented intent
Failure modes · 6 escenarios
| Mode | Behavior |
|---|---|
| Missing session variable (app.clinic_id NULL) | Policy evaluates NULL = ::uuid · returns false · zero rows · safe default behavior · backend must set or error |
| Wrong tenant in session variable | Returns rows from wrong tenant · CRITICAL bug · backend bug · session set wrong context · audit log reveals · prevent via JWT validation rigor |
| service_role used unintentionally | Bypasses ALL RLS · all data visible · monitor service_role usage · alert spike · investigate immediate |
| Policy syntax error during migration | Migration fails atomic · DB state unchanged · staging caught first · production zero impact |
| Performance regression policy complex | Slow queries detected via pg_stat_statements · investigate policy plan · simplify or add index · postmortem if user-facing |
| Policy disabled accidentally | Direct DB ALTER TABLE ... DISABLE RLS · audit log captures · alert founder · revert immediately · postmortem |
Performance considerations · honest disclosure
RLS policies add overhead query planning + execution. Mitigation: all clinic_id columns indexed · policies use simple equality (NOT complex joins) · pgbouncer pooling reduces session variable set overhead.
Measured overhead actual: ~5-15ms p95 vs no-RLS baseline. Acceptable trade-off vs security benefit. Si traffic grows beyond budget · optimize via materialized views per-tenant OR caching layer · NEVER disable RLS.
¿Tu DBA team necesita policies completas?
Para Enterprise procurement · all policies SQL exportable · test suite negative scenarios · migration scripts disponibles bajo NDA.