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

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)

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.

Tags:

Ssh

R