PostgreSQL 17 & PgBouncer: Connection Pooling Masivo & Hardening

En arquitecturas de alta concurrencia basadas en Laravel Octane (FrankenPHP / Swoole) o clústeres masivos de workers asíncronos en Laravel Horizon, la creación desmedida de conexiones concurrentes puede colapsar la memoria de PostgreSQL (donde cada proceso de backend consume entre 10MB y 20MB de RAM).

Para resolver este cuello de botella, Mañuti implementa PgBouncer como un middleware de pooling transaccional de ultra bajo consumo, complementado con autenticación SCRAM-SHA-256 y blindaje perimetral mediante UFW para forzar que todo el tráfico pase obligatoriamente por el pooler.

flowchart TD
    APP["Mañuti App Tier / Octane Worker Fleet<br/>(10,000 conexiones concurrentes en ráfaga)"]
    
    UFW["UFW Firewall Filtering Layer<br/>- Puerto 6432: PERMITIDO para App Tier<br/>- Puerto 5432: DENEGADO (Solo 127.0.0.1)"]
    
    subgraph BOUNCER ["PgBouncer Middleware"]
        POOL["pool_mode =<br/>transaction"]
        AUTH["userlist.txt<br/>(Auth SCRAM-SHA-256)"]
        
        CONN["max_client_conn = 10,000"]
        DEF_POOL["default_pool_size = 20"]
        
        %% Enlace invisible para alinear la fila superior
        POOL ~~~ AUTH
        
        %% Relación lógica interna
        CONN --> DEF_POOL
    end
    
    PG[("PostgreSQL 17 Engine<br/>(Persistencia Transaccional con MVCC)")]

    APP -->|"TCP Port 6432 (Subred 192.168.1.0/24)"| UFW
    UFW --> BOUNCER
    BOUNCER -->|"Multiplexación Loopback<br/>127.0.0.1:5432"| PG

1. Modos de Pooling en PgBouncer: ¿Por qué Transaction Mode?

PgBouncer ofrece tres estrategias de gestión de conexiones. Para el ecosistema Mañuti, se implementa Transaction Pooling:

2. Aprovisionamiento de PostgreSQL 17 (Ubuntu 24.04 LTS)

2.1 Repositorio PGDG Oficial e Instalación

# 1. Dependencias y Repositorio PGDG
sudo apt update && sudo apt upgrade -y
sudo apt install -y curl ca-certificates software-properties-common

sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# 2. Instalación de PostgreSQL 17
sudo apt update
sudo apt install -y postgresql-17

# 3. Validar estado del clúster
pg_lsclusters

2.2 Configuración de Escucha y Autenticación SCRAM-SHA-256

Configurar PostgreSQL para escuchar en la interfaz privada (192.168.1.174) y localhost:

sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '192.168.1.174,localhost'/" /etc/postgresql/17/main/postgresql.conf

Establecer autenticación estricta con SCRAM-SHA-256 en /etc/postgresql/17/main/pg_hba.conf:

echo "host    all             all             127.0.0.1/32            scram-sha-256" | sudo tee -a /etc/postgresql/17/main/pg_hba.conf
echo "host    all             all             192.168.1.0/24          scram-sha-256" | sudo tee -a /etc/postgresql/17/main/pg_hba.conf
sudo systemctl restart postgresql

2.3 Creación de Credenciales de Base de Datos

Definir contraseñas seguras para el superusuario y el usuario de la aplicación:

# Definir contraseña de postgres
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'MasterSuperAdminPass_2025!';"

# Crear base de datos y usuario de Mañuti
sudo -u postgres psql <<EOF
CREATE DATABASE manuti_core;
CREATE USER manuti_app WITH PASSWORD 'AppClientSecurePass_2025!';
GRANT ALL PRIVILEGES ON DATABASE manuti_core TO manuti_app;
ALTER DATABASE manuti_core OWNER TO manuti_app;
EOF

3. Instalación y Configuración de PgBouncer

3.1 Instalación de Binarios

sudo apt install -y pgbouncer

3.2 Generación del Archivo de Autenticación (userlist.txt)

PgBouncer intercepta las credenciales y las valida localmente antes de tocar a PostgreSQL. Extraemos los hashes SCRAM directamente del catálogo pg_shadow:

sudo -u postgres psql -Atq -c \
  "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow WHERE usename IN ('postgres', 'manuti_app')" \
  | sudo tee /etc/pgbouncer/userlist.txt

# Asignar permisos estrictos
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt

3.3 Configuración Maestra (/etc/pgbouncer/pgbouncer.ini)

Respaldar el archivo por defecto y aplicar la configuración sintonizada:

sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.bak
sudo truncate -s 0 /etc/pgbouncer/pgbouncer.ini

cat <<EOF | sudo tee /etc/pgbouncer/pgbouncer.ini
[databases]
# Mapeo comodín hacia la instancia local
* = host=127.0.0.1 port=5432 auth_user=postgres

[pgbouncer]
# Escucha en red privada y loopback
listen_addr = 192.168.1.174, 127.0.0.1
listen_port = 6432

# Seguridad y Autenticación
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = postgres, manuti_app

# Sintonización de Pooling Masivo
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 100

# Archivos de Registro y Control
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
EOF

3.4 Permisos y Puesta en Marcha

sudo adduser --system --group --home /etc/pgbouncer --no-create-home pgbouncer 2>/dev/null || true
sudo chown -R pgbouncer:postgres /etc/pgbouncer
sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer

# Verificar que PgBouncer escucha en el puerto 6432
sudo ss -tunlp | grep 6432

4. Hardening Perimetral y Firewall (UFW)

Para evitar que clientes o atacantes eludan el Connection Pooler, el puerto nativo de PostgreSQL (5432) se restringe estrictamente a 127.0.0.1. El tráfico externo solo puede ingresar por el puerto de PgBouncer (6432):

sudo ufw --force reset
sudo ufw default deny incoming
sudo ufw default allow outgoing

# Acceso SSH para administración
sudo ufw allow 22/tcp

# PgBouncer: Permitido únicamente para la subred de aplicaciones
sudo ufw allow from 192.168.1.0/24 to any port 6432 proto tcp comment 'PgBouncer Traffic'

# PostgreSQL: Blindado exclusivamente a tráfico local de este servidor
sudo ufw allow from 127.0.0.1 to any port 5432 proto tcp comment 'Postgres Local Loopback Only'

sudo ufw enable
sudo ufw reload

Verificación esperada con sudo ufw status numbered:

     To                         Action      From
     --                         ------      ----
[ 1] 22/tcp                     ALLOW IN    Anywhere
[ 2] 6432/tcp                   ALLOW IN    192.168.1.0/24             # PgBouncer Traffic
[ 3] 5432/tcp                   ALLOW IN    127.0.0.1                  # Postgres Local Loopback Only

5. Pruebas Operativas y Validación de Aislamiento

5.1 Prueba de Acceso Autorizado (vía PgBouncer)

Desde cualquier equipo en la subred 192.168.1.0/24:

psql -h 192.168.1.174 -p 6432 -U manuti_app -d manuti_core -c \
  "SELECT current_user, inet_server_addr(), inet_client_addr(), pg_backend_pid();"

Al ejecutarlo repetidas veces, la consulta responderá con éxito inmediato, demostrando que PgBouncer despacha la transacción y recicla el socket.

5.2 Prueba de Aislamiento Negativo (Intento de Salto a Puerto 5432)

Desde un equipo cliente externo:

nc -zv 192.168.1.174 5432

Resultado esperado: Connection timed out o Connection refused. El firewall descarta el paquete y previene el bypass del middleware.

6. Monitoreo y Consola Administrativa Interna

PgBouncer expone una base de datos virtual llamada pgbouncer para inspección en caliente del estado de las conexiones.

Conexión a la consola administrativa:

psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer

Comandos de Inspección Críticos:

-- 1. Ver clientes conectados y su estado de espera
SHOW CLIENTS;

-- 2. Inspeccionar la saturación de los pools de conexiones activas
SHOW POOLS;

-- 3. Ver métricas de uso de memoria y descriptores de socket
SHOW MEM;

-- 4. Recargar configuración de pgbouncer.ini sin desconectar clientes
RELOAD;

-- 5. Pausar conexiones entrantes de forma segura para tareas de mantenimiento
PAUSE;
RESUME;

7. Integración en el Aplicativo Mañuti (config/database.php y .env)

7.1 Consideración Crítica para Transaction Pooling: Prepared Statements

Al operar en pool_mode = transaction, múltiples clientes comparten los mismos procesos de backend en Postgres de forma intercalada. Si un driver de PHP envía sentencias preparadas nativas del lado del servidor (PREPARE stmt), otro cliente concurrente podría chocar con un error prepared statement already exists.

Para resolver esto en Laravel 12/13 y Octane, se debe forzar la emulación de prepared statements del lado del cliente PDO:

// config/database.php
'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '192.168.1.174'),
    'port' => env('DB_PORT', '6432'), // Puerto de PgBouncer
    'database' => env('DB_DATABASE', 'manuti_core'),
    'username' => env('DB_USERNAME', 'manuti_app'),
    'password' => env('DB_PASSWORD', 'AppClientSecurePass_2025!'),
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
    'sslmode' => 'prefer',
    'options' => [
        // CRUCIAL: Emular prepared statements para Transaction Pooling
        PDO::ATTR_EMULATE_PREPARES => true,
        PDO::ATTR_PERSISTENT => false,
        PDO::ATTR_TIMEOUT => 3,
    ],
],

7.2 Archivo de Entorno (.env)

DB_CONNECTION=pgsql
DB_HOST=192.168.1.174
DB_PORT=6432
DB_DATABASE=manuti_core
DB_USERNAME=manuti_app
DB_PASSWORD=AppClientSecurePass_2025!