PostgreSQL Database Functions for Multi-Tenant SaaS

Makerkit includes built-in PostgreSQL functions for common multi-tenant operations like permission checks, role verification, and subscription status. Use these functions in RLS policies and application code to enforce consistent security rules across your database.

Calling Database Functions

From SQL (RLS Policies)

Use functions directly in SQL schemas and RLS policies:

-- In an RLS policy
create policy "Users can view their projects"
  on public.projects
  for select
  using (
    public.has_role_on_account(account_id)
  );

From Application Code (RPC)

Call functions via Supabase RPC:

const { data: isOwner, error } = await supabase.rpc('is_account_owner', {
  account_id: accountId,
});
if (isOwner) {
  // User owns this account
}

Account Functions

is_account_owner

Check if the current user owns an account. Returns true if the account is the user's personal account or if they created a team account.

public.is_account_owner(account_id uuid) returns boolean

Use cases:

• Restrict account deletion to owners
• Gate billing management
• Control team settings access

Example RLS:

create policy "Only owners can delete accounts"
  on public.accounts
  for delete
  using (public.is_account_owner(id));

has_role_on_account

Check if the current user has membership on an account, optionally with a specific role.

public.has_role_on_account(
  account_id uuid,
  account_role varchar(50) default null
) returns boolean

Parameters:

• account_id: The account to check
• account_role: Optional role name (e.g., 'owner', 'member'). If omitted, returns true for any membership.

Example RLS:

-- Any member can view
create policy "Members can view projects"
  on public.projects
  for select
  using (public.has_role_on_account(account_id));
-- Only owners can update
create policy "Owners can update projects"
  on public.projects
  for update
  using (public.has_role_on_account(account_id, 'owner'));

is_team_member

Check if a specific user is a member of a team account.

public.is_team_member(
  account_id uuid,
  user_id uuid
) returns boolean

Use case: Verify team membership when the current user context isn't available.

can_action_account_member

Check if the current user can perform actions on another team member (remove, change role, etc.).

public.can_action_account_member(
  target_team_account_id uuid,
  target_user_id uuid
) returns boolean

Logic:

1. If current user is account owner: true
2. If target user is account owner: false
3. Otherwise: Compare role hierarchy levels

Example:

const { data: canRemove } = await supabase.rpc('can_action_account_member', {
  target_team_account_id: teamId,
  target_user_id: memberId,
});
if (!canRemove) {
  throw new Error('Cannot remove a user with equal or higher role');
}

Permission Functions

has_permission

Check if a user has a specific permission on an account. This is the primary function for granular access control.

public.has_permission(
  user_id uuid,
  account_id uuid,
  permission_name app_permissions
) returns boolean

Parameters:

• user_id: The user to check (use auth.uid() for current user)
• account_id: The account context
• permission_name: A value from the app_permissions enum

Default permissions:

create type public.app_permissions as enum(
  'roles.manage',
  'billing.manage',
  'settings.manage',
  'members.manage',
  'invites.manage'
);

Example RLS:

create policy "Users with tasks.write can insert tasks"
  on public.tasks
  for insert
  with check (
    public.has_permission(auth.uid(), account_id, 'tasks.write'::app_permissions)
  );

Example RPC:

async function checkTaskWritePermission(accountId: string) {
  const { data: hasPermission } = await supabase.rpc('has_permission', {
    user_id: (await supabase.auth.getUser()).data.user?.id,
    account_id: accountId,
    permission: 'tasks.write',
  });
  return hasPermission;
}

See Permissions and Roles for adding custom permissions.

Subscription Functions

has_active_subscription

Check if an account has an active or trialing subscription.

public.has_active_subscription(account_id uuid) returns boolean

Returns true when:

• Subscription status is active
• Subscription status is trialing

Returns false when:

• No subscription exists
• Status is canceled, past_due, unpaid, incomplete, etc.

Example RLS:

create policy "Only paid accounts can create projects"
  on public.projects
  for insert
  with check (
    public.has_active_subscription(account_id)
  );

Example application code:

const { data: isPaid } = await supabase.rpc('has_active_subscription', {
  account_id: accountId,
});
if (!isPaid) {
  redirect('/pricing');
}

Authentication Functions

is_super_admin

Check if the current user is a super admin. Requires:

• User is authenticated
• User has super_admin role
• User is currently signed in with MFA (AAL2)

public.is_super_admin() returns boolean

Example RLS:

create policy "Super admins can view all accounts"
  on public.accounts
  for select
  using (public.is_super_admin());

is_mfa_compliant

Check if the current user meets MFA requirements. Returns true when:

• User enabled MFA and is signed in with MFA (AAL2)
• User has not enabled MFA (AAL1 is sufficient)

public.is_mfa_compliant() returns boolean

Use case: Allow users without MFA to continue normally while enforcing MFA for users who enabled it.

is_aal2

Check if the current user is signed in with MFA (AAL2 authentication level).

public.is_aal2() returns boolean

Use case: Require MFA for sensitive operations regardless of user settings.

Example:

-- Require MFA for billing operations
create policy "MFA required for billing changes"
  on public.billing_settings
  for all
  using (public.is_aal2());

Configuration Functions

is_set

Check if a configuration value is set in the public.config table.

public.is_set(field_name text) returns boolean

Example:

-- Check if a feature flag is enabled
select public.is_set('enable_new_dashboard');

Function Reference Table

Related Resources

• Permissions and Roles for adding custom permissions
• Database Schema for extending your schema
• Row Level Security for RLS patterns
• Database Tests for testing database functions