Row-Level Security for Multi-Tenant Postgres: USING vs WITH CHECK in Practice

This piece isn't about turning RLS on. It's about what goes inside the policies once it is, and about proving they do what you think they do instead of just trusting the SQL.

That distinction matters because a bad RLS policy compiles fine. Postgres accepts it without complaint. Nobody catches the mistake in code review, because reading a policy tells you what it's supposed to allow, not what it actually denies. The only way to know for sure is to attack it as a real user with real credentials, then read the output. Most of this article is that attack, run for real, against Atlas's actual schema.

The tenant boundary in three tables

The code throughout is real, pulled from Atlas, a project and task management app I built. Every table in it is scoped to a project, and the project is the tenant boundary. Real schema, from supabase/migrations/001_initial_schema.sql:

create table public.projects (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  description text,
  status text default 'active' check (status in ('active', 'completed', 'archived')),
  owner_id uuid references public.profiles(id) on delete cascade not null,
  created_at timestamptz default now() not null
);

create table public.project_members (
  project_id uuid references public.projects(id) on delete cascade not null,
  user_id uuid references public.profiles(id) on delete cascade not null,
  role text default 'collaborator' check (role in ('owner', 'collaborator')),
  joined_at timestamptz default now() not null,
  primary key (project_id, user_id)
);

create table public.tasks (
  id uuid default gen_random_uuid() primary key,
  title text not null,
  description text,
  status text default 'todo' check (status in ('todo', 'in_progress', 'done')),
  project_id uuid references public.projects(id) on delete cascade not null,
  assignee_id uuid references public.profiles(id) on delete set null,
  created_at timestamptz default now() not null,
  due_date timestamptz
);

The tenant key is different on each table, and it's worth naming plainly. On projects, the tenant is the row itself. A project doesn't belong to another project. On project_members and tasks, the tenant key is project_id. Every policy in this piece exists to enforce one rule against that key: a user only touches rows whose project_id traces back to a project they belong to.

RLS on these three tables was turned on in the same migration, explicitly, not by any later automation:

alter table public.projects enable row level security;
alter table public.project_members enable row level security;
alter table public.tasks enable row level security;

Reads filter, writes check, and that split lives in two clauses

Every RLS policy attaches to one command, SELECT, INSERT, UPDATE, DELETE, or ALL, and to a clause. USING is a filter. It decides, per row, whether a query can see that row at all. A row that fails USING on a SELECT doesn't error, it just isn't there. WITH CHECK is a gate on the row a write is about to leave behind. On INSERT, the new row has to pass it or the insert fails. On UPDATE, both clauses run: USING decides which existing rows can be targeted, and WITH CHECK decides whether the row's new state is still allowed to exist.

Here's tasks, current policies, one per command:

create policy "tasks: project members can read"
  on public.tasks for select
  to authenticated
  using (
    is_active_user()
    and (
      exists (
        select 1 from public.project_members
        where project_id = tasks.project_id
        and user_id = auth.uid()
      ) or
      exists (
        select 1 from public.projects
        where id = tasks.project_id
        and owner_id = auth.uid()
      )
    )
  );

create policy "tasks: project members can create"
  on public.tasks for insert
  to authenticated
  with check (
    is_active_user()
    and (
      exists (
        select 1 from public.project_members
        where project_id = tasks.project_id
        and user_id = auth.uid()
      ) or
      exists (
        select 1 from public.projects
        where id = tasks.project_id
        and owner_id = auth.uid()
      )
    )
  );

create policy "tasks: project members can update"
  on public.tasks for update
  to authenticated
  using (
    is_active_user()
    and (
      exists (
        select 1 from public.project_members
        where project_id = tasks.project_id
        and user_id = auth.uid()
      ) or
      exists (
        select 1 from public.projects
        where id = tasks.project_id
        and owner_id = auth.uid()
      )
    )
  );

create policy "tasks: owner can delete"
  on public.tasks for delete
  to authenticated
  using (
    is_active_user()
    and exists (
      select 1 from public.projects
      where id = tasks.project_id
      and owner_id = auth.uid()
    )
  );

Ignore is_active_user() for now. It's a later account-deletion guard layered onto these policies, not part of the tenant-isolation story here.

Look at the update policy. It defines USING. It has no WITH CHECK at all. That's not a gap, it's Postgres's own default: when a policy needs a WITH CHECK and doesn't specify one, Postgres reuses the USING expression for both jobs. So tasks: project members can update runs the same membership check twice on a single UPDATE, once to decide which row a member is even allowed to select for editing, and again against the row's resulting state before the update is allowed to land. A member can't target a task outside their projects, and they can't use an update to move a task into someone else's project either. One expression, applied on both ends.

is_project_member() exists because the inline version caused recursion

The is_project_member() function already showed up in the audit-log piece, used inside a trigger. Here it solves a different problem entirely.

The original project_members read policy, from migration 002, checked membership by querying project_members from inside its own policy:

create policy "project_members: members can read"
  on public.project_members for select
  to authenticated
  using (
    exists (
      select 1 from public.project_members pm
      where pm.project_id = project_members.project_id
      and pm.user_id = auth.uid()
    )
  );

That's a policy on project_members that queries project_members. Postgres has to apply the table's RLS policy to evaluate the table's RLS policy. Infinite recursion. Migration 003 fixed it by moving the check into a function:

CREATE OR REPLACE FUNCTION public.is_project_member(_user_id uuid, _project_id uuid)
 RETURNS boolean
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
begin
  return exists (
    select 1 from public.project_members
    where project_id = _project_id
    and user_id = _user_id
  );
end;
$function$

create policy "project_members: members can read"
  on public.project_members for select
  to authenticated
  using (is_project_member(auth.uid(), project_id));

SECURITY DEFINER is what actually breaks the loop. It makes the function run as its owner, not as the calling authenticated role, so the SELECT inside it doesn't re-trigger project_members's RLS policy the way a plain inline subquery would. The check now happens from outside the policy chain instead of one more layer inside it.

grant all looks reckless until you know what's actually gating access

The grants on all three tables, verbatim from migration 001:

grant all on public.projects to authenticated;
grant all on public.project_members to authenticated;
grant all on public.tasks to authenticated;

Read on its own, that looks like every authenticated user can do anything to every row in these tables. At the grant level, that's true. In practice, it isn't, because grants and policies answer two different questions. The grant says which commands the authenticated role may attempt at all. The policy says which specific rows that attempt is allowed to touch. Postgres checks both, in order. grant all opens the door to trying an UPDATE. USING and WITH CHECK decide whether any row actually moves.

By contrast, activity_log takes one narrow SELECT grant, nothing else, because nothing but a SECURITY DEFINER trigger was ever meant to write to it. projects, project_members, and tasks are different tables with a different job. Real users insert, update, and delete their own rows directly, so the grant has to allow it. All of the isolation work moves onto the policies instead, on purpose.

A denied read returns nothing, a denied write returns zero rows

Postgres RLS denies reads by filtering, not erroring, a SELECT returns fewer rows, never a permission error for a correctly-denied row. So when you do see a permission error on a SELECT, it's a missing grant, not RLS doing its job.

The write side behaves differently, and it's worth being precise about it. A SELECT with a failing USING clause just comes back with fewer rows, or none, no error to catch. An UPDATE or DELETE with a failing USING clause looks the same on the surface, UPDATE 0, DELETE 0, no error. But a write can also go loud: if a row passes USING and gets selected for the update, and the resulting row then fails WITH CHECK, Postgres raises an actual error, new row violates row-level security policy. Reads can only go quiet. Writes can go quiet or loud, depending on which clause fails and when.

Know which one you're looking at before you debug it.

User A cannot read, update, or delete User B's task, and the terminal proves it

Setup: two real users on Atlas's local Supabase stack, connected with plain psql, no application code involved. e2e-primary owns a project and a task. e2e-secondary owns a separate project and a separate task. Each is authenticated by setting request.jwt.claims directly in the session, the same value Supabase's PostgREST layer would set from a real JWT.

As e2e-primary, against a task owned by e2e-secondary:

set role authenticated;
select set_config('request.jwt.claims', json_build_object('sub', '3cb56877-7dfa-4dc9-ae26-9f48a5a95bfe', 'role', 'authenticated')::text, false);

select id, title, project_id from public.tasks where id = '66666666-6666-6666-6666-666666666666';
-- id | title | project_id
-- ----+-------+------------
-- (0 rows)

update public.tasks set title = 'HACKED BY A' where id = '66666666-6666-6666-6666-666666666666';
-- UPDATE 0

delete from public.tasks where id = '66666666-6666-6666-6666-666666666666';
-- DELETE 0

reset role;

UPDATE 0 on its own would be ambiguous. It looks identical to a WHERE clause that simply matched nothing, RLS or no RLS. The step that actually proves isolation is the read-back, as superuser, right after:

select id, title, project_id from public.tasks where id = '66666666-6666-6666-6666-666666666666';

--                   id                  |               title               |              project_id
-- --------------------------------------+------------------------------------+--------------------------------------
--  66666666-6666-6666-6666-666666666666 | Adversarial Proof Task B (rerun)  | 55555555-5555-5555-5555-555555555555
-- (1 row)

The row is still there. Still owned by e2e-secondary's project. Still holding its original title. Nothing moved.

Running the same three commands the other direction, e2e-secondary against e2e-primary's task, produced the same shape of result: zero rows on the SELECT, UPDATE 0, DELETE 0, and a superuser read-back confirming e2e-primary's task kept its original title. The denial isn't a one-way artifact of a single policy. It holds in both directions.

Honest scope, and where this doesn't reach

The proof you just ran is a point-in-time manual check, not a guarantee that holds over time. Nothing re-runs it, so a later policy change could break isolation silently and no test would notice. To close that, wire the same kind of adversarial check into CI with a tool like pgTAP or pg_prove, so a broken policy fails the build instead of waiting on a reviewer to spot it.

And the proof assumes what any RLS setup has to assume: a credential that bypasses RLS entirely, whether that's superuser, the service role, or a leaked connection string, sits outside this threat model. These policies gate the authenticated role going through the app's normal connection. They were never built to survive a stolen database password, and no policy design changes that.

If you're running RLS on a multi-tenant schema of your own, don't take a policy's word for it. Run the version of this test against your own tables, with two real users and real credentials, and read what actually comes back.