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.

system2("ssh", c("-L8080:localhost:80", "-N", "-T", "otherhost"), wait=FALSE) worked for me on linux. Doesn't work on windows, though, likely due to lack of fork, so you might need something in the background (such as parallel or future to run another R session). Stopping it might work with tools::pskill, haven't tested. — r2evans
– r2evans, Commented Jul 5, 2016 at 21:35

Andy · Accepted Answer · 2016-12-13 16:32:14Z

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)

Artem Klevtsov · Accepted Answer · 2019-03-29 10:51:38Z

4

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

Comments

AzizSM · Accepted Answer · 2017-11-09 03:51:35Z

1

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)

edited Nov 9, 2017 at 3:51

answered Nov 8, 2017 at 8:37

AzizSM

6,3594 gold badges45 silver badges54 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related