SSL connections fail using binary OSX package on CRAN

[peterfoley]

Using the binary OSX package on CRAN, connections fail to a Redshift server that requires SSL, but when the package is installed from source, the connections succeed.

When I attempt to require SSL with binary version, I get this error message, which points to SSL support missing in the CRAN binary compilation:

> library(DBI); library(RPostgreSQL); con <- dbConnect(dbDriver('PostgreSQL'), dbname="dbname=<dbname> sslmode=require", host="<host>", user="<user>, password="<password>", port=5439)
Warning message:
package ‘RPostgreSQL’ was built under R version 3.4.1 
Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (could not connect<user>@<host>:5439 on dbname "<dbname>": sslmode value "require" invalid when SSL support is not compiled in
)

[tomoakin]

As explained in https://forums.developer.apple.com/thread/65619, OpenSSL is depricated as a public API in macOS. So, current source, not including the SSL library code, will not be capable of SSL connection in stardard macOS environment. If you compile in a environment where libssl and libpq was made SSL activated form, then the driver can use SSL. The route needed to handle is relatively clear but not necessarily easy. We need to include a SSL library, after choosing which one to include.

[tomoakin]

With current license situation, it appears impossible to include the library code due to incompatibility of GPL and OpenSSL license. https://www.gnu.org/licenses/license-list.en.html#OpenSSL

OpenSSL is currently working to update the license to Apache License v. 2.0 https://www.openssl.org/blog/blog/2017/03/22/license/

However, still this is likely incompatible with GPL-2, the currently specified license. https://www.gnu.org/licenses/license-list.en.html#apache2

Thus, license change is necessary before redistributing libssl linked versions.

[chrisvoorpostel]

Can you provide more details on the workaround you describe above? See here: https://stackoverflow.com/questions/46076325/how-can-i-compile-rpostgresql-with-libssl-and-libpg-and-ssl-activation

[chrisvoorpostel]

After looking at this a bit more, it looks as though the issue (on Windows) is that the installer does not recognize an existing installation of libpq. My installed version has --with-openssl enabled, but source is compiled using the bundled libpq instead. When I install from source, I see the following line in the R console:

(cd libpq; make -f Makefile.win)
make[1]: Entering directory `/cygdrive/c/Users/xxxxxxxxx/AppData/Local/Temp/RtmpO6lkcB/R.INSTALL85028c8122b/RPostgreSQL/src-i386/libpq'
cp pg_config.h.win pg_config.h

I think on windows this line in configure is not working:

if test "${PG_CONFIG}" != ""; then

    # Use pg_config for header and linker arguments
    PG_INCDIR=`${PG_CONFIG} --includedir`
    PG_LIBDIR=`${PG_CONFIG} --libdir`

Am I on the right track? Is there any way to use an existing libpq install in Windows?

[tomoakin]

On windows, the configure is not used at all and configure.win is used, which is blank. Thus, currently on windows platform, presence of libpq installation is not sought at all, and the attached libpq is used. I am not sure if there is any way to link against external libpq, but if it is possible, writing configure.win could be done to check if there is a linkable libpq.

[writetoritu]

@peterfoley Did you find a solution to this issue? We are running into the same error (on OS X El Capitan).

[bvvamsik]

@tomoakin Can you please post a solution where we can compile or reinstall everything so that the connection works. (on how to make SSL to work on my Mac OS X El Capitan)

[tomoakin]

The direction is first prepare PostgreSQL that can connect through SSL (Perhaps, there are many ways to accomplish this and I do not have specific recommendation.). Then, compile the RPostgreSQL package from source using the libpq (and include files) that is used by the working PostgreSQL.

[sheyd]

Cross-posting solution I was able to find for macOS El Capitan users here: https://stackoverflow.com/a/48834537/8371976

I was able to solve this for the El Capitan macOS R users in my office, by doing the following:

• Remove RPostgreSQL R package if you already have installed. Methods vary on how to do this, but from either R.app console or R in Terminal, type remove.packages('RPostgreSQL')

• Make sure you have Homebrew installed, and from Terminal run: brew install libpq openssl ostgresql

• Open R.app, and from the Packages & Data menu, select Package Installer.

• From the first drop-down menu, choose CRAN (sources) (choose mirror closest to you if you haven't used this before).

• Using package search, find RPostgreSQL and for the options below, keep At System Level checked, and check Install Dependencies, then click Install Selected.

• Quit out of all R and RStudio programs, and try using the new from source installed RPostgreSQL package.

DISCLAIMER: If you have heavy compile dependencies on OpenSSL or libpq for other programs, I have no idea how doing the above may break other programs.

[lusava]

Hello @tomoakin, I'm trying to setup SSL connections in Windows, what do you mean by "prepare PostgreSQL that can connect through SSL" ?

[esilverberg]

sheyd's steps did not work for us on OSX High Sierra.

Instead, we did the following:

• Uninstall RPostgreSQL by typing remove.packages('RPostgreSQL') from within the RStudio terminal
• Make sure you have Homebrew installed, and from Terminal run: brew install libpq openssl postgresql
• Re-install RPostgreSQL from source by running: install.packages('RPostgreSQL', type = 'source')

[sheyd]

@esilverberg Thanks for the update, you are correct that the postgresql brew package was required. It was already pre-installed on the laptops I used the original solution on, and missed it as a dependency. Updated original post, thank you for putting the additional info here!