// travaux pratiques · Fondamentaux en informatique
TP02 - Créer une base, un rôle et des tables avec contraintes
- TP 2 / 7
- 7 étapes
- ≈ 45 min
- Accès libre
Objectif : créer un rôle propriétaire et une base dédiée, puis concevoir les trois tables d’un inventaire de parc informatique avec clés primaires, clés étrangères et contraintes de validation, et vérifier que la base refuse les données incohérentes.
Contexte
Une base de données bien conçue protège les données contre les erreurs avant même qu’une application ne les vérifie : un équipement rattaché à un site inexistant, une durée d’intervention négative ou deux machines portant le même nom sont refusés par le moteur lui-même. Ces règles, posées une fois dans le schéma, valent pour tous les programmes et tous les utilisateurs qui écrivent dans la base, qu’il s’agisse d’un script d’import dans une petite équipe ou de dizaines d’applications dans une grande DSI.
Le laboratoire du cours repose sur un cas familier aux équipes IT : l’inventaire du parc. La table sites décrit les implantations, equipements les serveurs, postes et équipements réseau, et interventions l’historique des opérations des techniciens. À la fin du TP, vous saurez créer une base appartenant à un rôle non privilégié et traduire des règles de gestion en contraintes SQL.
Prérequis : TP01 terminé (PostgreSQL 17 en service sur pg01).
Durée estimée : 45 minutes.
Actions à réaliser
1. Créer le rôle propriétaire
On n’utilise pas le superutilisateur postgres pour les objets d’une application : on crée un rôle dédié, autorisé à se connecter, sans privilège d’administration de l’instance. createuser est un utilitaire qui exécute CREATE ROLE ; l’option --pwprompt (-P) demande le mot de passe de façon interactive, ce qui évite qu’il apparaisse dans l’historique du shell.
sudo -u postgres createuser --pwprompt parc_admin
Résultat attendu : aucune sortie après la saisie du mot de passe. Dans sudo -u postgres psql -c '\du', parc_admin apparaît sans aucun attribut particulier.
2. Créer la base et lui donner un propriétaire
createdb -O crée la base et en donne la propriété au rôle indiqué. Depuis PostgreSQL 15, le schéma public d’une nouvelle base appartient au rôle spécial pg_database_owner, c’est-à-dire au propriétaire de la base : parc_admin pourra y créer ses tables, alors que les autres rôles ne le pourront pas.
sudo -u postgres createdb -O parc_admin parc
Résultat attendu : \l dans psql montre la base parc avec parc_admin comme propriétaire, encodage UTF8.
3. Se connecter avec le nouveau rôle et un fichier .pgpass
Le rôle parc_admin n’a pas de compte système homonyme : la méthode peer le refuserait par socket. On se connecte donc en TCP sur l’adresse de bouclage, où pg_hba.conf exige un mot de passe. Pour ne pas le retaper à chaque fois, on le place dans ~/.pgpass, au format hôte:port:base:utilisateur:motdepasse. Les clients PostgreSQL ignorent ce fichier si ses droits sont plus larges que 0600.
echo 'localhost:5432:parc:parc_admin:VotreMotDePasse' >> ~/.pgpass
chmod 600 ~/.pgpass
psql -h localhost -U parc_admin -d parc
Résultat attendu : l’invite parc=> (le > indique un rôle non superutilisateur). \conninfo précise une connexion TCP à localhost, port 5432, et mentionne une connexion SSL si un certificat est configuré (c’est le cas par défaut sous Debian, avec un certificat autosigné).
4. Créer la table des sites
Une colonne identité (GENERATED ALWAYS AS IDENTITY) est la forme conforme au standard SQL de la numérotation automatique, à préférer à l’ancien pseudo-type serial. UNIQUE interdit les doublons de noms et NOT NULL rend la ville obligatoire.
CREATE TABLE sites (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
nom text NOT NULL UNIQUE,
ville text NOT NULL
);
Résultat attendu : CREATE TABLE. \d sites affiche les trois colonnes, l’index sites_pkey de la clé primaire et l’index sites_nom_key créé automatiquement par la contrainte d’unicité.
5. Créer la table des équipements
Chaque équipement appartient à un site : la clé étrangère REFERENCES sites(id) garantit que le site existe. La contrainte CHECK limite la catégorie à une liste fermée, le type inet valide les adresses IP et permet des recherches par sous-réseau, et DEFAULT renseigne la date de mise en service si elle n’est pas fournie.
CREATE TABLE equipements (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
nom_hote text NOT NULL UNIQUE,
categorie text NOT NULL
CHECK (categorie IN ('serveur','poste','switch','pare-feu','imprimante')),
site_id integer NOT NULL REFERENCES sites(id),
adresse_ip inet,
mise_en_service date NOT NULL DEFAULT current_date,
actif boolean NOT NULL DEFAULT true
);
Résultat attendu : \d equipements affiche, en bas, une section « Check constraints » (equipements_categorie_check) et une section « Foreign-key constraints » (equipements_site_id_fkey).
6. Créer la table des interventions
L’option ON DELETE CASCADE supprime l’historique d’un équipement quand celui-ci est supprimé ; c’est un choix de gestion, à discuter : beaucoup d’équipes préfèrent désactiver l’équipement (actif = false) pour garder la traçabilité. Le type timestamptz enregistre un instant précis, indépendant du fuseau horaire du client.
CREATE TABLE interventions (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
equipement_id integer NOT NULL REFERENCES equipements(id) ON DELETE CASCADE,
date_intervention timestamptz NOT NULL DEFAULT now(),
technicien text NOT NULL,
description text NOT NULL,
duree_min integer CHECK (duree_min > 0)
);
Résultat attendu : \dt liste maintenant trois tables, toutes avec parc_admin comme propriétaire.
7. Tester les contraintes
Une contrainte n’a de valeur que si l’on a vérifié qu’elle bloque bien les cas interdits. Exécutez ces insertions volontairement fautives :
INSERT INTO sites (nom, ville) VALUES ('Siège', 'Nice');
INSERT INTO sites (nom, ville) VALUES ('Siège', 'Cannes');
INSERT INTO equipements (nom_hote, categorie, site_id) VALUES ('srv-test', 'routeur', 1);
INSERT INTO equipements (nom_hote, categorie, site_id) VALUES ('srv-test', 'serveur', 99);
INSERT INTO equipements (nom_hote, categorie, site_id, adresse_ip) VALUES ('srv-test', 'serveur', 1, '192.168.300.1');
DELETE FROM sites;
Résultat attendu : la première insertion réussit ; les quatre suivantes échouent avec, dans l’ordre, une violation d’unicité (« duplicate key value violates unique constraint “sites_nom_key” »), une violation de la contrainte CHECK, une violation de clé étrangère (la clé site_id=99 n’existe pas dans sites) et une erreur de syntaxe de type inet. La dernière commande vide la table sites pour repartir proprement au TP03.
Vérification
Le TP est réussi si \dt montre sites, equipements et interventions appartenant à parc_admin, si \d equipements affiche la contrainte de vérification et la clé étrangère, et si chacune des insertions fautives de l’étape 7 a été refusée avec un message explicite.
Problèmes fréquents
- « permission denied for schema public » : la base a été créée sans
-O parc_admin, elle appartient àpostgres. Corrigez avecALTER DATABASE parc OWNER TO parc_admin;en tant quepostgres. - Le mot de passe est redemandé malgré
.pgpass: droits trop ouverts (le client affiche un avertissement), ligne mal formée, ou nom d’hôte différent (localhostdans le fichier,127.0.0.1sur la ligne de commande). Les champs doivent correspondre à ce que vous tapez. - « password authentication failed for user “parc_admin” » : mot de passe erroné. Redéfinissez-le avec
sudo -u postgres psql -c '\password parc_admin', qui le chiffre avant l’envoi. - « cannot insert a non-DEFAULT value into column “id” » : vous avez fourni une valeur pour une colonne
GENERATED ALWAYS. Laissez la base la générer.
Bonnes pratiques et sécurité
- Un rôle propriétaire par application, distinct des rôles qui serviront à lire ou écrire au quotidien (TP06) : le compte utilisé par l’application ne doit pas pouvoir supprimer les tables.
- Jamais de mot de passe en clair dans une commande SQL tapée au clavier : préférez
createuser -Pou la méta-commande\password, qui évitent les historiques et les journaux. - Posez les règles de gestion dans le schéma (types précis,
NOT NULL,CHECK, clés étrangères) plutôt que de compter sur chaque application. - Conservez le script de création du schéma dans un dépôt Git : il documente la base et permet de la reconstruire.
Questions de contrôle
1. Pourquoi parc_admin peut-il créer des tables dans le schéma public de la base parc ?
Voir la réponse
Depuis PostgreSQL 15, le schéma public appartient à pg_database_owner, c’est-à-dire au propriétaire de la base, ici parc_admin. Les autres rôles n’ont que le droit USAGE sur ce schéma.
2. Que garantit la clé étrangère site_id REFERENCES sites(id) ?
Voir la réponse
Qu’un équipement ne peut référencer qu’un site existant, et qu’un site encore référencé ne peut pas être supprimé (comportement par défaut, sans ON DELETE CASCADE).
3. Quel avantage le type inet apporte-t-il par rapport à text pour une adresse IP ?
Voir la réponse
Il refuse les adresses invalides et offre des opérateurs réseau, par exemple « est contenu dans ce sous-réseau », utilisés au TP04.
Pour aller plus loin
Versionnez le script de création du schéma avec Git, comme expliqué dans notre article Git, le gardien des versions de code. Dans une grande organisation, les évolutions de schéma passent par des scripts de migration relus et appliqués par la chaîne de déploiement, jamais par des modifications manuelles en production. Conservez la base parc : le TP03 la remplit.
// formation sur mesure
Former votre équipe sur ce sujet ?
Azur Secure anime des sessions intra-entreprise, à distance ou dans vos locaux sur la Côte d'Azur, adaptées au niveau de vos équipes.