When Supabase RLS authenticates but doesn't authorize — how auth.role() = 'authenticated' leaks every row, and how to scope by owner

Let me state the conclusion first. When a Supabase app "shows other people's data to every logged-in user," the cause is usually not whether RLS exists, but what the policy predicate means. RLS is on. A policy is there. It won't trip the dashboard's "RLS not enabled" warning. And yet, if the policy says using (auth.role() = 'authenticated'), that table is now "readable in full by anyone who is logged in." The policy checks authentication (are you logged in?) but never checks authorization (is this row yours?) — that is the subject of this article.

This is not an imagined bug. A static-analysis field study of 1,000 public Supabase apps (116,662 RLS policies) found that 9.2% of the 994 apps that ship RLS had at least one of these "authenticates but doesn't authorize" policies. Every one of the 235 findings was hand-audited to precision 1.0 (zero residual false positives) — and it is a lower bound, so the real number is likely higher.

Up front: this is not "don't use AI" or "Supabase is dangerous." RLS is powerful and Supabase is robust. The problem is that authorization — a "vertical" risk — is easily waved through by both the AI and the developer with "it worked, so it's fine." Below: why this predicate leaks every row, why it gets mass-produced, and how to tell it apart from a legitimate shared table and fix it — grounded in primary sources and real SQL.

1. Where a predicate splits authentication from authorization

First, pin the terms down precisely.

• Authentication = confirming "who you are." Login is this.
• Authorization = confirming "do you have the privilege over this operation / this data."

An RLS policy's USING clause is where authorization belongs. But these three look alike and mean entirely different things:

-- ① authentication only (are you logged in?) — no row filter
using ( auth.role() = 'authenticated' )

-- ② authentication only (does a user id exist?) — same trap as ①
using ( auth.uid() is not null )

-- ③ authorization (is this row yours?) — bound to the owner
using ( (select auth.uid()) = user_id )

① and ② are nothing more than proof that a session exists. They answer "is the caller logged in?" with a boolean and never reference the user_id column, so they return the same answer for every row (true while logged in). The result: every authenticated user gets every row.

Only ③ compares auth.uid() (the ID of the caller) against the row's ownership column user_id. That is authorization. RLS evaluates this expression per row and returns only the rows where it is true — so swapping an ID returns zero of someone else's rows.

to authenticated is the audience, not the rows returned. create policy ... to authenticated declares "apply this policy to the authenticated role," while the row filter is decided by USING. So to authenticated using (auth.role() = 'authenticated') means "for logged-in users, show everything if they're logged in" — it says "authenticated only" twice. This is the single most misunderstood point (PostgreSQL: Row Security Policies).

This "authenticates but doesn't authorize" gap is a sibling of IDOR (the class where code forgets the ownership check). Where IDOR opens outside RLS via a missing .eq("user_id", …) in application code, this one is the RLS policy predicate itself abandoning authorization. Same boundary to defend — ownership — but a different place it leaks.

2. Why auth.role() = 'authenticated' leaks every row

Supabase auto-exposes PostgreSQL tables as a REST API via PostgREST. Consider this plausible-looking schema:

create table public.notes (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users,   -- ← there is an ownership column
  body text,
  created_at timestamptz not null default now()
);

-- RLS is enabled (this part is correct)
alter table public.notes enable row level security;

-- but the policy is "authentication only"
create policy "authenticated can read notes"
on public.notes for select
to authenticated
using ( auth.role() = 'authenticated' );   -- ← only checks that you're logged in

RLS is on. A policy exists. It won't trip Supabase's "RLS not set" warning. And yet every user's notes leak.

# The attacker does nothing special: log in with their own JWT and fetch everything.
curl "https://<project>.supabase.co/rest/v1/notes?select=*" \
     -H "apikey: <anon-key>" \
     -H "Authorization: Bearer <any logged-in user's JWT>"
# → every other user's user_id and body come back, row for row

The reason is simple. A logged-in user's auth.role() is always 'authenticated', so the USING expression is true for every row and PostgREST returns the whole table. The user_id column exists, but the policy never looks at it.

Rewriting to auth.uid() is not null changes nothing: while logged in, auth.uid() is always non-NULL, so it is true for every row too.

The real-world weight of this class shows up in a published CVE. CVE-2025-48757 (CVSS 9.3 CRITICAL, CWE-863 Incorrect Authorization) is a case where AI-generated sites shipped insufficient RLS/authorization, allowing arbitrary read/write of tables. The classification is exactly A01:2021 Broken Access Control (OWASP) — #1 in the OWASP Top 10.

3. Why AI and developers mass-produce this policy

There are structural reasons this doesn't stay a one-off "oops" but gets produced over and over.

Reason 1: literal implementation of the prompt. Ask for "let logged-in users read their notes," and both AI and humans faithfully write "is the caller logged in?" auth.role() = 'authenticated' is that sentence turned almost directly into SQL. The unspoken requirement — "only their own rows" — never appears unless you state it.

Reason 2: it never surfaces in a demo. During development you use one account and create only your own notes, so "returns everything" just looks like "I can see all my notes." Nobody rewrites an ID to peek at another user, so the tests stay green all the way to production.

Reason 3: to authenticated breeds false comfort. Official tutorials (correctly) recommend restricting the role with to authenticated. But "restricted to authenticated" and "restricted to the owner's rows" are different things — and it's easy to stop at the former.

Does asking the AI to "write it securely" fix it? Don't expect too much. In Veracode's 2025 study, security grades of generated code stayed flat even as models got smarter (2025 GenAI Code Security Report). AI is strong at "working code," not at guaranteeing "a structure that doesn't break." Use its speed, but always pass through verification gates (scan, test, review).

So this is not a matter of "attention" but of a structure that never surfaces. That is exactly why mechanical detection — not vigilance — is what closes it (§6).

4. Authenticated-only isn't always a bug — telling it apart from a shared table

This is the part I most want to state honestly. "Not owner-scoped" does not mean "vulnerable." Tables where authenticated-only is legitimate genuinely exist.

-- reference table: fine for every logged-in user to read (not a bug)
create table public.countries ( code text primary key, name text not null );
alter table public.countries enable row level security;

create policy "all authenticated can read countries"
on public.countries for select
to authenticated
using ( true );   -- or auth.role() = 'authenticated'. Legitimate for shared master data

Shared reference data — countries, categories, feature flags, public posts — is fine for everyone to read. Writing auth.uid() = user_id here would be the actual mistake (no one could read it).

So how do you tell them apart? The test is simple.

An ownership column present, but only the session checked — that combination is the danger signal to verify. This is precisely why the finding should be treated as medium, non-blocking, "verify intent" — not as a confirmed exploit. The field study reports "9.2%" as "to verify," not "confirmed leak," to keep exactly this honesty.

5. Writing owner scope correctly (a fix catalog)

Once it's a danger signal, bind the predicate to the owner. Case by case:

5.1 Basics: read and write only your own rows

-- read: only the owner's rows
create policy "owners read their notes"
on public.notes for select
to authenticated
using ( (select auth.uid()) = user_id );

-- insert: can only create rows with your own id (WITH CHECK is required)
create policy "owners insert their notes"
on public.notes for insert
to authenticated
with check ( (select auth.uid()) = user_id );

-- update: update a visible row, keeping your own id (both USING and WITH CHECK)
create policy "owners update their notes"
on public.notes for update
to authenticated
using ( (select auth.uid()) = user_id )
with check ( (select auth.uid()) = user_id );

Two points:

1. Wrap auth.uid() in (select auth.uid()). This is Supabase's recommended performance idiom: it evaluates the function once in the initial plan (cached) instead of re-evaluating per row, which is orders of magnitude faster on large tables. Plain auth.uid() = user_id falls into the "correct but slow" trap (Supabase: Row Level Security; details in RLS performance optimization).
2. Write WITH CHECK alongside every write. USING is "which rows are visible"; WITH CHECK is "which values may be written." Forget it and you open a write bypass where a caller can create rows with someone else's user_id.

5.2 Multi-tenant: scope by membership

For org-level rather than personal data, go through membership.

-- only rows whose notes.org_id is one of the caller's orgs
create policy "members read org notes"
on public.notes for select
to authenticated
using (
  org_id in (
    select org_id from public.memberships
    where user_id = (select auth.uid())
  )
);

Tenant isolation is a generalization of owner scoping; patterns are in multi-tenant RLS design.

5.3 Confusing but correct — shapes that do authorize

The following predicates are easy for a naive scanner to misread as "authentication only," but they do authorize correctly (these are the false-positive classes the field study drove out). If your policy matches these, there is nothing to fix.

-- ① participant binding: the caller is one of the parties (DMs) — legitimate
using ( (select auth.uid()) in (sender_id, receiver_id) )

-- ② case-insensitive email binding — legitimate
using ( lower(email) = lower((select auth.jwt()) ->> 'email') )

-- ③ service_role gate (backend only) — irrelevant to normal users, legitimate
using ( auth.role() = 'service_role' )

-- ④ RBAC / claim delegation (a different authorization layer) — not the session-only gap
using ( (select auth.jwt()) ->> 'user_role' = 'admin' )

① and ② bind the caller to the row, so they are authorized. ③ is backend-only, and service_role bypasses RLS entirely, so it is irrelevant to a normal user's path. ④ delegates to a role/claim — a different authorization layer from the session-only hole (RBAC design).

6. Find it mechanically — paste-only checker / CLI / pgTAP

Confirming "are our policies OK?" by vigilance every time doesn't scale. Mechanize it three ways.

Way 1: paste it in the browser (no install, SQL never leaves the page)

If you just want to judge one policy, the fastest path is the free RLS checker that runs entirely in your browser. Paste using (auth.role() = 'authenticated') and it classifies it as "authenticated only (verify)"; paste using ((select auth.uid()) = user_id) and it says "owner-scoped (OK)." The classification runs 100% in the browser; the SQL is never sent anywhere.

Way 2: scan the whole repo

Sweep supabase/migrations/**.sql and surface every policy with this pattern.

# no install, no config. Runs locally and contacts nothing.
npx @aegiskit/cli scan

This is the rls/policy-not-owner-scoped rule of the OSS scanner Aegis used in the field study above: it flags policies that "prove a session exists but never scope rows, on a table that has an ownership column" (detection details).

Way 3: turn it into a pgTAP regression test

Once found and fixed, lock it so it never regresses: prove, in CI, that while logged in as user A, user B's rows return zero (pgTAP RLS regression tests).

The honest scope — a tool sees the shape; a human sees the intent

Don't blur this line. No static or dynamic tool can prove your authorization is correct. A tool sees the shape of the predicate, not the meaning of your business rules or data model. "Is this table really shared reference data?" "Is the tenant isolation correct by design?" "Is privilege escalation possible?" — only human review and audit can answer those. Zero findings means "you didn't step on the common traps," not "your authorization is correct." A tool mechanizes detection so people can focus on the hard judgments; it is a complement to design and review, not a replacement.

7. A checklist for teams and clients

Whether it's outsourced code or code you had AI write, confirm at least this before production. Framed so a non-expert can judge it.

From a client's viewpoint, the single most effective question is "What happens if I hit someone else's ID?" If they can't answer immediately, authorization verification is likely not built into the design. A good developer answers it on the spot.

This "RLS is present but authorization is missing" class is a core pattern I target for detection with my own OSS security tool Aegis (npx @aegiskit/cli scan), and an area I have actually closed in both client and in-house work. Building fast with AI is itself correct. Firming up what you built fast, safely and without leaks — if you need to build that verification, or an authorization review / audit of an existing Next.js × Supabase app, feel free to reach out.