Enforce Row-Level Security in Postgres

Enable row-level security, write policies that filter by the current tenant, and force policies even for the table owner so isolation is enforced by the database.

~45 minsecuritymulti-tenantpostgresql

Task

Add row-level security policies so one tenant can never read or modify another tenant rows.

When to use this

Authorization lives only in application code, so a single forgotten WHERE clause or a direct psql session can expose every tenant.

Prerequisites

PostgreSQL 9.5+
A per-request setting to carry the current tenant id

Steps

1. Enable row-level security on each tenant table.

Expected output: The table now honors policies.

2. Create a policy that permits rows where tenant_id matches the session setting.

Expected output: Only the current tenant rows are visible.

3. Set the tenant id per transaction with a session-local setting.

Expected output: Each request is scoped to one tenant.

4. Add FORCE ROW LEVEL SECURITY so even the table owner is subject to policies.

psql -c "SET app.tenant_id = 'acme'; SELECT count(*) FROM orders;"

Expected output: Bypassing the app cannot bypass isolation.

Troubleshooting

Problem

Queries run as a superuser and ignore RLS

Solution

Superusers and BYPASSRLS roles bypass policies; use a dedicated non-superuser application role.

Problem

Setting leaks across pooled connections

Solution

Use SET LOCAL inside a transaction or reset the setting on checkout.

Limitations

RLS adds planning overhead to every query and must be paired with proper indexes on the tenant column.
It does not replace application-level checks for actions that depend on business rules beyond tenant membership.

Context

Add row-level security policies so one tenant can never read or modify another tenant rows. Authorization lives only in application code, so a single forgotten WHERE clause or a direct psql session can expose every tenant.

Steps

Enable row-level security on each tenant table. Expected: The table now honors policies.
Create a policy that permits rows where tenant_id matches the session setting. Expected: Only the current tenant rows are visible.
Set the tenant id per transaction with a session-local setting. Expected: Each request is scoped to one tenant.
Add FORCE ROW LEVEL SECURITY so even the table owner is subject to policies. Expected: Bypassing the app cannot bypass isolation.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Verify

The table now honors policies.
Only the current tenant rows are visible.
Each request is scoped to one tenant.
Bypassing the app cannot bypass isolation.

Limits

RLS adds planning overhead to every query and must be paired with proper indexes on the tenant column.
It does not replace application-level checks for actions that depend on business rules beyond tenant membership.