7

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 postgres@192.168.1.179 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.

Community
  • 1
  • 1
Dean MacGregor
  • 11,847
  • 9
  • 34
  • 72
  • Did you find any solution? I asked http://stackoverflow.com/questions/38942118/connect-postgresqlover-ssl-with-rpostgresql-on-windows, I think it's same issue – Jaehyun Shin Aug 14 '16 at 12:14
  • @JaehyunShin The problem is with the windows postgres driver so this is unlikely to ever be fixed. If you use the RODBC library instead then you can access your database so it isn't as if you can't access your database. – Dean MacGregor Aug 15 '16 at 15:17
  • It doesn't work on Mac either. Just use the RPostgres package instead. – Brian D Jan 27 '21 at 22:10

1 Answers1

2

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")
)
T.C. Helsloot
  • 151
  • 1
  • 7