Recognize a missing Postgres database by SQLSTATE - #5312

A Postgres URI that names no database gets reported as a verification error even when the credentials are good.

The detector already treats "database does not exist" as proof of a working credential, since the server only looks the database up once authentication has passed. The check builds that message from a database name, and when the connection string supplies none it assumes the default is postgres. Here is the exchange, credential masked. Note the trailing slash: the string names no database.

  dialled   postgresql://svc_reports:••••••••@db-prod-07.internal:5432/
                                                                      ↑ no database given
  libpq     omits the parameter, so the server fills it in
  server    pq: database "svc_reports" does not exist
                          ↑ the user name, not "postgres"
  check     looks for: database "postgres" does not exist   →   no match

Postgres defaults the database to the user name: port->database_name = pstrdup(port->user_name) in backend_startup.c, under the comment "The database defaults to the user name", matching the StartupMessage description in the protocol documentation. The driver plays no part in it: lib/pq copies the options it was given into the startup packet and leaves an absent database absent, with no default of its own anywhere in the package.

Ordering is what makes the reply safe to read as success. PerformAuthentication runs before the lookup that raises database "%s" does not exist, so reaching that error means the credentials were accepted. That is the same conclusion the existing code states in its own comment.

SQLSTATE settles this without guessing a name. 3D000 is invalid_catalog_name, both drivers expose it, lib/pq by name and pgx on its error type, and it carries the same meaning whatever the database was called. It also holds on a server running a non-English lc_messages, where comparing message text cannot work at all.

Message matching stays as a fallback, since a connection pooler can relay the failure without a SQLSTATE, and it now expects the name the server would have used. That closes a second gap in the same helper: an error naming some other missing database used to satisfy the check and mark the credential verified.

The postgres default dates from #2314 in January 2024 and reached the pgx path through #5217. Anyone scanning connection strings written without an explicit database loses those verifications today, which is how this surfaced: a corpus of 170 Postgres credentials where 139 omit the database reported every one of them as unverifiable.