Mise à jour du 10/12/2021.
PostgreSQL hérite de Ingres (son nom en provient : post ingres qui par contraction à donné postgres puis postgreSQL). Cela signifie que son origine remonte à 1985 et il en est actuellement à sa 13ème version majeure. Outre sa résilience et sa très grande stabilité c'est son moteur de recherche plein texte qui a conditionné notre choix. Nous le préférons largement à mySQL (Maria DB en fait) qui, intégré à Oracle, est nettement moins performant et cohérent.
Dans notre système de messagerie, la base de données est utilisée pour stocker :
L'installation de PostgreSQL en version 13 à partir de la distribution Debian 11 est assez simple grâce au gestionnaire de paquets via la commande apt. Il suffit de taper apt install postgresql. Cette commande installe, configure et lance PostgreSQL.
Laissez l'installation se dérouler. Une fois celle-ci achevée, vérifiez que le serveur est actif, en tapant la commande :
systemctl status postgresql
Ensuite depuis un compte root connectez vous en tant que compte postgres en tapant la commande :
su - postgres
Lancez alors la commande psql -V et vérifiez que le client console est en version 13. Connectez vous à la base de données via la commande psql -p 5432. Vous êtes maintenant connecté en tant que super-utilisateur à la base de données. Tapez la commande SELECT version(); et vérifiez que le serveur est en version 13. Pour quitter le client console, tapez \q.
Commencez par arrêter le serveur avec la commande :
systemctl stop postgresql
Avec votre éditeur de texte console préféré, ouvrez le fichier /etc/postgresql/13/main/postgresql.conf. ce fichier est le fichier de configuration principal du moteur de base de données. Notez alors le chemin sur lequel se trouvent les fichiers de la base de données (propriété data_directory). Notez également le fichier associé à la propriété hba_filecar ce fichier gère les adresses IP autorisées à la connexion et leur mode (en principe /etc/postgresql/13/main/pg_hba.conf). Par défaut, le répertoire des données devrait être /var/lib/postgresql/13/main. Selon vos besoins et votre configuration, vous pouvez changer les valeurs de ces propriétés.
Dans notre usage (système de messagerie) le serveur écoute uniquement des requêtes locales sur le port TCP 5432. En principe, nous recommandons de modifier ce port. Toutefois, dans une utilisation avec postfix, nous n'avons pas trouvé le moyen de modifier le port d'écoute du serveur de données et pour éviter toute attaque directe sur la base de données, nous interdisons tout simplement l'accès à ce port via le pare-feu du serveur.
Si vous tenez absolument à le modifier, cherchez la propriété port et affectez-lui le numéro de port souhaité. Attention sous Linux, ce numéro doit être supérieur à 1024 (ports réservés à un accès avec les droits root) et inférieur à 65536. Vous ne pouvez pas non plus affecter un numéro de port utilisé par une autre application car la première application qui utilisera ce port fera échouer le lancement des autres accédant à ce même port. Enfin, si votre serveur est situé derrière le pare-feu d'un fournisseur, vous devez tenir compte des ports TCP ouverts imposés par ce dernier et éviter d'affecter à PostgreSQL un de ceux-ci.
Pour autoriser l'écoute à distance via l'Internet (ce que nous ne faisons pas dans notre cas pour des raison e sécurité), il est recommandé de modifier la propriété listen. Remplacez simplement la valeur localhost par une étoile entre apostrophes ('*') ou par l'adresse ou les adresses IP sur lesquelles les "clients" seront autorisés à se connecter. L'étoile permet l'écoute sur toutes les adresses réseau de la machine que ces adresses soient liées à une interface physique ou virtuelles.
Cette section n'a pas d'intérêt si vous ne devez pas administrer à distance le serveur de bases de données. C'est notre cas car nous administrons les bases de données via un terminal protégé par notre fournisseur.
Dans le cadre strict du système de messagerie, seules des connexions locales sont utilisées et donc autorisées. Si vous devez administrer à distance la base de données, il est recommandé que la configuration de PostgreSQL restreigne les adresses IP autorisées à se connecter. Il faut commencer par recenser les adresses IP des consoles d'administration distantes puis les reporter dans le fichier /etc/postgresql/13/main/pg_hba.conf. Chaque ligne est de la forme
host all all x.y.z.t/32 md5
Notez qu'il est possible de fixer les bases de données administrables ainsi que les utilisateurs autorisés à s'y connecter.
Une fois ces modifications effectuées, relancez le serveur via la commande :
systemctl start postgresql
puis vérifiez via une des deux commandes ci-dessous :
netstat -lpn | grep {nouveau N° de port}
ss -lpn | grep {nouveau N° de port}que l'écoute du serveur se fait effectivement sur le port dédié au serveur de base de données.
DBA est l'acronyme de DataBase Administrator ou "administrateur de base de données". Il s'agit d'un utilisateur qui a tout pouvoir sur l'ensemble des bases de données. Son mot de passe est donc critique et doit être modifié.
L'unique compte DBA à l'installation du système est postgres. Ce qui rend les choses difficiles à comprendre c'est que l'on utilise 3 fois le terme 'postgres' pour désigner 3 choses différentes :
Ce n'est effectivement pas très judicieux... Voici la procédure pour modifier le mot de passe du compte DBA postgres depuis un compte root :
su - postgres psql postgres \password postgres
La première commande vous permet de vous connecter en tant que l'utilisateur linux postgres sans avoir à débloquer et fournir un mot de passe car vous disposez des droits du compte linux root.
La seconde commande lance le programme client console de PostgreSQL. Ce programme permet de se connecter à une base de données afin de la modifier ou de l'interroger. L'invite de commande attend soit des instructions SQL, soit des commandes spécifiques. Par défaut il se connecte à la base postgres et utilise le compte de base de données postgres sans avoir besoin de mot de passe (grâce au compte linux postgres). En effet, sans mention du compte de base de données, psql interprète le compte Linux comme étant également le nom de compte de base de données à utiliser. Si c'est le cas, le mot de passe n'est pas exigé puisqu'une authentification a déjà été faite par Linux avant d'ouvrir la session.
La 3ème ligne est une commande interne à psql. Elle demande à modifier le mot de passe du compte de base de données (pas celui de Linux) nommé postgres. Cette dernière commande vous demande de confirmer le nouveau mot de passe. Mémorisez le précieusement sinon vous aurez de sérieuses difficultés à administrer votre serveur. Nous créerons par la suite cet autre compte de base de données pour notre système de messagerie. A cette occasion il nous faudra nous connecter en tant que DBA.
Pour des raisons de sécurité, nous vous recommandons de créer un second compte DBA et de désactiver le compte DBA postgres en lui interdisant de se connecter (NOLOGIN). Ensuite, c'est avec ce nouveau compte que vous devrez effectuer les opérations d'administration.
Le compte linux postgres est lié à la base de données postgres qui peut être vue comme une méta-base du moteur de bases de données. Du point de vue sécurité, l'accès à cette base est donc critique. A l'issue de l'installation du paquet Debian de postgreSQL, les simples commandes
su - postgres psql
donnent un accès complet au moteur de base de données. Cela signifie qu'une attaque du système réussie équivaut à prendre la main sur la base de données. Le risque est trop grand. Il faut désactiver à la fois le compte linux postgreset le "role" postgres en base de données avec l'idée que cette opération doit pouvoir être réversible.
Pour cela, vous devez opérer depuis un autre compte DBA. Il suffit d'interdire au role postgres de pouvoir se connecter :
ALTER ROLE postgres NOLOGIN
Bien entendu l'instruction SQL duale est
ALTER ROLE postgres LOGIN
Nous allons ici faire deux choses :
En voici les commandes :
usermod -s /usr/sbin/nologin postgres usermod -L postgres
Les commandes duales sont :
usermod -s /bin/bash postgres usermod -U postgres
On peut vérifier son blocage via la commande passwd --status postgres. Vous devriez obtenir quelque chose du type : postgres L 02/13/2021 0 99999 7 -1. Le point important est le 2ème champ : la lettre L pour Locked (verrouillé).
Nous voilà prêt à stocker des données et à administrer le serveur de données ! Si nous décidons d'administrer à distance notre base de données, il est recommandé soit d'utiliser le chiffrement SSL, soit d'utiliser un tunnel SSH. Pour éviter une manipulation toujours délicate des certificats (à renouveler) nous recommandons d'utiliser le tunnel SSH.
Rédaction par Jean-Marie Piatte (1983-2021)