Why auth.uid() = user_id fails in Supabase RLS: uuid vs text

TL;DR: auth.uid() returns a uuid — it parses the verified JWT's sub claim into a Postgres uuid. A text user_id column can't be compared to that, because Postgres has no implicit cast between uuid and text in either direction. So using ( auth.uid() = user_id ) does not quietly evaluate to false — it raises operator does not exist: uuid = text (SQLSTATE 42883), usually the moment you save the policy. The correct fix is to make the ownership column uuid and compare with (select auth.uid()) = user_id, no cast. You only hit the silent "returns nothing, no error" version after you cast around the error with auth.uid()::text = user_id and the stored string isn't the exact canonical uuid.

Why does auth.uid() = user_id throw "operator does not exist: uuid = text"?

Because the two sides have incompatible types and Postgres refuses to guess a cast. auth.uid() is declared RETURNS uuid; your user_id column is text. There is no built-in uuid = text operator, so Postgres errors out with the hint "You might need to add explicit type casts."

The reason so many people meet this as a policy that won't even create — rather than a query-time error — is that Supabase type-checks the predicate when you save it. The dashboard surfaces it as:

failed to create pg.policies: operator does not exist: uuid = text

(This is the exact symptom reported in supabase/supabase Discussion #7697.) So correct the premise up front: the raw mismatch is a loud error, not a silent empty result. That is the safe direction — the bug fails closed.

This is a different problem from auth.uid() returning NULL. There, no JWT reached Postgres, so auth.uid() is empty and NULL = user_id denies every row. Here, both auth.uid() and user_id are populated — they simply can't be compared as-is. Don't conflate the two; the diagnosis and fix are different.

Why does the query return nothing after I cast it?

Because the obvious way to dodge the error introduces a second, quieter bug. To make the operator resolve, most people write:

using ( auth.uid()::text = user_id )   -- now text = text, so it runs

That is a valid text = text comparison, so Postgres runs it happily — and returns zero rows, with no error, whenever the stored string isn't byte-identical to what auth.uid()::text produces. And auth.uid()::text always yields the canonical form: 36 characters, lowercase, hyphenated, no surrounding whitespace or quotes. If a row holds A1B2... uppercase, or a value that was padded/quoted on insert, or was written from the wrong source field, it compares false for every row. That is the actual "returns nothing" symptom people search for — and it still fails closed (fewer rows, never more).

How do I fix the uuid vs text mismatch?

The root cause and the best fix are the same for both symptoms: the ownership column should be uuid, matching auth.users.id, and compared to auth.uid() with no cast.

-- 1) Drop policies that reference the column first. Otherwise Postgres blocks
--    the type change: "cannot alter type of a column used in a policy definition".
drop policy if exists "Users read their own rows" on public.todos;

-- 2) Convert text -> uuid. The USING clause parses each existing text value into
--    a uuid; it FAILS LOUDLY if any row holds a non-uuid string (that's good — you
--    want to know before you trust the column).
alter table public.todos
  alter column user_id type uuid using user_id::uuid;

-- 3) (recommended) tie ownership to auth.users so orphaned ids can't exist.
-- alter table public.todos
--   add constraint todos_user_id_fkey foreign key (user_id) references auth.users (id);

-- 4) Recreate the policy — now uuid = uuid, no cast, and index-friendly.
create policy "Users read their own rows"
  on public.todos
  for select
  to authenticated
  using ( (select auth.uid()) = user_id );

The (select auth.uid()) wrapper is the Supabase-recommended form: Postgres evaluates it once per statement as a cached initPlan instead of once per row, which matters a lot on big tables (see RLS performance).

Can't migrate the column? Cast in the policy — with eyes open

Sometimes you can't alter the column yet. Casting inside the predicate works, but each direction has a real trade-off:

-- A) cast the FUNCTION to text  -> comparison is text = text
--    + can use a plain btree index on the text user_id column
--    - requires an EXACT canonical lowercase uuid string; brittle to case/whitespace
using ( (select auth.uid())::text = user_id )

-- B) cast the COLUMN to uuid    -> comparison is uuid = uuid
--    + normalizes representation, so valid uuids that differ only in letter case match
--    - the per-row cast defeats a plain text index (you'd need an expression index)
--    - THROWS at runtime if ANY single row holds text that isn't a valid uuid
using ( (select auth.uid()) = user_id::uuid )

One caching gotcha after changing a column's type: PostgREST may keep serving the old type. Reload the schema cache so the Data API sees the new column:

notify pgrst, 'reload schema';

What if my IDs really aren't UUIDs (Clerk, Auth0, custom)?

Then don't force the column to uuid. Providers like Clerk mint subject ids such as user_2abc..., which aren't UUIDs at all — you can't route them through auth.uid(), because casting a non-uuid sub to uuid fails with invalid input syntax for type uuid. Keep user_id as text and compare it to the raw text claim instead:

using ( (select auth.jwt() ->> 'sub') = user_id )   -- text = text, no uuid involved

This is the intended pattern for third-party auth — see Clerk + Supabase RLS. Trying to migrate these ids to uuid will just fail the user_id::uuid cast, which is Postgres correctly telling you they aren't UUIDs.

How do I confirm the mismatch in the SQL editor?

Carefully — the editor runs as the postgres role, which bypasses RLS, so a broken policy can look like it works. Impersonate the authenticated role inside a transaction and inspect the types directly:

begin;
set local role authenticated;
set local request.jwt.claims to '{"sub":"PUT-A-REAL-USER-UUID-HERE","role":"authenticated"}';

select pg_typeof(auth.uid());                          -- uuid
select data_type from information_schema.columns       -- text  <- the mismatch, in black and white
  where table_schema = 'public' and table_name = 'todos' and column_name = 'user_id';
select user_id, auth.uid()::text = user_id as match    -- eyeball true/false per row
  from public.todos limit 20;

rollback;   -- undo the role + claims so they don't leak into later editor queries

If pg_typeof says uuid and data_type says text, that single query pair is your diagnosis. For the full impersonation workflow, see how to test Supabase RLS.

FAQ

Does auth.uid() = user_id return false or throw an error? It throws. Postgres has no uuid = text operator, so the predicate raises operator does not exist: uuid = text (42883) — and because Supabase type-checks the policy on save, you usually see failed to create pg.policies: operator does not exist: uuid = text before the policy even exists. The silent empty-result only appears after you cast to text on both sides.

Why does auth.uid()::text = user_id return an empty result set? Because the stored string isn't the canonical uuid. auth.uid()::text is always 36 chars, lowercase, hyphenated, no whitespace. An uppercase, padded, or quoted stored value compares false for every row, so you get zero rows and no error. Normalize the data or switch the column to uuid.

Is this uuid/text mismatch a security hole? No — on its own it fails closed: you get fewer rows or a hard error, never more rows. The danger is the panic fix. Loosening the policy to USING (true), deleting the auth.uid() check, or disabling RLS turns a fail-closed correctness bug into a fail-open data leak.

Why does the same schema throw error 42501 on INSERT instead? Because on writes the mismatch fails the policy's WITH CHECK clause, which surfaces as new row violates row-level security policy (42501). Same root cause, different SQLSTATE — see new row violates RLS policy.

Does ALTER COLUMN ... TYPE uuid work while a policy uses the column? No. Postgres blocks it with cannot alter type of a column used in a policy definition. Drop the dependent policies first, alter the column, then recreate them — the ordering shown above.

Does Supabase Storage hit the same bug? Yes. storage.objects stores ownership in owner_id, which is text (the older owner column is a deprecated uuid). Comparing owner_id to auth.uid() (uuid) in a storage policy mismatches the same way, so Supabase's own storage-policy examples compare it as auth.uid()::text = owner_id.

Where GuardLayer fits

Be honest about the boundary: GuardLayer is a static scanner. It reads your migration SQL and application code — not your live column types, not the JWT, not the query's result set. It cannot know that user_id is text while auth.uid() is uuid, and it cannot see that a predicate returned zero rows. None of its rules type-check a policy predicate, and a mismatched policy still mentions auth.uid(), so it wouldn't even trip the "no user scope" check. So say it plainly: GuardLayer does not detect this type mismatch, and it doesn't lint storage.objects policies for it either.

Where it genuinely earns its place is the reflexive fix. The panic reaction to "my rows vanished" is to loosen the policy to USING (true), delete the auth.uid() comparison, or run DISABLE ROW LEVEL SECURITY — each of which flips a fail-closed bug into a fail-open leak. Those are exactly the migrations GuardLayer flags at push time: policy-using-true, policy-no-user-scope, rls-explicitly-disabled, and rls-missing-on-table. Fix the mismatch by matching the types — and if you're tempted to reach for USING (true) first, read the USING (true) trap and the complete guide to Supabase RLS.