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

[dev-shah7]

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:
https://github.com/plausible/analytics/blob/50eef62c3fc96eea1278d1d6fa3080393a45480b/config/runtime.exs#L470-L478

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