How can I connect to PostgreSQL database on a server through an SSH Tunnel using R?

Question

I am trying to connect an RStudio Session to a PostgreSQL database which is on a server (Digital Ocean) through a SSH Tunnel. I am new to the SSH Connections, specially using R.

Systems Information:

On my local computer, I am using Windows 10 x64. I have installed PostgreSQL 13, R 4.0.3, OpenSSH Server and OpenSSHD Client (for the Open SSH I am using the standard Windows versions).

On the Digital Ocean Server I have Ubuntu 20.04.1, postgresql 12+214ubuntu0.1, openssh-server 1:8.2p1-4ubuntu0.2

General Context:

First of all, I have already generated a .PEM key pair (I will refer to the passphrase for the private key as 'passp') private key and enabled the public key in the Digital Ocean Server (DO Server from now on). Also, the DO Server is already configured to allow SSH connections (example ip: 123.456.789.01 and port:1234).

With the configuration mentioned above, I was able to connect PgAdmin to the Postgresql database in the DO Server. I logged in to the database using the following parameters:

• dbname = "db"
• doserver_user = "root"
• db_user = "username"
• passqord = "pwd"

I have managed to connect to the DO Server from my PC using the CMD application. The command that I am using is the following:

ssh -i "C:/path/to/ssh/key" -L 5555:127.0.0.1:1234 [email protected]

When this connection is open, I of course can access the DO Server. To verify that the localport 5555 was running, I ran the command netstat -ao | find "5555" and received the following:

 TCP    127.0.0.1:5555         hp-PC:0                LISTENING       14132

Before moving to R, I double-checked the sshd_config file to make sure that TCPForwarding was allowed and that ListenAdress was configured for the localhost (127.0.0.1). I did this check in my PC and on the DO Server.

With all of this done, I went to RStudio and tried to connect to the Postgresql database on the DO Server using the following code:

library(DBI)                                   #DBI version 1.1.1
library(RPostgres)                             #RPostgres version 1.3.2.9000
con <- DBI::dbConnect(drv = RPostgres::Postgres(), 
                  dbname= "db",
                  host = "127.0.0.1",
                  port = "5555",         
                  user= "root",
                  password= "pwd")

I am using host: "127.0.0.1" and port "5555" because as far as I understand, it is the port that is listening and has already been forwarded to the DO Server IP (123.456.789.01).

The attempts and errors:

Error 1

I have tried a lot of different options for the code in R and therefore I have gotten several errors. With the initial code mentioned above I get the following error:

Error: SSL error: wrong version number
expected authentication request from server, but received S

I also receive the same error if I change host to "localhost", or user to "username" (the user with which I access the databsae, not DO Server).

Error 2

I tried calling a different port in the R code (For example changing Port to "5433") to see if something would happen and got the error:

could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5433?

This error also happens if I close the connection from the CMD application or if I change the value of Host to another IP (For example host = 111.222.333.44).

Error 3

As a desperation move, I tried creating a connection directly from RStudio. To do this, I used the following:

library(ssh)                                                              #ssh version 0.7.0
session <- ssh::ssh_connect(host= "[email protected]:22", keyfile = "C:/path/to/ssh/key")
ssh::ssh_tunnel(session, port = local_port, target = serv_host)

And got the result:

/ Waiting for connection on port 5555...

As R is single threaded, I could not run the DBI::dbConnect from the same session (session A). So I started a new session (session B) and ran the DBI::dbConnect as mentioned above. The results were:

#Session A
- Waiting for connetion on port 1090... client connected!
Error: libssh failure at 'channel_open_forward': Socket error: No error

#Session B
#No result, just got stuck processing

The Ask

I just want to be able to connect RStudio in my PC to the Postgresql database on the DO Server. I really do not mind if this is accomplished by pointing out something I am doing wrong with the configuration or code, or if there is a completely different way of doing this.

I will be very appreciative of all and any suggestions you could give me.

Answer 1

Apparently this has an easy solution which came courtesy of Daniel Verité.

It is important to call port "5432" as it is the default port where Postgresql listens to the server (in this case Digital Ocean). Therefore my final code in the CMD application looks as follows:

ssh "C:/path/to/sshkey" -L 5555:127.0.0.1:5432 [email protected]

Where:

• 5555 is the port in the local computer (can be assigned by the user to any number between 1,024 and 32,767) Port Numbers for SSH and Telnet Connections
• 127.0.0.1 indicates the localhost
• 5432, as mentioned above, is the default port where PostgreSQL listens to incoming connections
• 123.456.789.01 is an example IP that corresponds to the server IP
• As an additional note, if you configured a passphrase with your ssh-key, it will be requested immediatly after you run this line of code in the CMD application

Once I did that, R connected without problems using the code mentioned above.

Answer 2

psql is an interactive terminal program provided by PostgreSQL. You can do a lot with psql tool e.g., execute SQL statements, manage database objects, etc.

you can check this:

ssh -i "C:/path/to/ssh/key" -L 5555:127.0.0.1:1234 [email protected]

you can check more about the SSH Port here