на каком порту работает postgresql
путаница портов postgresql 5433 или 5432?
Я установил postgresql на OSX. Когда я запускаю psql, я получаю
однако, из /etc / services
5433 занято Пирро, 5432 назначено на стр. Я могу связаться с
но почему psql думает, что это 5433 и как я могу заставить psql выглядеть в нужном месте по умолчанию?
7 ответов
/etc/services является только консультативным, это список известных портов. Это не означает, что что-либо действительно работает на этом порту или что именованная служба будет работать на этом порту.
в случае PostgreSQL обычно используется порт 5432, если он доступен. Если это не так, большинство установщиков выберет следующий свободный порт, обычно 5433.
вы можете увидеть, что на самом деле работает с помощью netstat инструмент (доступен в OS X, Windows и Linux, с синтаксисом командной строки разной во всех трех).
это еще больше усложняется в системах Mac OS X ужасным беспорядком различных пакетов PostgreSQL-древней версии Apple PostgreSQL, встроенной в ОС Postgres.app, Homebrew, Macports, установщик EnterpriseDB и т. д.
в конечном итоге происходит то, что пользователь устанавливает Pg и запускает сервер из одной упаковки, но использует psql и libpq клиент из другой упаковки. Обычно это происходит, когда они используется Postgres.app или homebrew Pg и подключение с psql который поставляется с ОС. Они не только иногда имеют разные порты по умолчанию, но и Pg, поставляемый с Mac OS X, имеет другой путь сокета unix по умолчанию, так что даже если сервер работает на том же порту, он не будет слушать тот же сокет Unix.
Как настроить удаленное подключение к БД 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:
💡 В качестве ключа указываем приватный ключ 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.
Частые вопросы
Как настроить удаленное подключение к БД PostgreSQL из Windows обсуждалось в этой статье. Я надеюсь, что теперь вы сможете настроить pgAdmin для подключения к PostgreSQL (прямое подключение или используя SSH туннель). Однако, если вы столкнетесь с каким-то проблемами при настройке сервера или pgAdmin, не стесняйтесь написать в комментариях. Я постараюсь помочь.
PostgreSQL (Русский)
Эта статья или раздел нуждается в переводе
Эта статья описывает как настроить PostgreSQL и интегрировать ее с PHP и Apache. Она также описывает, как сделать PostgreSQL доступным из клиента удалённого доступа. Считаем, что PHP и Apache уже установлены. Если вам нужна помощь настройки любой из этих программ, смотрите LAMP и следуйте всем разделам, кроме связанного с MySQL.
Contents
Установка PostgreSQL
Создание Вашей первой базы данных
-D Пользователь не может создавать базы данных
-R Пользователь не может создавать аккаунты
-S Пользователь не является суперпользователем
-P Запрашивать пароль при создании
С другой стороны, вы можете использовать команду createuser без параметров. Вывод в терминале выглядит так:
Знакомство с PostgreSQL
Доступ к оболочке базы данных
Есть, конечно, много других мета-команд, но именно эти должны помочь вам начать работу.
Настройка удалённого доступа к PostgreSQL
Из-под пользователя root редактируем файл
В разделе connections and authentications раскомментируйте или исправьте строку listen_addresses по вашему желанию на
где your_desired_ip_address — IP-адрес клиента.
После этого необходимо перезапустить демон, чтобы изменения вступили в силу
Если возникли проблемы взгляните на лог-файл сервера
Не менее важные ошибки можно увидеть командой:
Настройка PostgreSQL для работы с PHP
Настройка PostgreSQL для работы с HHVM
Если вы используете не ночную версию, то выполните это команду (проверено на HHVM 3.6.1), чтобы избежать ошибок компиляции:
Скопируем скомпилированное расширение:
Затем в /etc/hhvm/server.ini добавляем:
Изменение кодировки новой базы данных на UTF-8 (по вашему усмотрению)
1. Первое, мы должны сбросить template1. Шаблоны не могут быть сброшены, так что мы сначала изменим его, как обычную базу данных:
2. Сейчас уже можно сбросить её:
3. Создаём новую базу данных, с новой кодировкой по умолчанию из template0:
4. Теперь снова сделаем template1 шаблоном:
5. (Рекомендация) Документация по PostgreSQL advises рекомендует «замораживать» изменения шаблона функцией VACUUM FREEZE:
6. (По желанию) Если вы не хотите, чтобы кто-либо подключался к этому шаблону, присвойте параметру datallowconn значение FALSE:
Теперь вы можете создать базу данных используя стандартные команды в терминале:
Если снова войти в PSQL и проверить базу данных, вы должны увидеть правильную кодировку новой базы данных:
Решение проблем
Ускорение мелких транзакций
Если вы используете PostgreSQL на своей локальной машине для разработки и он медленный, то можете попробовать отключить synchronous_commit в конфигурации. Однако, не забывайте про его особенности.
Запретить запись на диск во время бездействия
PostgreSQL периодически обновляет свою статистику, лежащую в файле. По умолчанию этот файл находится на диске, что не даёт отдыхать (и изнашивает) жёсткому диску, заставляя его шуршать. Однако можно легко и безопасно поменять локацию файла внутрь ФС (/run) расположенной в ОЗУ с помощью такой настройки:
Обеспечение безопасности базы данных PostgreSQL
Введение
Базы данных — это Святой Грааль для хакеров, поэтому их необходимо защищать с особой тщательностью. Это первая из серии статей, в которых мы дадим обзор best practice в обеспечении безопасности баз данных. Мы начнем с одной из самых популярных СУБД с открытым исходным кодом, PostgreSQL, и рассмотрим несколько уровней безопасности, о которых стоит задуматься:
Безопасность на сетевом уровне
Безопасность на транспортном уровне
Безопасность на уровне базы данных
Безопасность на сетевом уровне
Файрволы
В идеале ваш сервер PostgreSQL должен быть полностью изолированным и не допускать никаких входящих подключений, SSH или psql. К сожалению, PostgreSQL не поддерживает стандартную настройку такого типа.
Примечание. При обновлении правил iptables рекомендуется использовать iptables-apply, который автоматически откатывает изменения в случае, если вы случайно заблокируете себя.
Приведенное выше правило PostgreSQL позволит любому подключаться к порту 5432. Его можно сделать более строгим, принимая подключения только с определенных IP-адресов или подсетей:
Возвращаясь к нашему идеальному сценарию, возможность полностью предотвратить входящие подключения к порту 5432 потребует своего рода локального агента, который поддерживает постоянное исходящее соединение с клиентским узлом (-ами) и имеет возможность проксировать трафик на локальный экземпляр PostgreSQL.
Такой метод называется «обратным туннелированием». Его можно продемонстрировать при помощи удаленного перенаправления портов. Вы можете открыть обратный туннель, выполнив следующую команду с узла, на котором работает база данных PostgreSQL:
Конечно, должен быть доступным из узла PostgreSQL и иметь запущенного SSH-демона. Команда перенаправит порт 5432 на сервере базы данных на порт 5432 на клиентском компьютере, и вы сможете подключиться к базе данных через туннель:
Прослушивание адресов
Хорошей практикой является ограничение адресов, которые прослушивает сервер для клиентских подключений, с помощью параметра listen_addresses файла конфигурации. Если узел, на котором работает PostgreSQL, имеет несколько сетевых интерфейсов, используйте этот параметр, чтобы убедиться, что сервер прослушивает только те интерфейсы, через который клиенты будут подключаться к нему:
Если клиенты, подключающиеся к базе данных, всегда находятся на одном и том же узле (или, скажем, совместно расположены в одном поде Kubernetes с PostgreSQL, работающим в качестве дополнительного контейнера), отключение прослушивания сокетов TCP может полностью исключить сеть из рассматриваемой картины. Запись пустой строки в параметр прослушиваемых адресов заставляет сервер принимать только соединения сокетов домена Unix:
Безопасность на транспортном уровне
Поскольку большая часть всемирной паутины переходит на HTTPS, найдется мало оправданий тому, чтобы не использовать надежное шифрование для соединений с базой данных. PostgreSQL поддерживает TLS (который по-прежнему называется SSL в документации, конфигурации и CLI по legacy-причинам) и позволяет использовать его для аутентификации как сервера, так и клиента.
Серверный TLS
Для аутентификации сервера сначала необходимо получить сертификат, который сервер будет предоставлять подключающимся клиентам. Let’s Encrypt упрощает получение бесплатных сертификатов X.509, например, с помощью инструмента CLI certbot:
Учтите, что по умолчанию certbot использует вызов HTTP-01 ACME для проверки запроса сертификата, который требует действительного DNS для запрошенного домена, указывающего на узел, и порт 80, который должен быть открыт.
Если по какой-то причине вы не можете использовать 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:
Чтобы уменьшить размер команд и не вводить пути к сертификатам 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-адрес:
Безопасность на уровне строк
Чтобы начать использовать безопасность на уровне строк, вам нужны две вещи: включить ее для таблицы и определить политику, которая будет контролировать доступ на уровне строк.
Основываясь на предыдущем примере, предположим, что вы хотите разрешить пользователям обновлять только свои собственные серверы. Сначала включите RLS в таблице:
Без какой-либо определенной политики PostgreSQL по умолчанию использует политику «запретить», что означает, что никакая роль (кроме владельца таблицы, который обычно является ролью, создавшей таблицу) не имеет к ней доступа.
Давайте определим пару настроек, которые позволяют пользователям видеть все серверы, но обновлять только свои собственные, определенные полем «владелец»:
Обратите внимание, что только владелец таблицы может создавать или обновлять для нее политику безопасности строк.
Аудит
До сих пор мы в основном говорили о превентивных мерах безопасности. Следуя краеугольным принципам безопасности — defence in depth, мы рассмотрели, как они накладываются друг на друга, чтобы замедлить продвижение гипотетического злоумышленника через систему.
Ведение подробного контрольного журнала — одна из опций безопасности системы, которое часто упускается из виду. Мониторинг доступа на уровне сети или на уровне узла для вашего сервера базы данных выходит за рамки этой статьи, но давайте посмотрим, какие у нас есть возможности, когда дело касается самого сервера PostgreSQL.
Самое простое, что можно сделать для улучшения видимости того, что происходит в базе данных, — это включить подробное ведение журнала. Добавьте следующие параметры в файл конфигурации сервера, чтобы включить ведение журнала всех попыток подключения и всех выполненных операторов SQL:
К сожалению, это практически всё, что можно сделать с помощью стандартной self-hosted установки PostgreSQL из коробки. Конечно, это лучше, чем ничего, но данный метод не масштабируется дальше нескольких серверов баз данных и простого «grepping».
pgAudit привносит большую структуризацию и детализацию в используемые операторы. Однако имейте в виду, что он по-прежнему основан на журналах, что затрудняет использование, если вы хотите отправить свои журналы аудита в структурированном формате во внешнюю SIEM систему для подробного анализа.
Заключение
Как и в случае с любой системой, разработанной с учетом требований безопасности, надлежащая защита доступа к экземпляру базы данных требует принятия защитных мер на нескольких уровнях стека.
В этой статье мы рассмотрели best practices в обеспечении безопасности базы данных PostgreSQL на нескольких уровнях, начиная с сетевой и транспортной безопасности, и изучили, как использовать гибкую систему привилегий пользователей PostgreSQL.
Дата-центр ITSOFT — услуги размещения и аренды серверов и стоек в двух дата-центрах в Москве. За последние годы UPTIME 100%. Размещение GPU-ферм и ASIC-майнеров, аренда GPU-серверов, лицензии связи, SSL-сертификаты, администрирование серверов и поддержка сайтов.
HowTo Safely Open a PostgreSQL Port for Remote Access?
This document describes how to open a PostgreSQL database port for remote access and includes security considerations for both Linux and Windows.
Why do you Need to Open a Port
You need to open a remote port if you want to access data in the database from a client running on a different computer in the network. The same is true, if you want to use pgAdmin for or similar tools for SQL development.
Check for Port IP
Please check if PostgreSQL is listening on a public port:
The results above (including «127.0.0.1:5432») shows that PostgreSQL is listening only for connects originating from the local computer, so we will have to edit the «postgresql.conf» configuration file. A result including «0.0.0.0:5432» indicates that PostgreSQL is already listening for remote connections.
You can usually ignore the line with «::1», as it refers to the IP v6 protocol, which is rarely used.
Edit postgresql.conf
This file is usually located in /var/lib/pgsql/data/ on Linux or C:\PostgreSQL\data\ on Windows or similar.
In this file we will edit the «listen_address» and «port» parameters, so that they look like below:
Restart PostgreSQL
After that we need to restart PG to activate the changes.
On recent Linux system you have to enter as user root:
Repeating the «Check for Port IP» step above, you should now see that the port IP is «0.0.0.0», meaning that it will accept connections from any remote computer.
Authentication Configuration using pg_hba.conf
pg_hba.conf is located in the same directly as postgresql.conf.
Please add the following two lines at the end of the file:
This means that remote access is allowed using IP v4 and IP v6 to all databases and all users using the «md5» authentication protocol.
Please «restart postgresql» again.
Open Linux Firewall Port
Does your PostgreSQL database run on a Linux server with the firewall enabled (like the [[https://sourceforge.net/projects/project-open/files/project-open/V5.0/|CentOS 7 ]project-open[ virtual appliance]])?
In this case you will have to poke a hole (as root):
Open Windows Firewall Port
Does your PostgreSQL database run on a Windows server with firewall enabled?
Setting PostgreSQL Passwords
In order to set the PostgreSQL password for the user «projop» (or whatever user. ) you need to connect locally to the database using an account with administration rights.
In the ]project-open[ Centos 7 virtual appliance just enter «psql» in a local terminal window running as user «projop».
Then please issue the following SQL command:
Connection Check via «psql»
Now you should be able to connect to the server using the standard PostgreSQL client «psql», the PG administration tool pgAdmin or any other application with a PostgreSQL interface. We will check with the command line «psql» tool on the remote computer:
As a result you should see «Password for user projop». This means that the port is open and active.
Then enter the «secret» password set above.
An error message like «psql: could not connect to server: No route to host. Is the server running on host «192.168.0.10» and accepting TCP/IP connections on port 5432?» indicates that some of the steps above did not work out.