Blog · July 27, 2026

app.notify revoked EXECUTE from anon. Postgres had already granted it to everyone, and the revoke never touched that grant.

Every write-RPC in Ledgenter that needs to notify someone — a handoff, a mention, an assignment, a blocked task — calls one shared helper: app.notify. It's SECURITY DEFINER, it inserts into public.notifications on behalf of another actor, and the migration that created it, 0009, ends its definition with the two lines you'd expect:

grant execute on function app.notify(uuid, text, text, text, text, uuid, uuid) to authenticated;
revoke execute on function app.notify(uuid, text, text, text, text, uuid, uuid) from anon;

Grant to the role that should call it, revoke from the role that shouldn't. Read on its own, that pair looks complete. It isn't.

The grant nobody revoked

Postgres grants EXECUTE on every newly created function to PUBLIC by default — not "authenticated," not "no one," PUBLIC, the pseudo-role every other role implicitly holds. anon isn't just the anon role; it's anon plus whatever PUBLIC holds, because PUBLIC isn't a role you can opt out of inheriting. So revoke ... from anon removes a grant anon never needed in the first place — it was never the thing standing between anon and the function. The PUBLIC grant from function creation was still sitting there, ungrazed, and anon walked straight through it.

This isn't a hypothetical gap. Every other SECURITY DEFINER helper in this codebase gets it right, including the pattern one migration earlier in the very same file:

execute format('revoke all on function %s from public, anon;', r.sig);
execute format('grant execute on function %s to authenticated;', r.sig);

from public, anon — together, every time, in fifteen separate places across the migration history (0006, 0008, 0018–0020, 0023–0025, 0034, 0041, 0043, 0047, 0054, 0063, 0078, 0092, 0100). app.notify, added in 0009, was the one function where the second name in that pair silently dropped, and nothing forced it to match — no lint rule connects "you wrote a DEFINER helper" to "you revoked from both."

What actually stopped anon

Nothing did, at the grant layer. What stopped anon from doing anything with the hole was a second, unrelated line inside the function body itself:

if app.current_tenant_id() is null then
  raise exception 'notify: no tenant in claims';
end if;

app.notify derives its tenant strictly from claims — never a parameter — and an anonymous session has no tenant claim to derive. So the function was reachable and immediately failed closed. No notification row was ever insertable through this path; the blast radius was a defense-in-depth gap, not a live hole. That's exactly why it survived unnoticed: the visible behavior — anon gets an error either way — is identical whether the EXECUTE grant is closed or merely leaking through PUBLIC. You cannot tell the two states apart by calling the function. You can only tell them apart by asking Postgres what it actually granted.

The test that asked

The catch came from a pgTAP assertion written specifically to check the grant, not the behavior — a has_function_privilege probe against anon directly, added while re-deriving app.notify's full contract for coverage rather than trusting the migration's own revoke line at face value:

select ok(
  not has_function_privilege('anon', 'app.notify(uuid,text,text,text,text,uuid,uuid)', 'execute'),
  'app.notify: anon has no execute grant at all');

That assertion failed — anon did hold execute, via PUBLIC — on a codebase where the equivalent test passes everywhere else, because everywhere else actually revokes from both roles. The fix is one line, migration 0101, closing the exact gap the test named:

revoke execute on function app.notify(uuid, text, text, text, text, uuid, uuid) from public;

An app.*-schema grant-only change to an existing function, no signature or return-shape change — no public contract drift, so it shipped straight to master and rode the same hourly deploy lane as everything else.

The lesson isn't "remember to revoke from public too," though that's true. It's that a revoke statement that reads correctly, compiles, and matches the shape of every revoke around it can still be a no-op — and the only way to know is to ask the database what it granted, not read the migration and nod along.

Start at ledgenter.com.