Postgres row-level security schema design

Row-level security moves authorisation into the database, where it cannot be skipped by a forgotten check in an API route. It is the right default for multi-tenant applications. It is also easy to get subtly wrong in ways that either lock everyone out or quietly let everyone in. These are the rules we apply on every build.

Enabling RLS is two steps, not one

Enabling row-level security without writing policies denies everything. Writing policies without granting table privileges also denies everything, because privileges and policies are separate mechanisms. Both must be present, in this order: create the table, grant privileges to the roles your policies reference, enable RLS, then create policies.

create table public.invoices (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null,
  total_cents integer not null,
  created_at timestamptz not null default now()
);

grant select, insert, update, delete on public.invoices to authenticated;
grant all on public.invoices to service_role;

alter table public.invoices enable row level security;

create policy "owners read their invoices"
  on public.invoices for select to authenticated
  using (auth.uid() = owner_id);

Never store roles on the user or profile table

The single most common security defect we find in existing projects is an is_admin boolean or a role column on the profiles table that the user can update. If a user can write to their own profile row — and they usually can, because that is how profile editing works — then they can promote themselves to admin. Every other policy that trusts that column collapses at the same moment.

Roles belong in their own table, writable only by trusted server-side code.

create type public.app_role as enum ('admin', 'staff', 'user');

create table public.user_roles (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null,
  role public.app_role not null,
  unique (user_id, role)
);

grant select on public.user_roles to authenticated;
grant all on public.user_roles to service_role;
alter table public.user_roles enable row level security;

Check roles through a security definer function

If a policy on user_roles queries user_roles, Postgres recurses and the query fails. The standard fix is a SECURITY DEFINER function that runs with the owner's privileges and therefore bypasses RLS on the tables it reads. Pin its search_path so the function cannot be tricked into resolving a different schema.

create or replace function public.has_role(_user_id uuid, _role public.app_role)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.user_roles
    where user_id = _user_id and role = _role
  );
$$;

create policy "admins read all invoices"
  on public.invoices for select to authenticated
  using (public.has_role(auth.uid(), 'admin'));

Write policies per operation, and name them plainly

A single FOR ALL policy is convenient and hides intent. Separate SELECT, INSERT, UPDATE and DELETE policies make the intended access model readable by whoever inherits the project. Remember that USING controls which existing rows a statement can see, while WITH CHECK controls what a row is allowed to look like after an insert or update. An UPDATE policy usually needs both, otherwise a user can move a row to another owner.

create policy "owners update their invoices"
  on public.invoices for update to authenticated
  using (auth.uid() = owner_id)
  with check (auth.uid() = owner_id);

Remember that SELECT policies gate writes too

An UPDATE or DELETE with a WHERE clause has to read the row first. If your SELECT policy hides a row — because it is a draft, archived, or pending approval — the owner cannot update or delete it either, and the symptom reported to you is 'my change silently did nothing'. Any status column that hides rows from the public needs a matching owner-scoped SELECT policy in the same migration.

Keep the service role on the server

The service role key bypasses RLS entirely. It belongs in server-side code and nowhere else — not in a mobile app bundle, not in a browser environment variable, not in a repository. If a piece of functionality seems to need it in the client, the policy model is wrong, not the key placement.

Test the policies, not the happy path

Sign in as user A and try to read, update and delete user B's rows. All four operations should fail.
Attempt an insert that sets owner_id to another user. The WITH CHECK clause should reject it.
Check the anonymous role explicitly: what can a signed-out visitor read?
Run the database linter after every migration and treat a table with RLS enabled and no policies as a bug, not a warning.

Policies are code. They deserve the same review as the application, and they are considerably harder to fix after a leak than before one.

Structuring a Postgres schema for row-level security