Why constrain writes at the database at all?

Because the database is the last line that firmware bugs and stolen tokens cannot argue with. Application validation helps, but a crafted request that bypasses your API — or a legitimate token wielded oddly — meets only the policy. WITH CHECK turns forgery into a rejection at the commit line.

Do devices really need per-device identities?

Shared fleet credentials undo the model: any compromised unit could then write as any other, and revocation would mean re-flashing everything. Per-device principals cost little at provisioning time and buy surgical revocation — flip one registry row and one device falls offline while its neighbours keep reporting.

What about bulk historical uploads?

Route them server-side under a dedicated role whose policy checks ownership against the registry just the same. Bulk is a volume argument, not an exemption: the check per row is cheap, and the alternative — a bypass path around RLS — reintroduces exactly the spoofing door the pattern exists to shut.

Guides

Telemetry writes: RLS on IoT ingest tables

IoT backends accept writes from hardware you do not control: firmware with bugs, credentials copied out of flashing jigs, devices resold without wiping. Telemetry tables therefore carry an unusual burden — the write path needs row-level constraints as much as the read path, because a spoofed device id should fail loudly rather than append into another fleet’s history.

On Supabase, ingest commonly posts through the REST surface with per-device tokens, which puts RLS squarely in the loop for every reading. Read-side policies alone do not cover this: a SELECT policy constrains retrieval, while INSERT obeys WITH CHECK — and a policy set written only with reads in mind leaves the append path either blocked or loosely admitted.

RowShield automates detection of exactly this gap: the MISSING_WITH_CHECK rule flags write paths that lack the check half, and this page shows the pattern that satisfies it — a device registry, per-device principals, and append-only telemetry guarded by a check bound to the caller’s proven identity.

Rules that check this

highWrite policy without WITH CHECKMISSING_WITH_CHECK

Register devices; scope tokens

Ground the model in a devices table: each physical unit mapped to an id, a fleet or customer owner, and a provisioning status. Devices authenticate as themselves — a per-device principal whose claims name the device id — so policy predicates compare the writing device against the row’s device_id directly, with no guessing from payloads.

Provisioning discipline completes the design. Flashing writes the device token; revocation flips the registry row; decommissioning disables the principal. Because policies consult the registry live, revocation takes effect on the next connection without touching policy text — the property that makes fleet operations survivable at scale.

The append path, checked

Telemetry is append-only: devices insert readings, nothing updates, nothing deletes. Express that literally — an INSERT policy whose WITH CHECK binds the row’s device_id to the caller’s claim, and no UPDATE or DELETE policies at all. Absence is the feature: with RLS forced, unmentioned actions deny by default.

ALTER TABLE public.device_readings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.device_readings FORCE ROW LEVEL SECURITY;

-- device_id_claim(): small helper reading the verified device claim
CREATE POLICY "device_inserts_own_readings"
  ON public.device_readings
  FOR INSERT
  TO authenticated
  WITH CHECK (device_id = device_id_claim());

CREATE POLICY "fleet_owner_reads_own_telemetry"
  ON public.device_readings
  FOR SELECT
  TO authenticated
  USING (device_id IN (
    SELECT d.id FROM public.devices d
    WHERE d.owner_id = (SELECT auth.uid())
  ));

Prove the write path

Two details repay attention. Wrap the claim lookup so its value evaluates once per statement, because ingest volume punishes per-row work; and index device_id with timestamp on the read side, since fleet dashboards filter exactly that way. The insert check itself stays cheap — one comparison per incoming row.

Rehearse attacks in transactions, the same trick used for tenant testing: assume a device principal with another fleet’s id planted in its claims, then attempt an insert. The expected verdict is a policy rejection, not a silent row. Flip the claims to the correct device and confirm success, completing the pair of proofs.

Keep the rehearsal scripted beside the migrations. Firmware updates, gateway changes and region rollouts regenerate tokens regularly, and each regeneration is a fresh chance for claim drift — a device presenting an id its token no longer proves. The script turns drift from a mystery outage into a five-minute check.

Continuous cover for a churning fleet

Fleets outgrow their schemas quietly: a new sensor type ships a companion table, a batch importer gains a bulk-insert path, a debug endpoint accepts readings from anywhere. Each is a write-path widening no read-side glance would catch, which is why MISSING_WITH_CHECK belongs in permanent rotation.

RowShield runs it on schedule: scans diff each table’s policy surface against the last snapshot and alert on transitions, catching write paths that lost their check half. Reads touch catalogue metadata only — never telemetry — probes issue GET requests only and refuse private addresses, and RowShield is an independent product unaffiliated with Supabase.

Frequently asked

Why constrain writes at the database at all?: Because the database is the last line that firmware bugs and stolen tokens cannot argue with. Application validation helps, but a crafted request that bypasses your API — or a legitimate token wielded oddly — meets only the policy. WITH CHECK turns forgery into a rejection at the commit line.
Do devices really need per-device identities?: Shared fleet credentials undo the model: any compromised unit could then write as any other, and revocation would mean re-flashing everything. Per-device principals cost little at provisioning time and buy surgical revocation — flip one registry row and one device falls offline while its neighbours keep reporting.
What about bulk historical uploads?: Route them server-side under a dedicated role whose policy checks ownership against the registry just the same. Bulk is a volume argument, not an exemption: the check per row is cheap, and the alternative — a bypass path around RLS — reintroduces exactly the spoofing door the pattern exists to shut.

Check your project in about ten seconds

Paste a URL. No signup, no writes, nothing stored.

Run the free audit