IAM releases often require schema changes. When this happens, multiple pods start simultaneously and must coordinate so exactly one applies the migration while others wait. Coordination relies on a session-level PostgreSQL advisory lock, which works only on a direct database connection. IAM runs [`bin/migrate`](https://gitlab.com/gitlab-org/auth/iam/-/tree/main/cmd/migrate) at startup. The goose migration tool acquires a session advisory lock, applies pending migrations, and releases the lock ([pkg/migrate](https://gitlab.com/gitlab-org/auth/iam/-/blob/main/pkg/migrate/migrate.go)). This lock is the only protection: the `schema_migrations` table has no unique constraint on `version_id`. A transaction-mode pooler gives a client a different server connection per transaction, and PgBouncer documents that session advisory locks never work in this mode ([features](https://www.pgbouncer.org/features.html)). ## Technical detail Two failure modes exist behind a transaction-mode pooler. - The unlock statement lands on a different server connection than the lock acquisition. The unlock fails. The lock stays held by a connection nobody owns. Later migration runs wait five minutes and fail. Upgrades stall with no clear cause. - A second migration process can receive the same server connection that already holds the lock. Advisory locks are re-entrant within a session, so both migration processes proceed. Concurrent schema changes can corrupt the database. Since IAM handles authentication, a corrupted database means users cannot sign in. ## Impact Nothing breaks today: staging, production, sandbox, and CI all connect directly to the database. The problem appears in self-managed installs, so the fix must land before the IAM Omnibus recipe is written. PgBouncer is not enabled by default in Omnibus, but when an operator enables it for HA, the component database framework pools component databases automatically in transaction mode with no reset query. A secondary defect also appears under the Omnibus pooler config: pgx uses prepared statements by default, but the Omnibus cookbook sets `max_prepared_statements = 0`. This breaks ordinary service queries. PgBouncer 1.24.0 and later enable prepared-statement support by default (200), so this issue is specific to current Omnibus defaults. The GATE sandbox hit the same failure with Rails and routed migrations around its pooler ([sandbox-config!81](https://gitlab.com/gitlab-org/architecture/auth-architecture/sandbox-config/-/merge_requests/81)); Rails production does the same ([docs](https://docs.gitlab.com/administration/postgresql/pgbouncer/)). ## Chosen fix IAM adopts the labkit v2 database APIs. The labkit infrastructure config carries two address pairs: `host`/`port` (direct) and `poolHost`/`poolPort` (pooler). The postgres client resolves the pool address only when the connection pool is enabled ([format.go](https://gitlab.com/gitlab-org/labkit/-/blob/master/v2/postgres/format.go#L150)). The migration client passes `postgres.WithConnectionPool(false)`, which selects the direct address and forces a single connection. The advisory lock works. Migrations keep running in the same container, reading the same config file. Artifact Registry already implements this pattern: its migration client [bypasses the pooler](https://gitlab.com/gitlab-org/ops/artifact-registry/-/blob/472ef3e/internal/datastore/postgres.go#L45-55) and a [regression test](https://gitlab.com/gitlab-org/ops/artifact-registry/-/blob/472ef3e/internal/datastore/migrations/migrations_test.go#L39-41) sets `PoolHost` to an invalid hostname to prove migrations never use it. labkit also defaults pgx to the simple query protocol for PgBouncer compatibility, which resolves the prepared-statement defect for service queries. This fix rides on the labkit v2 adoption ([#621514](https://gitlab.com/gitlab-org/gitlab/-/work_items/621514)), which needs the v2 database APIs added to its scope. The dedicated migration Job ([#602574](https://gitlab.com/gitlab-org/gitlab/-/work_items/602574)) is not required for this fix but remains planned: it removes the startup-time ceiling (goose waits up to 300 seconds for the lock while the startup probe kills the pod after 100 seconds), provides one migration runner per upgrade, and retires the `migration_username`/`migration_password` split. One design point remains open: labkit config carries one credential, but IAM separates an unprivileged service role from a privileged migration role. Either the labkit schema grows a second credential or the Job model resolves it. ## Interim Until the fix lands, self-managed installs must point `iam-data-access` at PostgreSQL directly or run the pooler in session pooling mode. The Artifact Registry self-managed install guide documents this ([artifact-registry!1963](https://gitlab.com/gitlab-org/ops/artifact-registry/-/merge_requests/1963)). ## Fixes needed regardless ADR data-02 overstates the lock behavior. It claims the lock releases automatically on ungraceful exit (a dead process holds it until the server times the connection out, which can take hours) and that it ensures a single migration runner on Omnibus (false behind a transaction-mode pooler). Both statements need correction. `scripts/entrypoint.sh` mentions a `run_on_startup` flag that does not exist. Either implement it or remove the comment. ## Open question Are there plans to enable YSQL Connection Manager on our YugabyteDB universes? It pools per transaction like PgBouncer, is off by default, and the setting on our universes has not been confirmed. YugabyteDB fixed advisory locks for it ([yugabyte-db#27249](https://github.com/yugabyte/yugabyte-db/issues/27249)), but verify rather than assume. ## Verification Reproduce both failure modes through a transaction-mode PgBouncer in the local compose stack and CI. Show that the migration client connects directly. Verify that two concurrent migrate runs remain mutually exclusive on both PostgreSQL and YugabyteDB. ## Links - ADR review discussion: https://gitlab.com/gitlab-org/architecture/auth-architecture/design-doc/-/merge_requests/133#note_3646401117 - Omnibus component database framework: https://gitlab.com/gitlab-org/omnibus-gitlab/-/merge_requests/9440 - Omnibus PgBouncer cookbook defaults: https://gitlab.com/gitlab-org/omnibus-gitlab/-/blob/master/files/gitlab-cookbooks/pgbouncer/attributes/default.rb - goose session locking: https://github.com/pressly/goose/issues/335