Roles & Admin Panels

Authentication answers who are you?. Authorization answers are you allowed to do this? — and that's where roles come in. In this lesson you'll build a multi-user app with a proper admin panel, using a role model that's safe by default and won't collapse the first time someone opens the browser devtools.

Part 1 · Why roles matter

"Logged in" and "allowed to" are not the same thing. A signed-in user is one of thousands — most should only see their own rows, a few should moderate content, and a tiny handful should be able to delete anything. That's a role.

Part 2 · The right data model

Two pieces: an app_role enum listing every role you support, and a user_roles table linking users to roles. One user can have many roles; one role can belong to many users.

Create an app_role enum with values admin, moderator, member. Create a user_roles table with user_id (references auth.users) and role app_role, unique on the pair. Enable RLS. Users can read their own roles; only service_role can insert or delete.

• Enum, not free text. Typos ("Admin" vs "admin") become bugs. An enum makes them impossible.
• Separate table. A user's profile is theirs to edit; their role is not.
• Unique (user_id, role). No point in giving someone "admin" twice.
• RLS on user_roles itself. Users may read their own row so the UI can react; only server code writes.

Part 3 · The has_role() security-definer function

You'll want to write policies like "only admins can delete posts". The naive way — a subquery on user_roles inside the policy — triggers RLS on user_roles itself, and now the policy is checking a policy that's checking a policy. Recursion. Errors. Bad time.

The fix is a security-definer function: a tiny database function that runs with the privileges of its owner, not the caller. It bypasses RLS on user_roles so your policies can call it freely.

create or replace function public.has_role(_user_id uuid, _role app_role)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.user_roles
    where user_id = _user_id and role = _role
  )
$$;

Now every "admin only" policy is one line:

create policy "admins can delete any post"
on public.posts for delete
to authenticated
using (public.has_role(auth.uid(), 'admin'));

Part 4 · Protecting the admin route

Two layers, and you need both. Hide the admin link in the UI so normal users don't see it — that's UX. Enforce the rule in database policies so the request fails even if someone types the URL — that's security.

• Client gate — read the current user's roles once on login, expose isAdmin from your auth hook, and hide admin routes/buttons when false. Great for UX, worthless as security.
• Server gate — every admin-only mutation is protected by an RLS policy calling has_role(auth.uid(), 'admin'). This is the one that actually stops attackers.
• Never trust the client's role claim. Even if your app sets localStorage.role = 'admin', the database doesn't care. Good.

Add an admin gate to the app. Fetch the current user's roles in the auth hook and expose isAdmin. Hide the admin nav link and redirect non-admins away from /admin. Also write an RLS policy that rejects any delete on posts unless the caller is admin.

Part 5 · Mini-project — a team dashboard with admin controls

Three roles: member (can post), moderator (can edit anyone's post), admin (can delete anything and manage members). One shared feed, one admin page.

1. 1Schema

   app_role enum + user_roles table. GRANT to authenticated (select) and service_role (all). RLS: users read their own row.

2. 2has_role()

   Security-definer boolean function. Use it in every role-scoped policy from here on.

3. 3posts table

   Anyone authenticated reads. Authors edit their own row. has_role(uid,'moderator') edits any row. has_role(uid,'admin') deletes any row.

4. 4Auth hook

   On login, fetch user_roles for the current user. Expose isAdmin / isModerator on the client for UX gating.

5. 5/admin/users

   List members. An admin-only server function upserts a role into user_roles. Show a toast on success, an error bubble on failure.

6. 6Audit

   Log every role change to a role_audit table (who, what, when, from → to). Never delete audit rows.

Build a /admin/users page. List all members with their current role. Only visible when isAdmin is true. Each row has three buttons — member, moderator, admin — that call a server function to update user_roles. The function must verify the caller is admin before writing.

Part 6 · Polish & pitfalls

The first-admin problem

If admin actions require you to already be an admin, how does the first admin exist? Two safe answers:

• Seed in a migration. Insert a single row into user_roles for your own user id in the same migration that creates the table. This runs with service_role and doesn't need a UI.
• Bootstrap script. An admin-only server function that promotes a hard-coded email to admin — but only if there are zero admins yet. Runs once, effectively self-destructs after.

Common mistakes

• Storing the role in localStorage. Users can edit it. Attackers definitely can. The role you trust is the one you fetch from the database after auth.
• Checking role only in React. The API is still open. Someone opens the network tab, copies the request, changes the id, and deletes another user's data.
• Forgetting GRANT on user_roles. RLS without GRANT = PostgREST returns a permission error before RLS even runs, and the auth hook silently thinks the user has no roles.
• Referencing user_roles inline in a policy. Recursion. Use has_role().
• One role, everywhere. If every feature checks isAdmin, you've built a two-tier app. Add a moderator/editor tier before the admin role becomes a bottleneck.
• No audit trail. Six months in, a row will change and nobody will remember who did it. Log role changes.

When to add a third (or fourth) role

• Different write scopes — a role that can edit others' content but not delete it.
• Different read scopes — a role that sees revenue data, others don't.
• A demo/read-only role you can hand to investors or auditors without exposing production controls.

Roles cheat sheet

Prompts you'll reuse

• "Create an app_role enum and a user_roles table with RLS" → safe role storage.
• "Add a has_role() security-definer function" → recursion-free role checks.
• "Only admins can delete posts; moderators can edit any post" → policy per action, per role.
• "Expose isAdmin from the auth hook" → clean UX gating in React.
• "Build a /admin/users page with promote/demote buttons" → the admin panel itself.
• "Log every role change to a role_audit table" → accountability.

Rules of thumb

• Roles live in their own table. Never on profiles, never in localStorage, never in a JWT you mint yourself.
• Client hides, database decides. UX gates for polish, RLS for truth.
• One function, every policy. has_role() is the single source of truth.
• Never trust the caller's role claim. Look it up server-side, every time.
• Audit role changes. Future-you will thank present-you.