How to connect to remote PostgreSQL with R, certificate validation required

Viewed 2856

I'm trying to connect to a remote Postgres database with ssl = verify ca mode. My problem seems to be similar to Connect to Redshift via SSL using R and Connect to Postgres via SSL using R, but they do not work properly. The error is always

Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect (null)@datadb1 on dbname "(null)"

My code is something like this

library("RPostgreSQL")
host = 'datadb1'
dbname = 'test'
port = 5432
password = pw
username = 'pep'

pg_dsn = paste0(
  'dbname=', dbname, ' ',
  'sslrootcert=', "C://root-ca.crt", ' ',
  "sslkey=C://pep.key", " ",
  "sslcert=C://pep.crt", 
  'sslmode=verify-ca'
)
dbConnect(RPostgreSQL::PostgreSQL(), dbname=pg_dsn, host=host, 
          port=port, password=password, user=username)

It's not a general database problem though, because I'm able to connect to the db using Python. Update: I had made a mistake in specifying the path; the error is actually this:

Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect pep@datadb1 on dbname "test") 
1 Answers
Related