Ecto config error with pg_hba.conf with NO local postgres running

Elixir 1.12.1

Phoenix v1.5.4

There is no local postgres running / no local pg_hba.conf

Here is ecto config

config :myapp, Myapp.Repo,
  adapter: Ecto.Adapters.Postgres,
  url: "postgres://user:pwd@pg_instance.elephantsql.com/db",
  pool_size: 10

mix ecto.create is throwing this error.

** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "router_public_ip", user "user", database "postgres", SSL off

Issues

  • It’s for some reason reading router_public_ip and not ip of pg_instance.elephantsql.com. Setting IP manually in config doesn’t help.
  • It’s reading the user correctly. But database is “postgres” instead of “db” (as mentioned in url)

What can be done?

pg_hba.conf can be set up to restrict clients by IP, that’s why PG is checking for router_public_ip.

You might need to set ssl: true in your repo configuration. Not sure why it might be trying to connect to the wrong database. Do you have an init function in your MyApp.Repo module?

There is no PostgreSQL installed locally and hence there is no pg_hba.conf

I’ve tried setting ssl:true and it doesn’t wotk

Can you post your Myapp.Repo.init/2 callback code? This looks a little bit like maybe your init function is overwriting the URL (or some other part of the configuration).

You might also try decomposing the URL:

config :myapp, Myapp.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "db",
  user: "user",
  password: "pw",
  hostname: "pg_instance.elephantsql.com"
defmodule Myapp.Repo do
  use Ecto.Repo,
    otp_app: :myapp,
    adapter: Ecto.Adapters.Postgres
end

There’s no init callback explicitly defined.

Tried. Doesn’t work

I think, the issue is something else, but wrong error is being thrown. Is there a way to enable more verbose logging?

Can you share the error related to this version?

12:05:10.245 [error] GenServer #PID<0.247.0> terminating
** (Postgrex.Error) FATAL 28000 (invalid_authorization_specification) no pg_hba.conf entry for host "router_public_ip", user "user", database "postgres", SSL off
    (db_connection 2.4.0) lib/db_connection/connection.ex:100: DBConnection.Connection.connect/2
    (connection 1.1.0) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.10) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol

Where is the DB hosted? Just a guess, but if you have access, check the postgres configuration there.

router_public_ip looks as if it’s not set up in the remote pg_hba.conf which is generating this connection error. Check if there is an entry in pg_hba.conf for the upstream router’s IP (NATed?) and/or if the router is passing router_public_ip as a hostname etc.

1 Like