PostgreSQL endurecido: roles, TLS, pg_hba y backups con PITR
PostgreSQL suele llegar a producción con la configuración por defecto: escucha solo en local, un superusuario llamado postgres y autenticación peer o trust para conexiones locales. En cuanto abres el puerto 5432 a la red — porque hay una app en otro host o una réplica — esa comodidad se convierte en riesgo. Endurecer PostgreSQL no es un solo ajuste: son cuatro capas que hay que aplicar en orden.
1. Roles con el principio de mínimo privilegio
El primer error es que la aplicación conecte como superusuario. Crea un rol dedicado con solo los permisos que necesita:
CREATE ROLE app_login NOLOGIN;
CREATE ROLE app_rw LOGIN PASSWORD 'cambia-esto-por-una-contraseña-larga';
GRANT app_login TO app_rw;
GRANT CONNECT ON DATABASE miapp TO app_rw;
GRANT USAGE ON SCHEMA public TO app_rw;
Y para que las tablas que se creen en el futuro hereden los permisos sin tener que acordarte cada vez:
ALTER DEFAULT PRIVILEGES FOR ROLE deployer IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
Además, desactiva el login del superusuario para conexiones de red y limita su uso al socket local:
ALTER ROLE postgres NOLOGIN;
Si necesitas administrar, usa un rol aparte con CREATEDB/CREATEROLE pero sin SUPERUSER, o entra por sudo -u postgres.
2. pg_hba.conf: quién entra, desde dónde y cómo
El fichero pg_hba.conf se evalúa de arriba a abajo; la primera línea que coincide decide. Pon siempre las reglas más específicas primero y termina con un rechazo explícito:
# conexiones locales: solo socket unix, con contraseña
local all all scram-sha-256
# app desde la VLAN interna, con TLS obligatorio
hostssl miapp app_rw 10.20.30.0/24 scram-sha-256
# administración solo desde la IP del bastion
hostssl all dba 10.20.30.99/32 scram-sha-256
# todo lo demás: denegado
host all all 0.0.0.0/0 reject
Dos detalles que importan: usa scram-sha-256 en lugar de md5 (fuerza password_encryption = 'scram-sha-256' en postgresql.conf y rota las contraseñas existentes para que se rehasheen) y prefiere hostssl sobre host para que las conexiones cifradas sean obligatorias, no opcionales. Recarga sin reiniciar con SELECT pg_reload_conf();.
3. TLS en las conexiones
Sin TLS, credenciales y datos viajan en claro por la red interna, y “red interna” incluye switches compartidos y capturas accidentales. Genera un certificado (interno o de tu CA) y actívalo:
openssl req -x509 -newkey rsa:4096 -days 825 -nodes \
-keyout /etc/postgresql/16/main/server.key \
-out /etc/postgresql/16/main/server.crt \
-subj "/CN=db01.midominio.lan"
chown postgres:postgres /etc/postgresql/16/main/server.key
chmod 600 /etc/postgresql/16/main/server.key
# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/16/main/server.crt'
ssl_key_file = '/etc/postgresql/16/main/server.key'
ssl_min_protocol_version = 'TLSv1.2'
Del lado cliente, exige verificación real del certificado en la cadena de conexión: sslmode=verify-full. El modo require cifra pero no valida contra quién hablas; solo verify-full cierra la puerta al man-in-the-middle. Compruébalo así:
psql "host=db01.midominio.lan dbname=miapp user=app_rw sslmode=verify-full" -c "SHOW ssl;"
Si devuelve on, la conexión va cifrada y autenticada.
4. Backups que permiten volver atrás en el tiempo
pg_dump sirve para copias lógicas, pero si borras una tabla a las 14:37 y lo descubres a las 15:00, un dump de medianoche no te devuelve los datos de las 14:36. Para eso existe PITR (point-in-time recovery): copia base física + archivo continuo de WAL.
Activa el archivado de WAL en postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal/%f && cp %p /var/lib/postgresql/wal/%f'
max_wal_senders = 3
Toma una copia base (sin bloquear la base de datos):
pg_basebackup -D /var/backups/pg/base-$(date +%F) -Ft -z -P -U replicador
Para restaurar: detén el clúster, extrae la copia base en el directorio de datos, crea recovery.signal (PostgreSQL 12+) con un postgresql.auto.conf que incluya restore_command = 'cp /var/lib/postgresql/wal/%f %p', y arranca. El servidor reproducirá los WAL archivados hasta el momento que indiques con recovery_target_time. Prueba la restauración en un host aparte al menos una vez al trimestre: un backup que nunca se ha restaurado es una promesa, no un backup.

Checklist final
| Capa | Acción | Verificación |
|---|---|---|
| Roles | La app no es superusuario; privilegios por defecto | \du y \dp en psql |
| pg_hba | scram-sha-256, hostssl, reject final |
SHOW hba_file; + prueba de conexión denegada |
| TLS | Certificado válido, TLS 1.2+ | SHOW ssl; → on |
| Backups | WAL archivado + copia base periódica | Restauración de prueba trimestral |
Ninguna de estas capas sustituye a las demás: los roles no sirven si el tráfico va en claro, el TLS no sirve si cualquiera puede conectarse, y todo es papel mojado sin una restauración probada. Aplícalas en orden y tu PostgreSQL dejará de ser el eslabón débil de la infraestructura.