A Clean Approach to Supabase Policies: Dynamic Role-Based Access Control (RBAC)

Refactor your messy Postgres policies into one clean, scalable pattern with just 4 rules per table.

The Problem: Supabase Policies Can Get Messy, Fast

If you’re building anything beyond a simple todo app, you’ll probably need:

Multiple user roles (e.g. admin, manager, employee) Role-specific access control (e.g. only managers can approve leave) Permissions that evolve as your app grows In Supabase, this quickly leads to dozens of policies per table, like:

This becomes unmanageable, especially when:

You want to add new roles You want to change permissions You don’t want to write SQL every time The Goal: Centralized, Dynamic RBAC in Supabase We want to move to a setup where:

✅ All roles and permissions live in tables (roles, permissions, etc.) ✅ Admins can manage them via UI (no SQL needed) ✅ Each table only needs 4 simple policies (CRUD) ✅ The logic for access control is centralized in one place

Step 1: Define Your Role Tables

Create these tables in your Supabase schema:

Step 2: Create a Dynamic authorize() Function

This function checks if the current user is allowed to perform a specific action on a given resource (like leaves.read).

Step 3: Write Just 4 Policies Per Table

No more role-specific policy clutter. Just plug in the dynamic authorize() function.

For example, for the leaves table:

That’s it! You now have full RBAC powered by SQL and backed by a UI-manageable schema.

Bonus Tips

🛡 You can add a superadmin flag instead of checking the role name 🔁 Want multiple roles per user? Just change user_roles to a many-to-many model 🚀 Want even more flexibility? Add granular scopes like can_approve, can_export, etc. The Result BeforeAfterDozens of policiesJust 4 per tableHard-coded role logicCentralized in authorize()SQL-only updatesUI-based permission controlNo admin overviewAdmins can see & update roles/permissions easily

Conclusion

This RBAC pattern gives you:

• Clean, maintainable policies
• A clear separation of concerns
• Flexibility to scale roles and permissions over time
• Admin-friendly UI possibilities

✨ Say goodbye to policy sprawl. Say hello to scalable, centralized access control.