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

[issuefiler]

Past Issues Searched

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

Issue is a Bug Report

• 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

analytics/config/runtime.exs

Lines 483 to 508 in e7e553c

	db_socket_dir? = String.starts_with?(db_host, "%2F") or db_host == ""
	if db_socket_dir? do
	[database] = String.split(db_uri.path, "/", trim: true)
	socket_dir =
	if db_host == "" do
	db_host = (db_uri.query || "") |> URI.decode_query() |> Map.get("host")
	db_host || raise ArgumentError, "DATABASE_URL=#{db_url} doesn't include host info"
	else
	URI.decode_www_form(db_host)
	end
	config :plausible, Plausible.Repo,
	socket_dir: socket_dir,
	database: database
	if userinfo = db_uri.userinfo do
	[username, password] = String.split(userinfo, ":")
	config :plausible, Plausible.Repo,
	username: username,
	password: password
	end
	else
	config :plausible, Plausible.Repo, url: db_url

• 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`