Postgresql открыть порт 5432 windows
Обновлено: 03.07.2024
На официальном сайте по этой ссылке доступен дистрибутив. Скачиваем его.
Установка PostgreSQL
Запускаем установщик от имени администратора (правая кнопка мыши по файлу)
Установщик автоматически установит Microsoft Visual c++
После чего перейдет к самой установке PostgreSQL, все время жмем далее, далее.
Вводим пароль подключения к базе PostgreSQL
Порт подключения оставляем по умолчанию 5432
Отказываемся от установки различных дополнений
Настройка PostgreSQL
Из соображения безопасности по умолчанию в PostgreSQL установлено значение слушать запросы только с localhost. Необходимо разрешить PostgreSQL слушать запросы из сети. Для этого нужно запустить pgadmin III, который установился вместе с PostgreSQL, подключиться к серверу и внести следующие настройки:
Если спросит ввести пароль, который мы установили ранее (helloasterisk)
Далее открыть файл pg_hba.conf
Файл может располагаться в следующей директории C:\Program Files\PostgreSQL\9.4\data (все зависит от OC)
Необходимо 2 раза щелкнуть левой кнопкой мыши по пустой строке
И установить параметры, как на картинке
Включен + Тип host База данных all Пользователь all IP Адрес 0.0.0.0/0 Метод md5
После чего нажать сохранить
После чего необходимо перезапустить службу PostgreSQL Запустить оснастку службы можно выполнив команду Win+R mmc services.msc
Настройка брандмауэра для PostgreSQL
Разрешаем входящие соединения на порт 5432 по протоколу TCP.
Заходим в панель управления Windows, открываем Брандмауэр
Жмем Дополнительные параметры
Во вкладке Правила для входящих подключений жмем Создать правило
Базы данных — это Святой Грааль для хакеров, поэтому их необходимо защищать с особой тщательностью. Это первая из серии статей, в которых мы дадим обзор best practice в обеспечении безопасности баз данных. Мы начнем с одной из самых популярных СУБД с открытым исходным кодом, PostgreSQL, и рассмотрим несколько уровней безопасности, о которых стоит задуматься:
Безопасность на сетевом уровне
Безопасность на транспортном уровне
Безопасность на уровне базы данных
Безопасность на сетевом уровне
Файрволы
В идеале ваш сервер PostgreSQL должен быть полностью изолированным и не допускать никаких входящих подключений, SSH или psql. К сожалению, PostgreSQL не поддерживает стандартную настройку такого типа.
Следующий способ, который можно предпринять для повышения безопасности сервера базы данных, — это заблокировать доступ к узлу, на котором работает база данных, на уровне порта, с помощью брандмауэра. По умолчанию, PostgreSQL прослушивает TCP-порт 5432. В зависимости от операционной системы существуют разные способы блокировки других портов. В Linux можно использовать iptables - наиболее доступную утилиту для управления файрволом:
Примечание. При обновлении правил iptables рекомендуется использовать iptables-apply, который автоматически откатывает изменения в случае, если вы случайно заблокируете себя.
Приведенное выше правило PostgreSQL позволит любому подключаться к порту 5432. Его можно сделать более строгим, принимая подключения только с определенных IP-адресов или подсетей:
Возвращаясь к нашему идеальному сценарию, возможность полностью предотвратить входящие подключения к порту 5432 потребует своего рода локального агента, который поддерживает постоянное исходящее соединение с клиентским узлом (-ами) и имеет возможность проксировать трафик на локальный экземпляр PostgreSQL.
Такой метод называется «обратным туннелированием». Его можно продемонстрировать при помощи удаленного перенаправления портов. Вы можете открыть обратный туннель, выполнив следующую команду с узла, на котором работает база данных PostgreSQL:
Конечно, <client-host> должен быть доступным из узла PostgreSQL и иметь запущенного SSH-демона. Команда перенаправит порт 5432 на сервере базы данных на порт 5432 на клиентском компьютере, и вы сможете подключиться к базе данных через туннель:
Прослушивание адресов
Хорошей практикой является ограничение адресов, которые прослушивает сервер для клиентских подключений, с помощью параметра listen_addresses файла конфигурации. Если узел, на котором работает PostgreSQL, имеет несколько сетевых интерфейсов, используйте этот параметр, чтобы убедиться, что сервер прослушивает только те интерфейсы, через который клиенты будут подключаться к нему:
Если клиенты, подключающиеся к базе данных, всегда находятся на одном и том же узле (или, скажем, совместно расположены в одном поде Kubernetes с PostgreSQL, работающим в качестве дополнительного контейнера), отключение прослушивания сокетов TCP может полностью исключить сеть из рассматриваемой картины. Запись пустой строки в параметр прослушиваемых адресов заставляет сервер принимать только соединения сокетов домена Unix:
Безопасность на транспортном уровне
Серверный TLS
Для аутентификации сервера сначала необходимо получить сертификат, который сервер будет предоставлять подключающимся клиентам. Let's Encrypt упрощает получение бесплатных сертификатов X.509, например, с помощью инструмента CLI certbot:
Если по какой-то причине вы не можете использовать Let's Encrypt и хотите сгенерировать все сертификаты локально, то можно использовать openssl CLI:
Конечно, в производственной среде необходимо убедиться, что эти сертификаты обновлены вовремя.
Клиентский TLS
Аутентификация клиента по сертификату позволяет серверу проверить личность подключающегося, подтверждая, что сертификат X.509, представленный клиентом, подписан доверенным центром сертификации (CA).
Рекомендуется использовать разные центры сертификации для выдачи сертификатов клиенту и серверу, поэтому давайте создадим клиентский CA и воспользуемся им для подписи сертификата клиента:
Обратите внимание, что поле CommonName (CN) сертификата клиента должно содержать имя учетной записи базы данных, к которой подключается клиент. Сервер PostgreSQL будет использовать его для идентификации клиента.
Конфигурация TLS
Собрав все части вместе, теперь вы можете настроить сервер PostgreSQL для приема соединений TLS:
Последняя часть настройки — обновить файл host-based аутентификации сервера PostgreSQL ( pg_hba.conf ), чтобы требовать TLS для всех подключений и аутентифицировать клиентов с помощью сертификатов X.509:
Теперь пользователи, подключающиеся к серверу, должны будут предоставить действительный сертификат, подписанный клиентским CA:
Стоит обратить внимание, что по умолчанию psql не будет выполнять проверку сертификата сервера, поэтому для параметра sslmode необходимо установить значение verify-full или verify-ca , в зависимости от того, подключаетесь ли вы к серверу PostgreSQL, используя то же имя хоста, которое закодировано в поле CN его сертификата X.509.
Чтобы уменьшить размер команд и не вводить пути к сертификатам TLS каждый раз, когда вы хотите подключиться к базе данных, вы можете использовать файл службы подключения PostgreSQL. Он позволяет группировать параметры подключения в «службы», на которые затем можно ссылаться в строке подключения через параметр «служба».
/.pg_service.conf со следующим содержанием:
Теперь при подключении к базе данных вам нужно будет только указать имя службы и имя базы данных, к которой вы хотите подключиться:
Безопасность на уровне базы данных
До сих пор мы рассматривали, как защитить сервер базы данных PostgreSQL от неавторизованных сетевых подключений, использовать шифрование и убедиться, что сервер и клиенты могут доверять друг другу с помощью взаимной аутентификации TLS. Еще одна часть головоломки — выяснить, что пользователи могут делать и к чему у них есть доступ после подключения к базе данных и подтверждения своей личности. Обычно такая процедура называется авторизацией.
PostgreSQL имеет комплексную систему прав пользователя, основанную на концепции ролей. В современных версиях PostgreSQL (8.1 и новее) «роль» является синонимом «пользователя», поэтому любое имя учетной записи базы данных, которое вы используете, скажем, с psql (например, «user = alice»), на самом деле является ролью с LOGIN атрибутом, который позволяет ей подключаться к базе данных. Фактически, следующие команды SQL эквивалентны:
Помимо возможности входа в систему, роли могут иметь иные атрибуты, позволяющие им обходить все проверки прав доступа ( SUPERUSER ), создавать базы данных ( CREATEDB ), создавать другие роли ( CREATEROLE ) и другие.
Помимо атрибутов, ролям могут быть предоставлены права доступа, которые можно разделить на две категории: членство в других ролях и привилегии на объекты базы данных. Давайте посмотрим, как они работают.
Предоставление роли прав доступа
В нашем воображаемом примере мы будем отслеживать инвентаризацию серверов:
По умолчанию, конфигурация PostgreSQL включает роль суперпользователя (обычно называемую «postgres»), используемую для начальной загрузки базы данных. Использование этой роли для всех операций с базой данных было бы эквивалентно постоянному использованию «root» в Linux, что никогда не являлось хорошей идеей. Вместо этого давайте создадим непривилегированную роль и при необходимости назначим ей права доступа, следуя принципу наименьших привилегий.
Вместо того, чтобы назначать привилегии/роли каждому новому пользователю индивидуально, можно создать «групповую роль» и предоставить другим ролям (сопоставление с отдельными пользователями) членство в этой группе. Скажем, вы хотите разрешить вашим разработчикам, Алисе и Бобу, просматривать список серверов, но не изменять его:
Теперь при подключении к базе данных и Алиса, и Боб унаследуют привилегии групповой роли «разработчик» и смогут выполнять запросы к таблице инвентаризации сервера.
Привилегия SELECT распространяется на все столбцы таблицы по умолчанию, хотя так быть не должно. Скажем, вы всего лишь хотели разрешить своим стажерам просматривать общую информацию об инвентаризации серверов, не позволяя им подключаться, скрывая IP-адрес:
Другие наиболее часто используемые привилегии объектов базы данных — INSERT , UPDATE , DELETE и TRUNCATE аналогичны соответствующим SQL выражениями. Также вы можете назначить права для подключения к определенным базам данных, создания новых схем или объектов в схеме, выполнения функции и так далее. Взгляните на раздел Privileges документации PostgreSQL, чтобы увидеть весь список.
Безопасность на уровне строк
Одной из наиболее продвинутых функций системы привилегий PostgreSQL является безопасность на уровне строк, которая позволяет вам предоставлять привилегии подмножеству строк в таблице. Сюда входят как строки, которые могут быть запрошены с помощью оператора SELECT , так и строки, которые могут быть результатами операторов INSERT , UPDATE и DELETE .
Чтобы начать использовать безопасность на уровне строк, вам нужны две вещи: включить ее для таблицы и определить политику, которая будет контролировать доступ на уровне строк.
Основываясь на предыдущем примере, предположим, что вы хотите разрешить пользователям обновлять только свои собственные серверы. Сначала включите RLS в таблице:
Без какой-либо определенной политики PostgreSQL по умолчанию использует политику «запретить», что означает, что никакая роль (кроме владельца таблицы, который обычно является ролью, создавшей таблицу) не имеет к ней доступа.
Политика безопасности строк — это логическое выражение, которое PostgreSQL будет применять для каждой строки, которая должна быть возвращена или обновлена. Строки, возвращаемые оператором SELECT , проверяются на соответствие выражению, указанному в разделе USING, в то время как строки, обновленные операторами INSERT , UPDATE или DELETE , проверяются на соответствие с WITH CHECK выражением.
Давайте определим пару настроек, которые позволяют пользователям видеть все серверы, но обновлять только свои собственные, определенные полем «владелец»:
Обратите внимание, что только владелец таблицы может создавать или обновлять для нее политику безопасности строк.
Аудит
До сих пор мы в основном говорили о превентивных мерах безопасности. Следуя краеугольным принципам безопасности — defence in depth, мы рассмотрели, как они накладываются друг на друга, чтобы замедлить продвижение гипотетического злоумышленника через систему.
Ведение подробного контрольного журнала — одна из опций безопасности системы, которое часто упускается из виду. Мониторинг доступа на уровне сети или на уровне узла для вашего сервера базы данных выходит за рамки этой статьи, но давайте посмотрим, какие у нас есть возможности, когда дело касается самого сервера PostgreSQL.
Самое простое, что можно сделать для улучшения видимости того, что происходит в базе данных, — это включить подробное ведение журнала. Добавьте следующие параметры в файл конфигурации сервера, чтобы включить ведение журнала всех попыток подключения и всех выполненных операторов SQL:
К сожалению, это практически всё, что можно сделать с помощью стандартной self-hosted установки PostgreSQL из коробки. Конечно, это лучше, чем ничего, но данный метод не масштабируется дальше нескольких серверов баз данных и простого «grepping».
Для более продвинутого аудита PostgreSQL вы можете использовать сторонние решения, такие как pgAudit . Если вы используете self-hosted экземпляр PostgreSQL, вам придется установить расширение вручную. Некоторые размещенные версии, такие как AWS RDS, поддерживают его прямо «из коробки», поэтому вам просто нужно включить его.
Заключение
Как и в случае с любой системой, разработанной с учетом требований безопасности, надлежащая защита доступа к экземпляру базы данных требует принятия защитных мер на нескольких уровнях стека.
В этой статье мы рассмотрели best practices в обеспечении безопасности базы данных PostgreSQL на нескольких уровнях, начиная с сетевой и транспортной безопасности, и изучили, как использовать гибкую систему привилегий пользователей PostgreSQL.
Дата-центр ITSOFT — услуги размещения и аренды серверов и стоек в двух дата-центрах в Москве. За последние годы UPTIME 100%. Размещение GPU-ферм и ASIC-майнеров, аренда GPU-серверов, лицензии связи, SSL-сертификаты, администрирование серверов и поддержка сайтов.
Правила брандмауэра уровня сервера можно использовать для управления общедоступным доступом к узлу координатора Цитус на основе указанного IP-адреса (или диапазона IP-адресов) в общедоступном Интернете.
Предварительные требования
Прежде чем приступить к выполнению этого руководства, необходимы следующие компоненты:
Создание правила брандмауэра на уровне сервера с помощью портала Azure
Эти параметры также доступны во время создания группы серверов Базы данных Azure для PostgreSQL — гипермасштабирование (Citus). На вкладке сети выберите общий доступ (разрешенный IP-адрес).
На странице группы серверов PostgreSQL под заголовком "Безопасность" щелкните Сети, чтобы открыть правила брандмауэра.
Установите флажок Разрешить общий доступ из служб и ресурсов Azure в Azure к этой группе серверов.
При необходимости выберите Разрешить доступ к рабочим узлам. При использовании этого параметра правила брандмауэра будут разрешать доступ ко всем рабочим узлам, а также к узлу координатора.
Щелкните Добавить текущий IP-адрес клиента, чтобы создать правило брандмауэра с общедоступным IP-адресом вашего компьютера, который воспринимается системой Azure.
Кроме того, вы можете нажать +Добавить 0.0.0.0-255.255.255.255 (справа от варианта B), чтобы разрешить доступ к порту узла-координатора 5432 не только для вашего IP-адреса, но и для всего Интернета. В этом случае клиентам по-прежнему необходимо выполнить вход с правильным именем пользователя и паролем, чтобы использовать кластер. Тем не менее мы рекомендуем предоставлять доступ для всего Интернета только на короткие периоды времени и только для нерабочих баз данных.
Проверьте IP-адрес перед сохранением конфигурации. В некоторых случаях IP-адрес, определенный порталом Azure, может отличаться от IP-адреса, используемого для доступа к Интернету и серверам Azure. Таким образом, может потребоваться изменить начальный IP-адрес и конечный IP-адрес, чтобы функция правила была ожидаемой. Используйте поисковую систему или другой сетевой инструмент, чтобы проверить собственный IP-адрес. Например, выполните поиск текста "какой у меня IP-адрес".
Добавьте дополнительные диапазоны адресов. В правилах брандмауэра можно указать отдельный IP-адрес или диапазон адресов. Если вы хотите ограничить правило, указав отдельный IP-адрес, введите его в полях начального и конечного IP-адресов. Открытие брандмауэра позволяет администраторам, пользователям и приложениям получать доступ к узлу-координатору через порт 5432.
На панели инструментов нажмите кнопку Сохранить, чтобы сохранить это правило брандмауэра уровня сервера. Дождитесь подтверждения успешного обновления правил брандмауэра.
Подключение из Azure
Существует простой способ предоставить доступ к базе данных Гипермасштабирования (Citus) приложениям, размещенным в Azure (например, приложениям веб-приложений Azure или приложениям, работающим на виртуальной машине Azure). Установите флажок Разрешить службам и ресурсам Azure доступ к этой группе серверов на портале из области сети и нажмите кнопку сохранить.
Этот параметр позволяет настроить брандмауэр так, чтобы разрешить все подключения из Azure, включая подключения из подписок других клиентов. При выборе этого параметра убедитесь, что используемое имя для входа и разрешения пользователя предоставляют доступ только авторизованным пользователям.
Установка PostgreSQL 11 в Windows 10
В процессе установки установите галочки на пунктах:
- 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.
Измените значение в пункте port = 5432 . Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:
Restart-Service -Name postgresql-x64-11
Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.
-
Запустите командную строку.
Основные команды PostgreSQL:
- Проверка установленной версии СУБД: psql –V
- Для создания новой базы данных воспользуйтесь утилитой createdb: createdb -U postgres testdb (где postgres суперпользователь, testdb новая база данных)Введите пароль суперпользователя.
- Проверить список активных баз: Psql -U postgres –l (пароль)
- С помощью инструмента createuser cоздадим нового пользователя: createuser –U postgres operator (где operator -имя нового пользователя)
- Предоставим пользователю привилегии суперпользователя (на практике этого делать не надо). Запустите интерактивную командную оболочку управления PostgreSQL (shell): psql –U postgres . С помощью SQL команды ALTER ROLE предоставим нужные права нашему пользователю: ALTER ROLE operator SUPERUSER CREATEROLE CREATEDB; . Мы предоставили пользователю права суперпользователя, права на создание ролей и баз данных.
- Для выводы списка пользователей и ролей в СУБД выполните команду: \du
PgAdmin: Визуальный редактор для PostgresSQL
Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.
- Для запуска редактора запустите PgAdmin 4 в меню Пуск
- Для доступа нужно ввести пароль суперпользователя postgres
- В панели Servers вы можете раскрыть список активных БД.
- В панели управления возможно быстро создать нового пользователя и группу, предоставить ему права. Для этого Откройте меню Object -> Create -> Create Login/Group.
- Для создания новой базы данных достаточно выбрать: Database в меню Object -> Create. В новом поле указать имя базы и владельца.
По умолчанию все созданные базы хранятся в каталоге 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
);
После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.
Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.
Здесь вы можете заполнить данные в таблице.
После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;
Читайте также: