name: supabase-signup-profile-debug
description: Debug and fix Supabase signup failures where profile/role creation silently fails — duplicate key errors, disappearing users, and auth flow broken by RLS/trigger conflicts
tags:
- supabase
- postgresql
- rls
- gotrue
- debug
- vps
Debugging Supabase Signup + Profile Creation Failures
Trigger Conditions
When a user reports:
- - Signup returns "duplicate key" on
profiles_user_id_unique - - Users disappear after logout/login
- - Signup says "User already registered" but user wasn't created
- - Profile/role not created after successful auth signup
Root Cause Pattern
Split-insert problem: both a DB trigger on auth.users AND frontend code try to insert the same profile. The trigger fires first (with ON CONFLICT DO NOTHING), then frontend code violates UNIQUE(user_id).
Secondary: missing RLS INSERT policy on user_roles, trigger attached to wrong table, or adminAuth.ts blocking login for users without profile.
Diagnostic Steps
1. Auth logs
`bash
docker logs
`
user_repeated_signup = email already exists. 422 = validation error.
2. Check trigger location
`sql
SELECT trg.tgname, trg.tgenabled, c.relname AS table_name, p.proname
FROM pg_trigger trg
JOIN pg_proc p ON trg.tgfoid = p.oid
JOIN pg_class c ON trg.tgrelid = c.oid
WHERE trg.tgname = 'on_auth_user_created';
`
Must be on auth.users. tgenabled != 'O' means disabled.
3. Check trigger function
`sql
SELECT prosrc FROM pg_proc WHERE proname = 'handle_new_user';
`
Must have ON CONFLICT (user_id) DO NOTHING.
4. Check RLS policies
`sql
SELECT tablename, policyname, cmd, with_check FROM pg_policies
WHERE tablename IN ('profiles', 'user_roles');
`
Minimum: profiles INSERT with WITH CHECK (auth.uid() = user_id) AND user_roles INSERT policy.
5. Check frontend signUp code
If code does BOTH auth.signUp() AND profiles.insert() — remove the manual insert, rely on trigger.
Fixes
Fix 1: Idempotent trigger
`sql
CREATE OR REPLACE FUNCTION 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 handle_new_user();
`
Fix 2: user_roles INSERT policy
`sql
CREATE POLICY "Users can create own roles" ON public.user_roles
FOR INSERT TO authenticated
WITH CHECK (auth.uid() = user_id);
`
Fix 3: Frontend — remove manual profile insert
Keep only auth.signUp() + user_roles.insert(). Profile is auto-created by trigger.
Docker SQL Transfer Note
Host files are NOT visible inside containers. Use docker cp:
`bash
docker cp /tmp/fix.sql
docker exec
`
Verification
1. Signup new user (single click — double-click gives "User already registered" which is expected)
2. Check profiles and user_roles tables for new rows
3. Logout/login — user should persist in the list