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.
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.
-
Installez les outils nécessaires et récupérez la clé du dépôt :
Terminalsudo 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 -
Vérifiez l'empreinte de la clé :
Terminalgpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.ascElle doit correspondre à
B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8. Si ce n'est pas le cas, arrêtez-vous là. -
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 -
Installez le serveur :
Terminalsudo apt update sudo apt install -y postgresql-18Le paquet crée et démarre automatiquement une instance nommée
18/main.
Vérifier l'installation
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo -u postgres psql -c "SELECT version();"
sudo ss -tlnp | grep 5432pg_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 :
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 :
peerpour les connexions locales par socket : le nom de l'utilisateur système doit correspondre au nom du rôle. C'est pourquoisudo -u postgres psqlfonctionne sans mot de passe.scram-sha-256pour les connexions TCP depuis127.0.0.1et::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.
-
Créez les rôles. Chaque commande demande un mot de passe, qui ne reste ainsi pas dans l'historique du shell :
Terminalsudo -u postgres createuser --pwprompt app_owner sudo -u postgres createuser --pwprompt app_reader -
Créez la base :
Terminalsudo -u postgres createdb --owner=app_owner appdb -
Limitez l'accès à cette base :
Terminalsudo -u postgres psql -d appdbSQLREVOKE 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; \qLa dernière instruction donne aussi à
app_readerun droit de lecture sur les futures tables créées parapp_owner. -
Vérifiez la connexion avec mot de passe :
Terminalpsql -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_readerdoit échouer avecpermission denied for table restore_check. La tablerestore_checkservira 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 :
ssh -N -L 5433:127.0.0.1:5432 VOTRE_USER@VOTRE_IP_SERVEURLaissez cette commande active, puis connectez votre client sur le port local 5433 :
psql -h 127.0.0.1 -p 5433 -U app_owner -d appdbOption 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.
-
Créez un script qui copie le certificat pour PostgreSQL et s'exécutera à chaque renouvellement :
Terminalsudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.shTerminal#!/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 -
Rendez-le exécutable et lancez-le une première fois :
Terminalsudo 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 -
Créez le fichier de configuration :
Terminalsudo nano /etc/postgresql/18/main/conf.d/90-remote-access.confConfigurationlisten_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' -
Ajoutez à la fin de
/etc/postgresql/18/main/pg_hba.confune règle limitée à une base, un rôle et une adresse :Textehostssl appdb app_owner VOTRE_IP/32 scram-sha-256hostsslrefuse toute connexion non chiffrée. Pour une adresse IPv6, utilisez le suffixe/128. -
Autorisez uniquement votre adresse dans le pare-feu. Avec UFW :
Terminalsudo ufw allow from VOTRE_IP to any port 5432 proto tcp -
Redémarrez PostgreSQL, car
listen_addressesn'est pas pris en compte par un simple rechargement :Terminalsudo 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.
-
Connectez-vous depuis votre poste avec un client
psqlen version 16 ou plus :Terminalpsql "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 :
SQLSELECT 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.
-
Créez le dossier de destination :
Terminalsudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql -
Créez le script :
Terminalsudo nano /usr/local/sbin/postgresql-backup.shTerminal#!/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 {} + -
Créez le service :
Terminalsudo chmod 755 /usr/local/sbin/postgresql-backup.sh sudo nano /etc/systemd/system/postgresql-backup.serviceConfiguration[Unit] Description=PostgreSQL backup After=postgresql.service [Service] Type=oneshot User=postgres Group=postgres ExecStart=/usr/local/sbin/postgresql-backup.sh -
Créez le minuteur :
Terminalsudo nano /etc/systemd/system/postgresql-backup.timerConfiguration[Unit] Description=Daily PostgreSQL backup [Timer] OnCalendar=*-*-* 02:30:00 RandomizedDelaySec=10min Persistent=true [Install] WantedBy=timers.target -
Activez-le et lancez une première sauvegarde :
Terminalsudo 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.
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_testVous 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 :
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 :
sudo apt update
sudo apt upgradeUne 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 :
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_lsclusterspg_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 :
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 :
sudo systemctl reload postgresql@18-mainFATAL: 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 :
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é
pg_lsclusters
sudo ss -tlnp | grep 5432
sudo ufw status
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.logConnection 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 :
sudo journalctl -u postgresql@18-main -n 50 --no-pager
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.logAvec 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.