Supabase 42501: permission denied for schema auth

Three different attempts, one error:

ERROR: 42501: permission denied for schema auth

A client-side supabase.from('users') pointed at the wrong schema. A view you
created over auth.users that works in the SQL editor and 42501s from the app.
A row level security policy that joins auth.users to check something about the
caller. All the same underlying refusal, and all fixed the same way — which is
not the way most search results suggest.

This is a boundary, not an oversight

Supabase's troubleshooting guide for 42501 errors on the database API
is explicit: the managed schemas — auth, vault — cannot be accessed directly
through the data API for security reasons, and are reachable only through
security definer functions.

The auth schema holds auth.users, which contains every account's email,
phone number, hashed password, provider identities, and raw metadata. The API
roles are the ones your browser key resolves to. Keeping one out of reach of the
other is the point.

That is why this error behaves differently from its more familiar sibling. When
anon loses its grants on the public schema you have a broken project, and
restoring the grants is the fix — that case is
permission denied for schema public.
When anon is refused on auth, the project is behaving correctly.

So the tempting one-liner:

-- Do not do this.
grant usage on schema auth to anon, authenticated;

does make the message disappear. It also means the only thing standing between a
key published in your JavaScript bundle and the user table is whatever table
grants remain. Every subsequent mistake — a permissive policy, a view, a
function that forgets to filter — becomes a full account dump.

Where each failure actually comes from

A client query against auth.users

supabase.from('users') resolves against the exposed schema (public by
default), so it usually fails with relation "public.users" does not exist
rather than 42501. Explicitly reaching for the auth schema — through
.schema('auth'), a Postgres view, or raw SQL over a direct connection — is what
produces the permission error.

There is no configuration that makes this work safely. Move the data instead.

A view over auth.users

This one deserves care, because the intuitive version of the story is backwards
and the backwards version is the dangerous one.

create view public.user_emails as
  select id, email from auth.users;

PostgreSQL's default for views is definer semantics: the view's underlying
query is checked against the view owner's privileges, not the caller's. So this
view, created by a privileged role, does not 42501 when authenticated selects
from it. It works. It hands out every email address in your project through the
data API, and the auth schema restriction you were relying on is simply routed
around.

I verified both branches against a real Postgres engine — a table in a schema the
calling role has no USAGE on, exposed through two views:

Supabase's own database linter flags exactly this as
0010_security_definer_view,
noting that Postgres's default setting for views is SECURITY DEFINER, which
means they use the permissions of the view's creator rather than the querying
user — and recommending with (security_invoker = on) so RLS applies normally.

Which reframes the error. If your view over auth.users returns 42501, it is
almost certainly declared security_invoker = true — that is, it is the
correctly configured view, and the boundary is doing its job. The fix is not to
remove security_invoker; that would trade an error message for a data leak.
The fix is one of the two patterns below.

A policy that joins auth.users

-- 42501 when evaluated as anon/authenticated.
create policy "admins can read everything"
on documents
for select
to authenticated
using (
  exists (
    select 1 from auth.users u
    where u.id = auth.uid()
      and u.raw_app_meta_data ->> 'role' = 'admin'
  )
);

Policies are evaluated as the calling role. The calling role cannot see the
auth schema, so the policy itself errors rather than returning false.

Note the asymmetry that confuses people: auth.uid(), auth.jwt() and
auth.role() are functions that Supabase grants to the API roles, so calling
them from a policy is fine. Selecting from the auth.users table is not. If
you need a claim, read it out of the JWT you already have:

using ( (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin' )

That version needs no access to the auth schema at all, and it is cheaper —
no subquery per row.

If you want to see the difference between those two policies actually execute
rather than take my word for it, both run in the
Supabase RLS Playground, which applies your
policies against a real Postgres engine in the browser and runs them as anon,
as two signed-in users and as service_role.

The fix: a security definer function

When you genuinely need data that only lives in auth.users, expose the minimum
through a function that runs as its owner:

create or replace function public.current_user_email()
returns text
language sql
security definer
set search_path = ''
stable
as $$
  select email from auth.users where id = (select auth.uid());
$$;

revoke all on function public.current_user_email() from public;
grant execute on function public.current_user_email() to authenticated;

Four things make this safe, and all four matter:

1. security definer — the function body runs with the owner's privileges, so it can read the auth schema.
2. set search_path = '' — pins resolution so a caller cannot shadow auth.users with an object of their own. A security definer function without a pinned search path is a privilege escalation waiting to be found. Fully qualify every name in the body when you do this.
3. The where clause — the function returns the caller's row, not an arbitrary one. A definer function that takes a user id parameter and returns that user's email is a lookup oracle for your whole user table.
4. revoke then grant execute — you decide who can call it, rather than inheriting public.

The better fix: stop needing the auth schema

Most applications that reach for auth.users want a display name, an avatar and
an email next to their own rows. Join a table you own instead:

create table public.profiles (
  id          uuid primary key references auth.users on delete cascade,
  email       text,
  full_name   text,
  avatar_url  text,
  updated_at  timestamptz default now()
);

alter table public.profiles enable row level security;

create policy "read own profile"
on public.profiles
for select
to authenticated
using ( (select auth.uid()) = id );

-- Populate it as accounts are created.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, email, full_name, avatar_url)
  values (
    new.id,
    new.email,
    new.raw_user_meta_data ->> 'full_name',
    new.raw_user_meta_data ->> 'avatar_url'
  );
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

Now every query your client makes stays inside public, joins work normally,
RLS applies the way it does everywhere else, and the auth schema stays sealed.

Two operational notes. The trigger runs inside the signup transaction, so if it
raises, the signup fails with Database error saving new user — a distinct
failure with its own causes, covered in
the trigger rollback fix.
And existing accounts need a one-time backfill; the trigger only fires on new
inserts.

Checklist

• Error mentions schema auth → you are crossing a deliberate boundary. Never grant your way through it.
• Need a claim (role, tenant, email) → read auth.jwt(); no auth schema access required.
• Need a column that only exists on auth.users → security definer function, search_path pinned, scoped to the caller.
• Need it joined to your own data → mirror it into public.profiles with a trigger.
• Error mentions schema public instead → different problem, different fix: permission denied for schema public.
• Query succeeds but returns nothing → not a permission error at all, that is RLS filtering silently.

For the wider set of policy shapes that survive multi-tenancy without reaching
into managed schemas,
RLS policy design patterns is the
companion piece.

Originally published at https://www.iloveblogs.blog