name: supabase-cloud-auth-debug

description: Debug "Database error querying schema" on Supabase Cloud when auth schema was manually created

tags: [supabase, auth, debugging, gotrue]

Supabase Cloud Auth Debug — "Database error querying schema"

Symptom

Login attempts return {"code":500,"error_code":"unexpected_failure","msg":"Database error querying schema","error_id":"..."} on Supabase Cloud (hosted).

Root Cause

The auth schema was created manually (e.g., via SQL) without running Supabase's internal GoTrue setup. The tables exist but GoTrue's internal stored procedures (gotrue_add_user, gotrue_update_user, check_token_exists, etc.) are missing.

Key Diagnostic Findings

What WORKS on Supabase Cloud:

• - Management API POST /v1/projects/{ref}/database/query — runs raw SQL ✅
• - auth.users table read/write via management API ✅
• - PostgREST API for non-auth tables ✅
• - auth.schema_migrations table exists with all migrations ✅
• - auth.instances table exists ✅
• - bcrypt password hashing works ✅

What FAILS:

• - GoTrue admin API POST /auth/v1/admin/users — "Database error checking email" or "Database error querying schema"
• - Management API POST /v1/projects/{ref}/authentication/users — returns "Cannot POST" (endpoint blocked on Cloud)
• - Any login attempt for users in the manually-created auth.users — "Database error querying schema"
• - Login works for users created via Supabase Dashboard (not via manual SQL insert)

Diagnostic Commands:

`bash

Check if GoTrue internal functions exist (should return more than 4 functions)

curl -s -X POST 'https://api.supabase.com/v1/projects/{ref}/database/query' \

-H 'Authorization: Bearer {pat}' \

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

-d '{"query": "SELECT proname FROM pg_proc WHERE pronamespace = '\''auth'\''::regnamespace ORDER BY proname;"}'

Should see: gotrue_add_user, gotrue_update_user, check_token_exists, etc.

If only sees: uid, role, email, jwt → GoTrue functions are MISSING

Test login (should return "invalid_credentials" not "database error")

curl -s -X POST 'https://{ref}.supabase.co/auth/v1/token?grant_type=password' \

-H 'apikey: {anon_key}' \

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

-d '{"email": "test@example.com", "password": "wrongpassword"}'

`

Why Password Updates Don't Fix It

bcrypt password updates directly in auth.users via SQL don't help because GoTrue's login flow reads other tables during authentication (refresh_tokens, sessions, flow_state, etc.) and calls internal functions that don't exist.

Only Working Solution

Create users via Supabase Dashboard UI (not via SQL or API). The Dashboard uses the internal GoTrue management path that works correctly.

---

Creating Users via SQL — With instance_id = NULL Trick

On Supabase Cloud where auth was manually created, a DISCOVERED workaround allows creating users directly in auth.users that CAN login:

Step 1: Generate bcrypt hash

`python

import bcrypt

h = bcrypt.hashpw('PASSWORD'.encode(), bcrypt.gensalt(rounds=10, prefix=b'2a'))

print(h.decode())

Output: $2a$10$...

`

Step 2: Insert user with instance_id = NULL

`bash

curl -s -X POST 'https://api.supabase.com/v1/projects/{ref}/database/query' \

-H 'Authorization: Bearer {pat}' \

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

-d '{

"query": "INSERT INTO auth.users (id, instance_id, aud, role, email, encrypted_password, email_confirmed_at, created_at, updated_at, raw_app_meta_data, raw_user_meta_data, is_sso_user, is_anonymous) VALUES ('\''{uuid}'\'', NULL, '\''authenticated'\'', '\''authenticated'\'', '\''email@example.com'\'', '\''$2a$10$hash'\'', now(), now(), now(), '\''{\"provider\": \"email\", \"providers\": [\"email\"]}'\''::jsonb, '\''{\"name\": \"Name\"}'\''::jsonb, false, false);"

}'

`

CRITICAL: instance_id must be NULL, NOT 00000000-0000-0000-0000-000000000000.

• - instance_id = 00000000-... → Login returns "Database error querying schema" ❌
• - instance_id = NULL → Login returns "Invalid login credentials" ✅ (progress! means auth works but password may be wrong)

Then UPDATE the password hash:

`bash

curl -s -X POST 'https://api.supabase.com/v1/projects/{ref}/database/query' \

-H 'Authorization: Bearer {pat}' \

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

-d '{"query": "UPDATE auth.users SET encrypted_password = '\''$2a$10$hash'\'' WHERE id = '\''{uuid}'\'';"}'

`

---

Admin Panel (/adm) — user_roles, NOT profiles.role

The AdmGuard component (in src/components/adm/AdmGuard.tsx) checks:

`sql

SELECT role FROM user_roles WHERE user_id = ? AND role = 'admin'

`

It does NOT check profiles.role. If a user has role='admin' in profiles but no row in user_roles, they see a blank page at /adm (the guard redirects to /).

Fix:

`bash

curl -s -X POST 'https://api.supabase.com/v1/projects/{ref}/database/query' \

-H 'Authorization: Bearer {pat}' \

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

-d '{"query": "INSERT INTO user_roles (user_id, role) VALUES ('\''{user_uuid}'\'', '\''admin'\'');"}'

`

After adding, user must re-login to get updated session claims.

Prevention

Never create auth schema objects manually on Supabase Cloud. All auth management must go through:

1. Supabase Dashboard UI

2. The official Auth Management API (which uses internal GoTrue, not the management API endpoint)

3. Supabase CLI (supabase auth commands)

---

Supabase SELF-HOSTED (deploy-vps) — Auth User Creation

Critical Discovery

On Supabase Self-hosted (deploy-vps stack), directly INSERTING users into auth.users via SQL with a manually-generated bcrypt hash DOES NOT WORK — login returns invalid_grant even though:

• - GoTrue reaches password verification (returns 400 not 500)
• - The user exists in auth.users
• - The bcrypt hash is correctly stored

But creating via /signup → works perfectly and login succeeds.

Root cause: GoTrue uses its own internal bcrypt implementation when creating via /signup. When you insert manually with a Python/PostgreSQL-generated hash, GoTrue's bcrypt verification may not match (even $2a$ prefix).

Working Pattern: User Creation on Self-Hosted

Step 1: Create user via /signup (GoTrue's own flow)

`bash

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

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

-H "apikey: {ANON_KEY}" \

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

`

Returns access_token + user object. Login now works.

Step 2: Elevate to admin if needed (via direct SQL)

`bash

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

UPDATE auth.users

SET role = 'supabase_admin',

is_super_admin = true

WHERE email = 'user@example.com';

"

`

For existing users: Use GoTrue Admin API (not direct SQL)

`bash

Get JWT secret from .env

JWT_SECRET="your-jwt-secret-from-env"

Update password via GoTrue admin endpoint

curl -s -X PUT "https://your-domain.com/auth/v1/admin/users/{user_id}" \

-H "Authorization: Bearer ${JWT_SECRET}" \

-H "apikey: {ANON_KEY}" \

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

-d '{"password":"NEW_PASSWORD"}'

`

⚠️ JWT must be the GOTRUE_JWT_SECRET, not anon key. Must have 3 segments (header.payload.signature) — if it only has 2 segments, it's the raw secret which won't work.

Direct SQL password update — only as fallback

`bash

Use PostgreSQL's crypt() with gen_salt('bf', 12) to generate $2a$ hash

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

"UPDATE auth.users SET encrypted_password = crypt('PASSWORD', gen_salt('bf', 12)) WHERE id = 'UUID';"

`

Then restart auth container to clear any cache.

GoTrue Internal Variables (Self-Hosted deploy-vps)

`

GOTRUE_DB_DATABASE_URL=postgres://postgres:*@db:5432/postgres

GOTRUE_DB_SEARCH_PATH=auth,public

GOTRUE_DB_SCHEMA=auth

GOTRUE_JWT_SECRET=kETxz0...UKI= # from .env

`

Auth Instances — Must Exist

`sql

-- Ensure auth.instances has the default instance

INSERT INTO auth.instances (id, uuid, raw_user_meta_data, created_at, updated_at)

VALUES ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '{}', now(), now())

ON CONFLICT (id) DO NOTHING;

`

Diagnostic: Test if Auth is Working

`bash

Create a test user via signup

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

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

-H "apikey: {ANON_KEY}" \

-d '{"email":"test999@example.com","password":"testpassword"}'

If that succeeds, test login

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

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

-H "apikey: {ANON_KEY}" \

-d '{"email":"test999@example.com","password":"testpassword"}'

`

If login works for the new user but not for an old manually-inserted user → the old user's hash is the problem.

Key Files (Self-Hosted deploy-vps)

• - .env: POSTGRES_PASSWORD, JWT_SECRET, ANON_KEY, GOTRUE_DB_DATABASE_URL
• - docker-compose.yml: All service configs (kong, auth, rest, db)
• - pg_hba.conf: Set to trust for local connections to eliminate DB auth issues

Relevant Context

• - Supabase Cloud project ref: dauftiqcvgaydddoxqhh
• - PAT: sbp_v0_0cdcd23cdc5c87e8e9c7e1ef10ba2cec931b4580
• - Service Role Key: eyJ_REDACTED

Relevant Context

• - Supabase Cloud project ref: dauftiqcvgaydddoxqhh
• - PAT: sbp_v0_0cdcd23cdc5c87e8e9c7e1ef10ba2cec931b4580
• - Service Role Key: eyJ_REDACTED