Установка PostgreSQL на CentOS 8 и его настройка с созданием базы и подключением удаленно


30 апреля 2020


Настройка и установка PostgreSQL на CentOS 8 с созданием роли и удаленным подключением

PostgreSQL - это реляционная база данных, которая распространяется свободно (бесплатная) с открытым исходным кодом. По популярности находится на 4-ом месте. В этой статье будет рассмотрены способы установки базы данных на CentOS 8 или RHEL 8, но и на предыдущих версиях установка будет аналогичной. Мы так же рассмотрим возможность создание первой базы, добавления в нее данных и другие настройки через шелл.

Установка

Вы можете установить сервер баз данных PostgreSQL из официального репозитория и из репозитория CentOS. Единственное различие в этих двух способах в том, что в официальном репозитории PostgreSQL будет более новая версия.

Сама установка делится на 2 пакета:

  • postgresql-server - сервер базы данных;
  • postgresql-contrib - дополнительные инструменты для работы с базами данных. Например для анализа или администрирования. Не входят в основной пакет, так как считается, что он нужен не всем. Пакет поддерживается теми же разработчиками, что и сервер. Устанавливать не обязательно, но рекомендуется.

С помощью репозитория CentOS

Установка пакетов выполняется следующей командой:

sudo yum install postgresql-server postgresql-contrib

Установка PostgreSQL из репозитория CentOS

Следующая команда создаст системные базы данных, директории и системные таблицы:

sudo postgresql-setup initdb

Инициализация PostgreSQL на CentOS

Для работы PostgreSQL нужно запустить демон и я рекомендую включить автоматический запуск, что бы после перезагрузки он запускался автоматически. Это делается следующей командой:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Включение сервиса PostgreSQL на CentOS

Если этот шаг у вас завершился с ошибками, значит что не корректно была выполнена команда initdb. Ошибки могут быть следующими:

  • Failed to start postgresql.service: Unit postgresql.service not found;
  • Failed to enable unit: Unit file postgresql.service does not exist.

О том что сервис включен говорят следующие значения:

systemctl status postgresql

Проверка работы сервиса PostgreSQL на CentOS

Для проверки корректной установки так же можно запустить команду, которая подключится к серверу PostgreSQL и вернет его версию, для проверки корректной установки:

sudo -u postgres psql -c "SELECT version();"

Запрос версии PostgreSQL на CentOS

Вы можете получить следующую ошибку:

  • could not change directory to “/home/user”: Permission denied

Говорит о том, что пользователь 'postgres' (который был создан автоматически с сервером PostgreSQL) не имеет прав на указанный каталог для создания файла .psql_history. В рамках примера эта ошибка не критична и может не отображаться вовсе, если вы выполняете команду не из домашнего каталога. Если, в последующем, она у вас так же появится, нужно будет добавить права на исполнение ('x') этой директории.

Установка из репозитория PostgreSQL

Как можно увидеть в примере выше, то из репозитория CentOS была доступна только 10 версия PostgreSQL. Если добавить в вашу ОС ссылку на официальный репозиторий PostgreSQL, то, скорее всего, у нас станет доступна более новая версия. На момент написания статьи, самая последняя актуальная версия PostgreSQL - 12. Вы можете проверить последнюю версию на официальном сайте проекта.

Установим репозиторий:

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Узнать какие версии стали доступны можно через следующую команду:

yum list postgresql*-server --disablerepo=AppStream

Установка сервера PostgreSQL 12 на CentOS

Установим сервер базы данных и пакет приложений:

sudo yum install postgresql12-server postgresql12-contrib --disablerepo=AppStream

Создадим системные базы и таблицы:

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

Инициализация PostgreSQL 12 на CentOS

Запустим и включим демон, что бы после перезагрузки он это делал автоматически:

sudo systemctl start postgresql-12
sudo systemctl enable postgresql-12

Запуск демона PostgreSQL 12 на CentOS

О том что демон включен и работает говорят следующие значения:

sudo systemctl status postgresql-12

Проверка демона PostgreSQL 12 на CentOS 

Мы так же можем подключиться к серверу PostgreSQL и запросить версию, что проверит корректность установки:

sudo -u postgres /usr/pgsql-12/bin/psql -c "SELECT version();"

Проверка работы PostgreSQL 12 на CentOS

 

Создание ролей и пользователей

При установке сервера баз данных автоматически создается пользователь 'postgres'. Он же и является суперпользователем для баз данных. Общая рекомендация для этого пользователя такая же как и для root - по возможности не работать от него и отсутствие возможности удаленного подключения, но можно использовать и его. Ниже будут рассмотрена возможность создания нового пользователя для удаленного подключения и включения такой возможности для пользователя 'postgres'.

Вы можете задать пароль для учетной записи postgres в CentOS. Учите, что это роль имеет возможность менять конфигурационные записи сервиса, сбрасывать пароли и многое другое:

sudo passwd postgres

Смена пароля пользователя postgres на CentOS 

Далее переключимся на пользователя postgres и установим ему пароль "Password123":

su - postgres
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'Password123';"

Смена пользователя на postgres в CentOS

Создание пользователя и роли базы данных

Ранее, PostgreSQL, имела концепцию пользователей и групп. Пользователи входили в группы, а на группы распространялись права. Эта концепция была изменена на роли и теперь, вне зависимости от того что вы хотите создать, вы создаете их. Роль может входить в другую роль и наследовать эти права. Каждая роль так же может быть с правом подключения к серверу "Login" и без него. Если вам будет легче, то вы можете воспринимать роль следующим образом:

  • Пользователь - это роль с правом подключения "Login";
  • Группа - это роль без права подключения.

Эти роли можно создавать через консоль psql в формате SQL запросов. По умолчанию, если не указывать для psql пользователя, сверяется локальный с тем, что есть в базах. Как уже говорилось - единственным созданным пользователем является posgres. Подключимся через него:

su - postgres 
psql

Запуск консоли psql PostgreSQL 12 на CentOS

Для создания роли есть две команды, где 'CREATE ROLE' создает только роль, а 'CREATE USER' - создает роль с правом входа 'LOGIN'. В примере ниже два одинаковых примера создающие пользователя 'TDB_admin' с правом входа:

CREATE USER TDB_admin;
# или
CREATE ROLE TDB_admin LOGIN;

Что бы увидеть созданные роли - выполните следующую команду:

\du

Создание роли на PostgreSQL с Login на CentOS

Как можно увидеть - у пользователя postgres есть поле атрибутов, которое определяет его права. Сами же атрибуты тоже являются наборами прав. Выделю 3 типа атрибутов, которые в основном используются:

  • Superuser - все возможные права;
  • Create role - создание ролей (без прав superuser);
  • Create DB - создание баз.

Так как роль, которая создала объект, может распоряжаться им полностью - дадим такое право созданному пользователю. Отмечу, что название атрибутов пишутся без пробелов:

ALTER ROLE TDB_admin CreateDB;

Выдача прав роли в PostgreSQL

Зададим пароль 'Password123' созданному пользователю:

ALTER ROLE TDB_admin PASSWORD 'Password123';

Все указанные выше команды можно поместить в одну:

CREATE USER "TDB_admin3" with CreateDB PASSWORD 'Password123';

Для выхода из консоли вы можете использовать следующую команду:

\q

Отмечу, что следующая команда подключается к консоли psql под новым пользователем и паролем, но с ошибкой указывающей на неверный метод аутентификации. Рассмотрение методов аутентификаций и подключения будет показано ниже.

psql -U TDB_admin -W
  • psql: error: could not connect to server: FATAL:  Peer authentication failed for user

Создание базы и выдача прав на объект

Если мы создадим базу под пользователем 'postgres', то он станет ее владельцем, а пользователь 'TDB_admin' не будет иметь на нее прав. Это можно исправить.

Создадим базу:

CREATE DATABASE Test_DB;

Создание базы данных в PostgreSQL на CentOS

Следующей командой мы сменим существующего владельца на 'TDB_admin'. 

ALTER DATABASE Test_DB OWNER TO TDB_admin;

Следующей командой мы увидим список всех баз и их владельцев:

\l;

Смена владельца базы в PostgreSQL на CentOS

Выйти из консоли можно использовав:

\q;

 

Настройка удаленного подключения к базе

PostgreSQL может использовать множество протоколов аутентификации. Самые распространенные способы:

  • Trust - можно подключаться без использования пароля;
  • Password - подключение только используя пароль;
  • Ident - возможность удаленного подключение с именем и паролем;
  • Peer - только локальное подключение.

По умолчанию все пользователи создаются с типом аутентификации 'Peer'. Что бы это изменить нужно редактировать файл 'pg_hba.conf'. Для 12-ой версии postgresql этот файл находится по пути:

/var/lib/pgsql/12/data/

Для 10-ой версии, установленной из репозитория CentOS путь следующий:

/var/lib/pgsql/data/

Там же находится файл 'postgresql.conf', который указывает с каких адресов и на какие порты принимать подключения.

Откроем файл 'postgresql.conf' и отредактируем его:

sudo nano /var/lib/pgsql/12/data/postgresql.conf

Настройка подключения для PostgreSQL в файле postgresql.conf на CentOS

Нам нужно:

  1. Снять комментарий со строки 59, которая указывает с какого IP адреса принимать подключения. В качестве адреса заменить localhost на знак '*', говорящий что подключения можно принимать с любого адреса;
  2. Снять комментарий со строки 63, указывающий какой порт нужно слушать.

Теперь нам нужно отредактировать файл 'pg_hba.conf' написав строчку аналогично выделенной:

sudo nano /var/lib/pgsql/12/data/pg_hba.conf

Настройка сетевого доступа для пользователя PostgreSQL в файле pg_hba.conf

На примере выше я добавил 78 и 79 строчку, которая строится по следующему характеру:

  • type - указывает на источник подключения, будет ли он локальный, удаленный или зашифрованный. Принимает значения: host, local, hostssl и hostnossl. Я использовал запись host (78), которая говорит что к базе будут подключаться по TCP/IP с отсутствием или использованием SSL. Запись 'local' - говорит, что пользователь будет подключаться локально (используя сокет);
  • database - можно указать название базы, к которому разрешено подключение. Можно так же указать 'all', что не ограничит доступ к базам;
  • user - аналогично базам мы можем указать пользователя, который будет подключаться. Можно указать 'all';
  • address - адрес с которого вы планируете подключаться. В качестве любого IP адреса можно указать '0.0.0.0/0'. Нужно указывать маску. Маска типа /32 говорит об одном IP адресе. Маска /24 будет говорить об 254 адресах. Для типа соединения local - адрес указывать не нужно;
  • method - тип аутентификации, который был описан выше. В описанном случае используется парольная аутентификация по хэш сумме. В этом поле может быть так же reject, который запрещает соединения.

Далее сохраняем файл и перезагружаем сервис:

sudo systemctl restart postgresql-12
#или
sudo systemctl restart postgresql

Правила фаерволла

Если у вас не получится подсоединиться к базе, вы можете попробовать прописать правила в фаерволле. Пример ниже разрешает подключения с IP адреса указанном у меня в файле 'pg_hba.conf'. Вам нужно будет поменять на свой. PostgreSQL принимает подключения по 5432 порту:

sudo firewall-cmd --permanent --zone=trusted --add-source=192.168.3.1/32
sudo firewall-cmd --permanent --zone=trusted --add-port=5432/tcp
sudo firewall-cmd --reload

Подключение и возможные проблемы

Подключится мы можем используя клиент psql, IDE, модули и т.д. В случае psql, для типа аутентификации MD5, подойдет такая команда:

psql -h 192.168.3.107 -U tdb_admin template1 -W

Где

  • -h - указывает на хост к которому я подключаюсь. Если вы делаете это локально, то указывать не нужно;
  • -U - имя пользователя под которым выполняется подключение;
  • template1 - это база данных к которой нужно подключиться;
  • -W - запрашивает пароль.

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               trust
# или
local   all             all                                     trust

Подключение типа trust снижает требования к типу аутентификации не требуя пароль. Вы так же можете подключаться используя роль postgres исключив возможную проблему с правами. Роль так же должна быть с правом 'LOGIN', о чем писалось выше.

Если проблема не решается - попробуйте перенести ваши записи в файле 'pg_hba.conf' на самый верх.

Так же отмечу, что при подключениях с некоторых IDE (JetBrains), без указания базы, появлялась ошибка. В качестве баз данных вы можете указать 'template1' или 'postgres', которые относятся к системным.

...

Теги: #centos #postgresql


Популярные тэги
Каналы
О блоге
Этот блог представляет собой конспекты выученного материала, преобретенного опыта и лучшие практики в системном администрировании и программировании.