Psycopg2 Python SSL支持未编译

I am trying to connect to my postgres database using psycopg2 with sslmode='required' param; however, I get the following error

我正在尝试使用psycopg2和sslmode ='required'参数连接到我的postgres数据库;但是，我收到以下错误

psycopg2.OperationalError: sslmode value "require" invalid when SSL support is not compiled in

Heres a couple details about my system

这是关于我的系统的几个细节

Mac OS X El Capitan
Mac OS X El Capitan
Python 2.7
Python 2.7
Installed psycopg2 via pip
通过pip安装psycopg2
Installed python via homebrew
通过自制软件安装python

Here is what I tried to do to fix the problem

这是我试图解决问题的方法

brew uninstall python
brew卸载python
which python still shows python living in /usr/local/bin/python, tried to uninstall this but couldnt. And heard that this is the python that the OS uses and should not be uninstalled anyways
哪个python仍然显示生活在/ usr / local / bin / python中的python，试图卸载它但不能。并且听说这是操作系统使用的python，不管怎么说都不应该卸载
brew install python --with-brewed-openssl --build-from-source
brew install python --with-brewed-openssl --build-from-source
pip uninstall psycopg2
点击卸载psycopg2
pip install psycopg2
pip安装psycopg2

After doing all of this, the exception still happens. I am running this python script via #!/usr/bin/env python Not sure if it matters, but that is a different directory than the one that which python shows

完成所有这些后，异常仍然发生。我通过＃！/ usr / bin / env python运行这个python脚本不确定它是否重要，但这是一个不同于python显示的目录

7 个解决方案

#1

Since you're installing via pip, you should be using the most recent version of psycopg2 (2.6.1). After a little digging through the code, it seems that the exception is being thrown in connection_int.c, which directly calls the postgresql-c-libraries to set up the db-connection. The call happens like so:

由于您是通过pip安装的，因此您应该使用最新版本的psycopg2（2.6.1）。在对代码进行一点挖掘之后，看起来异常是在connection_int.c中抛出的，它直接调用postgresql-c-libraries来设置db-connection。这样的调用是这样的：

self->pgconn = pgconn = PQconnectStart(self->dsn);

Dprintf("conn_connect: new postgresql connection at %p", pgconn);

if (pgconn == NULL)
{
    Dprintf("conn_connect: PQconnectStart(%s) FAILED", self->dsn);
    PyErr_SetString(OperationalError, "PQconnectStart() failed");
    return -1;
}
else if (PQstatus(pgconn) == CONNECTION_BAD)
{
    Dprintf("conn_connect: PQconnectdb(%s) returned BAD", self->dsn);
    PyErr_SetString(OperationalError, PQerrorMessage(pgconn));
    return -1;
}

The keywords which were specified in your connect statement to psycopg2.connect() are being handled to that function and errors are returned as OperationalError exception.

在您的connect语句中指定给psycopg2.connect（）的关键字正在处理该函数，并且错误将作为OperationalError异常返回。

The error is actually being generated directly in the postgresql-lib - you may want to check which version you are using, how it was built and, if possible, upgrade it to a version with SSL support or rebuilt it from source with SSL enabled.

该错误实际上是直接在postgresql-lib中生成的 - 您可能想要检查您正在使用的版本，它是如何构建的，如果可能，请将其升级到支持SSL的版本，或者在启用SSL的情况下从源重建它。

The postgresql-docs also state that missing SSL support will raise an error, if the sslmode is set to require, verify-ca or verify-full. See here under sslmode for reference.

postgresql-docs还声明如果sslmode设置为require，verify-ca或verify-full，则缺少SSL支持将引发错误。请参阅sslmode下的参考资料。

The postgres-website lists several ways to install postgres from binary packages, so you might choose one which suits your needs. I'm not familiar with OSX, so I don't have a recommendation what's best.

postgres-website列出了从二进制包安装postgres的几种方法，因此您可以选择一种适合您需求的方法。我不熟悉OSX，所以我没有推荐什么是最好的。

This question may also be helpful.

这个问题也许有帮助。

You also need to reinstall the psycopg2-module, be sure to use the newly installed lib when rebuilding it. Refer to the linked question (in short, you will need to place the path to pg_config which is included in your new installation to $PATH when running pip install psycopg2).

您还需要重新安装psycopg2模块，确保在重建时使用新安装的lib。请参阅链接的问题（简而言之，在运行pip install psycopg2时，您需要将新安装中包含的pg_config路径放到$ PATH中）。

#2

I had this same error, which turned out to be because I was using the Anaconda version of psycopg2. To fix it, I had adapt VictorF's solution from here and run:

我有同样的错误，原来是因为我使用的是anaconda版本的psycopg2。为了解决这个问题，我从这里开始调整VictorF的解决方案并运行：

conda uninstall psycopg2
sudo ln -s /Users/YOURUSERNAME/anaconda/lib/libssl.1.0.0.dylib /usr/local/lib
sudo ln -s /Users/YOURUSERNAME/anaconda/lib/libcrypto.1.0.0.dylib /usr/local/lib
pip install psycopg2

Then when you run conda list you'll see psycopg2 installed with <pip> in the far right column. After that, I just restarted Python and everything worked.

然后当你运行conda list时，你会看到在最右边的列中用安装了psycopg2。之后，我刚刚重启Python，一切正常。

#3

The error you are receiving is caused by a problem with Postgres itself, and not psycopg2.

您收到的错误是由Postgres本身的问题引起的，而不是psycopg2引起的。

psycopg2.OperationalError: sslmode value "require" invalid when SSL support is not compiled in

The above indicates that the version of Postgres that psycopg2 is built against does not have SSL support compiled in. When you attempt to connect to the running Posgres server with ssl=require it throws this error.

以上表示psycopg2构建的Postgres版本没有编译的SSL支持。当您尝试使用ssl = require连接到正在运行的Posgres服务器时，它会抛出此错误。

You don't mention how you installed Postgres but since you are using Homebrew for other things, I recommend you also install Postgres the same way:

你没有提到你如何安装Postgres，但由于你使用Homebrew做其他事情，我建议你也以同样的方式安装Postgres：

$ brew update
$ brew install postgresql

The formula for postgresql shows that it depends on openssl and compiles with the --with-openssl flag set. It will also install the neccessary libpq headers. You may need to reinstall psycopg2 after this step to ensure it picks up the correct libraries/version.

postgresql的公式显示它依赖于openssl并使用--with-openssl标志集进行编译。它还将安装必要的libpq标头。您可能需要在此步骤之后重新安装psycopg2，以确保它获取正确的库/版本。

Interestingly, there is a bug listed against conda which lists the same error that you report occurring when the conda version of psycopg2 — linked on a system with Homebrew postgres — was installed on a system without, suggesting missing SSL libraries can also trigger this.

有趣的是，有一个针对conda列出的错误列出了你报告发生的同一个错误，当你的系统上安装了带有Homebrew postgres的系统的conda版本的psycopg2时没有安装，提示缺少SSL库也可以触发这个错误。

I would suggest uninstalling any existing Postgres versions (including any installed via Homebrew) before reinstalling to minimise the risk of the wrong one being used.

我建议在重新安装之前卸载任何现有的Postgres版本（包括通过Homebrew安装的任何版本），以最大限度地降低使用错误的风险。

#4

As others have said, the error message looks to be coming from Postgres. You can verify this by typing: psql sslmode=require if it gives you a pgsql terminal then ssl works with postgres, if it errors then it doesn't

正如其他人所说，错误信息似乎来自Postgres。您可以通过键入来验证这一点：psql sslmode = require如果它给你一个pgsql终端然后ssl与postgres一起工作，如果它有错误那么它不会

Try and remove postgres and reinstall with openssl support:

尝试删除postgres并使用openssl支持重新安装：

brew uninstall postgres
brew update
brew install postgres --with-openssl

Alternatively, and this is the way I'd suggest, there is a one click installer at http://postgresapp.com that might also make it easier to get it installed. Follow the instructions on the site to get it installed correctly.

或者，这就是我建议的方式，http://postgresapp.com上有一个单击安装程序，也可以让它更容易安装。按照网站上的说明正确安装。

When I did it on Yosemite it installed at ~/Library/Application\ Support/Postgres93/var

当我在优胜美地上做它时，它安装在〜/ Library / Application \ Support / Postgres93 / var

You'll also want to create a certificate (this could also be where the error is coming from) either from a registrar if this is going to be public facing in the slightest or self signed if it's for a dev/test environment.

您还需要创建一个证书（这也可能是错误来自的地方），如果这将是面向公众的最轻微的，或者如果它是针对开发/测试环境的话，则可以从注册商处创建。

openssl req -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt
chmod og-rwx server.key

Navigate to your config directory, by default it is: ~/Library/Application\ Support/Postgres93/var

导航到您的config目录，默认情况下是：〜/ Library / Application \ Support / Postgres93 / var

Enable ssl support:

启用ssl支持：

vim postgresql.conf
# change this:
# ssl = on
# to this:
ssl = on

Restart the app and then check ssl with psql "sslmode=require"

重新启动应用程序，然后使用psql检查ssl“sslmode = require”

If that works then try it through your Python code. If it works with the code above, but not Python then it's definitely a Python code problem that needs to be worked through.

如果可行，则通过Python代码尝试。如果它适用于上面的代码，但不适用于Python，那么它肯定是一个需要解决的Python代码问题。

#5

As I can not comment:
Adding to Brideau's answer that this only worked for me after changing my version of Postgres.

由于我无法发表评论：在改变我的Postgres版本后，添加到Brideau的答案，这只适用于我。

brew uninstall postgres
brew update
brew install postgres --with-openssl

Then run the code provided by Brideau and it should work.

然后运行Brideau提供的代码，它应该工作。

#6

If you're using v2.6.1 or v2.6.2 of psycopg2 (like me), the answer was a simple upgrade to v2.7. Reading the release notes for psycopg2, there was a minor fix for SSL albeit it doesn't look particularly relevant.

如果您正在使用psycopg2的v2.6.1或v2.6.2（和我一样），答案是对v2.7的简单升级。阅读psycopg2的发行说明，对SSL有一个小修复，尽管它看起来并不特别相关。

My setup was as follows:

我的设置如下：

Mac OS X El Capitan 10.11.6
Mac OS X El Capitan 10.11.6
psycopg2 2.6.2 installed via pip
通过pip安装psycopg2 2.6.2
PostgreSQL installed via Enterprise DB Installer
通过Enterprise DB Installer安装PostgreSQL

Running pip uninstall psycopg2 followed by pip install psycopg2 resolved matters.

运行pip卸载psycopg2然后pip安装psycopg2解决了问题。

#7

-4

Try to install psycopg2 from MacPorts

尝试从MacPorts安装psycopg2

 sudo port install py27-psycopg2

#1

self->pgconn = pgconn = PQconnectStart(self->dsn);

Dprintf("conn_connect: new postgresql connection at %p", pgconn);

if (pgconn == NULL)
{
    Dprintf("conn_connect: PQconnectStart(%s) FAILED", self->dsn);
    PyErr_SetString(OperationalError, "PQconnectStart() failed");
    return -1;
}
else if (PQstatus(pgconn) == CONNECTION_BAD)
{
    Dprintf("conn_connect: PQconnectdb(%s) returned BAD", self->dsn);
    PyErr_SetString(OperationalError, PQerrorMessage(pgconn));
    return -1;
}