Introduction
A smart lock platform is a system where a software bug becomes a physical consequence. A leaked key, an over-broad database policy, or a test bypass left in production does not merely corrupt a record — it opens a door.
This article walks through a set of hardening patterns we applied across a recent development cycle of the UnlockOS SDK: tightening row-level security, making audit logs tamper-evident, encrypting third-party credentials, scoping tokens to a single capability, and enforcing in CI that security-relevant tests actually run. The implementation details are generalizable — every pattern below applies to any multi-tenant system built on Postgres and edge functions.
1. Push authorization into the database, not the client
The weakest authorization model is one that lives only in application code. If a client holds a key that can speak directly to the database, every select you forgot to guard is a data leak.
The baseline rule we converged on: anonymous and authenticated client roles get read access only to what is genuinely public; everything else is written by a privileged server role.
-- Anonymous clients must never write shared rows directly.
revoke insert, update, delete on public.guest_users from anon;
revoke all on public.integration_credentials from anon, authenticated;
-- Public, read-only surface stays explicitly enumerated.
grant select on public.facility_public_view to anon;Shared "guest mode" rows are then created by an edge function running with the service role, after it has validated the request itself. The client never gets a write path it could abuse; the server owns the invariant.
The same principle applied to calendar feed configuration: client writes to ical_feeds were closed entirely, because a feed URL is effectively a long-lived read token for a property's occupancy schedule.
2. RLS policies must model real ownership, not the simple case
A recurring bug class in multi-tenant systems: a policy that works for the single-tenant user and silently breaks — or silently over-permits — for the multi-tenant one.
We hit this with operators who own more than one facility. A policy that resolved "the owner" by picking a single membership row refused edits on the owner's second facility. The fix is to make the policy a set membership test, never a scalar lookup:
create policy checkin_config_owner_write
on public.checkin_configs
for all
to authenticated
using (
exists (
select 1
from public.facility_members fm
where fm.facility_id = checkin_configs.facility_id
and fm.user_id = auth.uid()
and fm.role = 'owner'
)
)
with check (
exists (
select 1
from public.facility_members fm
where fm.facility_id = checkin_configs.facility_id
and fm.user_id = auth.uid()
and fm.role = 'owner'
)
);Two details matter here:
- Always write
with check, not justusing.usingfilters what rows you can see and modify;with checkvalidates the resulting row. Without it, a user can update a row they own into a row they do not own — a tenant-hopping write. - Never collapse a one-to-many relationship into a scalar.
exists (...)scales to the multi-facility owner;= (select facility_id ... limit 1)does not.
Door assignment on a check-in configuration — the field that literally decides which locks a guest can open — was separately narrowed to owner-only, below the general edit permission. Privilege granularity should follow blast radius, not table boundaries.
3. Make the audit log something nobody can quietly edit
An audit log that the application can rewrite is a log you cannot testify with. Three properties are worth engineering for:
- Clients cannot read it directly. Reads go through an edge function that applies its own authorization and can redact fields. Direct
selectonaudit_logsis revoked. - Only triggers can write it. No application role has
inserton the audit table; rows appear as a side effect of the change they describe. - Deleting the subject does not delete the history.
revoke select, insert, update, delete on public.feature_flag_audit_log
from anon, authenticated;
create policy audit_insert_trigger_only
on public.feature_flag_audit_log
for insert
to public
with check (pg_trigger_depth() > 0);
create or replace function public.log_feature_flag_change()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
insert into feature_flag_audit_log (flag_key, actor_id, before_value, after_value, action)
values (
coalesce(new.key, old.key),
auth.uid(),
to_jsonb(old),
to_jsonb(new),
tg_op
);
return coalesce(new, old);
end;
$$;The pg_trigger_depth() > 0 check is a cheap, strong guarantee: an insert reaching the table outside of trigger execution is rejected. The security definer function with a pinned search_path prevents search-path hijacking — a classic privilege-escalation vector in Postgres.
For history retention, the audit row must not carry a cascading foreign key to the entity it describes. Store the key as a plain value, so deleting a feature flag leaves its change history intact.
Audit the rejections, too
Most systems log what succeeded. The interesting security signal is what failed. We extended audit coverage to webhook requests refused for an invalid signature, a stale timestamp, or bad credentials:
type WebhookRejection = 'invalid_signature' | 'stale_timestamp' | 'bad_credentials';
async function verifyWebhook(req: Request): Promise<Result<Payload, WebhookRejection>> {
const signature = req.headers.get('x-signature');
const timestamp = Number(req.headers.get('x-timestamp') ?? 0);
const body = await req.text();
if (Math.abs(Date.now() / 1000 - timestamp) > 300) {
await recordAudit({ action: 'webhook.rejected', reason: 'stale_timestamp', source: req.url });
return { ok: false, error: 'stale_timestamp' };
}
if (!signature || !timingSafeEqual(signature, sign(body, timestamp))) {
await recordAudit({ action: 'webhook.rejected', reason: 'invalid_signature', source: req.url });
return { ok: false, error: 'invalid_signature' };
}
return { ok: true, value: JSON.parse(body) as Payload };
}A burst of invalid_signature rejections is an attack indicator. If you only log successes, it is invisible.
4. Secrets: encrypt at rest, refresh on the server, pin the supply chain
Third-party integration credentials (PMS systems, payment providers, lock vendor APIs) are high-value targets. Three changes mattered:
- Encrypt them at rest and never expose them to clients. The client sees a connection status, never a key. When a key must be re-entered, the UI treats it as write-only.
- Refresh tokens server-side only. A refresh flow executed in the browser means the refresh token is in the browser. Moving it behind an edge function — shipped behind a feature flag so it could be rolled back instantly — removed an entire class of exposure.
- Pin third-party module versions. Edge functions importing from a CDN with a floating version specifier are a supply-chain hazard: a compromised or merely broken upstream release lands in production without a deploy. Pin one exact version everywhere and upgrade deliberately.
// Bad: floating version, resolved at cold start.
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';
// Good: one exact version, reviewed on upgrade.
import { createClient } from 'https://esm.sh/@supabase/[email protected]';Credential validation should also be honest. Rather than reporting "connected" from a locally parsed key format, call the provider, judge the connection by the mode payments actually use, reject keys the provider rejects, and log the provider's own error message. A green checkmark that lies is worse than a red one.
5. Scope tokens to one capability and one moment
When a guest needs to perform exactly one action from a link, issue a token that can do exactly that action.
Read-only status token. For an auto-checkout rescue flow, the guest's link must be able to read stay status and nothing else — and only once the scheduled end has passed:
interface StatusToken {
readonly kind: 'stay_status_read';
readonly stayId: string;
readonly notBefore: number;
readonly exp: number;
}
export function assertStatusReadable(token: StatusToken, now = Date.now()): void {
if (token.kind !== 'stay_status_read') throw new ForbiddenError('wrong_token_kind');
if (now < token.notBefore * 1000) throw new ForbiddenError('before_scheduled_end');
if (now >= token.exp * 1000) throw new ForbiddenError('token_expired');
}The kind discriminant is not decoration. It prevents a token minted for one purpose from being replayed against another endpoint — the confused-deputy problem that makes "one JWT for everything" designs fragile.
One-time binding token. Claiming an unowned reservation via a shared guest link must be single-use, atomically consumed:
update reservation_claim_tokens
set consumed_at = now(), consumed_by = $2
where token_hash = digest($1, 'sha256')
and consumed_at is null
and expires_at > now()
returning reservation_id;If the statement returns zero rows, the token was already used, expired, or forged — all three indistinguishable to the caller, by design. Note that only the hash is stored: a database dump must not yield usable links.
OTP failure semantics. Telling an expired code apart from a wrong code is a usability improvement, but it must not weaken the system. The rule we settled on: distinguish failure categories in the UI, never leak whether a specific code existed, and never force a guest to re-enter a code already consumed — a consumed code returns a terminal state, not a retry prompt.
6. Session scoping and cookie isolation
Two small changes with outsized impact:
- Local-scope sign-out for admin sessions. Signing out of the admin console should end that session, not revoke every session of that user across every device and app. Global sign-out as a default turns a routine logout into a denial of service.
- Derive the auth cookie namespace from the project reference. When multiple environments share a domain, a static cookie name lets a staging session collide with production state. Deriving the namespace from the project identifier makes cross-environment session bleed structurally impossible.
export function cookieNamespace(anonKey: string): string {
const ref = JSON.parse(atob(anonKey.split('.')[1])).ref as string;
return `sb-${ref}-auth-token`;
}
await supabase.auth.signOut({ scope: 'local' });7. Never ship a test bypass — and be ready to revert
One change in this cycle removed an auth-bypass-otp escape hatch used by automated tests. A bypass that exists in production code is an authentication backdoor, no matter how it is gated.
That change was then reverted — because removing it broke an E2E suite that depended on it. This is worth stating plainly rather than hiding: the correct sequence is replace the dependency first, remove the bypass second. Tests should obtain real credentials through a test-only issuance path that runs under a separate key and cannot be reached in production, rather than relying on a production code path that skips verification.
The healthy engineering signal here is not that the removal succeeded on the first try — it is that the rollback was fast, deliberate, and recorded.
A related improvement: an assertion that checked a gate by grepping index.ts source was rewritten to check behaviour.
// Brittle: passes if the source string exists, even if the gate is bypassed.
assert.match(readFileSync('index.ts', 'utf8'), /manualCheckoutEnabled/);
// Robust: fails if the gate stops being enforced.
const res = await handler(requestWithoutManualCheckout());
assert.equal(res.status, 403);
assert.equal((await res.json()).code, 'manual_checkout_disabled');Source-text assertions are security theatre. Only behavioural assertions survive a refactor.
8. Make CI fail when a security test is not actually running
The most dangerous test is one that exists in the repository but is never executed. A test file not referenced by any workflow gives a false sense of coverage.
We added a CI gate that enumerates every integration and SQL test file and fails if one is not named in a workflow:
set -euo pipefail
missing=0
while IFS= read -r file; do
if ! grep -rq "$(basename "$file")" .github/workflows/; then
echo "::error file=$file::test file is not referenced by any workflow"
missing=1
fi
done < <(find tests/integration supabase/tests -name '*.test.ts' -o -name '*.test.sql')
exit "$missing"This is a one-screen script that permanently closes a silent-coverage-loss failure mode. Pair it with pinned runner images and a pinned Node version so that "it passed in CI" means something reproducible.
9. Delete data you no longer need
Retention is a security control, not only a compliance checkbox. Data you have deleted cannot be exfiltrated. A scheduled sweep that removes guest personal data past a defined horizon — three years in our case — bounds the value of any future breach.
create or replace function public.sweep_expired_guest_data()
returns integer
language plpgsql
security definer
set search_path = public
as $$
declare
removed integer;
begin
with purged as (
update guest_profiles
set email = null, phone = null, full_name = null, purged_at = now()
where purged_at is null
and last_activity_at < now() - interval '3 years'
returning 1
)
select count(*) into removed from purged;
insert into audit_logs (action, detail) values ('retention.sweep', jsonb_build_object('rows', removed));
return removed;
end;
$$;Note that the sweep writes its own audit entry. A retention job that runs silently is indistinguishable from one that has quietly stopped.
Summary
The patterns worth carrying into any security-critical system:
| Area | Rule |
|---|---|
| Authorization | Enforce in the database with RLS; always pair using with with check |
| Multi-tenancy | Model ownership as set membership, never a scalar lookup |
| Audit logs | Trigger-only writes, no client reads, history survives deletion |
| Observability | Log rejections, not just successes |
| Secrets | Encrypt at rest, refresh server-side, pin exact dependency versions |
| Tokens | One capability, one use, time-bounded, stored as a hash |
| Sessions | Local-scope sign-out; namespace cookies per environment |
| Tests | Assert behaviour, never source text; fail CI on unreferenced tests |
| Data | Delete what you no longer need, and audit the deletion |
None of these is individually clever. Their value is cumulative: each one removes a path an attacker could take, and each one is cheap enough to keep in place for years. In a system that controls physical doors, that durability matters more than sophistication.