Linux

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

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

Для каждого приложения лучше создавать отдельную базу данных и отдельную роль PostgreSQL.

Не рекомендуется подключать приложение к базе от имени административной роли postgres. Отдельная роль упрощает разграничение доступа, аудит, смену пароля и удаление приложения.

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

Проверено на: Ubuntu Server 24.04 LTS
Уровень сложности: начальный
Время выполнения: около 10–15 минут
Требуемый доступ: локальный административный доступ к PostgreSQL

Что будет настроено

После выполнения инструкции:

  • будет создана отдельная роль приложения;
  • роли будет назначен пароль;
  • будет создана отдельная база данных;
  • роль станет владельцем базы;
  • будет проверено подключение;
  • будут проверены привилегии;
  • будет показано безопасное удаление тестовых объектов.

Используемые примеры

В статье используются:

База данных: app_db
Роль: app_user
Пароль: STRONG_PASSWORD

Замените значения на свои.

Не используйте реальные пароли в открытой документации, shell history и репозиториях.

Подключение к PostgreSQL

sudo -u postgres psql

Ожидается приглашение:

postgres=#

Проверка текущей роли

SELECT current_user;

Ожидается:

postgres

Создание роли с паролем

CREATE ROLE app_user
WITH
    LOGIN
    PASSWORD 'STRONG_PASSWORD';

Параметр:

LOGIN

разрешает роли подключаться к PostgreSQL.

Без него роль можно использовать как группу привилегий, но не как самостоятельную учётную запись.

Безопасный ввод пароля

Команда с паролем может сохраниться в истории psql.

Безопаснее создать роль без пароля:

CREATE ROLE app_user
WITH LOGIN;

Затем выполнить:

\password app_user

psql запросит пароль интерактивно и не покажет его на экране.

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

\du app_user

Или:

SELECT
    rolname,
    rolcanlogin,
    rolsuper,
    rolcreatedb,
    rolcreaterole,
    rolreplication
FROM pg_roles
WHERE rolname = 'app_user';

Для обычной роли приложения ожидается:

rolcanlogin = true
rolsuper = false
rolcreatedb = false
rolcreaterole = false
rolreplication = false

Создание базы данных

CREATE DATABASE app_db
OWNER app_user;

Роль app_user станет владельцем базы.

Проверка базы

\l app_db

Или:

SELECT
    datname,
    pg_get_userbyid(datdba) AS owner
FROM pg_database
WHERE datname = 'app_db';

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

\c app_db

Проверьте:

SELECT
    current_database(),
    current_user;

Текущая роль всё ещё будет postgres, потому что подключение выполнено от административного пользователя.

Выход из psql

\q

Проверка подключения от имени приложения

psql   -h 127.0.0.1   -U app_user   -d app_db

PostgreSQL запросит пароль.

После подключения:

SELECT
    current_database(),
    current_user;

Ожидается:

app_db
app_user

Почему используется 127.0.0.1

Подключение:

psql -U app_user -d app_db

без -h обычно выполняется через Unix socket и может использовать peer-аутентификацию.

Подключение через:

127.0.0.1

использует TCP и соответствующее правило pg_hba.conf.

Если TCP-аутентификация ещё не настроена, подключение может завершиться ошибкой. Это не означает, что база или роль созданы неправильно.

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

Подключитесь от имени app_user:

psql   -h 127.0.0.1   -U app_user   -d app_db

Создайте таблицу:

CREATE TABLE test_items (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Добавьте запись:

INSERT INTO test_items (name)
VALUES ('Проверка подключения');

Проверьте:

SELECT * FROM test_items;

Почему владелец базы может создавать таблицы

Владелец базы имеет широкие права на саму базу, но создание объектов также зависит от прав на схему.

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

public

В современных конфигурациях права на неё могут быть более строгими, чем в старых установках.

Проверка текущей схемы

SHOW search_path;

Обычно:

"$user", public

Проверьте схемы:

\dn+

Проверка владельца схемы public

SELECT
    nspname,
    pg_get_userbyid(nspowner) AS owner
FROM pg_namespace
WHERE nspname = 'public';

Назначение прав на схему public

Если app_user не может создавать таблицы:

sudo -u postgres psql -d app_db

Затем:

GRANT USAGE, CREATE
ON SCHEMA public
TO app_user;

Проверьте:

\dn+ public

Более строгий вариант с отдельной схемой

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

CREATE SCHEMA app
AUTHORIZATION app_user;

Проверка:

\dn+ app

Тогда объекты можно создавать явно:

CREATE TABLE app.test_items (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

Отдельная схема удобна, если в одной базе работают несколько приложений или групп объектов.

Настройка search_path для роли

ALTER ROLE app_user
IN DATABASE app_db
SET search_path = app, public;

Проверьте после нового подключения:

SHOW search_path;

Изменение вступает в силу для новых сеансов.

Создание базы через одну команду

Из shell:

sudo -u postgres   createdb   --owner=app_user   app_db

Создание роли через createuser

sudo -u postgres   createuser   --login   --pwprompt   app_user

Параметр:

--pwprompt

запрашивает пароль интерактивно.

Интерактивное создание роли

sudo -u postgres createuser --interactive

Для обычного приложения отвечайте:

Shall the new role be a superuser? no
Shall the new role be allowed to create databases? no
Shall the new role be allowed to create more new roles? no

Проверка существования роли до создания

SELECT 1
FROM pg_roles
WHERE rolname = 'app_user';

Если строка возвращена, роль уже существует.

Проверка существования базы

SELECT 1
FROM pg_database
WHERE datname = 'app_db';

Ошибка role already exists

Если выполнено:

CREATE ROLE app_user;

и роль уже существует, PostgreSQL вернёт ошибку.

Проверьте роль:

\du app_user

Не удаляйте существующую роль, пока не проверены её подключения и владельцы объектов.

Ошибка database already exists

Проверьте:

\l app_db

Убедитесь, что существующая база действительно относится к нужному приложению.

Ошибка permission denied for schema public

Подключитесь как postgres к нужной базе:

sudo -u postgres psql -d app_db

Назначьте:

GRANT USAGE, CREATE
ON SCHEMA public
TO app_user;

Либо создайте отдельную схему и назначьте владельца.

Ошибка password authentication failed

Проверьте:

  • имя роли;
  • пароль;
  • базу;
  • host;
  • правило pg_hba.conf;
  • метод аутентификации;
  • регистр символов в имени.

Проверьте существование роли:

sudo -u postgres   psql   -Atc   "SELECT rolname
   FROM pg_roles
   WHERE rolname = 'app_user';"

Проверка возможности входа

sudo -u postgres   psql   -Atc   "SELECT rolcanlogin
   FROM pg_roles
   WHERE rolname = 'app_user';"

Ожидается:

t

Если false:

ALTER ROLE app_user LOGIN;

Проверка срока действия пароля

SELECT
    rolname,
    rolvaliduntil
FROM pg_authid
WHERE rolname = 'app_user';

Доступ к pg_authid обычно есть только у суперпользователя.

Если rolvaliduntil равен NULL, срок действия не ограничен.

Установка срока действия пароля

ALTER ROLE app_user
VALID UNTIL '2027-12-31 23:59:59+00';

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

Запрет создания баз и ролей

Для обычного приложения:

ALTER ROLE app_user
NOCREATEDB
NOCREATEROLE
NOSUPERUSER
NOREPLICATION;

Проверка атрибутов

\du app_user

Установка лимита подключений

Пример:

ALTER ROLE app_user
CONNECTION LIMIT 20;

Проверка:

SELECT
    rolname,
    rolconnlimit
FROM pg_roles
WHERE rolname = 'app_user';

Значение:

-1

означает отсутствие индивидуального лимита.

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

Назначение комментария

COMMENT ON ROLE app_user
IS 'Роль приложения example.com';

Комментарий к базе:

COMMENT ON DATABASE app_db
IS 'Основная база приложения example.com';

Проверка:

\du+

\l+

Проверка владельца объектов

Подключитесь к базе:

sudo -u postgres psql -d app_db

Проверьте таблицы:

SELECT
    schemaname,
    tablename,
    tableowner
FROM pg_tables
WHERE schemaname NOT IN (
    'pg_catalog',
    'information_schema'
)
ORDER BY schemaname, tablename;

Объекты приложения должны принадлежать ожидаемой роли.

Изменение владельца базы

ALTER DATABASE app_db
OWNER TO app_user;

Изменение владельца схемы

ALTER SCHEMA app
OWNER TO app_user;

Изменение владельца таблицы

ALTER TABLE app.test_items
OWNER TO app_user;

Если объекты были созданы от имени postgres, приложение может столкнуться с ошибками прав.

REASSIGN OWNED

Для передачи всех объектов одной роли другой внутри текущей базы:

REASSIGN OWNED BY old_user TO app_user;

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

Не используйте её без предварительного списка объектов.

Проверка привилегий на базу

SELECT
    datname,
    datacl
FROM pg_database
WHERE datname = 'app_db';

Более читаемо:

\l+ app_db

CONNECT к базе

Владелец базы имеет право подключения.

Для дополнительной роли можно назначить:

GRANT CONNECT
ON DATABASE app_db
TO app_user;

TEMPORARY

Если приложению нужны временные таблицы:

GRANT TEMPORARY
ON DATABASE app_db
TO app_user;

Владелец базы обычно уже имеет необходимые права.

Отзыв доступа PUBLIC

По умолчанию некоторые права могут быть доступны псевдороли PUBLIC.

Для закрытой базы можно выполнить:

REVOKE CONNECT
ON DATABASE app_db
FROM PUBLIC;

Затем явно разрешить:

GRANT CONNECT
ON DATABASE app_db
TO app_user;

Перед отзывом убедитесь, что другие нужные роли не потеряют доступ.

Проверка подключения после REVOKE

psql   -h 127.0.0.1   -U app_user   -d app_db   -c   'SELECT current_database(), current_user;'

Хранение строки подключения

Пример формата:

postgresql://app_user:STRONG_PASSWORD@127.0.0.1:5432/app_db

Не размещайте строку с реальным паролем:

  • в Git;
  • в Dockerfile;
  • в открытом .env.example;
  • в логах;
  • в shell history;
  • в документации.

Специальные символы в URI

Если пароль содержит:

@
:
/
#
?

его нужно URL-encode для connection URI.

Безопаснее передавать параметры отдельно или использовать secret-файл.

Проверка через переменную окружения

export PGPASSWORD='STRONG_PASSWORD'

psql   -h 127.0.0.1   -U app_user   -d app_db   -c   'SELECT current_user;'

Удалите переменную:

unset PGPASSWORD

PGPASSWORD может быть видна другим процессам или попасть в окружение. Для постоянного использования лучше применять .pgpass с правильными правами.

Использование .pgpass

Создайте:

nano ~/.pgpass

Формат:

127.0.0.1:5432:app_db:app_user:STRONG_PASSWORD

Назначьте права:

chmod 600 ~/.pgpass

Проверка:

psql   -h 127.0.0.1   -U app_user   -d app_db   -c   'SELECT current_user;'

Не используйте .pgpass для секретов production без контроля доступа и резервного копирования.

Удаление тестовой таблицы

Подключитесь как app_user:

psql   -h 127.0.0.1   -U app_user   -d app_db

Удалите:

DROP TABLE IF EXISTS test_items;

Если использовалась схема app:

DROP TABLE IF EXISTS app.test_items;

Безопасное удаление базы

Сначала завершите подключения.

Подключитесь как postgres к другой базе:

sudo -u postgres psql -d postgres

Проверьте активные сеансы:

SELECT
    pid,
    usename,
    application_name,
    client_addr
FROM pg_stat_activity
WHERE datname = 'app_db';

Завершите только тестовые подключения:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'app_db'
  AND pid <> pg_backend_pid();

Удалите базу:

DROP DATABASE app_db;

Удаление роли

После удаления базы проверьте объекты роли:

\du app_user

Удалите:

DROP ROLE app_user;

Если роль владеет объектами, PostgreSQL не позволит удалить её.

Ошибка role cannot be dropped

Проверьте зависимости:

\du app_user

В каждой базе могут существовать объекты или привилегии роли.

Используйте:

REASSIGN OWNED BY app_user TO postgres;
DROP OWNED BY app_user;

только после полного анализа последствий.

Нельзя удалять роль приложения до базы

Если база принадлежит app_user, сначала:

  • удалить базу;
  • либо передать владельца;
  • либо передать объекты.

Иначе DROP ROLE завершится ошибкой зависимостей.

Безопасный порядок создания

  1. Подключиться как postgres.
  2. Создать роль с LOGIN.
  3. Задать пароль через \password.
  4. Проверить атрибуты роли.
  5. Создать базу с владельцем app_user.
  6. Проверить владельца базы.
  7. Проверить схему.
  8. Подключиться от имени приложения.
  9. Создать тестовую таблицу.
  10. Выполнить тестовую запись.
  11. Удалить тестовый объект.
  12. Сохранить секрет в защищённом месте.

Быстрый вариант через SQL

CREATE ROLE app_user
WITH LOGIN;

\password app_user

CREATE DATABASE app_db
OWNER app_user;

Быстрый вариант через shell

sudo -u postgres   createuser   --login   --pwprompt   app_user

sudo -u postgres   createdb   --owner=app_user   app_db

Проверка:

psql   -h 127.0.0.1   -U app_user   -d app_db   -c   'SELECT current_database(), current_user;'

Итог

После выполнения инструкции:

  • создана отдельная роль приложения;
  • задан пароль без вывода в командной строке;
  • создана отдельная база данных;
  • роль назначена владельцем;
  • проверены атрибуты и привилегии;
  • выполнено тестовое подключение;
  • рассмотрена отдельная схема;
  • показано безопасное удаление тестовых объектов.

Следующим этапом можно изучить основные команды и режимы работы клиента psql.

← Предыдущая статья Установка PostgreSQL в Ubuntu Следующая статья → Основы работы с psql