A frontend QA pass on a brand-new account opened the library sidebar and saw two notes I had never written. They were public seed entries from a different user. Same UUIDs across every fresh account I tested.

This is a post-mortem of how multiple Postgres Row Level Security policies on the same table, glued together by OR, returned every flagged row to every authenticated user. And how the application layer trusted RLS to be a backstop and added zero filters of its own.

The fix shipped a few hours after the bug was found. Here is what happened, what I changed, and what I would do differently next time.

The setup

Single table, multi-user app. Each row has a user_id and a boolean is_public. The product has a private surface (your own notes) and a planned public surface (a shared atlas of notes you opt-in to publish). The public surface is not built yet, but I added the schema for it on day one because I figured the policies were free to write up front.

Here are the two policies that lived on the table:

CREATE POLICY "own_data" ON journal_entries
  FOR ALL
  USING (auth.uid() = user_id);

CREATE POLICY "public_entries" ON journal_entries
  FOR SELECT
  USING (is_public = TRUE);

Both are correct in isolation. Together they are a leak.

How RLS combines policies

When a table has multiple permissive policies, Postgres ORs their USING expressions. So the effective SELECT predicate on journal_entries becomes:

auth.uid() = user_id   -- from "own_data"
OR
is_public = TRUE       -- from "public_entries"

Reading that out loud: a row is visible if it belongs to me or if anyone in the system marked it public. Which is exactly what I asked for. It is also exactly the bug.

The expectation was that is_public would only matter on the dedicated public surface. The reality is that RLS does not know which surface my query is coming from. It evaluates the predicate against whatever the caller is doing right now. Every select * from journal_entries from an authenticated session now returned my rows plus every is_public=TRUE row across every user.

Why nothing failed loudly

Two reasons.

One: my code never set is_public = TRUE in the user-facing flow. The flag existed in the schema, the policy existed in the migration, but the only rows in the entire database with the flag set were two seed entries I had inserted by hand months ago for a demo. So in development, on my account, the leak was invisible. Both leaked rows belonged to me.

Two: the application code trusted RLS as the access boundary. Every query at the data-access layer looked like this:

const { data } = await supabase
  .from("journal_entries")
  .select("id, title, body, created_at")
  .order("created_at", { ascending: false });

No .eq("user_id", user.id). Nine call sites. The reasoning at the time was "RLS already filters by user_id, doubling up is noise." That reasoning is wrong the moment a second policy enters the picture. The OR semantics turn "RLS filters by user_id" into "RLS filters by user_id OR by something else."

How QA found it

A new test account, just signed up, no entries written. The library sidebar should have been empty. Instead it had two notes I did not recognize. UUIDs 8e0fb236... and a192e2f7.... I tried it on a second new account. Same UUIDs. Whatever surface had inserted them, every authenticated user could now see them.

Five minutes of git blame on the migration file led me to the public_entries policy. Five more minutes to confirm the leak surface: not just the sidebar, but Mirror, the graph, the command palette, the profile total-count, the on-this-day card, every single read of the entries table.

Sales had been live for about three days at that point. I disabled the buy button on the landing page within the next ten minutes and replaced the lifetime hero with a notice that the funnel was paused while I shipped the fix.

The fix, in two parts

I treated this as a defense-in-depth problem. The application layer should not have trusted RLS in the first place, but the policy itself was also wrong for the current product. Both got fixed.

Part 1: explicit user_id filters at the app layer

Every select from journal_entries got an explicit .eq("user_id", user.id):

const { data: { user } } = await supabase.auth.getUser();
if (!user) redirect("/login");

const { data } = await supabase
  .from("journal_entries")
  .select("id, title, body, created_at")
  .eq("user_id", user.id)              // <- new
  .order("created_at", { ascending: false });

Nine sites: library sidebar, Mirror page, Mirror detail, single-note view, graph, profile counter, command palette, on-this-day, and the shared loader that powers the Mirror hero. All paired with an auth.getUser() at the top of the handler so a missing session redirects to login instead of running an unauthenticated query.

This change is the load-bearing one. Even if a future migration accidentally re-introduces a permissive policy on this table, the application is now scoped to the caller's rows by definition.

Part 2: drop the unused policies at the DB layer

The public surface is not shipped. There is no production code path that depends on public_entries, public_profiles, or public_goals returning rows. So the policies should not exist on a production table. New migration:

DROP POLICY IF EXISTS "public_entries" ON journal_entries;
DROP POLICY IF EXISTS "public_profiles" ON profiles;
DROP POLICY IF EXISTS "public_goals" ON goals;

UPDATE journal_entries
SET is_public = FALSE
WHERE id IN (
  '8e0fb236-8801-40ec-9e70-5e7dc3a9bf50',
  'a192e2f7-4523-4c86-be58-7df5314dced9'
);

Two effects. The OR-combine that was producing the leak is gone, so a future query that forgets the explicit user_id filter is at least scoped to auth.uid() = user_id again. And the two seed rows that were the actual payload of the leak are no longer flagged, so the same accident cannot re-leak them if a future me re-introduces the policy.

When the public surface ships for real, it will not piggyback on a permissive RLS policy. It will go through a SECURITY DEFINER RPC with explicit access checks, returning only the columns the public view should expose. RLS does one thing well: scope a row to its owner. Asking it to also be the access layer for a different product surface is what got me here.

Verification

A second QA pass on three brand-new accounts: empty library sidebar, empty Mirror, empty graph, empty everything. The two phantom UUIDs no longer appear anywhere. Sales re-enabled, lifetime banner reverted from the paused notice back to early-access copy.

The general lesson

RLS is a backstop, not a primary access control mechanism. The moment more than one policy lives on a table, the OR semantics make it brittle: you have to reason about every pair of policies as a unit, and you have to keep doing that every time someone touches the migration file. The cost of an explicit .eq("user_id", user.id) at the app layer is one line. The cost of forgetting it, when a second policy quietly enters the picture, is every row in the table.

Three things I am going to do differently next time:

Default to explicit filters at the app layer, even with RLS in place. RLS catches the case where I forget. The explicit filter catches the case where RLS forgets. Both layers should agree.

Do not write policies for unbuilt features. The policy that caused this was for a public surface that does not exist yet. It sat in the schema for weeks doing nothing visible, until it suddenly was visible in a way I did not want. If the feature is not built, the policy should not be either.

Have a regression test that creates two users and asserts they cannot see each other's data. Spin up account A, write an entry, spin up account B, query the library, assert the entry is not in the result. This kind of test would have failed the moment the policy was added. It is going on the to-do list this week.

The fix is live. The seed data is unflagged. The buy button is back on. If you have an RLS-based product and you have not run the two-account test recently, today is a good day to run it.