☰

Как подключиться к базе данных PostgreSQL на сервере через SSH-туннель с помощью R?

tag-icon • tag-icon cmd.exe openssh postgresql ssh-tunnel rstudio
Как подключиться к базе данных PostgreSQL на сервере через SSH-туннель с помощью R?

Я пытаюсь подключить сеанс RStudio к базе данных PostgreSQL, которая находится на сервере (Digital Ocean) через туннель SSH. Я новичок в соединениях SSH, особенно с использованием R.

Информация о системах:

На моем локальном компьютере я использую Windows 10 x64. Я установил PostgreSQL 13, R 4.0.3, OpenSSH Server и OpenSSHD Client (для Open SSH я использую стандартные версии Windows).

На сервере Digital Ocean у меня 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 к базе данных Postgresql на сервере DO. Я вошел в базу данных, используя следующие параметры:

  • имя_базы_данных = "db"
  • doserver_user = "root"
  • db_user = "имя пользователя"
  • пароль = "pwd"

Мне удалось подключиться к серверу DO с моего ПК с помощью приложения CMD. Команда, которую я использую, следующая:

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 разрешен и ListenAdress настроен для localhost (127.0.0.1). Я выполнил эту проверку на своем ПК и на сервере DO.

Сделав все это, я перешел в RStudio и попытался подключиться к базе данных Postgresql на сервере DO, используя следующий код:

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», поскольку, насколько я понимаю, это порт, который прослушивается и уже перенаправлен на IP-адрес сервера DO (123.456.789.01).

Попытки и ошибки:

Ошибка 1

Я перепробовал много разных вариантов кода в R и поэтому получил несколько ошибок. С исходным кодом, указанным выше, я получаю следующую ошибку:

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

Я также получаю ту же ошибку, если меняю хост на «localhost» или пользователя на «username» (пользователь, под которым я получаю доступ к базе данных, а не к серверу 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 или меняю значение Host на другой IP (например, host = 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 однопоточный, я не смог запустить DBI::dbConnect из того же сеанса (сеанс A). Поэтому я начал новый сеанс (сеанс 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

Спросите

Я просто хочу иметь возможность подключить RStudio на моем ПК к базе данных Postgresql на сервере DO. Я действительно не против, если это будет достигнуто указанием на то, что я делаю неправильно с конфигурацией или кодом, или если есть совершенно другой способ сделать это.

Я буду очень признателен за любые предложения, которые вы мне дадите.

решение1

Видимо, у этой проблемы есть простое решение, которое любезно предоставилДаниэль Верите.

Важно указать порт «5432», так как это порт по умолчанию, на котором PostgreSQL слушает сервер.(в данном случае Digital Ocean). Поэтому мой окончательный код в приложении CMD выглядит следующим образом:

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

Где:

  • 5555 — порт локального компьютера (пользователь может назначить ему любое число от 1024 до 32767)Номера портов для подключений 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здесь

Связанный контент