DEV Community: Guido

My security test was green for six weeks because it could not turn red

Guido — Fri, 28 Aug 2026 15:58:52 +0000

I had a SQL test that logged in as two users and checked that neither could read the other's rows. It ran on every push. It was green for about six weeks.

Then I broke an assertion on purpose, just to watch it fail. It didn't fail. It threw a type error on the line that builds the failure message.

Every check in that file had been passing into a branch that had never once executed.

The line

The test collects failures into an array and raises at the end:

DECLARE fails text[] := '{}';
...
IF v_int <> 1 THEN fails := fails || 'own profile not visible'; END IF;
IF v_int <> 0 THEN fails := fails || 'other profiles visible';  END IF;
...
IF array_length(fails, 1) IS NULL THEN
  RAISE EXCEPTION 'PASS';
ELSE
  RAISE EXCEPTION 'FAIL — %', array_to_string(fails, ' | ');
END IF;

fails || 'some message' reads like appending a string to an array, and in Postgres that is a real thing — anyarray || anyelement exists and does exactly that. So the line looks fine, and it type-checks, and it never runs while everything passes.

The moment a check actually fails, it raises:

22P02 malformed array literal: "own profile not visible"

Why

I assumed the message text was the problem — spaces, or something needing quoting. It isn't. Four cases against a real database:

fails := fails || 'own profile not visible';        -- 22P02 malformed array literal
fails := fails || 'own profile not visible'::text;  -- works, {"own profile not visible"}
fails := array_append(fails, 'own profile not visible');  -- works
fails := fails || 'nospaces';                       -- 22P02 malformed array literal

The one with no spaces and no commas fails too, which rules out the content. What actually happens is operator resolution. A bare '...' literal has no type yet — it is unknown. Postgres has both anyarray || anyelement and anyarray || anyarray, and with an unknown on the right it resolves to the array-to-array form, then tries to read your sentence as an array literal.

Add ::text and it picks the other operator. Or skip the ambiguity entirely and use array_append, which is what I did.

None of this is a Postgres bug. It is a perfectly reasonable resolution rule that happens to turn a failure path into a type error, in the one place where nobody looks, because that code only runs on the day something is already wrong.

The part that actually bothers me

The bug is small. What it did was not.

For six weeks I had a green check on a security property, and I would have told you with a straight face that cross-user isolation was covered. It wasn't covered. It wasn't uncovered either — it was unrunnable, and from outside, unrunnable looks exactly like passing. Same colour, same duration, same silence.

The failure mode of a normal bug is that something breaks. The failure mode of this one was confidence.

Two more in the same file, once I started looking

Pulling that thread found two others that had also never fired.

The vacuous pass. One assertion was "user B sees zero of user A's rows." That is trivially true when user A has no rows. Run the suite against a fresh database with one signup and it passes, brilliantly, having tested nothing. It now counts the fixtures first and reports INCONCLUSIVE rather than OK:

SELECT count(*) INTO v_total FROM public.profiles;
IF v_total < 2 THEN
  fails := array_append(fails, format(
    'INCONCLUSIVE: only %s profile(s) exist. Sign up a second user, '
    'otherwise the isolation check passes for lack of data', v_total));
END IF;

That distinction matters more than it sounds. OK and I could not test this are different answers, and a suite that returns the first when it means the second is lying to you politely.

Deny-only. Someone reviewing the file pointed out that every single assertion was of the form "B cannot see A's data." A policy that denies everything to everyone passes all of them. There was no assertion that the thing was still supposed to work, so the suite couldn't tell a correct policy from a broken one that happened to fail closed. That took one line to fix and I have no idea how long I would have gone without noticing.

What I do now

Before trusting a green suite, I break one assertion on purpose and watch it go red. Then I put it back.

It takes ten seconds. It is the only check I have that tells me the difference between a test that passes and a test that cannot fail. Every other signal those two produce is identical.

I would extend it to anything that reports on its own health — a monitor, a linter, a CI gate, a security scanner. If you have never seen it fail, you do not know that it can.

Four Expo + Supabase bugs where the error message sends you the wrong way

Guido — Mon, 24 Aug 2026 12:29:03 +0000

Every one of these cost me more than a day. They have the same shape: the app tells you what's wrong, you believe it, and the message points somewhere the bug isn't.

That's what makes them expensive. A bug that says nothing costs you an afternoon of bisecting. A bug that confidently misdirects you costs a day, because you spend it fixing something that was never broken.

1. A query that never resolves and never rejects

Symptom. The app has been backgrounded for a while. You come back, it fetches something, and the spinner spins forever. No error, no timeout, nothing in the logs. It looks exactly like a dead network, so that's where you go looking.

What's actually happening. The underlying fetch got suspended by the OS while the phone was dozing and never woke up. The promise is not pending because the request is slow. It is pending because nothing will ever settle it.

This is why the obvious fixes don't help:

Adding a retry does nothing, because the first attempt never finished, so your retry logic never runs.
Adding a .catch() does nothing, because there is no rejection to catch.
Checking connectivity does nothing, because connectivity is fine.

The fix is to stop trusting the promise to settle:

export function withTimeout<T>(promise: Promise<T>, ms = 8000): Promise<T> {
  return Promise.race([
    promise,
    new Promise<never>((_, reject) =>
      setTimeout(() => reject(new Error(`timeout after ${ms}ms`)), ms)
    ),
  ]);
}

Then wrap every call that talks to Supabase:

const { data, error } = await withTimeout(
  supabase.from('notes').select('id, content')
);

A dead promise becomes a real rejection you can show, log, or retry. The important part isn't the helper, it's applying it everywhere rather than at the one call site where you first noticed the hang.

2. Intermittent freezes right after sign in

Symptom. The app locks up shortly after signing in. Not every time — maybe one in five. Never while you're watching, and never on the machine you're debugging on.

What's actually happening. You're awaiting something inside the onAuthStateChange callback:

supabase.auth.onAuthStateChange(async (event, session) => {
  setSession(session);
  const profile = await fetchProfile(session.user.id);  // this is the problem
  setProfile(profile);
});

Supabase documents this: the callback runs while the auth library holds an internal lock, and awaiting a call that itself needs that lock deadlocks the whole thing. Because it depends on timing, it reproduces intermittently, which means you'll blame your own async code long before you suspect the callback contract.

The fix. Keep the callback synchronous. Set state, nothing else:

supabase.auth.onAuthStateChange((event, session) => {
  setSession(session);
});

Then do the side effects in an effect keyed on the user id, where awaiting is safe:

useEffect(() => {
  const userId = session?.user?.id;
  if (!userId) return;
  let cancelled = false;
  void (async () => {
    const profile = await withTimeout(fetchProfile(userId));
    if (!cancelled) setProfile(profile);
  })();
  return () => { cancelled = true; };
}, [session?.user?.id]);

Keying on the id rather than the session object matters too: the session gets a new identity on every token refresh, and you don't want to refetch the profile every hour for no reason.

3. RLS is correct and still hands out the column you hid

This is the one I see most, and the one with the most expensive misdirection at the end.

Symptom. You lock a table down so each user only reads their own row. You test it. It works. And the response still contains every column of that row, including the ones that were never meant to reach the client.

What's actually happening. Row-Level Security filters rows. It does not filter columns. A policy can be perfectly correct and still return the whole row, because deciding which row you may read and deciding which fields of it you may see are two different mechanisms.

Columns are a GRANT question:

revoke select on public.profiles from anon, authenticated;
grant  select (id, display_name, created_at) on public.profiles to authenticated;

Now select('*') fails on that table, which is correct and intended. Select explicit columns.

And now the part that actually burns the afternoon. The same rule applies to writes, and the error will lie to you about it:

// fine — 204, the write is legal
await supabase.from('profiles').update({ display_name });

// 42501 permission denied
await supabase.from('profiles').update({ display_name }).select();

// fine
await supabase.from('profiles').update({ display_name }).select('id, display_name');

The failing one is not failing on the write. PostgREST reads the row back to return a representation, and it reads it with select=* — that read is what gets denied. The error says:

permission denied for table profiles
hint: GRANT SELECT ON public.profiles TO authenticated

Follow that hint and you hand back every column you just spent the afternoon locking down, to fix a bare .select(). The write was never the problem.

While you're there, restrict which columns can be written too. A policy like using (user_id = auth.uid()) lets a user change any column of a row they own — including the ones that decide what they are:

revoke update on public.profiles from authenticated;
grant  update (display_name) on public.profiles to authenticated;

Without that, update profiles set role = 'admin' where id = me passes every row-level check you have, because the row genuinely is theirs.

4. A web export that boots asking for the env var you already set

This one is fresh — I hit it two days ago while recording a demo.

Symptom. npx expo export -p web runs clean. It even prints that it loaded your .env. Exit code 0. You serve the output and the app dies immediately:

[supabase] Missing EXPO_PUBLIC_SUPABASE_URL or EXPO_PUBLIC_SUPABASE_ANON_KEY.
Copy .env.example to .env and fill in the values.

The variable is set. It has been set the whole time. It works in development.

What's actually happening. Metro had a cached transform of that module from a build where the variables weren't available. Babel inlines process.env.EXPO_PUBLIC_* at transform time, so it had already replaced the lookup with undefined, folded the guard to always-true, and dead-code-eliminated everything after it. The compiled module in the bundle was, literally, an unconditional throw:

var e,n=r(d[2]);(e=n)&&e.__esModule,r(d[3]);throw new Error("[supabase] Missing ...

No createClient call anywhere. It didn't fail to find the variable at runtime — the code that reads it doesn't exist in the bundle.

The fix:

npx expo export -p web --clear

The lesson that outlives the bug. A green expo export proves the bundle built. It does not prove the app boots. I had been treating that exit code as a verification step for weeks. It isn't one — open the export before you trust it.

The thread running through all four

In each case the system reported something true and useless. The fetch really did hang. The freeze really was in your async code. Permission really was denied for that table. The variable really was missing from the bundle.

The misdirection is in the gap between what failed and what caused it, and that gap is where the day goes.

The habit that helps isn't knowing these four. It's distrusting green results. My own RLS test suite sat green for weeks and I only found out it could never report a failure when I deliberately broke an assertion to watch it fail — and it didn't. It threw a type error on the line that builds the failure message. Every check had been passing into a branch that had never once executed.

So: break your own checks on purpose, once, and confirm they scream. A test that can't fail looks exactly like a test that passes.

All four of these are handled in a starter I open sourced (MIT) while extracting them from apps I ship: github.com/Guidondor/expo-supabase-starter. Disclosure, since it's mine: there's a paid edition too, but the free one is the full auth, offline queue and RLS base.

If you've hit a fifth one of these, I'd genuinely like to hear it.