Access contract

The access SQL module gives policies and the other SQL modules one way to ask "may the caller do this": can(scope, id, permission). The functions behind it come from the access model you pick in sql.modules.access, so the same policies work over a fixed role list, a role and permission catalog you already have, an authorization provider, or functions your app already wrote.

better-supabase sql add access

create policy "members read" on public.projects for select to authenticated
  using (organization_id in (select better_supabase.tenant_ids_with('projects.read')));

create policy "admins update" on public.projects for update to authenticated
  using (organization_id in (select better_supabase.tenant_ids_with('projects.update')))
  with check (organization_id in (select better_supabase.tenant_ids_with('projects.update')));

create policy "admins delete the organization" on public.organizations for delete to authenticated
  using (
    id = (select better_supabase.current_tenant_id())
    and (select better_supabase.can('organization', better_supabase.current_tenant_id(), 'organizations.delete'))
  );

tenant_ids_with() returns a set, so Postgres runs it once per statement instead of once per row. Use it in using and with check whenever the tenant comes from the row. Keep can() for checks whose arguments are constants for the statement, and wrap the call in (select ...) so Postgres evaluates it once.

The contractCopy Anchor Link

Permission keys are dotted strings. A granted * matches every key, and members.* matches every key that starts with members.. SQL modules check the keys in their permissions config, so sql.modules.invitations.permissions.invite can be organization.members.invite in an app that already uses that name:

export default defineConfig({
  sql: {
    modules: {
      access: {},
      invitations: { permissions: { invite: "organization.members.invite" } },
    },
  },
});

Keys follow one scheme, <area>.<verb>, with read for viewing. Platform-wide actions are checked with is_platform(). An action whose default is another action's key follows that key as configured, unless you set the action itself.

ModelsCopy Anchor Link

sql.modules.access.model picks where permissions come from. Every model writes the same contract, so you can change models without touching your policies.

roles (the default)Copy Anchor Link

Roles and their keys live in the config. The tenant module's memberships table holds each member's role, and its role check follows the names here. Platform permissions come from the platform_permissions claim (platformClaim renames it), an array your access token hook writes.

export default defineConfig({
  sql: {
    modules: {
      access: {
        roles: {
          owner: ["*"],
          admin: ["organization.*", "members.*", "billing.*"],
          member: ["organization.read", "projects.*"],
        },
      },
    },
  },
});

Without roles, the defaults are owner (*), admin, member and viewer. can_assign() lets a member hand out a role only when their own role holds every key of it, so an admin can't make someone an owner.

catalogCopy Anchor Link

Roles, permissions and their links are rows in tables. A per-tenant override table can grant or deny a key for one role in one tenant (a deny wins), and a platform table assigns platform roles to users. In managed mode the block creates the tables; in adopt mode you point it at yours:

export default defineConfig({
  sql: {
    modules: {
      tenant: {
        mode: "adopt",
        tables: { memberships: "public.organization_members" },
        columns: {
          memberships: {
            tenant: "organization_id",
            role: "role_id",
            lastUsedAt: null,
          },
        },
      },
      access: {
        mode: "adopt",
        model: "catalog",
        tables: {
          roles: "public.roles",
          permissions: "public.permissions",
          rolePermissions: "public.role_permissions",
          overrides: "public.organization_role_overrides",
          platformAssignments: "public.user_roles",
        },
        columns: {
          roles: { key: "slug" },
          overrides: { tenant: "organization_id" },
        },
      },
    },
  },
});

null marks an optional table or column you don't have. With overrides: null, role defaults are the only source.

providerCopy Anchor Link

With an authorization provider in the config's authorization key, can() and tenant_ids_with() call the provider's idsWith template at its tenantScope, and platform_can() calls its isPlatform. The id type is that scope's idType.

export default defineConfig({
  authorization: authorizationProvider(),
  sql: {
    modules: {
      organizations: {},
      invitations: {},
      access: { model: "provider" },
    },
  },
});

sql add refuses, instead of guessing, when the config has no authorization, or when sql.modules.access.idType disagrees with the tenant scope's idType.

The provider's functions check role and scope, never row conditions. Every permission key a SQL module checks must be sqlComplete: true in the provider's permissions, or sql add and doctor stop. Map an action to another key with sql.modules.<module>.permissions.

can_assign() calls the provider's canAssign, and sql.modules.access.functions.canAssign replaces it. Without either, only the service role assigns roles, unless the tenant module is installed, and doctor BS411 warns.

can_user() and member_can() answer for another user through idsWithFor and isPlatformFor. A provider without them answers for the caller only: called for another user, they raise SQLSTATE 0A000 instead of returning null. can_assign_as(user, tenant, role) checks a stored user's authority, such as the inviter's when an invitation is accepted, through canAssignFor; sql.modules.access.functions.canAssignFor replaces it with your own template ({user}, {tenant} and {role}), under the provider and custom models.

Blocks next to a providerCopy Anchor Link

A provider already answers permission questions and may guard memberships, so some module checks change under the provider model:

customCopy Anchor Link

Your app already has the functions. Give the contract as SQL templates with {scope}, {id}, {permission}, {user}, {tenant} and {role} placeholders. can, tenantIdsWith, isPlatform and canAssign are required. member_can(user, tenant, permission) and can_assign_as(user, tenant, role) are required when the organizations or invitations modules are installed: those modules call them for a stored user, not only auth.uid(). canAssignFor ({user}, {tenant}, {role}) is the custom body of can_assign_as. Without it, write can_assign_as yourself so an invitation accept can check the inviter again.

export default defineConfig({
  sql: {
    modules: {
      access: {
        model: "custom",
        functions: {
          can: "public.authorize({scope}, {id}, {permission})",
          tenantIdsWith: "public.organizations_with({permission})",
          isPlatform: "public.is_staff({permission})",
          canAssign: "public.can_grant_role({tenant}, {role})",
        },
      },
    },
  },
});

With mode: "custom" instead, the block writes nothing at all and you write the contract functions under those names yourself. better-supabase sql print access lists their signatures, and doctor checks them.

Membership roles stored as idsCopy Anchor Link

An adopted memberships table often stores a foreign key to a roles table instead of the role name. sql.modules.tenant.options.roleThrough names that table, its key column and the column with the role name:

sql: {
  modules: {
    tenant: {
      mode: "adopt",
      tables: { memberships: "public.team_members" },
      columns: { memberships: { tenant: "team_id", role: "role_id" } },
      options: {
        roleThrough: { table: "public.team_roles", id: "id", column: "key" },
      },
    },
  },
},

Every model then reads role names through the table: has_organization_role, member_organization_ids and the memberships claim compare names, the roles model looks up each name's permissions, and can_assign receives the name. The organizations and invitations modules accept a role name or id, store the id, and refuse a role the table doesn't have (ORGANIZATION_ROLE_UNKNOWN, INVITATION_ROLE_UNKNOWN). A managed memberships table stores names, so the option is for mode: "adopt" only.

When the roles table also holds tenant custom roles, whose keys can repeat across tenants, add its tenant column as tenant (roleThrough: { table, id, column, tenant: "organization_id" }); under the catalog model, map sql.modules.access.columns.roles.tenant instead. A key then resolves among the tenant's own roles and the shared ones (no tenant), the tenant's first, and an id of another tenant's role is unknown in this one.

When the roles table also holds roles of another kind, such as the platform roles the invitations module's platformRoles.through points at, name the tenant roles with where, a condition on the roles row {row} (roleThrough: { table, id, column, where: "{row}.scope = 'organization'" }). Every lookup of a role name or id for a membership keeps to those rows, including SSO and waitlist joins: organization invitations, update_invitation and accept refuse any other role with INVITATION_ROLE_UNKNOWN, and update_member_role and ownership transfer with ORGANIZATION_ROLE_UNKNOWN. When the table is also platformRoles.through's, where is required on both, and doctor BS324 reports the side that lacks it.

With where set, the tenant module also puts a bs_role_scope trigger on the memberships table, so a direct write can't store a role outside it either: an insert or a role update through a client policy, the service role or an admin connection fails with MEMBERSHIP_ROLE_SCOPE (SQLSTATE 23514). The invitations module does the same on platformRoles.table with platformRoles.through.where (PLATFORM_ROLE_SCOPE). Both triggers check new writes only; rows stored before you set where stay until they change. Removing where drops the trigger.

Under the provider model, sql add reads the setting from the provider's roleSources: when one of them is the adopted memberships table, the tenant module uses its role.through lookup. An explicit roleThrough wins; set one to add where, which roleSources doesn't carry.

References within one tenantCopy Anchor Link

A foreign key checks that a referenced row exists, not that it belongs to the same tenant, so a task could point at another organization's project. sql.modules.tenant.options.sameTenant lists the references that must stay inside the row's tenant, and the tenant module puts a bs_same_tenant_<column> trigger on each table. Every insert, and every update of the column or the tenant column, then needs a referenced row with the same tenant, whoever writes it (a client policy, the service role or an admin connection); otherwise it fails with TENANT_MISMATCH (SQLSTATE 23514).

sql: {
  modules: {
    tenant: {
      options: {
        sameTenant: [
          { table: "public.tasks", column: "project_id", references: "public.projects" },
          {
            table: "public.task_settings",
            column: "template_id",
            references: {
              table: "public.templates",
              column: "id",
              tenant: "organization_id",
              where: "{row}.type = 'task'",
            },
          },
        ],
      },
    },
  },
},

match ties the reference to other columns of the row being written. An asset on a job must belong to the job's customer, and a material on a quote line must belong to the quote's customer:

sameTenant: [
  {
    table: "public.jobs",
    column: "asset_id",
    references: "public.assets",
    match: { customer_id: "customer_id" },
  },
],

Each key is a column of the row and each value a column of the referenced row, and the two must hold the same value. The comparison is is not distinct from, so a job without a customer accepts only an asset without one. An update of a match column runs the check again, so moving the job to another customer fails while its asset still belongs to the first one.

A match key can also read a column through another reference of the row, written <reference column>.<column>. A quote asset must belong to the quote's customer, and the asset row holds quote_id, not the customer:

sameTenant: [
  {
    table: "public.quote_assets",
    column: "asset_id",
    references: "public.assets",
    match: { "quote_id.customer_id": "customer_id" },
    through: { quote_id: "public.quotes" },
  },
],

through names the table each dotted key's reference column points at, as "schema.table" or { table, column } when its key is not id. The check reads customer_id from the quote that quote_id points at and compares it with the asset's customer_id, with is not distinct from as above, so a row without a quote accepts only an asset without a customer. An update of quote_id runs the check again. A later change to the quote's customer is not checked here; give that table its own entry or trigger if it can change.

Every trigger calls the one function better_supabase.same_tenant() with the entry as its arguments. A null reference passes, so a nullable column stays optional, and the foreign key still reports a row that does not exist. The check runs on the referencing table only: it does not stop a change to the tenant column of a referenced row. Rows stored before you add an entry are checked when they change. The triggers are written in managed and adopt mode; in custom mode the module writes no SQL.

Composite foreign keys are the trigger-free alternative: a unique (id, organization_id) on the referenced table and a foreign key (project_id, organization_id) references projects (id, organization_id) on the referencing one. Postgres then checks both directions, including a referenced row moving tenants, at the cost of an extra unique index per table and a tenant column in every key. Use sameTenant when the tables already exist and their keys should stay as they are, or when the reference also needs a where condition.

Disabled tenants and usersCopy Anchor Link

sql.modules.access.disabled names columns that switch a tenant or a user off when they are set, such as disabled_at. A disabled tenant or user gets no permissions, and membership_claims() leaves them out of the token.

sql: {
  modules: {
    access: {
      disabled: {
        tenant: "public.organizations.disabled_at",
        user: "public.profiles.disabled_at",
        userKey: "user_id",
      },
    },
  },
},

The tenant column is matched against the table's id. userKey names the column matched against the user id; it defaults to id.

The checks are better_supabase.tenant_disabled(id) and better_supabase.user_disabled(user_id). The access module's file defines them; the tenant module's file defines them only when access isn't installed (or runs in custom mode), so each function lives in one schema file.

Either subject also takes an active row: a table, the id column, and a nullable disabledAt column, a status column with its active values, or both. Only a row that matches counts as active, so a tenant or user without a row is disabled.

disabled: {
  tenant: {
    table: "public.organizations",
    id: "id",
    status: "state",
    active: ["active", "trial"],
  },
  user: { table: "public.profiles", id: "id", disabledAt: "banned_at" },
},

Under the provider model, sql add and sql sync read both from the provider's suspension. A subject set in sql.modules.access.disabled keeps its own setting. The data-lifecycle module disables a tenant awaiting deletion by setting the disabledAt column, so a tenant row with only a status column is not switched off during the grace period.

Suspended membershipsCopy Anchor Link

A suspended membership keeps its role but grants nothing in that tenant. The tenant module reads it from memberships.disabledAt, a nullable timestamp column on the memberships table: member_can, tenant_ids_with, member_permissions, can_assign_as, permission_claims, member_organization_ids, has_organization_role and membership_claims skip a membership while the column is set. The other memberships of the same user keep working.

A managed memberships table gets the disabled_at column. An adopted table opts in by mapping the column, so an existing table without it keeps working:

sql: {
  modules: {
    tenant: {
      mode: "adopt",
      tables: { memberships: "public.organization_users" },
      columns: { memberships: { disabledAt: "disabled_at" } },
    },
  },
},

Under the provider and custom models, member_can and tenant_ids_with also refuse a tenant whose membership row is suspended, on top of what the provider or your functions decide. The organizations block suspends and resumes members with suspend_member and resume_member.

The active tenantCopy Anchor Link

current_tenant_id() and the tenant() plugin need the tenant a request works in. sql.modules.access.activeTenant says where it comes from.

With 'resolver', pass tenant to the server. It runs once per request, and the tenant it returns becomes context.tenant for the tenant() plugin, the better_supabase.tenant setting for ctx.sql, and the x-bs-tenant header for ctx.db. current_tenant_id() returns it only while the caller is a member, so a tampered header or slug grants nothing.

import "server-only";
import { createServer } from "better-supabase/server";

import { betterSupabase } from "./index";

export const bs = createServer(betterSupabase, {
  tenant: async (request, auth) => {
    const slug = new URL(request.url).pathname.split("/")[2];
    return slug ? organizationIdForSlug(slug) : undefined;
  },
});

Under createNext, Server Components, actions and private caches have no request URL: the resolver gets a request with the incoming headers and a placeholder URL. Pass the tenant from the route params instead. It goes where the resolver's result goes, so current_tenant_id() and the tenant() plugin check it the same way, and a tenant the caller doesn't belong to grants nothing:

export default async function Customers({
  params,
}: PageProps<"/[organizationId]/customers">) {
  const { organizationId } = await params;
  const { db } = await bs.context({ tenant: organizationId });
  const customers = await db.customers.findMany().orThrow();
  return <CustomerList customers={customers} />;
}

bs.cached({ tenant }) does the same inside 'use cache: private'. Take the tenant as an argument of your function, because Next.js keys the cache entry on the arguments:

async function getCustomers(organizationId: string) {
  "use cache: private";
  const { db } = await bs.cached({ tenant: organizationId });
  return db.customers.findMany().orThrow();
}

Actions take it from their validated input with bs.action({ input, tenant: (input) => input.organizationId }, fn). bs.route() and bs.context(request) pass the real request, so a resolver that reads the URL works there.

A route that already knows the tenant passes it directly: bs.context(request, { tenant }). contextFromResolution runs only synchronous resolvers; pass { tenant } to it when yours is async.

With 'claim', doctor --as <user id> calls your access token hook and warns when the claims it returns have no tenant.

Claims for the access token hookCopy Anchor Link

The block never owns your access token hook. It gives you claim builders to call from it: membership_claims(user) from the tenant module and permission_claims(user) from this one.

create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb language plpgsql stable set search_path = '' as $$
declare
  user_id uuid := (event ->> 'user_id')::uuid;
begin
  event := jsonb_set(event, '{claims,memberships}', better_supabase.membership_claims(user_id));
  return jsonb_set(event, '{claims,permissions}', better_supabase.permission_claims(user_id));
end $$;

sql.modules.tenant.options.claimFormat picks the memberships claim's form: 'array' (the default, [{ scope, id, roles }]) or 'map' ({ "<tenant id>": "<role>" }).