AUTHTEMPLATE

Role-Based Access Control

Hiding the admin button is not access control. The button is a suggestion; the API route it calls is the door, and if the door only checks "is logged in" rather than "is allowed", every member of your app is one copied fetch request away from being an admin. This is the single most common security hole in agent-built apps, because the happy path demos perfectly: the admin sees admin things, the viewer doesn't, and nobody in the demo thought to ask the database directly.

So this spec enforces roles at the database layer with row-level security. The app checks roles too, for UX and for clean error messages, but the policy that actually holds is the one Postgres evaluates on every query. A bug in your route handler, a forgotten check in a new endpoint, an agent that helpfully adds a query somewhere: RLS catches all of it, because the database doesn't care how the query arrived.

Two design decisions in here are load-bearing, so know why they exist. Roles live in their own table, not as a column on profiles, because profiles are user-editable and a user who can edit the row that stores their role will eventually promote themselves. And the role lookup happens through a security definer function, because an RLS policy that queries a table which itself has RLS recurses; the function is the standard Postgres escape hatch, and your agent will get this wrong without being told.

Prerequisites

  • Working auth (the Supabase email auth template pairs with this one).
  • Access to your project's SQL editor or a migrations setup.
markdown
# Spec: role-based access (admin / member / viewer)

Add three roles to [app name], enforced with Postgres row-level security.
The database is the enforcement layer; app-layer checks are UX on top.

## Stack

- Database: [Supabase Postgres / Postgres with an auth.uid() equivalent]
- Auth already works; every request resolves to a user id server-side.

## Roles and what they mean (edit the verbs, keep the shape)

| Role   | Can                                                        |
|--------|------------------------------------------------------------|
| admin  | everything below, plus manage roles and delete [resources]  |
| member | create and edit their own [resources], read the team's      |
| viewer | read [resources]; write nothing                             |

New users default to [viewer — safest default, promote deliberately].

## Schema

```sql
create type app_role as enum ('admin', 'member', 'viewer');

-- Separate table on purpose: profiles are user-editable, roles must not be.
create table user_roles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  role app_role not null default 'viewer',
  updated_at timestamptz not null default now()
);

alter table user_roles enable row level security;

-- security definer avoids RLS recursion when policies need a role lookup
create function public.get_my_role()
returns app_role language sql security definer stable
set search_path = '' as $$
  select role from public.user_roles where user_id = auth.uid()
$$;

-- Users may see their own role. Nobody inserts/updates via the API:
-- role changes go through the admin RPC below.
create policy "read own role" on user_roles
  for select using (auth.uid() = user_id);
```

## Policies on the actual data (repeat this block per protected table)

```sql
alter table [resources] enable row level security;

create policy "any role can read" on [resources]
  for select using (public.get_my_role() is not null);

create policy "members create own" on [resources]
  for insert with check (
    public.get_my_role() in ('admin', 'member') and owner_id = auth.uid());

create policy "members edit own, admins edit all" on [resources]
  for update using (
    public.get_my_role() = 'admin'
    or (public.get_my_role() = 'member' and owner_id = auth.uid()));

create policy "only admins delete" on [resources]
  for delete using (public.get_my_role() = 'admin');
```

## Changing roles (admin-only, audited)

```sql
create table role_changes (
  id bigint generated always as identity primary key,
  changed_by uuid not null, target uuid not null,
  old_role app_role, new_role app_role not null,
  at timestamptz not null default now()
);

create function public.set_role(target uuid, new_role app_role)
returns void language plpgsql security definer set search_path = '' as $$
begin
  if public.get_my_role() <> 'admin' then
    raise exception 'admins only';
  end if;
  if target = auth.uid() then
    raise exception 'cannot change your own role';  -- no self-demotion lockouts,
  end if;                                            -- no self-promotion either
  insert into public.role_changes (changed_by, target, old_role, new_role)
    select auth.uid(), target, role, new_role
    from public.user_roles where user_id = target;
  update public.user_roles set role = new_role, updated_at = now()
    where user_id = target;
end $$;
```

Seed the first admin once, from the SQL editor, by hand. Never expose a
route that grants admin without an existing admin behind it.

## App layer (UX, not enforcement)

- Server routes check the role before acting and return 403 with a plain
  message; nicer than surfacing a raw RLS error.
- UI hides what the role can't do. This is politeness. The policies above
  are the actual door.
- Never query with the service-role key in request handlers: it bypasses
  RLS and turns every policy above into a comment.

## Verification (run as each role, before calling this done)

1. As viewer: reads succeed; insert/update/delete fail at the DATABASE even
   when you call the API directly with curl.
2. As member: create a resource, edit it, fail to edit another member's,
   fail to delete anything.
3. As member, call `set_role` on yourself: exception.
4. As admin: change a role, confirm the `role_changes` row landed.

Adaptation notes:

  • More roles or per-team roles: make user_roles keyed on (user_id, team_id) and give get_my_role a team argument. The policy shape survives; only the lookup widens.
  • Permission checks sprinkled through app code drift; policies in one migration file don't. When the app and the database disagree about what a member can do, believe the database, then fix the app.
  • If a policy misfires, debug with explain and by running the failing query in the SQL editor with set role authenticated plus a test JWT claim, not by disabling RLS "temporarily." Temporarily has a way of shipping.
  • Supabase can also mint roles into JWT claims via an auth hook, which saves the per-query lookup at scale. Start with the table and function above; move the lookup into the token only when you measure the need.
  • The mistake: testing only as admin. Every role gets its own pass through the verification list, with curl, because the viewer path is where the holes live and the admin path is where your attention was.

Contribute an improvement →