Using SSL https connections with www_fdw on windows

One of the foreign data wrappers I included in the PostgreSQL 9.3 Windows FDW bag and PostgreSQL 9.4 Windows FDW bag is the www_fdw extension used for querying web services. Someone asked that since I didn't build curl with SSL support, they are unable to use it with https connections. The main reason I didn't is that the EDB installs come with ssleay32.dll and libeay32.dll (even the 64-bit) which are dependencies of curl when built with SSL support. I wanted to minimize the issue of distributing dlls that are packaged with Windows PostgreSQL installers already.

Though this article is specific to using www_fdw on Windows systems, many of the issues are equally applicable to other platforms, so may be worth a read if you are running into similar issues with using specialty SSL certificates on Linux/Unix/Mac.

You'll know you have curl built without ssl support, because when you try to create an https Foreign server, you'll get this error from WWW_FDW

ERROR:  Can't get a response from server: Protocol "https" not supported or disabled in libcurl

Unfortunately curl is not distributed with the EDB PostgreSQL windows builds. However you can easily swap out the non-ssl enabled curl binary I included in the FDW package with an SSL enabled one and get SSL support in the www_fdw as well as the ogc_fdw driver we included. Here's what you do:

Swapping out the non-ssl enabled curl with ssl-enabled curl

  1. Download one of the windows binaries from http://curl.haxx.se/download.html. I'm running PostgreSQL 9.4 64-bit, so I chose Win64 - Mingw64 one. I chose it, not because it was Mingw64, but because it had the fewest number of dependencies.
  2. This particular package also includes ssleay32.dll and libeay32.dll, but since EDB already has those libraries, I did not copy those files. The curl library is called libcurl.dll, but the name I bound with is libcurl-4.dll. So to swap out the one I provide with this SSL enabled one, just rename the libcurl.dll to libcurl-4.dll and then copy into your PostgreSQL/9.4/bin (or 9.3 if on 9.3) folder

Now you are almost ready to go. Your www_fdw understands SSL. There is now one more small problem of certificates to contend with.

Working with SSL certificates

Even after you install the version of curl with SSL support, if you create your www FDW server and foreign table with the Google search example code here: https://github.com/cyga/www_fdw/wiki/Documentation, you might get the error:

ERROR:  Can't get a response from server: SSL certificate problem: unable to get local issuer certificate

The simplest way I've discovered to resolve this issue on windows is to explicitly specify the certificate as part of the WWW FDW Server definition. So here's what you do

  1. Download the ca-bundle.crt described in http://curl.haxx.se/docs/caextract.html
  2. Copy that into a folder accessible by the postgres service account (you might have to grant permissions). I like to put it in a folder like C:\SSLCerts
  3. Now create your WWW FDW Server with this SQL command and pass in the certificate using the www_fdw cainfo option

    CREATE SERVER www_fdw_google_search_server FOREIGN DATA WRAPPER www_fdw 
        OPTIONS (uri 'https://ajax.googleapis.com/ajax/services/search/web?v=1.0', 
            cainfo 'C:/SSLCerts/ca-bundle.crt');
  4. If you had already created the SERVER and don't want to have to drop your Foreign tables, you can update the SERVER, as you can with any FDW server, with a command like this:

    ALTER SERVER www_fdw_google_search_server OPTIONS (ADD cainfo 'C:/SSLCerts/ca-bundle.crt');

    If you need to change the path of the certificate, say you moved it to D, you can use the SET command instead of ADD to change an exisiting option:
    ALTER SERVER www_fdw_google_search_server OPTIONS (SET cainfo 'D:/SSLCerts/ca-bundle.crt');