Connect to Redshift via Ssl Using R

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)

Connect R to Redshift with RPostgreSQL on Mac 10.11.3

Taking a shot in the dark here. Have you tried RPostgres?

Install it like so

# install.packages("devtools")
devtools::install_github("RcppCore/Rcpp")
devtools::install_github("rstats-db/DBI")
devtools::install_github("rstats-db/RPostgres")

Then test it out

library(DBI)
library(RPostgres)

con <- dbConnect(RPostgres::Postgres(),
           host = "XXXXXXXX.us-east-1.redshift.amazonaws.com",
           port = 5439,
           user = "XXXXXXXX",
           password = "XXXXXXXX",
           dbname = "db1")

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

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