Role-Based Access for Internal Tools: A Practical Design

Good role-based access for internal tools starts with a few flat roles that match how people work — in a request desk, requester, agent and admin — and handles special cases with small, additive grants (a team lead who can view a team's tasks, an HR person who sees leave) rather than new roles. Enforce roles in the database with row-level security, mirror them in the UI, and log every role change. Three roles and two grants beat forty permissions.

What RBAC is (and isn't)

Role-based access control assigns permissions to roles and roles to people, so you manage "what agents can do" once instead of per person. NIST's RBAC model formalized this in the 1990s and it remains the standard vocabulary: users, roles, permissions, and sessions in which roles are active.

RBAC isn't the only model. Attribute-based access control (ABAC) decides from attributes — "same organization", "same team", "record owner". Real internal tools use both: a role says what kind of user you are; attributes like organization and team say which records that role applies to. In Postgres policies, that's exactly role = 'agent' and team_id = my_team.

Start with the fewest roles that match reality

For a request desk, three roles cover nearly everyone:

Add an owner as a special admin: the one person who can delete the organization, transfer ownership, and whom other admins can't demote. Without it, two admins can lock each other out.

Resist adding roles for every exception. Each new role multiplies the combinations you have to test and explain.

Special cases: additive grants, not new roles

Real organizations have people who need a little more than their role: a team lead who should see their team's tasks, a director who oversees several teams, an HR person who approves leave. The tempting fix is new roles — team_lead, director, hr_agent. The problem: they're combinations of existing roles plus something, so you end up with hr_admin, team_lead_agent and so on.

LetRelay uses grants instead: small, specific, additive permissions layered on the flat roles.

• Oversight grants — a person can view (read-only) the tasks of specific teams or people. A team lead is an agent with an oversight grant for their team; a director has grants for several teams.
• HR access — a flag that lets a person see and decide leave requests across the organization.

Grants only ever add visibility. They never remove what a role already has, so they can't accidentally lock someone out, and "why can Jordan see this?" always has a one-line answer.

Enforce in the database

The UI hides buttons; the database decides. Each policy combines the tenant wall, the role, and any grants:

create policy requests_select on public.requests for select to authenticated
using (
  organization_id = (select public.current_user_org())
  and (
    requester_id = (select auth.uid())
    or (select public.current_user_role()) = 'admin'
    or ((select public.current_user_role()) = 'agent'
        and (team_id = (select public.current_user_team()) or team_id is null))
  )
);

This is LetRelay's actual policy on requests. A requester querying the requests table directly — bypassing the UI entirely — still gets only their own rows. Why this belongs in the database rather than in route handlers is argued in row-level security vs app-layer authorization.

Two details matter for grants:

• Keep grant checks cheap. A grant lookup inside a policy runs for every candidate row. Store grants in a small indexed table and look them up with a security definer helper; never make a policy on tasks re-scan tasks to decide visibility.
• Read roles from a table users can't write. A role in user_metadata or a request body is user-controlled.

A permission matrix you can actually read

Writing the matrix down forces decisions and doubles as the test plan. For a request desk:

Every ✓ is a policy clause, and every — is a test that must fail. If a cell needs a paragraph of explanation, the design is too complicated.

Protect the role column itself

The most sensitive write in the system is someone changing a role. Rules we enforce with a database trigger:

• Only admins can change roles, and only within their own organization.
• Nobody can change their own role.
• Only the owner can make or remove admins.
• The owner can't be demoted; ownership moves only through an explicit transfer.

A before update trigger on profiles can check all of these, which means they hold even for a direct API call, not just the Settings screen.

Mirror roles in the UI

Hiding controls a person can't use isn't security, but it is good design:

• Navigation shows only what the role can reach. A requester doesn't see "Analytics".
• Buttons that would fail are hidden, not disabled with a tooltip — unless the reason is useful ("Pro feature").
• Pages a role can't access redirect server-side, before rendering.

Derive UI visibility from the same role value the database uses, read once in the layout. Two sources of truth drift.

Invites, approval and offboarding

Access control is also about the lifecycle:

• Invites set the role up front. An admin invites a person as an agent on a specific team; the invite carries it.
• Self-sign-ups start restricted. A new account joining an existing organization waits for admin approval before seeing anything.
• Offboarding removes access immediately. Deactivate the profile (policies check an approved status), reassign their open requests, and keep their history for the audit trail.

Audit every change

Log role changes, grant changes and ownership transfers to an events table: who changed what, from what, to what, when. It's the first thing you'll want during a security question, and it costs one insert per change.

Test roles like an attacker

For each role, sign in as a real user and try every operation on every table — including against other teams and other organizations. The assertions are simple: requesters see only their own rows; agents see only their team; nobody crosses organizations; nobody can edit their own role. LetRelay runs these checks as five roles across two organizations on every verification run. The multi-tenant setup behind them is in the Supabase multi-tenant RLS guide.

FAQ

How many roles should an internal tool have?

As few as reflect genuinely different jobs — typically three to four. Handle exceptions with additive grants instead of new roles, which keeps combinations testable.

What's the difference between an owner and an admin?

Admins manage the organization; the owner is the one admin who can't be demoted, can transfer ownership and can delete the organization. It prevents admins from locking each other out.

Should roles live in the JWT?

Only if a server controls them (app_metadata) and you accept they may be stale until the token refreshes. Reading roles from a protected table in policies is simpler and always current.

Is hiding buttons enough?

No. Hiding controls is for usability. The enforcement must happen in the database or server code, because anyone can call your API directly.

Sources

• NIST — Role Based Access Control
• OWASP — Authorization Cheat Sheet
• Supabase — Row Level Security
• PostgreSQL — Trigger Functions
• Wikipedia — Role-based access control