Postgresql как настроить удаленный доступ

Как настроить удаленное подключение к PostgreSQL

По умолчанию, сервер баз данных pgsql разрешает подключение только с локального компьютера. Данная инструкция показывает, как разрешить подключения с удаленных систем. Она написана на примере Linux CentOS и PostgreSQL 9.5.

Для начала посмотрим путь расположения конфигурационного файла postgresql.conf:

# su — postgres -c «psql -c ‘SHOW config_file;'»

Команда выдаст, примерно, следующее:

* нас интересует строчка /db/pgsql/postgresql.conf — это путь расположения нужного нам конфигурационного файла.

Теперь открываем на редактирование основной файл конфигурации PostgreSQL:

* напоминаю, что начало пути /db/pgsql может быть другим. Мы его получили командой ps.

Находим и редактируем следующее:

* по умолчанию, параметр закомментирован и настроен на прослушивание запросов только с локального сетевого интерфейса. В данном примере мы разрешили прослушивание запросов на всех IP-адресах (*), но, если требуется более безопасная настройка, можно просто перечислить последние через пробел.

После открываем на редактирование следующий конфигурационный файл:

и внизу добавляем следующую строку:

host all all 192.168.0.10/32 password

* в данном примере мы разрешаем удаленные подключения к серверу с компьютера 192.168.0.10. Доступ предоставляется всем учетным записям и всем базам (значение all). При желании, вместо all можно указать конкретные данные для повышения безопасности.

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

# systemctl restart postgresql-9.5

Проверить подключение можно с удаленного компьютера следующей командой:

# psql -h 192.168.0.15 -U usersql

* где 192.168.0.15 — IP-адрес сервера баз данных; usersql — имя учетной записи, от которой идет подключение.

Источник

Удалённый доступ к PostgreSQL

19 января 2014 г. 7:27

​В первую очередь нужно разрешить подключаться к postgresql с внешних ip. Для этого находим файл postgresql.conf (у меня находится в папке /etc/postgresql/9.1/main) и правим следующую строчку:

Обратите внимание, что нужно убрать перед директивой listen_addresses знак комментария #.

А в файле pg_hba.conf (лежит в там же, что и postgresql.conf) добавляем следующую строчку:

где:
hostssl — подключаемся через SSL
all — разрешаем подключение ко всем базам
postgreadmin — имя пользователя, которому разрешаем подключение
0.0.0.0/0 — маска сети с которой можно подключаться
md5 — способ шифрования пароля

Теперь нам нужно создать пользователя postgreadmin в postgresql, через которого будем подключаться:

Используемые опции команды createuser :

-s — добавить пользователю права суперпользователя
-r — разрешить пользователю создавать других пользователей
-d — разрешает пользователю создавать базы данных
-P — добавить к пользователю пароль

Настройка закончилась, самое время перезапустить postqresql, например командой:

и уже подключиться с другого ПК, используя какой-нибудь клиент, например, pgadmin.

В pgadmin нужно щёлнуть «Новое подключение» и в окне ввести следующие поля:

Имя — желаемое имя, например: my_server
Хост — ip-адрес сервера, например: 188.123.456.768
Порт — 5432 (по умолчанию)
Имя пользователя — postgreadmin
Пароль — созданный выше пароль

Остальные поля стоят по умолчанию. Нажимаете OK. Если всё заработало, то поздравляю — удалённое подключение к postgresql настроено.

Источник

Установка PostgreSQL в Ubuntu 20.04

PostgreSQL — это СУБД ( система управления реляционными базами данных), которая используется для хранения и извлечения данных с помощью стандартного языка запросов SQL. Он также известен как Postgres, который поставляется бесплатно с открытым исходным кодом. PostgreSQL поддерживает JSON, XML, например другие программы управления базами данных этого не делают.

Рекомендую к прочтению:

В этом руководстве вы узнаете, как установить PostgreSQL в Ubuntu 20.04.

Что требуется для установки PostgreSQL?

Перед установкой Postgres в Ubuntu вам необходимо выполнить некоторые основные требования.

1. Установленная Ubuntu 20.04
2. Привилегии Sudo для установки пакетов.
3. Подключение к интернету.

Установка PostgreSQL в Ubuntu

PostgreSQL доступен в репозиториях Ubuntu по умолчанию. Просто используйте команду apt для установки PostgreSQL. Наиболее распространенными установленными пакетами являются PostgreSQL server, client, pgadmin, а также библиотеки.

Для установки конкретной версии вы можете использовать PostgreSQL Apt repository.

Чтобы установка PostgreSQL Ubuntu прошла гладко, выполните следующие действия.

Во-первых, убедитесь, что ваша система находится в актуальном состоянии, выполнив следующую команду.

Установите пакет Postgres, а затем пакет — contrib, он добавит дополнительные утилиты и функциональные возможности.

$ sudo apt install postgresql postgresql-contrib

После завершения установки PostgreSQL запустите службу PostgreSQL. Это можно сделать командой которая написана ниже.

$ sudo systemctl start postgresql.service

Также включите автоматический запуск службы PostgreSQL при загрузке системы. Делается это командой.

$ sudo systemctl enable postgresql.service

После установки вы можете проверить состояние PostgreSQL, выполнив следующую команду.

$ sudo systemctl status postgresql.service

По умолчанию пользователь Postgres создается во время установки. Чтобы получить доступ к оболочке Postgres, сначала переключитесь на пользователя Postgres.

Теперь введите команду psql,

Для проверки версии PostgreSQL выполните следующую команду:

Создание роли в PostgreSQL

Для авторизации и аутентификации Postgres использует понятие “роли”. Которое в некоторых отношениях похоже на учетные записи Unix. Во время установки Postgres настраивается таким образом, чтобы использовать аутентификацию ident. Идентификация Ident связывает роль Postgres с соответствующими системными учетными записями Linux / Unix. Только суперпользователи и юзеры с ролью «CREATEROLE” отвечают за создание новых ролей.

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

Создание базы данных в PostgreSQL

Для создания новой базы данных требуется программа PostgreSQL database shell (psql). Войдите в оболочку psql и введите следующую команду, чтобы создать пользователя и установить пароль. Здесь я собираюсь создать имя пользователя jacky с паролем mystrongpassword. Вы можете создать свой собственный.

postgres=# CREATE USER jacky WITH PASSWORD ‘mystrongpassword’;

Создайте базу данных (например, gallary), используя следующую команду,

postgres=# CREATE DATABASE gallery;

Предоставьте доступ к БД которую только что создали.. Здесь я собираюсь предоставить все права на базу данных gallery, чтобы пользователь jacky мог в ней работать.

postgres=# GRANT ALL PRIVILEGES ON DATABASE gallery to jacky;

Чтобы выйти из оболочки PostgreSQL требуется ввести команду q и нажать enter.

Включение удаленного доступа к PostgreSQL

По умолчанию PostgreSQL слушает на интерфейсе 127.0.0.1. Если вы хотите получить доступ к базе данных из другого места. Тогда вам требуется настроить PostgreSQL для прослушки различных сетевых интерфейсов. Чтобы настроить PostgreSQL для различных интерфейсов, откройте конфигурационный файл PostgreSQL с помощью nano или другим текстовым редактором. Я буду использовать редактор Vim.

$ sudo vi /etc/postgresql/12/main/postgresql.conf

В конфигурационном файле найдите запись listen_addresses, находится она в «Connection Settings». Раскомментируйте строку и измените localhost на ‘*’. Это даст команду PostgreSQL прослушивать все сетевые интерфейсы для входящих подключений.

Сохраните свою конфигурацию и перезапустите сервер PostgreSQL, чтобы изменения отразились в БД.

$ sudo systemctl restart postgresql.service

Теперь можно проверить прослушивает ли PostgreSQL другой интерфейс. Для этого выполните следующую команду.

Если вы включили брандмауэр UFW на сервере, тогда вам необходимо открыть порт 5432 для входящих TCP — соединений. Чтоб сделать это выполните команду.

Также проверьте правило брандмауэра UFW, делается это командой.

Заключение

В этом руководстве вы узнали, как установить сервер PostgreSQL на Ubuntu 20.04. Кроме того, вы также узнали, как привязать PostgreSQL к различным сетевым интерфейсам, чтобы принимать соединения от удаленного клиента.

Источник

Как настроить удаленное подключение к БД PostgreSQL из Windows

Настройка удаленного подключения к БД PostgreSQL 13 на сервере Ubuntu 18 LTS из ОС Windows 10 утилиты pgAdmin 4, двумя способами: подключение с помощью SSH туннеля и прямое подключение к серверу PostgreSQL.

💡 Я рекомендую использовать подключение через SSH туннель, простое в настройке и безопасное. При использовании SSH туннеля, порт PostgreSQL не открывается для внешних подключений.

pgAdmin — самая популярная и многофункциональная платформа для администрирования и разработки с открытым исходным кодом для PostgreSQL, самой совершенной базы данных с открытым исходным кодом в мире.

Официальный сайт pgAdmin

Узнать расположение файлов конфигурации PostgreSQL: postgresql.conf, pg_hba.conf.

Узнать порт PostgreSQL командой:

В примере, файл конфигурации PostgreSQL располагается по пути /etc/postgresql/13/main/postgresql.conf, порт подключения 5432.

Содержание

SSH туннель к PostgreSQL

Подключение к серверу PostgreSQL с использованием SSH туннеля. При выборе такого типа подключения никаких дополнительных настроек на сервере PostgreSQL не требуется.

Настройка pgAdmin на Windows 10:

1. Запускаем pgAdmin
2. Создаем новое подключение к серверу: Object -> Create -> Server;
3. Вкладка General:
   1. Name: название сервера (произвольное);
4. Вкладка Connection:
   1. Host Name: localhost;
   2. Port: 5432;
   3. Maintenance database: mybase;
   4. Username: postgres;
5. Вкладка SSH Tunnel:
   1. Use SSH tunneling: Yes;
   2. Tunnel host: myserver-IP;
   3. Tunnel port: 58222;
   4. Username: root;
   5. Authentication: Identity file;
   6. Identity file: path_key.

💡 В качестве ключа указываем приватный ключ id_rsa из Настройка SSH сертификатов на сервере. Как изменить порт SSH в Частые вопросы.

Настройки pgAdmin для подключения через SSH туннель

Прямое подключение к PostgreSQL

Для настройки прямого подключения к PostgreSQL вносим изменения в файлы конфигурации postgresql.conf и pg_hba.conf

Настройка PostgreSQL

postgresql.conf

Файл postgresql.conf находится в папке установки PostgreSQL.

Раскомментируем или добавим строку:

Мы разрешили прослушивание запросов от всех IP-адресов.

💡 Возможные варианты:
listen_addresses = ‘0.0.0.0’ чтобы слушать все IPv4;
listen_addresses = ‘::’ чтобы слушать все IPv6;
listen_addresses = ‘your.host.ip.adress’ определенный адрес или список адресов через запятую.

pg_hba.conf

Файл pg_hba.conf находится в папке установки PostgreSQL.

Открываем на редактирование:

Добавляем запись в секцию # IPv4 local connections:

Запись разрешает подключение к БД mybd пользователю postgres с IP адресом 41.223.232.15, используя пароль.

После изменения файлов конфигурации, перезапустите службу PostgreSQL.

Настройка pgAdmin

Настройка прямого подключения к базе данных PostgreSQL через интернет или локальную сеть используя pgAdmin.

1. Запустите pgAdmin
2. Создаем новое подключение к серверу: Object -> Create -> Server;
3. Вкладка General:
   1. Name: название сервера (произвольное);
4. Вкладка Connection:
   1. Host Name: RemoteServerIP;
   2. Port: 5432;
   3. Maintenance database: mybase;
   4. Username: postgres.

Частые вопросы

ps aux | grep postgres | grep — -D

grep -H ‘^port’ /etc/postgresql/*/main/postgresql.conf

Файл /etc/ssh/sshd_config , строка port.

Как настроить удаленное подключение к БД PostgreSQL из Windows обсуждалось в этой статье. Я надеюсь, что теперь вы сможете настроить pgAdmin для подключения к PostgreSQL (прямое подключение или используя SSH туннель). Однако, если вы столкнетесь с каким-то проблемами при настройке сервера или pgAdmin, не стесняйтесь написать в комментариях. Я постараюсь помочь.

Источник