Aprende a Cómo proteger filas por usuario con Row-Level Security en PostgreSQL

TLS cifra el tráfico, pero no confirma por sí solo la identidad del servidor. Esta guía configura PostgreSQL para rechazar conexiones TCP sin TLS y hace que el cliente valide la CA y el nombre DNS antes de enviar credenciales o consultas.

Qué vas a lograr

Al finalizar:

PostgreSQL presentará un certificado con el SAN db.ejemplo.com.
El cliente se conectará con sslmode=verify-full.
pg_hba.conf rechazará conexiones TCP sin cifrar.
La autenticación utilizará scram-sha-256.
Podrás comprobar versión y cifrado mediante pg_stat_ssl.
Tendrás un rollback que no sobrescribe certificados anteriores.

TLS, SCRAM y verify-full son controles diferentes:

Control	Función
TLS	Cifra el transporte
`scram-sha-256`	Autentica la credencial del rol
`verify-full`	Valida la CA y que el nombre coincida con el certificado

PostgreSQL denomina históricamente “SSL” a estas conexiones, aunque utiliza protocolos TLS actuales. PostgreSQL: conexiones TCP/IP seguras

Requisitos

PostgreSQL autogestionado en Ubuntu 24.04 o compatible.
Acceso mediante SSH y consola alternativa.
DNS estable, por ejemplo db.ejemplo.com.
Certificado y clave privada en formato PEM.
CA raíz o cadena de confianza para el cliente.
Base appdb y rol app_tls.
CIDR autorizado; se utilizará 198.51.100.24/32 como ejemplo.
Backup y ventana de mantenimiento.

Activar ssl=on o modificar listen_addresses requiere reiniciar PostgreSQL. Una instancia única sufrirá una interrupción breve.

Paso 1: registrar rutas y estado actual

sudo -u postgres psql -X -P pager=off \
  -c "SELECT version();" \
  -c "SHOW ssl;" \
  -c "SHOW ssl_min_protocol_version;" \
  -c "SHOW listen_addresses;" \
  -c "SHOW config_file;" \
  -c "SHOW hba_file;" \
  -c "SHOW data_directory;"

sudo pg_lsclusters
sudo ss -ltnp | grep ':5432'

CONFIG_FILE=$(sudo -u postgres psql -XAtqc 'SHOW config_file')
HBA_FILE=$(sudo -u postgres psql -XAtqc 'SHOW hba_file')
PGDATA=$(sudo -u postgres psql -XAtqc 'SHOW data_directory')

printf 'config=%s\nhba=%s\ndata=%s\n' \
  "$CONFIG_FILE" "$HBA_FILE" "$PGDATA"

Comprueba que el rol tenga una contraseña SCRAM sin mostrar el secreto:

sudo -u postgres psql -X -P pager=off -c \
  "SELECT rolname,
          rolpassword LIKE 'SCRAM-SHA-256$%' AS usa_scram
   FROM pg_authid
   WHERE rolname = 'app_tls';"

Si devuelve false, cambia la contraseña dentro de psql:

SET password_encryption = 'scram-sha-256';
\password app_tls

Paso 2: crear un backup verificable

STAMP=$(date -u +%Y%m%dT%H%M%SZ)
BACKUP_DIR="/root/postgresql-tls-$STAMP"

sudo install -d -m 0700 "$BACKUP_DIR"
sudo cp --preserve=all "$CONFIG_FILE" "$BACKUP_DIR/postgresql.conf"
sudo cp --preserve=all "$HBA_FILE" "$BACKUP_DIR/pg_hba.conf"

sudo sh -c "cd '$BACKUP_DIR' &&
  sha256sum postgresql.conf pg_hba.conf > SHA256SUMS"

Paso 3: preparar el certificado

En producción, utiliza una CA pública o corporativa. La siguiente CA es exclusivamente para un laboratorio:

umask 077

openssl genpkey -algorithm RSA \
  -pkeyopt rsa_keygen_bits:3072 \
  -out lab-ca.key

openssl req -x509 -new -sha256 -days 3650 \
  -key lab-ca.key \
  -out lab-ca.crt \
  -subj '/CN=CA laboratorio PostgreSQL' \
  -addext 'basicConstraints=critical,CA:TRUE' \
  -addext 'keyUsage=critical,keyCertSign,cRLSign' \
  -addext 'subjectKeyIdentifier=hash'

openssl genpkey -algorithm RSA \
  -pkeyopt rsa_keygen_bits:3072 \
  -out server.key

openssl req -new -sha256 \
  -key server.key \
  -out server.csr \
  -subj '/CN=db.ejemplo.com'

Crea las extensiones del certificado:

cat > server.ext <<'EOF'
basicConstraints=critical,CA:FALSE
keyUsage=critical,digitalSignature,keyEncipherment
extendedKeyUsage=serverAuth
subjectAltName=DNS:db.ejemplo.com
EOF

openssl x509 -req -sha256 -days 365 \
  -in server.csr \
  -CA lab-ca.crt \
  -CAkey lab-ca.key \
  -CAcreateserial \
  -extfile server.ext \
  -out server.crt

Valida cadena, propósito, nombre, vigencia y correspondencia con la clave:

openssl x509 -in server.crt -noout \
  -subject -issuer -dates -ext subjectAltName

openssl x509 -in server.crt \
  -checkend 2592000 -noout

openssl verify \
  -CAfile lab-ca.crt \
  -purpose sslserver \
  -verify_hostname db.ejemplo.com \
  server.crt

CERT_FP=$(openssl x509 -in server.crt -pubkey -noout |
  openssl pkey -pubin -outform DER |
  sha256sum | cut -d' ' -f1)

KEY_FP=$(openssl pkey -in server.key -pubout -outform DER |
  sha256sum | cut -d' ' -f1)

test "$CERT_FP" = "$KEY_FP"

Las opciones -purpose y -verify_hostname comprueban que el certificado pueda utilizarse como servidor TLS y que su identidad coincida. OpenSSL: verificación de certificados

Paso 4: instalar archivos versionados

No sobrescribas un certificado activo:

CERT_ID=20260831

sudo install -d \
  -o root -g postgres -m 0750 \
  /etc/postgresql/tls

sudo install \
  -o root -g root -m 0644 \
  server.crt \
  "/etc/postgresql/tls/server-$CERT_ID.crt"

sudo install \
  -o root -g postgres -m 0640 \
  server.key \
  "/etc/postgresql/tls/server-$CERT_ID.key"

sudo -u postgres test -r \
  "/etc/postgresql/tls/server-$CERT_ID.crt"

sudo -u postgres test -r \
  "/etc/postgresql/tls/server-$CERT_ID.key"

PostgreSQL acepta una clave propiedad de root con grupo de lectura y modo 0640. También puede utilizarse postgres:postgres 0600. PostgreSQL: permisos de la clave

Paso 5: configurar PostgreSQL

Modifica las claves existentes en el archivo indicado por SHOW config_file:

ssl = on
ssl_cert_file = '/etc/postgresql/tls/server-20260831.crt'
ssl_key_file = '/etc/postgresql/tls/server-20260831.key'
ssl_min_protocol_version = 'TLSv1.2'
listen_addresses = 'localhost,10.0.0.5'

Utiliza el mismo CERT_ID del paso anterior. Sustituye 10.0.0.5 por la IP privada efectiva.

No es necesario configurar ssl_ca_file para TLS unidireccional. Ese parámetro se utiliza cuando PostgreSQL debe verificar certificados de cliente.

Paso 6: exigir TLS en `pg_hba.conf`

Coloca estas reglas antes de cualquier regla host amplia:

# Rechazar TCP IPv4 e IPv6 sin TLS.
hostnossl  all    all      0.0.0.0/0          reject
hostnossl  all    all      ::0/0               reject

# Acceso TLS específico para la aplicación.
hostssl    appdb  app_tls  198.51.100.24/32   scram-sha-256

hostssl solo coincide con conexiones cifradas; hostnossl coincide con las no cifradas. PostgreSQL utiliza la primera regla coincidente. PostgreSQL: pg_hba.conf

Estas reglas limitan app_tls y appdb, pero no eliminan otros accesos TLS definidos posteriormente. Audita todas las reglas TCP si necesitas restringir el acceso remoto completo.

Si UFW es el firewall efectivo:

sudo ufw status verbose
sudo ufw status numbered |
  sudo tee "$BACKUP_DIR/ufw-before.txt"

sudo ufw allow from 198.51.100.24 \
  to any port 5432 proto tcp \
  comment "postgresql-tls-$STAMP"

printf '%s\n' '198.51.100.24 port 5432' |
  sudo tee "$BACKUP_DIR/ufw-rule-added.txt"

No añadas esta regla si ya existe.

Paso 7: validar antes de reiniciar

SELECT sourcefile, sourceline, name, setting, error
FROM pg_file_settings
WHERE error IS NOT NULL;

SELECT line_number, type, database, user_name,
       address, auth_method, error
FROM pg_hba_file_rules
ORDER BY line_number;

La primera consulta debe devolver cero filas. En la segunda, error debe estar vacío y las reglas nuevas deben aparecer antes de reglas más amplias.

Paso 8: reiniciar y comprobar

PG_VERSION=16
PG_CLUSTER=main

sudo pg_ctlcluster "$PG_VERSION" "$PG_CLUSTER" restart
sudo pg_ctlcluster "$PG_VERSION" "$PG_CLUSTER" status

sudo journalctl \
  -u "postgresql@${PG_VERSION}-${PG_CLUSTER}" \
  --since '-5 minutes' \
  --no-pager

No copies 16 main sin comprobar antes la salida de pg_lsclusters.

Paso 9: conectar con `verify-full`

Utiliza un archivo de CA dedicado para no sobrescribir ~/.postgresql/root.crt:

install -d -m 0700 ~/.postgresql

install -m 0600 \
  lab-ca.crt \
  ~/.postgresql/db-ejemplo-root.crt

psql "host=db.ejemplo.com \
hostaddr=192.0.2.10 \
port=5432 dbname=appdb user=app_tls \
sslmode=verify-full \
sslrootcert=$HOME/.postgresql/db-ejemplo-root.crt \
connect_timeout=5"

Dentro de psql:

\conninfo

SELECT ssl, version, cipher, bits
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

El resultado debe mostrar ssl = true, una versión TLS y un cifrado. pg_stat_ssl

Paso 10: pruebas negativas

Texto plano:

psql "host=db.ejemplo.com hostaddr=192.0.2.10 \
port=5432 dbname=appdb user=app_tls \
sslmode=disable connect_timeout=5"

Nombre incorrecto:

psql "host=otro.ejemplo.com hostaddr=192.0.2.10 \
port=5432 dbname=appdb user=app_tls \
sslmode=verify-full \
sslrootcert=$HOME/.postgresql/db-ejemplo-root.crt \
connect_timeout=5"

Ambas conexiones deben fallar. verify-full valida tanto la cadena de confianza como el nombre del servidor. PostgreSQL: TLS en libpq

Rollback

BACKUP_DIR=/root/postgresql-tls-AAAAMMDDTHHMMSSZ

CONFIG_FILE=/etc/postgresql/16/main/postgresql.conf
HBA_FILE=/etc/postgresql/16/main/pg_hba.conf
PG_VERSION=16
PG_CLUSTER=main

sudo sh -c "cd '$BACKUP_DIR' &&
  sha256sum -c SHA256SUMS"

sudo cp --preserve=all \
  "$BACKUP_DIR/postgresql.conf" "$CONFIG_FILE"

sudo cp --preserve=all \
  "$BACKUP_DIR/pg_hba.conf" "$HBA_FILE"

if sudo pg_ctlcluster "$PG_VERSION" "$PG_CLUSTER" \
  status >/dev/null 2>&1; then
  sudo pg_ctlcluster "$PG_VERSION" "$PG_CLUSTER" restart
else
  sudo pg_ctlcluster "$PG_VERSION" "$PG_CLUSTER" start
fi

if sudo test -f "$BACKUP_DIR/ufw-rule-added.txt"; then
  sudo ufw delete allow from 198.51.100.24 \
    to any port 5432 proto tcp
fi

sudo journalctl \
  -u "postgresql@${PG_VERSION}-${PG_CLUSTER}" \
  --since '-10 minutes' \
  --no-pager

Los certificados nuevos pueden conservarse para investigar: al restaurar postgresql.conf, el servidor vuelve a las rutas anteriores.

Aprende a Cómo proteger filas por usuario con Row-Level Security en PostgreSQL

Qué vas a lograr

Requisitos

Paso 1: registrar rutas y estado actual

Paso 2: crear un backup verificable

Paso 3: preparar el certificado

Paso 4: instalar archivos versionados

Paso 5: configurar PostgreSQL

Paso 6: exigir TLS en `pg_hba.conf`

Paso 7: validar antes de reiniciar

Paso 8: reiniciar y comprobar

Paso 9: conectar con `verify-full`

Paso 10: pruebas negativas

Rollback

Cloud Serversby Donweb

Publicaciones relacionadas

Qué vas a lograr

Requisitos

Paso 1: registrar rutas y estado actual

Paso 2: crear un backup verificable

Paso 3: preparar el certificado

Paso 4: instalar archivos versionados

Paso 5: configurar PostgreSQL

Paso 6: exigir TLS en pg_hba.conf

Paso 7: validar antes de reiniciar

Paso 8: reiniciar y comprobar

Paso 9: conectar con verify-full

Paso 10: pruebas negativas

Rollback

Cloud Serversby Donweb

Publicaciones relacionadas

Únete a la comunidadpara más beneficios

Paso 6: exigir TLS en `pg_hba.conf`

Paso 9: conectar con `verify-full`

Únete a la comunidad
para más beneficios