PostgreSQL или Postgres — это универсальная объектно-реляционная система управления базами данных с открытым исходным кодом, обладающая множеством дополнительных функций, которая позволяет создавать отказоустойчивые среды или сложные приложения.

В этом руководстве вы узнаете, как установить сервер базы данных PostgreSQL в Ubuntu 20.04, и изучите основы администрирования базы данных PostgreSQL.

Выполните следующие команды, чтобы установить сервер PostgreSQL в Ubuntu:

sudo apt update
sudo apt install postgresql postgresql-contrib

Мы также устанавливаем пакет contrib PostgreSQL, который предоставляет несколько дополнительных функций для системы баз данных PostgreSQL.

После завершения установки служба PostgreSQL запустится автоматически. Используйте psqlинструмент для проверки установки, подключившись к серверу базы данных PostgreSQL и распечатав его версию :

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

Вот и все. PostgreSQL установлен, и вы можете начать его использовать.

Роли PostgreSQL и методы аутентификации

Разрешения на доступ к базе данных в PostgreSQL обрабатываются с помощью концепции ролей. Роль может представлять пользователя базы данных или группу пользователей базы данных.

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

  • pg_hba.confДоверие — роль может подключаться без пароля, если выполняются условия, определенные в файле.
  • Пароль. Роль может подключиться, указав пароль. Пароли могут быть сохранены как scram-sha-256, md5, и password(открытый текст).
  • Ident — поддерживается только для соединений TCP/IP. Он работает путем получения имени пользователя операционной системы клиента с необязательным отображением имени пользователя.
  • Peer — то же, что и Ident, но поддерживается только для локальных подключений.

Аутентификация клиента PostgreSQL определяется в файле конфигурации с именем pg_hba.conf. По умолчанию PostgreSQL использует одноранговый метод аутентификации для локальных подключений.

Пользователь postgresсоздается автоматически при установке PostgreSQL. Этот пользователь является суперпользователем экземпляра PostgreSQL и эквивалентен привилегированному пользователю MySQL.

Чтобы войти на сервер PostgreSQL в качестве postgresпользователя, сначала переключитесь на пользователя , а затем откройте приглашение PostgreSQL с помощью psqlутилиты:

sudo su - postgres
psql

Отсюда вы можете взаимодействовать с вашим экземпляром PostgreSQL. Чтобы выйти из оболочки PostgreSQL, введите:

\q

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

sudo -u postgres psql

Как правило, вы должны войти на сервер базы данных postgresтолько с локального хоста.

Создание роли и базы данных PostgreSQL

Только суперпользователи и роли с CREATEROLEпривилегиями могут создавать новые роли.

В следующем примере показано, как создать новую роль с именем johnбазы данных johndbи предоставить права доступа к базе данных:

Создайте новую роль PostgreSQL:

sudo su - postgres -c "createuser john"

Создайте новую базу данных PostgreSQL:

sudo su - postgres -c "createdb johndb"

Чтобы предоставить пользователю права доступа к базе данных, подключитесь к оболочке PostgreSQL:

sudo -u postgres psql

и выполните следующий запрос:

GRANT ALL PRIVILEGES ON DATABASE johndb TO john;

Включить удаленный доступ к серверу PostgreSQL

По умолчанию сервер PostgreSQL прослушивает только локальный интерфейс ( 127.0.0.1).

Чтобы включить удаленный доступ к вашему серверу PostgreSQL, откройте файл конфигурации postgresql.confи добавьте listen_addresses = '*'в CONNECTIONS AND AUTHENTICATIONраздел.

sudo nano /etc/postgresql/12/main/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

Сохраните файл и перезапустите службу PostgreSQL:

sudo service postgresql restart

Проверьте изменения с помощью ssутилиты:

ss -nlt | grep 5432

Вывод показывает, что сервер PostgreSQL прослушивает все интерфейсы ( 0.0.0.0):

LISTEN  0        244              0.0.0.0:5432           0.0.0.0:*              
LISTEN  0        244                 [::]:5432              [::]:*

Следующим шагом является настройка сервера для приема удаленных подключений путем редактирования pg_hba.confфайла.

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

/etc/postgresql/12/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane can access all databases from all locations using md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane can access only the janedb from all locations using md5 password
host    janedb          jane            0.0.0.0/0                md5

# The user jane can access all databases from a trusted location (192.168.1.134) without a password
host    all             jane            192.168.1.134            trust

Последний шаг — открыть порт 5432в брандмауэре.

Предполагая, что вы используете UFW для управления брандмауэром и хотите разрешить доступ из 192.168.1.0/24подсети, вы должны выполнить следующую команду:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

Убедитесь, что ваш брандмауэр настроен на прием подключений только из доверенных диапазонов IP-адресов.