Anyone using libpqxx on Debian Jessie?

General
#1

I’m connecting to my three roach cluster securely using libpqxx. This works fine from the Linux Subsystem For Windows on my Surface Pro. It works fine from a Debian Stretch machine.

Problem is when I put the code onto a Google cloud instance running Debian Jessie it fails to connect.

This is how I make the connection:

Persist::Persist() {
    cout << "Persist::Persist..." << endl;
    try {
        string uri = ("postgresql://someone@someserver:26257/"
                      "time_series?password=1234&sslmode=verify-full&"
                      "sslrootcert=ca.crt");
         auto cp = new pqxx::connection(uri);
    ...
    ...
    } catch (const exception &e) {
        cerr << "Persist::Persist: FAIL" << endl;
   }
}

This is the error I get on Debian Jessie:

$ ./testFeeder
Persist::Persist...
Persist::Persist: FAIL
server common name "node" does not match host name "someserver"
Segmentation fault

Thinking this may be an issue with our Jessie server I fired up a clean instance of Jessie in VirtualBox, with the same results.

I have no idea where it’s getting that common name “node” from.

Did I create my ca.crt incorrectly?

Do I have a problem with an old openssl version on Jessie (OpenSSL 1.0.1t 3 May 2016) ?

Sorry this is not directly a cockroachdb question but pqxx is what we have to use here.

Any ideas? Anyone?

Edit: Sure enough upgrading that Jessie in VirtualBox to Stretch gets my code working. Problem is we are not about to upgrade our cloud server instances.

#2

TLDR: you’ll want to use verify-ca for older versions of libpq.

This is an issue with the version of libpq. Older versions used to check that the address (DNS or IP) used to reach a server matches the Common Name field of the server’s certificate. The cockroach server certificates are also client certificates, and node is the username for cockroach nodes.

Newer versions of libpq check the Subject Alternative Name instead, which in our case contains the ip addresses and dns names you specified on cockroach certs create-node <addresses>.

You can find more details the libpq-ssl page. Choosing different versions seems to show that the change occurred at version 9.5. Sure enough, the libpq included in jessie is 9.4.

The way to get around this check without upgrading the libpq library is to use sslmode=verify-ca instead of verify-full. This will skip the host check but verify that the certificate is valid.
This still provides you with SSL communication, but it does open you to man-in-the-middle attacks.
That said, you control exactly which certificates are signed, so verify-ca is most likely enough.

#3

Thank you marc.

“sslmode=verify-ca” works here.

It’s been a traumatic week for me. What with memory leaks in libcrypto/pqxx, bugs in cockroachdb and now this pqxx certificate thing. All the time wondering if it’s me or not. I guess you know the feeling.

Thanks again.