Flow
Where each check runs
1Check the account
The app account name is sane and the account exists.
2Read the rule
Compare what the app needs with the owner rule in the catalog.
3Stop and explain
Print what is missing and the statements to run, and grant nothing.
4Run migrations
Only when the deploy is applying them.
5Name the leftovers
List any table the app cannot use, before the schema check.
The deploy ran my database migrations, and only then did the schema check right after them fail. By then the database had already changed.
The cause was a restore. A few days earlier I had rebuilt the production database for the ordering system from a copy of the development one. The restored database was missing one rule: the default privileges for the account that runs migrations. I think the copy dropped it, but that's a guess.
Two accounts and one standing rule
I use two database accounts. Migrations run as the owner, which can change the schema. The app runs as a separate account that isn't the owner.
In Postgres, a new table belongs to whoever made it, and other accounts can't use it until someone grants access. A default-privilege rule says, ahead of time, "when the owner makes a new table, let the app use it too." That rule is stored apart from the tables, so a database can have every table and still not have the rule.
Probably the old tables kept their access, so the restore didn't look broken. When a migration made new tables, the app couldn't use them, and the only check that noticed ran after the migrations were done. Running the deploy again would never fix it, because a person had to add the rule.
That order was my mistake. The answer was in the database's own catalog all along, cheap to read. The deploy waited for the damage instead.
Check first, then migrate
I had the agent add a read-only check before migrating. The order is the whole idea:
The order is the point: confirm the grant rule, migrate, name any table the app can't use, then compare the models to the database.
The first check makes sure the app's account name is sane and the account exists. Then it reads the catalog and confirms the owner's rule gives the app read, insert, update and delete on tables, plus use of sequences and types. If anything is missing, the deploy stops before migrating and prints the exact statements to run.
It never runs them itself. If a deploy script hands out database access to get past its own check, it's giving away access nobody reviewed, and it hides the drift. A person should decide that.
The second check runs after migrating and lists any table the app can't use, by name. It can't prevent anything, but a list of names beats a vague failure. Both checks only run when the deploy is applying migrations.
What it still misses
It only counts access given straight to the app's account, so access through a group would make it stop a deploy that would have worked. It covers one schema, and the after-check skips sequences. The test only reads the script as text and never touches a real database, so the query itself is still unproven.