Row-level security is a schema decision, not a code-review convention

A signed-in knowledge base has one requirement that outranks everything else: my documents must not appear in your answers.

The tempting way to satisfy it is a convention. Every query filters on user_id. Everybody knows to do it. Code review catches the ones that forget.

That is not a security boundary. It is a habit, and it holds until someone writes a route at 11pm, or an admin script skips the filter deliberately, or a new join brings in a table whose filter nobody added. In a RAG application it is worse than usual, because the leak is not "a row appeared in a list" — it is someone else's document quoted as a source in your answer, with a citation, phrased confidently.

So the constraint belongs in the schema.

Four tables, four policies

alter table public.documents     enable row level security;
alter table public.chunks        enable row level security;
alter table public.conversations enable row level security;
alter table public.messages      enable row level security;
 
create policy "own documents" on public.documents
  for all using (auth.uid() = user_id) with check (auth.uid() = user_id);
create policy "own chunks" on public.chunks
  for all using (auth.uid() = user_id) with check (auth.uid() = user_id);
create policy "own conversations" on public.conversations
  for all using (auth.uid() = user_id) with check (auth.uid() = user_id);
create policy "own messages" on public.messages
  for all using (auth.uid() = user_id) with check (auth.uid() = user_id);

Three details in there that are easy to get wrong.

using and with check. using filters what you can read and which rows you can modify. with check validates the rows you are writing. Omit the second one and a user can read only their own rows while happily inserting rows stamped with someone else's user_id. Read protection without write protection is half a boundary.

Every table, not just the top one. chunks carries its own user_id even though it already has document_id pointing at a row that has one. That denormalisation is deliberate: a policy that has to join to establish ownership is a policy that can be defeated by a query that joins differently. Each table answers "whose is this?" without asking another table.

Cascading deletes on the auth reference. references auth.users(id) on delete cascade means deleting an account actually removes the data, rather than leaving orphaned rows that no policy now matches and no query can reach.

The keyword the whole thing hangs on

Vector search cannot be a plain select — it needs a function. And a Postgres function is exactly where RLS quietly stops applying, if you let it.

create or replace function public.match_chunks(
  query_embedding vector(1536),
  match_threshold float,
  match_count int
)
returns table (id uuid, document_id uuid, content text, similarity float)
language sql stable
security invoker                    -- ← this line
set search_path = public
as $$
  select c.id, c.document_id, c.content,
         1 - (c.embedding <=> query_embedding) as similarity
  from public.chunks c
  where c.user_id = auth.uid()
    and 1 - (c.embedding <=> query_embedding) > match_threshold
  order by c.embedding <=> query_embedding
  limit match_count;
$$;

security definer — the default many examples reach for — runs the function as its owner, which typically bypasses row-level security entirely. Every policy above becomes decorative for anything that goes through this function, which in a RAG app is the query that builds the context for every answer.

security invoker runs it as the caller, so the policies apply.

set search_path = public belongs next to it for the same class of reason: without a pinned search path, a function can be made to resolve chunks to a different table than the one you meant.

Note also that the function's where clause repeats c.user_id = auth.uid() even though the policy already enforces it. Redundant, and I kept it — the explicit predicate lets the planner use the chunks_user_idx index directly, and it means the query is correct on its own terms rather than only in the presence of a policy defined elsewhere.

The application layer still has a job

RLS being the boundary does not mean the routes get to be careless. It means their job is narrower and clearer: establish who the caller is, then let the database decide what they can see.

Every protected route starts the same way:

const supabase = await createClient();
const { data: { user } } = await supabase.auth.getUser();
 
if (!user) {
  return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
}

getUser() rather than getSession() — the first verifies the token with the auth server; the second reads what is in the cookie, which the client controls.

And the session refresh runs in the proxy before anything else touches cookies:

// Do not remove: this call refreshes the session and must run before any
// other code that reads cookies.
const { data: { user } } = await supabase.auth.getUser();

That comment exists because I removed it once, thinking it was a dead call, and spent a while confused about expired sessions.

Verify it with a second account, not by reading the policies

This is the part I would insist on. A tenancy bug is not the kind you find by re-reading your own code — you already believe it is right, which is why you wrote it that way.

What actually establishes the property: sign in as a second, unrelated account. Upload a document. Ask a question whose answer only exists in the first account's documents.

The correct outcome is not "a less good answer". It is "I don't know", with no sources cited — because the retrieval genuinely returned nothing.

Then do the inverse, and check the first account's citations still point only at its own files. Two accounts, two directions, ten minutes. It is the only test that exercises the whole stack — auth, policy, function security mode, and the retrieval query — against the actual claim you are making to users.

Worth stealing even if you never touch Supabase

• Put tenancy in the schema. A predicate the database enforces survives refactors, new routes, and future contributors. A convention does not.
• with check, not just using. Read isolation without write isolation is not isolation.
• Audit every function for its security mode. In a system where one function builds the context for every answer, one wrong keyword is the entire boundary.
• Prove it from a second account. Correct-looking code and a correct system are different claims, and only one of them is testable.

The migration is supabase/migration.sql — sixty lines, and the security model is all of it.