Make an SSH tunnel to another computer via R to access postgreSQL table

Question

As part of my R workflow for one of my projects, I load in data from a postgreSQL table located on a remote server.

My code looks like this (anonymized credentials).

I first open an ssh connection to the remote server in terminal.

ssh -p Port -L LocalPort:IP:RemotePort servername"

I then connect to the postgres database in R.

# Load the RPostgreSQL package
library("RPostgreSQL")

# Create a connection
Driver <- dbDriver("PostgreSQL") # Establish database driver
Connection <- dbConnect(Driver, dbname = "DBName", host = "localhost", port = LocalPort, user = "User")

# Download the data
Data<-dbGetQuery(Connection,"SELECT * FROM remote_postgres_table")

This approach works fine, and I am able to download the data with no problems.

However, I would like to do the first step - i.e., creating the ssh connection - in R, rather than in terminal. Here is my attempt to do so, with accompanying error.

# Open the ssh connection in R
system("ssh -T -p Port -L LocalPort:IP:RemotePort servername")

# Load the RPostgreSQL package
library("RPostgreSQL")

# Create a connection
Driver <- dbDriver("PostgreSQL") # Establish database driver
Connection <- dbConnect(Driver, dbname = "DBName", host = "localhost", port = LocalPort, user = "User")

# Download the data
Data<-dbGetQuery(Connection,"SELECT * FROM remote_postgres_table")

Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

To clarify my question, I would like to perform this entire workflow (establish a connection, download postgreSQL data) entirely in R without any steps in terminal.

Answer 1

Solution with R packages only:

cmd <- 'ssh::ssh_tunnel(ssh::ssh_connect(host = "[email protected]:22", passwd = "m1C5jOZy"), port = 5555, target = "127.0.0.1:3306")'
pid <- sys::r_background(
    std_out = FALSE,
    std_err = FALSE,
    args = c("-e", cmd)
)
con <- DBI::dbConnect(
    drv = RMariaDB::MariaDB(),
    host = "127.0.0.1",
    port = 5555,
    user = "user",
    password = "pass",
    dbname = "db"
)
# do somehting
DBI::dbDisconnect(con)

Used sys, ssh package to make tunnel

See also this comment.

Answer 2

As per @r2evans suggestions.

##### Starting the Connection #####
# Start the ssh connection to server "otherhost"
system2("ssh", c("-L8080:localhost:80", "-N", "-T", "otherhost"), wait=FALSE)

You can kill the process by manually finding and typing in the pid or automatically by killing all pids matching your server name. Be warned that you only want to use this latter version if you're using a relatively unique server name that is unlikely to be duplicated in other processes.

##### Killing the Connection: Manually #####
# To end the connection, find the pid of the process
system2("ps",c("ax | grep otherhost"))
# Kill pid (x) identified by the previous grep.
tools::pskill(x)

##### Killing the Connection: Automatically #####
# To end the connection, find the pid of the process
GrepResults<-system2("ps",c("ax | grep otherhost"),stdout=TRUE)
# Parse the pids from your grep into a numeric vector
Processes<-as.numeric(sub(" .*","",GrepResults)) 
# Kill all pids identified in the grep
tools::pskill(Processes)

Answer 3

As an alternative you may use plink with shell

library(RPostgreSQL)
drv  <- dbDriver("PostgreSQL")

cmd<- paste0(
  "plink ",
  # use key and run in background process
  " -i ../.ssh/id_rsa -N -batch  -ssh",
  # port forwarding
  " -L 5432:127.0.0.1:5432",
  # location of db
  " [email protected]"
)

shell( cmd, wait=FALSE)
# sleep a while before the the connection been established. 
Sys.sleep(5)

conn <- dbConnect(
  drv,
  host = "127.0.0.1",
  port=5432,
  dbname="mydb",
  password = "pass"
)

dbListTables(conn)