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

Question

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

Answer 1

According to the error message, the problem is that you're passing empty values for the username and database name. That suggests your actual code doesn't match what you've entered here. I would write a 10-line Rscript program that just connects and grabs a bit of data, like this:

#!/usr/bin/Rscript

library("RPostgreSQL")
host = '192.168.36.2'
dbname = 'test'
port = 5432
password = 'secret'
username = 'pep'

pg_dsn = paste(
                'dbname=', dbname, ' ',
                'sslrootcert=', 'rootCA.pem', ' ',
                'sslkey=pem.key', ' ',
                'sslcert=pem.crt', ' ',
                'sslmode=verify-ca',
                sep=""
                )

conn <- dbConnect(RPostgreSQL::PostgreSQL(), dbname=pg_dsn, host=host,
          port=port, password=password, user=username)
rs <- dbSendQuery(conn, statement="SELECT COUNT(*) FROM users")
data <- fetch(rs, n=1)
dim(data)

So I don't think this is related to SSL certs at all, but the fact that your variables aren't being set the way you think they are.

EDIT: I created my own CA and used it to sign a server cert and a client cert. I put Postgres 9.3 on a fresh VM and have connections working, with certs required on both sides. I can connect with both psql and R. So I'm afraid I can't reproduce your problem. But a few things look suspicious in your code:

• You only need one forward slash in your paths, not two. (If you were using backslashes you'd need two.)

• You need a space before sslmode, like this:

  'sslcert=pem.crt', ' ',
  

  not this:

  'sslcert=pem.crt',
  

Do either of those changes fix your problem?