PostgreSQL Row-Level Security for Multi-Tenant SaaS

Why application-level tenant filters are not enough

A shared-schema SaaS application commonly stores a tenant_id on every tenant-owned row. The API then adds WHERE tenant_id = ... to each query. That convention is useful for query planning and readability, but it is a fragile security boundary by itself: one forgotten predicate, overly broad repository method, background task, or ad-hoc query can cross tenant boundaries.

PostgreSQL row-level security (RLS) lets a table attach policies that decide which rows a database role may read or change. When RLS is enabled and no applicable policy exists, PostgreSQL uses default-deny behavior. Policies can target commands and roles, and PostgreSQL evaluates USING expressions for existing rows while WITH CHECK controls rows created by inserts or updates. The PostgreSQL row security documentation and CREATE POLICY reference define the current behavior.

RLS is defense in depth, not a complete authorization system. Table grants still determine whether a role can reach an object at all, while policies determine which rows it can reach. Rate limits, subscription rules, field-level restrictions, and workflow permissions may need separate controls. Supabase documents this two-layer relationship in its Data API security guide.

Model tenant ownership before writing policies

Start with one stable tenant identifier and one authoritative membership table. Every tenant-owned table should carry a non-null tenant key with a foreign key to the tenant record. Avoid inferring tenancy from mutable labels, email domains, URL slugs, or a client-supplied header that the server has not verified.

create table public.organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null
);

create table public.organization_members (
  organization_id uuid not null references public.organizations(id),
  user_id uuid not null,
  role text not null check (role in ('owner', 'admin', 'member')),
  primary key (organization_id, user_id)
);

create table public.projects (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id),
  name text not null,
  created_by uuid not null
);

create index projects_organization_id_idx
  on public.projects (organization_id);

Propagate the tenant key even when it can be discovered through a chain of joins. A direct key makes ownership obvious, simplifies policies, and gives PostgreSQL an indexable predicate. Protect its integrity with foreign keys and ensure child records cannot be moved to another tenant through an update that the policy forgot to check.

For the broader choice between shared tables, separate schemas, and separate databases, see our multi-tenant SaaS architecture guide. RLS is strongest when it reinforces a deliberate data model rather than compensating for ambiguous ownership.

Write command-specific policies with explicit read and write rules

The example below uses Supabase Auth's auth.uid() to identify the signed-in user. Supabase recommends enabling RLS on every table in an exposed schema and notes that unauthenticated auth.uid() calls return null. Its RLS guide also recommends targeting the authenticated role and indexing policy columns.

alter table public.projects enable row level security;

create policy "members can read organization projects"
on public.projects
for select
to authenticated
using (
  (select auth.uid()) is not null
  and exists (
    select 1
    from public.organization_members membership
    where membership.organization_id = projects.organization_id
      and membership.user_id = (select auth.uid())
  )
);

create policy "members can create organization projects"
on public.projects
for insert
to authenticated
with check (
  exists (
    select 1
    from public.organization_members membership
    where membership.organization_id = projects.organization_id
      and membership.user_id = (select auth.uid())
  )
  and created_by = (select auth.uid())
);

Use separate policies when read, create, update, and delete permissions differ. An update needs both visibility of the old row and permission for the proposed new row; an explicit WITH CHECK prevents a user from changing organization_id to escape the intended boundary. Keep business roles such as owner or billing administrator in trusted database records or server-managed claims, not user-editable profile metadata.

For a complete authentication layer around the policies, our Next.js and Supabase authentication guide covers sessions, role checks, and server-side verification.

Keep privileged paths narrow and server-only

PostgreSQL superusers, roles with BYPASSRLS, and normally the table owner bypass row security. PostgreSQL can apply FORCE ROW LEVEL SECURITY when the owner should also be subject to policies, but migrations and administrative workflows still need a carefully designed role model.

Supabase service-role credentials can bypass RLS and must never be exposed in a browser or customer-controlled environment. Reserve them for tightly scoped server jobs that genuinely need cross-tenant access. Validate every job input, log the acting system and tenant scope, and prefer a narrow database function or dedicated role over giving a general request handler unrestricted table access.

• Separate end-user data access from migrations, support tools, billing jobs, and scheduled maintenance.
• Do not accept a client-provided user or tenant identifier as proof of authorization on a privileged connection.
• Revoke unnecessary grants and keep internal tables and helper functions outside exposed schemas.
• Pin a safe search_path and review ownership when using SECURITY DEFINER functions.
• Rotate and monitor privileged credentials as production secrets.

Make tenant policies easy for PostgreSQL to plan

Authorization predicates run as part of normal queries, so schema and index design matter. Index tenant keys and membership lookup columns. Keep policy expressions stable and understandable. Continue to include an explicit tenant filter in application queries: RLS remains the enforcement boundary, while the query predicate communicates intent and can help the planner construct an efficient plan.

select id, name
from public.projects
where organization_id = $1
order by id
limit 50;

Use EXPLAIN (ANALYZE, BUFFERS) with representative data and the same non-owner role used by the application. Check point lookups, list pages, sorting, pagination, and membership-heavy paths. Treat a complex policy like production query code: measure it, review it, and prevent accidental recursion between protected tables.

RLS cannot repair an exhausted connection pool or an unbounded query. Pair policy work with the capacity practices in our Node.js database connection-pooling guide.

Test denied access, not only successful access

Positive tests prove that one user can complete a task. Isolation tests prove that another user cannot see or change it. Create at least two tenants with distinct users and data, run tests through the same database role and authentication context as production, and attempt every operation across the boundary.

Include indirect paths: views, database functions, joins, nested API resources, bulk operations, realtime subscriptions, file metadata, and support tooling. Verify that new tables enter a default-deny review process. A migration test can query the catalog for tenant-owned tables without RLS or without applicable policies and fail before deployment.

Test policy changes as security migrations. Capture the role, identity claims, grants, and expected result in fixtures so a future refactor cannot silently broaden access.

Roll out RLS without hiding application defects

Inventory every table, role, view, function, and service that touches tenant data. Add tenant keys and indexes first, backfill them with validation, then introduce policies in a staging environment using production-like roles. Compare application results before and after enforcement, including background jobs and administrative workflows.

Deploy in small groups of tables where practical. Watch authorization failures, empty result sets, latency, query plans, and support workflows. If a feature fails after enforcement, fix its identity or access contract; do not respond with a broad permissive policy that restores functionality by weakening isolation.

Document who may bypass RLS, why, from which runtime, and how that path is tested. Review the inventory whenever a migration adds an exposed table or changes membership semantics.

PostgreSQL RLS production checklist