Schema drift, defined

A definition

Schema drift is the accumulation of structural change in a database after the point at which somebody last checked what that structure permits. It is not corruption and not a mistake. It is the ordinary consequence of a project continuing to be worked on.

The word is doing something specific. Drift is not "the schema changed" — schemas are supposed to change. It is the widening gap between the schema as it is and the last time anyone evaluated it as a security surface. A project where migrations ship weekly and the last policy review was in April has four months of drift, regardless of whether any individual migration was careless.

What accumulates is not only tables. Every one of these alters what your database will hand out, and each is invisible in isolation:

• A new table, with row-level security not yet enabled on it.
• A new column on an existing table, which a row-filtering policy does not constrain — policies filter rows, not columns.
• A policy dropped or replaced during a migration, leaving a table covered by fewer rules than the team believes.
• A new permissive policy added alongside existing ones, where the effective rule is now the union of all of them and nobody has evaluated the union.
• A storage bucket flipped public for a legitimate reason that has since passed.
• An index removed, making a policy expensive enough that somebody will eventually propose turning it off.

Nothing here is negligent. Every step was a reasonable response to a real requirement.

Comparing states rather than reading them

Drift is only visible as a difference, which is why reading your current schema does not reveal it. The current state looks like a schema. It looks fine. What tells you anything is the same schema captured twice.

A useful snapshot is more than a table list. It needs the properties that decide behaviour: for each table whether row-level security is enabled and whether it is forced, and its columns; for each policy its command, whether it is permissive or restrictive, which roles it applies to, and both its USING and WITH CHECK expressions; for each index its columns and predicate, because an index is part of whether a policy survives contact with production; for each storage bucket whether it is public. Then a timestamp, so the comparison has two ends.

select t.tablename, t.rowsecurity, p.policyname, p.cmd, p.roles, p.qual, p.with_check
from pg_tables t
left join pg_policies p on p.schemaname = t.schemaname and p.tablename = t.tablename
where t.schemaname = 'public'
order by t.tablename, p.policyname;

Save that output. Run it again next month. The diff is the drift, and it will contain three kinds of line: something appeared, something came back after being fixed, and something disappeared — that last one being a finding as often as the first.

Two practical notes on doing this by hand, because the naive version produces a diff nobody reads. Sort deterministically, or half the output will be rows that merely moved. And store the snapshot somewhere outside the database it describes: a snapshot that a migration can drop along with everything else is not much of a baseline.

The interpretation is where the judgement lives. Not every difference is a problem — a new table with a correct policy attached is a healthy diff, and a policy replaced by a narrower one is an improvement. What you are looking for is a smaller set: a table that arrived without row-level security, a policy that got broader, a WITH CHECK clause that vanished, a bucket that became public, an index that was dropped from under a policy that depends on it. Everything else is noise you should be able to dismiss in seconds.

The reason drift deserves a name at all is that it reframes the question people ask. "Is our database secure" has no stable answer and invites an audit that is out of date the week it is delivered. "What changed since we last looked" has a definite answer, it is cheap to compute, and it is the only version of the question that stays true long enough to act on.

The reason this is worth mechanising rather than diarising is not that the query is hard. It is that the useful question is never "is our schema secure", which has no stable answer, but "what changed since we last looked" — and that one is only answerable if somebody was looking on a schedule.

Policies themselves do not fail, which is a longer argument made here. They keep doing exactly what they were written to do, while the world they were written for moves.

Run a free audit

RowShield is an independent product with no affiliation to, or endorsement from, Supabase.