Deploy Guard Privilege-Name Versus Command-Anchoring Automation: A Guard You Learn to Bypass Guards Nothing

A deploy-time scanner built to block genuinely dangerous database commands before they reach production is a real, valuable safety mechanism — and its entire value depends on a property that is easy to state and surprisingly easy to get wrong in practice: it needs to recognize the actual DANGEROUS ACTION, not merely a word that happens to share a spelling with one. SQL itself makes this specifically tricky, because the same word can name either a command (an actual instruction to truncate a table) or a privilege (the mere permission to do so, which a statement can just as easily be REVOKING as granting). A scanner that cannot tell the two apart will, sooner or later, block a completely safe change — and the moment that happens even once, the practical lesson learned by whoever hit the false block is not "the gate works," it is "the gate is sometimes wrong, so find a way past it when it is." A safety mechanism whose users have learned to route around it, even occasionally, has already lost the property that made it worth building.

Centriu Loop's own deployment pipeline includes a dedicated scanning step specifically written to protect a small set of core financial tables — the ledger of transactions and the current-balance table — from a set of genuinely destructive database operations reaching production undetected: truncating a table, dropping a table, database, or schema, or performing an unauthorized delete or update on the two most sensitive tables specifically.

The scanner's original implementation searched for each dangerous verb as a bare, unanchored word anywhere in a migration's text. That approach broke against a real, legitimate migration containing the line `revoke update, delete, truncate on public.loop_lancamentos from anon, authenticated` — a statement whose entire purpose is to REMOVE those three privileges from two roles that should never have held them, a defensively-written security correction, not a danger. The scanner's own pattern, matching the bare word "truncate" with no regard for context, could not distinguish that occurrence — the NAME of a privilege being revoked — from the same word appearing as an actual `TRUNCATE TABLE ...` command. The migration was rejected, and the fix's own account is explicit about the real cost of that specific kind of false rejection: a safety gate that blocks a genuinely correct, safe change teaches whoever encounters the block that the gate itself can be wrong — and once that lesson is learned, the practical response to hitting the gate is to look for a way past it, which quietly destroys the gate's protective value for every future case, including the ones where it would have been entirely correct to block.

The fix replaces the bare-word match with a match anchored to the actual grammatical START of a SQL command — the very beginning of a line, or the position immediately following a semicolon that ends the previous statement. A dangerous verb occurring at the start of a command is an actual instruction to perform that action; the identical word occurring elsewhere in the text — as the name of a privilege inside a `revoke` or `grant` clause, for instance — no longer matches at all, because it is not positioned where an actual command would begin.

A second, independent gap was identified and closed inside the same commit, this time an evasion the ORIGINAL scanner (and even a naively re-anchored one) would still miss entirely: a destructive verb constructed dynamically inside PL/pgSQL's `execute format('...')` mechanism, which builds and runs a SQL string at execution time from a format template. A scanner that only recognizes a dangerous verb sitting directly, statically, in the migration's own plain SQL text has no visibility at all into a verb assembled this way — the literal word never appears in the position a static, line-anchored pattern is looking for. A dedicated, separate check specifically for a destructive verb appearing inside an `execute format(...)` call closes this path explicitly, rather than leaving it to accident.

The fix proves its own correctness in both directions inside the same commit, deliberately not treating either direction as sufficient alone: all 5 of Loop's real, currently-shipping migrations — including the exact `revoke ... truncate ...` statement that triggered the original false rejection — pass the corrected scanner without any change to their own content, confirming the fix does not merely trade one kind of false rejection for another. And a line deliberately injected specifically to test the negative case — a genuine, standalone `truncate table public.loop_lancamentos;` command — is caught and rejected by the corrected scanner before the migration is ever allowed to reach the actual database, confirming the real protection the scanner exists to provide was not weakened in the process of removing the false positive.

Before the fix, a real, already-shipped Loop migration containing the defensively-written line `revoke update, delete, truncate on public.loop_lancamentos from anon, authenticated` — a statement that REMOVES three dangerous privileges from two roles that should never have held them — was rejected by the deploy scanner for containing the word "truncate," with no distinction between that word naming a privilege being revoked and the same word naming an actual destructive command. After the fix, the identical migration passes the scanner unchanged, because the word no longer appears positioned at the actual start of a command — while a genuine, standalone `truncate table ...` command, tested deliberately as a negative case, is still caught and rejected exactly as intended.

Centriu Loop's deploy-time migration scanner now distinguishes a dangerous SQL verb functioning as an actual command from the identical word appearing elsewhere (such as naming a privilege being revoked), by anchoring its match to the real start of a command — eliminating a class of false rejection that had already blocked one genuine, safe migration — while separately closing an evasion path for a destructive verb built dynamically inside `execute format(...)`, and proving both the false-positive fix and the original protection in the same commit.

This automation governs the internal precision of Centriu Loop's own deploy-time migration safety scanner — it does not change what any customer-facing feature does, does not add a customer-facing setting, and is specific to a text-based scanning approach for SQL migration safety; a system that enforces the identical protection purely through database-level privilege grants (rather than by scanning migration text before it runs) would not be exposed to this specific class of false-rejection risk.

Searching for a dangerous verb as a bare word anywhere in a migration's text is the simpler scanner to write, and works correctly for as long as the word never legitimately appears in any OTHER grammatical role in real migration text — a condition that fails the first time a defensive, privilege-removing statement is written using the same vocabulary as the danger it prevents. Anchoring the match to the actual start of a command takes slightly more care to implement correctly, but distinguishes a real destructive instruction from the same word appearing as a privilege name, a comment, or any other non-command context — closing the false-positive risk without weakening the scanner's real protection.