RBAC Schema

Role Based Access Control (RBAC) lets you describe what users may do inside a team without hard-coding permission checks in Rust. This schema lives alongside the auth module and leans on auth.me() to ensure a user row exists before roles or permissions are granted. Apply this after the Auth and Teams migrations—the final function here replaces the Teams version of auth.me() so callers automatically receive roles and permissions alongside the user and team context.

Full schema

Paste this DBMate-friendly block after applying the Auth and Teams snippets. It creates the RBAC tables plus helpers that expose the current user's roles and permissions, and includes a migrate:down that restores the Teams-focused auth.me().

-- migrate:up

-- ===========================================
-- SCHEMA
-- ===========================================
CREATE SCHEMA IF NOT EXISTS rbac;

-- ===========================================
-- TABLE: rbac.roles
-- ===========================================
CREATE TABLE IF NOT EXISTS rbac.roles (
  id          int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name        text UNIQUE NOT NULL,
  description text
);

-- ===========================================
-- TABLE: rbac.permissions
-- ===========================================
CREATE TABLE IF NOT EXISTS rbac.permissions (
  id          int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name        text UNIQUE NOT NULL,
  description text
);

-- ===========================================
-- TABLE: rbac.role_permissions
-- ===========================================
CREATE TABLE IF NOT EXISTS rbac.role_permissions (
  role_id       int NOT NULL REFERENCES rbac.roles(id) ON DELETE CASCADE,
  permission_id int NOT NULL REFERENCES rbac.permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

-- ===========================================
-- TABLE: rbac.user_roles
-- ===========================================
CREATE TABLE IF NOT EXISTS rbac.user_roles (
  user_id int NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role_id int NOT NULL REFERENCES rbac.roles(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, role_id)
);

CREATE INDEX IF NOT EXISTS idx_rbac_user_roles_user ON rbac.user_roles (user_id);

-- ===========================================
-- FUNCTION: rbac.grant_role()
-- ===========================================
CREATE OR REPLACE FUNCTION rbac.grant_role(p_user_id int, p_role_name text)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  _role_id int;
BEGIN
  SELECT id INTO _role_id FROM rbac.roles WHERE name = p_role_name;
  IF _role_id IS NULL THEN
    RAISE EXCEPTION 'Unknown role=%', p_role_name;
  END IF;

  INSERT INTO rbac.user_roles (user_id, role_id)
  VALUES (p_user_id, _role_id)
  ON CONFLICT DO NOTHING;
END;
$$;

-- ===========================================
-- FUNCTION: rbac.revoke_role()
-- ===========================================
CREATE OR REPLACE FUNCTION rbac.revoke_role(p_user_id int, p_role_name text)
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  _role_id int;
BEGIN
  SELECT id INTO _role_id FROM rbac.roles WHERE name = p_role_name;
  IF _role_id IS NULL THEN
    RETURN;
  END IF;

  DELETE FROM rbac.user_roles
  WHERE user_id = p_user_id
    AND role_id = _role_id;
END;
$$;

-- ===========================================
-- FUNCTION: auth.me(p_team_id)
-- Extends the base helper to include roles and permissions
-- ===========================================
CREATE OR REPLACE FUNCTION auth.me(p_team_id int DEFAULT NULL)
RETURNS TABLE (
  user_row   auth.users,
  team_id    int,
  roles      text[],
  permissions text[]
)
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  _claims     jsonb := auth.jwt();
  _sub        text;
  _email      text;
  _first_name text;
  _last_name  text;
  _user       auth.users;
  _team_id    int;
  _team_name  text;
BEGIN
  _sub := _claims->>'sub';
  IF _sub IS NULL THEN
    RAISE EXCEPTION 'JWT missing sub claim';
  END IF;

  _email      := _claims->>'email';
  _first_name := COALESCE(_claims->>'given_name', _claims->>'first_name');
  _last_name  := COALESCE(_claims->>'family_name', _claims->>'last_name');

  INSERT INTO auth.users (external_id, email, first_name, last_name)
  VALUES (
    _sub,
    _email,
    _first_name,
    _last_name
  )
  ON CONFLICT (external_id) DO UPDATE
    SET email      = COALESCE(EXCLUDED.email, auth.users.email),
        first_name = COALESCE(EXCLUDED.first_name, auth.users.first_name),
        last_name  = COALESCE(EXCLUDED.last_name, auth.users.last_name),
        updated_at = now()
  RETURNING * INTO _user;

  IF p_team_id IS NOT NULL THEN
    _team_id := p_team_id;
  ELSE
    SELECT tm.team_id
    INTO _team_id
    FROM auth.team_members tm
    WHERE tm.user_id = _user.id
    ORDER BY tm.joined_at
    LIMIT 1;
  END IF;

  IF _team_id IS NULL THEN
    _team_name := format('%s''s team', COALESCE(NULLIF(trim(_first_name), ''), 'My'));

    INSERT INTO auth.teams (name, created_by)
    VALUES (_team_name, _user.id)
    RETURNING id INTO _team_id;

    INSERT INTO auth.team_members (team_id, user_id, role)
    VALUES (_team_id, _user.id, 'Owner')
    ON CONFLICT (team_id, user_id) DO NOTHING;
  ELSE
    INSERT INTO auth.team_members (team_id, user_id, role)
    VALUES (_team_id, _user.id, 'Member')
    ON CONFLICT (team_id, user_id) DO NOTHING;
  END IF;

  RETURN QUERY
  SELECT
    _user,
    _team_id,
    COALESCE((
      SELECT array_agg(r.name ORDER BY r.name)
      FROM rbac.user_roles ur
      JOIN rbac.roles r ON r.id = ur.role_id
      WHERE ur.user_id = _user.id
    ), ARRAY[]::text[]),
    COALESCE((
      SELECT array_agg(DISTINCT p.name ORDER BY p.name)
      FROM rbac.user_roles ur
      JOIN rbac.role_permissions rp ON rp.role_id = ur.role_id
      JOIN rbac.permissions p ON p.id = rp.permission_id
      WHERE ur.user_id = _user.id
    ), ARRAY[]::text[]);
END;
$$;

-- migrate:down
DROP FUNCTION IF EXISTS auth.me(int);
DROP FUNCTION IF EXISTS rbac.revoke_role(int, text);
DROP FUNCTION IF EXISTS rbac.grant_role(int, text);
DROP TABLE IF EXISTS rbac.user_roles;
DROP TABLE IF EXISTS rbac.role_permissions;
DROP TABLE IF EXISTS rbac.permissions;
DROP TABLE IF EXISTS rbac.roles;
DROP SCHEMA IF EXISTS rbac;

-- Restore Teams version of auth.me()
CREATE OR REPLACE FUNCTION auth.me(p_team_id int DEFAULT NULL)
RETURNS auth.users
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  _claims     jsonb := auth.jwt();
  _sub        text;
  _email      text;
  _first_name text;
  _last_name  text;
  _user       auth.users;
  _team_id    int;
  _team_name  text;
BEGIN
  _sub := _claims->>'sub';
  IF _sub IS NULL THEN
    RAISE EXCEPTION 'JWT missing sub claim';
  END IF;

  _email      := _claims->>'email';
  _first_name := COALESCE(_claims->>'given_name', _claims->>'first_name');
  _last_name  := COALESCE(_claims->>'family_name', _claims->>'last_name');

  INSERT INTO auth.users (external_id, email, first_name, last_name)
  VALUES (
    _sub,
    _email,
    _first_name,
    _last_name
  )
  ON CONFLICT (external_id) DO UPDATE
    SET email      = COALESCE(EXCLUDED.email, auth.users.email),
        first_name = COALESCE(EXCLUDED.first_name, auth.users.first_name),
        last_name  = COALESCE(EXCLUDED.last_name, auth.users.last_name),
        updated_at = now()
  RETURNING * INTO _user;

  IF p_team_id IS NOT NULL THEN
    _team_id := p_team_id;
  ELSE
    SELECT tm.team_id
    INTO _team_id
    FROM auth.team_members tm
    WHERE tm.user_id = _user.id
    ORDER BY tm.joined_at
    LIMIT 1;
  END IF;

  IF _team_id IS NULL THEN
    _team_name := format('%s''s team', COALESCE(NULLIF(trim(_first_name), ''), 'My'));

    INSERT INTO auth.teams (name, created_by)
    VALUES (_team_name, _user.id)
    RETURNING id INTO _team_id;

    INSERT INTO auth.team_members (team_id, user_id, role)
    VALUES (_team_id, _user.id, 'Owner')
    ON CONFLICT (team_id, user_id) DO NOTHING;
  ELSE
    INSERT INTO auth.team_members (team_id, user_id, role)
    VALUES (_team_id, _user.id, 'Member')
    ON CONFLICT (team_id, user_id) DO NOTHING;
  END IF;

  RETURN _user;
END;
$$;

How it works

• Roles and permissions live in their own schema so you can manage them independently from auth.
• rbac.grant_role() and rbac.revoke_role() are thin wrappers over rbac.user_roles. Use them in migrations or admin panels to keep logic in one place.
• auth.me() is now the single entry point for authentication, team membership, and RBAC context, returning user_row, team_id, roles, and permissions columns you can destructure anywhere in SQL.

Example workflow

BEGIN;

SET LOCAL row_level_security.jwt = $${
  "sub": "1234567890abcdef",
  "email": "[email protected]",
  "given_name": "Daniel",
  "family_name": "Purton"
}$$;

-- Make sure the user exists and capture RBAC context
SELECT * FROM auth.me(); -- columns: user_row, team_id, roles, permissions

-- Map a role to permissions
INSERT INTO rbac.roles (name) VALUES ('Administrator')
ON CONFLICT DO NOTHING;
INSERT INTO rbac.permissions (name) VALUES ('ManageTeam')
ON CONFLICT DO NOTHING;
INSERT INTO rbac.role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM rbac.roles r, rbac.permissions p
WHERE r.name = 'Administrator' AND p.name = 'ManageTeam'
ON CONFLICT DO NOTHING;

-- Grant the role to the current user
SELECT rbac.grant_role(auth.id(), 'Administrator');

-- Hydrate roles + permissions again (now includes Administrator/ManageTeam)
SELECT * FROM auth.me();

COMMIT;

Tie the results into row-level policies or feature flags with a quick CTE:

WITH ctx AS (SELECT * FROM auth.me())
SELECT 'ManageTeam' = ANY(ctx.permissions)
FROM ctx;