Log the select version() when connecting to the database

[steve-chavez]

Problem

Newcomers can connect to the wrong database when trying PostgREST. For example, on https://matrix.to/#/!YGChDzXeYxtlBZqVsc:gitter.im/$BZUIQtjG5xGvLKxaF_rtyp0cAKXq8ln2Rha66KtFtrc?via=gitter.im&via=matrix.org&via=matrix.freyachat.eu, the user created the API roles on a db and connected to other db (1):

18/Jan/2024:17:31:43 +0800: Starting PostgREST 12.0.2...
18/Jan/2024:17:31:43 +0800: Attempting to connect to the database...
18/Jan/2024:17:31:43 +0800: {"code":"PGRST000","details":"connection to server at "[localhost](http://localhost/)" (::1), port 5432 
failed: FATAL:  role "authenticator" does not exist\n","hint":null,"message":"Database connection error. Retrying the connection."}

There have been other cases where the user created tables on a db and connected to another one too; resulting in 404s when doing curl /table (2) and leading to thinking PostgREST has an outdated schema cache.

Solution

Log the output of select version() when connecting to a db. For example, on PopOS this shows:

select version();
                                                                version                                                                 
----------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

This will show the OS so users will know they're connecting to their native pg or a dockerized one.

Would help with (2) but not with (1).

---

For (1) maybe we can fallback to connecting with the postgres role, die and log the version()?

[steve-chavez]

If we used the standard psql ini file (#3101 (comment)), users wouldn't have this problem as they could check the connection on psql like:

$ PGSERVICE=paulservice PGSERVICEFILE=pg_service.ini psql

And use the same config for postgrest:

$ PGSERVICE=paulservice PGSERVICEFILE=pg_service.ini postgrest

[wolfgangwalther]

as they could check the connection on psql like:

And use the same config for postgrest:

This should already be possible, since this file is supported via libpq.

[steve-chavez]

Leaving the INI file enhancement for #3101.

Will just log the version here. It'll look like:

09/Feb/2024:16:46:50 -0500: Successfully connected to PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.3.0, 64-bit

Definitely an improvement over just Connection Successful.