Connection error - Database does not exists after patching to a newer version

[jeanpierre92]

Environment

• PostgreSQL version: (if using docker, specify the image)
• 15.4(container)
• PostgREST version: (if using docker, specify the image)
• 11.2.0 and 11.1.0(container)
• Operating system:

Description of issue

From PostgREST version 11.0.1 the api can connect to the database via the config:

PGRST_SERVER_PORT=2888
PGRST_DB_URI=postgres://forecasting:<PW_HERE>@<HOST_HERE>:5432/forecasting
PGRST_DB_SCHEMA=api
PGRST_DB_ANON_ROLE=forecasting

But after patching to the next version I see errors:

26/Sep/2023:13:14:51 +0200: {"code":"PGRST000","details":"connection to server at "<HOST_HERE>" (10.8.106.143), port 5432 failed: FATAL: database "forecasting fallback_application_name='PostgREST 11.2.0'" does not exist\n","hint":null,"message":"Database connection error. Retrying the connection."}

I see the same errors in the logging in the postgresql database logging. The config has not changed between versions.

[steve-chavez]

Likely caused by:

• Obtain the PostgREST version through SQL #2647
• Add access to the PostgREST version through SQL #2740

@jeanpierre92 Are there special characters in PW_HERE or HOST_HERE?

cc @laurenceisla

[jeanpierre92]

@steve-chavez Yes, in the password there are chars like: "=)[&", the host does not have special chars.

[laurenceisla]

I tested this and the solution should be to change those special characters to percent-encoded characters, e.g. =)[& to %3D%29%5B%26.

I can confirm that percent-encoding was not needed in v11.0.1. PostgREST didn't check if it's a valid URI and sent it directly to the connection library, which maybe sent the URI string as-is to the database.

In v11.2.0, the password MUST be percent encoded, because now a library checks first if the URI is valid (which is not if it has those special characters). Not sure if it should be considered a breaking change, the docs suggested that the password should be percent encoded.

[steve-chavez]

Just to clarify. To add the fallback_application_name, the code is considering that the connection string is not a valid URI format, and then it assumes it's in key-value format. This is what gives the weird error message.

postgrest/src/PostgREST/Config.hs

Lines 499 to 505 in a6e3eda

	addFallbackAppName :: ByteString -> Text -> Text
	addFallbackAppName version dbUri = dbUri <>
	case uriQuery <$> parseURI (toS dbUri) of
	Nothing -> " " <> keyValFmt -- Assume key/value connection string if the uri is not valid
	Just "" -> "?" <> uriFmt
	Just "?" -> uriFmt
	_ -> "&" <> uriFmt

This needs to be fixed somehow, because if previous versions did connect then it was indeed a valid URI. We should aim to behave exactly like psql for the connection string.

---

Something that's weird is that some special non-urlencoded characters do cause an error when connecting, see:

• handling the @ in the connection user #1244

Seems the connection only fails on a non-urlencoded @?

[jeanpierre92]

I can confirm that with v11.2.0 and the percent-encoding password it is fixed and it gives no longer errors. But, just for information, the percent-encoding did not work with v11.0.1 (like you said, its probably send as a string)

[steve-chavez]

To solve this issue, I think we can just identify that the connstring is in URI format by detecting the URI scheme designator:

The URI scheme designator can be either postgresql:// or postgres://.
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS

Also note:

URIs generally follow RFC 3986, except that multi-host connection strings are allowed as further described below.
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

Says "generally follow". While our parseURI is strictly RFC 3986.

---

There's a lib for parsing the pg connstring: https://hackage.haskell.org/package/postgresql-simple-opts. Maybe we can use that one. It already covers the key/value format too.

Edit: the library has errors jfischoff/postgres-options#8

---

The libpq logic for checking if it's a URI it's quite simple:

https://github.com/postgres/postgres/blob/a829b704015104f49a11ea007957ef03a03dc0d4/src/interfaces/libpq/fe-connect.c#L5880-L5921