PostgreSQL, часто называемый просто Postgres, представляет собой универсальную систему управления объектно-реляционными базами данных с открытым исходным кодом. Она имеет множество мощных функций, таких как онлайн резервное копирование, восстановление на определенный момент времени, вложенные транзакции, запросы SQL и JSON, многоуровневое управление параллелизмом (MVCC), асинхронная репликация и многое другое.
Установка PostgreSQL
На момент написания этой статьи последней версией PostgreSQL, доступной из репозиториев Debian по умолчанию, была PostgreSQL версии 11.5.
Для установки PostgreSQL на ваш сервер Debian выполните следующие шаги в качестве root или пользователя с привилегиями sudo:
1. Начните с обновления пакетов APT:
sudo apt update
2. Установите PostgreSQL сервер и пакет для разработки, предоставляющий дополнительные возможности для базы данных PostgreSQL:
sudo apt install postgresql postgresql-contrib
3. После завершения установки запустится служба PostgreSQL. Для проверки установки используйте инструмент psql для вывода версии сервера:
sudo -u postgres psql -c "SELECT version();"
Вывод должен выглядеть примерно так:
PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
psql - это интерактивная терминальная программа, позволяющая взаимодействовать с PostgreSQL сервером.
Роли и методы аутентификации PostgreSQL
PostgreSQL обрабатывает разрешения доступа к базам данных, используя концепцию ролей. В зависимости от того, как вы настраиваете роль, она может представлять пользователя базы данных или группу пользователей базы данных.
PostgreSQL поддерживает несколько методов аутентификации. Наиболее часто используются следующие методы:
- Trust - роль может подключаться без пароля, если выполняются критерии, определенные в файле pg_hba.conf.
- Password - роль может подключаться, предоставляя пароль. Пароли могут храниться в виде scram-sha-256 md5 и пароля (чистый текст).
- Ident - поддерживается только для TCP/IP соединений. Он работает путем получения имени пользователя операционной системы клиента с возможностью отображения имени пользователя.
- Peer - такой же, как и Ident, но поддерживается только при локальных подключениях.
Аутентификация клиента PostgreSQL определяется в конфигурационном файле pg_hba.conf. Для локальных подключений PostgreSQL настроен на использование метода peer аутентификации.
Пользователь postgres автоматически создается при установке PostgreSQL. Этот пользователь является суперпользователем экземпляра PostgreSQL, и он эквивалентен корневому пользователю в MySQL.
Чтобы войти на сервер PostgreSQL в качестве "postgres", переключитесь на пользователя postgres и получите доступ к PostgreSQL командной строке с помощью утилиты psql:
sudo su - postgres
psql
Отсюда вы можете взаимодействовать с PostgreSQL сервером. Выход из оболочки PostgreSQL:
\q
Вы можете использовать команду sudo для доступа к PostgreSQL командной строке без переключения пользователей:
sudo -u postgres psql
Пользователь postgres обычно используется только с локального хоста.
Создание базы данных и ролей PostgreSQL
1. Во-первых, создайте роль, выполнив следующую команду:
sudo su - postgres -c "createuser worker"
2. Затем создадим базу данных с помощью команды createdb:
sudo su - postgres -c "createdb worker-db"
3. Чтобы предоставить права доступа пользователю к базе данных, подключитесь к оболочке PostgreSQL:
sudo -u postgres psql
Выполните следующий запрос:
grant all privileges on database worker-db to worker;
Разрешить удаленный доступ к PostgreSQL Server
По умолчанию PostgreSQL, сервер слушает только на локальном хосте 127.0.0.0.1.
Если вы хотите подключаться к PostgreSQL серверу из удаленных мест, вам нужно настроить сервер на прослушивание публичного порта и изменить конфигурацию для приема удаленных подключений.
Откройте конфигурационный файл postgresql.conf и добавьте в раздел CONNECTIONS AND AUTHENTICATION адрес list_address = '*'. Это указывает серверу прослушивать все сетевые порты.
sudo nano /etc/postgresql/11/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
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:*
LISTEN 0 128 [::]:5432 [::]:*
Вывод должен показать, что PostgreSQL сервер слушает все порты (0.0.0.0.0).
Последним шагом будет настройка сервера на прием удаленных подключений путем редактирования файла pg_hba.conf.
Ниже приведены несколько примеров, демонстрирующих различные варианты использования:
TYPE DATABASE USER ADDRESS METHOD The user jane will be able to access all databases from all locations using an md5 password host all jane 0.0.0.0/0 md5 The user jane will be able to access only the janedb from all locations using an md5 password host janedb jane 0.0.0.0/0 md5 The user jane will be able to access all databases from a trusted location (10.10.10.250) without a password host all jane 10.10.10.250 trust