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.
-L 5555:127.0.0.1:1234
: why 1234? This must be the port number of Postgres on the remote. By default it's 5432.