SSL issues for self hosting with digitalocean's managed postgres

[xiaohanyu]

Hello,

First, thanks very much for this awesome project!

I tried to self host plausible CE on my local and the official docker-compose.yml works like a charm.

However, the official docker-compose.yml use local postgres container, which may be good for dev use but lack of critital features for production case like PITR, data backup/restore, etc.

I am curious, is it possible to host plausible CE with managed postgres? In my case, I use DigitalOcean's managed postgres and SSL is mandatory, ref issue.

The error message with DO's managed postgres:

$ docker-compose logs -f plausible
plausible-1  | Loading plausible..
plausible-1  | Starting dependencies..
plausible-1  | Starting repos..
plausible-1  | create Plausible.Repo database if it doesn't exist
plausible-1  | 09:44:43.386 [error] Postgrex.Protocol (#PID<0.167.0>) failed to connect: ** (Postgrex.Error) FATAL 08P01 (protocol_violation) SSL required
plausible-1  | 09:44:43.386 [error] Postgrex.Protocol (#PID<0.168.0>) failed to connect: ** (Postgrex.Error) FATAL 08P01 (protocol_violation) SSL required
plausible-1  | 09:44:43.396 [error] Postgrex.Protocol (#PID<0.186.0>) failed to connect: ** (Postgrex.Error) FATAL 08P01 (protocol_violation) SSL required
plausible-1  | 09:44:43.727 [error] Postgrex.Protocol (#PID<0.190.0>) failed to connect: ** (Postgrex.Error) FATAL 08P01 (protocol_violation) SSL required
plausible-1  | ** (MatchError) no match of right hand side value: {:error, "killed"}
plausible-1  |     (plausible 0.0.1) lib/plausible_release.ex:47: anonymous fn/2 in Plausible.Release.createdb/0
plausible-1  |     (elixir 1.16.0) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
plausible-1  |     (plausible 0.0.1) lib/plausible_release.ex:46: Plausible.Release.createdb/0
plausible-1  |     nofile:1: (file)
plausible-1  |     (stdlib 5.2) erl_eval.erl:750: :erl_eval.do_apply/7
plausible-1 exited with code 1
plausible-1  | Loading plausible..
plausible-1  | Starting dependencies..
plausible-1  | Starting repos..
plausible-1  | create Plausible.Repo database if it doesn't exist
plausible-1  | 09:44:54.749 [error] Postgrex.Protocol (#PID<0.168.0>) failed to connect: ** (Postgrex.Error) FATAL 08P01 (protocol_violation) SSL required
plausible-1  | 09:44:54.749 [error] Postgrex.Protocol (#PID<0.167.0>) failed to connect: ** (Postgrex.Error) FATAL 08P01 (protocol_violation) SSL required
plausible-1  | 09:44:54.767 [error] Postgrex.Protocol (#PID<0.186.0>) failed to connect: ** (Postgrex.Error) FATAL 08P01 (protocol_violation) SSL required
plausible-1  | 09:44:55.133 [error] Postgrex.Protocol (#PID<0.190.0>) failed to connect: ** (Postgrex.Error) FATAL 08P01 (protocol_violation) SSL required
plausible-1  | ** (MatchError) no match of right hand side value: {:error, "killed"}
plausible-1  |     (plausible 0.0.1) lib/plausible_release.ex:47: anonymous fn/2 in Plausible.Release.createdb/0
plausible-1  |     (elixir 1.16.0) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
plausible-1  |     (plausible 0.0.1) lib/plausible_release.ex:46: Plausible.Release.createdb/0
plausible-1  |     nofile:1: (file)
plausible-1  |     (stdlib 5.2) erl_eval.erl:750: :erl_eval.do_apply/7

DigitalOcean managed postgres provides their own certificates for managed postgres, for Node.js apps, you can simply set an environment variable NODE_EXTRA_CA_CERTS to make it points to the path of the download ca certificates, and then Node.js apps could connect DO's managed postgres with ssl with no warnings.

From what I can see here:

1. Elixir by default seems having no such thing of NODE_EXTRA_CA_CERTS so you can't just provide extra ca certificates by setting an environment variable (which is non-invasive for the app), ref post
2. @ukutaht has made some trial for postgres ssl but I was not able to find the doc for this and the commit is long time ago.

Thanks very much again for your help!

[ruslandoga]

👋 @xiaohanyu

I am curious, is it possible to host plausible CE with managed postgres?

Yes, it is possible. You can mount the cacertfile as a volume and provide the path to it via DATABASE_CACERTFILE

[ruslandoga]

Note that your DATABASE_URL would need to end with something like ?ssl=true

[xiaohanyu]

@ruslandoga thanks for your help, I tried to set DATABASE_CACERTFILE as you mentioned and make it points to the ca file provided by DO, however, still got following errors, check log (password changed to avoid leaking)

I tried both sslmode=require and sslmode=true, just FYI.

~ $ env | grep DATABASE
DATABASE_URL=postgresql://doadmin:test-password@private-ppresume-do-user-14181994-0.c.db.ondigitalocean.com:25060/plausible?sslmode=require
CLICKHOUSE_DATABASE_URL=http://clickhouse:8123/plausible_events_db
DATABASE_CACERTFILE=/app/docker/digitalocean/ca-certificate.crt
~ $ /entrypoint.sh db createdb
Loading plausible..
Starting dependencies..
Starting repos..
create Plausible.Repo database if it doesn't exist
08:11:43.634 [error] Postgrex.Protocol (#PID<0.168.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "10.124.0.3", user "doadmin", database "plausible", no encryption
08:11:43.634 [error] Postgrex.Protocol (#PID<0.167.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "10.124.0.3", user "doadmin", database "plausible", no encryption
08:11:43.695 [error] Postgrex.Protocol (#PID<0.186.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "10.124.0.3", user "doadmin", database "postgres", no encryption
08:11:43.712 [error] Postgrex.Protocol (#PID<0.190.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "10.124.0.3", user "doadmin", database "postgres", no encryption
** (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
~ $ ls -l $DATABASE_CACERTFILE
-rw-rw-r--    1 plausibl plausibl      1537 Feb  1 15:00 /app/docker/digitalocean/ca-certificate.crt

~ $ export DATABASE_URL=postgresql://doadmin:test-password@private-ppresume-do-user-14181994-0.c.db.ondigitalocean.com:25060/plausible?sslmode=true
~ $ /entrypoint.sh db createdb
Loading plausible..
Starting dependencies..
Starting repos..
create Plausible.Repo database if it doesn't exist
08:12:11.955 [error] Postgrex.Protocol (#PID<0.168.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "10.124.0.3", user "doadmin", database "plausible", no encryption
08:12:11.955 [error] Postgrex.Protocol (#PID<0.167.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "10.124.0.3", user "doadmin", database "plausible", no encryption
08:12:12.003 [error] Postgrex.Protocol (#PID<0.186.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "10.124.0.3", user "doadmin", database "postgres", no encryption
08:12:12.021 [error] Postgrex.Protocol (#PID<0.190.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "10.124.0.3", user "doadmin", database "postgres", no encryption
** (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

[ruslandoga]

The database URL should end with ssl=true, it's an Ecto thing.

[ruslandoga]

Please make sure your app IP address is allowed to connect to the database.

It seems like you need to create the database through DigitalOcean UI and skip the createdb step.

_{docker-compose.yml}

  plausible:
    image: plausible/analytics:v2.0
-   command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh run"
+   command: sh -c "sleep 10 && /entrypoint.sh db migrate && /entrypoint.sh run"

[xiaohanyu]

Thanks for your help, both of your tips (ssl=true thing and getting rid of /entrypoint.sh db createdb) is necessary for this to work.

However, when working with DO's managed postgres, it takes extremely long time to finish the migration (in my case, more than 5 minutes), reason unknown, just FYI.

~ $ date
Fri Mar  1 11:29:40 UTC 2024
~ $ time /entrypoint.sh db migrate
Loading plausible..
Starting dependencies..
Starting repos..
Running migrations for Elixir.Plausible.Repo
Running migrations for Elixir.Plausible.IngestRepo
Migrations successful!
real    5m 18.36s
user    0m 5.40s
sys     0m 1.80s
~ $ date
Fri Mar  1 11:35:24 UTC 2024
~ $ env | grep DATABASE
DATABASE_URL=postgresql://doadmin:test-password@private-ppresume-do-user-14181994-0.c.db.ondigitalocean.com:25060/plausible?ssl=true
CLICKHOUSE_DATABASE_URL=http://clickhouse:8123/plausible_events_db
DATABASE_CACERTFILE=/app/docker/digitalocean/ca-certificate.crt

[xiaohanyu]

Anyway I am gonna drop DO's managed postgres for this case, thanks very much for your support!

[pasztorl]

Hi,

I'm in the same issue.
I created a proc trace and seen this in the migrate log:

y(3) {state,{no_state,#{after_connect=>nil,after_connect_timeout=>15000,backoff=>#{'__struct__'=>'Elixir.DBConnection.Backoff',max=>30000,min=>1000,state=>{2223,10000},type=>rand_exp},client=>closed,connection_listeners=>[],mod=>'Elixir.Postgrex.Protocol',opts=>[{pool_index,1},{types,'Elixir.Postgrex.DefaultTypes'},{repo,'Elixir.Plausible.Repo'},{telemetry_prefix,[plausible,repo]},{otp_app,plausible},{timeout,300000},{connect_timeout,300000},{handshake_timeout,300000},{socket_options,[]},{pool_size,2},{hostname,<<"plausible-pgbouncer">>},{scheme,<<"postgres">>},{username,<<"plausible">>},{password,<<"xxx">>},{database,<<"plausible&ssl=true">>},{port,5432},{pool,'Elixir.DBConnection.ConnectionPool'}],pool=><0.161.0>,state=>nil,tag=>#Ref<0.1584622794.4072538113.9917>,timer=>nil}},[],#{t0q=>[],{timeout,backoff}=>{#Ref<0.1584622794.4072407041.10131>,nil}},false}

the interesting part is that: {database,<<"plausible&ssl=true">>} so the ?ssl=true is attached to the database name, there is a problem parsing the db url?

echo $DATABASE_URL
postgres://plausible:xxx@plausible-pgbouncer:5432/plausible&ssl=true

[Mark24Slides]

Update: after I cahnged path in DATABASE_CACERTFILE and did several restarts, plausible v2.1.0 started to work

[adrian-moisa]

Hi @Mark24Slides. What can you share about the certificate path? I'm facing the same issue. Cheers!

[adrian-moisa]

Turns out my config was ok (following advice from this ticket). I was out of connections, all 22 used apparently. I tried running plausible and clickhouse alone in argo. That worked. Now I have to ugprade my db to next level. Wanted to keep the cost low but looks there's no way around it...

[ruslandoga]

👋 @adrian-moisa

You can tell Plausible to use fewer connections to PostgreSQL via DATABASE_URL env var:

DATABASE_URL=postgres://postgres:postgres@plausible_db:5432/plausible_db?pool_size=10

You can try lowering pool_size option until it works :)

[Mark24Slides]

@adrian-moisaahve do plausible deployment in kubernetes, via helm chart.

tl;dr: put it to the /app/ca-certificate.crt, created DATABASE_CACERTFILE=/app/ca-certificate.crt env varaible.

1. Created secret plausible-ca.yaml:

apiVersion: v1
kind: Secret
metadata:
  name: {{ .Chart.Name }}-ca-certificate
  namespace: {{ .Release.Namespace }}
data:
  ca-certificate.crt: {{ .Values.plausible.caCert | b64enc | quote }}
type: Opaque

Value plausible.caCert is my CA cert.

2. Added this secret to mount in my deployment:

          volumeMounts:
            - mountPath: /app/ca-certificate.crt
              subPath: ca-certificate.crt
              name: ca-certificate
      volumes:
        - name: ca-certificate
          secret:
            secretName: {{ .Chart.Name }}-ca-certificate

3. Added ENV variable (path) in deployment:

          - name: DATABASE_CACERTFILE
            value: /app/ca-certificate.crt