我试图连接到一个远程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-00pute-1.amazonaws 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 但我仍然招致这个问题。但是,我可以简单地使用命令行客户端进行连接(即使不更改pg_hba配置):
psql - h ec2-00-000-00-00pute-1.amazonaws -p 5492 database-name -W手动提供密码。
代码在我的Mac上本地运行,因此存在配置错误或被阻塞的情况。我无法弄清楚它可能是什么。任何建议欢迎。
(很明显,真实世界的价值已被上面样本中的占位符所取代)
解决方案Heroku要求您使用SSL。
如果您连接到Heroku,则: 在本地主机上修改PostgreSQL服务器的 pg_hba.conf 绝对不会有影响,因为这不是您要连接的服务器;
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-00pute-1.amazonaws 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 offInvestigating into pg_hba.conf, I've temporarily introduced the following line:
host all all trustAnd restarted Postgres with
sudo /etc/init.d/postgresql-9.3 restartBut 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-00pute-1.amazonaws -p 5492 database-name -Wmanually 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,但可以通过命令行
发布评论