basalt / prisma/src / crossTenantScanSql

Function: crossTenantScanSql()

> crossTenantScanSql(options): string

Defined in: prisma/src/cross-tenant.ts:238

SQL for a cross-tenant scan function: a SECURITY DEFINER function that returns the identifiers of the rows matching where across every tenant.

crossTenantScanSql({
  name: 'stuck_jobs',
  table: 'jobs',
  tenantColumn: 'tenantId',
  columns: ['id'],
  where: `t."status" = 'PROCESSING' AND t."updatedAt" < now() - interval '15 minutes'`,
  role: 'app',        // the role the app connects as
  owner: 'app_owner', // must bypass the table's RLS (BYPASSRLS / superuser)
})

Parameters

options

CrossTenantScanSqlOptions

Returns

string

Security

This function is a deliberate RLS bypass: inside it the tenant policies do not apply. That is only safe because of what it returns — identifiers, and nothing else. Never widen it to a column holding tenant data (a name, an amount, an email): a caller with EXECUTE on it would read every tenant's data in one call, with no policy in the way. Fetch the data itself inside tenancy.run(tenantId, …), through the scoped client.

What the generated SQL does, statement by statement:

drops and recreates the function (idempotent, like rlsPolicySql);
pins search_path inside the function — a SECURITY DEFINER function without one is a privilege-escalation hole (a caller could point an unqualified name at a table of their own);
marks it STABLE and PARALLEL SAFE (it only reads);
caps and pages the result: p_limit is clamped to maxRows, and (p_after_tenant, p_after_id) is an ordered cursor, so a sweep can never pull millions of rows in one call;
REVOKE ALL … FROM PUBLIC, then GRANT EXECUTE to the application role only — the default on a new function is EXECUTE for PUBLIC, which for a SECURITY DEFINER function means everyone.

Every identifier is validated and quoted; nothing is interpolated raw.