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.

Did you find any solution? I asked stackoverflow.com/questions/38942118/…, I think it's same issue — Jaehyun Shin
– Jaehyun Shin, Commented Aug 14, 2016 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
– Dean MacGregor, Commented Aug 15, 2016 at 15:17
It doesn't work on Mac either. Just use the RPostgres package instead. — Brian D
– Brian D, Commented Jan 27, 2021 at 22:10

T.C. Helsloot · Accepted Answer · 2020-02-18 14:03:10Z

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")
)

answered Feb 18, 2020 at 14:03

T.C. Helsloot

1711 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can't create dbConnect to Postgres with SSL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related