无法连接psycopg2，但可以通过命令行 [英] Unable to connect with psycopg2, but can via command line

问题描述

我试图连接到一个远程Postgres数据库（在这里是一个Heroku Postgres实例）。我有一个Fabric命令，它使用 psycopg2 来对数据库执行一些操作。我的Postgres连接发生如下：

 ＃conf是从heroku config派生的哈希DATABASE_URL 
 self.conn = psycopg2.connect（
 database = conf.get（'database'），＃由heroku提供
 user = conf.get（'username'），＃是由heroku提供的数据库用户名
 password = conf.get（'密码'），＃由heroku提供
 host = conf.get（'host'），＃eg ec2-00-000-00-00.compute-1.amazonaws.com 
 port = conf。 get（'port'）＃5492 
）
  

运行脚本时出错：

  psycopg2.OperationalError：致命错误：用户my-server-user的密码认证失败
致命错误：no pg_hba.conf my-ip-address-here，用户my-server-user，数据库database-name-here，SSL off 
  

调查pg_hba.conf，我已经te我们推出了以下产品线：

 主机全部信任
  

然后重新启动Postgres
pre $ sudo /etc/init.d/postgresql -9.3重新启动

但我仍然招致这个问题。但是，我可以简单地使用命令行客户端进行连接（即使不更改pg_hba配置）：

  psql  - h ec2-00-000-00-00.compute-1.amazonaws.com -p 5492 database-name -W 
  

手动提供密码。

代码在我的Mac上本地运行，因此存在配置错误或被阻塞的情况。我无法弄清楚它可能是什么。任何建议欢迎。

（很明显，真实世界的价值已被上面样本中的占位符所取代）

解决方案

Heroku要求您使用SSL。

如果您连接到Heroku，则：

• 在本地主机上修改PostgreSQL服务器的 pg_hba.conf 绝对不会有影响，因为这不是您要连接的服务器;

您必须使用SSL 。将 sslmode ='require'选项传递给 psycopg2 。

I'm attempting to connect to a remote Postgres database (in this case a Heroku Postgres instance). I have a Fabric command that does some work against the DB using psycopg2.

My Postgres connection occurs as so:

# conf is a hash derived from the heroku config DATABASE_URL
self.conn = psycopg2.connect(
    database=conf.get('database'), # supplied by heroku
    user=conf.get('username'), # is the database username supplied by heroku
    password=conf.get('password'), # supplied by heroku
    host=conf.get('host'), # e.g ec2-00-000-00-00.compute-1.amazonaws.com
    port=conf.get('port') # 5492
)

Error from running the script:

psycopg2.OperationalError: FATAL:  password authentication failed for user "my-server-user"
FATAL:  no pg_hba.conf entry for host "my-ip-address-here", user "my-server-user", database "database-name-here", SSL off

Investigating into pg_hba.conf, I've temporarily introduced the following line:

host    all             all             trust

And restarted Postgres with

sudo /etc/init.d/postgresql-9.3 restart

But I am still incurring the issue. I am, however, able to simply connect using command line client (even without the change to the pg_hba configuration):

psql -h ec2-00-000-00-00.compute-1.amazonaws.com -p 5492 database-name -W

manually supplying the password.

The code runs locally on my Mac, so there is something that is misconfigured or blocked. I just can't figure out what it may be. Any suggestions welcome.

(Obviously, real world values have been replaced with placeholders in samples above)

解决方案

Heroku requires that you use SSL.

If you are connecting to Heroku, then:

• Modifying the pg_hba.conf of a PostgreSQL server on the local host will have absolutely zero effect, since that's not the server you are connecting to;

• You must use SSL. Pass the sslmode='require' option to psycopg2.

这篇关于无法连接psycopg2，但可以通过命令行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！