name: supabase-rls-role-permissions-debug

description: Debug RLS blocking authenticated users + fix role_permissions schema mismatches in Supabase Cloud apps

trigger: "400 Bad Request on role_permissions, isAdm not working despite correct DB values"

Supabase RLS + Role Permissions Schema Debug

Symptoms

• - App console shows: GET .../rest/v1/role_permissions?select=...&role_id=in.(...) 400
• - User has role='admin' in profiles and user_roles table, but isAdm is false in the app
• - has_role() RPC works when called directly, but app UI doesn't reflect admin status
• - usePermissions hook returns empty data

Root Causes (two must-check)

1. RLS Policy blocks authenticated users on user_roles

The app's useAuth.ts loads roles via:

`sql

supabase.from("user_roles").select("role").eq("user_id", uid)

`

If RLS on user_roles only allows anon role, authenticated users get empty results.

Check:

`sql

-- via Management API

SELECT pol.polname, pol.polroles::text, pol.polcmd::text,

pg_get_expr(polqual, polrelid::regclass) as qual

FROM pg_policy pol

JOIN pg_class c ON c.oid = pol.polrelid

WHERE c.relname = 'user_roles' AND pol.polcmd = 'r';

`

Fix: Add authenticated policy:

`sql

CREATE POLICY user_roles_read_authenticated ON public.user_roles

FOR SELECT TO authenticated USING (true);

`

2. role_permissions table missing expected columns

The app's usePermissions.ts expects: role_id, section, can_read, can_edit, can_delete.

If the table has a DIFFERENT schema (e.g., only permission_id, role text, no role_id), queries with .in("role_id", ...) return 400 Bad Request (invalid column).

Check column names:

`sql

SELECT column_name, data_type FROM information_schema.columns

WHERE table_name = 'role_permissions' ORDER BY ordinal_position;

`

Fix: Add missing columns

`sql

ALTER TABLE public.role_permissions ADD COLUMN IF NOT EXISTS role_id uuid;

ALTER TABLE public.role_permissions ADD COLUMN IF NOT EXISTS section text;

ALTER TABLE public.role_permissions ADD COLUMN IF NOT EXISTS can_read boolean DEFAULT false;

ALTER TABLE public.role_permissions ADD COLUMN IF NOT EXISTS can_edit boolean DEFAULT false;

ALTER TABLE public.role_permissions ADD COLUMN IF NOT EXISTS can_delete boolean DEFAULT false;

ALTER TABLE public.role_permissions ENABLE ROW LEVEL SECURITY;

CREATE POLICY role_permissions_select ON public.role_permissions

FOR SELECT TO authenticated USING (true);

`

Key Insight: When Authenticated Policy Still Returns Empty

If user_roles has both anon AND authenticated policies but the query still returns [], the authenticated policy may have a restrictive USING clause. Classic trap: a policy allowing only specific role IDs (polroles = '{16481}') silently blocks the user's actual role.

Fix — drop all existing policies and replace with single permissive one:

`sql

-- via Management API (bypasses RLS)

DROP POLICY IF EXISTS user_roles_read_public ON public.user_roles;

DROP POLICY IF EXISTS user_roles_read_authenticated ON public.user_roles;

CREATE POLICY user_roles_select ON public.user_roles FOR SELECT USING (true);

`**

Then force PostgREST schema reload:**

`sql

NOTIFY pgrst, 'reload';

`

Diagnostic Checklist

1. GET /rest/v1/user_roles?select=role&user_id=eq. — if [] with auth token, RLS issue

2. Browser console — look for 400 on role_permissions requests

3. has_role(uid, 'admin') via RPC — if true but app still says no admin, RLS blocking the frontend query

4. Check profiles.role AND user_roles.role — app checks both

5. role_permissions table schema vs what usePermissions.ts code expects

Key Files (DocCorretor example)

• - src/hooks/useAuth.ts — loads user_roles → sets roles[] → isAdm = roles.includes("admin")
• - src/hooks/usePermissions.ts — queries role_permissions by role_id (NOT by role name)
• - usePermissions only runs if !isAdm — so blocking issue is in useAuth first

Supabase Cloud Query Pattern

`bash

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

-H "Authorization: Bearer " \

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

-d '{"query":""}'

`