Schema drift in Supabase: what moves, what breaks, what watches
Schema drift is any gap between the database you believe you have and the database actually serving traffic. In a Supabase project it arrives through five doors at once: migrations, dashboard editors, SQL consoles, point-in-time restores, and generated code shipping faster than anyone reviews it. None of these doors post warnings, because none of them fail.
Scope note before mechanics, as everywhere in this cluster: RowShield detects drift by snapshotting the catalog on every scheduled scan and diffing against the previous run, delivering transitions named created, resolved, or regressed. Drift diffing is product behaviour rather than one of the nine named detection rules, so this guide teaches mechanisms plus self-serve checks instead of implying rule-backed automation.
RowShield does not detect this yet. This guide gives you the catalog queries to check it yourself. The nine rules that do ship are listed on the rules index.
What moves
Four families of objects carry security meaning, and all four move silently. Tables appear, disappear, or get recreated by migrations that drop-and-recreate as the easiest path to a column change — and policies do not survive their parent table dropping. Policies are rewritten, narrowed, widened, or deleted outright, sometimes as deliberate fixes, sometimes as collateral.
Storage buckets flip between private and public with one checkbox; the flip lives only in bucket metadata rows nobody opens. Functions change privilege or definition when generated helpers get regenerated over older versions. Each family has its own catalog home — pg_class, pg_policies, storage.buckets, pg_proc — which is precisely what makes systematic diffing tractable: the security surface is enumerable.
Velocity multiplies the problem. A conventional team might touch schema weekly; an AI-assisted one touches it hourly, and every prompt is a small dice roll against posture. Drift volume scales with generation speed, not headcount.
What breaks
Drift fails in two opposite directions, and both hurt. Exposure drift widens access: a recreated table loses its policies and answers every row to the anon key; a bucket goes public; a helper function inherits a broader grant than its predecessor. Availability drift narrows access: RLS enabled without policies deny-everything, a renamed column leaves a predicate referencing a ghost, and your app renders empty states while every dashboard reports healthy.
The nastiest property is timing rather than kind. Everything happens at deploy speed, between audits, on Friday afternoons. Any point-in-time check taken days earlier describes a database that no longer exists, which is why after-incident reviews of "but we tested this" so often end at a diff nobody thought to run.
How to see drift yourself today
Snapshot the security-relevant catalog into a file and diff outputs whenever movement is suspected:
-- snapshot.sql — run periodically, keep the outputs
SELECT 'table' AS kind, c.relname AS name,
c.relrowsecurity AS rls_enabled
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r','p')
UNION ALL
SELECT 'policy', tablename || '/' || policyname, permissive = 'PERMISSIVE'
FROM pg_catalog.pg_policies WHERE schemaname = 'public'
ORDER BY 1, 2;Reading a diff like an operator
The diff alone is not the answer; triage order is. New tables check first for RLS state, then for policy presence per command the app performs. Changed policies read as text — ask whether the expression still resolves against current columns and whether it narrows or widens. Vanished objects get a quick provenance question: intentional drop, migration side-effect, or restore artifact?
Most diffs are legitimate features, which is exactly why proportionality matters. Benign changes should confirm quietly; dangerous ones should interrupt loudly. Teams that re-read full inventories every cycle stop reading within a month — the failure mode continuous systems are engineered against.
What watches instead
Manual diffs work exactly as long as someone remembers to run them — the same reliability class as remembering to open the Security Advisor. RowShield industrialises the pattern: a snapshot every scan cycle (daily on Free, hourly on Indie, fifteen minutes on Team) compared with the last, every difference delivered as an alert naming what appeared, what returned, and what disappeared, with severity attached where the drifted state intersects shipped rules.
RowShield reads pg_catalog and bucket metadata only, never rows, and is an independent product unaffiliated with Supabase.
Frequently asked
- What is schema drift in plain terms?
- Any gap between the database state you believe you have and the state actually serving traffic. It accumulates through migrations, dashboard edits, restores and generated code — usually without errors anywhere.
- Does RowShield detect schema drift automatically?
- Yes — scheduled scans snapshot the catalog and diff consecutive runs, alerting on created, resolved and regressed transitions. It is monitoring behaviour rather than a named detection rule, which this page states rather than hides.
- Is drift ever harmless?
- Frequently — most changes are legitimate features. The value of diffing is proportionality: benign changes confirm quietly, dangerous ones interrupt loudly, and nobody re-reads full inventories.
- How often should we snapshot manually if we DIY?
- At minimum after every deploy and any console work; daily if the project moves fast. Most DIY efforts decay to monthly within a quarter, which is why automation exists.
Check your project in about ten seconds
Paste a URL. No signup, no writes, nothing stored.
Run the free audit