3

I need to connect in R to a DB that uses an SSH tunnel. Based on googling SO tells me that I have to create an SSH tunnel first, then connect to my DB, but there is little on how to safely close the tunnel after executing my SQL.

Example:

if(!require(RPostgreSQL)) install.packages("RPostgreSQL")

# Create tunnel
myssh<-"ssh -f <server_user>@<server_ip>  -L <unused_local_port>:localhost:<database_remote_port> -N"
system(myssh)

# Connect to DB
drv  <- dbDriver("PostgreSQL")
conn <- dbConnect(
  drv,
  host = "127.0.0.1", user = "postgres",
  password = "", dbname = "mydb",
)

# How to close tunnel?

How to correctly detect and close the SSH tunnel created by R?

PS I'm on Windows!

Community
  • 1
  • 1
Steph Locke
  • 5,951
  • 4
  • 39
  • 77
  • Steph! Before I go dust off my Windows VM, does `server_user` have the authority to run scripts on the remote (I'm assuming) linux system or just establish a tunnel? – hrbrmstr Feb 24 '16 at 12:11
  • @hrbrmstr `server_user` can indeed do stuff on the linux box – Steph Locke Feb 24 '16 at 12:55
  • 1
    maybe the article [Auto-closing SSH tunnels](http://www.g-loaded.eu/2006/11/24/auto-closing-ssh-tunnels/) has the solution. – bdecaf Feb 24 '16 at 13:28
  • @bdecaf that is totally the solution. Can you post it as an answer so I can chuck some points your way? – Steph Locke Feb 24 '16 at 13:35

2 Answers2

4

I found a nice article at Auto-closing SSH tunnels - it suggests using such code to connect:

ssh -f -L 3306:127.0.0.1:3306 me@remote.example.org sleep 10;

It uses a feature that ssh will not close a connection while in use. It will first wait 10s - and if there the connection gets used close the connection once it is finished.

bdecaf
  • 4,652
  • 23
  • 44
1

On Mac and Linux you can close it as any other background ssh connection:

ps -ef | grep ssh

and then

kill -9 pid

I dont't know if there is ps on windows

Rentrop
  • 20,979
  • 10
  • 72
  • 100
  • Is that an interactive process or could you extract a pid from the `ps` and pipe it into the `kill`? – Steph Locke Feb 24 '16 at 13:08
  • You can always extract the pid from Terminal into R by running `system("ps aux | grep ssh", intern=TRUE)` (it's the first number in the row, i.e., the entry after your computer's username). – rvrvrv Aug 19 '17 at 20:27