That pesky libpq.dylib came back to haunt me, SHA2 certification

[Bastiaan Olij]

Hey All,

These past few weeks have been interesting and that is an understatement :)

I've been mailing back and forth with UK tech support and a small bump
in the road set aside, if this is what tech support is going to be under
the new management of Omnis Software I am more enthusiastic about the
future then ever. These guys spend days getting to the bottom of things
and it has been an extremely helpful learning experience on both sides.

Now you'd think the story of libpq.dylib was long settled, this was a
hot topic in the old Studio 4 days when the first of us, Doug, Lou and
us here at Instinct systems to name but a few ventured into the
wonderful world of secure connections as we moved our databases into the
cloud.

We had a bit of rude wake-up call a few weeks back as we were using SHA1
certificates which are now deemed so insecure that many products won't
accept even valid SHA1 certificates. While not directly effecting Omnis
as PostgreSQL still happily accepts SHA1 certificates we decided to move
up to SHA256 and that spectacularly broke everything.

So first things first, a note of warning, there is a lot of information
out on the web that is incorrect or misleading when it comes to SSL
certificates. Loads of people who only half understand what they are
doing (I count myself among those people ;) ).
This command has proven to be a lifesaver:
---
openssl x509 -text -noout -in mycertificate.crt
---
This dumps out all the info about the certificate mycertificate.crt in a
readable format. It will tell you if you've created your certificates
correctly. The openssl command is extremely good at silently accepting
your mistakes. Put a parameter in the wrong place and you'll likely
never know until you take a closer look at the certificate that was
actually generated.

There are a few more handy commands and I might do a write-up one of
these days about creating properly signed certificates but that is a
story for another day. This one though needs to be in your arsenal.

That brings us back to libpq. We've been using a libpq.dylib from 2011
which has served us well. The issue we had in Studio 4 days has long
since been remedied and if you simply place this file in your MacOS
folder within your Omnis app bundle it will be used. But it turns out it
only supports SHA1.

Apple is so nice they now supply a relatively new libpq.dylib in
/usr/lib. It unfortunately does not seem to be usable for our purposes.
There are two more dylibs in play here, libssl and libcrypto. First of
all, at least for the copies on my 10.11 and 10.12 machines, this
version of libpq.dylib does not seem to have bindings with either of
those two libraries. That may mean they are only used when in the same
folder or more likely, not used at all. But more importantly, the
libraries supplied by the OS are 0.9.x libraries. As far as we've been
able to tell PostgreSQL requires 1.x versions of these libraries to work.

So we started looking at our PostgreSQL installation (I used
enterprisedb 9.4 installers) and low and behold, all files we needed can
be found in its lib folder. The libpq.dylib supplied by PostgreSQL has
bindings to libssl.1.0.0.dylib and libcrypto.1.0.0.dylib but
interestingly enough they are bound to those files in /usr/lib. Also
note they are bound to those exact filenames (probably to prevent
accidentally loading the 0.9 versions supplied by the OS). Placing this
libpq.dylib in your MacOS folder and the libssl/libcrypto files in
/usr/lib works like a charm but gives us a problem.

On 10.12 /usr/lib is notoriously hard to administer and even if you
successfully place files there, they are likely to be cleaned up the
next time Apple releases a patch or you run a repair of your system. Our
only option seems to be to turn SIP off, which is not an option for most
of our clients.

However pgAdmin has been working fine for us (yes I know, some people
hate on pgAdmin) so I had a closer look at the pgAdmin in our Postgres
installation. There were two copies installed by the installer.
1) /Applications/PostgreSQL 9.4/pgAdmin III which is useless
2) /Library/PostgreSQL/9.4/pgAdmin3 is the one you want to look at
If you open up the contents of that second copy of pgAdmin you will find
our 3 files in it's Contents/Frameworks folder but the libpq.dylib is
not the same one as in the lib folder.
If you examine this libpq.dylib you will find that it binds to the
libssl and libcrypto libraries within @executable_path/../../Frameworks

Place that copy of libpq.dylib in your Omnis application bundles
Contents/MacOS folder, and place those copies of libssl.1.0.0.dylib and
libcrypto.1.0.0.dylib into your Omnis application bundles
Contents/Frameworks folder and suddenly you have a fully self contained
working copy of Omnis that support SHA256 and SHA512 certificate
validation.

Do keep in mind that these files do fall under the OpenSSL and
PostgreSQL licenses which to me are still mostly confusing as to what
you are, and aren't, allowed to do with them.

Cheers,

Bas

P.S. that all of the above I've only tested to the n-th degree on Studio
6.1.3, I have yet to do experimenting with Studio 8 but the guys in the
UK have assured me they've had this all working successfully on Studio 8.

P.S.s. to examine dylib files use the command:
otool -L name.dylib
And it will list dependencies, Lou, I owe you an apology because I
seriously underestimated that tool and you've been advocating it for
years :)