Accéder à sa base de données PostgreSQL depuis QGis ou pgAdmin de manière sécurisée

Comment interconnecter ses outils de travail sans mettre en péril la sécurité du système informatique ? L’objectif de cet article est de présenter une manière sécurisée de connecter QGis ou pgAdmin à une base de données PostgreSQL, afin d’atteindre le meilleur compromis entre praticité et sécurité.

Le blog Makina-corpus

Comment interconnecter ses outils de travail sans mettre en péril la sécurité du système informatique ? L’objectif de cet article est de présenter une manière sécurisée de connecter QGis ou pgAdmin à une base de données PostgreSQL, afin d’atteindre le meilleur compromis entre praticité et sécurité.

Les partenaires de Makina Corpus utilisent plusieurs outils informatiques dans la valorisation de leurs territoires naturels. QGis, pgAdmin, PostGis représentent une partie de la palette de travail classique des systèmes d’information géographique. Il est souvent utile de pouvoir interfacer ces outils entre eux, notamment pour pouvoir travailler sur une même base de données depuis les différents logiciels.

Pour autant, il est important de savoir interconnecter ces outils sans mettre en péril la sécurité du système informatique. En effet, autoriser les connexions à une base de données depuis n’importe quelle machine distante représente une source de vulnérabilité conséquente. Non seulement la base de données en question peut parfois contenir des données sensibles (mots de passe, comptes utilisateurs) mais même si ce n’est pas le cas, des attaquants capables d’effectuer une élévation des privilèges peuvent commencer par pénétrer dans la base de données pour ensuite gagner un contrôle complet sur le système d’information. L’objectif de cet article est de présenter une manière sécurisée de connecter QGis ou pgAdmin à une base de données PostgreSQL, afin d’atteindre le meilleur compromis entre praticité et sécurité.

Préambule : Une connexion à la base de données, c’est quoi ?

Pour se connecter à une base de données PostgreSQL, il faut :

  • la localiser grâce à :
    • une adresse IP (ou un nom de domaine),
    • un numéro de port (souvent 5432),
    • un nom de base de données,
  • puis s’y authentifier grâce à :
    • un nom d’utilisateur,
    • un mot de passe.

1 - Les connexions locales

Avec Postgres, un système de type “firewall”, configurable et activé par défaut, empêche les connexions depuis l’extérieur (réseau internet). Utilisons l’exemple de l’application Geotrek, déployée sur la même machine que sa base de données, qui se connecte via l’adresse 127.0.0.1 (représentant la machine locale) et le port adéquat (souvent 5432). C’est l’architecture classique d’une application Django.

Les connexions locales

Ainsi, sur la machine locale, une instance de QGis peut se connecter à la même base de données sans aucun problème et sans modification de la configuration.

Les connexions locales

Les connexions locales sont sécurisées et sont les seules acceptées par défaut. Notre objectif est de conserver l’efficacité de ce firewall tout en pouvant se connecter depuis l’extérieur.

2 - Les connexions distantes

Pour pouvoir connecter une instance QGis à une base de données déployée sur une machine distante, on pourrait choisir de modifier la configuration du firewall PostgreSQL afin d’ouvrir les connexions à travers le réseau.

Le firewall de PostreSQL se configure via le fichier pg_hba.conf. Ce dernier permet de configurer avec précision les accès : autoriser les connexions depuis certains réseaux seulement, depuis certaines machines seulement (par adresse IP), ou encore pour certains utilisateurs seulement.

Les connexions distantes

Cette solution présente plusieurs inconvénients :

  • Elle peut entrer en conflit avec d’autres firewalls présents sur le système d’information. Il faut alors passer du temps à analyser les autres mécanismes de sécurité présents, et éventuellement les re-configurer eux aussi.
  • Elle nécessite de redémarrer le serveur PostgreSQL, ce qui cause une interruption temporaire du service.
  • Elle nous fait prendre le risque de créer une configuration trop permissive. En effet, la configuration la plus simple et rapide pour résoudre notre problème consiste à autoriser toutes les connexions depuis l’extérieur. Cela expose la base de données, et donc le système d’informations, aux attaquants extérieurs.
Serveur PostgreSQL. Connexions distantes

C'est pourquoi cet article ne vous expliquera pas comment faire cela. A la place, nous allons simuler une connexion locale en utilisant le mécanisme de sécurité SSH.

Ici, c’est le protocole SSH qui va se charger de faire la connexion entre les deux machines. Ce protocole répond à nos impératifs de sécurité en créant un tunnel de connexion (si vous déjà entendu parler de VPN, notez que le tunnel SSH est un moyen de les construire). Dans ces conditions, depuis le point de vue de la base de données, tout se passe comme si la connexion était locale, et elle est donc autorisée sans modification du firewall.

Protocole SSH. Connexions distantes.

2.1 Prérequis

  • L’utilisateur de QGis sur la machine B doit disposer d’un accès SSH à la machine A :
    • user@machine_A
    • mot de passe - ou clé SSH (recommandé)
  • La machine B doit disposer d’un client SSH :
    • Linux : client SSH classique,
    • Windows < 10 : Putty (voir liens dans les références),
    • Windows 10: client SSH à activer (voir liens dans les références).

2.2 - Trouver les informations de connexion

L’utilisateur sur la machine B doit disposer des 5 informations de connexion listées en introduction. Dans le cas de Geotrek, il peut utiliser le client SSH de son choix pour aller récupérer les informations de connexion locale dans le fichier /opt/geotrek-admin/var/conf/env grâce aux commandes ci-dessous. Sinon, il convient de les demander à son administrateur système et/ou la personne qui a installé la base de données.

SSH. Trouver les informations de connexion

Bien prendre note de ces informations, et se déconnecter.

SSH Exit - Trouver les informations de connexion

2.3 - Ouvrir le tunnel SSH

Pour ouvrir le tunnel SSH, il faut utiliser les informations récupérées dans la partie précédente, et choisir un numéro de port libre sur la machine locale, par exemple le port 5665.

Open Tunnel SSH

Dans notre exemple cela donne :

Open Tunnel SSH

Attention , si le message bind [127.0.0.1]:5665: Address already in use apparaît, alors il faut essayer avec un autre numéro de port, par exemple 5666, ainsi de suite jusqu'à la disparition de ce message.

Une fois le tunnel ouvert, il faut s’assurer de le conserver ouvert le temps de la session de travail (ne pas fermer la fenêtre du client SSH, entre autres). Grâce à ce tunnel, toute connexion locale au port 5665 (adresse 127.0.0.1) sera redirigée de manière sécurisée vers le port 5432 de la machine distante.

2.4 - Connecter son outil de travail à la base de données

Voyons maintenant comment utiliser le tunnel SSH pour atteindre la base de données depuis nos différents outils.

2.4.A - QGis

  • Créer une nouvelle connexion :
QGis Créer une nouvelle connexion
  • Entrer les informations de connexion locale. Selon notre exemple cela donne (pour rappel, ici le mot de passe est dsrtT51Dzs3R4U72a2):
QGis connexion locale
  • Cliquer sur “Convert to configuration” pour enregistrer le nom d’utilisateur et le mot de passe. Ensuite cliquer sur “OK”.
QGis Convert to configuration
  • La base de données doit désormais apparaître dans la catégorie “PostGIS” du menu “Navigateur” à gauche.

2.4.B - pgAdmin 4

  • Créer une nouvelle connexion :
pgAdmin 4
  • Dans l’onglet “Général”, choisir un nom pour la connexion.
pgAdmin connexion
  • Dans l’onglet “Connexion”, entrer les informations de connexion locale. Selon notre exemple cela donne (pour rappel, ici le mot de passe est dsrtT51Dzs3R4U72a2):
pgAdmin connexion
  • Cliquer sur “Save”. On peut remarquer qu’il existe un onglet “SSH Tunnel”, mais ici on ne s’en sert pas car on a déjà ouvert le tunnel SSH manuellement dans la partie 2.3.
  • La base de données doit désormais apparaître dans la catégorie “Servers” du menu “Navigateur” à gauche.

Conclusion

Le protocole SSH permet d’interconnecter rapidement les outils de travail des professionnels de la cartographie, de manière sécurisée et robuste. Il a l’avantage d’être très répandu et s’utilise de manière indifférenciée sur Windows et Linux (depuis Windows 10). A mon sens, il est important de prendre un peu de temps pour s’y intéresser car la protection de nos environnements de travail et la prévention des risques représentent des enjeux forts de nos performances futures.

Références

Tutoriels utiles

Pour aller plus loin

Contenus corrélés

Formations associées

Outils et bases de données

PostgreSQL

Nantes Du 02 au 04 novembre 2021

Voir la formation

SIG/Webmapping

PostGIS

A distance (foad) Du 13 au 17 décembre 2021 (complet)

Voir la formation

Actualités en lien

03/07/2020

Webmapping : comparaison des serveurs de tuiles vectorielles depuis Postgres / PostGIS

Un ensemble de serveurs de tuiles vectorielles basés sur la fonction ST_AsMVT() de PostGIS sont disponibles. Makina Corpus vous propose un tour d’horizon des spécificités des différentes solutions.

Voir l'article
28/01/2020

Paralléliser des requêtes avec PostgreSQL

PostgreSQL permet de découper les requêtes pour en exécuter des parties en parallèle. Il faut toutefois en connaître les concepts pour pouvoir en bénéficier au mieux et ne pas empêcher le planificateur de requêtes de le faire.

Voir l'article
29/08/2019

Améliorez votre SQL : utilisez des index filtrés

L'indexation d'une base de données est un vaste sujet, dans cet article nous examinerons une possibilité offerte par PostgreSQL dans les index et le filtrage de l'index pour qu'il ne s'applique pas à toute la table.

Voir l'article

Inscription à la newsletter

Nous vous avons convaincus