☰

Wie kann ich mit R über einen SSH-Tunnel eine Verbindung zu einer PostgreSQL-Datenbank auf einem Server herstellen?

tag-icon • tag-icon cmd.exe openssh postgresql ssh-tunnel rstudio
Wie kann ich mit R über einen SSH-Tunnel eine Verbindung zu einer PostgreSQL-Datenbank auf einem Server herstellen?

Ich versuche, eine RStudio-Sitzung über einen SSH-Tunnel mit einer PostgreSQL-Datenbank zu verbinden, die sich auf einem Server (Digital Ocean) befindet. Ich bin neu bei SSH-Verbindungen, insbesondere bei der Verwendung von R.

Systeminformationen:

Auf meinem lokalen Computer verwende ich Windows 10 x64. Ich habe PostgreSQL 13, R 4.0.3, OpenSSH Server und OpenSSHD Client installiert (für Open SSH verwende ich die Standard-Windows-Versionen).

Auf dem Digital Ocean Server habe ich Ubuntu 20.04.1, postgresql 12+214ubuntu0.1, openssh-server 1:8.2p1-4ubuntu0.2

Allgemeiner Kontext:

Zunächst einmal habe ich bereits ein .PEM-Schlüsselpaar (ich werde die Passphrase für den privaten Schlüssel als „passp“ bezeichnen) mit privatem Schlüssel generiert und den öffentlichen Schlüssel im Digital Ocean Server (ab jetzt DO-Server) aktiviert. Außerdem ist der DO-Server bereits so konfiguriert, dass SSH-Verbindungen zugelassen werden (Beispiel-IP: 123.456.789.01 und Port: 1234).

Mit der oben genannten Konfiguration konnte ich PgAdmin mit der Postgresql-Datenbank im DO-Server verbinden. Ich habe mich mit den folgenden Parametern bei der Datenbank angemeldet:

  • Datenbankname = "Datenbank"
  • doserver_user = "root"
  • db_user = "Benutzername"
  • passqord = "pwd"

Ich habe es geschafft, von meinem PC aus mithilfe der CMD-Anwendung eine Verbindung zum DO-Server herzustellen. Der von mir verwendete Befehl ist der folgende:

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

Wenn diese Verbindung geöffnet ist, kann ich natürlich auf den DO-Server zugreifen. Um zu überprüfen, ob der lokale Port 5555 aktiv ist, habe ich den Befehl ausgeführt netstat -ao | find "5555"und Folgendes erhalten:

 TCP    127.0.0.1:5555         hp-PC:0                LISTENING       14132

Bevor ich zu R gewechselt bin, habe ich die Datei sshd_config noch einmal überprüft, um sicherzustellen, dass TCPForwarding zulässig und ListenAdress für den lokalen Host (127.0.0.1) konfiguriert war. Diese Überprüfung habe ich auf meinem PC und auf dem DO-Server durchgeführt.

Nachdem dies alles erledigt war, ging ich zu RStudio und versuchte, mit dem folgenden Code eine Verbindung zur Postgresql-Datenbank auf dem DO-Server herzustellen:

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")

Ich verwende den Host „127.0.0.1“ und den Port „5555“, da dies meines Wissens der Port ist, der lauscht und bereits an die DO-Server-IP (123.456.789.01) weitergeleitet wurde.

Die Versuche und Fehler:

Fehler 1

Ich habe viele verschiedene Optionen für den Code in R ausprobiert und daher mehrere Fehler erhalten. Mit dem oben genannten ursprünglichen Code erhalte ich den folgenden Fehler:

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

Ich erhalte auch denselben Fehler, wenn ich den Host in „localhost“ oder den Benutzer in „Benutzername“ ändere (den Benutzer, mit dem ich auf die Datenbank zugreife, nicht den DO-Server).

Fehler 2

Ich habe versucht, im R-Code einen anderen Port aufzurufen (z. B. indem ich den Port auf „5433“ geändert habe), um zu sehen, ob etwas passiert, und habe den folgenden Fehler erhalten:

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?

Dieser Fehler tritt auch auf, wenn ich die Verbindung von der CMD-Anwendung aus schließe oder den Host-Wert in eine andere IP ändere (z. B. Host = 111.222.333.44).

Fehler 3

Aus Verzweiflung habe ich versucht, direkt aus RStudio eine Verbindung herzustellen. Dazu habe ich Folgendes verwendet:

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)

Und bekam das Ergebnis:

/ Waiting for connection on port 5555...

Da R ein Single-Thread-System ist, konnte ich DBI::dbConnect nicht aus derselben Sitzung (Sitzung A) ausführen. Also startete ich eine neue Sitzung (Sitzung B) und führte DBI::dbConnect wie oben beschrieben aus. Die Ergebnisse waren:

#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

Die Frage

Ich möchte lediglich RStudio auf meinem PC mit der Postgresql-Datenbank auf dem DO-Server verbinden können. Es macht mir wirklich nichts aus, wenn dies erreicht wird, indem ich auf etwas hinweise, was ich bei der Konfiguration oder dem Code falsch mache, oder wenn es eine völlig andere Möglichkeit gibt, dies zu tun.

Ich bin für alle Vorschläge, die Sie mir machen könnten, sehr dankbar.

Antwort1

Offenbar gibt es hierfür eine einfache Lösung, die uns zur Verfügung gestellt wurde vonDaniel Verité.

Es ist wichtig, Port "5432" anzurufen, da dies der Standardport ist, auf dem Postgresql auf den Server lauscht(in diesem Fall Digital Ocean). Daher sieht mein endgültiger Code in der CMD-Anwendung wie folgt aus:

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

Wo:

  • 5555 ist der Port im lokalen Computer (kann vom Benutzer einer beliebigen Nummer zwischen 1.024 und 32.767 zugewiesen werden)Portnummern für SSH- und Telnet-Verbindungen
  • 127.0.0.1 gibt den lokalen Host an
  • 5432 ist, wie oben erwähnt, der Standardport, an dem PostgreSQL auf eingehende Verbindungen wartet.
  • 123.456.789.01 ist eine Beispiel-IP, die der Server-IP entspricht
  • Als zusätzlicher Hinweis: Wenn Sie eine Passphrase mit Ihrem SSH-Schlüssel konfiguriert haben, wird diese sofort angefordert, nachdem Sie diese Codezeile in der CMD-Anwendung ausgeführt haben

Nachdem ich das getan hatte, stellte R mit dem oben genannten Code problemlos eine Verbindung her.

Antwort2

psql ist ein interaktives Terminalprogramm, das von PostgreSQL bereitgestellt wird. Mit dem psql-Tool können Sie viel tun, z. B. SQL-Anweisungen ausführen, Datenbankobjekte verwalten usw.

Sie können dies überprüfen:

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

Sie können mehr über den SSH-Port erfahrenHier

verwandte Informationen