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. Postgres 15 removed that: in a new cluster the public schema belongs to the database owner, and nobody else may create in it. Our four existing databases had been through in place major version upgrades, which keep the old permissions, so they still behaved like 14. Ireland was the first instance created fresh on 16, and the first place the new rule applied.
CI was already running 16, so upgrading the image would not have helped. The version was never the gap. The role was.
The fix in Ireland was one grant, CREATE on schema public to orders_migrator, which now lives in the database bootstrap in Terraform and has been applied to the old four as well, so all five agree. The bigger change was CI. The job runs the same bootstrap SQL as production against the service container, creates orders_migrator and the runtime role with the same grants, and runs migrations and tests as those roles. The superuser only exists to set things up. The first migration step reads rolsuper for current_user and fails the build if it is true. That check found two more repositories whose tests had only ever run as postgres, and one of them had a migration altering a table owned by a different role, which production would have refused.
A superuser cannot tell you what is allowed. Our tests proved the SQL worked and never asked who would be running it.
– Sergey Shinder
Top comments (0)