Psycopg2: SSL connections fail with since 2.7.0

[rraval]

We use client certificates to authenticate and encrypt our connections.

With version 2.6.2, the certificate works and connections go through without issue.

Since 2.7.0, connections fail with the following error:

(psycopg2.OperationalError) could not read certificate file "/home/encircle/.postgresql/postgresql.crt": unknown public key type

openssl has no issues reading or verifying the cert so I'm not sure what might have changed:

$ openssl verify -verbose -x509_strict -CApath nosuchdir -CAfile ~/.postgresql/root.crt ~/.postgresql/postgresql.crt 
/home/encircle/.postgresql/postgresql.crt: OK

$ cat ~/.postgresql/postgresql.crt | openssl x509 -text
Certificate:
    Data:
        Version: 1 (0x0)
        Serial Number: 0 (0x0)
    Signature Algorithm: ecdsa-with-SHA256
        Issuer: CN=encircle_ca
        Validity
            Not Before: Sep  5 03:46:47 2016 GMT
            Not After : Sep  5 03:46:47 2017 GMT
        Subject: CN=encircle
        Subject Public Key Info:
            Public Key Algorithm: id-ecPublicKey
                Public-Key: (384 bit)
                pub: 
                    ...
                ASN1 OID: secp384r1
                NIST CURVE: P-384
    Signature Algorithm: ecdsa-with-SHA256

[dvarrazzo]

Have you installed psycopg from a wheel package? Wheel packages are bundled with libssl: the version packaged is 0.9.8e. Maybe the type of the public key you are using is not supported by this version?

I'm interested in fixing the issue (which may probably involve building a more up-to-date libssl version on the build machines). Can you confirm this is the case? In the meantime you can force installing psycopg from source (hence using your versions of the libraries) using pip install --no-binary.

[rraval]

I can confirm that 2.7.0 was originally installed from the wheel package (which rejects the client certificate).

Installing from source fixes the problem:

$ sudo -H pip3 install psycopg2==2.7.0 --upgrade --no-binary psycopg2 --no-cache-dir
Collecting psycopg2==2.7.0
  Downloading psycopg2-2.7.tar.gz (421kB)
    100% |████████████████████████████████| 430kB 6.3MB/s 
Installing collected packages: psycopg2
  Running setup.py install for psycopg2 ... done
Successfully installed psycopg2-2.7

The machine I'm testing on is using openssl 1.0.2g.

[dvarrazzo]

I wonder how hard is to build openssl from scratch on Centos 5 (https://github.com/pypa/manylinux)...

[rraval]

I had a go at it, it seems that openssl-1.0.2k seems to build just fine. Here's a Dockerfile:

FROM quay.io/pypa/manylinux1_x86_64
ARG openssl_version=1.0.2k

WORKDIR /tmp
RUN wget -O openssl-${openssl_version}.tar.gz "https://www.openssl.org/source/openssl-${openssl_version}.tar.gz"
RUN tar -xzvf openssl-${openssl_version}.tar.gz

WORKDIR /tmp/openssl-${openssl_version}
RUN ./config --prefix=/usr/local --openssldir=/usr/local
RUN make
RUN make install

WORKDIR /
RUN rm -r /tmp/openssl-${openssl_version}

ENTRYPOINT /bin/bash

This sticks the static library into /usr/local/lib64/libssl.a. I'm not sure what your build setup is like but this should be a starting point.

[dvarrazzo]

I played a bit with it too, ending up with this script.

Haven't tried to build the package yet, nor checked the dependencies of the libs via ldd, nor actually tested anything yet...

[dvarrazzo]

Tried to build libpq with the above stuff. It is using libssl 1.0.0 but pulling in 0.9.8 too.

[root@19d1c7d31edf scripts]# unzip -l /build/psycopg2/dist/psycopg2-2.7/psycopg2-2.7-cp27-cp27mu-manylinux1_x86_64.whl | grep "\.libs" | awk '{print $4}' | sort
psycopg2/.libs/libcom_err-beb60336.so.2.1
psycopg2/.libs/libcrypto-057f9504.so.1.0.0
psycopg2/.libs/libcrypto-de69073a.so.0.9.8e
psycopg2/.libs/libgssapi_krb5-174f8956.so.2.2
psycopg2/.libs/libk5crypto-622ef25b.so.3.1
psycopg2/.libs/libkeyutils-1-ff31573b.2.so
psycopg2/.libs/libkrb5-fb0d2caa.so.3.3
psycopg2/.libs/libkrb5support-d7ce89d4.so.0.1
psycopg2/.libs/liblber-2-9d326cbe.3.so.0.2.31
psycopg2/.libs/libldap_r-2-e083a59e.3.so.0.2.31
psycopg2/.libs/libpq-53b1dc55.so.5.9
psycopg2/.libs/libresolv-2-c4c53def.5.so
psycopg2/.libs/libsasl2-e96a0dbf.so.2.0.22
psycopg2/.libs/libselinux-cf8f9094.so.1
psycopg2/.libs/libsepol-b4f5b513.so.1
psycopg2/.libs/libssl-a65b360f.so.0.9.8e
psycopg2/.libs/libssl-b3ae70b2.so.1.0.0
psycopg2/.libs/libz-a147dcb0.so.1.2.3

Playing with ldd it seems it is only libldap linking to it so we'd need to build it from source too.

[dvarrazzo]

@rraval I may have created some packages ok. They link with:

libcom_err-beb60336.so.2.1
libcrypto-057f9504.so.1.0.0
libgssapi_krb5-174f8956.so.2.2
libk5crypto-622ef25b.so.3.1
libkeyutils-1-ff31573b.2.so
libkrb5-fb0d2caa.so.3.3
libkrb5support-d7ce89d4.so.0.1
liblber-2-b42c6e24.4.so.2.10.7
libldap_r-2-80ec947a.4.so.2.10.7
libpq-10043c8d.so.5.9
libresolv-2-c4c53def.5.so
libsasl2-e96a0dbf.so.2.0.22
libselinux-cf8f9094.so.1
libsepol-b4f5b513.so.1
libssl-b3ae70b2.so.1.0.0
libz-a147dcb0.so.1.2.3

Could you please try them? They are available in http://initd.org/psycopg/upload/psycopg2-2.7.1.dev0/ and you can install them with pip install URL (you can use pip install psycopg2 and then uninstall it to know what is the right .whl for you)

[dvarrazzo]

@b-harper you seem expert with RPM maybe I can ask you a favour? Fixing this bug involves building a few libraries instead of using the ones packaged on CentOS 5, which are too old. Could you please check if this script is sensible? In particular I think I got the dependencies right (the libraries include in the package are what I expect) but I'm not sure I'm passing all the right configure parameters to build the libraries, and if this makes a difference. Also, I'm missing the OS patches, and again I don't know how much they would be required.

Do you think it is possible/useful to build these packages starting from the rpm sources of CentOS 6 instead? Or could you suggest if we are missing some obvious configuration bits from these libraries? Thank you!

[carlwgeorge]

RHEL/CentOS (EL) 5 will be EOL in four weeks (2017-03-31). Switching to EL6 for building the wheels will be a much more efficient use of your time than compiling newer OpenSSL on EL5.

[dvarrazzo]

I know, this ties to https://github.com/pypa/manylinux/issues/96, it's a decision I haven't made myself. I hope there will be a decision soon about a manylinux2 or something like that. I'll be very happy to support that and drop this stuff.

[b-harper]

@carlwgeorge beat me to the punch. Old EL is old.

I would also recommend against packaging OpenSSL in general for EL. It is just not worth all the headaches.

[rraval]

@dvarrazzo Unfortunately, the dev version you provided fails with a different error:

2.7.0 fails with

(psycopg2.OperationalError) could not read certificate file "/home/encircle/.postgresql/postgresql.crt": unknown public key type

psycopg2-2.7.1.dev0-cp35-cp35m-manylinux1_x86_64.whl fails with:

(psycopg2.OperationalError) could not read certificate file "/home/encircle/.postgresql/postgresql.crt": unsupported algorithm

[dvarrazzo]

I can see if I can get the ball rolling maybe on the manylinux side, but without packaging libssl we couldn't have released wheel packages, or we could have but it would have been a badly crippled psycopg.

It can be argued whether it's the case to fix this bug within the current manylinux1 framework, or not fix it now but wait for manylinux2 hopefully shipping with a mustard-cutting libssl. People needing the missing libssl features can still install psycopg from source.

Let's see if the need of the missing openssl features is a compelling demand or if the workaround of installing from source is enough as a stopgap measure (I hope it is! Wheels packages have been around for one day only, people can't be so spoiled! :D)

Waiting for the metoo to roll in...

[dvarrazzo]

@rraval if you want to play with the script linked in this thread you are welcome: you may know better than me what is the missing algorithm.

[dvarrazzo]

@rraval FYI, I've added some notes in the docs about skipping wheel packages.

[dvarrazzo]

@rraval with previous libssl build I had dropped some algorithms (copied configure flags from the rpm recipe, thinking it was a good idea). Can you please try wheels from there? They are at http://initd.org/psycopg/upload/psycopg2-2.7.1.dev1/

Still not 100% convinced this is the way to fix this issue: in parallel I'm trying blindly to build manylinux on CentOS 6 and see what death I'm going to die.

[rraval]

@dvarrazzo sorry for the delay, $dayjob got hectic for a bit.

2.7.1.dev1 seems to work properly!

[rraval]

@dvarrazzo how goes the battle?

I'm trying to decide whether to keep pinning 2.6.2 or update our deployment scripts to build 2.7.0 from source. 2.7.1dev1 looked good from my perspective but I'm not sure if it trades off stability/portability in other areas.

[dvarrazzo]

@rraval my plan would be to release 2.7.1 with ssl 1.0.2k in a few days.

[rraval]

@dvarrazzo That's awesome. We'll just wait for that then. Thanks for the effort :)