Postgres connection rejected with sslMode=full in DATABASE_URL, and empty DATABASE_CACERTFILE

[thomastvedt]

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'm supplying a DATABASE_URL=postgres://username:[email protected]:5432/prod_plausible?sslmode=full to my postgres server which indicates that SSL should be used (sslmode=full). However the connection is blocked by postgres, reason: encryption. The only way to get it to work is to include the DATABASE_CACERTFILE=/app/DigiCert-Global-Root-CA.pem environment variable and copy over root certificate used to sign the postgresql instance. I don't want to do this, since Azure has changed the root CA certificate in the past for postgresql, and I don't want to update these manually.

I looked at the code and notice that if DATABASE_CACERTFILE is set, a ssl flag is set here:

analytics/config/runtime.exs

Lines 470 to 478 in 50eef62

	else
	config :plausible, Plausible.Repo,
	url: db_url,
	socket_options: db_maybe_ipv6
	if db_cacertfile do
	config :plausible, Plausible.Repo, ssl: [cacertfile: db_cacertfile]
	end
	end

I think that this ssl flag should probably be set if the DATABASE_CACERTFILE is NOT supplied, but the DATABASE_URL has ssl in the connection string?

I am however not familiar with the plausible source code or the language used here.

Expected behavior

• When the DATABASE_URL postgres connectionstring indicates that SSL should be used, I expect it to be used
• I expect plausible to be able to verify the full certificate chain without supplying root certificates manually

Screenshots

No response

Environment

- OS: MacOs
- Browser: Arc
- Plausible community edition, ghcr.io/plausible/community-edition:v2.1.4
- Running in Kubernetes, custom helm chart and custom Dockerfile

[ruslandoga]

👋 @thomastvedt

Thank you for the report!

This issue has come up a few times in the past, so I guess it makes sense to support at least some PostgreSQL-specific query params. Like sslmode. I'll try to open a PR with this change in the next few days.

In the meantime, please see #4904 for the explanation of the current behavior and available workarounds.