☰

如何使用 R 通过 SSH 隧道连接到服务器上的 PostgreSQL 数据库？

tag-icon • tag-icon cmd.exe openssh postgresql ssh-tunnel rstudio
如何使用 R 通过 SSH 隧道连接到服务器上的 PostgreSQL 数据库？

我正在尝试通过 SSH 隧道将 RStudio 会话连接到服务器 (Digital Ocean) 上的 PostgreSQL 数据库。我是 SSH 连接的新手，特别是使用 R。

系统信息：

在我的本地计算机上，我使用的是 Windows 10 x64。我已经安装了 PostgreSQL 13、R 4.0.3、OpenSSH 服务器和 OpenSSHD 客户端（对于 Open SSH，我使用的是标准 Windows 版本）。

在 Digital Ocean Server 上，我有 Ubuntu 20.04.1、postgresql 12+214ubuntu0.1、​​openssh-server 1:8.2p1-4ubuntu0.2

一般背景：

首先，我已经生成了 .PEM 密钥对（我将私钥的密码称为“passp”）私钥并在 Digital Ocean 服务器（从现在开始为 DO 服务器）中启用了公钥。此外，DO 服务器已配置为允许 SSH 连接（例如 ip：123.456.789.01 和端口：1234）。

通过上述配置，我能够将 PgAdmin 连接到 DO 服务器中的 Postgresql 数据库。我使用以下参数登录数据库：

  • 数据库名称 = “db”
  • doserver_user =“root”
  • db_user = “用户名”
  • 密码 = “pwd”

我已成功使用 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 并且已为本地主机 (127.0.0.1) 配置了 ListenAdress。我在我的 PC 和 DO 服务器上进行了此项检查。

完成所有这些后，我转到 RStudio 并尝试使用以下代码连接到 DO 服务器上的 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 服务器上的 Postgresql 数据库。我真的不介意通过指出我在配置或代码方面做错的事情来实现这一点，或者是否有完全不同的方法来实现这一点。

我将非常感激您能给我的所有建议。

答案1

显然，这个问题有一个简单的解决方案，它来自于丹尼尔维瑞特。

调用端口“5432”很重要，因为它是 Postgresql 监听服务器的默认端口（在本例中为 Digital Ocean）。因此，我在 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-key 配置了密码，在 CMD 应用程序中运行此行代码后，系统会立即请求该密码

一旦我这样做了，R 就可以使用上面提到的代码顺利地连接。

答案2

psql 是 PostgreSQL 提供的一个交互式终端程序。您可以使用 psql 工具做很多事情，例如执行 SQL 语句、管理数据库对象等。

你可以检查一下：

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

您可以查看有关 SSH 端口的更多信息这里

相关内容