name: supabase-self-hosted-pg-hba-auth-fix

description: Fix PostgreSQL auth (scram-sha-256 → trust) and recover GoTrue/PostgREST on Supabase self-hosted Docker after restarts

category: devops

tags: [supabase, postgresql, docker, self-hosted, auth]

created: 2026-06-02

Supabase Self-Hosted PostgreSQL Auth Fix

Context

When deploying Supabase self-hosted on a VPS using Docker, GoTrue (auth container) fails to connect to PostgreSQL after restarts, causing auth/rest containers to crash. The root cause is PostgreSQL's pg_hba.conf authentication method being set to scram-sha-256 which GoTrue cannot satisfy (it has no password configured in its connection string).

Problem

After docker compose down && up or container restarts, the auth and rest containers fail with connection errors. The database appears empty or unreachable.

Solution

Step 1: Identify the REAL pg_hba.conf

PostgreSQL inside the container uses /etc/postgresql/pg_hba.conf, NOT the one at /var/lib/postgresql/data/pg_hba.conf (which gets regenerated from the image on every fresh start).

`bash

Inside the db container, find which hba file is actually used:

docker exec deploy-vps-db-1 psql -U postgres -c "SHOW hba_file;"

The result will be something like /etc/postgresql/15/main/pg_hba.conf.

Step 2: Change auth method to trust

Inside the container (not the bind-mounted directory):

`bash

Edit the actual hba file

docker exec deploy-vps-db-1 sed -i 's/scram-sha-256/trust/g' /etc/postgresql/15/main/pg_hba.conf

Or edit manually with:

`bash

docker exec -it deploy-vps-db-1 vi /etc/postgresql/15/main/pg_hba.conf

Change all scram-sha-256 entries to trust.

Step 3: Reload without restarting PostgreSQL

`bash

docker exec deploy-vps-db-1 psql -U postgres -c "SELECT pg_reload_conf();"

Or send SIGUSR1:

`bash

docker kill -s SIGUSR1 deploy-vps-db-1

Step 4: Verify auth.instances has data

`sql

SELECT * FROM auth.instances;

If empty, insert the default instance:

`sql

INSERT INTO auth.instances (id, uuid, provider, inserted_at)

VALUES ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', 'postgres', NOW());

Step 5: Fix Postgres user search_path

GoTrue connects as postgres user (not supabase_auth_admin). Without auth in the search_path, it can't find auth.identities:

`sql

ALTER ROLE postgres SET search_path TO auth, public, extensions;

Step 6: Grant auth schema permissions

`sql

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA auth TO supabase_auth_admin;

GRANT USAGE ON SCHEMA auth TO supabase_auth_admin;

Step 7: Restart affected containers

`bash

docker restart deploy-vps-auth-1 deploy-vps-rest-1

Step 8: Persist the fix permanently

The change inside the container is lost on image upgrade. To persist:

Option A (recommended): Use a custom Dockerfile:

`dockerfile

FROM supabase/postgres:15.1.0.117

COPY pg_hba.conf /etc/postgresql/15/main/pg_hba.conf

Option B: Mount from host in docker-compose:

`yaml

services:

db:

volumes:

- ./pg_hba.conf:/etc/postgresql/15/main/pg_hba.conf:ro

Why not just use password auth?

GoTrue's connection string is hardcoded to host=db user=postgres with no password. The POSTGRES_PASSWORD env var sets the postgres user password, but the connection method is determined by pg_hba.conf. trust allows any local connection without a password.

Critical rule: NEVER run `docker compose down` on deploy-vps

The db container uses a bind mount (./volumes/db/data:/var/lib/postgresql/data) which survives down but the container gets recreated from image on up. Use docker restart instead to avoid losing data and having the regenerated pg_hba.conf reset your auth changes.

Creating admin users via GoTrue API

Never insert users directly into auth.users with SQL — the encrypted_password field requires proper bcrypt hashing that only GoTrue can do correctly. Use the signup endpoint instead:

`bash

curl -X POST https://rochasalesseguros.com.br/auth/v1/signup \

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

-H "apikey: YOUR_ANON_KEY" \

-d '{"email": "admin@example.com", "password": "Admin123456", "data": {"full_name": "Admin"}}'

Then set role to authenticated (NOT supabase_admin) and manually create entries in public.profiles and public.user_roles.

Edge Function directory structure

Deno Edge Runtime's --main-service flag requires the function to be in a directory named main. If deploying to /opt/rochasales/supabase/functions/, the main function must be at:

/opt/rochasales/supabase/functions/main/index.ts

Not at manage-users/index.ts — you need to create a main/ subdirectory and copy the function code there.

Pitfalls

- pg_hba.conf in data dir gets regenerated: The bind mount at /var/lib/postgresql/data/ means docker compose down && up doesn't wipe data, BUT the pg_hba.conf inside the container at /etc/postgresql/ is NOT the one in the data dir — it's the one from the image. Changes to the data dir's pg_hba.conf are lost on restart.

- Setting role = 'supabase_admin' in auth.users causes login loops: PostgREST reads the role claim and uses it for SET ROLE. If it's supabase_admin, it tries to do things as the postgres superuser, which fails. Always use authenticated.
- Creating users via SQL gives wrong password hash: Use GoTrue's /signup API endpoint. Direct SQL insertion leaves encrypted_password empty or incorrectly hashed, causing permanent login failure.
- Kong strips JWT signatures: The jwt-auth plugin with consumer_name: apikey strips the 3rd part of JWTs. Edge Functions receive 2-part tokens. If verifying JWT signatures, handle this.

Diagnosis: "invalid_grant: Invalid login credentials" from GoTrue

When login fails with invalid_grant and the auth container is healthy, check if the user was created via direct SQL INSERT instead of GoTrue's signup API.

Step 1: Inspect auth logs

`bash

docker logs deploy-vps-auth-1 --tail 50 | grep -E 'invalid_grant|unsupported_grant_type'

Step 2: Check auth.users creation timestamp

`bash

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

"SELECT id, email, created_at, confirmed_at FROM auth.users WHERE email='USER_EMAIL';"

If created_at matches a time when someone ran docker exec psql ... INSERT INTO auth.users, the password hash is wrong.

Step 3: Verify via direct API test

`bash

curl -X POST http://127.0.0.1:8000/auth/v1/token?grant_type=password \

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

-d '{"email":"USER_EMAIL","password":"THE_PASSWORD"}'

If this returns {"error":"invalid_grant","error_description":"Invalid login credentials"} locally but the same credentials worked before a manual INSERT, the hash is corrupt.

Solution

Delete the bad user record and recreate via GoTrue signup:

`bash

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

"DELETE FROM auth.users WHERE email='USER_EMAIL';"

Then create via the API:

`bash

curl -X POST https://rochasalesseguros.com.br/auth/v1/signup \

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

-H "apikey: ANON_KEY" \

-d '{"email":"USER_EMAIL","password":"STRONG_PASSWORD"}'

Warning: GoTrue signup sends a confirmation email. If GOTRUE_MAILER_AUTOCONFIRM=true (no email confirmation needed), the user is immediately usable. Otherwise, manually confirm in the database:

`sql

UPDATE auth.users SET confirmed_at = NOW() WHERE email = 'USER_EMAIL';

Supabase Self-Hosted PostgreSQL Auth Fix

Context

Problem

Solution

Step 1: Identify the REAL pg_hba.conf

Inside the db container, find which hba file is actually used:

Step 2: Change auth method to trust

Edit the actual hba file

Step 3: Reload without restarting PostgreSQL

Step 4: Verify auth.instances has data

Step 5: Fix Postgres user search_path

Step 6: Grant auth schema permissions

Step 7: Restart affected containers

Step 8: Persist the fix permanently

Why not just use password auth?

Critical rule: NEVER run docker compose down on deploy-vps

Creating admin users via GoTrue API

Edge Function directory structure

Pitfalls

Diagnosis: "invalid_grant: Invalid login credentials" from GoTrue

Step 1: Inspect auth logs

Step 2: Check auth.users creation timestamp

Step 3: Verify via direct API test

Solution

Critical rule: NEVER run `docker compose down` on deploy-vps