Precreating postgres databases in an managed env

[MTRNord]

Hi,

I am selfhosting on a kubernetes. My Postgres is managed by the cluster so the createdb task has no permission to access the postgres db (by design since aplications should have the least amount of possible permission). It is not clear to me which Databses have to exist. Seemingly the one in the connection string is not the one used since I get this error on startup with createdb which I assumed to just be a noop in my case:

17:06:35.900 [error] Postgrex.Protocol (#PID<0.186.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) pg_hba.conf rejects connection for host "100.96.3.252", user "plausible", database "postgres", no encryption
** (MatchError) no match of right hand side value: {:error, "killed"}
(plausible 0.0.1) lib/plausible_release.ex:40: anonymous fn/2 in Plausible.Release.createdb/0
(elixir 1.14.3) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
(plausible 0.0.1) lib/plausible_release.ex:39: Plausible.Release.createdb/0
nofile:1: (file)
(stdlib 4.2) erl_eval.erl:748: :erl_eval.do_apply/7

Is there any documentation on which databases I need to give it or someone can tell me? :) If thats not an option I guess I will use other software sadly as giving it access to create databases is not an option for me for security reasons.

[MTRNord]

Actually this may be a cause of FATAL 28000 (invalid_authorization_specification) pg_hba.conf rejects connection for host "100.96.3.252", user "plausible", database "plausible", no encryption meaning it is using some unusual way of auth

[MTRNord]

Unusual means not md5 here it seems

[MTRNord]

Ok turns out I was totally mislead by the error. Its actually just a missing ?ssl=true

[ruslandoga]

👋 @MTRNord

Seemingly the one in the connection string is not the one used since I get this error on startup with createdb

We can't connect to the database-to-be-created before it is created, so the default "postgres" one is used in this very first step.

Is there any documentation on which databases I need to give it or someone can tell me?

In PostgreSQL, only DATABASE_URL is used when the app runs.

Its actually just a missing ?sslmode=require

This query string would be ignored in DATABASE_URL, it needs to be something like ?ssl=true

For more info about that, cacerts, and FATAL 28000 please see #3849