📄 SKILL.md

← Vault

name: supabase-self-hosted-auth-user-recovery

description: Recover Supabase (GoTrue) admin login on self-hosted Docker VPS when direct DB insertion of users creates hashes GoTrue can't verify. Fix is to use /signup API then update role.

tags: [supabase, gotrue, auth, self-hosted, vps, bcrypt]

updated: 2026-06-02

Supabase Self-Hosted Auth User Recovery

Problem

Admin login fails on self-hosted Supabase (GoTrue on Docker/VPS) even when user exists in auth.users with correct password. Auth returns invalid_grant: Invalid login credentials but the user IS in the database and password is correct.

Root Cause

When a user is inserted directly into auth.users via SQL (bypassing GoTrue's internal hash generation), GoTrue cannot verify the password because:

1. GoTrue uses its own bcrypt implementation to hash passwords

2. Manually inserted hashes (even $2a$12$... PostgreSQL crypt hashes) may not be compatible

3. The /admin/users endpoint (admin JWT required) often fails with 401: invalid JWT when JWT secret has special characters or encoding issues

Solution

Step 1: Delete the broken user

`sql

DELETE FROM auth.identities WHERE user_id = '';

DELETE FROM auth.users WHERE email = '';

`

Step 2: Create user via GoTrue signup API (NOT direct SQL)

`bash

curl -s -X POST "https://your-domain.com/auth/v1/signup" \

-H "Content-Type: application/json" \

-H "apikey: " \

-d '{"email":"user@example.com","password":"yourpassword"}'

`

This creates the user with a GoTrue-compatible hash. GoTrue ALWAYS generates its own hash internally.

Step 3: Update role to admin via SQL

`sql

UPDATE auth.users

SET role = 'supabase_admin',

is_super_admin = true

WHERE email = 'user@example.com';

`

Step 4: Verify login works

`bash

curl -s -X POST "https://your-domain.com/auth/v1/token?grant_type=password" \

-H "Content-Type: application/json" \

-H "apikey: " \

-d '{"email":"user@example.com","password":"yourpassword"}'

`

Key Lessons

1. NEVER insert users directly into auth.users via SQL — always use the GoTrue /signup endpoint. GoTrue generates its own bcrypt hash internally.

2. The /admin/users endpoint is unreliable for password updates — the admin JWT often fails verification due to encoding issues with JWT secrets containing +, /, =. Use signup API instead.

3. GoTrue signup always works even if email already exists — it returns duplicate key error, which confirms the email is taken. Then you can delete and recreate.

4. PostgreSQL crypt() generates $2a$ prefix hashes — these are NOT the same as what GoTrue's bcrypt generates ($2b$). Even with matching cost, GoTrue may not verify them correctly.

Additional Failure Modes Discovered

GoTrue /admin/users JWT fails with "invalid number of segments"

If JWT_SECRET contains + characters (e.g., kETxz0JNJhJyg5UYxbd8zG6g2v9kXTYZGjY+3v7LUKI), the admin JWT used with /admin/users endpoint fails to parse. The fix is to use the /signup flow instead of direct SQL or admin API.

Login funciona mas frontend "blinks" (login + logout imediato)

O loop de login/logout no browser (200 no login → 204 no logout) tem VÁRIAS causas:

1. JWT com role: supabase_admin → PostgREST tenta SET ROLE supabase_admin → falha com 42501 → frontend得不到dados → crash

2. RLS nas tabelas auth.users/public.profiles → SELECT bloqueado para authenticated → frontend não consegue ler perfil → crash

3. auth.instances vazio → GoTrue mal inicializado

4. Erro JS no callback do Supabase client → extensão do navegador ou CSP local

Ver supabase-self-hosted-login-loop-fix para o diagnóstico completo.

Container Diagnostics

`bash

Check auth container logs

docker logs deploy-vps-auth-1 --since 5m

Check auth DB connection

docker exec deploy-vps-auth-1 env | grep GOTRUE_DB

Check user in DB

docker exec deploy-vps-db-1 psql -U postgres -d postgres -c \

"SELECT id, email, role, is_super_admin, length(encrypted_password) FROM auth.users;"

`

Related