Multi-Tenant Access Control: RLS, Claims, and Idempotent Webhooks

A smart-lock platform is only as trustworthy as its weakest boundary. When a single deployment serves many facilities — hotels, coworking spaces, residential buildings — every query, every JWT claim, and every inbound webhook is a potential cross-tenant leak. This article walks through a set of hardening patterns we applied across the authorization stack, the database layer, and the external-integration boundary, with generalizable code you can apply to any multi-tenant system.

1. Row-Level Security Needs WITH CHECK, Not Just USING

A very common Postgres RLS mistake: writing an UPDATE policy with only a USING clause. USING controls which rows you may see and target. WITH CHECK controls what the row is allowed to look like afterwards. Without the latter, a tenant can select a row they legitimately own and rewrite its facility_id to another tenant — effectively donating (or stealing) a record across the tenancy boundary.

-- VULNERABLE: caller can move the row to another tenant
CREATE POLICY check_ins_update ON check_ins
  FOR UPDATE
  USING (facility_id = ANY (current_facility_ids()));
-- HARDENED: the post-image must also stay inside the caller's scope
DROP POLICY check_ins_update ON check_ins;
CREATE POLICY check_ins_update ON check_ins
  FOR UPDATE
  USING (facility_id = ANY (current_facility_ids()))
  WITH CHECK (facility_id = ANY (current_facility_ids()));

The rule of thumb: every FOR UPDATE and FOR INSERT policy must have a WITH CHECK. Audit them mechanically:

SELECT schemaname, tablename, policyname, cmd
FROM pg_policies
WHERE cmd IN ('UPDATE', 'INSERT')
  AND with_check IS NULL;

If that query returns rows, you have a tenant-hopping hole.

2. Make Tenant Keys Structurally Immutable

RLS is a runtime guard. Defense in depth says the tenant key should also be immutable at the schema level, so that a service-role script, a migration, or a future policy regression cannot silently relocate records.

CREATE OR REPLACE FUNCTION assert_facility_id_immutable()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.facility_id IS DISTINCT FROM OLD.facility_id THEN
    RAISE EXCEPTION 'facility_id is immutable (table %, id %)', TG_TABLE_NAME, OLD.id
      USING ERRCODE = '23514';
  END IF;
  RETURN NEW;
END;
$$;
CREATE TRIGGER check_ins_facility_id_immutable
  BEFORE UPDATE ON check_ins
  FOR EACH ROW EXECUTE FUNCTION assert_facility_id_immutable();

The complementary front-end change matters just as much: stop sending the tenant key in edit payloads at all. If the client never transmits facility_id on update, there is no field to tamper with, and the server derives scope purely from the authenticated session.

type CheckInEditablePayload = Omit<CheckInRow, 'id' | 'facility_id' | 'created_at'>;
export function toEditPayload(form: CheckInForm): CheckInEditablePayload {
  const { facility_id: _ignored, ...editable } = form;
  return editable;
}

TypeScript's Omit turns "please don't send the tenant id" from a code-review convention into a compile-time guarantee.

3. Claims-Based Authorization: Mint Early, Verify Everywhere

Role checks that hit the database on every request are slow and easy to forget. Encoding facility-staff membership as additive JWT claims gives you a single, cheap source of truth — but only if claims are minted at the right moments and always treated as untrusted input until verified.

Two moments matter: initial login and role/facility switching. A subtle bug class is minting claims only in the second path, which leaves a first-time user with an empty claim set and a mysteriously empty UI.

export interface FacilityStaffClaims {
  facility_roles: Record<string, StaffRole>;
}
export type StaffRole = 'owner' | 'manager' | 'member';
const WRITE_ROLES: ReadonlySet<StaffRole> = new Set(['owner', 'manager']);
export function canRead(claims: FacilityStaffClaims, facilityId: string): boolean {
  return Boolean(claims.facility_roles[facilityId]);
}
export function canWrite(claims: FacilityStaffClaims, facilityId: string): boolean {
  const role = claims.facility_roles[facilityId];
  return role !== undefined && WRITE_ROLES.has(role);
}

The same claim shape should drive the database policy, so the UI and the data layer can never disagree:

CREATE OR REPLACE FUNCTION current_facility_ids()
RETURNS uuid[]
LANGUAGE sql
STABLE
AS $$
  SELECT COALESCE(
    ARRAY(
      SELECT (jsonb_object_keys(
        COALESCE(auth.jwt() -> 'facility_roles', '{}'::jsonb)
      ))::uuid
    ),
    ARRAY[]::uuid[]
  );
$$;

Role normalization is a security concern

When roles are spelled differently across environments (facility_manager vs manager, FACILITY_MEMBER vs member), permission checks silently fall through to the deny branch — or worse, to a permissive default. Normalize at the boundary and record the mapping in an ADR so it stays stable:

const ROLE_ALIASES: Record<string, StaffRole> = {
  owner: 'owner',
  facility_owner: 'owner',
  manager: 'manager',
  facility_manager: 'manager',
  member: 'member',
  facility_member: 'member',
};
export function normalizeRole(raw: string | null | undefined): StaffRole | null {
  if (!raw) return null;
  return ROLE_ALIASES[raw.trim().toLowerCase()] ?? null;
}

Note the fail-closed ?? null: an unrecognized role grants nothing. Then run an environment drift audit that compares the distinct role values in staging and production against the alias table — drift discovered by a scheduled job is far cheaper than drift discovered by a support ticket.

4. Invitations: Idempotent Accept, Tenant-Scoped Everything

Staff invitation flows are a classic cross-tenant hole because they mint privileges by design. Three properties are non-negotiable:

1. The invite token is a random UUID, verified server-side — never a guessable email+facility pair.
2. Acceptance is idempotent: double-clicking "Accept" must not create duplicate memberships or escalate a role.
3. The invite is scoped to the selected facility, not to "whatever facility the creator currently has open."

CREATE OR REPLACE FUNCTION accept_staff_invite(p_invite_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_invite staff_invites%ROWTYPE;
BEGIN
  SELECT * INTO v_invite
  FROM staff_invites
  WHERE id = p_invite_id
    AND accepted_at IS NULL
    AND expires_at > now()
    AND lower(email) = lower(auth.jwt() ->> 'email')
  FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'invite_invalid' USING ERRCODE = '42501';
  END IF;
  INSERT INTO facility_staff (facility_id, user_id, role)
  VALUES (v_invite.facility_id, auth.uid(), v_invite.role)
  ON CONFLICT (facility_id, user_id) DO NOTHING;
  UPDATE staff_invites SET accepted_at = now() WHERE id = v_invite.id;
END;
$$;

SECURITY DEFINER with a pinned search_path, a row lock, an expiry check, an identity check against the JWT, and ON CONFLICT DO NOTHING — each line closes a specific attack or race.

5. Webhook Attribution: Never Trust a Shared Identifier

External hardware sends webhooks back to you. If you attribute those events using a shared identifier — a lock id, a door id, a facility id — two concurrent operations on the same lock become indistinguishable, and an event can be recorded against the wrong record. The fix is to embed a per-operation UUID in the credential you issue, and to attribute strictly by that UUID.

export function buildPinTargetName(slipId: string): string {
  return `slip:${slipId}`;
}
export function resolveSlipIdFromWebhook(payload: WebhookPayload): string | null {
  const match = /^slip:([0-9a-f-]{36})$/i.exec(payload.targetName ?? '');
  return match ? match[1] : null;
}
export async function handleDeliveryWebhook(payload: WebhookPayload) {
  const slipId = resolveSlipIdFromWebhook(payload);
  if (!slipId) {
    await auditLog.warn('webhook.unattributed', { targetName: payload.targetName });
    return;
  }
  await recordDelivery({
    slipId,
    entranceId: payload.entranceId,
    occurredAt: parseVendorTimestamp(payload.createTime),
  });
}

Two details worth stealing:

• Fail loudly but safely. An unattributable webhook is logged for audit rather than guessed at. Guessing is how events land on the wrong tenant.
• Persist the correlating keys you will need later. Recording entrance_id alongside the delivery log is what makes camera/recording replay possible at audit time. If you don't store the join key at write time, the audit trail is effectively lost.

6. Timestamps Are a Correctness Boundary

Audit logs that are nine hours off are worse than no audit logs — they create false confidence. Vendor APIs often emit local wall-clock time without an offset. Parsing that string with a naive new Date() silently applies the server's timezone.

import { fromZonedTime } from 'date-fns-tz';
export function parseVendorTimestamp(raw: string, vendorZone = 'Asia/Tokyo'): Date {
  if (/(?:Z|[+-]\d{2}:?\d{2})$/.test(raw)) {
    return new Date(raw);
  }
  return fromZonedTime(raw, vendorZone);
}

The same discipline applies inside the product: reservation times must be rendered and stored in the facility's timezone, not the browser's. A booking rendered in the viewer's locale but written in UTC-shifted form produces off-by-one-day access windows — a lock that opens on the wrong date is a security incident.

export function toFacilityInstant(localInput: string, facilityTimeZone: string): string {
  return fromZonedTime(localInput, facilityTimeZone).toISOString();
}

And watch inclusive range boundaries. A week view that computes start < day instead of start <= day will drop every reservation that begins exactly on the first day of the range — the kind of off-by-one that hides for months because it only affects one column of a grid.

7. One Source of Truth for Capacity Gates

A "shows available, returns 409 on submit" bug is a reliability failure with a security flavor: the availability calculation and the booking gate were two different code paths that drifted. The fix is structural — derive capacity from the authoritative relation, and have both the read path and the write path call the same function.

export function resolveCapacity(roomType: RoomType, linkedRooms: Room[]): number {
  return linkedRooms.reduce((sum, room) => sum + room.capacity, 0);
}
export function isSlotBookable(input: SlotInput): Result<true, BookingRejection> {
  if (!input.plan.availableDays.includes(input.dayOfWeek)) {
    return err({ code: 'DAY_NOT_AVAILABLE' });
  }
  if (input.occupied >= resolveCapacity(input.roomType, input.linkedRooms)) {
    return err({ code: 'CAPACITY_EXCEEDED' });
  }
  return ok(true);
}

The grid renderer and the POST handler both call isSlotBookable. Parity is then a property of the architecture rather than a promise in a code review. A discriminated Result type also keeps rejection reasons typed, so the UI can localize them precisely instead of falling back to a generic "error occurred."

8. Lock In the Behavior With Tests

Every guard above deserves a regression test, and the cheapest way to get them is a test harness that runs in CI from day one.

import { describe, expect, it } from 'vitest';
describe('accept flow guards', () => {
  it('is idempotent on repeated accept', async () => {
    await acceptInvite(inviteId);
    await acceptInvite(inviteId);
    const rows = await listStaff(facilityId);
    expect(rows.filter((r) => r.userId === userId)).toHaveLength(1);
  });
  it('rejects an unknown role fail-closed', () => {
    expect(normalizeRole('SUPER_ADMIN_X')).toBeNull();
  });
  it('keeps already-checked-out calls idempotent', async () => {
    const first = await checkOut(reservationId);
    const second = await checkOut(reservationId);
    expect(first.status).toBe('checked_out');
    expect(second.status).toBe('checked_out');
  });
});

Finally, apply least privilege to CI itself. GitHub Actions tokens default to broad write scopes unless you declare otherwise:

permissions:
  contents: read
  id-token: write

A workflow that only builds and tests needs contents: read and nothing else. This is a one-line change that shrinks the blast radius of a compromised third-party action.

Summary

Trust in a physical-access system is assembled from unglamorous, layered guarantees:

• WITH CHECK on every write policy, plus immutable tenant keys enforced by triggers.
• Tenant ids removed from client payloads and from the type that describes them.
• Claims minted on both login and role-switch, normalized fail-closed, and shared by UI and database.
• Invitations accepted through an idempotent, SECURITY DEFINER RPC with expiry and identity checks.
• Webhooks attributed by per-operation UUIDs, with unattributable events audited rather than guessed.
• Timestamps parsed and stored in an explicit timezone so audit trails are actually true.
• A single shared predicate behind both availability display and the booking gate.
• Tests and least-privilege CI that keep all of the above from regressing.

None of these is individually clever. Together they are what turns "we think it's secure" into "we can show you why."