Fix: Database error granting user (Supabase)

Database error granting user is a 500 from Supabase Auth raised after the password was accepted, while the session is being issued. The usual cause is a trigger on auth.users that runs when sign-in updates last_sign_in_at — and throws. Get the real Postgres error from the logs, then fix the trigger function (security definer, a pinned search_path, schema-qualified names, return new). Don't loosen RLS on the table it writes to.

The response body is as uninformative as they come:

{"code":"unexpected_failure","message":"Database error granting user"}

That exact body is the opening post of discussion #33268, titled "Users unable to login", and the string itself goes back to supabase/auth#541, opened in 2021 and describing it as intermittent on sign-up and sign-in.

It's the sign-in sibling of Database error saving new user. Same class of failure — your code running inside an Auth transaction and breaking it — at a different moment. That's why signup can work perfectly while every login fails.

Why does sign-in fail with "Database error granting user"?

Because signing in writes to the database, and anything attached to those writes can abort the login.

A successful login isn't a read. Auth stamps the user row with the new sign-in time and creates session and refresh-token rows. If you have a trigger on auth.users — especially one on UPDATE — it runs as part of that login. When it raises an error, the grant fails and Auth returns a generic 500 rather than your database internals.

The clearest proof is in the logs from #33268:

error update users last_sign_in field: ERROR: control reached end of trigger procedure without RETURN (SQLSTATE 2F005)`

Read it left to right: Auth was updating last_sign_in_at, a trigger fired, and the trigger function was broken. The login died with it.

Get the real error first

Dashboard → Logs → Auth logs for the failed request, then Postgres logs for the same minute. The appended message is almost always one of these:

• control reached end of trigger procedure without RETURN — a PL/pgSQL trigger function with no return new;. As a Supabase collaborator put it in #33268: "your function has to do RETURN NEW; before the end."
• relation "profiles" does not exist — the function uses an unqualified table name. It resolves fine when you test it in the SQL editor and fails inside the Auth server's session. The thread's other root cause.
• permission denied for table ... — the function is security invoker (the Postgres default), so it runs as Supabase's auth role, which has no business in your public schema.
• new row violates row-level security policy — the same invoker problem, surfacing as RLS instead of a grant.

If you use a custom access token hook, it runs at this same moment but fails with its own messages — see the claims schema error instead.

Restore logins now

If users are locked out, remove the trigger first and fix it second. The same collaborator's advice: "You can delete the trigger by dropping it with the SQL editor."

drop trigger if exists on_auth_user_sign_in on auth.users;

Logins recover immediately. You lose whatever the trigger was recording until you put a working version back.

The fix

create or replace function public.record_login()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.login_events (user_id, signed_in_at)
  values (new.id, new.last_sign_in_at);
  return new;
end;
$$;

create trigger on_auth_user_sign_in
  after update of last_sign_in_at on auth.users
  for each row
  when (old.last_sign_in_at is distinct from new.last_sign_in_at)
  execute function public.record_login();

What each part is doing:

• security definer runs the function as its owner rather than as Auth's role, so it can write to public.
• set search_path = '' stops that elevated function resolving names through a caller-controlled path — the hole explained in SECURITY DEFINER functions and search_path. With an empty path, you must write public.login_events, which also kills the "relation does not exist" failure for good.
• return new — required for a row-level trigger function.
• when (...) means the function only runs when the sign-in time actually changed, not on every update to the user row.

Keep auth triggers small. Anything slow, network-bound or likely to fail — calling an API, sending mail — belongs in a queue or a webhook, not inside the login transaction.

The forum fix that opens the table

Further down #33268, another user — not Supabase — posts a case study that includes relaxing the insert policy from WITH CHECK (auth.uid() = id) to WITH CHECK (true). It's easy to see why it looks like progress, and here is that fix applied to a real migration:

Two findings. The first is the function missing its search_path — the thing that actually needed fixing.

The second is the damage. A policy with no TO clause applies to public — every role — so on any project where anon or authenticated holds an insert grant on login_events, every visitor with your publishable key can now write rows into your login audit table with any user_id and any timestamp. You've turned a record of real logins into a record anyone can forge.

And it didn't help the trigger. A security definer function owned by the table's owner isn't subject to that table's RLS unless you've set force row level security, so the relaxed policy grants nothing the trigger needed — it only widens access for everyone else. using (true) and with check (true) look harmless for exactly this reason.

Quick self-check

-- Every user-defined trigger on the tables a sign-in writes to,
-- and whether its function is safe to run inside Auth.
select c.relname as on_table,
       t.tgname  as trigger_name,
       p.proname as function_name,
       p.prosecdef as security_definer,
       p.proconfig as settings        -- should include search_path=""
from pg_trigger t
join pg_class c on c.oid = t.tgrelid
join pg_namespace n on n.oid = c.relnamespace
join pg_proc p on p.oid = t.tgfoid
where n.nspname = 'auth'
  and c.relname in ('users', 'sessions', 'refresh_tokens', 'identities')
  and not t.tgisinternal;

Any row with security_definer = false, or with no search_path in settings, is a login outage or a privilege problem waiting to happen. Then check the tables those functions write to for with check (true) policies you added while firefighting.

FAQ

Why does signup work but login fails? Different writes, different triggers. Signup inserts into auth.users; login updates it and creates sessions. A trigger on UPDATE never runs during signup.

Why only some users? The trigger's failure is data-dependent — a missing profile row, a null column, a unique constraint that one user's data happens to hit. Check the Postgres log for the specific failing statement.

Is this the same as "Database error saving new user"? Same wrapper, different moment. That one fires on the signup insert; this one fires while issuing a session. The fixes overlap almost entirely.

Can I just disable RLS on the table the trigger writes to? It will make the error go away and expose the table to your publishable key. Fix the function with security definer and a pinned search_path instead.

Should the trigger swallow its own errors so logins never fail? Wrapping the body in exception when others then return new; keeps logins working, at the cost of silently losing the data the trigger was supposed to write. Acceptable for analytics; not for anything you rely on.