Chapitre 11.1 — Installer PostgreSQL

⏱️ TL;DR — Un apt install postgresql et le serveur de base de données tourne déjà, en local, sous un utilisateur système dédié nommé postgres. Le geste fondateur : ne pas faire tourner ton app avec le super-utilisateur de la base. Tu vas créer un rôle applicatif (formacampus) avec un mot de passe fort, une base dont il est propriétaire, puis t’y connecter — au terminal avec psql, et depuis ton app avec une chaîne de connexion DATABASE_URL. Ce chapitre pose les fondations ; la sécurité de l’accès, c’est le chapitre suivant.

🎯 Objectifs

• Installer PostgreSQL et vérifier que le service tourne.
• Comprendre le rôle de l’utilisateur système postgres et de sudo -u postgres psql.
• Créer un rôle applicatif dédié, une base, et lui donner les droits.
• Te connecter avec psql et maîtriser les commandes de base (\l, \c, \dt, \du, \q).
• Construire la chaîne de connexion que ton app utilisera.

Installer le serveur

PostgreSQL est dans les dépôts d’Ubuntu et Debian. On rafraîchit l’index puis on installe.

🐚 Au terminal — Installer PostgreSQL et vérifier le service :

sudo apt update                 # rafraîchit l'index des paquets
sudo apt install postgresql     # installe le serveur (et le client psql)
 
systemctl status postgresql     # le service doit être "active"

Le paquet postgresql installe le serveur, le client psql, et démarre le service, configuré pour redémarrer au boot. La version fournie est la version stable de ta distribution (ne cherche pas à mémoriser un numéro : psql --version te la dira le jour venu). Par défaut, PostgreSQL écoute uniquement en local — bonne nouvelle, c’est exactement ce qu’on veut. On verrouillera ça finement au chapitre 11.2.

💡 Réflexe — Après toute installation de service, deux vérifications immédiates : systemctl status <service> (il tourne ?) et systemctl is-enabled <service> (il redémarrera au reboot ?). Un service installé mais qui ne remonte pas après un redémarrage, c’est la panne « inexplicable » du lundi matin.

L’utilisateur système postgres

L’installation crée un utilisateur système Linux nommé postgres. C’est lui qui possède les fichiers de données et sous lequel tourne le serveur. PostgreSQL fait par défaut correspondre cet utilisateur Linux à un super-utilisateur de base du même nom : c’est le mécanisme d’authentification peer (l’identité Linux fait foi, sans mot de passe).

Concrètement, pour ouvrir une console d’administration, tu empruntes l’identité postgres le temps d’une commande :

sudo -u postgres psql
# ouvre psql en tant que super-utilisateur "postgres"
# l'invite devient : postgres=#

Ici, sudo -u postgres exécute la commande suivante en tant que l’utilisateur postgres, et psql est le client en ligne de commande. Comme l’identité Linux correspond, aucun mot de passe n’est demandé. Tu es maintenant admin de la base : c’est de là qu’on crée rôles et bases.

⚠️ Piège — Ne fais jamais tourner ton application avec le super-utilisateur postgres. Il peut tout : supprimer n’importe quelle base, lire toutes les données, créer d’autres rôles. Une faille dans l’app deviendrait une compromission totale du serveur de base. On crée toujours un rôle dédié, aux droits limités à sa base.

Créer le rôle applicatif et la base

Le principe : un rôle par application, propriétaire de sa base et de rien d’autre. Dans la console ouverte via sudo -u postgres psql, on tape du SQL.

-- 1. Créer un rôle applicatif avec un mot de passe FORT
CREATE USER formacampus WITH PASSWORD 'REMPLACE-par-un-secret-long-et-aleatoire';
 
-- 2. Créer une base dont ce rôle est propriétaire
CREATE DATABASE formacampus OWNER formacampus;
 
-- 3. (ceinture et bretelles) lui accorder tous les droits sur cette base
GRANT ALL PRIVILEGES ON DATABASE formacampus TO formacampus;

Décortiquons. CREATE USER crée un rôle capable de se connecter (dans PostgreSQL, « user » est un rôle avec le droit de login). Le WITH PASSWORD fixe son mot de passe — c’est ce secret que ton app présentera. CREATE DATABASE ... OWNER formacampus crée la base et en fait formacampus le propriétaire : il pourra créer ses tables, migrer son schéma, sans jamais toucher aux autres bases. Le GRANT est une sécurité explicite qui confirme les droits sur cette base précise.

🔒 Sécurité — Le mot de passe de ce rôle doit être fort : long (24+ caractères), aléatoire, généré par une machine (openssl rand -base64 24 fait très bien l’affaire), jamais un mot du dictionnaire ni réutilisé. Ce secret finit dans ton fichier .env sur le serveur — jamais dans git. C’est la clé de toutes les données de tes utilisateurs ; traite-le comme tel.

Vérifie ton travail sans quitter psql :

\du    -- liste les rôles : tu dois voir "formacampus"
\l     -- liste les bases : tu dois voir la base "formacampus"
\q     -- quitte psql

Se connecter en tant que rôle applicatif

Jusqu’ici tu étais postgres (admin). Vérifions maintenant que le rôle applicatif peut se connecter à sa base, exactement comme le fera l’app :

psql -U formacampus -h localhost -d formacampus
# -U : le rôle       -h localhost : force une connexion réseau locale (TCP)
# -d : la base       un mot de passe sera demandé

Pourquoi -h localhost alors qu’on est sur la même machine ? Sans -h, psql tente une connexion par socket Unix, souvent réglée en peer (elle exigerait un utilisateur Linux formacampus). Avec -h localhost, tu passes en TCP local sur 127.0.0.1, la méthode par mot de passe — celle que ton app utilisera. Si la connexion aboutit et affiche l’invite formacampus=>, ta base applicative est prête. (Le détail de ces méthodes d’authentification, c’est tout le chapitre 11.2.)

Les commandes psql à connaître

psql est un client complet. Les méta-commandes commencent par un antislash et ne sont pas du SQL — elles inspectent la base. Le minimum vital :

Tout ce qui ne commence pas par un antislash est du SQL et doit finir par un point-virgule (SELECT now();). Oublier le ; est l’erreur numéro un du débutant : psql attend alors la suite de ta requête au lieu de l’exécuter.

📚 La doc — Les méta-commandes psql sont listées par \? dans la console, et par man psql au terminal. La référence SQL complète (CREATE USER, GRANT, CREATE DATABASE…) est sur la doc officielle PostgreSQL, section « SQL Commands » — la source qui fait autorité, versionnée.

La chaîne de connexion, côté application

Ton app ne tape pas de commandes psql : elle lit une chaîne de connexion, généralement dans une variable d’environnement. Le format standard :

# fichier .env sur le serveur (jamais commité dans git)
DATABASE_URL=postgres://formacampus:LE-MOT-DE-PASSE@localhost:5432/formacampus

On lit cette URL de gauche à droite : le protocole postgres://, puis formacampus (le rôle), : le mot de passe, @localhost l’hôte, :5432 le port par défaut de PostgreSQL, et /formacampus la base. Cette même chaîne alimente aussi bien un ORM Node (Prisma, Drizzle) que Doctrine côté Symfony. Le point crucial : l’hôte est localhost — l’app et la base sont sur le même VPS, la connexion ne quitte jamais la machine.

🧭 Sur FormaCampus — L’équipe installe PostgreSQL sur le même VPS Ubuntu LTS que l’API Symfony. Elle crée le rôle formacampus (mot de passe généré par openssl rand), la base formacampus dont il est propriétaire, et pose DATABASE_URL=postgres://formacampus:...@localhost:5432/formacampus dans le .env de l’API. La base contient des données personnelles d’apprenants : la connexion en localhost garantit qu’elle ne transite jamais par le réseau public, première brique de leur conformité (voir le cours RGPD).

✏️ Exercices

Exercice 1 — Crée une base pour un projet. Tu déploies une app boutique. Écris la séquence SQL (dans sudo -u postgres psql) qui crée un rôle boutique avec mot de passe, une base boutique dont il est propriétaire, et lui donne les droits. Puis la commande psql pour t’y connecter en tant que ce rôle.

CREATE USER boutique WITH PASSWORD 'un-secret-long-et-aleatoire';
CREATE DATABASE boutique OWNER boutique;
GRANT ALL PRIVILEGES ON DATABASE boutique TO boutique;

psql -U boutique -h localhost -d boutique

Exercice 2 — Décode la chaîne. Un collègue te donne postgres://app:s3cr3t@localhost:5432/prod. Nomme chaque composant, et dis ce qui te dérange dans cet exemple du point de vue sécurité.

🧠 Quiz de révision

Chapitre suivant : Accès & pg_hba — comment PostgreSQL décide qui a le droit de se connecter, et pourquoi le port ne doit jamais toucher Internet.