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

Questions / Help
1

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?

2

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

3

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?

4

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

5

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).

6

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"
7 
defmodule Myapp.Repo do
  use Ecto.Repo,
    otp_app: :myapp,
    adapter: Ecto.Adapters.Postgres
end

There’s no init callback explicitly defined.

8

Tried. Doesn’t work

9

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

10

Can you share the error related to this version?

11 
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
12

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
13

I ran into this same issue, it an issue that comes from how elephantsql shared resources work.
The solution is to specify the maintenance database, there is such a configuration that you can add to your repo config in config.ex

config :chat, Chat.Repo,
  maintenance_database: "<db-name>",

You can check out why this works in their docs

3 Likes
14

this helped in my case

1 Like