Postgrex-主机"xxx.xxx.xxx.xxx"没有pg_hba.conf条目；

问题描述

我有一个单独托管的Postgres数据库，我正在尝试将其与Phoenix应用程序一起使用.我的prod配置是:

I have a Postgres Database hosted separately which I'm trying to use with my Phoenix Application. My prod config is:

config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.Postgres,
  url: "postgres://username:password@myhost:5432/my_database",
  size: 20

当我的应用程序托管在Heroku上时，此方法运行良好，但是由于将其移至VPS，所以我不断收到此错误:

This worked fine while my application was hosted on Heroku, but since I've moved it to a VPS, I keep getting this error:

17:07:13.665 [info] GET /subjects
17:07:13.707 [info] Processing by MyApp.SubjectController.index/2
  Parameters: %{"format" => "html"}
  Pipelines: [:browser, :authorize]
17:07:13.951 [error] GenServer #PID<0.515.0> terminating
** (exit) %Postgrex.Error{message: nil, postgres: %{code: :invalid_authorization_specification, file: "auth.c", line: "474", message: "no pg_hba.conf entry for host \"xxx.xxx.xxx.xxx\", user \"username\", database \"my_database\", SSL off", pg_code: "28000", routine: "ClientAuthentication", severity: "FATAL"}}
17:07:13.970 [info] Sent 500 in 305ms
17:07:13.972 [error] #PID<0.513.0> running MyApp.Endpoint terminated
Server: xxx.xxx.xxx.xxx:80 (http)
Request: GET /subjects
** (exit) exited in: GenServer.call(#PID<0.515.0>, {:query, "SELECT s0.\"id\", s0.\"name\", s0.\"inserted_at\", s0.\"updated_at\" FROM \"subjects\" AS s0", []}, 5000)
    ** (EXIT) %Postgrex.Error{message: nil, postgres: %{code: :invalid_authorization_specification, file: "auth.c", line: "474", message: "no pg_hba.conf entry for host \"xxx.xxx.xxx.xxx\", user \"username\", database \"my_database\", SSL off", pg_code: "28000", routine: "ClientAuthentication", severity: "FATAL"}}

我还可以从Postgres GUI工具连接到数据库，因此没有任何问题.我注意到的一件事是，它将VPS IP声明为主机，而不是url中指定的主机.

I can also connect to the Database from Postgres GUI tools, so there isn't anything wrong with that. One thing that I noted was that it states my VPS IP as the host instead of the host specified in the url.

这没关系，因为数据库完全位于单独的主机上，但是我仍然在这里尝试解决方案:

It shouldn't matter since the Database is on a separate host altogether, but I still tried the solutions here:

• 主机没有pg_hba.conf条目
• 连接到PostgreSQL服务器:致命:主机没有pg_hba.conf条目

• No pg_hba.conf entry for host
• Connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

真的很为此苦苦挣扎，请帮忙！

Really struggling with this, please help!

更新:这是我的pg_hba.conf:

Update: Here's my pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
host    all             all             0.0.0.0/0               md5

推荐答案

这太愚蠢了，但我发现了问题所在.我尝试连接的主机期望安全的数据库连接.默认情况下，SSL是关闭的，因此我已启用它:

This is so stupid, but I figured out the problem. The host I was trying to connect to, expected a secure database connection. By default, SSL is turned off, so I had enable it:

config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.Postgres,
  url: "postgres://username:password@myhost:5432/my_database",
  ssl: true,
  size: 20

---

感谢这个答案: Node.js，PostgreSQL错误:主机没有pg_hba.conf条目

---

Thanks to this answer: Node.js, PostgreSQL error: no pg_hba.conf entry for host

这篇关于Postgrex-主机"xxx.xxx.xxx.xxx"没有pg_hba.conf条目；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！