Cómo habilitar SSL para conexiones PostgreSQL

Por defecto, todas las conexiones de PostgreSQL son inseguras, lo que puede causar problemas de seguridad cuando se ejecutan en entornos de producción con alto tráfico. La encriptación SSL asegura que cualquier dato transferido no sea interceptado por nadie en medio de una conexión.

Este tutorial te enseñará cómo habilitar SSL/TLS para conexiones PostgreSQL.

Prerrequisitos

• Un servidor Linux. Para nuestro tutorial, estamos ejecutando un servidor Ubuntu 22.04.
• Un usuario sudo no root.
• Un nombre de dominio completamente calificado (FQDN) como postgresql.example.com.
• Asegúrate de que todo esté actualizado. $ sudo apt update $ sudo apt upgrade
• Algunos paquetes que tu sistema necesita. $ sudo apt install curl nano software-properties-common apt-transport-https ca-certificates lsb-release ubuntu-keyring -y Algunos de estos paquetes pueden ya estar instalados en tu sistema.

Paso 1 - Configurar el Firewall

Antes de instalar cualquier paquete, el primer paso es configurar el firewall para abrir puertos para HTTP, HTTPS y PostgreSQL.

Verifica el estado del firewall.

$ sudo ufw status

Deberías ver algo como lo siguiente.

Estado: activo

Para                         Acción      Desde
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)

Abre los puertos HTTP, HTTPS y PostgreSQL en el firewall.

$ sudo ufw allow 5432
$ sudo ufw allow http
$ sudo ufw allow https

Verifica el estado nuevamente para confirmar.

$ sudo ufw status
Estado: activo

Para                         Acción      Desde
--                         ------      ----
OpenSSH                    ALLOW       Anywhere                  
80/tcp                     ALLOW       Anywhere                  
443                        ALLOW       Anywhere                  
5432                       ALLOW       Anywhere                  
OpenSSH (v6)               ALLOW       Anywhere (v6)             
80/tcp (v6)                ALLOW       Anywhere (v6)             
443 (v6)                   ALLOW       Anywhere (v6)
5432 (v6)                  ALLOW       Anywhere (v6)             

Paso 2 - Instalar PostgreSQL 14

Ubuntu 22.04 viene con PostgreSQL 14 por defecto. Para instalar, emite el siguiente comando.

$ sudo apt install postgresql postgresql-contrib

El paquete postgresql-contrib contiene algunas utilidades adicionales.

También puedes usar el repositorio APT oficial de PostgreSQL para instalar. Ejecuta el siguiente comando para agregar la clave GPG de PostgreSQL.

$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql-key.gpg >/dev/null

Agrega el repositorio APT a tu lista de fuentes.

$ sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/postgresql-key.gpg arch=amd64] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Actualiza el repositorio del sistema.

$ sudo apt update

Ahora, puedes instalar PostgreSQL usando el comando mencionado anteriormente.

Verifica el estado del servicio PostgreSQL.

$ sudo systemctl status postgresql
? postgresql.service - PostgreSQL RDBMS
     Cargado: cargado (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Activo: activo (salió) desde Lun 2022-12-12 00:01:06 UTC; hace 19s
   Main PID: 3497 (código=salió, estado=0/SUCCESS)
        CPU: 1ms

Dec 12 00:01:06 postgresql systemd[1]: Iniciando PostgreSQL RDBMS...
Dec 12 00:01:06 postgresql systemd[1]: Finalizado PostgreSQL RDBMS.

Puedes ver que el servicio está habilitado y en ejecución por defecto.

Establece la contraseña para la cuenta postgres.

$ sudo -i -u postgres psql -c "ALTER USER postgres PASSWORD '';"

Paso 3 - Instalar SSL

Para instalar un certificado SSL usando Let’s Encrypt, necesitamos descargar la herramienta Certbot. Usaremos el instalador de paquetes Snapd para eso. Ubuntu 22.04 viene preinstalado con Snap.

Asegúrate de que tu versión de Snapd esté actualizada.

$ sudo snap install core 
$ sudo snap refresh core

Instala Certbot.

$ sudo snap install --classic certbot

Usa el siguiente comando para asegurarte de que el comando Certbot se ejecute creando un enlace simbólico al directorio /usr/bin.

$ sudo ln -s /snap/bin/certbot /usr/bin/certbot

Genera un certificado SSL.

$ sudo certbot certonly --standalone --agree-tos --no-eff-email --staple-ocsp --preferred-challenges http -m [email protected] -d postgresql.example.com

El comando anterior descargará un certificado en el directorio /etc/letsencrypt/live/postgresql.example.com en tu servidor.

Genera un certificado de grupo Diffie-Hellman.

$ sudo openssl dhparam -dsaparam -out /etc/ssl/certs/dhparam.pem 4096

Paso 4 - Configurar la renovación de Certbot para PostgreSQL

PostgreSQL no tiene permiso para acceder a los certificados de la carpeta de Let’s Encrypt, por lo tanto, no podemos decirle que use los certificados de la carpeta directamente. La alternativa es copiar los certificados al directorio de PostgreSQL, pero eso solo funciona temporalmente ya que se vencerán y tendrás que copiarlos nuevamente manualmente.

El mejor método es usar un gancho de renovación que se ejecutará automáticamente en cada renovación y realizará las operaciones de copia.

Busca el directorio de datos de PostgreSQL.

$ sudo -i -u postgres psql -U postgres -c 'SHOW data_directory'

Crea el archivo de gancho de renovación y ábrelo para editar.

$ sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh

Pega el siguiente código en él.

#!/bin/bash
umask 0177
DOMAIN=postgresql.example.com
DATA_DIR=/var/lib/postgresql/15/main
cp /etc/letsencrypt/live/$DOMAIN/fullchain.pem $DATA_DIR/server.crt
cp /etc/letsencrypt/live/$DOMAIN/privkey.pem $DATA_DIR/server.key
chown postgres:postgres $DATA_DIR/server.crt $DATA_DIR/server.key
# solo para SELinux - CentOS, Red Hat
# chcon -t postgresql_db_t $DATA_DIR/server.crt $DATA_DIR/server.key

Guarda el archivo presionando Ctrl + X y entrando Y cuando se te pida.

Haz que el archivo sea ejecutable.

$ sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh

Paso 5 - Configurar PostgreSQL

Encuentra la ruta del archivo de configuración de PostgreSQL.

$ sudo -i -u postgres psql -U postgres -c 'SHOW config_file'

Abre el archivo para editar.

$ sudo nano /etc/postgresql/15/main/postgresql.conf

Localiza la sección de Configuración de Conexión y descomenta la variable listen_address y cambia su valor a *. Asegúrate de que se vea como sigue.

listen_address = '*'        # qué dirección(es) IP escuchar;

Localiza la sección de SSL y edita el archivo para que coincida con los siguientes valores.

ssl = on  
ssl_cert_file = 'server.crt'  
ssl_key_file = 'server.key'  
ssl_prefer_server_ciphers = on
ssl_dh_params_file = '/etc/ssl/certs/dhparam.pem'

Guarda el archivo presionando Ctrl + X y entrando Y cuando se te pida.

Paso 6 - Configurar la Conexión PostgreSQL

Abre el archivo /etc/postgresql/15/main/pg_hba.conf para editar.

$ sudo nano /etc/postgresql/15/main/pg_hba.conf

Agrega la siguiente línea para habilitar SSL para PostgreSQL.

hostssl all  all  0.0.0.0/0  scram-sha-256

Guarda el archivo presionando Ctrl + X y entrando Y cuando se te pida.

Paso 7 - Renovar Certificado

Ejecuta el siguiente comando para realizar una renovación forzada. Esto activará el script de implementación que copia los certificados a la ubicación correcta para que PostgreSQL los use.

$ sudo certbot renew --force-renewal

Verifica que los certificados se hayan copiado al directorio de datos de PostgreSQL.

$ sudo ls /var/lib/postgresql/15/main/

Verás la siguiente salida que te mostrará los certificados.

base          pg_dynshmem   pg_notify    pg_snapshots  pg_subtrans  PG_VERSION  postgresql.auto.conf  server.crt
global        pg_logical    pg_replslot  pg_stat       pg_tblspc    pg_wal      postmaster.opts       server.key
pg_commit_ts  pg_multixact  pg_serial    pg_stat_tmp   pg_twophase  pg_xact     postmaster.pid

Reinicia PostgreSQL para aplicar los cambios.

$ sudo systemctl restart postgresql

Paso 8 - Probar la Conexión

Conéctate a la base de datos desde otra máquina con el cliente PostgreSQL instalado.

$ psql -d "dbname=postgres sslmode=require" -h postgresql.example.com -U postgres

Deberías ver el siguiente aviso de PostgreSQL. Estamos usando un cliente con PostgreSQL 14, por lo tanto, verás una advertencia sobre versiones incompatibles.

Contraseña para el usuario postgres:
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1), servidor 15.1 (Ubuntu 15.1-1.pgdg22.04+1))
ADVERTENCIA: versión principal de psql 14, versión principal del servidor 15.
         Algunas funciones de psql pueden no funcionar.
Conexión SSL (protocolo: TLSv1.3, cifrado: TLS_AES_256_GCM_SHA384, bits: 256, compresión: apagada)
Escribe "help" para ayuda.

postgres=#

Esto confirma una conexión SSL exitosa.

Sal de la consola.

postgres=# \q

Si tu aplicación está usando una cadena de conexión, úsala en el siguiente formato para una conexión SSL.

postgres://user:[email protected]:5432/database_name?sslmode=require

Puedes cambiar el modo SSL a verify-full o verify-ca si tienes el certificado raíz de Let’s Encrypt disponible en la ubicación /var/lib/postgresql/.postgresql en el lado del cliente.

Crea el directorio /var/lib/postgresql/.postgresql.

$ sudo mkdir -p /var/lib/postgresql/.postgresql

El certificado raíz de Let’s Encrypt es ISRG Root X1, que se encuentra en el servidor en la ubicación /usr/share/ca-certificates/mozilla/ISRG_Root_X1.crt.

Copia el certificado raíz al directorio /var/lib/postgresql/.postgresql.

$ sudo cp /usr/share/ca-certificates/mozilla/ISRG_Root_X1.crt /var/lib/postgresql/.postgresql/root.crt

Prueba la conexión usando el modo verify-full o verify-ca y deberías ver una conexión exitosa.

Paso 9 - Verificar los Clientes

Inicia sesión en la consola de PostgreSQL en el servidor.

$ sudo -i -u postgres psql

Ejecuta el siguiente comando SQL para verificar los clientes conectados.

SELECT ssl.pid, usename, datname, ssl, ssl.version, ssl.cipher, ssl.bits, client_addr
FROM pg_catalog.pg_stat_ssl ssl, pg_catalog.pg_stat_activity activity
WHERE ssl.pid = activity.pid;

Deberías ver una salida similar.

 pid  | usename  | datname  | ssl | version |         cipher         | bits |  client_addr
------+----------+----------+-----+---------+------------------------+------+----------------
 5126 | postgres | postgres | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | 122.161.84.220
 5154 | postgres | postgres | f   |         |                        |      |
(2 filas)

Esto confirma la conexión del cliente desde el lado del servidor.

Conclusión

Esto concluye el tutorial sobre cómo habilitar SSL en las conexiones de PostgreSQL. Si tienes alguna pregunta, publícalas en los comentarios a continuación.