# Pattern 08 — Unsafe JWT custom claim extraction

## The failure

AI extracts JWT claims with direct text casts:

```sql
CREATE POLICY "admins_read_all" ON admin_logs
  FOR SELECT USING (
    (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'
  );
```

Or for boolean claims:

```sql
(auth.jwt() -> 'app_metadata' ->> 'is_admin')::boolean = true
```

The first version is mostly fine but returns `NULL` if `app_metadata` or `role` is absent — which RLS treats as deny (correct). The danger is false reliance: developers assume this is safe without understanding why, and make unsafe variations.

The second version has a real failure mode: `->>` returns text, and casting text to boolean in PostgreSQL accepts `'true'`, `'false'`, `'t'`, `'f'`, `'yes'`, `'no'`, `'on'`, `'off'` (case-insensitive) but throws an error on anything else. If your auth provider ever sends an unexpected value (`'TRUE'` is fine; `'1'` is not), the policy throws an error for all rows — effectively locking everyone out.

## The three issues

**Issue 1: Missing COALESCE**
When the claim is absent, `auth.jwt() -> 'app_metadata' ->> 'role'` returns NULL. `NULL = 'admin'` is NULL — which RLS treats as deny. This is safe but implicit. Using `COALESCE(..., false)` makes the intent explicit and is marginally more future-proof.

**Issue 2: Fragile boolean casting**
`->>` returns JSONB values as text. Casting that text to boolean accepts most truthy strings but throws on values like `'1'`, `'yes'` (case matters for some), or any non-standard value. Use JSONB comparison instead.

**Issue 3: Per-row re-evaluation**
`auth.jwt()` is called once per row by default. On a 100k-row table, that's 100k JSON parse operations per query. A STABLE wrapper function may be cached by PostgreSQL across rows.

## The fix

```sql
-- String claim: use COALESCE to make the NULL-handling explicit
CREATE POLICY "admins_read_all"
  ON admin_logs
  FOR SELECT
  TO authenticated
  USING (
    coalesce(
      (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin',
      false
    )
  );

-- Boolean claim: compare as JSONB, not cast text to boolean
CREATE POLICY "verified_users"
  ON sensitive_data
  FOR SELECT
  TO authenticated
  USING (
    coalesce(
      (auth.jwt() -> 'app_metadata' -> 'is_verified') = 'true'::jsonb,
      false
    )
  );
```

## Performance wrapper (optional, for high-volume tables)

```sql
CREATE OR REPLACE FUNCTION auth_role()
RETURNS text LANGUAGE sql STABLE SET search_path = public AS $$
  SELECT coalesce(auth.jwt() -> 'app_metadata' ->> 'role', '')
$$;

CREATE POLICY "admins_read_all" ON admin_logs
  FOR SELECT TO authenticated
  USING (auth_role() = 'admin');
```

PostgreSQL may evaluate a STABLE function once per query when called with the same arguments, rather than once per row.

## Verifying your JWT structure

In Supabase SQL editor, check what your JWT actually contains:

```sql
SELECT auth.jwt();
-- Inspect the output to see the exact structure of app_metadata
-- before writing policies that reference it
```

JWT claims are set by your Supabase Auth hooks or via the admin API — verify the claim exists and has the expected type before writing policies that depend on it.