Self-host in Kubernetes: cannot connect to postgres

[digital88]

Hello.

I'm trying to deploy plausible in kubernetes. I have created clickhouse and postgres databases and ensured they accessible from inside the cluster. I'm using this chart to deploy plausible: https://artifacthub.io/packages/helm/imio/plausible-analytics

In values.yaml I disabled db deployment from chart, because I already have them deployed, and just set connection strings:

databaseURL: "postgres://plausible_db:<password-redacted>@<mydbhost>:6432/plausible_db"
clickhouseDatabaseURL: "https://plausible_events:<password-redacted>@<mydbhost>:9440/plausible_events"

I installed chart but keep getting postgres connection errors:

2023-11-21T10:58:16.646944379Z stderr F + /entrypoint.sh db createdb
2023-11-21T10:58:17.093704258Z stdout F Loading plausible..
2023-11-21T10:58:17.101397443Z stdout F Starting dependencies..
2023-11-21T10:58:17.181481211Z stdout F Starting repos..
2023-11-21T10:58:17.249409059Z stdout F create Plausible.Repo database if it doesn't exist
2023-11-21T10:58:17.343639773Z stdout F 10:58:17.335 [error] Postgrex.Protocol (#PID<0.182.0>) failed to connect: ** (Postgrex.Error) ERROR 3D000 (invalid_catalog_name) odyssey: ca64af90ed93c: route for 'postgres.plausible_db' is not found
2023-11-21T10:58:17.373190818Z stdout F 10:58:17.372 [error] Postgrex.Protocol (#PID<0.188.0>) failed to connect: ** (Postgrex.Error) ERROR 3D000 (invalid_catalog_name) odyssey: c33e14b2a5f7f: route for 'postgres.plausible_db' is not found
2023-11-21T10:58:17.428772812Z stderr F ** (MatchError) no match of right hand side value: {:error, "killed"}
2023-11-21T10:58:17.42881728Z stderr F     (plausible 0.0.1) lib/plausible_release.ex:40: anonymous fn/2 in Plausible.Release.createdb/0
2023-11-21T10:58:17.428824481Z stderr F     (elixir 1.14.3) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
2023-11-21T10:58:17.42882921Z stderr F     (plausible 0.0.1) lib/plausible_release.ex:39: Plausible.Release.createdb/0
2023-11-21T10:58:17.428834443Z stderr F     nofile:1: (file)
2023-11-21T10:58:17.428839191Z stderr F     (stdlib 4.2) erl_eval.erl:748: :erl_eval.do_apply/7

Plausible seems to be connecting to database named postgres instead of plausible_db, as seen in log 'postgres.plausible_db' is not found. I dont understand why. Can somebody please take a look?

[ruslandoga]

👋 @digital88

Note that postgres is the username to connect as, not the database name.

postgres://<username>:<password>@<host>:<port>/<db>

[ruslandoga]

Seems like it might have been a permission error all along :)

[digital88]

Yes, I will try to fix it, then try re-run 'Ecto.Migrator.with_repo(Plausible.Repo, &Ecto.Migrator.run(&1, :up, all: true)) |> IO.inspect'
Thanks for great help!

[ruslandoga]

Here's what I think happened:

• the provider created the database "out-of-band" so createdb actually became unnecessary, and during createdb postgrex was trying to connect to the default database (before creating plausible_db) which the provider forbade
• migratedb "works" because the database actually already exists and some migrations run successfully, but creating extensions fails because the provider forbids them, so you'd probably need to enable them through the providers interface, whatever it is

[ruslandoga]

You don't need to run these manual commands anymore, just remove /entrypoint db createdb from the startup command and it should work (once extensions are enabled).

[digital88]

Yes, I enabled extension citext using cloud provider UI. After that postgres migration completed successfully. Now I need to fix Ch CA cert.

[digital88]

Is there a way to point plausible to custom CA cert for clickhouse connection? I have .crt file on disk inside image. For example this is how it is done in java:

public static void main(String[] args) {
        String DB_HOST    = "<hostname>";
        String DB_NAME   = "plausible_events";
        String DB_USER    = "plausible_events";
        String DB_PASS     = "<password>";

        String CACERT     = "/usr/local/share/ca-certificates/InternalRootCA.crt";

        String DB_URL = String.format("jdbc:clickhouse://%s:8443/%s?ssl=1&sslmode=strict&sslrootcert=%s", DB_HOST, DB_NAME, CACERT);

        try {
            Class.forName("com.clickhouse.jdbc.ClickHouseDriver");
            Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
            ResultSet rs = conn.createStatement().executeQuery("SELECT version()");
            if(rs.next()) {System.out.println(rs.getString(1));}

            conn.close();
        }
        catch(Exception ex) {ex.printStackTrace();}
    }

[ruslandoga]

Oof, can I take latest tag and build image then? Is it stable?

I don't know, I haven't tried it yet. Currently I'm running v2.0.0 ...

I think it should be stable since it's used in Plausible Cloud, but there could be some additional configs / migrations required.

[ruslandoga]

Or maybe I could run some sort of bin/plausible eval '<here code to set CA cert>' ?

This could work if you run something to override runtime.exs

[digital88]

Ok, understood, will try overriding runtime.exs

[ruslandoga]

If you decide to mount a custom runtime.exs, I think this patch is all that's needed wrt to [email protected]

--- runtime.exs
+++ runtime_custom.exs
@@ -302,6 +302,15 @@
   inet6: maybe_ch_ipv6
 ]

+ch_cacertfile = get_var_from_path_or_env(config_dir, "CLICKHOUSE_CACERTFILE")
+
+ch_transport_opts =
+  if ch_cacertfile do
+    ch_transport_opts ++ [cacertfile: ch_cacertfile]
+  else
+    ch_transport_opts
+  end
+
 config :plausible, Plausible.ClickhouseRepo,
   loggers: [Ecto.LogEntry],
   queue_target: 500,

[digital88]

Yes! It works now. Thanks again for you help!

[adrien-barret]

@digital88 any chance that you can share you k8s deployment file ?

[digital88]

I can't share unfortunately, but I can recommend this chart as starting point https://github.com/imio/helm-plausible-analytics/tree/0.2.2