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 blocks.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 blocks.invitations.permissions.invite can be organization.members.invite in an app that already uses that name:

export default defineConfig({
  blocks: {
    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

blocks.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({
  blocks: {
    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({
  blocks: {
    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.

provider is the default model whenever the config sets authorization, so every module check that goes through can() or tenant_ids_with() answers from the provider. Set another model to keep the built-in roles; doctor then reports policies and functions that call the access contract (BS414), because they answer from roles the provider never sees.

export default defineConfig({
  authorization: authorizationProvider(),
  blocks: {
    organizations: {},
    invitations: {},
    access: {},
  },
});

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

The tenant is one level. For an organization with environments under it, the provider declares both as scopes and picks the tenant level with tenantScope; see nested tenancy.

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 blocks.<module>.permissions.

can_assign() calls the provider's canAssign, and blocks.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; blocks.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({
  blocks: {
    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. blocks.tenant.options.roleThrough names that table, its key column and the column with the role name:

blocks: {
  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 blocks.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. blocks.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).

blocks: {
  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.

sql sync can derive the entries from the foreign keys in the schema files instead. With sameTenant: { auto: true }, every single-column foreign key from a table with the tenant column to another table with it gets an entry, unless the key already includes the tenant column. The scan reads references on a column, foreign key (...) references table constraints and alter table ... add constraint ... foreign key; a key without a column list references the table's declared primary key. Keys over more than one column, and references that need match, through or where, go in entries. A listed entry wins over the derived one for the same table and column:

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

A derived entry renders the same trigger as the same entry listed by hand. doctor reports each such foreign key that has no bs_same_tenant_<column> trigger (BS340).

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.

Delegated tokensCopy Anchor Link

A delegated token is one an OAuth client or agent acts with: it carries a client_id, or an RFC 8693 act chain that is not a support or impersonation session. The access functions narrow it in every model: member_can, member_can_while_deleting, platform_can and tenant_ids_with ask better_supabase.cap_allows(permission), which is true only when one of the token's scopes maps to the permission. The user's own roles still apply, so the token gets the overlap.

Map scopes to permission keys in authorization.delegation.scopes (the provider's map) or blocks.access.scopes, which is merged over it. A scope without an entry grants the key with its own name, and ["*"] grants every key.

blocks: {
  access: {
    scopes: {
      "posts:write": ["posts.*"],
      "mcp:admin": ["*"],
    },
  },
},

cap_allows also reads the bs_permission_cap claim that forContext(context, { permissions }) sets, and refuses every key outside it. A check about another user (an inviter, the holders of a permission) is not narrowed by the caller's token, and the service role is never narrowed. token_scope_allows(scope) in the actor module stays available for a policy that checks the literal scope.

A provider that maps scopes at runtime (per client, or from RFC 9396 authorization_details) sets the delegation.permissions hook on its authorization hooks. forContext calls it for a delegated context and caps the work to the keys it returns, narrowed by its own permissions. A throw grants nothing.

Disabled tenants and usersCopy Anchor Link

blocks.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.

blocks: {
  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 blocks.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.

Every model also renders member_can_while_deleting(user, tenant, permission), the same check as member_can without the disabled-tenant test. The data-lifecycle module calls it so a member can cancel a pending deletion while the tenant is disabled.

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:

blocks: {
  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. blocks.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.

Removed members and the tenant claimCopy Anchor Link

The tenant claim lives in the access token, and a token stays valid until it expires (jwt_expiry in supabase/config.toml: 3600 seconds by default and in the fixture). What a removed member can still do depends on which check reads the claim:

A claim in app_metadata also survives a refresh. With the 'claim' source, the tenant module (managed or adopted) clears app_metadata.tenant_id when the membership it points at is deleted, so the next token carries no tenant. In custom mode nothing clears it, and a removed member keeps the claim on every new token until you clear it.

To close that window sooner:

• Lower jwt_expiry (for example to 600). Clients refresh more often, and a refresh drops the tenant once the claim's source no longer has it (an access token hook that reads the membership table, or the cleared app_metadata).
• End the removed user's sessions so they get no new token: call supabase.auth.admin.signOut(jwt, "global") with one of their access tokens, or delete their rows in auth.sessions from a security definer function. The current access token still works until it expires, but bs.proxy({ endedSession }) turns away their page loads right away (see Ended sessions).
• Prefer the module's current_tenant_id() or tenant_ids_with() over the raw claim in your own policies, since both read the membership table.

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 $$;

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