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

[lokeshjoshi]

I am trying to connect an RStudio Session to a PostgreSQL database which is on a server (Godaddy) through an SSH Tunnel. I am new to SSH port , especially using R Programming Language. I'm using Windows 10 x64. I have installed PostgreSQL 13, R 4.0.3, OpenSSH Server, and OpenSSH 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.

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 root@123.456.789.01

When this connection is open, I of course can access the DO Server. To verify that the local port 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

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 a server, but received S

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

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.

Tank You

 

[devops]

Based on the error message you received, it seems that the SSL version of the PostgreSQL database does not match the SSL version expected by the RPostgreSQL package in R. One possible solution is to disable SSL encryption in the RPostgreSQL package by setting the sslmode parameter to "disable" in the dbConnect() function.

Here's an example code snippet that should work:

library(RPostgreSQL)

# Set the parameters for the SSH tunnel
ssh_user <- "root"
ssh_host <- "123.456.789.01"
ssh_port <- 22
ssh_key_file <- "C:/path/to/ssh/key"
local_port <- 5555
remote_port <- 5432

# Set the parameters for the PostgreSQL database
db_name <- "db"
db_user <- "username"
db_password <- "pwd"

# Start the SSH tunnel
system(paste0("ssh -i '", ssh_key_file, "' -L ", local_port, ":localhost:", remote_port, " ", ssh_user, "@", ssh_host, " -p ", ssh_port))

# Connect to the PostgreSQL database through the SSH tunnel
con <- dbConnect(RPostgreSQL::PostgreSQL(),
                  dbname = db_name,
                  host = "127.0.0.1",
                  port = local_port,
                  user = db_user,
                  password = db_password,
                  sslmode = "disable")

# Execute a query to test the connection
result <- dbGetQuery(con, "SELECT * FROM mytable")

# Close the connection
dbDisconnect(con)

In this code, we first start the SSH tunnel using the system() function, which runs a command in the shell. We use the ssh command to set up the tunnel, with the -i parameter specifying the path to the SSH key file, the -L parameter specifying the local and remote ports to forward, and the SSH user and host information. Once the tunnel is established, we can connect to the PostgreSQL database using the dbConnect() function from the RPostgreSQL package, with the host parameter set to "127.0.0.1" and the port parameter set to the local port we forwarded through the tunnel. We also set the sslmode parameter to "disable" to avoid SSL version compatibility issues. Finally, we execute a simple query to test the connection, and close the connection using the dbDisconnect() function.