Enforcing roles in JED with Supabase row level security

The first version of access control in a lot of apps is a hidden button. Admins see the Settings link, members don't. It looks right in a demo, and it protects nothing, because anyone with the publishable key and a bit of curiosity can call the database directly.

In JED, I wanted the database itself to decide who can read and change what. The app checks permissions too, but the real line is drawn in Postgres with row level security.

Roles and permissions are tables

JED stores access in a few plain tables. roles holds role codes like member and admin. permissions holds codes like content.write or settings.read. role_permissions maps one to the other, and user_roles says which user has which role.

When someone signs up, a trigger creates their profile and gives them the member role. Members have none of the admin permissions. The first admin is granted with one SQL insert, run once per project:

insert into public.user_roles (user_id, role_code)
select user_id, 'admin'
from public.profiles
where lower(email) = lower('you@example.com')
on conflict do nothing;

After that, admins change roles from the Users screen.

Every table asks the same question

RLS is enabled on every table in the public schema. The policies call a helper, has_permission, which checks the signed-in user's roles against role_permissions. A content policy looks roughly like this:

create policy content_entries_update on public.content_entries
  for update to authenticated
  using (public.has_permission('content.write'))
  with check (public.has_permission('content.write'));

Each operation gets its own policy, so reading content and writing it are separate decisions. Updates pair using with with check, so a user can't edit a row into a shape they wouldn't be allowed to create.

The app checks too, but it isn't the last line

Server functions in JED check the user's permissions before doing anything, and the admin menu only shows screens you can open. That's for a good experience and clear error messages. If a check is missing because of a bug, RLS is still there. A member who calls the content table directly with their own session gets nothing back.

This is also why the API is safe to build. API tokens in JED run every query as the token's owner, through the same policies. The service role key, which bypasses RLS, is only used server-side for the few account tasks Supabase reserves for it, like sending an invite.

Protecting the last admin

Roles can lock you out if you're careless. JED refuses two things: removing your own admin role, and removing the admin role from the last admin. The role change goes through a database function, set_user_role_atomic, so the check and the change happen together. The same rules apply when an admin changes roles through the API or the MCP server.

Never trust user metadata

One thing I avoid on purpose is putting roles in Supabase user metadata. Users can edit their own user_metadata, so basing access on it would let anyone promote themselves. Roles live in tables that only admins can write to, and the policies on those tables require roles.manage.

What it costs

RLS adds work. Every new table needs policies, indexes on the columns those policies use, and tests that try the forbidden paths. When a query returns nothing, sometimes the answer is a missing policy, not a bug in the app.

I'd still take that trade every time. Client sites get handed over, extended, and edited by people who weren't there when they were built. Putting the rules in the database means the next developer can add a page without accidentally opening up the data behind it.