My verification script mutated a real user's row and only rolled back one field

When you write a migration that runs a functional test against a production database, do you count exactly how many columns that function writes?

A security audit flagged one of my Postgres functions. moodbite.update_streak(p_user_id, ...) runs as security definer and takes p_user_id as an argument, with no check that the caller was the same person. Since the anon key ships inside the client binary and is effectively public, anyone could pass another user's uuid and manipulate their streak. Low severity — no money or subscription value at stake — but still a data integrity hole.

The fix was one line:

if p_user_id is distinct from auth.uid() then
  raise exception 'forbidden';
end if;

I shipped it as a single migration file, and decided to verify both "the guard actually blocks it" and "the function still works when the guard passes" inside that same file. Ship and prove it, in one shot.

What would you have done?

To verify, you have to actually call the function. But this function has a side effect — calling it mutates a profiles row. Do you create a throwaway test row in production, or borrow an existing row and put it back?

A throwaway row needs cleanup later, and forgetting that cleanup is its own kind of contamination. I chose to borrow an existing row and restore it: save the values before the call, then update back to those values after.

select id, current_streak, last_logged_date
into v_id, v_before_streak, v_before_date
from moodbite.profiles limit 1;
 
-- test the guard rejects a foreign uuid, test it accepts the owner ...
 
update moodbite.profiles
   set current_streak = v_before_streak,
       last_logged_date = v_before_date
 where id = v_id;

Shipped. The verification log printed pass. The guard actually blocked a foreign uuid, and the owner's own call went through normally. In that moment, that felt like the whole story.

What I missed

Reread the body of update_streak and it actually writes three columns:

update moodbite.profiles
   set current_streak   = v_curr_streak,
       longest_streak   = v_long_streak,
       last_logged_date = v_today
 where id = p_user_id;

The rollback only saved two of them: current_streak and last_logged_date. It never saved, and never restored, longest_streak. The "owner's own call" test bumped that real user's streak to 1, and the function set their best-ever record from 0 to 1 via greatest(coalesce(longest_streak, 0), 1). The restore update never even mentioned that column, so there was no path back to the original value.

The migration shipped, the verification log passed, and one real user's longest_streak quietly sat at 1 from then on. I found it during a later audit — only after laying the function's actual write-list next to the rollback's save-list did the gap show up.

The fix

The fix wasn't the restore code — it was the save code, first. Every column that appears in the function's set clause now gets saved before the call.

select last_logged_date, current_streak, longest_streak
into v_last_date, v_before_streak, v_before_long
from moodbite.profiles limit 1;
 
-- ...
 
update moodbite.profiles
   set current_streak = v_before_streak,
       longest_streak = v_before_long,
       last_logged_date = v_before_date
 where id = v_id;

And I left the incident as a comment inside the migration file itself, so whoever touches this function next (including future me) doesn't repeat it:

-- (2026-08-26: the first pass forgot to restore longest_streak, leaving a
--  real user's profile mutated from 0 to 1 until a later audit caught it
--  and it was corrected by hand. Restoring all three fields here now.)

The mutated row was fixed by hand. But the original verification run never recorded what the value had been before, so the correction relied on a later audit reconstructing it — a value you never saved is not guaranteed to be perfectly recoverable later.

Self-check

If you have code that tests a side-effecting function against a production row and rolls it back, check three things.

1. Have you laid the rollback's save-list next to the function's actual set/update clause and counted them side by side? A list you copied by hand can drift from the real one without anyone noticing.
2. If "save → call → restore" is written as separate steps, does the restore automatically track it when the function later touches a new column? If it doesn't, this bug comes back.
3. Does a passing verification log prove "the guard worked," or "nothing got mutated"? In this case it only proved the first, and the second was false.

A verification block that tests directly against production is powerful, but the rollback is really just counting the same column list twice, by hand. I made the tests pass and production gained users hit the same spot — a green light doesn't mean "no side effects." Before you write a rollback, recount exactly how many things the function actually changes.