Row-Level Security for Multi-Tenant SaaS: A Practical Pattern

If you're building multi-tenant SaaS on Postgres, Row Level Security-style row-level security is the safest primitive you can reach for. App-layer WHERE tenant_id = ? is one forgotten join away from a data breach. RLS pushes the check into the database where you can't forget it.

The pattern

1. Store the tenant identifier on every row (workspace_id in our schema).
2. Enable RLS on the table: ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;
3. Add a policy that reads auth.uid() and joins to a membership table.
4. Grant SELECT/INSERT/UPDATE/DELETE to authenticated.
5. Roles live in a separate user_roles table — never on the profile.

The role table trick

Storing roles on the user or profile table is a privilege-escalation waiting to happen. Any RLS policy that reads its own row can be tricked. Split roles into user_roles, check via a SECURITY DEFINER function like has_role(auth.uid(), 'admin'), and the recursion vanishes.

Testing it

Write RLS tests that connect as a fake user and try to read another tenant's row. This is a five-line pgTAP test that pays for itself the first time someone refactors a policy.

What RLS doesn't solve

• Cross-tenant search indexes — those need application-layer filtering too.
• Signed file URLs — expire them or they'll outlive the permission that issued them.
• Realtime channels — verify subscription permissions on connect, not just on read.

Our secure client portal checklist covers the rest of the surface. Every X-com production table follows this pattern — see the trust center for the audit summary.