Users and roles

A role holds a set of capabilities. A user holds a set of roles. Any server route or RLS policy can ask one question: does the calling user hold this capability?

The tables

public.roles              key, label, description, is_builtin
public.role_capabilities  role_key, capability
public.user_roles         user_id, role_key

The migration 004_roles_and_capabilities.sql adds them.

• A capability key looks like posts:publish. The literal * means every capability.
• The role key and the capability string are free text. A format constraint checks them. They are not an enum, so any layer can add its own capability in its own migration.
• The built-in admin role holds one row: ('admin', '*').
• The first account that signs up becomes an admin. It gets profiles.is_admin = true and the admin role. The database ignores any is_admin value in the sign-up data.

is_admin() and has_capability()

public.is_admin() returns true for a user whose profiles.is_admin column is true, or who holds the admin role. The migration grants the admin role to every existing admin, so no data migration is needed.

public.has_capability(cap) is the general check. It is true for an admin, or for a role grant that names cap or *. Prefer has_capability() over is_admin() in new code.

The column profiles.is_admin stays. New code must keep writing it.

Guard a server route

import { requireCapability } from '../../../../utils/capability-guard'

export default defineEventHandler(async (event) => {
  await requireCapability(event, 'settings:manage')
  // ...
})

requireCapability throws 401 if the caller is not signed in. It throws 403 if the caller lacks the capability. The check runs in the database, through has_capability(). It reads auth.uid() from the session of the caller.

Other guards:

• requireAdmin(event). It reads profiles.is_admin directly. The migrations routes use it, so no route depends on a migration that it exists to apply. There is no system:migrate capability.
• requireAdminRole(event). It calls the rpc is_admin(), so it also accepts an admin who has the role only. The access routes use it.

Client side

The layer registers a permissionsDriver. It reads GET /api/_pluto/supabase/me/capabilities. The route returns an empty list, and not a 500, when my_capabilities() does not exist yet. usePlutoPermissions().can() uses the list to show or hide UI.

The layer declares the capabilities settings:manage and users:read.

Why profiles has no update policy

public.profiles has a select policy and no update policy, on purpose. A user who could update their own row could also set is_admin to true. When a user must change one column of their own row, add a security definer function that writes only that column for auth.uid(). See Profile language.

The Users page

The layer adds a Users item to the admin menu, between Media and Settings. A click opens the Users page. The item has two children: Users and Roles. Roles shows for admins only. Without it, the item is a plain link.

The page is at /admin/users. It needs the users:read capability. Without it, the page shows a no-permission alert.

• It shows name and email (with a "You" badge on your row), role badges, a status (Active, or Invited with the date), and the last sign-in.
• Only an admin sees Invite user and the row menu. The server checks again.
• The row menu has Edit roles, Resend invite, Copy invite link, and Revoke access. Resend and copy are for pending users. Copy needs the service key.

Invite

The invite modal has email, an optional name, roles, and the email language. The default language is the one of the admin. See Invites and email.

Edit roles and revoke

• Edit roles replaces the roles of a user. A user needs at least one role. If you remove your own admin role, a warning shows.
• Revoke access removes all roles. The account stays. The last admin cannot be revoked. The server answers 409 lastAdmin.

The Roles page

The page is at /admin/roles. It is for admins only.

The table shows the role (label, key, and a Built-in badge), the number of permissions (or Full access for *), the number of users, and a row menu.

The role editor has a name, a key, a description, and the permissions. The key is made from the name until you edit it. It must match ^[a-z0-9_]+$. The permissions are grouped by layer. A role needs at least one permission. The admin role opens read-only. A built-in role cannot be deleted. The delete modal says how many users lose the role.

The access API

The migration 008_users_and_roles_admin.sql adds eight security definer functions with an empty search_path: list_staff_users, list_roles, find_user_id_by_email, set_user_roles, add_user_roles, create_role, update_role, and delete_role. The routes under /api/_pluto/supabase/access/** call them with the session of the caller.

Rules:

• Every write needs is_admin(). There is no users:manage capability.
• The Users list shows staff only. Staff is a user with a role, or with profiles.is_admin.
• A new role has no * and at least one capability. The admin role cannot be edited or deleted.
• A rule error is P0001 with the message pluto:<code>. A missing right is 42501 with pluto:forbidden. The server maps last_admin and role_exists to 409, forbidden to 403, the format codes to 400, and the *_not_found codes to 404.

The admin column sync

set_user_roles(target, role_keys) replaces the roles of a user. An empty array is a revoke. It sets profiles.is_admin to true when admin is in the list, in the same transaction. add_user_roles sets the column to true when it adds admin.

The last-admin rule: if the target is an admin, the new set has no admin, and no other admin exists, the function raises pluto:last_admin. Every role writer takes an advisory lock, so two admins cannot remove each other at the same time.

If you grant the admin role by hand with SQL, also set profiles.is_admin = true.

Grant a role by hand

Use the Users page when you can. You can also run SQL:

insert into public.roles (key, label) values ('editor', 'Editor')
on conflict (key) do nothing;

insert into public.role_capabilities (role_key, capability) values
  ('editor', 'posts:publish'), ('editor', 'posts:read_drafts')
on conflict do nothing;

insert into public.user_roles (user_id, role_key)
select id, 'editor' from public.profiles where email = 'someone@example.com'
on conflict do nothing;

Limits

• There is no per-row ownership. A capability is all or nothing for every row of a table.
• profiles.is_admin and the admin role grant can drift apart when you write user_roles by hand.