Accueil » Formations » Fondamentaux en informatique » Bases de données et SQL avec PostgreSQL » TP06 – Droits d’accès, rôles applicatifs et connexions distantes

TP06 – Droits d’accès, rôles applicatifs et connexions distantes

← Bases de données et SQL avec PostgreSQL

// travaux pratiques · Fondamentaux en informatique

TP06 - Droits d’accès, rôles applicatifs et connexions distantes

  • TP 6 / 7
  • 7 étapes
  • ≈ 60 min
  • Accès libre

Objectif : appliquer le principe du moindre privilège avec un rôle de groupe en lecture seule et un compte applicatif limité, puis ouvrir l’accès réseau au seul poste autorisé, en connexion chiffrée et authentifiée par SCRAM.

Contexte

Une base de données sert rarement une seule personne : un outil de tableaux de bord lit les données, une application en écrit, un administrateur fait évoluer le schéma. Donner à tous le compte propriétaire, ou pire le superutilisateur, est la configuration la plus répandue… et la plus dangereuse : une injection SQL dans l’application ou un mot de passe divulgué donne alors un accès total. Dans une petite structure, deux ou trois rôles bien pensés suffisent ; dans un grand groupe, les mêmes principes s’appliquent à grande échelle, avec des rôles de groupe rattachés à l’annuaire et des règles d’accès déployées automatiquement.

À la fin de ce TP, vous saurez distribuer des privilèges précis, les vérifier, et autoriser une connexion distante sans exposer l’instance à tout le réseau.

Prérequis : TP05 terminé ; VM admin01 (192.168.56.20) joignable depuis pg01 (192.168.56.10). Les commandes SQL des étapes 1 à 3 s’exécutent en tant que postgres dans la base parc : sudo -u postgres psql -d parc.

Durée estimée : 60 minutes.

Actions à réaliser

1. Fermer l’accès par défaut à la base

Par défaut, PostgreSQL accorde au pseudo-rôle PUBLIC (tous les rôles) le droit de se connecter à chaque base. On le retire pour que seuls les rôles explicitement autorisés puissent ouvrir une session sur parc ; son propriétaire conserve tous ses droits.

REVOKE CONNECT ON DATABASE parc FROM PUBLIC;
\l parc

Résultat attendu : REVOKE. Avant la commande, la colonne « Access privileges » de \l parc était vide (droits par défaut implicites) ; elle affiche désormais =T/parc_admin (PUBLIC ne garde que le droit de créer des tables temporaires) et parc_admin=CTc/parc_admin (tous les droits du propriétaire, dont c pour CONNECT).

2. Créer un rôle de groupe en lecture seule

On attribue les droits à un rôle de groupe (sans connexion), puis on y rattache des comptes : ajouter ou retirer une personne ne demande plus de toucher aux privilèges. ALTER DEFAULT PRIVILEGES fait en sorte que les tables que parc_admin créera plus tard soient, elles aussi, lisibles par le groupe ; sans cette commande, le GRANT ne vaut que pour les objets existants.

CREATE ROLE parc_lecture NOLOGIN;
GRANT CONNECT ON DATABASE parc TO parc_lecture;
GRANT USAGE ON SCHEMA public TO parc_lecture;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO parc_lecture;
ALTER DEFAULT PRIVILEGES FOR ROLE parc_admin IN SCHEMA public
  GRANT SELECT ON TABLES TO parc_lecture;
CREATE ROLE lecteur_bi LOGIN IN ROLE parc_lecture;
\password lecteur_bi

Résultat attendu : une suite de CREATE ROLE, GRANT et ALTER DEFAULT PRIVILEGES sans erreur. \du lecteur_bi montre l’appartenance au groupe parc_lecture. GRANT … ON ALL TABLES couvre aussi la vue v_interventions.

3. Créer un compte applicatif limité

Le compte de l’application ne reçoit que ce dont elle a besoin : lire les sites et équipements, lire et ajouter des interventions. Il ne peut ni modifier ni supprimer, et le nombre de connexions simultanées est plafonné pour qu’un défaut de l’application ne sature pas l’instance. Avec une colonne identité, le droit INSERT sur la table suffit : aucun droit séparé sur la séquence n’est nécessaire, contrairement au pseudo-type serial.

CREATE ROLE app_parc LOGIN CONNECTION LIMIT 10;
\password app_parc
GRANT CONNECT ON DATABASE parc TO app_parc;
GRANT SELECT ON sites, equipements TO app_parc;
GRANT SELECT, INSERT ON interventions TO app_parc;
\dp interventions

Résultat attendu : \dp interventions affiche dans « Access privileges » une entrée parc_admin=arwdDxtm/parc_admin (tous les droits du propriétaire ; la lettre m n’apparaît qu’à partir de la version 17), parc_lecture=r/parc_admin et app_parc=ar/parc_admin (a pour INSERT, r pour SELECT).

4. Tester les droits en local

On vérifie à la fois ce qui doit marcher et ce qui doit échouer : un test de sécurité qui ne prouve que les accès autorisés ne prouve rien. Quittez psql et exécutez, depuis votre compte (le mot de passe est demandé) :

psql -h localhost -U lecteur_bi -d parc -c "SELECT count(*) FROM v_interventions;"
psql -h localhost -U lecteur_bi -d parc -c "DELETE FROM sites;"
psql -h localhost -U app_parc -d parc -c "INSERT INTO interventions (equipement_id, technicien, description, duree_min) VALUES (3, 'app', 'Test du compte applicatif', 10) RETURNING id;"
psql -h localhost -U app_parc -d parc -c "UPDATE interventions SET duree_min = 1;"

Résultat attendu : le comptage réussit ; le DELETE échoue avec « permission denied for table sites » ; l’insertion réussit et renvoie un identifiant ; l’UPDATE échoue avec « permission denied for table interventions ».

5. Faire écouter le serveur sur l’interface du laboratoire

ALTER SYSTEM écrit le paramètre dans postgresql.auto.conf, qui est lu après postgresql.conf. On ajoute uniquement l’adresse de l’interface du laboratoire, jamais * sans raison. Ce paramètre n’est pris en compte qu’au redémarrage.

sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = 'localhost,192.168.56.10';"
sudo systemctl restart postgresql@17-main
sudo ss -ltnp | grep 5432
sudo -u postgres psql -c "SHOW ssl;"

Résultat attendu : ss montre désormais une écoute sur 192.168.56.10:5432 en plus des adresses de bouclage, et SHOW ssl renvoie on (configuration par défaut de Debian, avec un certificat autosigné). Si la valeur est off, remplacez hostssl par host à l’étape suivante et notez le point pour la mise en production.

6. Autoriser le seul poste admin01 dans pg_hba.conf

On ajoute une règle précise : type hostssl (connexion TCP chiffrée obligatoire), base parc, deux comptes nommés, une adresse source unique et la méthode scram-sha-256. Ajoutez la ligne à la fin de /etc/postgresql/17/main/pg_hba.conf, puis rechargez : pour ce fichier, un rechargement suffit.

echo 'hostssl parc lecteur_bi,app_parc 192.168.56.20/32 scram-sha-256' | sudo tee -a /etc/postgresql/17/main/pg_hba.conf
sudo -u postgres psql -c "SELECT pg_reload_conf();"
sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules WHERE address = '192.168.56.20';"

Résultat attendu : pg_reload_conf renvoie t, et la vue pg_hba_file_rules affiche la nouvelle règle avec une colonne error vide. Si un pare-feu local est actif sur pg01, autorisez le port 5432/TCP depuis 192.168.56.20 uniquement (avec ufw : sudo ufw allow from 192.168.56.20 to any port 5432 proto tcp).

7. Se connecter depuis admin01 et contrôler les sessions

Sur admin01, le paquet postgresql-client fournit psql sans installer de serveur. L’option sslmode=require impose le chiffrement côté client.

sudo apt install -y postgresql-client
psql "host=192.168.56.10 dbname=parc user=lecteur_bi sslmode=require" -c "SELECT current_user, inet_client_addr();"
psql "host=192.168.56.10 dbname=parc user=parc_admin sslmode=require" -c "SELECT 1;"

Pendant qu’une session est ouverte depuis admin01, contrôlez-la sur pg01 :

sudo -u postgres psql -c "SELECT a.usename, a.client_addr, s.ssl, s.version FROM pg_stat_activity a JOIN pg_stat_ssl s ON s.pid = a.pid WHERE a.datname = 'parc';"

Résultat attendu : la première connexion renvoie lecteur_bi et 192.168.56.20 ; la seconde est refusée avec « no pg_hba.conf entry for host “192.168.56.20”, user “parc_admin”, database “parc” », car le compte propriétaire n’est pas autorisé à distance. La requête de contrôle montre la session avec ssl à t et la version TLS négociée.

Vérification

Le TP est réussi si : lecteur_bi lit mais ne modifie pas ; app_parc insère mais ne met pas à jour ; seuls ces deux comptes peuvent se connecter depuis 192.168.56.20 et en TLS ; parc_admin et postgres sont refusés à distance ; pg_hba_file_rules ne signale aucune erreur.

Problèmes fréquents

  • « Connection refused » depuis admin01 : le serveur n’écoute pas sur 192.168.56.10 (redémarrage oublié après ALTER SYSTEM) ou un pare-feu bloque le port. Vérifiez avec ss -ltnp sur pg01.
  • « no pg_hba.conf entry for host … no encryption » : le client tente une connexion non chiffrée alors que la règle est hostssl. Ajoutez sslmode=require, ou vérifiez que ssl est bien à on.
  • « permission denied for database parc » : le rôle n’a pas reçu CONNECT depuis le REVOKE … FROM PUBLIC. Accordez-le au rôle ou à son groupe.
  • « permission denied for table … » sur une table créée après le TP : les privilèges par défaut ont été définis par un autre rôle que celui qui a créé la table. ALTER DEFAULT PRIVILEGES doit viser le rôle créateur (FOR ROLE parc_admin).

Bonnes pratiques et sécurité

  • Un compte par usage (application, outil de reporting, personne), jamais de compte partagé : les journaux et pg_stat_activity doivent permettre d’identifier qui fait quoi.
  • Méthode scram-sha-256 partout : md5 est dépréciée depuis PostgreSQL 18 et sera retirée. Pas de méthode trust en dehors d’un laboratoire isolé.
  • En production, utilisez un certificat émis par votre autorité interne et sslmode=verify-full côté client, qui vérifie l’identité du serveur ; require chiffre sans vérifier.
  • Ne publiez jamais le port 5432 sur Internet : passez par un VPN ou un bastion, et limitez les adresses sources dans pg_hba.conf et dans le pare-feu.

Questions de contrôle

1. Pourquoi retirer CONNECT à PUBLIC ?

Voir la réponse

Parce que, par défaut, tout rôle peut se connecter à toute base. Le retrait impose une autorisation explicite par base.

2. Quel fichier faut-il recharger et quel paramètre impose un redémarrage ?

Voir la réponse

Une modification de pg_hba.conf se prend en compte par rechargement (pg_reload_conf()) ; listen_addresses nécessite un redémarrage de l’instance.

3. À quoi sert ALTER DEFAULT PRIVILEGES ?

Voir la réponse

À accorder automatiquement des droits sur les objets qui seront créés plus tard par un rôle donné ; un GRANT … ON ALL TABLES ne concerne que les tables existantes.

Pour aller plus loin

Pour joindre la base depuis un poste distant sans ouvrir le port 5432, un tunnel SSH est une solution simple : voir le cours SSH : connexion à distance sécurisée avec OpenSSH. Dans un grand groupe, on rattache souvent les rôles de groupe à l’annuaire (méthode ldap ou Kerberos via gss dans pg_hba.conf) et on active la sécurité au niveau des lignes (ROW LEVEL SECURITY) pour cloisonner les données par entité. Conservez la configuration : le TP07 sauvegarde la base et ses rôles.

// 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.

Demander un programme
Défiler vers le haut