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

# TYPE  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# ------ --------- ----- -------- ------- ---------
host    all       all   0.0.0.0/0 md5

# Разрешить локальные подключения через Unix-сокет
local   all             all                                     trust

# Разрешить подключение с localhost с паролем
host    all             all             127.0.0.1/32            md5

# Разрешить доступ к БД 'appdb' пользователю 'appuser' с определенной подсети
host    appdb           appuser         192.168.1.0/24          md5

# Разрешить репликацию только для standby серверов
host    replication     replicator      192.168.1.100/32        md5

# Запретить все остальные подключения
host    all             all             0.0.0.0/0               reject

-- Создаем пользователя для приложения
CREATE ROLE app_user WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOINHERIT
    NOREPLICATION
    CONNECTION LIMIT 10
    PASSWORD 'StrongPassword123!';

-- Создаем базу данных для приложения
CREATE DATABASE app_database
    OWNER app_user
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8';

-- Даем права на подключение к базе
GRANT CONNECT ON DATABASE app_database TO app_user;

-- Подключаемся к базе данных приложения
\c app_database

-- Даем права на использование схемы public
GRANT USAGE ON SCHEMA public TO app_user;

-- Даем права на создание таблиц в схеме public
GRANT CREATE ON SCHEMA public TO app_user;

-- После создания таблиц, даем конкретные права
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Даем права на использование последовательностей
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Настраиваем права по умолчанию для будущих таблиц
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

# На каком адресе слушать подключения
# '*' - все интерфейсы, 'localhost' - только локальные
listen_addresses = 'localhost,192.168.1.100'

# Порт для подключений
port = 5432

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

# Размер буфера для SSL
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

# Таймауты соединений
authentication_timeout = 60s  # 1 минута на аутентификацию
tcp_keepalives_idle = 60       # Проверять соединение каждую минуту

# ============================================
# PRODUCTION CONFIGURATION - SECURE SETTINGS
# ============================================

# Локальные подключения через Unix socket
local   all             postgres                                peer
local   all             all                                     scram-sha-256

# Localhost connections
host    all             all             127.0.0.1/32            scram-sha-256

# Application connections from specific subnet
host    app_database    app_user        192.168.1.0/24          scram-sha-256

# Replication only from standby servers
host    replication     replicator      192.168.1.200/32        scram-sha-256
host    replication     replicator      192.168.1.201/32        scram-sha-256

# Monitoring user from monitoring server
host    postgres        monitor_user    192.168.1.50/32         scram-sha-256

# DENY EVERYTHING ELSE
host    all             all             0.0.0.0/0               reject

-- Посмотреть текущие подключения
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    client_port,
    backend_start,
    state
FROM pg_stat_activity
WHERE state IS NOT NULL;

-- Посмотреть настройки pg_hba через SQL
SELECT * FROM pg_hba_file_rules;

-- Проверить права пользователя
\du app_user

-- Посмотреть, к каким базам есть доступ
SELECT 
    datname,
    datallowconn,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
WHERE datallowconn = true;

# Перезагрузить конфигурацию без перезапуска сервиса
sudo systemctl reload postgresql

# Или изнутри PostgreSQL
SELECT pg_reload_conf();

# Проверить, что конфигурация загружена
SHOW hba_file;
SHOW config_file;

# Проверить логи PostgreSQL
sudo tail -f /var/log/postgresql/postgresql-14-main.log

# Проверить, слушает ли PostgreSQL на порту
sudo netstat -tlnp | grep 5432
sudo ss -tlnp | grep 5432

# Проверить подключение с клиента
psql -h 192.168.1.100 -U app_user -d app_database

# Проверить с подробным выводом
PGPASSWORD='password' psql \
    -h 192.168.1.100 \
    -U app_user \
    -d app_database \
    -c "SELECT current_user, current_database();"