Comment activer SSL pour les connexions PostgreSQL

Par défaut, toutes les connexions PostgreSQL sont non sécurisées, ce qui peut causer des problèmes de sécurité lorsqu’elles sont exécutées dans des environnements de production à fort trafic. Le chiffrement SSL garantit que les données transférées ne sont pas interceptées par quiconque au milieu d’une connexion.

Ce tutoriel vous apprendra à activer SSL/TLS pour les connexions PostgreSQL.

Prérequis

• Un serveur Linux. Pour notre tutoriel, nous exécutons un serveur Ubuntu 22.04.
• Un utilisateur sudo non-root.
• Un nom de domaine entièrement qualifié (FQDN) comme postgresql.example.com.
• Assurez-vous que tout est à jour. $ sudo apt update $ sudo apt upgrade
• Quelques paquets dont votre système a besoin. $ sudo apt install curl nano software-properties-common apt-transport-https ca-certificates lsb-release ubuntu-keyring -y Certains de ces paquets peuvent déjà être installés sur votre système.

Étape 1 - Configurer le pare-feu

Avant d’installer des paquets, la première étape consiste à configurer le pare-feu pour ouvrir les ports pour HTTP, HTTPS et PostgreSQL.

Vérifiez l’état du pare-feu.

$ sudo ufw status

Vous devriez voir quelque chose comme ceci.

Status: active

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)

Ouvrez les ports HTTP, HTTPS et PostgreSQL dans le pare-feu.

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

Vérifiez à nouveau l’état pour confirmer.

$ sudo ufw status
Status: active

To                         Action      From
--                         ------      ----
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)             

Étape 2 - Installer PostgreSQL 14

Ubuntu 22.04 est livré avec PostgreSQL 14 par défaut. Pour l’installer, exécutez la commande suivante.

$ sudo apt install postgresql postgresql-contrib

Le paquet postgresql-contrib contient quelques utilitaires supplémentaires.

Vous pouvez également utiliser le dépôt APT officiel de PostgreSQL pour installer. Exécutez la commande suivante pour ajouter la clé GPG de PostgreSQL.

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

Ajoutez le dépôt APT à votre liste de sources.

$ 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'

Mettez à jour le dépôt système.

$ sudo apt update

Maintenant, vous pouvez installer PostgreSQL en utilisant la commande mentionnée ci-dessus.

Vérifiez l’état du service PostgreSQL.

$ sudo systemctl status postgresql
? postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2022-12-12 00:01:06 UTC; 19s ago
   Main PID: 3497 (code=exited, status=0/SUCCESS)
        CPU: 1ms

Dec 12 00:01:06 postgresql systemd[1]: Starting PostgreSQL RDBMS...
Dec 12 00:01:06 postgresql systemd[1]: Finished PostgreSQL RDBMS.

Vous pouvez voir que le service est activé et en cours d’exécution par défaut.

Définissez le mot de passe pour le compte postgres.

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

Étape 3 - Installer SSL

Pour installer un certificat SSL en utilisant Let’s Encrypt, nous devons télécharger l’outil Certbot. Nous utiliserons le gestionnaire de paquets Snapd pour cela. Ubuntu 22.04 est livré avec Snap préinstallé.

Assurez-vous que votre version de Snapd est à jour.

$ sudo snap install core 
$ sudo snap refresh core

Installez Certbot.

$ sudo snap install --classic certbot

Utilisez la commande suivante pour vous assurer que la commande Certbot s’exécute en créant un lien symbolique vers le répertoire /usr/bin.

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

Générez un certificat SSL.

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

La commande ci-dessus téléchargera un certificat dans le répertoire /etc/letsencrypt/live/postgresql.example.com sur votre serveur.

Générez un certificat groupe Diffie-Hellman.

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

Étape 4 - Configurer le renouvellement de Certbot pour PostgreSQL

PostgreSQL n’a pas la permission d’accéder aux certificats du dossier Let’s Encrypt, donc nous ne pouvons pas lui dire d’utiliser les certificats du dossier directement. L’alternative consiste à copier les certificats dans le répertoire PostgreSQL, mais cela ne fonctionne que temporairement car ils expireront et vous devrez les copier à nouveau manuellement.

La meilleure méthode consiste à utiliser un hook de renouvellement qui s’exécutera automatiquement à chaque renouvellement et effectuera les opérations de copie.

Recherchez le répertoire de données PostgreSQL.

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

Créez le fichier de hook de renouvellement et ouvrez-le pour l’édition.

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

Collez le code suivant dedans.

#!/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
# uniquement pour SELinux - CentOS, Red Hat
# chcon -t postgresql_db_t $DATA_DIR/server.crt $DATA_DIR/server.key

Enregistrez le fichier en appuyant sur Ctrl + X et en entrant Y lorsqu’on vous le demande.

Rendez le fichier exécutable.

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

Étape 5 - Configurer PostgreSQL

Trouvez le chemin du fichier de configuration PostgreSQL.

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

Ouvrez le fichier pour l’édition.

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

Localisez la section Paramètres de connexion et décommentez la variable listen_address et changez sa valeur en *. Assurez-vous qu’elle ressemble à ceci.

listen_address = '*'      # quelle(s) adresse(s) IP écouter;

Localisez la section SSL et modifiez le fichier pour correspondre aux valeurs suivantes.

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'

Enregistrez le fichier en appuyant sur Ctrl + X et en entrant Y lorsqu’on vous le demande.

Étape 6 - Configurer la connexion PostgreSQL

Ouvrez le fichier /etc/postgresql/15/main/pg_hba.conf pour l’édition.

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

Ajoutez la ligne suivante pour activer SSL pour PostgreSQL.

hostssl all  all  0.0.0.0/0  scram-sha-256

Enregistrez le fichier en appuyant sur Ctrl + X et en entrant Y lorsqu’on vous le demande.

Étape 7 - Renouveler le certificat

Exécutez la commande suivante pour effectuer un renouvellement forcé. Cela déclenchera le script de déploiement qui copie les certificats à l’emplacement correct pour que PostgreSQL puisse les utiliser.

$ sudo certbot renew --force-renewal

Vérifiez que les certificats sont copiés dans le répertoire de données PostgreSQL.

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

Vous verrez la sortie suivante qui vous montrera les certificats.

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

Redémarrez PostgreSQL pour appliquer les modifications.

$ sudo systemctl restart postgresql

Étape 8 - Tester la connexion

Connectez-vous à la base de données depuis une autre machine avec le client PostgreSQL installé.

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

Vous devriez voir l’invite PostgreSQL suivante. Nous utilisons un client avec PostgreSQL 14, donc vous verrez un avertissement concernant des versions incompatibles.

Password for user postgres:
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1), server 15.1 (Ubuntu 15.1-1.pgdg22.04+1))
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Cela confirme une connexion SSL réussie.

Quittez le shell.

postgres=# \q

Si votre application utilise une chaîne de connexion, utilisez-la dans le format suivant pour une connexion SSL.

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

Vous pouvez changer le mode SSL en verify-full ou verify-ca si vous avez le certificat racine pour Let’s Encrypt disponible dans le répertoire /var/lib/postgresql/.postgresql du côté client.

Créez le répertoire /var/lib/postgresql/.postgresql.

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

Le certificat racine Let’s Encrypt est ISRG Root X1 qui se trouve sur le serveur à l’emplacement /usr/share/ca-certificates/mozilla/ISRG_Root_X1.crt.

Copiez le certificat racine dans le répertoire /var/lib/postgresql/.postgresql.

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

Testez la connexion en utilisant le mode verify-full ou verify-ca et vous devriez voir une connexion réussie.

Étape 9 - Vérifier les clients

Connectez-vous au shell PostgreSQL sur le serveur.

$ sudo -i -u postgres psql

Exécutez la commande SQL suivante pour vérifier les clients connectés.

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;

Vous devriez voir une sortie similaire.

 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 rows)

Cela confirme la connexion du client du côté serveur.

Conclusion

Cela conclut le tutoriel sur l’activation de SSL sur les connexions PostgreSQL. Si vous avez des questions, posez-les dans les commentaires ci-dessous.