Can't create dbConnect to Postgres with SSL

Question

I'm running a Postgres-9.4 server that I would like to require SSL for. When I connect to the Postgres server from my laptop with either pgadmin or windows odbc connection, it works with SSL. However when I try to connect with R using SSL it fails.

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, 
                 user = "postgres", 
                 password = mypasswd, 
                 dbname = "dbname=postgres sslmode=prefer",
                 host = "192.168.1.179")

If I set my pg_hba.conf to allow non-ssl connections then this will work. When I set it to only allow SSL connections then this will fail. Unfortunately dbConnect doesn't have a verbose option so I don't get anything more than could not connect [email protected] on dbname "postgres"

I found this question which seems to suggest I'm doing the right thing but, no go.

Edit:

I did some more digging and found this discussion which suggests that this won't work on windows due to various library/dll issues. That discussion is a few years old at this point so perhaps it has been resolved. I can confirm that doing the above from linux does work.

Answer 1

My understanding of the problem is that RPostgreSQL uses a client that doesn't support SSL.

I switched to the package RPostgres and got it to work. I first installed RPostgres with install.packages('RPostgres') then used this package to connect with sslmode="require".

library('RPostgres') #Instead of library('RPostgreSQL')
con <- dbConnect(RPostgres::Postgres(),
                 user = "postgres", 
                 password = mypasswd, 
                 dbname = "postgres",
                 host = "192.168.1.179",
                 sslmode = "require")
)