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

Question

I am trying to connect to a remote Postgres database using ssl = verify ca . My problem seems to be like Connecting to Redshift via SSL using R and Connecting to Postgres via SSL using R , but they do not work properly. Error always

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

My code is 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) 

This is not a common database problem, because I can connect to db using Python. Update . I made a mistake in showing the way; this is actually a mistake:

 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 are passing empty values ​​for the username and database name. This suggests that your actual code does not match what you entered here. I would write a 10-line Rscript program that simply connects and captures some data, for example:

 #!/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) 

Therefore, I do not think that this is due to SSL certificates in general, but the fact that your variables are not set the way you think.

EDIT . I created my own certification authority and used it to sign the server certificate and client certificate. I put Postgres 9.3 on a new virtual machine and worked with connections, and certificates are required on both sides. I can connect to psql and R. Therefore, I'm afraid I can not reproduce your problem. But a few things look suspicious in your code:

• You only need one slash in your paths, not two. (If you used a backslash, you'll need two.)

• You need space in front of sslmode , for example:

   'sslcert=pem.crt', ' ', 

  Not this:

   'sslcert=pem.crt', 

Does any of these changes fix your problem?