0

I am having trouble creating an SSL connection using RPostgreSQL to an AWS hosted PostgreSQL database.

Here is what I've tried so far:

  1. Created the PostgreSQL database on AWS.
  2. Set the database parameter "rds.force_ssl" to 1.
  3. Downloaded the AWS public key from https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
  4. Test the connection from a windows command prompt with psql (it works).

  5. Executed the following in R:

    library(RPostgreSQL)
cert <- paste0("C:/Users/johnr/Downloads/", "rds-combined-ca-bundle.pem")
dbname <- paste0("dbname=", "flargnog", " ", "sslrootcert=", cert, " ", "sslmode=verify-full")
host <- "xxxxxx.xxxxx.us-region-2.rds.amazonaws.com"
con <- dbConnect(dbDriver("PostgreSQL"), user="username", host=host, port=5432, dbname=dbname, password="abcd1234!")

I receive an error message after executing the last statement:

Error in postgresqlNewConnection(drv, ...) : 
RS-DBI driver: (could not connect username@xxxxxx.xxxxx.us-region-2.rds.amazonaws.com on dbname "flargnog"

If I change the rds.force_ssl setting to 0 (and remove the ssl stuff from dbname) the connection works just fine.

I have looked at other posts on Stackoverflow related to this issue. This and this seem to indicate an SSL connection is not possible due to issues with RPostgreSQL. However, this post indicates that you can.

Any guidance would be appreciated!

Community
  • 1
  • 1
John R
  • 503
  • 1
  • 5
  • 7

1 Answers1

0

You can try to ssh to the rds instance using e.g. putty and port-forward your local port 5432 to the remote port 5432. Once the ssh connection is open in R just connect to localhost:5432...

Here is how to port-forward using putty:
http://www.akadia.com/services/ssh_putty.html

Here is how this works via command-line: https://gist.github.com/magnetikonline/3d239b82265398568f31

P.S.: Make sure your instance is in a security-group that accepts ssh connections - port 22

Rentrop
  • 20,979
  • 10
  • 72
  • 100