Postgresql открыть доступ по сети windows

In order to remotely access a PostgreSQL database, you must set the two main PostgreSQL configuration files:

postgresql.conf
pg_hba.conf

Here is a brief description about how you can set them (note that the following description is purely indicative: To configure a machine safely, you must be familiar with all the parameters and their meanings)

First of all configure PostgreSQL service to listen on port 5432 on all network interfaces in Windows 7 machine:
open the file postgresql.conf (usually located in C:\Program Files\PostgreSQL\9.2\data) and sets the parameter

listen_addresses = '*'

Check the network address of WindowsXP virtual machine, and sets parameters in pg_hba.conf file (located in the same directory of postgresql.conf) so that postgresql can accept connections from virtual machine hosts.
For example, if the machine with Windows XP have 192.168.56.2 IP address, add in the pg_hba.conf file:

host all all 192.168.56.1/24 md5

this way, PostgreSQL will accept connections from all hosts on the network 192.168.1.XXX.

Restart the PostgreSQL service in Windows 7 (Services-> PosgreSQL 9.2: right click and restart sevice). Install pgAdmin on windows XP machine and try to connect to PostgreSQL.

Источник

PostgreSQL is a powerful open-source relational database management system that has become a popular choice for developing web applications. With PostgreSQL, you can store, retrieve, and manipulate data in a structured manner. However, in some cases, you might want to allow remote access to your PostgreSQL database so that you can connect to it from another computer. Allowing remote access to your PostgreSQL database can be useful for troubleshooting, testing, or for allowing other users to access the data stored in your database.

Method 1: Update pg_hba.conf

To allow remote access to PostgreSQL database on Windows, you can update the pg_hba.conf file. Here are the steps to do it:

Locate the pg_hba.conf file. The file is usually located in the data directory of PostgreSQL installation. For example, the default data directory for PostgreSQL 12 on Windows is «C:\Program Files\PostgreSQL\12\data».
Open the pg_hba.conf file in a text editor.
Find the section that starts with «IPv4 local connections:». This section controls access for connections made from the same machine as the PostgreSQL server.
Add the following line to the section to allow remote access from a specific IP address or range:
```
host    all             all             <IP_address>/<netmask>        md5
```
Replace <IP_address> and <netmask> with the IP address and netmask of the remote machine. For example, to allow access from the IP address 192.168.1.100, use:
```
host    all             all             192.168.1.100/32        md5
```
The «md5» at the end means that the connection will use MD5 authentication.
Find the section that starts with «IPv6 local connections:», and repeat step 4 for IPv6 connections if needed.
Save the pg_hba.conf file.
Restart the PostgreSQL server for the changes to take effect.

Here is an example of the updated pg_hba.conf file:


host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.100/32        md5

host    all             all             ::1/128                 md5

In this example, connections from the IP address 192.168.1.100 are allowed in addition to local connections.

Method 2: Update postgresql.conf

How to Allow Remote Access to PostgreSQL database on Windows using postgresql.conf?

Follow these steps to allow remote access to PostgreSQL database on Windows using postgresql.conf:

Open the postgresql.conf file located in the data directory of your PostgreSQL installation.
Find the line that starts with #listen_addresses and uncomment it by removing the # symbol at the beginning of the line.
```
listen_addresses = 'localhost'
```
Change it to:

This will allow PostgreSQL to listen on all available network interfaces.
Find the line that starts with #port and uncomment it by removing the # symbol at the beginning of the line.

Change it to the port you want to use for remote connections.
Save and close the postgresql.conf file.

Restart the PostgreSQL service to apply the changes.

net stop postgresql-x64-13
net start postgresql-x64-13

Add the IP address or subnet of the remote machine to the pg_hba.conf file located in the data directory of your PostgreSQL installation.
```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.0/24          md5
```
This will allow all users to connect to all databases from any machine on the 192.168.1.0/24 subnet using the md5 authentication method.

Note: Make sure to replace 192.168.1.0/24 with the IP address or subnet of the remote machine you want to allow access from.
Save and close the pg_hba.conf file.

That’s it! You have successfully allowed remote access to your PostgreSQL database on Windows using postgresql.conf.

Method 3: Create a new user with specific IP address

To allow remote access to a PostgreSQL database on Windows, you can create a new user with a specific IP address. Here are the steps to do this:

Open the pg_hba.conf file located in the PostgreSQL data directory. This file contains the configuration for client authentication.
Add the following line to the file:

host    all             <username>       <IP address>/32        md5

Replace <username> with the name of the new user you want to create and <IP address> with the IP address of the remote machine you want to allow access from.

Save the file and restart the PostgreSQL server for the changes to take effect.
Create the new user using the following SQL command:

CREATE USER <username> WITH PASSWORD '<password>';

Replace <username> with the name of the new user and <password> with a strong password.

Grant the necessary privileges to the new user using the following SQL command:

GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>;

Replace <database_name> with the name of the database you want to grant access to.

That’s it! Now the new user with the specified IP address can remotely access the PostgreSQL database.

Here is an example of what the pg_hba.conf file might look like:


local   all             all                                     trust
host    all             all             127.0.0.1/32            md5
host    all             mynewuser       192.168.1.100/32        md5
host    all             all             ::1/128                 md5

And here are the corresponding SQL commands to create the new user and grant privileges:

CREATE USER mynewuser WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO mynewuser;

Method 4: Use a VPN connection

To allow remote access to PostgreSQL database on Windows using VPN connection, follow the steps below:

Step 1: Install PostgreSQL

First, you need to install PostgreSQL on your Windows machine. You can download the latest version from the official PostgreSQL website.

Step 2: Configure PostgreSQL

Once you have installed PostgreSQL, you need to configure it to allow remote access. To do this, follow the steps below:

Open the pg_hba.conf file located in the data directory of your PostgreSQL installation.
Add the following line to the file:
```
host    all             all             0.0.0.0/0               md5
```
This line allows access to all databases and all users from any IP address. You can modify it to specify a specific IP address or range of IP addresses.
Save the file and restart PostgreSQL.

Step 3: Set up VPN Connection

Next, you need to set up a VPN connection to your Windows machine. There are many VPN solutions available, but we will use OpenVPN as an example.

Download and install OpenVPN on your Windows machine.
Download the OpenVPN configuration file from your VPN provider.
Open the OpenVPN GUI and import the configuration file.
Connect to the VPN server.

Step 4: Connect to PostgreSQL

Finally, you can connect to your PostgreSQL database using any PostgreSQL client software. Here is an example using psql:

psql -h <IP address of your Windows machine> -U <username> -d <database name>

Replace <IP address of your Windows machine>, <username>, and <database name> with the appropriate values.

That’s it! You can now access your PostgreSQL database remotely using a VPN connection.

Источник

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 - 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;

Источник

Lab

/ PostgreSQL

Как настроить удалённый доступ к PostgreSQL с удалённых серверов

Есть два метода настройки удалённого доступ к PostgreSQL с внешних серверов: 1) открыть доступ к вашему PostgreSQL вообще со всех серверов в интернете и 2) Вручную настроить те сервера с которых можно будет подключится к вашей базе.

Первый метод хорош только в случае тестового или учебного проекта, так как небольшой баг в коде может раскрыть данные доступа к вашей базе и скомпрометировать всё её содержимое. Если вы думаете, что ваш проект слишком маленький и вас это не касается, то вы ошибаетесь. Недавно я запустил совершенно чистый сервер с новым ip и оставил открытый доступ к базе, уже через час какой-то бот стёр всю БД и предлагал выкуп, за возвтар данных.

Настройка доступа избранным ip

Для того, что бы настроить доступ к PostgreSQL с разрешённых вами серверов нужно для начала открыть доступ к PostgreSQL серверу с внешних серверов. Открываем файл /etc/postgresql/14/main/postgresql.conf Обратите внимание, что у вас может отличатся путь к файлу, например с выходом версии 15 PostgreSQL

В разделе Connection Settings нужно раскомментировать строку listen_addresses и привести её к виду:

listen_addresses = '*'

Далее в этой же директории открываем файл pg_hba.conf и в самом низу добавляем ip адреса тех серверов которым вы хотите дать доступ к вашему PostgreSQL.

Пример строки для каждого сервера:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             80.121.11.21/32        scram-sha-256

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             120.121.11.11/32        scram-sha-256

В примере я открыл удалённый доступ для сервера 80.121.11.21 и 120.121.11.11

В моей конфигурации открыт доступ для всех пользователей, во всем базам данных: host all all для большей безопасности вы можете ограничить этот набор. Например открыть доступ только пользователю remote: host all remote ко всем базам, или ограничить до одного пользователя и одной базе: host orders remote Как видно из примера, только пользователь remote сможет подключится с удачного сервера и получить доступ, только к базе данных orders

Не забывайте перезагружать PostgreSQL после каждого изменения конфиг файлов

sudo service postgresql restart

Доступ со всех ip

Если предостережение выше на вас не подействовало, вы в праве на свой страх и риск открыть доступ к вашей базе со всех серверов в интернете.

Для этого вам необходимо добавить следующую конфигурацию в файл pg_hba.conf


host    all             all             0.0.0.0/0            md5

На этом у меня всё!

Источник

Настройка доступа пользователей по сети

Непосредственно после установки к PG можно получить доступ только с того компьютера, на котором он установлен. Если вы выполняете установку Адаптера СМЭВ на одном компьютере, то вам не обязательно выполнять действия, описанные в данной статье. Если же PG и Адаптер СМЭВ установлены на разных компьютерах, вам необходимо обеспечить доступ к PG по сети.

Чтобы организовать доступ по сети, внесите изменение в конфигурационный файл C:\Program Files\PostgreSQL12\data\pg_hba.conf.

В разделе «# IPv4 local connections:» этого файла требуется строку:

host all all 127.0.0.1/32 md5

заменить на строку:

host all all 0.0.0.0/0 md5

В результате конфигурация в файле pg_hba.conf должна выглядеть следующим образом:

Чтобы внесённые изменения вступили в силу, необходимо перезапустить службу «postgresql-x64-12», или просто перезагрузить компьютер.

Так же, в зависимости от настроек вашей ОС, для доступа к PG по сети может потребоваться открыть TCP/IP порт в брандмауэре. Для этого нажмите комбинацию клавиш Win+R. В появившемся окне введите «firewall.cpl»:

После нажатия кнопки «ОК» откроется окно брандмауэра:

Нажмите ссылку «Дополнительные параметры». В открывшемся окне перейдите в раздел «Правила входящих подключений»:

Выберите Действие «Создать правило…». Откроется окно мастера создания правила для нового входящего подключения. Выберите в нём тип правила «Для порта»:

После нажатия кнопки «Далее» выберите протокол TCP и укажите порт, который был выбран вами при установке PG. По умолчанию это порт 5432:

Нажмите кнопку «Далее» и в открывшемся окне выберите действие «Разрешить подключение»:

Нажмите кнопку «Далее». В открывшемся окне также нажмите кнопку «Далее», ничего не изменяя в составе профилей:

На следующем шаге работы мастера укажите имя правила. Например, такое:

Нажмите кнопку «Готово» для завершения работы мастера. Вы увидите созданное правило в перечне правил входящих подключений:

После этого доступ к PG по сети будет открыт.

Можно переходить к установке и настройке Адаптера.

Источник