Migrations that silently weaken policies

Migrations rarely attack authorization directly — they just rearrange the objects policies depend on, and protection erodes as a side effect. This article shows the four recurring patterns in runnable SQL, what each leaves behind, and the before/after checklist that catches them.

Policies are database objects with dependencies. They reference columns by name, attach to tables by name, and exist only while their prerequisites exist. That makes them first-class casualties of ordinary refactoring: any migration that touches tables or columns can strengthen features while quietly weakening the rules that protect them. Nothing about this is exotic. Every pattern below was executed against current Postgres during this article's preparation, and every one left a working application behind.

What distinguishes migration-driven drift from other kinds is its direction of surprise: it almost always helps in the short term. The recreated table stops erroring. The CASCADE unblocks the deploy. The split finishes the feature. Each pattern buys immediate progress with deferred isolation debt, which is exactly the trade teams accept under pressure unless a check exists to price it.

Why migrations are where policies go to die

Code review has an asymmetry baked into it: reviewers evaluate diffs for behavior the change is about. A migration adding a notifications table gets reviewed for column types, indexes, and backfill logic — nobody re-verifies the authorization posture of the whole schema on every PR, because that verification is tedious and manual.

Meanwhile, policies are post-data objects: they are created after tables, they depend on columns, and they carry no presence in application code. When a migration's blast radius reaches them, no test fails unless someone wrote a test asserting policy existence — which most suites don't. The result is a systematic blind spot, summarized:

Migration act	What reviewers check	What actually changes
Recreate table	Columns, indexes	RLS flag gone; every policy gone
Drop column	Callers of the column	Policies referencing it die with CASCADE
Rename anything	Updated references in code	Policies follow automatically (the safe case)
Split/merge tables	New query paths	Old policies cover none of the new surface

The rest of this article walks each row with real SQL, then gives you the two-query checklist that closes the gap regardless of which pattern sneaks through.

A note on scope before the patterns: everything here concerns schema migrations, but the same dependency logic applies to data backfills that drop-and-refill tables, and to environment copies that ship schemas between staging and production by hand. Wherever table definitions travel without their post-data objects, the four patterns below are what waits. The names change — "sync script" instead of "migration" — and the outcome does not.

Pattern 1: recreate the table, lose everything

The most common form is accidental. A type must change, a constraint must be rebuilt, an ORM suggests drop-and-recreate because Postgres lacks some direct alteration. All three variants below were run and produce the same outcome:

create table projects (
 id uuid primary key,
 owner_id uuid not null,
 title text not null
);
alter table projects enable row level security;

create policy "projects_select"
 on projects for select to authenticated
 using ((select auth.uid()) = owner_id);

State today: RLS enabled, one ownership policy. Now the refactor:

-- Variant A: explicit drop-and-recreate under the same name
drop table projects;
create table projects (
 id uuid primary key,
 owner_id uuid not null,
 title text not null
);

-- Variant B: clone-based rebuild
create table projects_v2 (like projects including all);
-- ...copy data, swap names...

-- Variant C: create-table-as shortcut
create table projects_v3 as select * from projects;

As executed, all three descendants share one property catalogued immediately after the run:

select c.relname, c.relrowsecurity as rls_enabled
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by c.relname;

 relname | rls_enabled
-------------+-------------
 projects | f
 projects_v2 | f
 projects_v3 | f

INCLUDING ALL copies columns, indexes, defaults, even storage parameters — and not row security. CREATE TABLE AS doesn't pretend to. And variant A's fresh table starts life exactly like any new table: flag off, zero policies. On a Supabase project the client roles' default grants mean each of these tables is immediately readable through the API the moment data lands. The feature being migrated keeps working — better than before, if the old setup had been half-broken. The full anatomy of that window is at new table shipped without RLS.

Worth internalizing: the danger scales with how routine the operation feels. A drop-and-recreate performed during an incident gets scrutiny; the same operation embedded in a Friday-afternoon ORM migration gets merged. Teams adopting schema-management tools should read their generated SQL once with this specific question in mind — "does anything here DROP or re-CREATE a table my policies live on?" — because tool output optimizes for schema equivalence, and policy objects are not part of what it considers equivalent.

Pattern 2: CASCADE amputates a policy legally

Supabase's own dependency tracking will warn you — loudly, even. The question is whether anyone hears it over deadline pressure. Set up a policy that depends on a column:

create table documents (
 id uuid primary key,
 owner_id uuid not null,
 title text not null,
 confidential boolean not null default false
);
alter table documents enable row level security;

create policy documents_confidential_gate
 on documents as restrictive for select to authenticated
 using (confidential = false);

Then try the obvious refactor:

alter table documents drop column confidential restrict;

Postgres refuses, naming the victim in advance:

ERROR: cannot drop column confidential of table documents because
 other objects depend on it
DETAIL: policy documents_confidential_gate on table documents
 depends on column confidential of table documents
HINT: Use DROP ... CASCADE to drop the dependent objects too.

That DETAIL line is the database telling you precisely which protection dies if you proceed. The CASCADE path proceeds anyway:

alter table documents drop column confidential cascade;
-- NOTICE: drop cascades to policy documents_confidential_gate
-- on table documents

Migration succeeds. Application works. The restrictive gate — possibly the one thing standing between permissive policies and your compliance posture — is gone, recorded only in a NOTICE that CI logs swallow. We watched this exact beat delete a timeline's only defense in the schema drift guide's month four; here is the pre-flight query that would have surfaced the dependency before writing any DDL:

select tablename, policyname, cmd
from pg_policies
where schemaname = 'public'
 and (qual ilike '%confidential%' or with_check ilike '%confidential%');

Every row returned is a policy scheduled for execution if this migration ships with CASCADE. Run it as part of drafting the migration, not after the NOTICE appears — the difference between "we rewrote the gate onto the new classification column in the same PR" and "we discovered the amputation next quarter" is entirely when this query runs.

Pattern 3: renames are safer than their reputation

Good news for once, verified both ways. Renaming a column updates policy expressions automatically:

select qual from pg_policies where tablename = 'projects';
-- (( SELECT auth.uid() AS uid) = owner_id)

alter table projects rename column owner_id to created_by;

select qual from pg_policies where tablename = 'projects';
-- (( SELECT auth.uid() AS uid) = created_by)

Renaming the table carries its policies along identically. Postgres tracks these dependencies properly, and rename is a metadata operation — no policy loss occurs. The residual risks are human, not mechanical:

• The rename can break generated clients and raw SQL elsewhere, producing pressure to "just revert it" via recreate — landing you in Pattern 1.
• Renames don't fix stale intent: a policy now reading created_by = auth.uid() still encodes whatever assumption it always had, under a fresher name.
• Renaming a column out from under a tautology-adjacent policy changes nothing about its width; renames preserve meaning, including bad meaning.

Treat renames as the pattern that behaves — and let that trust make you more suspicious of the patterns that don't. There is a second-order risk worth naming: a rename that updates policies correctly can still break application queries, generated types, or embedded dashboards, and the fastest "fix" under pressure is recreating the old column name — sometimes as a drop-and-recreate of the whole table, landing you in Pattern 1 with nobody watching the authorization surface. When a rename ships, watch the follow-up commits for exactly that rebound.

Pattern 4: splits and merges leave orphans and unions

Feature growth often splits a table ("archive old rows") or merges two ("unify profiles"). Both operations interact badly with policy sets:

Splitting strands the original policies on the original table. The new sibling — same shape, same sensitivity — inherits nothing, for the same reasons as Pattern 1's clones. Teams remember to migrate queries; policies aren't queries, and nothing red flags their absence until someone probes the new table as an outsider.

Merging is sneakier because nothing looks missing. Suppose two tables each carry an ownership select policy, and a consolidation folds both datasets into one table while porting both policies verbatim. Permissive policies OR together — the union semantics from policy accumulation — so rows now match whichever legacy condition is looser. Each policy was individually reviewed and correct; the merge silently promoted the more permissive of the pair to govern the whole combined dataset.

Neither failure produces an error, a failed test, or a dashboard signal. Both are visible in ten seconds to anyone who lists policies per table after the migration and compares against before.

The dump-and-restore special case

Schema moves through time by migrations; it also moves by dumps. The two paths treat policies differently, which is where restore-shaped drift enters:

• A full schema dump (pg_dump without --data-only) includes policy definitions alongside tables. Restore it faithfully and protection arrives with the schema — this is the safe default.
• A data-only restore into freshly created tables carries no policies at all, because data-only means no post-data objects. If the target tables were hand-created during the operation, they start exactly like Pattern 1's recreates: flag off, zero policies, grants ready.
• Partial workflows — one table dumped here, restored there, the original dropped in between — combine both hazards and are the most common way a single-table "cleanup" deletes its own protection.

None of these paths warn. A restore completes successfully whether or not the resulting schema matches what you'd call protected, because "matches intent" was never a property dumps carry. The practical rules: prefer full-schema restores; when surgery is unavoidable, run the snapshot pair from the checklist below immediately after; and treat any environment rebuilt outside the migration pipeline as unauthorized until verified — the assumption that production equals what CI approved is precisely the gap staging-versus-production drift lives in.

The two-query checklist that catches all four patterns

This is the entire discipline, sized to fit in a migration template. Before the migration runs, snapshot the protection state:

-- protection_snapshot.sql
select c.relname,
 c.relrowsecurity as rls_enabled,
 count(p.policyname) as policy_count
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_policies p
 on p.schemaname = n.nspname and p.tablename = c.relname
where n.nspname = 'public' and c.relkind = 'r'
group by c.relname, c.relrowsecurity
order by c.relname;

After the migration runs, run it again. Three comparisons tell you everything:

1. Any new table with rls_enabled = false or policy_count = 0 — Patterns 1 and 4a.
2. Any familiar table whose policy_count dropped without an explicit, intended policy deletion in the diff — Pattern 2's CASCADE signature.
3. Any table where policies changed but the diff didn't mention authorization at all — Pattern 4b's union effect, and anything else unexpected.

Teams using migration tools can wire the snapshot pair into CI as a generated artifact: fail the build on unexplained deltas, require a one-line justification comment for intentional ones. The wiring is deliberately boring — capture to a file, diff against the previous capture, non-zero exit on unexplained change:

psql "$DATABASE_URL" -f protection_snapshot.sql > after.txt
diff migrations/_protection_baseline.txt after.txt \
 || (echo "Protection inventory changed — justify or fix"; exit 1)
cp after.txt migrations/_protection_baseline.txt

Committed alongside the migrations it guards, the baseline file makes protection drift as reviewable as any code change: the PR diff now contains authorization consequences, in four columns, where a reviewer cannot miss them. The cost is seconds per migration; the alternative cost is measured in quarters, as teams who found the same regression twice tend to discover.

For state that changes outside migrations — restores, dashboard edits, hotfix branches — no CI hook can see it. That residual window is exactly what continuous monitoring covers, and why our remediation flow (the SQL reference) pairs proposed fixes with ongoing checks rather than one-time cleanups.

Common questions

Doesn't pg_dump capture policies? Aren't restores safe?

Schema dumps include policies, so a full dump-and-restore preserves them. The danger sits in partial workflows: data-only restores applied to freshly created tables, hand-built replacement tables during surgery, or snapshots predating a hardening migration. Restores reproduce whatever state existed — including states you were glad to leave behind.

How do I find which policies a planned DROP COLUMN would kill?

Query pg_policies for the column name inside qual and with_check, as shown in Pattern 2 — plus check pg_depend for non-policy dependents. If several policies appear, plan their replacements in the same migration rather than accepting the CASCADE notice as an answer.

Is there ever a legitimate reason to disable RLS in a migration?

Yes, narrowly: bulk-load sessions sometimes disable constraints and security temporarily for speed, re-enabling afterward within the same migration. Treat it like disabling triggers — acceptable when scoped, transactional, and restored; a red flag anywhere else. Your post-migration snapshot should show the flag back on, which is precisely what the checklist enforces.

We use an ORM that manages schema. Does this apply?

Doubly. ORM-generated migrations are exactly where recreate-style refactors originate, and the tool's diff view shows tables and columns, not policy objects. Run the snapshot pair around ORM-generated migrations especially — they're the highest-volume source of Pattern 1 in the wild.

Do views over a table change any of this?

Views depend on their underlying tables the way policies do, so renames propagate cleanly — but replacing or dropping tables beneath views follows the same CASCADE mechanics as policies, and pre-Postgres-15 view semantics bypass row security entirely unless the view is declared security_invoker. If your migration touches a table feeding views, extend the pre-flight query to include pg_depend, and review the views guide for the invoker setting.

---

Check your schema's current protection inventory in minutes: run the free scan — paste your app URL and review findings with proposed remediation SQL for anything drifting.

RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.