12

I'm currently trying to connect to a redshift database in R. This needs to be done over an SSL connection but I can't seem to find options to specify the path of the certificate file to use in dbConnect. Google hasn't been to helpful either surprisingly enough.

Is it really that difficult establish a postgres SSL connection to redshift via R or am I just missing out on something fundamental?

Michael Aquilina
  • 5,352
  • 4
  • 33
  • 38

1 Answers1

9

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)
Thomas Grainger
  • 2,271
  • 27
  • 34