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. This is not Omnibus-specific. Any self-managed install that fronts IAM's database with a transaction-mode pooler hits this, and the fix must land before that path goes GA. 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. Artifact Registry self-managed via CNG is on the same track, targeting an Oct 15 launch. A self-managed customer running a connection pooler in front of it hits the same two failure modes described above. This is already called out to customers as [known gap #8 in the AR self-managed install guide](https://gitlab.com/gitlab-org/ops/artifact-registry/-/blob/ee014de5b8ece98f2e3ed10f987c29c8b7eac287/docs/dev/self-managed-install.md#L622), which tells operators to point `iam-data-access` at PostgreSQL directly or run the pooler in session mode until this issue is fixed. Because that workaround is already documented, this does not need to block the Oct 15 Artifact Registry self-managed beta launch — but it does need a real fix before GA, since telling HA customers to avoid transaction-mode pooling isn't sustainable long-term. 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. ## Change The `endpoint` database source gains an optional pooler address (`pool_host`, `pool_port`), mirroring `poolHost` and `poolPort` in the Fairway infrastructure contract. Self-managed installs use the `endpoint` source, so pooler support for general availability is complete with this issue; the `infrastructure` source maps the contract's `poolHost` and `poolPort` in https://gitlab.com/gitlab-org/gitlab/-/work_items/631841. The service client connects to the pooler address when it is set and recycles connections through `ConnMaxLifetime` and `ConnMaxIdleTime`, as the LabKit postgres package documents for PgBouncer. It keeps pgx's `CacheStatement` mode: PgBouncer 1.21 and later track prepared statements in transaction mode when `max_prepared_statements` is above zero. The spike https://gitlab.com/gitlab-org/gitlab/-/work_items/632288 verified this on a CloudNativePG Pooler with PgBouncer 1.25.1 and `max_prepared_statements = 512`, the size of pgx's statement cache; PgBouncer's parse counters showed the statements were tracked. The `endpoint` source on the reference branch of https://gitlab.com/gitlab-org/auth/iam/-/merge_requests/803 (commit 35ac71cb) carries `pool_host` and `pool_port`, and the migration connection string ignores them. The migration client always connects to the database address with one connection, so the advisory lock works. 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. The `endpoint` mapping from https://gitlab.com/gitlab-org/gitlab/-/work_items/611098 merged in https://gitlab.com/gitlab-org/auth/iam/-/merge_requests/738. The fix needs only the `endpoint` fields and the direct migration connection string. The LabKit client in https://gitlab.com/gitlab-org/gitlab/-/work_items/632292 is separate work that adds the connection recycling described above and a span per query. The simple query protocol (https://gitlab.com/gitlab-org/gitlab/-/work_items/632293) is not needed for this path; it matters for the Omnibus PgBouncer defaults and for PgBouncer versions before 1.21. The dedicated migration Job (https://gitlab.com/gitlab-org/gitlab/-/work_items/602574) is not required for this and remains planned: it removes the startup-time ceiling, 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. ## YSQL Connection Manager YSQL Connection Manager (YCM) is YugabyteDB's built-in transaction-mode pooler. It is off on the Aeon universes. It runs inside every tserver behind the PSC endpoint, so it is cluster-wide and migrations can't bypass it. They don't need to. Since the fix for [yugabyte-db issue 27249](https://github.com/yugabyte/yugabyte-db/issues/27249) (YB 2025.1 and later), a session that takes a session-level advisory lock is sticky: YCM pins that client connection to one backend until the client disconnects. goose runs lock, migrations and unlock on one connection, so neither failure mode above applies. The fix in the Change section works unchanged on YB: declare only host and port, with no pool address. Enabling YCM is therefore a capacity decision, not a correctness one. A separate issue is to be filed for it. The production Aeon universe runs v2025.2.5.2-b5, which includes the fix. ## 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. Repeat the YugabyteDB run with YCM enabled in the local Docker Compose stack (`enable_ysql_conn_mgr=true`). Add the PgBouncer to the compose stack and a CI job that runs the data-access integration suite and two concurrent `migrate` runs through it, on PostgreSQL. Check that connections are recycled on the configured lifetime and idle time. Update gap 8 in https://gitlab.com/gitlab-org/ops/artifact-registry/-/blob/main/docs/dev/self-managed-limitations.md, and state in the install guide that a transaction-mode pooler needs PgBouncer 1.21 or later with `max_prepared_statements` above zero. ## 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