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:
Now you are almost ready to go. Your www_fdw understands SSL. There is now one more small problem of certificates to contend with.
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
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');
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');
ALTER SERVER www_fdw_google_search_server OPTIONS (SET cainfo 'D:/SSLCerts/ca-bundle.crt');