☰

如何使用 R 透過 SSH 隧道連接到伺服器上的 PostgreSQL 資料庫？

tag-icon • tag-icon cmd.exe openssh postgresql ssh-tunnel rstudio
如何使用 R 透過 SSH 隧道連接到伺服器上的 PostgreSQL 資料庫？

我正在嘗試透過 SSH 隧道將 RStudio 會話連接到伺服器（數位海洋）上的 PostgreSQL 資料庫。我是 SSH 連線的新手，特別是使用 R。

系統資訊：

在我的本機電腦上，我使用的是 Windows 10 x64。我已經安裝了 PostgreSQL 13、R 4.0.3、OpenSSH Server 和 OpenSSHD Client（對於 Open SSH，我使用標準 Windows 版本）。

在數位海洋伺服器上我有Ubuntu 20.04.1、postgresql 12+214ubuntu0.1、​​openssh-server 1:8.2p1-4ubuntu0.2

一般背景：

首先，我已經產生了一個.PEM金鑰對（我將私鑰的密碼稱為「passp」）私鑰，並在Digital Ocean Server（從現在開始是DO Server）中啟用了公鑰。此外，DO 伺服器已設定為允許 SSH 連線（範例 IP：123.456.789.01 和連接埠：1234）。

透過上述配置，我能夠將 PgAdmin 連接到 DO Server 中的 Postgresql 資料庫。我使用以下參數登入資料庫：

  • 資料庫名稱=“資料庫”
  • doserver_user =“根”
  • db_user = "使用者名稱"
  • 密碼=“密碼”

我已成功使用 CMD 應用程式從我的 PC 連接到 DO 伺服器。我正在使用的命令如下：

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

當這個連接打開時，我當然可以訪問DO伺服器。為了驗證本機連接埠 5555 是否正在運行，我運行了命令netstat -ao | find "5555"並收到以下內容：

 TCP    127.0.0.1:5555         hp-PC:0                LISTENING       14132

在轉向 R 之前，我仔細檢查了 sshd_config 文件，以確保允許 TCPForwarding 並且為 localhost (127.0.0.1) 配置了 ListenAdress。我在我的 PC 和 DO 伺服器上進行了此檢查。

完成所有這些後，我進入 RStudio 並嘗試使用以下程式碼連接到 DO Server 上的 Postgresql 資料庫：

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

我使用主機：“127.0.0.1”和連接埠“5555”，因為據我了解，該連接埠正在偵聽並且已轉送到 DO 伺服器 IP (123.456.789.01)。

嘗試和錯誤：

錯誤1

我在 R 中嘗試了很多不同的程式碼選項，因此遇到了一些錯誤。使用上面提到的初始程式碼，我收到以下錯誤：

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

如果我將主機更改為“localhost”，或將用戶更改為“用戶名”（我訪問資料庫的用戶，而不是 DO 伺服器），我也會收到相同的錯誤。

錯誤2

我嘗試在 R 程式碼中呼叫不同的連接埠（例如將連接埠變更為“5433”）以查看是否會發生某些情況並收到錯誤：

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?

如果我從 CMD 應用程式關閉連線或將主機的值變更為另一個 IP（例如主機 = 111.222.333.44），也會發生此錯誤。

錯誤3

無奈之下，我嘗試直接從 RStudio 建立連線。為此，我使用了以下方法：

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)

並得到結果：

/ Waiting for connection on port 5555...

由於 R 是單線程的，我無法從同一會話（會話 A）運行 DBI::dbConnect。因此，我啟動了一個新會話（會話 B）並運行上面提到的 DBI::dbConnect。結果是：

#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

詢問

我只想能夠將 PC 中的 RStudio 連接到 DO Server 上的 Postgresql 資料庫。我真的不介意這是否是透過指出我在配置或程式碼上做錯的事情來完成的，或者是否有完全不同的方法來做到這一點。

我將非常感謝您給我的所有建議。

答案1

顯然，這有一個簡單的解決方案，這是由丹尼爾·維里特。

呼叫連接埠“5432”很重要，因為它是 Postgresql 監聽伺服器的預設端口（在本例中為數字海洋）。因此，我在 CMD 應用程式中的最終程式碼如下所示：

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

在哪裡：

  • 5555 是本機電腦中的連接埠（使用者可以指定 1,024 到 32,767 之間的任意數字）SSH 和 Telnet 連線的連接埠號
  • 127.0.0.1 表示本地主機
  • 5432，如上所述，是 PostgreSQL 監聽傳入連接的預設端口
  • 123.456.789.01 是對應伺服器 IP 的範例 IP
  • 另請注意，如果您使用 ssh 金鑰配置了密碼，則在 CMD 應用程式中執行此行程式碼後，將立即請求該密碼

一旦我這樣做了，R 使用上面提到的程式碼就可以毫無問題地連接。

答案2

psql是PostgreSQL提供的互動式終端程式。您可以使用 psql 工具做很多事情，例如執行 SQL 語句、管理資料庫物件等。

你可以檢查這個：

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

您可以查看有關 SSH 連接埠的更多信息這裡

相關內容