no pg_hba.conf entry for host when connecting to Postgres via Heroku

[pezholio]

Hey,

I'm trying to run Plausaible via Heroku Container Registry. I have it all set up, but the app doesn't seem to connect to the database. I get the following error in my logs:

2021-07-15T12:56:28.183887+00:00 app[web.1]: 12:56:28.182 [error] Postgrex.Protocol (#PID<0.2514.0>) failed to connect: ** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "63.33.64.8", user "xxxxx", database "xxxx", SSL off

(Where xxxxx and xxxx are the username and database name of my Postgres instance)

And then a final failure of:

2021-07-15T12:56:29.850631+00:00 app[web.1]: {"Kernel pid terminated",application_controller,"{application_start_failure,plausible,{{shutdown,{failed_to_start_child,'Elixir.Plausible.Session.Salts',{#{'__exception__' => true,'__struct__' => 'Elixir.DBConnection.ConnectionError',message => <<\"connection not available and request was dropped from queue after 2622ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:\n\n  1. By tracking down slow queries and making sure they are running fast enough\n  2. Increasing the pool_size (albeit it increases resource consumption)\n  3. Allow requests to wait longer by increasing :queue_target and :queue_interval\n\nSee DBConnection.start_link/2 for more information\n\">>,severity => error},[{'Elixir.Ecto.Adapters.SQL',raise_sql_call_error,1,[{file,\"lib/ecto/adapters/sql.ex\"},{line,749}]},{'Elixir.Ecto.Adapters.SQL',execute,5,[{file,\"lib/ecto/adapters/sql.ex\"},{line,682}]},{'Elixir.Plausible.Session.Salts','-start_link/1-fun-0-',0,[{file,\"lib/plausible/session/salts.ex\"},{line,8}]},{'Elixir.Agent.Server',init,1,[{file,\"lib/agent/server.ex\"},{line,8}]},{gen_server,init_it,2,[{file,\"gen_server.erl\"},{line,417}]},{gen_server,init_it,6,[{file,\"gen_server.erl\"},{line,385}]},{proc_lib,init_p_do_apply,3,[{file,\"proc_lib.erl\"},{line,226}]}]}}},{'Elixir.Plausible.Application',start,[normal,[]]}}}"}

It seems as though, by default, the Plausible app doesn't want to connect via SSL, but Heroku requires this. Is there any way round this? I've tried appending ?sslmode=require to the DATABASE_URL but this doesn't seem to work either.

[pezholio]

OK, so I've got over that hurdle now by adding:

config :plausible, Plausible.Repo, url: db_url, ssl: true

to line 186 of config/runtime.exs (which I'll try and craft into a PR with some env config)

However, now I'm hitting an issue when I try to run the migrations:

heroku run db migrate -a my-app

15:14:54.453 [error] GenServer #PID<0.191.0> terminating
** (Postgrex.Error) FATAL 42501 (insufficient_privilege) permission denied for database "postgres"

User does not have CONNECT privilege.
    (db_connection 2.2.2) lib/db_connection/connection.ex:87: DBConnection.Connection.connect/2
    (connection 1.0.4) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.14) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Last message: nil
15:14:54.485 [error] GenServer #PID<0.205.0> terminating
** (Postgrex.Error) FATAL 42501 (insufficient_privilege) permission denied for database "postgres"

User does not have CONNECT privilege.
    (db_connection 2.2.2) lib/db_connection/connection.ex:87: DBConnection.Connection.connect/2
    (connection 1.0.4) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.14) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Last message: nil

Any ideas?