In April we opened our first site in Ireland, and the order service got its own database there, built by the same Terraform module and migrated by the same pipeline as the other four. The first deploy stopped at the first migration with permission denied for schema public. Every migration in that repository had passed in CI on every pull request for three years.
Our CI job starts Postgres as a service container from the official image and runs the migrations against it before the integration tests. The image creates one role, from POSTGRES_USER, and that role is a superuser. A superuser skips permission checks entirely, so CI could prove that our SQL was valid and nothing at all about whether our application was allowed to run it.
In production the migrations run as orders_migrator, which is not a superuser and does not own the database. That had always worked because, up to Postgres 14, every role could create objects in the public schema.…