How to handle headless IoT clients safely without building a massive intermediary auth API.

When an application user reads from device_readings, they should only see data from devices they actually own. Since device_readings doesn't have a user_id column directly, we look up ownership by joining through the devices table.

The ownership chain looks like this:

auth.users → devices.user_id → device_readings.device_id

CREATE POLICY "user_see_own_device_rows"
  ON device_readings
  FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM devices
      WHERE devices.device_id = device_readings.device_id
        AND devices.user_id   = auth.uid()
    )
  );

When a device posts a new reading it uses a HTTP POST. PostgREST translates that into a native SQL INSERT on device_readings. Before the row is written, the RLS policy calls verify_device_secret(device_id, header_secret).

• The header_secret comes from the x-device-secret HTTP header. PostgREST stashes all request headers into a session variable before running the query, so the policy can read it back with:

  current_setting('request.headers')::json->>'x-device-secret'
  

• If verify_device_secret returns false — wrong secret, missing header, unknown device — the INSERT is rejected and PostgREST returns HTTP 403. No row is written. The device gets no information about why it failed.

Here is the function to validate the device secret matches the device id:

-- The function checks if the secret matches for a given device_id
 CREATE OR REPLACE FUNCTION verify_device_secret(p_device_id text, p_secret text)
 RETURNS boolean
 LANGUAGE sql
 SECURITY DEFINER
 STABLE
 AS $$
   SELECT EXISTS (
     SELECT 1 FROM devices
     WHERE device_id     = p_device_id
       AND device_secret = p_secret
   );
 $$;

The SECURITY DEFINER part is important. Using it, the function runs as its owner (the superuser), not as the anon role calling it. Without it, the anon role would need SELECT on devices to do the lookup, which would expose the secrets table. With it, the anon role can call the function but never sees the devices table directly.

We also need to explicitly grant the anon role permission to call the function:

GRANT EXECUTE ON FUNCTION verify_device_secret TO anon;

Without this, the RLS policy will fail with a permission error even if the secret is correct.

So the full chain is: HTTP header → RLS policy → verify_device_secret() → devices table.

Now, we bind this function to our INSERT policy:

CREATE POLICY "device_insert_own_readings"
  ON device_readings
  FOR INSERT
  TO anon
  WITH CHECK (
    verify_device_secret(
      device_id,
      current_setting('request.headers', true)::json->>'x-device-secret'
    )
  );

Note: INSERT policies use WITH CHECK instead of USING. USING filters rows on read; WITH CHECK validates rows on write.

This is the 'curl' equivalent of the code that my IOT device would use. I'm using atomvm on my client side, but this effectively reproduces the same thing.

curl -X POST https://your-project.supabase.co/rest/v1/device_readings \
  -H "apikey: your-publishable-key" \
  -H "Authorization: Bearer your-publishable-key" \
  -H "Content-Type: application/json" \
  -H "x-device-secret: changeme-super-secret-32-bytes!!" \
  -d '{"device_id": "dev-001", "value": "23.5"}'

On success: HTTP 201, no body. On wrong secret: HTTP 403.

The two keys are doing different jobs:

• apikey / Authorization — identifies the Supabase project. This is the publishable key, found under your Supabase project Settings → API. It is safe to ship on the device.
• x-device-secret — authenticates the specific device, checked by the RLS policy. This is private and unique per device.

In production, device registration (inserting a row into devices) would be handled by an admin interface or provisioning script. The device itself only needs its device_id and device_secret burned into its firmware at manufacture or setup time.

Devices should only ever record history, never rewrite it. The cleanest way to enforce data immutability in Postgres RLS is elegantly simple: don't write a policy for it.