По умолчанию PostgreSL принимает подключения только с локального хоста. Для того чтобы получить доступ к СУБД удаленно необходимо изменить конфигурационный файл и открыть порты в Windows.
Реализуем конфигурацию PostgreSQL
Сначала вам потребуется перейти в директорию, в которой располагаются БД PostgreSQL. Для PostgreSQL 13. В директории data найдите файл pg_hba.conf и откройте его любым текстовым редактором.
- x86: C:\Program Files (x86)\PostgreSQL\13\data
- x64: C:\Program Files\PostgreSQL\13\data
В конец файла добавьте обязательно допишите:
host postgres postgres all md5
Расшифровка:
| Вид подключения | База данных | Пользователь | IP-адрес удаленного подключения | Метод аутентификации |
|---|---|---|---|---|
| host | postgres | postgres | all | md5 |
Мы разрешили подключение к системе postgres с любого IP, включая пользователя из стандартной базы MS PostGreen. Это небезопасно и опасно для пользователей. Однако это может быть опасным в будущем: найдите строку listten_ addresses =’*»в файле conf/pcgl.conf. Далее откройте файл в текстовом редакторе и найдите раздел CONNECTIONS AND AUTHENTICATION.
В одной из первых строк раздела указан параметр listen_ addresses =’* ‘. Если значение этого параметра отсутствует или имеет другое значения, скопируйте его в файл и вставьте обратно!
На этом редактирование закончено, теперь осталось настроить брандмауэр Windows.
Настройка брандмауэра Windows
Нажмите сочетание клавиш Win + S. В поисковой строке введите «защит…» или «defend»(для английской версии). Переходим в «Монитор брандмауэра Защитника Windows«.
Переходим внутрь. Выберите раздел Правила для входящих подключений и кликните по кнопке Создать правило, расположенной на правом столбце Действия.
Откроется мастер создания правила для нового входящего подключения. Выберите тип правила — для порта. Нажмите кнопку Далее.
Для настройки правил входящего доступа необходимо указать протокол и порт. По умолчанию PostgreSQL «слушает» 5432 порт. Открываем именно его.
Далее мы разрешаем внешние подключения к порту 5432.
В
Все оставляем без изменений и нажимаем Далее.
Мы создали правило под названием PostgreSeal ingoing, которое позволяет вход в порт 5432 с любого внешнего IP и порта.
Проверка удаленного подключения к PostgreSQL
Проверим доступность 5432 портов в PostgreSL под Linux. Сделать это можно следующей командой в утилите telnet, скачать можно этой командой:
apt install telnet
Скорее всего вам понадобятся root-права для установки утилиты которые можно получить при помощи команды: sudo su. Конечно, вы должны знать пароль от root-пользователя.
Проверим доступность 5432 порта:
telnet 185.233.2.45 5432
Синтаксис команды следующий: telnet IP-адрес сервера Порт. В нашем случае IP-адрес сервера — 185.233.2.45, а порт — 5432.
Если порт доступен, telnet вернет следующую информацию:
Trying 185.233.2.45...
Connected to 185.233.2.45.
Escape character is '^]'.
Чтобы прервать подключение нажмите 2 раза Enter или сочетание клавиш Ctrl + Z. Теперь, когда мы убедились в доступности 5432 порта, подключимся к PostgreSQL с помощью специального PostgreSQL клиента — psql.
Скачаем psql из репозитория:
apt install psql
Теперь подключимся удаленно к PostgreSQL. Синтаксис команды следующий:
psql -U пользователь PostgreSQL -h IP сервера -d БД для подключения
В нашем случае команда выглядит так:
psql -U postgres -h 185.233.2.45 -d postgres
Далее необходимо ввести пароль пользователя, под которым осуществляется подключения. Пароль задавался при установки PostgreSQL.
Команда вернула следующую информацию:
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1), server 13.3)
WARNING: psql major version 12, server major version 13.
Some psql features might not work.
Type "help" for help.
postgres=#
Все, мы подключились к PostgreSQL удаленно из Linux с помощью psql.
В мире современных информационных технологий удалённое подключение к базам данных стало неотъемлемой частью работы многих предприятий и организаций. В одной из предыдущих статей нашего справочника мы описали процесс установки PostgreSQL на VPS, работающий под управлением Windows Server 2016. Теперь же мы продолжим этот путь и рассмотрим процесс первоначальной настройки удалённого доступа к PostgreSQL, что в дальнейшем позволит управлять данными и обеспечивать доступ к ним из любой точки мира.
После установки PostgreSQL позволяет подключаться исключительно с локального хоста. Для того, чтобы получить возможность удалённого подключения к PostgreSQL необходимо внести некоторые изменения в файл конфигурации и в настройки брандмауэра Windows.
На нашем сервере конфигурационный файл pg_hba.conf находится в директории C:\Program Files\PostgreSQL\15\data. В конец данного файла необходимо добавить следующую строку:
host postgres postgres all md5
Данная строка разрешает подключения к PostgreSQL с любого IP-адреса. При этом подключение осуществляется к БД postgres под учётной записью postgres. В реальной жизни таким образом поступать ни в коем случае нельзя, поскольку такое подключение не является безопасным. Для подключения к СУБД администратору нужно создать специального пользователя с ограниченными правами.
В той же директории находится файл postgresql.conf, в котором должна присутствовать строка listen_addresses = '*' в разделе CONNECTIONS AND AUTHENTICATION:
Создание правила в брандмауэре Windows
Далее, необходимо произвести некоторые настройки брандмауэра, которые в Windows Server 2016 удобнее производить через Server Manager.
Для запуска Server Manager перейдите в стартовое меню и кликните по соответствующей иконке.
После того, как менеджер откроется, перейдите Tools → Windows Firewall with Advanced Security.
В мониторе брандмауэра необходимо добавить правило для подключения к СУБД. Для этого перейдите Inbound Rules → New Rule.
В открывшемся окне мастера выберите тип Port, затем нажмите Next.
В следующем окне укажите протокол TCP и номер порта — 5432, после чего снова нажмите Next.
Затем для нашего тестового подключение мы разрешим все подключения, поэтому активируйте опцию Allow the connection и так же нажмите Next.
В следующем окне без внесения каких-либо изменений нажмите Next.
И, наконец, в заключительном окне мастера в строку Name введите имя создаваемого правила и нажмите Finish.
Таким образом, созданное правило должно будет появиться в мониторе брандмауэра.
Тестирование созданного подключения
Для проверки возможности подключения мы будем использовать виртуальный сервер, работающий под управлением операционной системы Ubuntu. Во-первых, необходимо проверить доступ к порту 5432. Для чего можно использовать утилиту telnet. Синтаксис данной утилиты применяется следующий:
telnet IP-address Port
В данном случае IP-address — IP-адрес сервера, а Port — номер порта, к которому производится подключение. В случае, если порт на удалённом сервере доступен для подключения, вывод команды telnet будет выглядеть следующим образом:
Для того, чтобы прервать установленное соединение, используйте комбинацию клавиш Ctrl Z.
Далее, необходимо установить на linux-сервер клиента, с помощью которого будет осуществляться подключение к PostgreSQL.
$ sudo apt install postgresql-client
После успешной инсталляции проверьте версию установленного софта:
$ psql --version
Вывод команды выглядит примерно следующим образом:
Теперь при помощи postgresql-client вы сможете подключиться к вашей СУБД, установленной на виртуальном windows-сервере. Формат команды для подключения выглядит как:
psql -U user-name -h IP-address -d DB-name
В данном случае:
-U— ключ, который говорит о том, что подключение будет осуществлено под учётной записью, указанной после ключа;user-name— имя учётной записи, используемой для подключения;-h— ключ, указывающий на то, что подключение будет произведено к удалённому хосту;IP-address— IP-адрес удалённого хоста;-d— ключ, после которого указывается имя базы данных, к которой производится подключение;DB-name— имя базы данных.
В нашем случае команда для подключения будет выглядеть следующим образом:
psql -U postgres -h 194.58.71.188 -d postgres
На скриншоте ниже видно, что мы успешно подключились к СУБД, расположенной на нашей тестовой VPS с использованием терминального клиента psql в командной строке на linux-сервере.
-
Lucas
-
July 12, 2024
If you need to access PostgreSQL installed on Windows from another computer, this step-by-step guide will help you configure your PostgreSQL server to allow remote connections securely and efficiently.
Step 1: Install PostgreSQL
If you don’t already have PostgreSQL installed, follow these instructions:
- Visit the official PostgreSQL website: PostgreSQL Downloads.
- Download the appropriate installer for your version of Windows.
- Follow the installer’s instructions to complete the installation.
Step 2: Configure the file postgresql.conf
The file postgresql.conf controls various PostgreSQL server settings. You need to adjust it to allow external connections.
- Navigate to the PostgreSQL data directory, which is usually
C:\Program Files\PostgreSQL\<versão>\data\. - Open the file
postgresql.confwith a text editor. - Find the line:
#listen_addresses = 'localhost'Change to:
listen_addresses = '*'- Save and close the file.
Step 3: Configure the file pg_hba.conf
The file pg_hba.conf controls which computers can connect to PostgreSQL and which authentication methods will be used.
- In the same data directory, open the file
pg_hba.confwith a text editor. - Add the following line to the end of the file to allow connections from any IP address:
host all all 0.0.0.0/0 md5Alternatively, for greater security, you can specify a range of trusted IP addresses:
host all all <IP>/<máscara> md5- Save and close the file.
Step 4: Open the Port in Windows Firewall
To allow other computers to connect to PostgreSQL, you must open port 5432 in the Windows firewall.
- Open the “Control Panel” and go to “System and Security” > “Windows Firewall”.
- Click on “Advanced Settings”.
- Select “Input Rules” and click on “New Rule”.
- Choose “Port” and click “Next”.
- Select “TCP” and enter “5432” for the specific local ports.
- Click on “Next”, choose “Allow Connection”, and configure the profiles as required (Domain, Private, Public).
- Give the rule a name and finish.
Step 5: Restart the PostgreSQL Service
For the changes to the configuration files to take effect, the PostgreSQL service must be restarted.
- Open the Windows “Service Manager” (
services.msc). - Find the PostgreSQL service, right-click and select “Restart”.
Step 6: Test the Remote Connection
Finally, test the connection from another computer using a database client such as pgAdmin or DBeaver.
- Open the database client on the other computer.
- Set up a new connection with the IP of the Windows server where PostgreSQL is installed and port 5432.
- Test the connection to make sure everything is working properly.
Final considerations
Configuring PostgreSQL for remote access on Windows is a relatively simple process, but requires attention to configuration and security details. Make sure that your network environment allows remote connections and that your firewall and security rules are configured correctly to protect your server from unauthorized access.
By following these steps, you will be able to access your PostgreSQL database remotely, making it easier to manage and interact with your applications. If you have any questions or encounter problems during configuration, consult the official PostgreSQL documentation or seek help in specialized forums.
PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.
Содержание:
- Установка PostgreSQL 11 в Windows 10
- Доступ к PostgreSQL по сети, правила файерволла
- Утилиты управления PostgreSQL через командную строку
- PgAdmin: Визуальный редактор для PostgresSQL
- Query Tool: использование SQL запросов в PostgreSQL
Установка PostgreSQL 11 в Windows 10
Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.
В процессе установки установите галочки на пунктах:
- PostgreSQL Server – сам сервер СУБД
- PgAdmin 4 – визуальный редактор SQL
- Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
- Command Line Tools – инструменты командной строки
Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).
По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.
Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.
Доступ к PostgreSQL по сети, правила файерволла
Чтобы разрешить сетевой доступ к вашему экземпляру PostgreSQL с других компьютеров, вам нужно создать правила в файерволе. Вы можете создать правило через командную строку или PowerShell.
Запустите командную строку от имени администратора. Введите команду:
netsh advfirewall firewall add rule name="Postgre Port" dir=in action=allow protocol=TCP localport=5432
- Где rule name – имя правила
- Localport – разрешенный порт
Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:
New-NetFirewallRule -Name 'POSTGRESQL-In-TCP' -DisplayName 'PostgreSQL (TCP-In)' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432
После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.
Совет. Для изменения порта в установленной PostgreSQL отредактируйте файл postgresql.conf по пути C:\Program Files\PostgreSQL\11\data.
Измените значение в пункте
port = 5432
. Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:
Restart-Service -Name postgresql-x64-11
Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.
- Запустите командную строку.
Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните:
chcp 1251 - Перейдите в каталог bin выполнив команду:
CD C:\Program Files\PostgreSQL\11\bin
Основные команды PostgreSQL:
PgAdmin: Визуальный редактор для PostgresSQL
Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.
По умолчанию все созданные базы хранятся в каталоге base по пути C:\Program Files\PostgreSQL\11\data\base.
Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.
Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.
Query Tool: использование SQL запросов в PostgreSQL
Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.
- Выберите базу данных, в панели Tools откройте Query Tool
- Создадим таблицу сотрудников:
CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);
Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.
После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.
Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.
Здесь вы можете заполнить данные в таблице.
После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;
Setting up a remote connection to the PostgreSQL 13 database on the Ubuntu 18 LTS server from Windows 10 using the pgAdmin 4 utility, in two ways: connecting using an SSH tunnel and connecting directly to the PostgreSQL server.
? I recommend using an SSH tunnel, which is easy to set up and secure. When using an SSH tunnel, the PostgreSQL port does not open for external connections.
To use SSH tunnel, you need to configure SSH Certificates for login to Ubuntu.
pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.
PgAdmin official website
Find out the location of PostgreSQL configuration files: postgresql.conf, pg_hba.conf.
ps aux | grep postgres | grep -- -DFind out the PostgreSQL port with the command:
grep -H '^port' /etc/postgresql/*/main/postgresql.conf
In the example, the PostgreSQL configuration file is located at /etc/postgresql/13/main/postgresql.conf, connection port 5432.
Content
- SSH tunnel to PostgreSQL
- Direct connection to PostgreSQL
- FAQ
Article in other languages:
?? – Cómo configurar el acceso remoto a la base datos PostgreSQL desde Windows
?? – Как настроить удаленное подключение к БД PostgreSQL из Windows
SSH tunnel to PostgreSQL
Connect to PostgreSQL server using SSH tunnel. When choosing this type of connection, no additional settings are required on the PostgreSQL server.
Setting Up pgAdmin on Windows 10:
- Launch pgAdmin
- Create a new connection to the server: Object -> Create -> Server;
- General tab:
- Name: server name (arbitrary);
- Connection tab:
- Host Name: localhost;
- Port: 5432;
- Maintenance database: mybase;
- Username: postgres;
- SSH Tunnel tab:
- Use SSH tunneling: Yes;
- Tunnel host: myserver-IP;
- Tunnel port: 58222;
- Username: root;
- Authentication: Identity file;
- Identity file: path_key.
? Specify the id_rsa private key from Configuring SSH certificates on the server as a key. How to change SSH port in FAQ.
Direct connection to PostgreSQL
To set up a direct connection to PostgreSQL, we make changes to the postgresql.conf and pg_hba.conf configuration files
PostgreSQL setup
postgresql.conf
The postgresql.conf file is located in the PostgreSQL installation folder.
sudo nano /etc/postgresql/13/main/postgresql.confLet’s uncomment or add the line:
listen_addresses = '*'We have allowed requests from all IP addresses.
? Possible options:listen_addresses = '0.0.0.0' to listen to all IPv4;listen_addresses = '::' to listen to all IPv6;listen_addresses = 'your.host.ip.adress' specific address or list of addresses separated by commas.
pg_hba.conf
The pg_hba.conf file is located in the PostgreSQL installation folder.
sudo nano /etc/postgresql/13/main/pg_hba.confAdd an entry to the # IPv4 local connections section:
host mybd postgres 41.223.232.15/32 md5The entry allows the user postgres with IP address 41.223.232.15 to connect to the mybd database using a password.
After the changes, restart the PostgreSQL service.
systemctl restart postgresqlpgAdmin setup
Setting up a direct connection to the PostgreSQL database via the Internet or local network using pgAdmin.
- Start pgAdmin
- Create a new connection to the server: Object -> Create -> Server;
- General tab:
- Name: server name (arbitrary);
- Connection tab:
- Host Name: RemoteServerIP;
- Port: 5432;
- Maintenance database: mybase;
- Username: postgres.
FAQ
PostgreSQL how to find out the location of configuration files?
ps aux | grep postgres | grep — -D
PostgreSQL how to find out the port?
grep -H ‘^port’ /etc/postgresql/*/main/postgresql.conf
SSH how to find out or change the connection port?
File /etc/ssh/sshd_config, line Port.
How to Setup Remote Access to PostgreSQL Database from Windows was discussed in this article. I hope you can now configure pgAdmin to connect to PostgreSQL (direct connection or using SSH tunnel). However, if you run into any problems while configuring the server or pgAdmin, feel free to write in the comments. I will try to help.