name: comercialrs-adm-role-debug

description: Debug and fix /adm access and role-based access in ComercialRS — when AdmGuard redirects to / despite user having admin role in profiles table

tags:

- supabase

- rls

- react

- comercialrs

- auth

Debugging /adm Access in ComercialRS

Trigger Conditions

• - User reports /adm always redirects to / after login
• - User is confirmed admin in profiles table but cannot access admin panel
• - AuthContext shows correct role after refresh but AdmGuard still redirects

Root Causes Found

Bug 1: Wrong column name in AuthContext

AuthContext.tsx queries profiles.user_id but the profiles table in ComercialRS uses id as the column (matching auth.users.id). There is NO user_id column on the profiles table.

`tsx

// WRONG — returns null always

supabase.from('profiles').select('role').eq('user_id', session.user.id)

// CORRECT

supabase.from('profiles').select('role').eq('id', session.user.id)

`

Bug 2: AdmGuard queries wrong table

AdmGuard.tsx queries user_roles table which contains IDs from a different system (Trindade Painel do Corretor user IDs — not the same as Supabase auth user.id). This always returns empty.

`tsx

// WRONG — user_roles.user_id != auth.users.id in this project

supabase.from('user_roles').select('role').eq('user_id', session.user.id).eq('role', 'admin')

// CORRECT — use profiles table

supabase.from('profiles').select('role').eq('id', session.user.id).eq('role', 'admin')

`

Bug 3: profiles table schema

The profiles table in this Supabase project stores role in the role column and uses id (not user_id) matching auth.users.id.

Diagnostic Steps

1. Verify user role in profiles

`bash

SERVICE_KEY=""

curl -s "https://dauftiqcvgaydddoxqhh.supabase.co/rest/v1/profiles?select=id,email,role&eq.id=" \

-H "apikey: $SERVICE_KEY" -H "Authorization: Bearer $SERVICE_KEY"

`

Or via Management API:

`bash

PAT=""

curl -s -X POST "https://api.supabase.com/v1/projects//database/query" \

-H "Authorization: Bearer $PAT" \

-d '{"query": "SELECT id, email, role FROM profiles WHERE id='"'"''"'"';"}'

`

2. Check RLS policies on profiles

`sql

SELECT policyname, cmd, qual FROM pg_policies WHERE tablename = 'profiles';

`

Minimum needed: SELECT policy for authenticated role with qual = true.

3. Test Anon key access

`bash

ANON_KEY=""

curl -s "https://dauftiqcvgaydddoxqhh.supabase.co/rest/v1/profiles?select=id,role&limit=5" \

-H "apikey: $ANON_KEY" -H "Authorization: Bearer $ANON_KEY"

`

Empty [] with 200 = RLS blocking. 401 = invalid key.

4. Browser console check

Open DevTools → Console on comercial.rochasalesseguros.com.br after login:

`

await window.__SUPABASE_CLIENT__.from('profiles').select('id,role').eq('id', '').single()

`

Key Files to Fix

/var/www/comercialrs/src/context/AuthContext.tsx

Line ~72: change .eq('user_id', session.user.id) → .eq('id', session.user.id)

/var/www/comercialrs/src/components/adm/AdmGuard.tsx

Change from('user_roles') → from('profiles') and .eq('user_id', ...) → .eq('id', ...)

Deployment

`bash

cd /var/www/comercialrs && npm run build

sshpass -p '' rsync -avz --delete dist/ root@:/var/www/comercialrs/dist/

sshpass -p '' ssh root@ "nginx -t && systemctl reload nginx"

`

Verification

1. Login as admin user

2. Open DevTools → Application → Local Storage → clear sb- (logout clears it)

3. Login again

4. Check Network tab for profiles?select=role&id=eq. — should return admin

5. Navigate to /adm — should show admin panel, not redirect

Common Pitfalls

• - Do NOT add user_id column to profiles — the project uses id (same as auth.users)
• - Do NOT rely on user_roles table for role checks — its IDs are from external system
• - profiles.role column stores the canonical role: admin, manager, or pre_sales

Bug 4: RLS Blocking Writes on user_roles (Role Change Fails Silently)

Symptom

In /adm → Usuários & Cargos, changing a user's role via the Select dropdown shows error toast "Falha ao atualizar cargo". The role does NOT persist after refresh.

Root Cause

Table user_roles had RLS enabled but only a SELECT policy existed. The frontend code does DELETE + INSERT to change roles — INSERT and DELETE operations were blocked by RLS.

`tsx

// AdmUsers.tsx — changeRole does delete + insert

const { error: delErr } = await supabase.from('user_roles').delete().eq('user_id', userId);

const { error: insErr } = await supabase.from('user_roles').insert({ user_id: userId, role: newRole });

`

Diagnostic

Check existing RLS policies on user_roles:

`bash

curl -s -X POST "https://api.supabase.com/v1/projects//database/query" \

-H "Authorization: Bearer $PAT" \

-d '{"query": "SELECT policyname, cmd FROM pg_policies WHERE tablename = '\''user_roles'\''"}'

`

Expected: SELECT, INSERT, DELETE, UPDATE policies. If only SELECT exists, writes are blocked.

Also test with anon key — empty [] on SELECT doesn't mean table is empty (RLS blocks it):

`bash

curl -s "https://dauftiqcvgaydddoxqhh.supabase.co/rest/v1/user_roles?limit=5" \

-H "apikey: " -H "Authorization: Bearer "

`

Fix

Add missing INSERT, UPDATE, DELETE policies via Management API:

`bash

PAT=""

PROJECT="dauftiqcvgaydddoxqhh"

curl -s -X POST "https://api.supabase.com/v1/projects/$PROJECT/database/query" \

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d '{"query": "ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY"}'

curl -s -X POST "https://api.supabase.com/v1/projects/$PROJECT/database/query" \

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d '{"query": "CREATE POLICY user_roles_insert ON public.user_roles FOR INSERT TO authenticated WITH CHECK (true)"}'

curl -s -X POST "https://api.supabase.com/v1/projects/$PROJECT/database/query" \

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d '{"query": "CREATE POLICY user_roles_delete ON public.user_roles FOR DELETE TO authenticated USING (true)"}'

curl -s -X POST "https://api.supabase.com/v1/projects/$PROJECT/database/query" \

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d '{"query": "CREATE POLICY user_roles_update ON public.user_roles FOR UPDATE TO authenticated USING (true) WITH CHECK (true)"}'

`

Verification

After applying policies, test role change in /adm → Usuários & Cargos. Should show "Cargo atualizado" toast and persist after page refresh.

Also verify data is readable:

`bash

curl -s -X POST "https://api.supabase.com/v1/projects/$PROJECT/database/query" \

-H "Authorization: Bearer $PAT" -H "Content-Type: application/json" \

-d '{"query": "SELECT user_id, role FROM user_roles LIMIT 3"}'

`

Should return rows (not empty) — user_roles contains IDs from Trindade Painel do Corretor system, not Supabase auth IDs.