- Как настроить удалённый доступ к PostgreSQL с удалённых серверов
- Настройка доступа избранным ip
- Доступ со всех ip
- Как настроить удаленное подключение к PostgreSQL
- Как работать с пользователями в PostgreSQL
- Создание нового пользователя
- 1. Создание пользователя
- 2. Назначение прав на использование базы данных
- 3. Настройка файла pg_hba.conf
- 4. Проверка
- Настройка прав доступа к базе с помощью групп
- Редактирование пользователя
- 1. Смена пароля
- Удаление пользователей и групп
- Назначение особых прав пользователям PostgreSQL
- Учетная запись для резервного копирования
- Графический интерфейс
Как настроить удалённый доступ к PostgreSQL с удалённых серверов
Есть два метода настройки удалённого доступ к PostgreSQL с внешних серверов: 1) открыть доступ к вашему PostgreSQL вообще со всех серверов в интернете и 2) Вручную настроить те сервера с которых можно будет подключится к вашей базе.
Первый метод хорош только в случае тестового или учебного проекта, так как небольшой баг в коде может раскрыть данные доступа к вашей базе и скомпрометировать всё её содержимое. Если вы думаете, что ваш проект слишком маленький и вас это не касается, то вы ошибаетесь. Недавно я запустил совершенно чистый сервер с новым ip и оставил открытый доступ к базе, уже через час какой-то бот стёр всю БД и предлагал выкуп, за возвтар данных.
Настройка доступа избранным ip
Для того, что бы настроить доступ к PostgreSQL с разрешённых вами серверов нужно для начала открыть доступ к PostgreSQL серверу с внешних серверов. Открываем файл /etc/postgresql/14/main/postgresql.conf Обратите внимание, что у вас может отличатся путь к файлу, например с выходом версии 15 PostgreSQL
В разделе Connection Settings нужно раскомментировать строку 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
Как настроить удаленное подключение к PostgreSQL
Обновлено: 03.04.2021 Опубликовано: 11.10.2016
По умолчанию, сервер баз данных 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
Обновлено: 22.03.2023 Опубликовано: 20.07.2016
Часть нижеописанных операций нужно выполнять в командной оболочке PostgreSQL. Она может быть запущена от пользователя postgres — чтобы войти в систему от данного пользователя, вводим:
* если система выдаст ошибку, связанную с нехваткой прав, сначала повышаем привилегии командой sudo su или su.
Теперь запускаем командную оболочку PostgreSQL:
* в данном примере, вход выполняется от учетной записи postgres к шаблонной базе template1.
Для просмотра всех пользователей СУБД:
Создание нового пользователя
Для того, чтобы была возможность подключения к СУБД PostgreSQL от нового пользователя, необходимо создать данного пользователя, назначить ему права, выполнить настройку файла pg_hba.conf.
1. Создание пользователя
а) Добавление новой роли (пользователя) из оболочки SQL:
=# CREATE USER dmosk WITH PASSWORD ‘myPassword’;
* в примере создана роль dmosk с паролем myPassword.
б) Добавление новой роли (пользователя) из командной строки Linux:
2. Назначение прав на использование базы данных
Даем права на базу командой:
=# GRANT ALL PRIVILEGES ON DATABASE «database1» to dmosk;
Теперь подключаемся к базе, к которой хотим дать доступ:
* в примере подсоединимся к базе с названием database1.
а) Так мы добавим все права на использование всех таблиц в базе database1 учетной записи dmosk:
database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO «dmosk»;
* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.
Посмотреть список всех схем можно командой:
б) Также можно дать доступ к базе для определенных таблиц:
database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO «dmosk»;
* в данном примере мы даем права на таблицу table1.
3. Настройка файла pg_hba.conf
Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.
Для начала смотрим путь до него:
В ответ мы получим, что-то на подобие:
Возвращается в командную строку суперпользователя:
Добавляем права на подключение нашему созданному пользователю:
.
# IPv4 local connections:
host all dmosk 127.0.0.1/32 md5
.
* в данном примере мы разрешили подключаться пользователю dmosk ко всем базам на сервере (all) от узла 127.0.0.1 (localhost) с требованием пароля (md5).
* необходимо, чтобы данная строка была выше строки, которая прописана по умолчанию
host all all 127.0.0.1/32 ident.
В файле уже может быть строка на подобие:
host all all 127.0.0.1/32 md5
Это значит, что у нас будут права на подключение с локального хоста под всеми пользователями с использованием пароля.
После перезапускаем службу:
systemctl restart postgresql-9.6
* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.
4. Проверка
Для теста пробуем подключиться к PostgreSQL с помощью созданного пользователя:
psql -U dmosk -d database1 -h 127.0.0.1
* в данном примере мы подключается к локальному серверу 127.0.0.1 под пользователем dmosk и базе database1, на которую мы дали ему права.
Настройка прав доступа к базе с помощью групп
Сначала создадим групповую роль:
=# CREATE ROLE «myRole» NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
* данной командой создана группа myRole с минимальными правами.
Теперь добавим ранее созданного пользователя dmosk в эту группу:
Подключимся к базе данных, для которой хотим настроить права
и предоставим все права для группы myRole всем таблицам базы database1
database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO GROUP «myRole»;
Редактирование пользователя
1. Смена пароля
Рассмотрим несколько примеров смены пароля пользователя.
=# ALTER USER postgres PASSWORD ‘password’
* в данном примере мы зададим пароль password для пользователя postgres.
* после ввода данной команды система потребует дважды ввести пароль для пользователя (в нашем примере, postgres).
Из командной строки Linux:
sudo -u postgres psql -U postgres -d postgres -c «ALTER USER postgres PASSWORD ‘password'»
* по сути, мы выполняем также запрос в оболочке sql.
Удаление пользователей и групп
Удаление пользователя выполняется следующей командой:
database1=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM «dmosk»;
* обратите внимание, данный запрос отличается от предоставления прав двумя моментами: 1) вместо GRANT пишем REVOKE; 2) вместо TO «dmosk» пишем FROM «dmosk»;
Назначение особых прав пользователям PostgreSQL
Помимо ALL PRIVILEGES можно выдавать права на особые операции, например:
=# GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO «dmosk»;
* команда позволит выдать права на получение данных, их обновление и добавление. Другие операции, например, удаление будут запрещены для пользователя dmosk.
Назначение прав для определенной таблицы:
database1=# GRANT ALL PRIVILEGES ON table_users TO «dmosk»;
* в данном примере мы предоставим все права на таблицу table_users в базе данных database1;
Учетная запись для резервного копирования
Для выполнения резервного копирования лучше всего подключаться к базе с минимальными привилегиями.
Сначала создаем роль, которую будем использовать для выполнения резервного копирования:
=# CREATE USER bkpuser WITH PASSWORD ‘bkppasswd’;
* мы создадим учетную запись bkpuser с паролем bkppasswd.
Предоставляем права на подключения к базе
=# GRANT CONNECT ON DATABASE database TO bkpuser;
* в данном примере к базе database.
Подключаемся к базе (в нашем примере database):
Даем права на все последовательности в схеме:
=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser;
* мы дали права для схемы public. Это схема является схемой по умолчанию, но в вашем случае она может быть другой. В таком случае, подставляем свое значение.
Графический интерфейс
Иногда проще воспользоваться программой для выставления прав и работы с PostgreSQL. Могу посоветовать приложение pgAdmin. Оно позволит в оконном режиме не только создать и удалить пользователей, но и полноценно работать с СУБД.