External Postgres DB connection (with ssl, digitalocean) does not work

[Mark24Slides]

Hi, experience some issues with latest version of Plausible.
Refuses to work with external PostgreSQL DB connection (with ssl) (hosted on DigitalOcean cloud).

Have Plausible v2.0.0 deployed (works normally, with locally installed clickhouse and external postgresql), and today started the upgrade procedures to v2.1.0, did everything mentioned in release v2.1.0, but got several errors/issues with starting new plausible container.

Using DATABASE_URL to pass PG connection string, tried firstly same string as was used for v2.0.0 (checked with echo $DATABASE_URL, all is ok):
DATABASE_URL=postgres:https://$(DB_USER):$(DB_PASSWORD)@$(DB_HOST):$(DB_PORT)/$(DB_NAME)?ssl=true
and got:

13:10:41.090 [error] Postgrex.Protocol (#PID<0.196.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state wait_cert at ssl_handshake.erl:2133 generated CLIENT ALERT: Fatal - Unknown CA
 - {:tls_alert, {:unknown_ca, ~c"TLS client: In state wait_cert at ssl_handshake.erl:2133 generated CLIENT ALERT: Fatal - Unknown CA\n"}}
13:10:41.090 [error] Postgrex.Protocol (#PID<0.168.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state wait_cert at ssl_handshake.erl:2133 generated CLIENT ALERT: Fatal - Unknown CA
 - {:tls_alert, {:unknown_ca, ~c"TLS client: In state wait_cert at ssl_handshake.erl:2133 generated CLIENT ALERT: Fatal - Unknown CA\n"}}
13:10:41.090 [error] Postgrex.Protocol (#PID<0.167.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state wait_cert at ssl_handshake.erl:2133 generated CLIENT ALERT: Fatal - Unknown CA
 - {:tls_alert, {:unknown_ca, ~c"TLS client: In state wait_cert at ssl_handshake.erl:2133 generated CLIENT ALERT: Fatal - Unknown CA\n"}}
13:10:41.379 [error] Postgrex.Protocol (#PID<0.204.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state wait_cert at ssl_handshake.erl:2133 generated CLIENT ALERT: Fatal - Unknown CA
 - {:tls_alert, {:unknown_ca, ~c"TLS client: In state wait_cert at ssl_handshake.erl:2133 generated CLIENT ALERT: Fatal - Unknown CA\n"}}
** (MatchError) no match of right hand side value: {:error, "killed"}
    (plausible 0.0.1) lib/plausible_release.ex:47: anonymous fn/2 in Plausible.Release.createdb/0
    (elixir 1.16.0) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
    (plausible 0.0.1) lib/plausible_release.ex:46: Plausible.Release.createdb/0
    nofile:1: (file)
    (stdlib 5.2) erl_eval.erl:750: :erl_eval.do_apply/7

Then, added sslmode=require:
DATABASE_URL=postgres:https://$(DB_USER):$(DB_PASSWORD)@$(DB_HOST):$(DB_PORT)/$(DB_NAME)?sslmode=require&ssl=true
and got same errors.

Then, tried to add sslmode=verify-ca and /app/ca-cetificate.crt (valid) and
DATABASE_URL=postgres:https://$(DB_USER):$(DB_PASSWORD)@$(DB_HOST):$(DB_PORT)/$(DB_NAME)?sslmode=verify-ca&sslrootcert=/app/ca-certificate.crt&ssl=true
and got same errors.

Tried also several fixes by ChatGPT and Google search, nothing helps, can anyone help with that?

Setup:
Plausible: plausible/community-edition:v2.1.0
Variables:

    DATABASE_URL: postgres:https://$(DB_USER):$(DB_PASSWORD)@$(DB_HOST):$(DB_PORT)/$(DB_NAME)?sslmode=verify-ca&sslrootcert=/app/ca-certificate.crt&ssl=true
    or
    DATABASE_URL: postgres:https://$(DB_USER):$(DB_PASSWORD)@$(DB_HOST):$(DB_PORT)/$(DB_NAME)?sslmode=required&ssl=true
    or
    DATABASE_URL: postgres:https://$(DB_USER):$(DB_PASSWORD)@$(DB_HOST):$(DB_PORT)/$(DB_NAME)?ssl=true
    or
    DATABASE_URL: postgres:https://$(DB_USER):$(DB_PASSWORD)@$(DB_HOST):$(DB_PORT)/$(DB_NAME)
    
    CLICKHOUSE_DATABASE_URL: http:https://$(CLICKHOUSE_USER):$(CLICKHOUSE_PASSWORD)@plausible-events-db:8123/$(CLICKHOUSE_DB)

    DB_USER: plausibledbuser
    DB_HOST: ********
    DB_PORT: ********
    DB_PASSWORD: ********
    DB_NAME: plausible
    
    GOOGLE_CLIENT_ID: ********
    GOOGLE_CLIENT_SECRET: ********
    
    BASE_URL: ********
    SECRET_KEY_BASE: ********
    TOTP_VAULT_KEY: ********
    CLICKHOUSE_PASSWORD: ********
    
    MAILER_ADAPTER: "Bamboo.Mua"
    MAILER_EMAIL: ********
    SMTP_HOST_ADDR: ********
    SMTP_HOST_PORT: ********
    SMTP_USER_NAME: ********
    SMTP_USER_PWD: ********
    SMTP_HOST_SSL_ENABLED: "false"
    
    DISABLE_REGISTRATION: "invite_only"

Clickhouse: clickhouse/clickhouse-server:24.3.3.102-alpine
Variables:

    CLICKHOUSE_PASSWORD: ********

[ruslandoga]

👋 @Mark24Slides

The error says that you need to add custom cacerts from digitalocean. Please see plausible/analytics#3849 (comment) for more details.

[Mark24Slides]

@ruslandoga can you please reopen this issue, as solution from plausible/analytics#3849 (comment) is not helping (plus several of them are already mention upper).

[Mark24Slides]

ssl=true, entrypoint.sh db createdb skip and DATABASE_CACERTFILE=/app/ca-certificate.crt does not work

[ruslandoga]

Sorry, but I don't see DATABASE_CACERTFILE in your post. Could you please try it first?

[ruslandoga]

Make sure to recreate the container for the updated env vars to take effect.

[Mark24Slides]

Added new path for DATABASE_CACERTFILE, and on the second or third restart, stated to work (envs vere not changed, magic)

[Mark24Slides]

@ruslandoga Checked ui and data, looks like v2.1.0 is 100% online