0

I connect to a PostgreSQL DB via RPostgres to perform queries.

My current workflow is the following:

  1. In the terminal, I create a tunnel via ssh.

    ssh -L PORT:host:25060

  2. On top of my R script, I create the DB connection:

    # use RPostgres library
require(RPostgres)

# create db instance 
db = dbConnect(
   Postgres(), 
   user = 'user',
   password = 'password',
   dbname = 'dbname',
   host = '127.0.0.1',
   port = 5433,
   sslmode = 'require'
   )

  3. I then query the database.

Every time I relaunch the R session or connect to a different tunnel, or I want to schedule a script, this procedure doesn't work.

Is there a way to incorporate the tunnel creation in thee R script? Moreover, how to properly encrypt the password and sensitive information?

Best,

chopin_is_the_best
  • 1,951
  • 2
  • 23
  • 39
  • Did you try to creat the tunnel using `system` command? https://stat.ethz.ch/R-manual/R-devel/library/base/html/system.html For the password, it will always be stored in clear "somewhere", to you must ensure that this "somewhere" is not accessible from outside – Chelmy88 Mar 30 '20 at 15:13
  • I successfully created the tunnel via `system()`. When I run the command executing it normally in the script, it works and opens the tunnel. When I `source('')` the previous script on top of another R script I get an error like: `bind [127.0.0.1]:5433: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 5433 Could not request local forwarding.` I still am able to create the connection tho. – chopin_is_the_best Apr 01 '20 at 15:10

0 Answers0