PostgREST + RLS for Multi-Tenant APIs
How PostgREST plus PostgreSQL Row Level Security removes a class of CRUD controllers — tenants are isolated in the database, and a thin backend only handles auth, uploads, and side effects.
Most API backends spend most of their code on CRUD with tenant filters. PostgREST exposes tables as REST from the schema. Combined with RLS, tenant isolation can live in PostgreSQL instead of application WHERE clauses.
PostgREST model
GET /inventory → SELECT * FROM inventory (RLS applies)
POST /inventory → INSERT INTO inventory (RLS applies)
GET /inventory?id=eq.42 → filtered SELECT (+ RLS)The app issues JWTs; PostgREST passes claims into session variables PostgreSQL policies read.
RLS for tenant isolation
ALTER TABLE inventory ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON inventory
USING (tenant_id = current_setting('app.tenant_id')::uuid);Rows that fail the policy are not merely omitted from JSON — they are invisible to the query. Tenant B cannot SELECT * Tenant A’s data.
Thin backend for side effects
PostgREST does CRUD. A small Node service still owns:
- JWT issuance after login
- S3 / presigned uploads
- Webhooks and notifications
- Unusual async work (e.g. Whisper → intent → internal PostgREST PATCH)
Where it breaks down
- Business rules beyond access control need triggers or app checks
- Custom aggregations need views or dedicated endpoints
- Migrations must coordinate schema + policies + PostgREST cache refresh
- Testing RLS means hitting the DB with different JWT claims
Drawn from patterns in the Manifest / factory-ERP work (wirecept) — useful when CRUD volume dwarfs custom logic.