📄 SKILL.md

← Vault

name: supabase-vps-container-recovery

description: Recover Supabase self-hosted (deploy-vps) containers when they fail to authenticate to the database after a docker compose operation. Fixes SCRAM-SHA-256 auth failures and JWT mismatches.

triggers:

- container restarting with "password authentication failed for user postgres"

- auth/rest/storage containers in restart loop after docker compose up

- JWSInvalidSignature errors on login

Quick Diagnosis

`bash

Check container status

docker ps -a --filter 'name=deploy-vps' --format '{{.Names}} {{.Status}}'

Check auth logs

docker logs deploy-vps-auth-1 --tail 5

`

Common Causes

1. SCRAM-SHA-256 pg_hba.conf conflict: host all all all scram-sha-256 is evaluated before more specific rules, blocking Docker network connections

2. JWT secret mismatch: GoTrue (GOTRUE_JWT_SECRET) different from PostgREST (PGRST_JWT_SECRET)

3. Container desync after compose: docker compose up -d recreates only that service with new env vars, but DB credentials may not match

Recovery Procedure

Step 1 — Fix pg_hba.conf (if auth can't connect)

`bash

Remove scram-sha-256 rule that's blocking Docker network connections

docker exec deploy-vps-db-1 sh -c 'sed -i "/host all all all scram-sha-256/d" /var/lib/postgresql/data/pg_hba.conf && psql -U postgres -c "SELECT pg_reload_conf();"'

`

Step 2 — Get actual credentials from .env

`bash

Read JWT secret (real value, not masked)

python3 -c "import os; d=open('/docker/deploy-vps/.env').read(); [print(k,v) for l in d.splitlines() for k,v in [l.split('=',1)] if k in ['POSTGRES_PASSWORD','JWT_SECRET','PGRST_JWT_SECRET']]"

Read postgres password directly from running DB container

docker exec deploy-vps-db-1 sh -c 'echo "$POSTGRES_PASSWORD"'

`

Step 3 — Recreate auth container manually

`bash

docker rm -f deploy-vps-auth-1

docker run -d \

--name deploy-vps-auth-1 \

--network deploy-vps_default \

-p 9999:9999 \

--restart unless-stopped \

-e GOTRUE_API_HOST=0.0.0.0 \

-e GOTRUE_API_PORT=9999 \

-e GOTRUE_DB_DRIVER=postgres \

-e GOTRUE_DB_DATABASE_URL="postgres://postgres:{POSTGRES_PASSWORD}@db:5432/postgres" \

-e GOTRUE_DB_SCHEMA=auth \

-e GOTRUE_DB_SEARCH_PATH=auth,public \

-e GOTRUE_SITE_URL=https://rochasalesseguros.com.br \

-e GOTRUE_URI_ALLOW_LIST='' \

-e GOTRUE_DISABLE_SIGNUP=false \

-e GOTRUE_JWT_SECRET={JWT_SECRET} \

-e GOTRUE_JWT_EXP=3600 \

-e GOTRUE_EXTERNAL_EMAIL_ENABLED=true \

-e GOTRUE_MAILER_AUTOCONFIRM=true \

-e API_EXTERNAL_URL=https://rochasalesseguros.com.br \

supabase/gotrue:v2.99.0

`

Step 4 — Recreate rest container manually

`bash

docker rm -f deploy-vps-rest-1

docker run -d \

--name deploy-vps-rest-1 \

--network deploy-vps_default \

-p 3002:3000 \

--restart unless-stopped \

-e PGRST_DB_URI="postgres://postgres:{POSTGRES_PASSWORD}@db:5432/postgres" \

-e PGRST_JWT_SECRET={JWT_SECRET} \

-e PGRST_DB_SCHEMA=public \

-e PGRST_DB_ANON_ROLE=anon \

-e PGRST_VERIFY_JWT=false \

-e DB_INSTALL=false \

postgrest/postgrest:v12.2.3

`

Step 5 — Verify auth + rest are connected

`bash

Auth health

curl http://:9999/health

Rest connection

docker logs deploy-vps-rest-1 --tail 3

`

Step 6 — Create admin user if auth.users is empty

`bash

Generate bcrypt hash

python3 -c "import bcrypt; print(bcrypt.hashpw(b'221109', bcrypt.gensalt(10)).decode())"

Insert admin user

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

INSERT INTO auth.users (id, instance_id, email, encrypted_password, email_confirmed_at, created_at, updated_at, raw_user_meta_data, raw_app_meta_data, last_sign_in_at, role, mfa_challenges, mfa_factors, deleted_at)

VALUES (

gen_random_uuid(),

'00000000-0000-0000-0000-000000000000',

'admin@rochasalesseguros.com.br',

'\$2b\$10\$IO0ia7dvOrSJjW7S1Vom4eRWKZ.R5t7PKya4PIa.TLR6ScZvqaOJq',

now(), now(), now(),

'{\"first_name\":\"Admin\",\"last_name\":\"Rocha Sales\"}',

'{\"provider\":\"email\",\"providers\":[\"email\"]}',

now(), 'authenticated', '{}', '{}', NULL

);"

`

Key Lessons