Установка PostgreSQL: создание базы, пользователя и настройка доступа

curl -sSL https://cp.lv/scripts/ustanovit-postgresql.sh | sudo bash

wget -qO- https://cp.lv/scripts/ustanovit-postgresql.sh | sudo bash

#!/usr/bin/env bash
# Установка PostgreSQL, создание базы данных и пользователя
set -e
export DEBIAN_FRONTEND=noninteractive

DB_NAME=app_db
DB_USER=app_user
DB_PASS='СложныйПароль123!'

# 1. Установка PostgreSQL и расширений
sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

# 2. Создание роли (пользователя) и базы данных
sudo -u postgres psql << EOF
CREATE ROLE ${DB_USER} WITH LOGIN PASSWORD '${DB_PASS}';
CREATE DATABASE ${DB_NAME} OWNER ${DB_USER};
GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME} TO ${DB_USER};
EOF

# 3. Права на схему public (обязательно с PostgreSQL 15+)
sudo -u postgres psql -d ${DB_NAME} -c "GRANT ALL ON SCHEMA public TO ${DB_USER};"

echo "Готово: база ${DB_NAME} и пользователь ${DB_USER} созданы."

PostgreSQL — мощная реляционная СУБД с поддержкой JSON, полнотекстового поиска и строгой целостности данных. Ниже — установка на Ubuntu 22.04/24.04, создание базы и пользователя, настройка доступа.

Шаг 1. Установка

PostgreSQL есть в стандартном репозитории:

sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo systemctl enable --now postgresql

Проверьте версию и статус:

psql --version
sudo systemctl status postgresql

Пакет postgresql-contrib добавляет полезные расширения (например, pg_stat_statements, uuid-ossp).

Шаг 2. Роль и база данных

PostgreSQL создаёт системного пользователя postgres. Войдите под ним в консоль:

sudo -u postgres psql

Создайте пользователя (роль) и базу под приложение:

CREATE ROLE app_user WITH LOGIN PASSWORD 'СложныйПароль123!';
CREATE DATABASE app_db OWNER app_user;
GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;
\q

В PostgreSQL 15+ права на схему public по умолчанию урезаны. Если приложение не может создавать таблицы, выдайте права на схему:

sudo -u postgres psql -d app_db

GRANT ALL ON SCHEMA public TO app_user;
\q

Шаг 3. Проверка входа

Подключитесь новым пользователем локально:

psql -h 127.0.0.1 -U app_user -d app_db -W

Введите пароль. Если приглашение app_db=> появилось — доступ работает.

Шаг 4. Настройка удалённого доступа

По умолчанию PostgreSQL слушает только localhost. Чтобы принять внешние подключения (например, от бэкенда на другом сервере), отредактируйте два файла. Найдите их пути:

sudo -u postgres psql -c 'SHOW config_file;'
sudo -u postgres psql -c 'SHOW hba_file;'

В postgresql.conf укажите адреса прослушивания. Откройте файл в редакторе (nano, vim или mcedit):

sudo nano "$(sudo -u postgres psql -tAc 'SHOW config_file;')"

Строка настройки:

listen_addresses = '10.0.0.5, localhost'

Либо допишите её одной командой — скопируйте и вставьте в консоль (heredoc допишет строку в конец файла):

sudo tee -a "$(sudo -u postgres psql -tAc 'SHOW config_file;')" > /dev/null << 'EOF'
listen_addresses = '10.0.0.5, localhost'
EOF

В pg_hba.conf добавьте правило для доверенной подсети с шифрованием пароля. Откройте файл в редакторе:

sudo nano "$(sudo -u postgres psql -tAc 'SHOW hba_file;')"

Содержимое правила:

# TYPE  DATABASE  USER      ADDRESS         METHOD
host    app_db    app_user  10.0.0.0/24     scram-sha-256

Или добавьте правило одной командой (кавычки 'EOF' — текст запишется как есть):

sudo tee -a "$(sudo -u postgres psql -tAc 'SHOW hba_file;')" > /dev/null << 'EOF'
host    app_db    app_user  10.0.0.0/24     scram-sha-256
EOF

Никогда не открывайте базу на 0.0.0.0/0 — только для конкретных IP или приватной сети. Примените изменения:

sudo systemctl restart postgresql

Откройте порт только для нужного источника:

sudo ufw allow from 10.0.0.10 to any port 5432

Полезные команды psql

Частые вопросы

Чем роль отличается от пользователя в PostgreSQL? Это одно и то же: пользователь — это роль с атрибутом LOGIN. CREATE USER — синоним CREATE ROLE ... WITH LOGIN.

Почему приложение пишет «permission denied for schema public»? С PostgreSQL 15 права на схему public ограничены. Выдайте их явно: GRANT ALL ON SCHEMA public TO app_user.

Как задать пароль пользователю postgres? Войдите sudo -u postgres psql и выполните ALTER USER postgres PASSWORD 'новый_пароль';.

Как проверить, слушает ли база внешний порт? sudo ss -tlnp | grep 5432 покажет адреса прослушивания. Если только 127.0.0.1 — правьте listen_addresses.

PostgreSQL чувствителен к скорости диска и объёму памяти под кэш. Под базы данных берите тарифы на NVMe: смотрите аренда VPS и профильный VPS для баз данных.