App Finishers

Fix guide · updated 2026-11-17

Supabase "Database error granting user" (500 at sign-in): find the trigger and fix it

Database error granting user · AuthRetryableFetchError 500 · unexpected_failure

People can sign up, but signing in fails. Your app shows Database error granting user, supabase-js reports AuthRetryableFetchError with status 500, and the Network tab shows a 500 on /auth/v1/token. The password was fine. The failure comes right after it: every sign-in writes to your database, and something your database runs on one of those writes failed. Almost always, that's a trigger you or your AI tool added on auth.users or auth.sessions.

What the error means

When Supabase Auth signs someone in, it makes three writes in one transaction: a new row in auth.sessions, a new row in auth.refresh_tokens, and an update of the user's last_sign_in_at in auth.users. If the database refuses any of them, Auth undoes all three and answers:

500 {"code":500,"error_code":"unexpected_failure","msg":"Database error granting user","error_id":"…"}

The update is the write that usually trips. In our test, Postgres logged it as:

UPDATE "users" AS users SET "last_sign_in_at" = $1, "updated_at" = $2 WHERE users.id = $3

So a trigger after update on auth.users runs at every sign-in, not only when someone edits their profile. If its function fails, nobody can sign in. A trigger after insert on auth.sessions, such as a login log, also runs at every sign-in.

Two more things we saw in our test. Nothing is half-done: the failed sign-in left no session and didn't change last_sign_in_at, so once the trigger is fixed, the next sign-in works with no cleanup. And supabase-js 2.119 reports this 500 as AuthRetryableFetchError, with error.code undefined: the message is all your app gets.

1. Find the real error

The app only gets the 500. The reason is in the logs. Make a sign-in fail once more, then open them:

In the Auth logs, the failed sign-in carries the database's error at the end of its line. In our test, a broken trigger on auth.users gave:

error update user`s last_sign_in field: ERROR: relation "profiles" does not exist (SQLSTATE 42P01)

and a broken trigger on auth.sessions gave:

error creating new session: … ERROR: relation "login_history" does not exist (SQLSTATE 42P01)

The Postgres logs show the same error, with the function and the line that raised it:

CONTEXT:  PL/pgSQL function public.sync_profile() line 1 at SQL statement

To list every trigger on Auth's tables, run this in the SQL Editor:

select c.relname as on_table, t.tgname as trigger_name, pg_get_triggerdef(t.oid) as definition
from pg_trigger t
join pg_class c on c.oid = t.tgrelid
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'auth' and not t.tgisinternal
order by 1, 2;

In our test it listed:

sessions | log_login       | CREATE TRIGGER log_login AFTER INSERT ON auth.sessions FOR EACH ROW EXECUTE FUNCTION log_login()
users    | handle_new_user | CREATE TRIGGER handle_new_user AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION handle_new_user()
users    | sync_profile    | CREATE TRIGGER sync_profile AFTER UPDATE ON auth.users FOR EACH ROW EXECUTE FUNCTION sync_profile()

The usual sign-up trigger (AFTER INSERT ON auth.users) doesn't run at sign-in. Look at anything AFTER UPDATE ON auth.users and anything on auth.sessions or auth.refresh_tokens. To read a function's code, run select pg_get_functiondef('public.sync_profile'::regproc); with your function's name.

2. If people are locked out right now

Drop the trigger first. In our test, the next sign-in worked right away:

drop trigger sync_profile on auth.users;

Use the trigger's name and table from the list above. Dropping a trigger keeps its function and your data. Then fix the function and create the trigger again (step 3).

3. Match the error and fix it

These are the errors we got in the logs with the triggers that AI tools and tutorials usually write, and what fixed each one.

relation "profiles" does not exist (42P01)

The trigger's function runs inside Auth's own query, as Auth's database user (supabase_auth_admin), whose search path is the auth schema. A table name without its schema, like profiles, is looked up in auth and isn't found. Adding security definer alone didn't help in our test: the function still used Auth's search path. What worked was both: security definer set search_path = '' and the full name public.profiles.

permission denied for table profiles (42501)

The function names public.profiles correctly but isn't security definer, so it runs with Auth's rights, and Auth's database user has no rights on your tables. Make it security definer, with set search_path = ''.

The fixed function for both cases, the one that let sign-in through in our test:

create or replace function public.sync_profile()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  update public.profiles
  set email = new.email, updated_at = now()
  where id = new.id;
  return new;
end;
$$;

A login log on auth.sessions needs the same two things: security definer set search_path = '', and insert into public.login_history … with the schema. In our test, that version logged the sign-in and let it through.

duplicate key value violates unique constraint "profiles_pkey" (23505)

The trigger fires after insert or update, and its body inserts the profile row. Sign-up created the row, so every sign-in tries to insert it again. Keep two triggers: one after insert that inserts the row for new users, and one after update that only updates it, like the function above.

Your own error message (P0001)

Some apps block sign-ins with a trigger that runs raise exception 'Account suspended'. That message only reaches the logs: in our test, the app got "Database error granting user". To block someone, use Auth's ban instead, from your server with the secret (service_role) key:

await supabase.auth.admin.updateUserById(userId, { ban_duration: '24h' }) // 'none' lifts the ban

In our test, the banned user's sign-in got a clear answer instead of a 500: AuthApiError 400, code user_banned, "User is banned".

Make the trigger run only when it matters

A sign-in only sets last_sign_in_at and updated_at. If your trigger only cares about the email or the user's metadata, name those columns. In our test, even with a broken function behind this trigger, sign-in still worked:

drop trigger if exists sync_profile on auth.users;
create trigger sync_profile
after update of email, raw_user_meta_data on auth.users
for each row execute function public.sync_profile();

A condition, when (old.email is distinct from new.email), worked the same way. Fix the function anyway: in our test, changing the email still hit the broken function and failed with "Error updating user".

4. What else the same trigger breaks

A broken after update on auth.users trigger doesn't only stop password sign-ins. With one in place, each of these failed with a 500 in our test, under other messages:

They all come from the same trigger, so the same fix applies.

Other messages from the same failure

If an AI tool built your app

Give your tool this prompt:

Sign-in to my Supabase app fails with "Database error granting user" (500). Every sign-in updates auth.users (last_sign_in_at) and inserts into auth.sessions, so a trigger on one of those tables is failing. Give me SQL for the Supabase SQL Editor that lists every trigger on tables in the auth schema (pg_trigger with pg_get_triggerdef), and show me the code of each trigger function that runs after update on auth.users or on auth.sessions. Then fix each function: make it security definer with set search_path = '', and write every table with its schema (public.profiles, not profiles). Don't insert a row that sign-up already created; update it instead. Don't raise exceptions to block sign-ins; use auth.admin.updateUserById with ban_duration from the server. If a trigger only needs email or metadata changes, recreate it as "after update of email, raw_user_meta_data on auth.users". Then test a sign-in.

Not quite your error?

Still stuck?

Run the trigger list from step 1 and open the Auth log line of one failed sign-in. The text after its last ERROR: is the real reason, and the Postgres log names the function and the line. If the list shows no trigger of yours on auth.users updates or on auth.sessions, that log line still names what refused the write: start from it.

Other Supabase problems? See Supabase fixes.