The database privilege I'd revoked had quietly come back in some regeneration

Is the REVOKE you wrote into your migration file actually reflected in the live privilege list right now?

The weekly security alert email arrived. A form email — "a table is exposed" — pointing at a coordinate-system table created by an extension. We can't fix its permissions, its contents are public standard data, and it carries no risk. It was the third time I'd gotten the same email, so I could have shrugged it off as "that one again."

Instead of the email, I pulled the full set via API. 214 items, and the single highest-severity item really was just that one.

What would you do?

The top severity is one harmless item. Do you close here, or open the 193 warnings one tier down? The real hole was in that pile.

The real hole was in the pile one severity tier down

Two internal wrapper functions that read from the secret store were sitting open with execute permission granted to the anonymous role. Pass a name, get the plaintext back, no identity check. They're callable over REST with only the public key that ships baked into the app. I poked at one and got a 200.

Two twists.

One. The migration that created these functions was locking them down correctly. The file had REVOKE ALL ... FROM PUBLIC and a GRANT to the service role only, written in exactly. But the live privilege list had the anonymous role and the authenticated role back in it. The platform's default grants auto-attach those two roles to a new function, so some regeneration along the way undid the lock. "I put the revoke in the migration" is not evidence of the current state.

Two. I did a full sweep of this exact class two months ago and still missed it. Back then I narrowed the check to three stages, and stage two was "the body contains insert / update / delete." A function that returns a secret writes not a single line. It disappeared from the filter structurally.

Narrowing by whether it writes misses the highest severity.

Honestly

• I can't prove anything was actually pulled during the exposure window. There's no after-the-fact confirmation from access logs.
• So the revoke isn't the end — rotating the deployment and server credentials is still pending. Rotation halts other systems at the same time, so it needs its own plan.
• This is the third time I've confirmed you can't prioritize by severity tier, and all three times it was the same email. The habit of ignoring the email was itself the warning sign.

The lesson: run the read branch separately

Full query, distribution by tier: 1 highest / 193 warning / 20 info. Inside the warnings, 83 anon-executable definer functions.

-- narrowing to the write branch only makes secret-returning functions vanish
and p.prorettype <> 'trigger'::regtype
and p.prosrc ~* 'select'
and not (p.prosrc ~* 'auth\.uid|auth\.jwt|auth\.role')

Two things that actually helped in the revoke procedure:

1. Run the self-verification block on its own first and confirm it fails. Skip this and you read "the check passed while empty" as success.
2. After the revoke, poke again with the anonymous key and you must get permission denied for it to count. Sending extra arguments produces a signature-mismatch 404, which is easy to misread as success. Send it with the real signature.

Of the 13 I re-swept on the read branch, the rest were all public by design. Revoking a live app's public API off a static rule alone will break the app — the absence of an identity check is not itself a hole.

Three checks

• If you've written a REVOKE into a migration file, have you ever queried whether it's reflected in the live privilege list now? Function regeneration re-attaches the default grants.
• When you audit definer-privilege functions, is your filter narrowed to "the body writes"? A function that returns a secret writes not a line and drops out of that filter.
• Did you get your "it's blocked" verdict from a permission denied on a real-signature call, or did you read an argument-mismatch 404 as success?

This is a follow-up to the post-deletion master key that was a public constant baked into the app. That one was about a constant in the function body being the privilege; this one is about the function privilege itself quietly coming back.

Pull the list of definer functions executable with the anonymous or public key, then count how many of them return a value. An audit narrowed to writes won't show them.