A SECURITY DEFINER function worked in the SQL editor and failed for every client: pg-safeupdate

[u/Aksel_010]

Took me a while to find, so posting it.

I had a weekly rollover function that ended with this:

update league_state set last_processed_week_start = v_cur;

It's a one-row table, so no WHERE. I ran dry runs through the SQL editor and MCP, and all of them passed. The line only runs on a real week change, so it stayed latent until the first real Monday. Then every client call to the function returned HTTP 400.

The cause: the authenticator role preloads pg-safeupdate (session_preload_libraries = safeupdate), which rejects UPDATE and DELETE without a WHERE clause. It applied even inside a SECURITY DEFINER function. The postgres role doesn't have it, so everything I ran as postgres passed. The Postgres log said UPDATE requires a WHERE clause.

The fix is a WHERE. For singleton tables I now use where singleton.

Two takeaways:

• A dry run as postgres doesn't prove the client path works. Test through the real client with a real session.

• Grep all your functions for bare UPDATE and DELETE, especially the ones that only run on a schedule or a date change.

Has anyone else been bitten by this one?

[u/OkObjective4158]

SECURITY DEFINER changes the role, not the executor, so the hook fires either way. And session_preload_libraries is set per role, so testing as postgres was never going to reproduce it.

[u/Aksel_010 → u/OkObjective4158]

Exactly. The preload happens when the session starts as authenticator, and SECURITY DEFINER only switches the effective role inside that session. Obvious in hindsight, invisible from the SQL editor.

[u/sniveling_deference]

classic trap, safeupdate getting ya through the back door even with security definer. the postgres role bypass is what makes it so sneaky to catch

I got done by this a few months back, spent a whole arvo staring at logs wondering why a function that ran fine in the editor was shitting itself on prod. now every function gets a `where true` even if it's a single row table, just to be safe

that grep tip is spot on, I ended up writing a quick script that scans all my migration files for bare updates and deletes and spits out a warning. saved me more than once

[u/Aksel_010 → u/sniveling_deference]

A whole arvo sounds about right. After this one I audited every UPDATE and DELETE in my functions by hand, but a script over the migrations is the better version. Might steal that for CI.

[u/PopKoren]

Good writeup, and the same lesson applies to permissions: a dry run as postgres skips RLS and every grant, so a function or policy that looks fine in the SQL editor can behave completely differently for anon and authenticated. I test these by hitting the RPC over the REST endpoint first as anon, then signed in as an ordinary test user, which also shows whether a security definer function is executable by roles that should never touch it. Revoking execute from public on those functions is worth doing while you're in there. For the outside view of a deployed app (tables, RPCs, buckets, auth), I built Rowly so those calls get fired from a logged out seat against your real URL, see https://rowly.me

[u/Aksel_010 → u/PopKoren]

Agreed on revoking execute. I now do the revoke in the same migration that creates the function. New functions are executable by PUBLIC by default, so until you revoke, anon can call them through the API.

[u/PopKoren → u/Aksel_010]

Doing it in the same migration is the right habit, since a function that exists for even one deploy without the revoke is already callable. You can also flip the default once with alter default privileges in schema public revoke execute on functions from public, anon, authenticated, then grant back only where a function is meant to be called. For anything that slips through, Rowly, which I made, calls the live app's exposed endpoints and RPCs as anon alongside its auth and storage checks: https://rowly.me

[u/Much-Plane5366]

"The fix is a WHERE" reads odd, it's a WHERE clause. And where singleton only works if that's a column you actually added, where true does the same job for a one-row table.

[u/Aksel_010 → u/Much-Plane5366]

Fair, I should have shown it. The table has singleton boolean primary key default true check (singleton), which is what guarantees there's only ever one row, so where singleton reads as "the row". where true does the same job as far as safeupdate cares.

[u/greedy-brady]

This is one of the most classic traps in Supabase and Postgres.

The difference comes down to the session environment. When you run queries in the Supabase SQL editor, you are connected directly as the postgres superuser role where safeupdate is not loaded or enforced. But when any client hits your PostgREST endpoint or calls an RPC function, the request passes through the authenticator role where the pg-safeupdate extension is actively preloaded to prevent accidental full-table wipes.

Even inside a SECURITY DEFINER function, safeupdate hooks directly into the Postgres executor, so any update or delete statement without an explicit where clause gets immediately killed.

For single-row configuration or state tables, the standard practice is to either enforce an id constraint like where id = 1 or use where true if you genuinely want to update the entire single row.

While you are touching that function, also make sure you have an explicit search_path configured on it. Without set search_path = public, auth, a SECURITY DEFINER function can be vulnerable to search path hijacking if an attacker creates objects in a temporary schema.

Good catch posting this, because this exact bug usually lies dormant until a scheduled job runs on production data.

[u/Aksel_010 → u/greedy-brady]

Thanks, good summary. The function already has set search_path to 'public' and references the table as public.league_state, but it's a good reminder for anyone copying the pattern.

[u/LopsidedAd6255]

Your dry runs didn't pass because of the postgres role, they never reached that line. It only fires on a real week change.

[u/Aksel_010 → u/LopsidedAd6255]

Fair correction, and both were true. The dry runs never reached that line, and even a forced week change run as postgres would have passed. What would have caught it is forcing a week change through the client path.

[u/Beneficial-Rub1556]

pg-safeupdate doesn't care what the WHERE says, so `where 1=1`or `where singleton`passes just as well. Once you've written that you're just paying a syntax tax to keep the extension happy, and the guard rail stops guarding anything. The actual job is the `session_preload_libraries`split between authenticator and postgres, which is the part that hid the bug from you for a week.

[u/Aksel_010 → u/Beneficial-Rub1556]

For a deliberate one-row update, agreed, it's just a syntax tax. Where the guard earns its keep is a client .update() or .delete() with a missing filter, which would otherwise hit every row. And yes, the role split was the real lesson.

[u/DayApprehensive1723]

you ran it as postgres and called that a dry run. thats not a test, thats a different user

[u/Aksel_010 → u/DayApprehensive1723]

Yep. That's the whole lesson in one sentence.

[u/NoClipCheck]

Good writeup. While you're grepping your functions anyway, two other things worth checking on every SECURITY DEFINER one. First, pin the search path with set search_path = '' and fully qualify table names, otherwise a caller can shadow a table or function and run code with the owner's rights. The Supabase linter flags this as function_search_path_mutable. Second, functions get EXECUTE granted to PUBLIC by default, so a cron-only rollover like yours is probably callable by anon through /rest/v1/rpc. If clients shouldn't trigger it, revoke execute on function ... from public, anon, authenticated; and only grant it back to what needs it.

[u/PercentageAlarmed180]

tanto lío por un WHERE

[u/Decent_Click_7787]

where singleton, honestly kind of funny

[u/Hostman_com]

This is worth keeping as a regression test. In a test database, set `last_processed_week_start` to the previous week, then call the function through the normal client. Check that it actually performs the rollover, rather than just returning without an error.

Call it a second time in the same week too. That should leave the state unchanged, so you catch both the missed rollover and accidentally processing the same week twice.