S

Supabase•6mo ago

Nomad

[Question] Best Practice for RLS Key: Using auth.users.id vs supabase_id Column?

Hi Supabase community!

I’m prototyping a large-scale application and evaluating the best schema design and RLS/RBAC patterns. I came across the official Supabase YouTube tutorial where they add a separate supabase_idsupabase_id column in the public usersusers table to mirror auth.users.idauth.users.id, but for complex, high-traffic systems I’m not sure this is the optimal approach.

Context & Goals:

• Implement a robust RBAC mechanism where roles (e.g. Admin, Editor, Viewer) control access across multiple schemas and tables.
• Enforce row-level security so users only see their own data, and roles can override this for administrative operations.
• Prototype quickly without introducing redundant columns or unnecessary joins.

What I’ve Tried:

• Following the tutorial’s pattern: adding supabase_id UUIDsupabase_id UUID in public.userspublic.users, populated via DEFAULT auth.uid()DEFAULT auth.uid() and triggers.
• Writing RLS policies that compare

  auth.uid()

  auth.uid()

  to supabase_idsupabase_id, which adds casting complexity and extra joins.
• Considering directly using id UUID PRIMARY KEY REFERENCES auth.users(id)id UUID PRIMARY KEY REFERENCES auth.users(id) in public.userspublic.users, then layering RBAC roles.

Key Questions:

1. For prototyping a large application, is it better practice to keep a single

   id

   id

   column in public.userspublic.users linked straight to auth.users.idauth.users.id, or to maintain a separate supabase_idsupabase_id?
2. How would you structure RBAC roles and permissions at scale—would you extend RLS policies, use custom PostgreSQL functions, or integrate a dedicated roles table?
3. What database design patterns or diagrams do you recommend for quickly iterating on RBAC/RLS before productionizing?

Any insights, schema diagrams, or examples of prototyping strategies would be greatly appreciated!

Tags: #RBAC #RLS #Prototyping #SchemaDesign #Supabase