Connect to Postgres via SSL using R
Instead of passing verify-full
to sslmode
, try require
or allow
:
dbConnect(dbDriver('PostgreSQL'),
dbname = 'dbname=foobar sslmode=require',
host = 'foobar.redshift.amazonaws.com',
port = 5439,
user = 'foobar',
password = 'foobar')
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?
Connect to Redshift via SSL using R
simply do:
host = 'redshift-name.xxxxxxxxxxxx.eu-west-1.redshift.amazonaws.com'
dbname = 'your_db_name'
port = 3306
password = 'hunter2'
username = 'rs_user'
redshift_cert = paste0(FILE_PATH, 'redshift-ssl-ca-cert.pem')
pg_dsn = paste0(
'dbname=', dbname, ' ',
'sslrootcert=', redshift_cert, ' ',
'sslmode=verify-full'
)
dbConnect(RPostgreSQL::PostgreSQL(), dbname=pg_dsn, host=host, port=port, password=password, user=username)
Related Topics
Adding X and Y Axis Labels in Ggplot2
How to Add New Column to an Dataframe (To the Front Not End)
Data.Frame Merge and Selection of Values Which Are Common in 2 Data.Frames
Take Sum of a Variable If Combination of Values in Two Other Columns Are Unique
R Plotting Confidence Bands with Ggplot
Finding Row Index Containing Maximum Value Using R
Removing Multiple Columns from R Data.Table with Parameter for Columns to Remove
How to Change Order of Array Dimensions
Reading Global Variables Using Foreach in R
Emoticons in Twitter Sentiment Analysis in R
How to Make Grouped Layout in Igraph
Ggplot2: How to Use Same Colors in Different Plots for Same Factor
Using Two Scale Colour Gradients on One Ggplot