Claims-Based RLS, Fail-Closed Guards & Tenant Isolation

When an SDK controls physical door locks, every ambiguity in the authorization path is a potential unlocked door. Over the last sprint the UnlockOS platform went through a security-focused sweep: row-level security was re-enabled on tenant tables, privilege checks moved from a legacy lookup table to signed JWT claims, SECURITY DEFINER views were removed, payment functions gained module-load environment guards, and an implicit "auto-resolve" code path in the token manager was deleted in favor of an explicit parameter.

None of these changes shipped a new feature. All of them made the system harder to misuse. This article walks through the patterns behind them, in a form you can apply to any multi-tenant, security-critical backend.

---

1. Trust the token, not the table

A classic privilege-check implementation asks the database "is this user an admin?" by selecting from a table:

-- Anti-pattern: privilege check depends on a readable table
create or replace function public.is_platform_admin()
returns boolean
language sql
stable
as $$
  select exists (
    select 1 from public.platform_admins
    where user_id = auth.uid()
  );
$$;

This has three problems. First, it couples every RLS policy evaluation to an extra table scan. Second, the table itself needs its own RLS policies — and if those policies reference is_platform_admin(), you get recursion. Third, the source of truth for "who is an admin" now lives in two places: the auth provider and the application database, which drift.

The fix is to make the signed token the single source of truth. The identity provider stamps the claim at login; the database reads it without touching any table:

create or replace function public.is_platform_admin()
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select coalesce(
    (
      nullif(current_setting('request.jwt.claims', true), '')::jsonb
        -> 'app_metadata' ->> 'is_platform_admin'
    )::boolean,
    false
  );
$$;

Two details matter more than they look:

• current_setting('request.jwt.claims', true) uses missing_ok = true, so an unauthenticated or background context yields NULL rather than an exception. Combined with coalesce(..., false), the function fails closed.
• set search_path = '' prevents search-path hijacking on a SECURITY DEFINER function — a well-known Postgres privilege-escalation vector.

Because app_metadata is only writable by the service role and is signed into the JWT, a client cannot forge the claim. And because the function no longer reads a table, it can be used freely inside policies without recursion.

2. Turn RLS back on — with policies you can reason about

Tenant tables that are "protected by the application layer" are not protected. Any leaked anon key, any forgotten .eq('organization_id', ...) filter, and the isolation is gone. Re-enabling RLS on a core table like organizations is only safe when the policies are cheap and non-recursive — which is exactly what the claims-based helper enables:

alter table public.organizations enable row level security;

create policy organizations_select_self
  on public.organizations
  for select
  to authenticated
  using (
    public.is_platform_admin()
    or id = public.current_organization_id()
  );

create policy organizations_write_admin_only
  on public.organizations
  for all
  to authenticated
  using (public.is_platform_admin())
  with check (public.is_platform_admin());

Always write both using and with check on mutating policies. using controls which rows you may see and target; with check controls what the row may look like after the write. Omitting with check lets an authorized user move a row into another tenant.

Views inherit the wrong identity by default

A Postgres view historically executes with the privileges of its owner, which silently bypasses RLS on the underlying tables. If you expose reporting views to clients, flip them to invoker semantics:

alter view public.v_user_transactions set (security_invoker = on);

Then add the condition you actually meant to enforce — for example, that a user's contact address is verified before transaction history is readable:

create policy user_transactions_select_owner
  on public.user_transactions
  for select
  to authenticated
  using (
    user_id = auth.uid()
    and coalesce(
      (auth.jwt() -> 'user_metadata' ->> 'email_verified')::boolean,
      false
    )
  );

3. Fail closed at module load, not at request time

Edge/serverless functions that handle payments or lock credentials typically read secrets from the environment. The dangerous version reads them lazily:

// Anti-pattern: a missing secret becomes an empty string at request time
const stripe = new Stripe(Deno.env.get('STRIPE_SECRET_KEY') ?? '');

A misconfigured deployment now boots successfully and fails halfway through a checkout, possibly after a lock grant has already been issued. Instead, validate the whole environment once, at module load, and refuse to start:

const REQUIRED_ENV = [
  'STRIPE_SECRET_KEY',
  'STRIPE_WEBHOOK_SECRET',
  'SUPABASE_URL',
  'SUPABASE_SERVICE_ROLE_KEY',
] as const;
type RequiredEnvKey = (typeof REQUIRED_ENV)[number];
export function requireEnv(
  keys: readonly RequiredEnvKey[],
): Readonly<Record<RequiredEnvKey, string>> {
  const missing: string[] = [];
  const resolved = {} as Record<RequiredEnvKey, string>;
  for (const key of keys) {
    const value = Deno.env.get(key);
    if (!value || value.trim() === '') {
      missing.push(key);
      continue;
    }
    resolved[key] = value;
  }
  if (missing.length > 0) {
    throw new Error(`[boot] missing required environment variables: ${missing.join(', ')}`);
  }
  return Object.freeze(resolved);
}

The as const tuple plus the derived RequiredEnvKey union means the returned record is fully typed: env.STRIPE_SECRET_KEY is string, never string | undefined, and a typo in a key name is a compile error.

One practical catch: a hard throw at import time breaks unit tests and static analysis that merely import the module. Gate the guard rather than weakening it:

const SKIP_BOOT_GUARD = Deno.env.get('SKIP_ENV_GUARD') === 'true';
export const env = SKIP_BOOT_GUARD
  ? ({} as Readonly<Record<RequiredEnvKey, string>>)
  : requireEnv(REQUIRED_ENV);

The escape hatch is opt-in, explicitly named, and never set in production deploy configuration.

4. Never guess between test and live credentials

A related class of bug: a settings screen offers a "test mode" toggle, but the flag is only held in component state and never persisted. On the next request the server sees undefined, falls back to the live key, and charges a real card during a rehearsal.

The defensive shape is to treat "unknown mode" as an error, not as a default:

export type PaymentMode = 'test' | 'live';
export interface PaymentCredentials {
  mode: PaymentMode;
  secretKey: string;
  publishableKey: string;
}
export function resolvePaymentCredentials(
  settings: { isTestMode?: boolean | null; testKeys?: KeyPair; liveKeys?: KeyPair },
): PaymentCredentials {
  if (settings.isTestMode === undefined || settings.isTestMode === null) {
    throw new ConfigurationError(
      'PAYMENT_MODE_UNRESOLVED',
      'isTestMode must be persisted explicitly; refusing to default to live keys',
    );
  }
  const mode: PaymentMode = settings.isTestMode ? 'test' : 'live';
  const keys = mode === 'test' ? settings.testKeys : settings.liveKeys;
  if (!keys) {
    throw new ConfigurationError('PAYMENT_KEYS_MISSING', `no ${mode} keys configured`);
  }
  return { mode, secretKey: keys.secretKey, publishableKey: keys.publishableKey };
}

The rule generalizes: when a boolean selects between a safe and a dangerous path, undefined must be an error, not the dangerous path.

5. Delete implicit context resolution

A token manager that caches credentials for multiple facilities had a convenience overload: if the caller omitted facilityId, it would "auto-resolve" the current one from ambient state. In a single-tenant session that works. In a multi-facility admin session it can hand you a token minted for the wrong property — a cross-tenant credential leak with a friendly API surface.

The fix was to remove the overload entirely and require the parameter:

export class TokenManager {
  private readonly cache = new Map<string, CachedToken>();
  async getAccessToken(facilityId: string): Promise<string> {
    if (!facilityId) {
      throw new UnlockOSError(
        'FACILITY_ID_REQUIRED',
        'facilityId is required; ambient resolution was removed to prevent cross-tenant token reuse',
      );
    }
    const cached = this.cache.get(facilityId);
    if (cached && cached.expiresAt - Date.now() > REFRESH_SKEW_MS) {
      return cached.accessToken;
    }
    const issued = await this.refresh(facilityId);
    this.cache.set(facilityId, issued);
    return issued.accessToken;
  }
  invalidate(facilityId: string): void {
    this.cache.delete(facilityId);
  }
}

Note that the cache is keyed by tenant, not global — a shared cache slot is the same bug in a different costume. And invalidate is per-tenant so that disconnecting one integration does not blow away unrelated sessions.

6. Authorization: enumerate the paths, return the reason

Broadening an authorization check is where 403 bugs get "fixed" by accident. An ownership check that only recognized the guest who made a reservation returned 403 when a property operator tried to cancel it. The tempting patch is to loosen the predicate. The safer patch is to make each grant path explicit and observable:

export type AuthzDecision =
  | { allowed: true; via: 'reservation_guest' | 'facility_operator' | 'platform_admin' }
  | { allowed: false; reason: 'not_found' | 'not_owner' | 'facility_mismatch' };
export async function verifyReservationAccess(
  ctx: RequestContext,
  reservationId: string,
): Promise<AuthzDecision> {
  const reservation = await ctx.db.findReservation(reservationId);
  if (!reservation) return { allowed: false, reason: 'not_found' };
  if (ctx.actor.kind === 'platform_admin') {
    return { allowed: true, via: 'platform_admin' };
  }
  if (ctx.actor.kind === 'guest' && reservation.guestId === ctx.actor.id) {
    return { allowed: true, via: 'reservation_guest' };
  }
  if (ctx.actor.kind === 'operator') {
    return ctx.actor.facilityIds.includes(reservation.facilityId)
      ? { allowed: true, via: 'facility_operator' }
      : { allowed: false, reason: 'facility_mismatch' };
  }
  return { allowed: false, reason: 'not_owner' };
}

The discriminated union pays for itself twice. Callers must handle both branches (the compiler enforces it), and the via / reason fields go straight into the audit log, so "who was allowed to cancel this reservation, and under which rule?" is answerable after the fact:

const decision = await verifyReservationAccess(ctx, reservationId);
await ctx.audit.record({
  action: 'reservation.cancel',
  reservationId,
  actorId: ctx.actor.id,
  outcome: decision.allowed ? 'allowed' : 'denied',
  rule: decision.allowed ? decision.via : decision.reason,
});
if (!decision.allowed) throw new ForbiddenError(decision.reason);

Operator access is scoped by facilityIds — an operator of property A still gets a denial (with the precise reason facility_mismatch) on property B. Widening a role is fine; widening it without a scope predicate is not.

7. Validate identifiers before they reach the database

Feature-flag overrides are resolved by user_id and organization_id. When the UI synthesized a sentinel string like "platform" for the non-tenant case, the override query received a non-UUID value. Postgres rejects it with 22P02 invalid input syntax for type uuid, the resolver catches the error, and — depending on the catch block — the flag either fails open or the whole page errors.

Two fixes were applied together, and both are worth copying:

const UUID_RE =
  /^[0-9a-f]{8}-[0-9a-f]{4}-[1-8][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
export function isUuid(value: unknown): value is string {
  return typeof value === 'string' && UUID_RE.test(value);
}
export async function resolveOverride(
  scope: { userId?: string | null; organizationId?: string | null },
): Promise<Override | null> {
  const filters: OverrideFilter[] = [];
  if (isUuid(scope.userId)) filters.push({ column: 'user_id', value: scope.userId });
  if (isUuid(scope.organizationId)) {
    filters.push({ column: 'organization_id', value: scope.organizationId });
  }
  if (filters.length === 0) return null;
  return queryOverrides(filters);
}

1. Skip, don't crash. A malformed scope identifier means "no override applies", which resolves to the flag's default — a deterministic outcome instead of an exception path.
2. Remove the sentinel at the source. Encoding "no organization" as a magic string inside a UUID-typed field is a type-system lie. null plus a separate scope: 'platform' | 'organization' discriminator keeps the type honest.

The accompanying test change is small but instructive: the override resolution tests were rewritten to use real UUIDs instead of strings like "user-1". Tests that use data shapes the production schema would reject are tests that validate a fiction.

const USER_A = '3f1c2a7e-9b4d-4c2f-8a51-0b7d6e5f4a3c';
const ORG_A = '8d2e4b1a-6c3f-4e5d-9a7b-1c2d3e4f5a6b';
it('prefers a user-scoped override over an organization-scoped one', async () => {
  await seedOverride({ flag: 'booking.grid_view', userId: USER_A, enabled: true });
  await seedOverride({ flag: 'booking.grid_view', organizationId: ORG_A, enabled: false });
  const result = await resolveFlag('booking.grid_view', { userId: USER_A, organizationId: ORG_A });
  expect(result).toEqual({ enabled: true, source: 'user_override' });
});

8. Gates belong on routes, not only on menus

Hiding a menu item behind a feature flag is a UX decision. It is not access control — the route is still reachable by typing the URL. Enforce the gate where navigation is resolved, and again on the server:

export function createFlagGuard(flags: FlagResolver) {
  return async (route: RouteDefinition, ctx: RequestContext) => {
    if (!route.requiredFlag) return { proceed: true } as const;
    const { enabled } = await flags.resolve(route.requiredFlag, {
      userId: ctx.actor.id,
      organizationId: ctx.actor.organizationId,
      facilityId: ctx.facilityId,
    });
    if (enabled) return { proceed: true } as const;
    return { proceed: false, redirectTo: '/not-available' } as const;
  };
}

The resolver takes the full scope — user, organization, and facility — so that a facility-level override cannot be bypassed by an organization-level default, and so the precedence order is defined in exactly one place rather than re-derived by each caller.

9. Schema changes deserve the same pipeline as code

A final, unglamorous item: database migrations moved into CI/CD. Ad-hoc migrations applied by hand are how a policy ends up existing in staging and not in production — the worst possible failure mode for RLS, because the application keeps working while isolation silently disappears in one environment.

set -euo pipefail
supabase db lint --level warning
supabase db diff --linked --schema public > /tmp/drift.sql
if [ -s /tmp/drift.sql ]; then
  echo "::error::linked database has drifted from migrations"
  cat /tmp/drift.sql
  exit 1
fi
supabase migration up --linked

The drift check is the important line. It turns "someone changed production by hand" from an invisible condition into a failed build. A companion guard in the local pre-commit hooks verifies that the linked project reference matches the current worktree, so a developer with several branches checked out cannot point a migration at the wrong environment.

---

Summary

The through-line in all of these changes is the same: remove the paths where the system has to guess.

Guess	Replacement
Admin status read from a mutable table	Signed JWT claim, coalesce(..., false)
Application-layer tenant filtering	RLS with using + with check
View runs as owner	security_invoker = on
Missing secret becomes ''	Throw at module load
Undefined test-mode flag falls back to live keys	Throw on unresolved mode
Ambient facility resolution	Required facilityId parameter
Non-UUID sentinel hits the database	Validate and skip, drop the sentinel
Menu-only feature gating	Route guard + server check
Hand-applied migrations	Pipeline with drift detection

Every one of these is a small, boring change. Together they mean that when the SDK says a door may open, there is exactly one code path that reached that conclusion, it is named in the audit log, and no environment misconfiguration could have widened it.