4 · The drift taxonomy

The question this chapter answers: how does a secure project quietly become an insecure one?

Nothing in this chapter is a policy failing. Every policy in TaskHarbor still parses and runs exactly as Chapter 3 wrote it. Drift is what happens around them: new tables, new migrations, new shortcuts, new surfaces. Five patterns account for nearly all of it, and each has a signature you can detect with a query.

The patterns below run in sequence on the TaskHarbor you built in Chapter 3 — Pattern 1's table is still there when Pattern 2 destroys the one it points at, which is the point. They are also the only examples in this book that damage the project deliberately: they leave tasks unprotected and a tautological policy in place. Run them on a scratch copy, or rebuild from Appendix C before continuing to Chapter 5.

Pattern 1: the table that arrived naked

A feedback feature ships. The generator produces a fine migration for comments — columns, keys, indexes — and no security statements, because nobody asked about readers.

create table public.comments (
  id      uuid primary key default gen_random_uuid(),
  task_id uuid references public.tasks(id) on delete cascade,
  body    text not null
);

In Postgres, row security is off by default; enabling it is a separate statement (Postgres docs). On projects where default grants to client roles are still active, the anon key can now read every comment and write new ones. The policies protecting tasks do not extend one inch to comments.

Signature: any row in the catalog with relrowsecurity = false (the query is Chapter 5's first one). This is the loudest pattern and the one scanners check first — including RowShield's own RLS_DISABLED class of finding, which exists precisely because this pattern never produces an error of its own.

Pattern 2: the migration that rebuilt the table

TaskHarbor's product team renames a column. An assistant helpfully regenerates the whole table. The plain drop table public.tasks; stops immediately — Pattern 1's comments.task_id still references it, and Postgres refuses with cannot drop table tasks because other objects depend on it. The hint in that error message is the whole pattern in one word:

drop table public.tasks cascade;
-- NOTICE: drop cascades to constraint comments_task_id_fkey on table comments

create table public.tasks (
  id         uuid primary key default gen_random_uuid(),
  project_id uuid not null,
  title      text not null,
  done       boolean not null default false,
  priority   text not null default 'normal'
);

Policies are catalog objects attached to a table. Drop the table and they vanish with it; recreate it and you get a fresh object with RLS disabled and zero policies. The migration "worked" — every test that touches structure passes — and the tenant boundary is gone. The fix from three weeks ago did not stop being correct; it stopped existing.

cascade widens the damage past the policies, and it does so with a NOTICE rather than an error. It dragged down comments_task_id_fkey — the foreign key comments used to hold on tasks. The comments table and every one of its rows survive; only the guarantee is gone. Nothing now stops a comment from pointing at a task id that no longer exists. The rewritten tasks never restored its own references public.projects(id) either, so project_id — the column every task policy joins through to find a workspace — is no longer guaranteed to point at a real project. Referential integrity and row security left in the same statement, and only one of them was ever mentioned in the pull request.

Signature: a table whose policy list is empty, or whose relrowsecurity flag flipped after a deploy — plus, for the cascade variant, a foreign key that appeared in pg_constraint last week and does not today. Comparing snapshots over time catches all three instantly; reading the current schema alone does not, because there is nothing left to read.

Pattern 3: permissive sprawl

A report comes in: some members see empty task lists. Debugging under pressure, someone adds:

create policy "tasks: quick check" on public.tasks
  for select to authenticated using (true);

Symptom gone. Policy left in place. By Chapter 2's combination rule, tasks now permits exists (...) or true — which is true. Every other select policy on the table is now decorative, and nothing errored anywhere. Sprawl also happens politely: three overlapping policies (own, team, org) whose union nobody re-derived as membership changed (the union trap).

Signature: any policy whose condition is constant true, and any table carrying multiple permissive policies for the same command. Both are plain-text readable in pg_policies.

Pattern 4: service-role creep

An integration starts throwing empty results. Someone remembers the server key bypasses everything, switches the fetcher to service_role, and moves on. The code works forever after — and the database's authorization layer stops being consulted on that path. Two costs follow. First, real policy bugs on that surface become invisible instead of loud. Second, each such call site becomes a place where a bug (a missing where, a leaked id) exposes rows across every tenant at once. The key itself may stay secret; its use has silently changed the trust model.

This is drift in behavior rather than in DDL, so no catalog query finds it. Its signatures live elsewhere: grep your server code for service-key initialization and demand one justifying sentence per hit. Keep user-facing paths on user tokens so policies keep doing their job; reserve the bypassing role for deliberate, scoped jobs.

Pattern 5: the surfaces beyond tables

Two TaskHarbor additions complete the taxonomy. A private bucket appears for attachments:

insert into storage.buckets (id, name, "public")
values ('task-files', 'task-files', false);

Table policies do not cover storage; storage.objects needs its own policies (Chapter 8). With none, uploads fail loudly — so someone flips "public" to true, and every attachment becomes URL-addressable. Quietly. Meanwhile a realtime subscription gets wired up for comments; Postgres Changes authorizes through the table's RLS policies, except that delete events carry no row-level check at all (Realtime docs) — a nuance almost no one reads until they need it.

Signature: buckets whose visibility nobody can explain, storage.objects policies that are absent or tautological, and publications containing tables whose policies predate the subscription.

Why reading is not enough

Notice what all five have in common: at no point does anything fail. The catalog stays internally consistent; the app keeps working; the dashboard shows green. That is why the honest defense is behavioral — ask the running database what each role can actually reach — and why Chapter 5 turns these five signatures into an afternoon audit you can run yourself. The patterns recur because the forces behind them (generation speed, deadline pressure, forgotten context) recur. Detection has to be repeatable, not heroic.

Check on your project

1. Run a catalog scan right now for tables with RLS disabled (Chapter 5 gives the exact query if you want it verbatim). Write down the count and today's date.
2. Open your last ten migrations. For each create table, check the same file contains enable row level security plus grants or policies. For each drop table or rebuild, note whether its policies and its foreign keys were recreated — cascade takes both and announces it only as a NOTICE.
3. Search your policies for constant-true conditions. If you find one, trace who added it and what bug it was papering over — then decide if the underlying rule deserves a restrictive policy.
4. List every call site that uses the service/secret key, each with its one-sentence justification. Anything you cannot justify is a candidate for Pattern 4.
5. Inventory every storage bucket and every realtime publication against the table list from item 1. Anything present in the second list but absent in your mental model is Pattern 5.