Search code examples
rpostgresqlsslsshssh-tunnel

Connect to database PostSQL using ssh tunnel in R

Objective is to connect R to PostgreSQL using an SSH tunnel.

Just had unsuccessful tries with libraries RPostgreSQL and DBI. I'm not finding a way to pass along SSH tunneling parameters (proxy host, proxy user, and private key).

I wonder if there's a way to somehow specify those SSH parameters along in db query string? Maybe another way out?

Solution

  • Here's a code sample that I've used:

    First, setup an ssh tunnel - this would be for an AWS EC2 instance:

    ssh -i 'YOURKEY.pem' -N -L 1234:YOURDATABASEHOST:5432 YOURUSER@YOURAWSINSTANCE

    Then in R:

    library(RPostgreSQL)

conn <- dbConnect(RPostgreSQL::PostgreSQL(), dbname = 'MYREMOTEDBNAME', host = '127.0.0.1', port = 1234, password = 'MYREMOTEPASSWORD', user = 'MYREMOTEUSER')

    As you can see, the key is to setup a tunnel between a remote instance of some kind and the remote database. Then you use this locally hosted tunnel (hence, 127.0.0.1 as your host) and the specified port (1234 in this case).