Multi-tenant SaaS on PostgreSQL: letting the database enforce the rules

Most multi-tenant data leaks aren't clever attacks. They're a missing where organization_id = ... in one query out of hundreds.

Put the rule where it can't be forgotten

PostgreSQL row-level security (RLS) lets the database itself decide which rows a request can see. Once a policy is in place, every query, including the one written in a hurry on a Friday, is filtered the same way.

alter table projects enable row level security;

create policy "members read their organisation's projects"
  on projects for select
  using (organization_id in (
    select organization_id from memberships where user_id = auth.uid()
  ));

Things that bite

• Index the tenant column. Policies add a filter to every query; without an index on organization_id they turn into table scans.
• Keep policy functions simple. A security definer helper is fine, but pin its search_path so it can't be hijacked.
• Never let users write their own role. Admin flags belong in a table only admins can update.
• Test as the user, not as the owner. Superusers and table owners bypass RLS, so tests run as them prove nothing.

Summary

Application checks are still useful for friendly error messages. But the guarantee should live in the database, where one forgotten line can't break it.