Around 1 AM I was doing what I thought was the easy part of a side project: applying a database migration. The migration ran clean. Then I tried to promote my own account to owner — the role that's allowed to manage everything else — and nothing happened. No error message. No success. Just a quiet refusal.
The database was doing exactly what I had told it to do. That was the problem.
The guard
The project has roles: owner, admin, member. I didn't want role changes to depend on the application code behaving, because application code gets bugs, and application code gets bypassed. So the rule lives in Postgres itself: a guard function sits on top of role changes and requires that the caller already be an owner.
It's the kind of rule you write and then feel quietly smug about.
One wrinkle: how the database knows who's calling. Inside the app, every request carries an auth session, so the guard checks auth.uid() and sees exactly who is knocking. Run SQL from outside the app, though — a migration script, a CLI, the dashboard's SQL editor — and there's no session at all. auth.uid() doesn't return a user. It returns NULL. As far as the database is concerned, nobody is calling.
And nobody is not an owner.
A small detour, because the night had two locks
The migration itself got there through a tool I'd just built. I was tired of pasting SQL into the dashboard editor every time I wanted to touch the project's database, so I wrote a small command-line runner that posts queries straight to Supabase's Management API — there's an endpoint that accepts a query string and runs it against your project.
First attempt: HTTP 403, Cloudflare error 1010. If you've never met that one, it means the request was rejected based on the signature of the client — the endpoint sits behind protection tuned for real browsers, and my plain HTTP client didn't look like one. The fix was almost embarrassingly low-tech: use curl, and send a browser User-Agent header. Same request, same credentials, different hat — straight through. Sometimes infrastructure doesn't want cleverness. It wants you to dress for the occasion.
The migration landed. And then the promotion query returned nothing, and the real story started.
The chicken, the egg, and me, at 1 AM
Here's the deadlock. The rule says only an owner can promote someone to owner. The owners table was empty — I was trying to create the first one. The promotion needed an owner's authority. The authority needed an owner. Perfectly logical, perfectly circular, forever.
I tried from the SQL editor too. Same wall — the editor also runs without an app session, so the guard sees NULL there as well. My perfectly reasonable rule had locked the only key inside the room, and was politely explaining that only key-holders could open the door.
The uncomfortable part: this wasn't a bug in the guard. The guard was working flawlessly. The bug was that I had never thought about day one. Every permission system has one — a moment when nobody has authority yet, and somebody has to get it. I'd built the lock and never built the way through it.
The fix, and the part that matters more
The fix was a bootstrap exception: allow promoting someone to owner, but only while the owners table is empty. The instant one owner exists, the exception is gone forever and the normal guard takes over. As narrow as I could make it — it doesn't grant anything except filling one empty seat, once.
I applied it in two places on purpose:
- the live database, where I was actually stuck
- the migration file itself, so any fresh environment gets the same rule from birth
Patching only the live database would have worked tonight and resurrected the bug, grinning, the next time I deployed somewhere clean.
Then the part that matters more than the fix: the negative test. With the exception in place, I tried promoting a plain member to admin from outside the app, through the Management API. Blocked. Good. The point of the exercise was never to open a door — it was to open exactly one door, exactly once, and prove all the other doors still hold.
Three things I'm taking from this
- Every permission system has a day-one problem. The bootstrap path — first admin, first key, first token — is security design, not setup chores. Decide deliberately who lights the first match, and keep that path as narrow as mine ended up.
- NULL is a caller too. If your authorization code reads an identity that might not exist, decide what "no identity" means. Mine defaulted to "not an owner," which was correct in steady state and catastrophic at bootstrap. Those are different situations, and the code should know which one it's in.
- Security rules deserve negative tests. Everyone tests that the right person gets through. Almost nobody tests that the wrong person still doesn't, immediately after changing the lock. The member-to-admin attempt is now a permanent line item on my checklist.
It's past 1 AM again as I write this, and the project finally has an owner. The guard is exactly as strict as it ever was — it just knows now that someone had to be first.
When you built your systems, did you design the day-one path, or did you find it the hard way?
