search

ankane / dbx

A fast, easy-to-use database library for R
Other
187 stars 15 forks source link

R postgres connection error using dbxConnect #26

Closed dcaud closed 3 years ago

dcaud commented 3 years ago

I've got a postgres database hosted on Digital Ocean. On a server, I use the below R code without error and am able to connect. However, on my local machine (Mac, M1, 11.2.3) I get an error. Any ideas?

library(DBI)
library(RPostgres)
library(dbplyr)
library(dbx)

con <- dbxConnect(adapter = "postgres",
                  host = "db-postgresql-XXX.ondigitalocean.com",
                  port = 11111,
                  dbname = "XXXX",
                  user = "XXXX",
                  password = "XXXXX")

The error I get is:

Error: FATAL: no pg_hba.conf entry for host "XXX", user "XXXX", database "XXXX", SSL off

p.s. I've replaced sensitive stuff in the above with XXX etc.

ankane commented 3 years ago

Hey @dcaud, that's a general Postgres error. You'll need to update the pg_hba.conf file on your Postgres server to allow connections from your IP address. You should be able to find some tutorials online for how to do that.

dcaud commented 3 years ago

Thanks for the quick response @ankane. This configuration worked before. I guess Digital Ocean could have changed something (because they "manage" the database). But that seems quite unlikely. It seems more likely to be a local config issue or something with one of the R libraries that may have changed.

dcaud commented 3 years ago

It seems that I may need to have "?sslmode=require" appended to the connection string. Can you advise how to test that with dbx? And I may also need to reference a ca.cert file that I can get. Any advice would be appreciated. I really just don't understand what changed to make my prior code not work anymore!

dcaud commented 3 years ago

It seems that the issue is with a new version of rpostgres:

https://github.com/r-dbi/RPostgres/issues/291

ankane commented 3 years ago

Nice find, looks like that issue is now resolved.