The migration that recreated the table without its policies

Why generators love drop-and-recreate

Changing a column type in place demands care — lock durations, cast validity, dependency rewriting across views and foreign keys. Dropping and recreating demands confidence. Generated migrations optimise for the second because the model cannot test lock windows against your traffic, only whether SQL parses. Posture is invisible in the output the generator sees: statements succeed, summaries read green, and nothing anywhere prices the authorization reset riding along inside a perfectly valid transaction.

The pattern peaks during refactors: normalising JSON blobs into real columns, splitting wide tables, rebuild-for-index changes. Each is legitimately easier as recreate — and each silently resets authorization to zero while looking like ordinary maintenance in the log.

Variations you will meet

The blunt pattern has quieter cousins. Migrations rebuilding a table to add a constraint, generators normalising JSON blobs into real columns, index strategies recommending rebuild-for-restructure — all arrive as drop-and-recreate wearing respectable clothes. CASCADE drops are the sharpest variant: one careless cascade removes dependent tables whose existence the author never considered, taking their policies along silently.

Foreign-key-driven recreations round out the family, where a referenced table gets rebuilt and every referencing table follows. Each variation shares one property: the log reads as ordinary maintenance, and nothing in the output mentions the authorization state that failed to travel with the new relation.

Recognising it after the fact

The signature is distinctive: policies existed last week, the table exists today, and pg_policies knows neither. One aggregate confirms the amnesia — and zero is the answer that should stop you cold, because a governed table reporting relrowsecurity true with a policy count of zero is deny-everything arriving quietly, dressed as ordinary maintenance:

SELECT c.relname AS table_name,
       count(p.policyname) AS policies_now
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_policies p
  ON p.schemaname = 'public' AND p.tablename = c.relname
WHERE c.relname = 'orders' AND c.relrowsecurity
GROUP BY c.relname;

Recovering cleanly

Recover from version control, not memory — the original policies lived in some earlier migration, and replaying them verbatim beats reconstructing intent under pressure. When originals cannot be located, RowShield’s remediation generator rebuilds owner-scoped policies from current columns with FORCE included, which is a defensible baseline rather than archaeology. Keep recovered definitions in version control from then on, even where they originated in a console session; the next recreate finds its restore material waiting instead of relying on anyone’s recollection of intent.

Then verify behaviourally before reopening: probe as the anon caller and confirm empty responses where exposure used to be. Catalog truth and wire truth agreeing is the only finish line worth declaring here.

Preventing the reset

Two guards compose well. Conventionally, forbid DROP TABLE in application-owned migrations — require expand/contract instead, where old and new coexist for one release cycle and the drop happens deliberately, reviewed, later.

Practically, let scheduled scans notice recreation within minutes: Team cadence bounds the window to fifteen minutes, Free to overnight — versus the historical alternative of discovering the loss at the next audit, or the next incident report.

Frequently asked

Do policies really vanish on table recreation?

Yes. Policies belong to their relation; DROP TABLE removes them irrecoverably, and the replacement starts with whatever the new DDL declares — usually nothing. Recovery therefore means replaying original definitions from version control or regenerating defensible baselines, because nothing in the database itself remembers what was lost, which is what makes this failure class feel unfair the first time you meet it.

Which severity is the naked-recreate outcome?

Both endings are findings: RLS-disabled recreation is critical exposure, while enabled-but-policyless is high-severity availability loss. The scan reports whichever actually occurred. The distinction maps to the two directions drift fails in: missing enablement exposes rows to whoever holds the anon key, while enablement without policies serves empty results masquerading as quiet success. Same migration, opposite symptoms, both caught by shipped rules.

Could backups restore the lost policies?

Point-in-time recovery restores wholesale moments, not selective objects — rolling back everything to recover policies trades data for DDL. Replay from version control or regenerate instead. Restoring an entire cluster moment to recover a handful of policy definitions also resurrects every other difference between then and now, trading one known loss for several unknown ones. Version control replay stays the honest path.