📄 SKILL.md

← Vault

name: supabase-vps-debug

description: Debug and fix Supabase self-hosted (Docker) on VPS — auth failures, RLS policy issues, schema drift, and profile auto-creation

Supabase Self-Hosted (Docker) — VPS Debug & Fix Guide

Skills for debugging Supabase auth and RLS issues on self-hosted Docker deployments on a VPS.

Quick SSH Access Pattern

`bash

sshpass -p '$PASSWORD' ssh -o StrictHostKeyChecking=no root@$IP "$CMD"

`

Finding the Right Containers

Self-hosted Supabase on VPS often uses a prefix like deploy-vps- (not the default app- prefix). List containers:

`bash

docker ps --format '{{.Names}}' | grep -E 'db|auth|rest|kong|meta'

`

Common container naming: deploy-vps-db-1, deploy-vps-auth-1, deploy-vps-rest-1, deploy-vps-kong-1, deploy-vps-meta-1, deploy-vps-studio-1

Database Access

`bash

Direct psql (if psql installed in container)

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

Find postgres password (from container env — may be masked as *)

docker inspect deploy-vps-db-1 --format='{{range .Config.Env}}{{println .}}{{end}}' | grep POSTGRES

`

Common Schema Inspection Commands

`sql

-- Table structure

\d table_name

-- RLS policies on a table

SELECT polname, polcmd::text FROM pg_policy WHERE polrelid = 'table_name'::regclass::oid;

-- Check if RLS is enabled

SELECT relname, relrowsecurity FROM pg_class WHERE relname = 'table_name';

-- Foreign keys

SELECT conname, contype FROM pg_constraint WHERE conrelid = 'table_name'::regclass::oid;

-- Check if function exists

SELECT proname FROM pg_proc WHERE proname = 'function_name';

-- Check trigger

SELECT tgname, tgenabled FROM pg_trigger WHERE tgname = 'trigger_name';

`

Finding the docker-compose.yml

Common locations to search:

`bash

find /root /home /opt -name 'docker-compose.yml' 2>/dev/null

Also check: ls /opt/project/supabase/

`

Key Debugging Insight: Migrations vs Schema Drift

Symptom: Code expects columns/tables that don't exist in the database, or RLS policies block operations that should work.

Root cause: Migrations exist in /opt/project/supabase/migrations/ but were never applied to the database.

Diagnosis:

`sql

-- Check applied migrations (Supabase auth migrations only = problem)

SELECT * FROM auth.schema_migrations ORDER BY version;

-- If only old Supabase migration versions (2017-2023) exist but project migrations

-- from 2026 are missing → migrations were never applied

`

Fix: Apply missing migrations or do direct SQL correction.

RLS Policy Fix Template

When a table blocks INSERT/UPDATE/DELETE with RLS but the code expects it to work:

`sql

-- 1. Check current policies

SELECT polname, polcmd::text FROM pg_policy WHERE polrelid = 'table_name'::regclass::oid;

-- 2. Drop permissive "allow all" if present

DROP POLICY IF EXISTS "allow all" ON table_name;

-- 3. Replace with correct policies

ALTER TABLE table_name DISABLE ROW LEVEL SECURITY;

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated can view" ON table_name FOR SELECT TO authenticated USING (true);

CREATE POLICY "Users can insert own" ON table_name FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);

`

Auto-Create Profiles on Signup (Gotrue/Auth Hook)

When signups create auth users but profiles fail to create:

`sql

-- 1. Ensure profile INSERT allows the user to create their own profile

DROP POLICY IF EXISTS "Admins can insert profiles" ON public.profiles;

CREATE POLICY "Users can insert own profile" ON public.profiles FOR INSERT TO authenticated

WITH CHECK (auth.uid() = user_id);

-- 2. Add integrity constraints

ALTER TABLE public.profiles ADD CONSTRAINT profiles_user_id_unique UNIQUE (user_id);

ALTER TABLE public.profiles ADD CONSTRAINT profiles_user_id_fkey

FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE;

-- 3. Create auto-profile trigger

CREATE OR REPLACE FUNCTION public.handle_new_user()

RETURNS trigger AS $$

BEGIN

INSERT INTO public.profiles (id, user_id, email, full_name, is_active)

VALUES (

gen_random_uuid(),

NEW.id,

NEW.email,

COALESCE(NEW.raw_user_meta_data->>'full_name', ''),

true

)

ON CONFLICT (user_id) DO NOTHING;

RETURN NEW;

END;

$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;

CREATE TRIGGER on_auth_user_created

AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

`

Reading Gotrue/Auth Logs

`bash

docker logs deploy-vps-auth-1 --tail 100 | grep signup

`

Key log messages:

Bucket must exist in storage.buckets table:

`sql

SELECT * FROM storage.buckets;

-- If 'documents' not found → INSERT it:

INSERT INTO storage.buckets (id, name, public) VALUES ('documents', 'documents', false);

`

Storage paths in code: Use storage_path column (not file_path). Format: clients/{client_id}/{filename}.

Pitfalls

1. Role ENUM cast: When using has_role() in policies, cast the role: has_role(auth.uid(), 'admin'::app_role)

2. SECURITY DEFINER functions: has_role must use SET search_path = public, auth — not just public — because uid() lives in the auth schema

3. auth.users vs public.users: The auth schema houses the actual users table — always reference auth.users(id)

4. ON CONFLICT DO NOTHING: Use when creating auto-profiles to handle race conditions on signup

5. Container naming prefixes: On VPS deployments, containers may use custom prefixes (deploy-vps-, root-) not the default app- prefix

6. Container env vars masked: docker inspect often shows * for passwords — check the actual running container's env or mount a shell to read them

7. PostgREST caches RLS: Always docker restart deploy-vps-rest-1 after policy changes

8. uid() is PostgREST, not PostgreSQL: Never available in direct psql connections

9. Trigger doesn't create user_roles: The on_auth_user_created trigger only creates profiles — user_roles must be inserted separately

10. Multi-column UNIQUE + NULL = problems: PostgreSQL allows multiple NULL rows in unique indexes — always verify constraints with pg_constraint

12. Storage JWT mismatch causes 400 "Invalid Compact JWS": The storage container's PGRST_JWT_SECRET must match the Supabase project's JWT_SECRET. If storage uses keys from project A but the app uses tokens from project B, all storage requests fail. Always get the JWT_SECRET from the same project whose anon/service keys are in the .env.

13. Kong key-auth plugin is inactive on storage routes: Do not trust Kong to validate storage API keys — it passes them through. Auth happens inside the storage container.

14. Storage uses local MinIO, not Supabase Cloud storage: The /storage/v1/ path routes to http://storage:5000 (Tusky), not to Supabase's cloud storage. These are separate systems.

16. Functions main/ directory missing → restart loop: Edge Runtime needs /home/deno/functions/main/index.ts (not just /home/deno/functions/manage-users/index.ts). See supabase-edge-runtime-vps-debug skill.

16. Multiple Supabase projects: Users may share service_role keys from project A while the deployed app uses project B. Always extract project ref from frontend bundle first.