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

sudo -u postgres psql -c "SHOW config_file;"
# Обычные пути:
# Ubuntu/Debian: /etc/postgresql/{version}/main/postgresql.conf
# CentOS/RHEL: /var/lib/pgsql/{version}/data/postgresql.conf

# Слушать все интерфейсы (для удаленного подключения)
listen_addresses = '*'  # или конкретные IP через запятую

# Порт по умолчанию
port = 5432

# Максимальное количество соединений
max_connections = 100

# Разрешить SSL соединения
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

-- Из psql выполни:
SHOW listen_addresses;
SHOW port;
SHOW max_connections;

-- Или посмотреть все параметры:
SELECT name, setting, unit FROM pg_settings 
WHERE name IN ('listen_addresses', 'port', 'max_connections');

# Разрешаем слушать все сетевые интерфейсы
listen_addresses = '*'  # вместо 'localhost'

# Проверяем порт
port = 5432

# Разрешить всем с любого IP (НЕ БЕЗОПАСНО для продакшена!)
host    all             all             0.0.0.0/0               md5

# Разрешить конкретной сети
host    all             all             192.168.1.0/24          md5

# Разрешить конкретному пользователю из конкретной сети
host    mydb            myuser          10.0.0.0/8              scram-sha-256

# SSL соединения
hostssl all             all             0.0.0.0/0               md5

# Локальные подключения (Unix socket)
local   all             all                                     peer
# или для паролей:
local   all             all                                     md5

# Для ufw (Ubuntu/Debian)
sudo ufw allow 5432/tcp
sudo ufw reload

# Для firewalld (CentOS/RHEL)
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

# Для iptables
sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
# Сохраняем правила в зависимости от дистрибутива

# Таймауты и лимиты
connectTimeout=30
socketTimeout=300
loginTimeout=20

# SSL настройки
ssl=true
sslmode=verify-full  # или require для самоподписанных сертификатов

# Кодировка
charset=UTF8

# 1. Проверяем, слушает ли порт
sudo netstat -tlnp | grep 5432
# Должно быть: tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN

# 2. Проверяем статус службы
sudo systemctl status postgresql

# 3. Проверяем фаервол
sudo ufw status  # или firewall-cmd --list-all

# Смотрим текущие правила
sudo cat /path/to/pg_hba.conf | grep -v "^#" | grep -v "^$"

# Добавляем правило для своей сети
# Например, для сети 192.168.1.0/24:
echo "host all all 192.168.1.0/24 md5" | sudo tee -a /path/to/pg_hba.conf

-- Создаем пользователя с паролем
CREATE USER myuser WITH PASSWORD 'strong_password';

-- Даем права на базу
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- Или меняем пароль существующему пользователю
ALTER USER postgres WITH PASSWORD 'new_password';

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

# Генерация самоподписанного сертификата
sudo openssl req -new -x509 -days 365 -nodes -text \
  -out /var/lib/pgsql/data/server.crt \
  -keyout /var/lib/pgsql/data/server.key \
  -subj "/CN=your-server-name"

# Устанавливаем правильные права
sudo chmod 600 /var/lib/pgsql/data/server.key
sudo chown postgres:postgres /var/lib/pgsql/data/server.*

-- Просмотр активных подключений
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    client_port,
    backend_start,
    state,
    query
FROM pg_stat_activity 
WHERE state = 'active';

-- Количество подключений по пользователям
SELECT usename, count(*) 
FROM pg_stat_activity 
GROUP BY usename;

-- Завершение проблемного подключения
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE pid = 12345;  -- замени на нужный PID