postgresql Psycopg2 报告 pg_hba.conf 错误

Psycopg2 reporting pg_hba.conf error

提问by Steve Gattuso

I've run into a weird situation while trying to use PostgreSQL and Psycopg2. For some reason, every time I attempt to connect to the postgre database via python, I get the following error:

我在尝试使用 PostgreSQL 和 Psycopg2 时遇到了一个奇怪的情况。出于某种原因，每次我尝试通过 python 连接到 postgre 数据库时，我都会收到以下错误：

psycopg2.OperationalError: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "steve", database "steve", SSL on
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "steve", database "steve", SSL off

Naturally, I checked pg_hba.conf to see what the issue was, but everything appeared to be configured correctly as far as I can see:

当然，我检查了 pg_hba.conf 以查看问题所在，但据我所知，一切似乎都配置正确：

pg_hba.conf:

pg_hba.conf:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

In addition, I've found that I can connect to the database via psql as I would expect:

此外，我发现我可以通过 psql 连接到数据库，正如我所期望的：

$ psql -U steve -h 127.0.0.1
...
steve=>

Anyone have any ideas as to what could be going on here? Thanks in advance!

任何人对这里可能发生的事情有任何想法吗？提前致谢！

回答by Erwin Brandstetter

Typical explanations include:

典型的解释包括：

• You are connecting to the wrong server.
  Is the DB server running on the same host as Python does?

• You got the wrong port.
  Check the server log if you see a connection attempt. You have to log connections for that, of course. See the config parameter log_connections.

• You did not reload(SIGHUP) the server after changing pg_hba.conf- or reloaded the wrong cluster (if you have multiple DB clusters).
  Use pg_ctlor pg_ctlcluseron Debian and derivatives for that.

• 您连接到错误的服务器。
  数据库服务器是否与 Python 运行在同一台主机上？

• 你弄错了端口。
  如果您看到连接尝试，请检查服务器日志。当然，您必须为此记录连接。请参阅配置参数log_connections。

• 您在更改后没有重新加载(SIGHUP) 服务器pg_hba.conf- 或者重新加载了错误的集群（如果您有多个数据库集群）。为此在 Debian 和衍生产品上
  使用pg_ctl或pg_ctlcluser。

回答by Sathish

I recently got into this same issue and I found the solution for this problem.

我最近遇到了同样的问题，我找到了这个问题的解决方案。

System:

系统：

1. I have an application server (with these packages installed python, django, psycopg2and postgres client 9.6.1(postgresql-9.6.1.tar.gz)), for instance ip address 10.0.0.1(a private address).
2. And AWS postgres RDS server "aws_rds_host_name" or any database IP address.

1. 我有一个应用程序服务器（安装了这些软件包python，django，psycopg2和postgres client 9.6.1（postgresql-9.6.1.tar.gz）），例如ip address 10.0.0.1（使用私有地址）。
2. 以及 AWS postgres RDS 服务器“ aws_rds_host_name”或任何数据库 IP 地址。

Error:

错误：

django.db.utils.OperationalError: FATAL: no pg_hba.conf entry for host"10.0.0.1", user "your_user", database "your_db", SSL off

django.db.utils.OperationalError: FATAL: no pg_hba.conf entry for host"10.0.0.1", user "your_user", database "your_db", SSL off

Solution:

解决方案：

While installing the postgres client 9.6.1source package in application server 10.0.0.1, we have to pass an argument "--with-openssl". I suggest to remove the existing the postgres clientand install with below steps.

postgres client 9.6.1在应用服务器中安装源包时10.0.0.1，我们必须传递一个参数“ --with-openssl”。我建议删除现有的postgres client并按照以下步骤安装。

1. Download the postgres client source package 9.6.1(postgresql-9.6.1.tar.gz)
2. Untar the package postgresql-9.6.1.tar.gz.
3. ./configure --prefix="your_preferred_postgres_path_if_needed" --with-openssl (this '--with-openssl'argument is important to get rid of that error)
4. make
5. make install
6. After successful installation, that error didn't occur when we ran the django project with psycopg2.

1. 下载postgres client source package 9.6.1( postgresql-9.6.1.tar.gz)
2. 解压缩包postgresql-9.6.1.tar.gz。
3. ./configure --prefix="your_preferred_postgres_path_if_needed" --with-openssl (this '--with-openssl'参数对于摆脱该错误很重要）
4. 制作
5. 进行安装
6. 安装成功后，运行django项目时没有出现这个错误psycopg2。

I hope this solution helps someone.

我希望这个解决方案可以帮助某人。