Can not connect PostgreSQL(over SSL) with RPostgresql on Windows
It's related to this issue: Can't create dbConnect to Postgres with SSL. One solution that worked for me (also with a Heroku DB) is to use the RPostgres package instead. You can install the dev. version from Github: https://github.com/rstats-db/RPostgres.
How to connect to remote PostgreSQL with R, certificate validation required
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?
Related Topics
Count Unique Combinations of Values
Implementation of Skyline Query or Efficient Frontier
How to Ignore Na in Ifelse Statement
How to Define Fill Colours in Ggplot Histogram
Extracting Indices for Data Frame Rows That Have Max Value for Named Field
Tm_Map Has Parallel::Mclapply Error in R 3.0.1 on MAC
R Shiny Dt - Edit Values in Table with Reactive
Accessing Parent Namespace Inside a Shiny Module
Automated Formula Construction
Creating New Shape Palettes in Ggplot2 and Other R Graphics
Using Lm in List Column to Predict New Values Using Purrr
Shiny Rcharts Multiple Chart Output
Join Two Data Tables and Use Only One Column from Second Dt
Parallel Processing in R Limited