Postgres-python多个SSL连接 [英] Postgres - python multiple SSL connections

问题描述

我在使用psycopg2和SSL建立两个并发的Postgres数据库连接（一个到主数据库，一个到从数据库）时遇到了麻烦。单独地，两个连接都起作用，即：

I have troubles establishing two concurrent Postgres databases connections (one to master, one to slave) using psycopg2 and SSL. Separately, both connection work ie:

import psycopg2
dsnMaster='dbname=... sslcert=path/to/master/cert'
psycopg2.connect(dsnMaster, connection_factory=None, async=False)

正常工作

import psycopg2
dsnSlave='dbname=... sslcert=path/to/slave/cert'
psycopg2.connect(dsnSlave, connection_factory=None, async=False

但是同时加入

import psycopg2
dsnMaster='dbname=... sslcert=path/to/master/cert'
psycopg2.connect(dsnMaster, connection_factory=None, async=False)
dsnSlave='dbname=... sslcert=path/to/slave/cert'
psycopg2.connect(dsnSlave, connection_factory=None, async=False)

对于第二个连接始终失败， SSL错误：块类型不是01
。似乎psycopg使用了先前连接的证书。

always fails for the second connection, with SSL error: block type is not 01 . It seems psycopg uses the certificates from the previous connection.

我有特里d到.close（）第一个连接（如此处所示，但没有ssl

I have tried to .close() the first connection (as shown here but without ssl change database (postgresql) in python using psycopg2 dynamically), and also tried the various psycopg.extensions isolation_level options, without success.

在此先感谢！

使用psycopg2动态更改python中的数据库（postgresql）），并尝试了各种psycopg.extensionsisolation_level选项，但没有成功。 / p>

Thanks in advance!

推荐答案

我相信我已将问题归结为libq ... PostgreSQL C库。

I believe that I've tracked the problem down to libq... The PostgreSQL C library.

我也注意到我不能为2个不同的连接使用不同的ssl客户端证书。第一个连接总是成功，而第二个连接总是失败，并出现 SSL错误：证书验证失败

I too noticed that I could not use different ssl client certificates for 2 different connections. The first connection always succeeds, while the 2nd connection always fails with SSL error: certificate verify failed

在服务器上日志我得到无法接受SSL连接：tlsv1警报未知ca

On the server log I get could not accept SSL connection: tlsv1 alert unknown ca

这告诉我第二个连接可能正在尝试

This tells me the 2nd connection is possibly trying to use the ssl cert from the first connection instead of using the ssl cert it is being told to use.

考虑此代码

import psycopg2
conn1 = psycopg2.connect('host=server1... sslcert=path/to/cert1')
conn2 = psycopg2.connect('host=server2... sslcert=path/to/cert2')

出现连接2

我发现psycopg2存在问题...也许是在缓存客户端ssl证书...。

I figured there was a problem with psycopg2... perhaps it was caching the client ssl certificate....

我继续构建了psycopg2的调试版本并安装了它。我再次尝试了代码，并获得了大量调试信息。这是我得到的调试信息。 （我只发布相关信息）

I went ahead and built a debug version of psycopg2 and installed it.. I tried my code again and got tons of debug info. Here is the debug information I got. (I'm only posting the relevant info)

[98940] psyco_connect: dsn = 'dbname=testdb user=testdb host=server1 sslrootcert=root1.crt sslkey=cert1.key sslcert=cert1.crt sslmode=verify-full', async = 0
[98940] connection_setup: init connection object at 0x103093048, async 0, refcnt = 1
[98940] con_connect: connecting in SYNC mode
[98940] conn_connect: new postgresql connection at 0x10047ff90
[98940] conn_connect: server standard_conforming_strings parameter: on
[98940] conn_connect: server requires E'' quotes: NO
[98940] conn_connect: using protocol 3
[98940] conn_connect: client encoding: UTF8
[98940] clear_encoding_name: UTF8 -> UTF8
[98940] conn_connect: DateStyle ISO, MDY
[98940] connection_setup: good connection object at 0x103093048, refcnt = 1
# ... Got a good 1st connection here
# ... (Tons more lines of output before the 2nd connection)
[98940] psyco_connect: dsn = 'dbname=testdb user=testdb host=server2 sslrootcert=root2.crt sslkey=cert2.key sslcert=cert2.crt sslmode=verify-full', async = 0
[98940] connection_setup: init connection object at 0x103093170, async 0, refcnt = 1
[98940] con_connect: connecting in SYNC mode
[98940] conn_connect: new postgresql connection at 0x100682d30
[98940] conn_connect: PQconnectdb(dbname=testdb user=testdb host=server2 sslrootcert=root2.crt sslkey=cert2.key sslcert=cert2.crt sslmode=verify-full) returned BAD
[98940] connection_init: FAILED
[98940] conn_close: PQfinish called
[98940] connection_dealloc: deleted connection object at 0x103093170, refcnt = 0

如果我切换2个连接器部分，结果相同...第一个连接成功，但是第二个连接失败。因此，第二个连接的dsn是正确的，因为如果先执行连接就会成功。

If I switch the 2 connections, it is the same result... The first connection succeeds but the 2nd connection fails. So, the dsn for the 2nd connection is correct because the connection succeeds if executed first.

检查psycopg2的来源，它只是调用 PQconnectdb 来自libq C库...并且使用正确的参数调用它。
您可以在
上的 PQconnectdb 上签出文档 http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PQCONNECTDB

Checking out the source of psycopg2, it simply calls PQconnectdb from the libq C library... and it's calling it with the correct parameters. You can check out the docs on PQconnectdb at http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PQCONNECTDB

这告诉我psycopg2使用正确的参数正确调用 PQconnectdb 和 PQconnectdb 只是在第二个连接上没有使用正确的证书。

This tells me that psycopg2 is calling the PQconnectdb correctly with the correct parameters and PQconnectdb is just not using the correct certificate on the 2nd connection.

此外，我还对其他程序进行了一些测试。我针对PostgreSQL（Mac版）测试了Navicat-相同的问题。第一个连接成功，第二个连接未能验证证书。重新启动Navicat时，它再次发生...无论我按什么顺序，第一个连接成功，第二个连接失败。

What's more is that I did some testing with other programs as well. I tested Navicat for PostgreSQL (Mac Version) - same issue. First connection succeeds, second connection fails to verify the certificate. When I restart Navicat, it happens again... first connection succeeds and second connection fails no matter what order I try.

PgAdmin发生了相同的事情（当前最新版本1.20）。第一次连接成功而第二次连接失败。

Same thing happens with PgAdmin (latest version is currently 1.20). First connection succeeds and 2nd connection fails.

我的怀疑是，只要使用libq进行连接，连接到PostgreSQL的任何软件或模块都会遭受相同的问题。事实上，我什至测试了PHP，并得到了相同的结果

My suspicion is that any software or module that connects to PostgreSQL will suffer the same issues as long as it uses libq to connect. As a matter of fact, I even tested PHP, and got the same result

root@test:~# php -a
Interactive mode enabled

php > // Test with server 1 first
php > $conn = pg_connect('host=server1 user=testdb dbname=testdb sslcert=cert1.crt sslmode=verify-full sslkey=cert1.key sslrootcert=root1.crt');
php > $conn2 = pg_connect('host=server2 user=testdb dbname=testdb sslcert=cert2.crt sslmode=verify-full sslkey=cert2.key sslrootcert=root2.crt');
PHP Warning:  pg_connect(): Unable to connect to PostgreSQL server: SSL error: certificate verify failed in php shell code on line 1
php > quit
root@test:~# php -a
Interactive mode enabled

php > // Test with server 2 first
php > $conn2 = pg_connect('host=server2 user=testdb dbname=testdb sslcert=cert2.crt sslmode=verify-full sslkey=cert2.key sslrootcert=root2.crt');
php > $conn = pg_connect('host=server1 user=testdb dbname=testdb sslcert=cert1.crt sslmode=verify-full sslkey=cert1.key sslrootcert=root1.crt');
PHP Warning:  pg_connect(): Unable to connect to PostgreSQL server: SSL error: certificate verify failed in php shell code on line 1
php > quit
root@test:~# php -a
Interactive mode enabled

php > // Test using the same certificate
php > $conn = pg_connect('host=server1 user=testdb dbname=testdb sslcert=cert1.crt sslmode=verify-full sslkey=cert1.key sslrootcert=root1.crt');
php > $conn2 = pg_connect('host=server2 user=testdb dbname=testdb sslcert=cert1.crt sslmode=verify-full sslkey=cert1.key sslrootcert=root1.crt');
php > // No problems. Both connect just fine now

我的建议是使用PostgreSQL提交错误报告。不知道这里是否是提交此类错误报告的正确位置
http：// www.postgresql.org/support/submitbug/

My suggestion is to submit a bug report with PostgreSQL. Not sure if this is the correct place to submit a bug report of this kind http://www.postgresql.org/support/submitbug/

在此方法有效之前，我想出了一个对我有用的解决方案...目前的解决方案是对两个服务器使用相同的证书。如果可以做到这一点，则它对两个连接均有效，并且您可以将2个单独的连接连接到2个单独的服务器...（只要两个连接都可以使用相同的客户端证书进行连接）

Until this works, I was able to come up with a solution that works for me... The solution for now is to simply use the same certificates for both servers. If you can do this, it works for both connections and you can have 2 separate connections to 2 separate servers... (as long as both connections can connect using the same client certificate)

对我来说，我只是对两个服务器使用了相同的服务器ssl证书，私钥和根证书...我只是使用通配符作为通用名并自己签署了证书（但是您可以使用商业通配符证书，如果愿意的话），然后，我生成了一个客户端证书，并使用了单个证书进行两个连接。

For me, I simply used the same server ssl certificate, private key, and root certificate for both servers... I just used a wildcard for the common name and signed the certificate myself (but you could use a commercial wildcard certificate if you like) Then, I generated a client certificate and used that single certificate for both connections.

这可能不是您要查找的答案，但这似乎是您使用客户端证书身份验证通过SSL与2个不同服务器建立2个连接的唯一方法。不管您使用哪种编程语言或软件，都是如此。

This might not be the answer you were looking for, but this seems to be the only way you can have 2 connections to 2 different servers over SSL using client certificate authentication. And this is true no matter what programming language or software you use.

因此，您的代码现在变为：

So, your code now becomes something like:

import psycopg2
dsnMaster='dbname=... sslcert=path/to/master/cert'
psycopg2.connect(dsnMaster, connection_factory=None, async=False)

# Here, the dsnSlave simply uses the same cert as the master
# Other connection details like the host and dbname can be different
dsnSlave='dbname=... sslcert=path/to/master/cert'
psycopg2.connect(dsnSlave, connection_factory=None, async=False)

这是我在python中有效的实际代码

This is my actual code in python that works

root@test:~# python3
Python 3.4.0 (default, Jun 19 2015, 14:20:21) 
[GCC 4.8.2] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> dsn1 = 'host=server1 user=testdb dbname=testdb sslcert=cert1.crt sslmode=verify-full sslkey=cert1.key sslrootcert=root1.crt'
>>> conn1 = psycopg2.connect(dsn1)
>>> dsn2 = 'host=server2 user=testdb dbname=testdb sslcert=cert1.crt sslmode=verify-full sslkey=cert1.key sslrootcert=root1.crt'
>>> conn2 = psycopg2.connect(dsn2)
>>> # YAY, no issues and both connections work

这篇关于Postgres-python多个SSL连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！