Подключение к кластеру PostgreSQL

Статья основана на восьмом видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.

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

Варианты доступа к кластеру

Одна инсталяция инстанса PostgreSQL называется кластером. Доступ к нему есть только с localhost, то есть с виртуальной машины, куда и был установлен PostgreSQL (не касается docker и kubernetes)

При установке PostgreSQL создается Linux пользователь postgres. Это не тот же самый пользователь, что и пользователь СУБД.

Пользователь postgres предназначен для запуска кластера PostgreSQL и является владельцем всех файлов, относящихся к PostgreSQL – исполняемых файлов, файлов данных и логов. Для работы с этими файлами нужен доступ к утилите sudo. Он предоставлен пользователю postgres.

По умолчанию к файлам кроме него могут получить доступ только суперпользователи Linux – root и группа пользователей, имеющая право на запуск утилиты sudo, позволяющей повысить свои личные права до прав суперпользователя.

Как это работает?

Доступ к PostgreSQL после запуска возможен только через psql и Unix socket

Мы пытаемся запустить клиента от своего имени, который, в свою очередь, спрашивает у операционной системы, что это за пользователь. Далее PostgreSQL проверяет, есть ли такой пользователь у него, открывает или не открывает соединение.

Это означает, что пароль от единственного пользователя СУБД НЕ будет запрашиваться при входе в PostgreSQL, вместо этого PostgreSQL спросит у ОС – авторизован ли такой пользователь в ОС.

Как зайти под именем пользователя postgres?

Первый вариант:

sudo su postgres - смена пользователя на postgres
psql - запуск psql

Второй вариант:

sudo -u postgres psql - выполнить команду 'psql' под пользователем postgres

Также можно создать нового пользователя и зайти под ним в любую базу данных:

create user username - создание пользователя
psql -d postgres - вход в psql в базу данных postgres

Для просмотра параметров подключения существует команда \conninfo

Обратите внимание, что при входе в psql значок в консоли поменяется на “=>”. Это означает, что клиент ждет ввода команды, которая обязательно должна закончиться точкой с запятой. Если значок изменился на “->”, то команда не была завершена – вы можете продолжить вводить её на новой строке (или завершить точкой с запятой).

Подключение по сети

Кроме подключения по Unix Socket стандартным подключением к СУБД является подключение по сети (протокол TCP/IP). PostgreSQL по умолчанию слушает порт 5432, но для получения доступа туда необходим пароль.

Для создания пароля:

psql - вход в psql
\password - с обратного слеша начинаются команды psql

или

ALTER USER postgres PASSWORD '123';

Для того, чтобы зайти по сети, нужно отредактировать два конфигурационных файла:

hba_file.conf – настройки встроенного в PostgreSQL firewall

postgresql.conf – настройки PostgreSQL, в том числе подключений извне

Расположение этих файлов зависит от типа и варианта ОС. Посмотреть, где они расположены можно из утилиты psql:

show hba_file;
show config_file;

В Ubuntu данные файлы расположены:

/etc/postgresql/16/main/pg_hba.conf
/etc/postgresql/16/main/postgresql.conf

Команды Linux можно выполнять прямо из PostgreSQLс помощью “\!”

Файл hba_file.conf

Используем утилиту просмотра файлов cat под текущим пользователем Linux postgres

\! cat /etc/postgresql/16/main/pg_hba.conf

Видим, что по умолчанию доступна только peer аутентификация – это доступ через socket, который был рассмотрен выше. Также есть доступ с localhost – 127.0.0.1, но нужен пароль. От маски зависит, с какой группы IP адресов можно получить доступ.

Нужно обратить внимание, что с 14 версии поменялся default метод шифрования пароля – теперь это SCRAM-SHA-256, вместо MD5. Если обновить версию, то можно потерять доступ, так как эти методы несовместимы. Сначала перешифруйте пароли, а потом обновляйте версию.

Не рекомендуется использовать систему PASSWORD, так как пароль передается в открытом виде без шифрования.

Отредактируем hba_file.conf

\! nano /etc/postgresql/16/main/pg_hba.conf

Указываем подсеть вместе с маской

Открывайте только сетевое подключение и только в доверенной локальной сети

Файл postgresql.conf

Отредактируем файл postgresql.conf

\! nano /etc/postgresql/16/main/postgresql.conf

В параметр listen_adresses вводим необходимый адрес

Если указать * , то будут прослушиваться подключения на всех интерфейсах, в том числе интернет. Однако, не рекомендуется давать PostgreSQL доступ в интернет в целях безопасности.

После изменений файлов надо перезагрузить кластер

sudo pg_ctlcluster 16 main restart

Подключение к PostgreSQL извне

После редактирования файлов можно подключаться с PostgreSQL с помощью пароля

psql -U postgres -W -d postgres -h 10.128.15.211

Так как у PostgreSQL открытый протокол обмена между кластерами и версиями, то никаких проблем при совместимости, начиная с версии 9.6, не наблюдается. Естественно, если вы используете psql 16 версии и подключаетесь к 10, то функционал будет доступен только 10 версии)

Презентация к статье здесь.

Девятая из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.