Postgres - python multiple SSL connections

Question

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)

works and so does

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

But joining both

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)

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

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.

Thanks in advance!

Answer 1

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

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

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.

Consider this code

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

Connection 2 appears to be using cert1 instead of cert2

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

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

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.

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

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.

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.

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

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

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/

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)

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.

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)

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