Skip to main content

Role-Based Access Control (RBAC) in JetShip 🔒

JetShip uses Role-Based Access Control (RBAC) to regulate user access and permissions. Built on Supabase, this system ensures that users can only perform actions allowed by their assigned roles.

This guide explains how roles, permissions, and access control work together to manage secure access based on hierarchy levels.

Database Schema and Structure​

The RBAC system consists of five key tables:

1. Roles Table​

Defines different user roles, such as admin, editor, and user, with a hierarchy level indicating privileges.

create table "public"."roles" (
  "id" bigint generated by default as identity not null,
  "role" character varying not null,
  "hierarchy" smallint not null default 999
);
  • Lower hierarchy numbers = higher privileges.
  • Admins (Hierarchy = 1) have the highest privilege.
  • Users (Hierarchy = 3) have the lowest privilege.

2. Permissions Table​

Stores specific permissions that can be assigned to roles.

create table "public"."permissions" (
  "id" bigint generated by default as identity not null,
  "permission" character varying not null
);
  • Examples: create_user, delete_post, view_reports.
  • Each permission can be linked to multiple roles.

3. Role-Permissions Mapping​

Since roles and permissions have a many-to-many relationship, this table connects them.

create table "public"."role_permissions" (
  "id" bigint generated by default as identity not null,
  "permission_id" bigint not null,
  "role_id" bigint not null
);
  • A role can have multiple permissions.
  • A permission can belong to multiple roles.

4. User-Roles Mapping​

Each user can have one or more roles.

create table "public"."user_roles" (
  "role_id" bigint not null,
  "user_id" bigint not null
);
  • Users can be assigned multiple roles, gaining permissions from each.
  • Roles define access levels, preventing unauthorized actions.

5. Users Table​

Stores user information and links users to Supabase authentication.

create table "public"."users" (
  "user_id" uuid not null,
  "name" character varying,
  "email" character varying not null,
  "id" bigint generated by default as identity not null
);
  • User authentication is managed through Supabase.

Hierarchy System in RBAC​

The hierarchy system is based on numerical levels, where lower numbers represent higher privileges.

Hierarchy Rules​

✅ Admins (Hierarchy = 1) have full control.
✅ Users can manage roles with a higher hierarchy number (lower privilege).
❌ Users cannot modify details of users with the same hierarchy level.
❌ Users cannot access or modify details of users with a lower hierarchy number (higher privilege).

Example​

  • Admin (1) can manage Editors (2) and Users (3).
  • Editor (2) can manage Users (3) but cannot modify Editor (2) as same hierarchy level.
  • Editor (2) can not see Admin (1) details.
  • User (3) can only modify roles with a hierarchy > 3 (if any exist).

Security Functions for Access Control​

To enforce these hierarchy rules, JetShip implements SQL security functions that validate whether a user can perform certain actions.

1. Admin-Only Check​

This function ensures that only Admins (Hierarchy = 1) can execute restricted actions.

CREATE OR REPLACE FUNCTION public.admin_only()
RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  IF get_user_hierarchy(get_user_id_from_auth(auth.uid())) = 1 THEN
    RETURN true;
  END IF;
  RAISE EXCEPTION 'Only admin can perform this action';
END;
$$;

If a non-admin tries to perform an admin-only action, an exception is raised.

2. Role Management Security​

Restricts who can modify roles based on their hierarchy.

CREATE OR REPLACE FUNCTION public.can_manage_role(role_hierarchy integer)
RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER AS $$
DECLARE
  user_hierarchy integer;
BEGIN
  user_hierarchy := get_user_hierarchy(get_user_id_from_auth(auth.uid()));

  IF user_hierarchy IS NULL THEN
    RAISE EXCEPTION 'User hierarchy not found';
  END IF;

  IF user_hierarchy = role_hierarchy THEN
    RAISE EXCEPTION 'You cannot modify roles at the same hierarchy level as yours';
  END IF;

  IF user_hierarchy > role_hierarchy THEN
    RAISE EXCEPTION 'You cannot modify roles above your hierarchy level';
  END IF;

  RETURN true;
END;
$$;
  • Users can modify roles with a higher hierarchy number (lower privilege).
  • Users cannot modify roles at their same or higher hierarchy level.

3. User Management Restrictions​

Determines whether a user can manage another user based on hierarchy.

CREATE OR REPLACE FUNCTION public.can_manage_user(auth_uid uuid, target_user_id uuid)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER AS $$
  SELECT CASE
    WHEN auth_uid = target_user_id THEN true
    ELSE (
      SELECT get_user_higher_hierarchy(auth_uid) < get_user_higher_hierarchy(target_user_id)
    )
  END;
$$;
  • Users can only manage others if their hierarchy is lower.
  • Users cannot modify users with equal or higher privileges.

Handling Permission Updates and Sessions​

Since permissions are stored in session tokens, they do not update automatically.

Steps to apply permission updates​

  1. Admin update permissions in the database.
  2. User must log out.
  3. User must log back in.
  4. The new session will load updated permissions.

🔹 Why? Permissions are stored in session tokens, which only refresh on login.

Database Policies for Secure Access​

Each table has strict access policies that use security functions to control:

  1. Who can view data (SELECT Policy).
  2. Who can create records (INSERT Policy).
  3. Who can modify existing records (UPDATE Policy).
  4. Who can delete records (DELETE Policy).

These policies enforce role-based access and prevent unauthorized modifications.

Common Issues and Solutions​

1. Permission Changes Not Taking Effect​

🔹 Problem: Updated permissions do not work immediately.
✅ Solution: Users must log out and log back in to refresh their session.

2. Unauthorized Access Errors​

🔹 Problem: A user cannot access expected resources.
✅ Solution:

  • Verify user’s role hierarchy.
  • Check role-permission mappings.
  • Ensure proper policy implementation.

3. Issues with Role Management​

🔹 Problem: Users cannot modify roles as expected.
✅ Solution:

  • Check hierarchy relationships.
  • Ensure there are no circular dependencies.
  • Verify that the user is not modifying roles at their same or higher level.

Conclusion​

JetShip’s RBAC system ensures structured and secure access management. By enforcing hierarchy-based roles, permission mappings, and security functions, users can only perform authorized actions.

For any access issues, check role hierarchies, permissions, and session tokens to resolve them efficiently. 🚀