Building a multi-tenant organization system with Supabase part 3

Introduction

In Part 2, we built the complete database schema for our multi-tenant system. Now we'll implement the authorization layer that makes it secure and functional:

JWT Claims - Custom auth hooks that inject tenant context
Permission System - Account-based authorization checks
Row Level Security - Database-level access control policies

Step 1: Postgres Triggers

Using postgres triggers and functions, let's make sure that all users have a personal account created for them

-- Function to handle new user registration
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
DECLARE
  personal_account_id uuid;
  username text;
  unique_username text;
BEGIN
  -- Extract username from metadata or email
  username := COALESCE(NEW.raw_user_meta_data->>'username', split_part(NEW.email, '@', 1));

  -- Generate unique username
  unique_username := username;
  WHILE EXISTS (SELECT 1 FROM public.user_profiles WHERE username = unique_username) LOOP
    unique_username := username || '_' || substr(gen_random_uuid()::text, 1, 4);
  END LOOP;

  -- Create user profile
  INSERT INTO public.user_profiles (id, email, username, email_status)
  VALUES (
    NEW.id,
    NEW.email,
    unique_username,
    CASE WHEN NEW.email_confirmed_at IS NULL THEN 'pending' ELSE 'confirmed' END
  );

  -- Create personal account with default client app role
  INSERT INTO accounts.accounts (name, owner_id, type, slug, status, app_role)
  SELECT
    unique_username,
    NEW.id,
    act.id,
    unique_username,
    acs.id,
    aar.id
  FROM accounts.account_types act,
       accounts.account_statuses acs,
       accounts.account_app_roles aar
  WHERE act.slug = 'personal'
    AND acs.slug = 'active'
    AND aar.slug = 'client'
  RETURNING id INTO personal_account_id;

  -- Add owner membership
  INSERT INTO accounts.account_members (account_id, user_id, role_id, membership_status)
  SELECT personal_account_id, NEW.id, ar.id, acs.id
  FROM accounts.account_roles ar, accounts.account_statuses acs
  WHERE ar.slug = 'owner' AND acs.slug = 'active';

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create the trigger
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

Step 2: Configure Auth Claims Hook

In your Supabase dashboard, go to Authentication > Hooks and set up the custom access token hook. So we can use Supabase Auth Hooks to inject the user's account memberships and roles into the claims object:

CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
RETURNS jsonb
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  claims jsonb;
  user_accounts jsonb;
BEGIN
  -- Get user's account memberships
  SELECT jsonb_object_agg(
    am.account_id::text,
    jsonb_build_object(
      'role', acr.slug,
      'account_name', a.name,
      'account_slug', a.slug,
      'status', ams.slug
    )
  ) INTO user_accounts
  FROM accounts.account_members am
  JOIN accounts.accounts a ON am.account_id = a.id
  JOIN accounts.account_roles acr ON am.role_id = acr.id
  JOIN accounts.account_statuses ams ON am.membership_status = ams.id
  WHERE am.user_id = (event->>'user_id')::uuid
  AND ams.slug = 'active';

  claims := event->'claims';

  -- Add custom claims to app_metadata
  claims := jsonb_set(
    claims,
    '{app_metadata}',
    COALESCE(claims->'app_metadata', '{}') || jsonb_build_object(
      'accounts', COALESCE(user_accounts, '{}')
    )
  );

  RETURN jsonb_set(event, '{claims}', claims);
END;
$$;

-- Grant necessary permissions
GRANT EXECUTE ON FUNCTION public.custom_access_token_hook TO supabase_auth_admin;

Step 3: Permission System

Account-Level Authorization

For checking permissions within a specific account:

CREATE OR REPLACE FUNCTION public.authorize_account(
  requested_permission text,
  account_id uuid
)
RETURNS boolean
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
  _user_id uuid;
  _jwt jsonb;
  _accounts jsonb;
  _account_role text;
  _account_app_role text;
  _app_permissions jsonb;
BEGIN
  _user_id := auth.uid();
  IF _user_id IS NULL OR account_id IS NULL THEN
    RETURN FALSE;
  END IF;

  _jwt := auth.jwt();
  _accounts := _jwt -> 'app_metadata' -> 'accounts';

  -- Get user's role and app role in this account
  _account_role := _accounts -> account_id::text ->> 'role';
  _account_app_role := _accounts -> account_id::text ->> 'app_role';
  _app_permissions := _accounts -> account_id::text -> 'app_permissions';

  -- Check account app-level permissions first
  IF _app_permissions ? requested_permission THEN
    RETURN TRUE;
  END IF;

  -- Check admin account privileges
  IF _account_app_role = 'admin' AND requested_permission LIKE 'app:%' THEN
    RETURN TRUE;
  END IF;

  -- Check account membership-level permissions
  CASE _account_role
    WHEN 'owner' THEN
      RETURN requested_permission IN ('account:read', 'account:write', 'account:admin', 'account:delete');
    WHEN 'admin' THEN
      RETURN requested_permission IN ('account:read', 'account:write', 'account:admin');
    WHEN 'member' THEN
      RETURN requested_permission IN ('account:read', 'account:write');
    WHEN 'guest' THEN
      RETURN requested_permission = 'account:read';
    ELSE
      RETURN FALSE;
  END CASE;

  RETURN FALSE;
EXCEPTION
  WHEN OTHERS THEN
    RAISE LOG 'Error in authorize_account function: %', SQLERRM;
    RETURN FALSE;
END;
$$;

App-Level Authorization

For checking app-wide permissions based on account app roles:

CREATE OR REPLACE FUNCTION public.authorize_app(requested_permission text)
RETURNS boolean
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
  _user_id uuid;
  _jwt jsonb;
  _accounts jsonb;
  _account_id text;
  _account_info jsonb;
  _app_permissions jsonb;
BEGIN
  _user_id := auth.uid();
  IF _user_id IS NULL THEN
    RETURN FALSE;
  END IF;

  _jwt := auth.jwt();
  _accounts := _jwt -> 'app_metadata' -> 'accounts';

  -- Check all user's accounts for the requested permission
  FOR _account_id IN SELECT jsonb_object_keys(_accounts) LOOP
    _account_info := _accounts -> _account_id;
    _app_permissions := _account_info -> 'app_permissions';

    -- Check if this account grants the requested permission
    IF _app_permissions ? requested_permission THEN
      RETURN TRUE;
    END IF;

    -- Special case: admin accounts get broad app permissions
    IF _account_info ->> 'app_role' = 'admin' AND requested_permission LIKE 'app:%' THEN
      RETURN TRUE;
    END IF;
  END LOOP;

  RETURN FALSE;
EXCEPTION
  WHEN OTHERS THEN
    RAISE LOG 'Error in authorize_app function: %', SQLERRM;
    RETURN FALSE;
END;
$$;

Membership Validation

Check if a user is a member of a specific account:

CREATE OR REPLACE FUNCTION public.is_account_member(account_id uuid)
RETURNS boolean
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
  _user_id uuid;
  _jwt jsonb;
  _accounts jsonb;
BEGIN
  _user_id := auth.uid();
  IF _user_id IS NULL OR account_id IS NULL THEN
    RETURN FALSE;
  END IF;

  _jwt := auth.jwt();
  _accounts := _jwt -> 'app_metadata' -> 'accounts';

  -- Check if account exists in user's accounts and is active
  RETURN (_accounts -> account_id::text ->> 'status') = 'active';
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END;
$$;

Account Role Check

Get the user's role in a specific account:

CREATE OR REPLACE FUNCTION public.get_account_role(account_id uuid)
RETURNS text
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
  _user_id uuid;
  _jwt jsonb;
  _accounts jsonb;
BEGIN
  _user_id := auth.uid();
  IF _user_id IS NULL OR account_id IS NULL THEN
    RETURN null;
  END IF;

  _jwt := auth.jwt();
  _accounts := _jwt -> 'app_metadata' -> 'accounts';

  -- Return user's role in this account
  RETURN _accounts -> account_id::text ->> 'role';
EXCEPTION
  WHEN OTHERS THEN
    RETURN null;
END;
$$;

Account App Role Check

Get the app role of a specific account:

CREATE OR REPLACE FUNCTION public.get_account_app_role(account_id uuid)
RETURNS text
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
  _user_id uuid;
  _jwt jsonb;
  _accounts jsonb;
BEGIN
  _user_id := auth.uid();
  IF _user_id IS NULL OR account_id IS NULL THEN
    RETURN null;
  END IF;

  _jwt := auth.jwt();
  _accounts := _jwt -> 'app_metadata' -> 'accounts';

  -- Return account's app role
  RETURN _accounts -> account_id::text ->> 'app_role';
EXCEPTION
  WHEN OTHERS THEN
    RETURN null;
END;
$$;

Step 4: Example Row Level Security Policies

User Profiles Policies

-- Users can view their own profile
CREATE POLICY "Users can view own profile" ON public.user_profiles
  FOR SELECT TO authenticated
  USING (select auth.uid() = id);

-- Users can update their own profile
CREATE POLICY "Users can update own profile" ON public.user_profiles
  FOR UPDATE TO authenticated
  USING (select auth.uid() = id);

Account Access Control

-- Members can view accounts they belong to
CREATE POLICY "Members can view their accounts" ON accounts.accounts
  FOR SELECT TO authenticated
  USING (is_account_member(id) = TRUE);

-- Only owners and admins can update accounts
CREATE POLICY "Account owners and admins can update" ON accounts.accounts
  FOR UPDATE TO authenticated
  USING (authorize_account('account:admin', id) = TRUE);

-- Only owners can delete accounts
CREATE POLICY "Account owners can delete" ON accounts.accounts
  FOR DELETE TO authenticated
  USING (authorize_account('account:delete', id) = TRUE);

-- Authenticated users can create new accounts
CREATE POLICY "Users can create accounts" ON accounts.accounts
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = owner_id);

Membership Management

-- Users can view memberships for accounts they belong to
CREATE POLICY "Users can view account memberships" ON accounts.account_members
  FOR SELECT TO authenticated
  USING (
    user_id = auth.uid()
    OR
    is_account_member(account_id) = TRUE
  );

-- Account owners and admins can manage memberships
CREATE POLICY "Account admins can manage memberships" ON accounts.account_members
  FOR INSERT TO authenticated
  WITH CHECK (
    authorize_account('account:admin', account_id) = TRUE
  );

CREATE POLICY "Account admins can update memberships" ON accounts.account_members
  FOR UPDATE TO authenticated
  USING (
    authorize_account('account:admin', account_id) = TRUE
  );

-- Only owners can delete memberships (or users leaving themselves)
CREATE POLICY "Account owners can delete memberships" ON accounts.account_members
  FOR DELETE TO authenticated
  USING (
    authorize_account('account:delete', account_id) = TRUE
    OR user_id = auth.uid()
  );

Domain Verification Policies

-- Account members can view domains
CREATE POLICY "Account members can view domains" ON public.domains
  FOR SELECT TO authenticated
  USING (
    is_account_member(account_id) = TRUE
  );

-- Account admins can manage domains
CREATE POLICY "Account admins can manage domains" ON public.domains
  FOR ALL TO authenticated
  USING (
    authorize_account('account:admin', account_id) = TRUE
  );

Step 5: Domain Management

Workspaces can add domains and verify domain ownership

CREATE TABLE public.domains(
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  account_id uuid REFERENCES accounts.accounts(id) ON DELETE CASCADE,
  name text NOT NULL CHECK (length(name) > 0 AND length(name) < 64),
  verification_code text,
  is_verified boolean DEFAULT FALSE,
  status uuid REFERENCES public.domain_statuses(id),
  updated_at timestamp with time zone DEFAULT now(),
  created_at timestamp with time zone DEFAULT now(),
  UNIQUE (account_id, name)
);

CREATE TABLE public.domain_statuses(
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name text NOT NULL,
  slug text NOT NULL UNIQUE,
  description text
);

-- Insert domain statuses
INSERT INTO public.domain_statuses (name, slug, description) VALUES
  ('Pending', 'pending', 'Domain verification is pending'),
  ('Verified', 'verified', 'Domain has been verified'),
  ('Failed', 'failed', 'Domain verification failed'),
  ('Expired', 'expired', 'Domain verification has expired');

Verification Process

-- Generate verification code on insert
CREATE OR REPLACE FUNCTION private.set_verification_code()
RETURNS TRIGGER AS $$
DECLARE
  random_code text;
BEGIN
  random_code := private.generate_random_code(5);
  NEW.verification_code := encode(extensions.digest(random_code, 'sha256'), 'hex');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Helper function to generate random codes
CREATE OR REPLACE FUNCTION private.generate_random_code(length int)
RETURNS text AS $$
BEGIN
  RETURN upper(substr(gen_random_uuid()::text, 1, length));
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Verify submitted code
CREATE OR REPLACE FUNCTION private.verify_code(domain_id uuid, code text)
RETURNS boolean AS $$
DECLARE
  hashed_code text;
  stored_code text;
BEGIN
  hashed_code := encode(extensions.digest(code, 'sha256'), 'hex');

  SELECT verification_code INTO stored_code
  FROM public.domains
  WHERE id = domain_id;

  RETURN hashed_code = stored_code;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create trigger for verification code generation
CREATE TRIGGER on_domain_insert
  BEFORE INSERT ON public.domains
  FOR EACH ROW EXECUTE FUNCTION private.set_verification_code();

Step 6: Data Cleanup Functions

Implement regular maintenance procedures:

-- Clean up expired invitations
CREATE OR REPLACE FUNCTION accounts.cleanup_expired_invitations()
RETURNS void AS $$
BEGIN
  DELETE FROM accounts.account_members
  WHERE user_id IS NULL
  AND invite_expires_at < NOW() - INTERVAL '7 days';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Archive soft-deleted accounts
CREATE OR REPLACE FUNCTION accounts.archive_deleted_accounts()
RETURNS void AS $$
BEGIN
  UPDATE accounts.accounts
  SET deleted_at = NOW()
  WHERE status = (SELECT id FROM accounts.account_statuses WHERE slug = 'deleted')
  AND deleted_at IS NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Next: Building the Application

In Part 4, we'll implement the Next.js application:

Auth Context: React hooks for managing multi-tenant state
Permission Guards: Client-side components that respect account and app-level roles
Account Switcher: Seamless organization switching with app role awareness
Subdomain Routing: Automatic account context from URLs
Workspace Management: Add, remove, invite users to workspaces

Building a multi-tenant organization system with Supabase part 3

Series navigation

Series description