Identify and close an SSH tunnel from R

Question

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!

Answer 1

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 [email protected] 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.

Answer 2

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