pg.ddx.io  pgdogdev/pgdog GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgdogdev/pgdog] issue #1798: Passthrough auth: a fresh PgDog rejects first logins with "connection pool ... is down"
2+ messages / 2 participants
[nested] [flat]

* [pgdogdev/pgdog] issue #1798: Passthrough auth: a fresh PgDog rejects first logins with "connection pool ... is down"
@ 2026-10-13 13:59 "jcarreiro (@jcarreiro)" <noreply+jcarreiro@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: jcarreiro (@jcarreiro) @ 2026-10-13 13:59 UTC (permalink / raw)
  To: pgdogdev/pgdog <noreply+pgdogdev-pgdog@github.com>

**PgDog version**

`main` at `381adefc`, after v0.1.61. We first saw this in production on v0.1.52.

**Description**

With `passthrough_auth` and no passwords in `users.toml`, a freshly started PgDog rejects some of its first clients:

```
connection pool for user "pgdog" and database "db1" is down
```

Postgres stays healthy throughout. We hit this on every rolling restart of pgdog, with four databases for one user. Two bugs combine:

1. **Racing logins each rebuild every pool.** `passthrough_auth::check_or_add()` reads the config, awaits `check_db()`, then stores the password and reloads. Concurrent first logins for the same user and database all read the config before the first one stores the password. They share one `check_db()` validation, then each one rebuilds every pool in turn.

2. **One retry isn't enough.** Each rebuild takes the previous pool set offline. A client that fetched its cluster just before a rebuild fails its parameter checkout with `AllReplicasDown`. `Connection::parameters()` waits for the reload and retries once, so a second rebuild inside that window fails the client.

Bug 2 happens even without bug 1. Four databases mean at least four rebuilds at startup, one per database's first login, and they land milliseconds apart.

#1760 doesn't fix either bug. Its validation dedup makes bug 1 worse: logins queue behind the shared validation, then all of them reload when it completes. In the burst test below, failed logins went from 16 on `5b68a7c8`, before #1760, to 379 on `381adefc`.

**Reproduction**

Postgres 17 on 127.0.0.1:55432, with role, password and database all `pgdog` and password authentication enabled. `trust` auth would pass every validation.

`pgdog.toml`:

```toml
[general]
log_level = "info,pgdog::backend::passthrough_auth=debug"
host = "127.0.0.1"
port = 6433
workers = 4
passthrough_auth = "enabled_plain_allow_change"
pooler_mode = "transaction"
default_pool_size = 30
min_pool_size = 5

[[databases]]
name = "db1"
host = "127.0.0.1"
port = 55432
database_name = "pgdog"

# The same for db2, db3 and db4.
```

`users.toml` lists user `pgdog` once for each database, with no password:

```toml
[[users]]
name = "pgdog"
database = "db1"

# The same for db2, db3 and db4.
```

Start PgDog. As soon as the port accepts connections, open 40 concurrent logins per database:

```sh
export PGPASSWORD=pgdog
for db in db1 db2 db3 db4; do
  seq 40 | xargs -P 40 -I{} psql -h 127.0.0.1 -p 6433 -U pgdog -d "$db" -Atc 'select 1' &
done
wait
```

Every one of 8 fresh starts rejected clients, 379 in total. A pgbench variant, 25 clients per database with `-t 5`, rejected clients in 10 of 10 starts.

**Logs**

PgDog logs `adding user ... via passthrough auth` once per password store, and each store triggers a full rebuild. Four databases need four. On `main`, one start logged 12, then rejected clients. Excerpts keep the time, level and message, and drop the `password not set` warnings:

```
13:33:38.061892Z DEBUG adding user "pgdog" to database "db1" via passthrough auth
13:33:38.061901Z DEBUG adding user "pgdog" to database "db3" via passthrough auth
13:33:38.061927Z DEBUG adding user "pgdog" to database "db2" via passthrough auth
13:33:38.061895Z DEBUG adding user "pgdog" to database "db4" via passthrough auth
13:33:38.064922Z DEBUG adding user "pgdog" to database "db4" via passthrough auth
13:33:38.065137Z DEBUG adding user "pgdog" to database "db3" via passthrough auth
13:33:38.065787Z DEBUG adding user "pgdog" to database "db1" via passthrough auth
13:33:38.066208Z DEBUG adding user "pgdog" to database "db4" via passthrough auth
13:33:38.067611Z DEBUG adding user "pgdog" to database "db4" via passthrough auth
13:33:38.069538Z DEBUG adding user "pgdog" to database "db1" via passthrough auth
13:33:38.069692Z DEBUG adding user "pgdog" to database "db4" via passthrough auth
13:33:38.070514Z ERROR aborting new client connection, connection pool is down
13:33:38.070381Z ERROR aborting new client connection, connection pool is down
13:33:38.071247Z ERROR aborting new client connection, connection pool is down
13:33:38.071119Z DEBUG adding user "pgdog" to database "db1" via passthrough auth
13:33:38.071830Z ERROR aborting new client connection, connection pool is down
...
```

Three starts on `main` logged 12, 9 and 7 stores. With the fix below, every start logs exactly 4 and rejects no clients:

```
13:33:55.205501Z DEBUG adding user "pgdog" to database "db1" via passthrough auth
13:33:55.219973Z DEBUG adding user "pgdog" to database "db2" via passthrough auth
13:33:55.586347Z DEBUG adding user "pgdog" to database "db3" via passthrough auth
13:33:55.587735Z DEBUG adding user "pgdog" to database "db4" via passthrough auth
```

The gap before `db3` comes from the test harness starting 160 `psql` processes. `main` shows the same gap.

Production showed the same pattern on v0.1.52. On one pod, the first rebuild came 7.5 s after boot. About 8 more rebuilds followed within 4 ms, where 3 would do. Five `connection pool is down` errors landed in the same 4 ms, each a fraction of a millisecond after a rebuild.

**Configuration**

The `pgdog.toml` and `users.toml` above. Production uses the same `passthrough_auth` mode, with one user across four databases.

**Proposed fix**

Branch: [jcarreiro/pgdog `fix/passthrough-auth-rebuild-race`](https://github.com/jcarreiro/pgdog/tree/fix/passthrough-auth-rebuild-race) ([diff against `main`](https://github.com/pgdogdev/pgdog/compare/main...jcarreiro:pgdog:fix/passthrough-auth-rebuild-race)). It has two commits, each with a test:

1. **Rebuild once per password.** A new `store()` takes the databases lock, re-reads the config, and reloads while still holding the lock. Logins that race with the same password wait for the first one, find the password stored, and return without a rebuild. `restore_after_reload()` uses the same path. Test: `backend::passthrough_auth::test::concurrent_first_logins_rebuild_pools_once`.

2. **Retry while reloads replace the pools.** `replace_databases()` bumps a new `databases::generation()` counter each time it swaps in a pool set. `Connection::parameters()` still retries once. After that, it keeps retrying only while a reload replaced the pools the failed attempt used. A failure on pools no reload replaced returns as before. Each extra retry needs another completed reload, so the loop ends. Test: `backend::pool::connection::test::parameters_survive_back_to_back_reloads`.

Each test fails on `main` and passes with its commit:

| Test                                         | `main`                              | With the fix  |
|----------------------------------------------|-------------------------------------|---------------|
| `concurrent_first_logins_rebuild_pools_once` | Fails 5 of 5                        | Passes 5 of 5 |
| `parameters_survive_back_to_back_reloads`    | Fails 3 of 3, 391–420 failed logins | Passes 5 of 5 |

The 17 existing `passthrough_auth` tests pass. End to end:

| Build         | Burst: failed logins, 8 starts | pgbench: starts with rejected clients, of 10 |
|---------------|--------------------------------|----------------------------------------------|
| `main`        | 379                            | 10                                           |
| Commit 1 only | 63                             | 7                                            |
| Both commits  | 0                              | 0                                            |

One behavior changes. Once a password passes validation, `store()` decides against the current config, not the config read before validation. Suppose two logins race with different passwords that both validate, in a mode that disallows password changes. Today the later login overwrites the first one's password. With the fix, the later login gets `NoPassthroughPasswordChange`, the same result it would get arriving a moment later.

**Not addressed**

- A new password for one database still rebuilds every pool and takes all of them offline. Rebuilding only the affected cluster would remove the root cause, but it changes how `Databases` gets swapped.

- Queries can still fail during a rebuild. The burst test also logs `pool is shut down` errors, on `main` and with the fix alike. Those clients log in, then lose their pool mid-query: `cluster_check()` reloads an offline cluster, but a rebuild can land between that check and the checkout. We haven't seen this in production. It shares the root cause above.

- `store()` holds a `parking_lot` mutex on a Tokio worker through the rebuild. The code before it took the same lock for the config write, then again for the reload, so the worker blocks for about as long as before. Login completion times in the burst test match `main`.


^ permalink  raw  [nested|flat] 2+ messages in thread

* Re: [pgdogdev/pgdog] issue #1798: Passthrough auth: a fresh PgDog rejects first logins with "connection pool ... is down"
@ 2026-11-20 10:55 "jglapa (@jglapa)" <noreply+jglapa@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: jglapa (@jglapa) @ 2026-11-20 10:55 UTC (permalink / raw)
  To: pgdogdev/pgdog <noreply+pgdogdev-pgdog@github.com>

We see the same thing

 `passthrough_auth = "enabled_plain_allow_change"`, transaction mode, no `users.toml`. Each of our tenants has its own database and its own Postgres user, so every first login after a pgdog start is a new user/database pair.

In our case it is only the second bug from the report (one retry is not enough). There are no racing logins for the same user here, just several different users logging in for the first time within the same few hundred milliseconds. That is enough.

Example from today, a pod that was one minute old with 6 databases configured. Four tenants connected for the first time within about 150 ms, and two clients were rejected:

10:20:00.106  new connection requested: reason=client ... [user_a@.../db_a]
10:20:00.206  new connection requested: reason=client ... [user_b@.../db_b]
10:20:00.209  ERROR aborting new client connection, connection pool is down
10:20:00.209  new connection requested: reason=client ... [user_c@.../db_c]
10:20:00.249  ERROR aborting new client connection, connection pool is down
10:20:00.249  new connection requested: reason=client ... [user_d@.../db_d]
10:20:00.360  new server connection: ... reason=client [user_a@.../db_a]
10:20:00.360  closing server connection: state=idle, reason=other [user_a@.../db_a]

After about one second everything is stable. The client side gets SQLSTATE 58000.

It happens on every pgdog pod start: rollouts, config changes, node replacements. It gets worse with more tenants, because a scheduled job that touches all tenants right after a restart produces all the first logins at once.

I looked at the v0.1.66 source and it still looks the same to me: `passthrough_auth::add()` goes through `reload_from_config()` and replaces the whole set of pools, and `Connection::parameters()` retries once.

Two things that would help us, either one would probably be enough:
- adding a passthrough user creates only that user's pool instead of rebuilding all of them
- `parameters()` waits for reloads to settle instead of retrying once

We can't put the users in `users.toml`, the credentials live in our application and tenants are added at runtime.

^ permalink  raw  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-20 10:55 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-13 13:59 [pgdogdev/pgdog] issue #1798: Passthrough auth: a fresh PgDog rejects first logins with "connection pool ... is down" "jcarreiro (@jcarreiro)" <noreply+jcarreiro@github.com>
2026-11-20 10:55 Re: [pgdogdev/pgdog] issue #1798: Passthrough auth: a fresh PgDog rejects first logins with "connection pool ... is down" "jglapa (@jglapa)" <noreply+jglapa@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox