name: supabase-postgrest-debug

description: Debug Supabase/PostgREST 400 errors and schema mismatches between migrations and live database

category: devops

tags: [supabase, postgrest, postgresql, rls, docker, debug]

created: 2026-04-16

Supabase PostgREST 400 Error Debugging

Common Symptom

Frontend calls to /rest/v1/table return 400 Bad Request, but the error message from PostgREST is opaque. The most common root cause: the database schema doesn't match what the code/migrations expect.

Investigation Checklist

1. Identify the actual database schema vs migration schema

Migrations live in the project's supabase/migrations/ directory. The live database may have a completely different structure if migrations were never applied.

`bash

Check what migrations ARE applied (Supabase internal migration table only — does NOT track project migrations)

docker exec psql -U postgres -d postgres -c "SELECT * FROM auth.schema_migrations ORDER BY version;"

Compare with local migration files

ls supabase/migrations/

Critical insight: auth.schema_migrations only tracks Supabase's internal auth migrations. Project-specific migrations in supabase/migrations/ are NOT tracked there and may never have been applied.

2. Check actual table structure

`sql

-- In the db container

docker exec psql -U postgres -d postgres -c "\d "

docker exec psql -U postgres -d postgres -c "\d " -- if schema prefix exists

-- Check indexes and foreign keys

docker exec psql -U postgres -d postgres -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE '%role%';"

3. Check RLS policies

`sql

-- All policies on a table (readable format)

SELECT polname, polcmd::text, polpermissive as permissive,

CASE WHEN polroles > 0 THEN 'role-based' ELSE 'for role' END as type

FROM pg_policy WHERE polrelid = '

'::regclass::oid;

-- Is RLS enabled?

SELECT relname, relrowsecurity FROM pg_class WHERE relname = '

-- Check if there's a permissive "allow all" policy (dangerous!)

SELECT polname FROM pg_policy WHERE polrelid = '

'::regclass::oid AND polname = 'allow all';

-- All policies with full details (joined)

SELECT polname, polcmd::text, polpermissive,

pg_get_expr(polqual, '

'::regclass) as check_expression,

pg_get_expr(polwithcheck, '

'::regclass) as with_check

FROM pg_policy WHERE polrelid = '

'::regclass::oid;

4. Find the Docker compose project name

Container names don't always match the project directory name. Docker compose prefixes containers with the directory name by default, but this can be overridden.

`bash

docker ps --format "{{.Names}}" | grep -E 'db|postgres|rest|supabase'

docker inspect --format='{{.Config.Labels["com.docker.compose.project"]}}'

5. Check PostgREST logs

`bash

docker logs --tail 50

Look for: connection errors to db, schema cache loaded count, connection refusals.

6. Get PostgREST DB URI and role

`bash

docker inspect --format='{{range .Config.Env}}{{println .}}{{end}}' | grep -iE 'pgrst|postgres|db'

7. Find database credentials

`bash

Check environment files

cat /path/to/project/.env

cat /path/to/project/.env.production

In container env (if exposed)

docker exec env | grep -i postgres

Common Patterns and Fixes

Pattern: "column does not exist" 400 error

The table has different columns than the code expects. Frontend sends custom_role_id but table has role_id.

Fix: Recreate the table with the correct schema (or apply the missing migration).

`sql

-- Step 1: Backup

CREATE TABLE IF EXISTS public.

_old AS SELECT * FROM public.

;

-- Step 2: Drop and recreate

DROP TABLE IF EXISTS public.

CASCADE;

CREATE TABLE public.

(

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

correct_column UUID NOT NULL REFERENCES public.other_table(id),

...

);

ALTER TABLE public.

ENABLE ROW LEVEL SECURITY;

Pattern: "new row violates row-level security policy" on INSERT/DELETE

RLS policies block the operation. Usually means the INSERT/DELETE policy requires a role check that fails.

Fix: Create the required role/type/function, then add policies.

`sql

-- Create app_role type

CREATE TYPE public.app_role AS ENUM ('admin', 'moderator', 'user');

-- Create user_roles table

CREATE TABLE IF NOT EXISTS public.user_roles (

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,

role app_role NOT NULL,

UNIQUE (user_id, role)

);

ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

-- Create has_role function

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role app_role)

RETURNS boolean

LANGUAGE sql

STABLE

SECURITY DEFINER

SET search_path = public

AS $$

SELECT EXISTS (

SELECT 1 FROM public.user_roles

WHERE user_id = _user_id AND role = _role::text

)

$$;

-- Then add policies using has_role()

CREATE POLICY "Admins can insert" ON public.my_table

FOR INSERT TO authenticated

WITH CHECK (public.has_role(auth.uid(), 'admin'));

Pattern: "allow all" RLS policy

The table has POLICY "allow all" FOR ALL which is overly permissive but also means the real policies were never created. Replace it:

`sql

ALTER TABLE public.

DISABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "allow all" ON public.

;

ALTER TABLE public.

ENABLE ROW LEVEL SECURITY;

-- Then recreate proper policies

SSH + Docker Debug Workflow

`bash

1. Connect to VPS

sshpass -p '' ssh -o StrictHostKeyChecking=no root@

2. List containers

docker ps --format "{{.Names}}\t{{.Image}}"

3. Find db container (may not be named "db")

docker ps --format "{{.Names}}" | grep -E 'postgres|db|supabase'

4. Run SQL

docker exec psql -U postgres -d postgres -c ""

5. Check logs

docker logs --tail 30

Key Files to Check

Path	Purpose
------	---------
`supabase/migrations/`	Project-specific migration files (check if applied)
`supabase/config.toml`	Supabase project config
`.env` / `.env.production`	Environment variables including DB credentials
`docker-compose.yml`	Service definitions
`auth.schema_migrations`	Only Supabase internal migrations (NOT project migrations)

Pattern: "User already registered" on signup

This error appears in docker logs deploy-vps-auth-1 when:

1. Double-click on frontend — two rapid /signup requests; first creates user, second fails because email already exists in auth.users

2. Confirmation email not sent — if auth.users has the user but app shows error, check email_confirmed_at (NULL = pending confirmation)

Debug:

`bash

docker logs deploy-vps-auth-1 --since '2026-04-16T18:35:00' 2>&1 | grep -i 'already'

Database fixes needed for signup flow:

`sql

-- 1. user_roles needs INSERT policy for authenticated users (signup creates own role)

CREATE POLICY "Users can create own roles" ON public.user_roles

FOR INSERT TO authenticated

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

-- 2. profiles needs INSERT policy for own profile (with trigger fallback)

CREATE POLICY "Users can insert own profile" ON public.profiles

FOR INSERT TO authenticated

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

-- 3. Auto-create profile on signup via trigger (handles future new users)

-- Captures full_name from auth.users raw_user_meta_data

CREATE OR REPLACE FUNCTION public.handle_new_user()

RETURNS trigger

LANGUAGE plpgsql

SECURITY DEFINER SET search_path = public

AS $$

BEGIN

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

VALUES (

new.id,

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

);

RETURN new;

END;

$$;

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();

Pattern: "User already registered" — frontend double-click prevention

If signup returns success but user sees error, or vice versa, it may be a double-click race condition:

- First click → auth.users INSERT succeeds
- Second click (rapid) → "already registered" error
- Frontend may show "Sucesso" for both (INSERT to user_roles fails silently)

Fix on frontend — disable button during submission:

`typescript

const [submitting, setSubmitting] = useState(false);

const handleSubmit = async (data) => {

if (submitting) return;

setSubmitting(true);

try {

await supabase.auth.signUp({ email: data.email, password: data.password });

// show success

} finally {

setSubmitting(false);

}

};

Pattern: Missing profile for existing users (pre-trigger)

If handle_new_user trigger was added after users already existed, those users have no profiles entry. Repair manually:

`sql

-- Find users without profiles

SELECT a.id, a.email, a.raw_user_meta_data ->> 'full_name' as name

FROM auth.users a

LEFT JOIN public.profiles p ON p.user_id = a.id

WHERE p.id IS NULL;

-- Insert missing profiles

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

SELECT a.id, a.id, COALESCE(a.raw_user_meta_data ->> 'full_name', '')

FROM auth.users a

LEFT JOIN public.profiles p ON p.user_id = a.id

WHERE p.id IS NULL;

Pattern: Repair users missing from user_roles

`sql

-- Find users without user_roles entry

SELECT a.id, a.email

FROM auth.users a

LEFT JOIN public.user_roles ur ON ur.user_id = a.id

WHERE ur.id IS NULL;

-- Insert with default role (replace and 'admin')

INSERT INTO public.user_roles (id, user_id, role)

VALUES (gen_random_uuid(), '', 'admin');

When All Else Fails

If the frontend and database are completely out of sync and migrations were never applied:

1. Compare every table in supabase/migrations/*.sql with the actual pg_tables

2. Create a delta SQL script to bring the schema in sync

3. Check for FK constraints, unique constraints, and index differences

4. Rebuild RLS policies from scratch referencing has_role() function