R RPostgreSQL Connect to Remote Postgres Database with SSL

Question

I am trying to connect to a remote PostgreSQL database from within R using the RPostgreSQL package, and I am getting errors that appear to be related to the SSL settings for the connection. I have verified that I can connect from the command line using psql, so I know the connection is valid and accessible from my computer.

My first attempt at connecting in R was the following (where <MyHost> and <MyPass> were filled out appropriately for my connection):

library(RPostgreSQL)
pg <- dbDriver( 'PostgreSQL' )
con = dbConnect( pg, host=<MyHost>, dbname='warehouse', 
                 user='warehouse', password=<MyPass>, port=5432 )

The error I received in R was:

RS-DBI driver: (could not connect warehouse@<MyHost>.com:5432 on 
dbname "warehouse": FATAL:  no pg_hba.conf entry for host "75.166.243.177", 
user "warehouse", database "warehouse", SSL off

I understood this to mean that I needed to apply SSL encryption to my connection, and so I tried the following, which seems to have worked for the OP here: Connect to Postgres via SSL using R

library(RPostgreSQL)
pg <- dbDriver( 'PostgreSQL' )
con = dbConnect( pg, host=<MyHost>, dbname='dbname=warehouse sslmode=require', 
                 user='warehouse', password=<MyPass>, port=5432 )

The error I received then was:

RS-DBI driver: (could not connect warehouse@<MyHost>:5432 on dbname 
"warehouse": sslmode value "require" invalid when SSL support is not 
compiled in

I found this post (Postgres SSLMode Value "require" Invalid When SSL Support Is Not Compiled Using Foreign Data Wrapper) that suggested my build of PostgreSQL may not have had the --with-openssl flag included when it was compiled, but running pg_config revealed that this flag was indeed set in my build. This makes sense because I was able to connect to the database using SSL when I used the psql command line tool.

Fearing my PostgreSQL installation may be at fault, I completely removed it. I then ran both of the above snippets of R code, just for kicks, and I received exactly the same errors. I was not expecting this, but this seems to indicate that either (a) I do not need a local install of PostgreSQL in order to use the RPostgreSQL package or (b) I have a confounding install of PostgreSQL somewhere else on my computer that was not built with the --with-openssl flag included. I have tried locate postgresql at the command line and didn't see anything obvious that looked like a separate install. In any case, I still haven't been able to successfully connect to the remote PostgreSQL database from within R.

I've scoured Stack Overflow for insight here, and it seems there are a number of unresolved questions of a similar nature, where users are unable to establish connections with a remote server for some reason. I tried to install the RPostgres package that Kirill Müller is maintaining here (https://github.com/rstats-db/RPostgres), but I could not get the R package install to recognize my local PostgreSQL install, even after trying to specify the include and lib paths manually using R CMD INSTALL, as the resulting error message suggested (I may have missed something here though...). It sounds like some users have had better luck with RPostgres than with RPostgreSQL, but again, I wasn't able to install this package.

I'm at a loss for what else to do, and am going to have to resort to other technology if I can't figure out how to establish a connection soon. Any help would be greatly appreciated.

Answer 1

Thanks for posting! You actually helped us solve it. Here is how we did it:

• Reboot your Mac into Recovery Mode by restarting your computer and holding down Command+R until the Apple logo appears on your screen.
• Click Utilities > Terminal.
• In the Terminal window, type in csrutil disable and press Enter.
• Restart your Mac.
• Open a Terminal.
• Run sudo rm /usr/lib/libpq.5.dylib
• Run sudo ln -s /Applications/Postgres.app/Contents/Versions/9.6/lib/libpq.5‌​.dylib
• Now you can connect to the DB.