# PostgreSQL Database Functions for Multi-Tenant SaaS

> Use built-in database functions for permissions, roles, subscriptions, and MFA checks. Includes has_permission, is_account_owner, has_active_subscription, and more.

*Canonical: https://makerkit.dev/docs/tanstack-supabase/development/database-functions*

---

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.

{% sequence title="Database Functions Reference" description="Built-in functions for multi-tenant operations" %}

[Call functions from SQL and RPC](#calling-database-functions)

[Account ownership and membership](#account-functions)

[Permission checks](#permission-functions)

[Subscription and billing](#subscription-functions)

[MFA and authentication](#authentication-functions)

{% /sequence %}

## Calling Database Functions

### From SQL (RLS Policies)

Use functions directly in SQL schemas and RLS policies:

```sql
-- 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:

```tsx
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.

```sql
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:**

```sql
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.

```sql
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:**

```sql
-- 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.

```sql
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.).

```sql
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:**

```tsx
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.

```sql
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:**

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

**Example RLS:**

```sql
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:**

```tsx
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](/docs/tanstack-supabase/development/permissions-and-roles) for adding custom permissions.

## Subscription Functions

### has_active_subscription

Check if an account has an active or trialing subscription.

```sql
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:**

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

**Example application code:**

```tsx
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)

```sql
public.is_super_admin() returns boolean
```

**Example RLS:**

```sql
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)

```sql
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).

```sql
public.is_aal2() returns boolean
```

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

**Example:**

```sql
-- 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.

```sql
public.is_set(field_name text) returns boolean
```

**Example:**

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

## Function Reference Table

| Function | Purpose | Common Use |
|----------|---------|------------|
| `is_account_owner(account_id)` | Check account ownership | Delete, billing access |
| `has_role_on_account(account_id, role?)` | Check membership/role | View, edit access |
| `is_team_member(account_id, user_id)` | Check specific user membership | Team operations |
| `can_action_account_member(account_id, user_id)` | Check member management rights | Remove, role change |
| `has_permission(user_id, account_id, permission)` | Check granular permission | Feature access |
| `has_active_subscription(account_id)` | Check billing status | Paid features |
| `is_super_admin()` | Check admin status | Admin operations |
| `is_mfa_compliant()` | Check MFA compliance | Security policies |
| `is_aal2()` | Check MFA authentication | Sensitive operations |

## Related Resources

- [Permissions and Roles](/docs/tanstack-supabase/development/permissions-and-roles) for adding custom permissions
- [Database Schema](/docs/tanstack-supabase/development/database-schema) for extending your schema
- [Row Level Security](/docs/tanstack-supabase/security/row-level-security) for RLS patterns
- [Database Tests](/docs/tanstack-supabase/development/database-tests) for testing database functions