Auth error 500: Database error querying schema

What you see

Signing in fails, usually from supabase.auth.signInWithPassword(), with:

{ "code": 500, "error_code": "unexpected_failure", "msg": "Database error querying schema" }

Sign-up may still work, and other users may sign in fine. If you can read Auth's own log, the line next to the 500 often says converting NULL to string is unsupported.

Why it happens

Auth runs SQL against the auth schema to look up the user, and this message means that lookup failed. There are two usual causes.

NULL values in token columns. Auth expects columns such as confirmation_token, recovery_token, email_change_token_new and email_change to hold an empty string when unused, never NULL. Users created through Auth always get empty strings. Users inserted with your own SQL (a seed script, a hand-written import from another system) often don't, and reading a NULL into those fields fails.

Changes inside the auth schema. Auth connects as supabase_auth_admin and expects to own its tables. Revoking privileges, changing an owner, altering or dropping a column, or adding your own function, view or trigger in auth that this role can't use will break its queries.

A trigger can also be involved. Signing in updates auth.users (for example last_sign_in_at) and writes to auth.sessions and auth.refresh_tokens, so a failing trigger on those tables fails the sign-in too.

How to fix it

Run these in Studio's SQL editor (Dashboard → your project → Database tab → Open Studio).

1. Look for NULL token columns:

   select count(*) from auth.users
   where confirmation_token is null or recovery_token is null
      or email_change_token_new is null or email_change is null;
   

2. If the count is above zero, replace the NULLs with empty strings:

   update auth.users set
     confirmation_token     = coalesce(confirmation_token, ''),
     recovery_token         = coalesce(recovery_token, ''),
     email_change_token_new = coalesce(email_change_token_new, ''),
     email_change           = coalesce(email_change, '')
   where confirmation_token is null or recovery_token is null
      or email_change_token_new is null or email_change is null;
   

   Fix your import script too, so new rows get '' in those columns.

3. Check that Auth still owns its tables. This should return no rows:

   select tablename, tableowner
   from pg_tables
   where schemaname = 'auth' and tableowner <> 'supabase_auth_admin';
   

4. List triggers on auth tables and test any you added (see Database error saving new user for how to reproduce a trigger failure):

   select c.relname as table_name, t.tgname
   from pg_trigger t
   join pg_class c on c.oid = t.tgrelid
   where c.relnamespace = 'auth'::regnamespace and not t.tgisinternal;
   

5. Move any objects you created in auth into your own schema. If Auth's own tables have lost privileges or changed owner and you can't put them back, restore the project to a point before the change.

On SupaHost

• If you moved users from another system with your own SQL rather than the Import tab, start with step 1.
• On Business and Enterprise, sudo supahost-logs app-one auth over SSH shows Auth's log, including the SQL error behind the 500.
• To roll back a schema change, use the Backups tab. A restore replaces one project's database without touching the others on the instance. See Restoring a project.

Related

• Users and the auth schema
• Database error saving new user
• Moving Auth users between projects
• Restoring a project