0

While I currently have a workaround method, I feel like there has got to be a better way to do this, something like SSHTunnelForwarder in Python.
(found here) https://sshtunnel.readthedocs.io/en/latest/

My current workaround:

1) Write a /.ssh/config file that specifies local port forwarding options.

2) In a terminal window, execute ssh -N location_of_db

3) In R, run the following: 

library(RPostgres)

drv <- RPostgres::Postgres()

dbName <- "my_database"
host <- "127.0.0.1"
port <- '5439'
user <- "username"

db <- dbConnect(drv, dbname=dbName, host=host, port=port, user=user,
           password=readLines("/Users/me/keys/db_password.txt"))

Is there an all R way to do this?

Note: In lieu of 1) and 2) you can just connect and set up local port forwarding with -L option on the ssh command (now in the comments), but this requires that you locate your ssh-agent for the db and provide other security info, as set up by the sys admin.

Taylor Pellerin
  • 127
  • 1
  • 6
  • 1
    I don't know of any R implementation of `ssh`, nor would I want to. If you really want it, you would need to (1) fork a separate R process, so that you could (2) run something like `system2("ssh", c("-L", "5439:localhost:5439", "-N", host)`, having it stay active the whole time you're working. Seems a bit inefficient, I would suggest (and in fact I do this daily) just using a simple terminal (such as mintty or xterm) with `ssh`-proper. – r2evans Jan 23 '18 at 05:59
  • BTW: the use of `-L` has nothing to do strictly with the `id_rsa` file. Additionally, if you use something like `ssh-agent` (or `KeePass` with the `KeeAgent` plugin), you can find ways around that, too. My norm is `ssh -L lport:host:rport myhost` (with or without `-N`) on a regular basis. And though suggested (esp when connecting to the same location repeatedly), the use of `~/.ssh/config` is also not required. – r2evans Jan 23 '18 at 06:02
  • @r2evans thanks for the replies. Given how smoothly SSHPortForwarder works in Python I hoped to replicate the process in R... I love the tidyverse! I attempted following a path like that workaround, it was not really fruitful... I'll just stick with bash! Thanks for the 'BTW', I'm aware of this, just was using one in my case but edited the post to make that more obvious to other readers. Since I am only connecting to one place on this machine the config is convenient :) – Taylor Pellerin Jan 23 '18 at 07:50
  • I definitely see some utility in it for some, when a terminal is otherwise not used or cluttering. There actually is a package (I was shocked!) called [`ssh.utils`](https://github.com/collectivemedia/ssh.utils), but it hasn't seen an update in years, so caveat emptor. The concept of "background processes" is not new to R; see [`rmote`](https://github.com/cloudyr/rmote), [`opencpu`](https://cran.r-project.org/web/packages/opencpu/index.html), even [`blogdown`](https://cran.r-project.org/web/packages/blogdown/index.html) (via [`servr`](https://cran.r-project.org/web/packages/servr/index.html)). – r2evans Jan 23 '18 at 17:38
  • Additionally, https://stackoverflow.com/a/34906738/3358272 suggests that `RCurl` supports `scp` commands; it might be possible to extend it. (Regardless, this question appears to be a duplicate of that one, albeit no accepted answer yet, or this one https://stackoverflow.com/q/17347450/3358272.) – r2evans Jan 23 '18 at 17:41
  • Possible duplicate of [how do you connect to a remote server with ssh in R](https://stackoverflow.com/questions/17347450/how-do-you-connect-to-a-remote-server-with-ssh-in-r) – r2evans Jan 23 '18 at 17:43
  • I was finding that `system` wasn't cutting it, as it was not accepting using it as a pseudo-terminal. I was having an issue was the lack of a timely update as well. I might play with the `scp` but at this point. I am happy with my current solution. Given the age and lack of resolution, I guess I can see why these questions did not pop up as possible duplicates while I was writing/searching – Taylor Pellerin Jan 23 '18 at 19:11
  • I just did `system2("ssh", c("somehost", "ls"))` (with ssh-agent and a configured host), and it worked without warnings (win10, OpenSSH_7.6p1, OpenSSL 1.0.2m 2 Nov 2017). What's your OS? – r2evans Jan 23 '18 at 19:16
  • BTW: [`RPostgres`](https://cran.r-project.org/web/packages/RPostgres/index.html) and [`RPostgreSQL`](https://cran.r-project.org/web/packages/RPostgreSQL/index.html) are completely different packages, use one or the other. They are different implementations for the same purpose, connecting (via [`DBI`](https://cran.r-project.org/web/packages/DBI/index.html)) to a postgres sql server. In your code, you never use (or need) `library(RPostgreSQL)`. Secondly, the recommendation is `library(DBI)` and never `library()`, since everything else is handled internally. – r2evans Jan 23 '18 at 19:19
  • Im on OSX 10.12.6, and I used `system` not `system2` which may have been the issue – Taylor Pellerin Jan 23 '18 at 19:46
  • I don't really like either one, to be honest. They are both a bit poor in that they rely on the user to do shell-quoting for all arguments. (Try `system("ssh remotehost touch '/tmp/hello world'")` or `system2("ssh", c("remotehost", "touch", "/tmp/hello world"))` or even `system2("ssh", c("remotehost", "touch", "'/tmp/hello world'"))`.) – r2evans Jan 23 '18 at 20:39
  • I'll give it a go when I'm back at work, thanks for the help! – Taylor Pellerin Jan 23 '18 at 21:12

0 Answers0