Настройка подключения к БД по SSH.

Ранее мы рассмотрели настройку подключения к базам данных в DBeaver по логину и паролю. А сечас посмотрим как это можно сделать по протоколу SSH (Secure Shell)

Проблематика

Для начала посмотрим где нам может это пригодиться. Допустим вы арендовали сервер, например, здесь: RUVDS. Установили на нем СУБД PostgreSQL, создали пользователя для подключения к БД, создали саму базу данных. И теперь вам надо работать с базой данных. Вы конечно можете делать это с помощью консоли psql непосредственно на сервере, но гораздо удобней это делать с помощью менеджера баз данных, каковым и является DBeaver со своего локального компьютера. Но скорее всего вы не сможете подключиться к базе просто указав ip-адрес сервера с портом. И один из способов это сделать - использование протокола SSH. Давайте рассмотрим шаги, которые необходимо выполнить, чтобы реализовать подключение по SSH.

Настройка конфигурационных файлов на сервере

Первое, что надо сделать, это отредактировать некоторые конфигурационные файлы PostgreSQL на сервере. По умолчанию в постгрес включено использование ssl. И это может мешать использованию SSH. Поэтому отключим использование ssl.

В файле postgresql.conf заменяем строку:

ssl = on
на
ssl = off

Также необходимо убедиться, что закомментированы следующие строки:

#ssl_cert_file = 'server.crt'
#ssl_key_file = 'server.key'
#ssl_ca_file = 'root.crt'

В Ubuntu файл postgresql.conf расположен вот по этому пути /etc/postgresql/<версия>/main/postgresql.conf. <версия> - это число с версией постгрес, которая у вас установлена.

Теперь надо отредактировать файл pg_hba.conf. Обычно находится в том же каталоге, что и postgresql.conf. Убедитесь, что для ваших подключений не используется SSL. Если у вас есть строки вида:

hostssl all all 127.0.0.0/0 md5

Замените их на:

host all all 127.0.0.0/0 md5

То есть hostssl заменяем на host. Это отключит ssl для всех подключений. Также необходимо добавить строку, которая разрешит подключение с ip-адреса по которому расположен сервер:

host testdb user 55.123.321.186/32 md5

testdb - имя базы данных
user - имя пользователя
55.123.321.186 - ip адрес вашего сервера

Можно также разрешить подключение ко всем базам под любым пользователем. Но лучше так конечно не делать по соображениям безопасности:

host all all 55.123.321.186/32 md5

На скриншоте нижее приведены оба варианта. Стоит также подчеркнуть, что метод аутентификации md5 является устаревшим, и используется в более ранних версиях postgresql. В последних версиях используется scram-sha-256. В этом случае его не нужно менять на md5.

Чтобы изменения вступили в силу перезагружаем postgresql:

sudo systemctl restart postgresql

Настройка Dbeaver

Теперь надо настроить подключение непосредственно в Dbeaver. Открываем свойства соединения и переходим на закладку SSH. Устанавливаем флажок Использовать тунель SSH. И в настройках прописываем параметры для подключения по ssh: хост, порт (по умолчанию 22), пользователя и пароль. Использование подключения по логину и паролю не очень надежно с точки зрения безопасности. Поэтому в качестве метода аутентификации лучше выбрать Публичный ключ и настроить подключение к серверу с помощью ключа.

Ну и конечно это не отменяет настроек подключения к БД на закладке Главное.

Если тест соединения не проходит надо попробовать в поле Хост прописать localhost, т.к. мы подключаемся через ssh. В этом случае и в файле pg_hba.conf можно не прописывать дополнительную строку: host testdb user 55.123.321.186/32 md5

И теперь мы можем работать с базой данных на сервере. Выполнять как DDL так и DML запросы.