FR

Linux et réseau

Installer et sécuriser PostgreSQL

Installez PostgreSQL 18 depuis le dépôt officiel sur Debian ou Ubuntu : rôles et droits, accès distant en TLS, sauvegardes automatiques et test de restauration.

9 min de lecture, mis à jour le 18 novembre 2026

Sur cette page

Vos valeurs

Renseignez-les une fois, les commandes du guide se mettent à jour.

Ce guide vous explique comment installer PostgreSQL sur votre VPS ou serveur dédié depuis le dépôt officiel du projet, créer des rôles aux droits limités, ouvrir si besoin un accès distant chiffré et mettre en place des sauvegardes que vous saurez restaurer.

À la fin, PostgreSQL écoute uniquement en local, chaque application dispose de son propre rôle, et une sauvegarde quotidienne est produite puis vérifiée par un test de restauration.

Commander un serveur

Pour héberger votre base de données, HostMyServers propose plusieurs offres :

Prérequis

  • Un serveur sous Debian 12 ou 13, ou Ubuntu 22.04 ou 24.04
  • Un accès SSH avec un utilisateur disposant de sudo
  • Un système à jour (sudo apt update && sudo apt upgrade)
  • Pour l'accès distant direct uniquement : un nom de domaine pointant vers le serveur

Ports à ouvrir

Usage Port À ouvrir ?
Applications sur le même serveur 5432/tcp en local Non
Accès distant par tunnel SSH (recommandé) 22/tcp, ou votre port SSH Déjà ouvert pour l'administration
Accès distant direct en TLS 5432/tcp Oui, uniquement depuis l'adresse IP de vos clients

Installer PostgreSQL depuis le dépôt officiel

Debian et Ubuntu fournissent PostgreSQL, mais la version reste figée pendant toute la vie de la distribution. Le dépôt PGDG (apt.postgresql.org) propose toutes les versions maintenues.

À la date de rédaction, la dernière version majeure est PostgreSQL 18 (18.7). Consultez la politique de versions pour connaître la version actuelle.

  1. Installez les outils nécessaires et récupérez la clé du dépôt :

    Terminal
    sudo apt install -y curl ca-certificates gpg
    sudo install -d /usr/share/postgresql-common/pgdg
    sudo curl -fsSL -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc
  2. Vérifiez l'empreinte de la clé :

    Terminal
    gpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc

    Elle doit correspondre à B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8. Si ce n'est pas le cas, arrêtez-vous là.

  3. Déclarez le dépôt :

    Terminal
    . /etc/os-release
    sudo tee /etc/apt/sources.list.d/pgdg.sources > /dev/null <<EOF
    Types: deb
    URIs: https://apt.postgresql.org/pub/repos/apt
    Suites: ${VERSION_CODENAME}-pgdg
    Architectures: $(dpkg --print-architecture)
    Components: main
    Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
    EOF
  4. Installez le serveur :

    Terminal
    sudo apt update
    sudo apt install -y postgresql-18

    Le paquet crée et démarre automatiquement une instance nommée 18/main.

Vérifier l'installation

Terminal
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo -u postgres psql -c "SELECT version();"
sudo ss -tlnp | grep 5432

pg_lsclusters doit afficher l'instance 18 main avec le statut online. ss doit montrer une écoute sur 127.0.0.1:5432 et [::1]:5432, et non sur 0.0.0.0.

Élément Emplacement
Configuration /etc/postgresql/18/main/
Réglages additionnels /etc/postgresql/18/main/conf.d/
Règles d'accès /etc/postgresql/18/main/pg_hba.conf
Données /var/lib/postgresql/18/main/
Journal /var/log/postgresql/postgresql-18-main.log

Comprendre l'authentification

Le fichier pg_hba.conf décide qui peut se connecter, à quelle base et avec quelle méthode. PostgreSQL lit ses lignes de haut en bas et applique la première qui correspond. Si cette méthode échoue, les lignes suivantes ne sont pas essayées.

Affichez les règles actives :

Terminal
sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"

Sur une installation neuve, vous devez trouver deux méthodes :

  • peer pour les connexions locales par socket : le nom de l'utilisateur système doit correspondre au nom du rôle. C'est pourquoi sudo -u postgres psql fonctionne sans mot de passe.
  • scram-sha-256 pour les connexions TCP depuis 127.0.0.1 et ::1 : un mot de passe est demandé.

Créer des rôles et une base

N'utilisez pas le superutilisateur postgres dans vos applications. L'exemple suivant crée une base appdb, un rôle propriétaire app_owner et un rôle en lecture seule app_reader.

  1. Créez les rôles. Chaque commande demande un mot de passe, qui ne reste ainsi pas dans l'historique du shell :

    Terminal
    sudo -u postgres createuser --pwprompt app_owner
    sudo -u postgres createuser --pwprompt app_reader
  2. Créez la base :

    Terminal
    sudo -u postgres createdb --owner=app_owner appdb
  3. Limitez l'accès à cette base :

    Terminal
    sudo -u postgres psql -d appdb
    SQL
    REVOKE ALL ON DATABASE appdb FROM PUBLIC;
    GRANT CONNECT ON DATABASE appdb TO app_reader;
    GRANT USAGE ON SCHEMA public TO app_reader;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
    ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO app_reader;
    \q

    La dernière instruction donne aussi à app_reader un droit de lecture sur les futures tables créées par app_owner.

  4. Vérifiez la connexion avec mot de passe :

    Terminal
    psql -h 127.0.0.1 -U app_owner -d appdb -c "CREATE TABLE restore_check (id int PRIMARY KEY, note text);"
    psql -h 127.0.0.1 -U app_owner -d appdb -c "INSERT INTO restore_check VALUES (1, 'sauvegarde OK');"
    psql -h 127.0.0.1 -U app_reader -d appdb -c "SELECT * FROM restore_check;"

    Une écriture avec app_reader doit échouer avec permission denied for table restore_check. La table restore_check servira plus loin à contrôler vos restaurations.

Accéder à la base à distance

Option recommandée : tunnel SSH

Le tunnel ne demande aucune modification de PostgreSQL ni du pare-feu. Depuis votre poste :

Terminal
ssh -N -L 5433:127.0.0.1:5432 VOTRE_USER@VOTRE_IP_SERVEUR

Laissez cette commande active, puis connectez votre client sur le port local 5433 :

Terminal
psql -h 127.0.0.1 -p 5433 -U app_owner -d appdb

Option avancée : connexion directe en TLS

Utilisez cette option si un service externe ne peut pas passer par SSH. Elle demande un nom de domaine, par exemple db.votre-domaine.com, et un certificat valide : consultez notre guide Installer un certificat SSL avec Let's Encrypt pour l'obtenir avec Certbot.

  1. Créez un script qui copie le certificat pour PostgreSQL et s'exécutera à chaque renouvellement :

    Terminal
    sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
    Terminal
    #!/bin/sh
    set -e
    DOMAIN="db.votre-domaine.com"
    [ "$RENEWED_LINEAGE" = "/etc/letsencrypt/live/$DOMAIN" ] || exit 0
    install -o postgres -g postgres -m 644 "$RENEWED_LINEAGE/fullchain.pem" /etc/postgresql/18/main/server.crt
    install -o postgres -g postgres -m 600 "$RENEWED_LINEAGE/privkey.pem" /etc/postgresql/18/main/server.key
    systemctl reload postgresql@18-main
  2. Rendez-le exécutable et lancez-le une première fois :

    Terminal
    sudo chmod 755 /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
    sudo RENEWED_LINEAGE=/etc/letsencrypt/live/db.votre-domaine.com /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
  3. Créez le fichier de configuration :

    Terminal
    sudo nano /etc/postgresql/18/main/conf.d/90-remote-access.conf
    Configuration
    listen_addresses = '*'
    ssl = on
    ssl_cert_file = '/etc/postgresql/18/main/server.crt'
    ssl_key_file = '/etc/postgresql/18/main/server.key'
    ssl_min_protocol_version = 'TLSv1.2'
  4. Ajoutez à la fin de /etc/postgresql/18/main/pg_hba.conf une règle limitée à une base, un rôle et une adresse :

    Texte
    hostssl appdb app_owner VOTRE_IP/32 scram-sha-256

    hostssl refuse toute connexion non chiffrée. Pour une adresse IPv6, utilisez le suffixe /128.

  5. Autorisez uniquement votre adresse dans le pare-feu. Avec UFW :

    Terminal
    sudo ufw allow from VOTRE_IP to any port 5432 proto tcp
  6. Redémarrez PostgreSQL, car listen_addresses n'est pas pris en compte par un simple rechargement :

    Terminal
    sudo systemctl restart postgresql@18-main
    sudo -u postgres psql -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

    La seconde commande ne doit renvoyer aucune ligne.

  7. Connectez-vous depuis votre poste avec un client psql en version 16 ou plus :

    Terminal
    psql "host=db.votre-domaine.com port=5432 dbname=appdb user=app_owner sslmode=verify-full sslrootcert=system"

    Contrôlez ensuite que la session est chiffrée :

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

Automatiser les sauvegardes

pg_dump exporte une base sans l'arrêter, mais il ne sauvegarde ni les rôles ni leurs mots de passe. Le script suivant exporte donc aussi ces éléments globaux avec pg_dumpall --globals-only.

  1. Créez le dossier de destination :

    Terminal
    sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
  2. Créez le script :

    Terminal
    sudo nano /usr/local/sbin/postgresql-backup.sh
    Terminal
    #!/bin/bash
    set -euo pipefail
    umask 077
    
    DEST="/var/backups/postgresql"
    RETENTION_DAYS=14
    STAMP="$(date +%F_%H%M%S)"
    TARGET="$DEST/$STAMP"
    
    mkdir -p "$TARGET"
    pg_dumpall --globals-only -f "$TARGET/globals.sql"
    
    psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate" |
    while IFS= read -r db; do
      pg_dump -Fc -d "$db" -f "$TARGET/$db.dump"
    done
    
    find "$DEST" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} +
  3. Créez le service :

    Terminal
    sudo chmod 755 /usr/local/sbin/postgresql-backup.sh
    sudo nano /etc/systemd/system/postgresql-backup.service
    Configuration
    [Unit]
    Description=PostgreSQL backup
    After=postgresql.service
    
    [Service]
    Type=oneshot
    User=postgres
    Group=postgres
    ExecStart=/usr/local/sbin/postgresql-backup.sh
  4. Créez le minuteur :

    Terminal
    sudo nano /etc/systemd/system/postgresql-backup.timer
    Configuration
    [Unit]
    Description=Daily PostgreSQL backup
    
    [Timer]
    OnCalendar=*-*-* 02:30:00
    RandomizedDelaySec=10min
    Persistent=true
    
    [Install]
    WantedBy=timers.target
  5. Activez-le et lancez une première sauvegarde :

    Terminal
    sudo systemctl daemon-reload
    sudo systemctl enable --now postgresql-backup.timer
    sudo systemctl start postgresql-backup.service
    sudo systemctl status postgresql-backup.service --no-pager
    sudo -u postgres ls -lR /var/backups/postgresql

Tester la restauration

Une sauvegarde n'a de valeur que si vous avez déjà réussi à la restaurer. Ce test utilise une base temporaire et ne touche pas à la base de production.

Terminal
STAMP="$(sudo -u postgres ls -1 /var/backups/postgresql | tail -n 1)"
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --no-owner --no-privileges --exit-on-error -d restore_test "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d restore_test -c "SELECT * FROM restore_check;"
sudo -u postgres dropdb restore_test

Vous devez retrouver la ligne sauvegarde OK. Refaites ce contrôle régulièrement, et comparez aussi quelques tables importantes pour votre application.

Restaurer après un incident

Sur un serveur neuf où PostgreSQL est installé, ou après avoir arrêté les applications et supprimé la base endommagée :

Terminal
STAMP="2026-11-18_023000"
sudo -u postgres psql -X -f "/var/backups/postgresql/$STAMP/globals.sql" postgres
sudo -u postgres dropdb --if-exists appdb
sudo -u postgres pg_restore --create --exit-on-error -d postgres "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d appdb -c "ANALYZE;"

Remplacez la valeur de STAMP par le dossier voulu. L'erreur role "postgres" already exists à la restauration des rôles est normale.

Mettre à jour PostgreSQL

Les versions mineures (par exemple de 18.6 à 18.7) corrigent des bogues et des failles. Elles s'installent avec le système et redémarrent le service :

Terminal
sudo apt update
sudo apt upgrade

Une nouvelle version majeure s'installe à côté de l'ancienne et demande une migration. Lancez d'abord une sauvegarde, puis adaptez les numéros de version :

Terminal
OLD=18
NEW=19
sudo systemctl start postgresql-backup.service
sudo apt install -y "postgresql-$NEW"
pg_lsclusters
sudo pg_dropcluster --stop "$NEW" main
sudo pg_upgradecluster -v "$NEW" "$OLD" main
pg_lsclusters

pg_dropcluster supprime ici l'instance vide créée par le nouveau paquet : vérifiez avec pg_lsclusters qu'elle ne contient rien d'utile. pg_upgradecluster place ensuite la nouvelle version sur le port d'origine et conserve l'ancienne, arrêtée. Une fois vos applications contrôlées, retirez-la :

Terminal
sudo pg_dropcluster "$OLD" main
sudo apt purge "postgresql-$OLD" "postgresql-client-$OLD"

Pensez à adapter le numéro de version dans le script de renouvellement du certificat si vous l'utilisez.

En cas de problème

FATAL: no pg_hba.conf entry for host

Aucune règle ne correspond à l'adresse, au rôle, à la base ou au chiffrement. Le message indique les valeurs reçues par le serveur. Vérifiez qu'elles correspondent à votre ligne hostssl, y compris l'adresse IP publique réelle de votre client, puis rechargez la configuration :

Terminal
sudo systemctl reload postgresql@18-main

FATAL: password authentication failed for user

Le mot de passe est incorrect ou le rôle n'en possède pas. Redéfinissez-le depuis une session d'administration :

Terminal
sudo -u postgres psql -c "\password app_owner"

FATAL: Peer authentication failed for user

Vous vous connectez par le socket local avec un rôle différent de votre utilisateur système. Ajoutez -h 127.0.0.1 pour utiliser le mot de passe, ou lancez la commande avec le bon utilisateur via sudo -u.

Connection refused ou délai dépassé

Terminal
pg_lsclusters
sudo ss -tlnp | grep 5432
sudo ufw status
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

Connection refused indique en général que PostgreSQL est arrêté ou n'écoute pas sur cette adresse. Un délai dépassé oriente plutôt vers un pare-feu.

permission denied for table ou for schema public

Le rôle n'a pas les droits nécessaires. Rejouez les GRANT en étant connecté à la bonne base. Pour les tables existantes créées par un autre rôle, ALTER DEFAULT PRIVILEGES ne suffit pas : il ne concerne que les futures tables.

Le service ne démarre plus après une modification

Le journal indique le fichier et la ligne en cause :

Terminal
sudo journalctl -u postgresql@18-main -n 50 --no-pager
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

Avec un certificat personnalisé, vérifiez aussi que server.key appartient à postgres avec les droits 600.

could not change directory to "/root"

Ce message apparaît quand vous lancez sudo -u postgres depuis un dossier que postgres ne peut pas lire. Il est sans conséquence : placez-vous dans /tmp pour le faire disparaître.

Références