Build a database boundary that remains correct as SaaS migrations accumulate.
Shared-schema PostgreSQL places many customers in the same tables. The design can be safe, but every query path and privileged helper must preserve the same tenant invariant.
Make tenant ownership explicit
Protected rows need an immutable or tightly controlled tenant key. Foreign keys and uniqueness constraints should include that key where necessary so cross-tenant relationships cannot be created accidentally.
Use RLS as defense in depth
Application filters are useful but easy to omit. PostgreSQL row-level security places an additional authorization decision at the database boundary. Policies should relate the current database identity or trusted claim to the row's tenant key.
sqlalter table public.projects enable row level security;
alter table public.projects force row level security;Keep privileged roles out of request paths
Table owners, superusers, and roles with BYPASSRLS can bypass ordinary policies. Service-role credentials and maintenance jobs therefore need narrower operational boundaries, logging, and tests of their own.
Constrain SECURITY DEFINER functions
A SECURITY DEFINER function executes with its owner's privileges. Pin search_path, schema-qualify referenced objects, revoke PUBLIC execution, and grant only the intended roles.
sqlalter function public.rotate_project_key(uuid) set search_path = '';
revoke execute on function public.rotate_project_key(uuid) from public;
grant execute on function public.rotate_project_key(uuid) to service_role;