PostgreSQL

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

Вы получаете полностью управляемый кластер PostgreSQL без необходимости ручной настройки — все происходит автоматически.

При создании кластера можно выбрать версию PostgreSQL: 14, 15, 16 или 17.

Создание баз

При создании нового кластера автоматически создается база с именем default_db. Дополнительные базы можно создавать во вкладке «Базы данных» в интерфейсе.

Пользователи и привилегии

Пользователей можно создавать на вкладке «Пользователи». Для этого нажмите кнопку «Добавить».

В открывшемся окне задайте имя пользователя и пароль:

• Имя: от 3 до 64 символов. Допускаются латинские буквы, цифры и спецсимволы.
• Пароль: от 8 до 30 символов. Допускаются латинские буквы, цифры и спецсимволы.

После создания вы можете настроить привилегии для пользователя.

Чтобы изменить привилегии позже, откройте вкладку «Пользователи», нажмите на три точки рядом с нужным пользователем и выберите пункт «Привилегии».

В окне настройки выберите необходимые привилегии. Можно задать доступ к конкретной базе или ко всем базам сразу, включив опцию «Одинаковые для всех баз».

Доступные привилегии

Расширения

PostgreSQL поддерживает расширения, которые позволяют расширить стандартную функциональность. Ниже представлены расширения, доступные для PostgreSQL:

Для включения нужного расширения перейдите во вкладку «Конфигурация» и нажмите на кнопку «Изменить» в строке «Расширения».

Настройка параметров

Вы можете изменить параметры, используемые для PostgreSQL, перейдя во вкладку «Конфигурация» и нажав на кнопку «Изменить параметры» в строке «Параметры базы данных».

Для получения дополнительной информации о параметрах нажмите кнопку «Открыть описание» рядом с нужным параметром.

Чтобы применить изменения, нажмите кнопку «Применить» внизу страницы.

Подключение к базе

Подключиться к базе можно через встроенный веб-интерфейс — Adminer. Для этого нажмите кнопку «Веб-интерфейс» и выберите Adminer.

Подключение из терминала

Установите клиент PostgreSQL:

sudo apt install postgresql-client

sudo pacman -S postgresql-libs

sudo dnf install postgresql

Существует три способа подключения к кластеру PostgreSQL:

• по публичному IP;
• по приватному IP;
• по домену.

Команду подключения можно скопировать:

• на вкладке «Дашборд»;

• на вкладке «Подключение».

При подключении по домену используется защищенное подключение по TLS. В новых кластерах оно включено по умолчанию, в старых — требуется включение вручную. 

Чтобы включить TLS:

1. Перейдите во вкладку «Подключение».
2. Нажмите кнопку «Включить защищенное подключение».
3. Дождитесь завершения настройки.

После включения TLS для подключения потребуется установить сертификат.

Готовые команды для установки доступны в панели управления, во вкладке «Подключение», для всех популярных операционных систем.

Создание ролей

Пользователь с привилегией CREATE ROLE может:

• создавать новых пользователей (CREATE USER);
• создавать роли (CREATE ROLE);
• управлять членством в ролях (GRANT ... TO ...).

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

Пользователь — это роль, которой разрешено авторизовываться в базе. Такая роль может подключаться к базе с логином и паролем.

Создать пользователя с паролем можно при помощи запроса:

CREATE USER someuser WITH PASSWORD 'password';

Где someuser — логин пользователя, password — пароль.

Новый пользователь отобразится в панели управления DBaaS через некоторое время после создания.

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

Вместо того чтобы напрямую раздавать права каждому пользователю, лучше создавать роли с нужными правами, а потом назначать пользователей в эти роли.

Создадим две роли: одна с правами только на чтение, вторая — с правами на чтение и запись.

Роль с правами только на чтение:

CREATE ROLE app_reader;

Роль с правами на чтение и запись:

CREATE ROLE app_writer;

Назначим роль пользователю:

GRANT app_writer TO someuser;

Настройка схемы

По умолчанию все таблицы в PostgreSQL создаются в схеме public. Для выдачи грантов необходимо создать новую схему:

CREATE SCHEMA app_data;

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

CREATE TABLE app_data.users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE app_data.orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES app_data.users(id),
  order_date TIMESTAMP DEFAULT now()
);

Теперь можем выдать права на схему для ранее созданных ролей:

GRANT USAGE ON SCHEMA app_data TO app_reader;
GRANT USAGE ON SCHEMA app_data TO app_writer;

И право на создание объектов в схеме:

GRANT CREATE ON SCHEMA app_data TO app_writer;

Установка прав на таблицы

Выдадим права на чтение всех существующих таблиц в схеме:

GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO app_reader;

И права на запись:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_data TO app_writer;

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

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT SELECT ON TABLES TO app_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

И права по умолчанию для новых последовательностей:

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

Создание дампа

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

pg_dump -x 'postgresql://<user>:<password>@<host>:5432/<db>' | gzip > default_db.sql.gz

Параметр -x (--no-privileges) исключает из дампа команды GRANT и REVOKE, так как назначение прав осуществляется через панель. Без этого флага возможны ошибки при загрузке дампа, хотя они не помешают восстановлению данных.

Загрузка дампа

Для загрузки дампа выполните:

zcat default_db.sql.gz | psql 'postgresql://<user>:<password>@<host>:5432/<db>'

Команда распаковывает дамп и передает его в клиент PostgreSQL для выполнения.

Репликация

Кластеры PostgreSQL в DBaaS используют связку Patroni и Etcd для обеспечения отказоустойчивости. Репликация работает по принципу leader-replica: один узел принимает все записи (лидер), а остальные получают изменения в асинхронном режиме.

В случае сбоя лидера система автоматически переключает роль на одну из реплик, обеспечивая высокую доступность базы данных.