PostgreSQL query-string connection parameters in `DATABASE_URL` are not respected when using a Unix domain socket.

[issuefiler]

Past Issues Searched

• [x] I have searched open and closed issues to make sure that the bug has not yet been reported

Issue is a Bug Report

• [x] This is a bug report and not a feature request, nor asking for self-hosted support

Using official Plausible Cloud hosting or self-hosting?

Self-hosting

Describe the bug

I’ve had a hard time troubleshooting this. The current logic for parsing DATABASE_URL, the postgres(ql):// connection URL, is incomplete.

Currently,

• PostgreSQL query-string connection parameters in DATABASE_URL, ?…=…&…=…, are not respected when using a Unix domain socket.
• You cannot have Plausible use a Unix domain socket file other than .s.PGSQL.5432.

The current URL parsing logic

https://github.com/plausible/analytics/blob/e7e553cf9d27991c640bc52ef797297435aac0a2/config/runtime.exs#L483-L508

• If the host part of the URL begins with %2F (uppercase) or is empty:
  • It takes the socket_dir from the host part or the host query-string parameter.
  • CAVEAT:
    • Other query-string parameters, including application_name, search_path, et cetera, are ignored. Currently, I’m specifying postgresql://user:password@%2FPostgreSQL%2Fsocket%2F/database?application_name=Plausible for my DATABASE_URL, but the application_name parameter doesn’t make it into the PostgreSQL server log.
    • The port, either in the port part (:…) or in the query-string, is ignored. It should not, because port determines the suffix of the socket filename. For example, port = 1 in postgresql.conf creates the Unix domain socket .s.PGSQL.1, not .s.PGSQL.5432, the default. I had to comment out port = 1 in my postgresql.conf for this reason.
• Otherwise:
  • The full URL gets passed (config :plausible, Plausible.Repo, url: db_url) to the underlying driver, Postgrex. The query-string parameters would be respected.
  • CAVEAT:
    • Postgrex does not like %2f/%2F in the host part, emitting the error non-existing domain - :nxdomain. It means you cannot specify a Unix domain socket for the host.
    • The clever trickery of having the host query-string parameter override the host part of the URL, e.g. postgresql://user:password@fakehost/database?host=%2FPostgreSQL%2Fsocket%2F, could not establish the connection, either.

Expected behavior

• PostgreSQL query-string connection parameters in DATABASE_URL should be respected in any case.
• It should allow specifying port for the Unix domain socket filename suffix.

Screenshots

No response

Environment

The Docker image `ghcr.io/plausible/community-edition:v3.0.1`