postgresql Psycopg2 Python SSL 支持未编译

Psycopg2 Python SSL Support is not compiled in

提问by user2158382

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

我正在尝试使用带有 sslmode='required' 参数的 psycopg2 连接到我的 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
• Python 2.7
• Installed psycopg2 via pip
• Installed python via homebrew

• Mac OS X El Capitan
• 蟒蛇 2.7
• 通过 pip 安装 psycopg2
• 通过自制软件安装python

Here is what I tried to do to fix the problem

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

• brew uninstall python
• which pythonstill 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
• brew install python --with-brewed-openssl --build-from-source
• pip uninstall psycopg2
• pip install psycopg2

• brew uninstall python
• which python仍然显示 python 生活在/usr/local/bin/python，试图卸载它但不能。并听说这是操作系统使用的python，无论如何都不应该卸载
• brew install python --with-brewed-openssl --build-from-source
• pip uninstall psycopg2
• pip install psycopg2

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

完成所有这些后，异常仍然发生。我正在通过#!/usr/bin/env python不确定它是否重要来运行这个 python 脚本，但这与which python显示的目录不同

回答by andreas-hofmann

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.

在 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-caor verify-full. See hereunder sslmodefor reference.

postgresql-docs 还指出，如果 sslmode 设置为require、verify-ca或verify-full，则缺少 SSL 支持将引发错误。请参阅此处以sslmode供参考。

The postgres-websitelists 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的-网站列出了几种方法来从二进制软件包安装Postgres的，所以你可以选择一个适合您的需求。我不熟悉 OSX，所以我没有最好的建议。

Thisquestion 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_configwhich is included in your new installation to $PATH when running pip install psycopg2).

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

回答by Brideau

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 hereand 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 listyou'll see psycopg2 installed with <pip>in the far right column. After that, I just restarted Python and everything worked.

然后，当您运行时，conda list您会<pip>在最右侧的栏中看到安装了 psycopg2 。之后，我刚刚重新启动了 Python，一切正常。

回答by mfitzp

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 psycopg2is built against does not have SSL support compiled in. When you attempt to connect to the running Posgres server with ssl=requireit throws this error.

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

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 postgresqlshows that it depends on openssland compiles with the --with-opensslflag set. It will also install the neccessary libpqheaders. You may need to reinstall psycopg2after this step to ensure it picks up the correct libraries/version.

postgresql的公式表明它依赖openssl并编译--with-openssl标志集。它还将安装必要的libpq标头。您可能需要psycopg2在此步骤后重新安装，以确保它选择正确的库/版本。

Interestingly, there is a bug listed against condawhich 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列出的错误，其中列出了当 conda 版本（psycopg2在带有 Homebrew postgres 的系统上链接）安装在没有安装的系统上时报告的相同错误，这表明缺少 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 安装的任何版本），以尽量减少使用错误版本的风险。

回答by Logan

As others have said, the error message looks to be coming from Postgres. You can verify this by typing: psql sslmode=requireif 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.comthat 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

导航到您的配置目录，默认情况下它是：~/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"

重新启动应用程序，然后检查 ssl psql "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 代码问题。

回答by thenaturalist

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

因为我无法发表评论：
添加到 Brideau 的回答中，这仅在更改了我的 Postgres 版本后才对我有用。

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

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

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

回答by Andy G

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 notesfor 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
• psycopg2 2.6.2 installed via pip
• PostgreSQL installed via Enterprise DB Installer

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

Running pip uninstall psycopg2followed by pip install psycopg2resolved matters.

运行pip uninstall psycopg2后pip install psycopg2解决问题。

回答by anatoly techtonik

Try to install psycopg2from MacPorts

尝试psycopg2从MacPorts安装

 sudo port install py27-psycopg2