Postgresql

Postgresql est un logiciel de gestion de base de donnée. Considéré plus sécurisé (par défaut) et activement maintenu par rapport à MariaDB. Tous les services ayant besoin de base de donnée utilisent ce service (sauf le service mail).

Configuration actuelle

Postgresql est géré depuis le serveur computing et n'est accessible que part les autres serveurs du réseau, et seulement pour les bases de données nécessaire (et pour les utilisateurs associés).

Les services suivants utilisent ce logiciel:

• Keycloak
• Nextcloud
• Mediawiki
• Synapse
• Forgejo

Les modifications apportées au fichier de configuration /etc/postgresql/{version}/main/postgresql.conf est l'ajout de la ligne

listen_addresses = '*'

Sur le fichier de configuration /etc/postgresql/main/{version}/main/pg_hba.conf, des lignes ont été ajoutées pour autoriser les connexions vers d'autres serveurs (voir la section)

Procédures

Ajout d'une base de donnée et d'un utilisateur associé

En tant qu'utilisateur postgres, lancer l'interface en ligne de commande de Postgresql avec la commande psql. Exécuter ensuite les commandes suivantes tour à tour, dans l'ordre, en remplaçant {database_name} par le nom de la base de donnée à créer, {user_name} par le nom de l'utilisateur associé à créer et {user_password} par le mot de passe de l'utilisateur Postgresql du nouvel utilisateur qui sera créé:

• CREATE DATABASE {database_name};
• CREATE USER {user_name} WITH ENCRYPTED PASSWORD '{user_password}';
• GRANT ALL PRIVILEGES ON DATABASE {database_name} TO {user_name};
• \c {database_name} postgres;
• GRANT ALL ON SCHEMA public TO {user_name};
• \q

Ajout d'une connexion avec un service sur un autre serveur

Dans le fichier /etc/postgresql/{version}/main/pg_hba.conf, ajouter la ligne:

hostssl {database_name} {user_name} {ipv4}/32 scram-sha-256
hostssl {database_name} {user_name} {ipv6}/128 scram-sha-256

Sauvegarde d'une base de donnée

En tant qu'utilisateur postgres, lancer la commande pg_dump -U postgres -d {database_name} -F tar -f {file_name}.tar pour créer le fichier {file_name}.tar contenant une copie de la base de donnée reconstituable (voir section).

Restauration d'une base de donnée

En tant qu'utilisateur postgres, lancer la commande pg_restore -U postgres -d {database_name} {file_path} avec {file_path} le chemin du fichier de backup de la base de donnée.

Installation

Sur un serveur sous debian, exécuter la commande apt install postgresql, puis ajouter la ligne listen_addresses = '*' au fichier /etc/postgresql/{version}/main/postgresql.conf.

Ensuite, ajouter une exception dans le pare-feu pour autoriser les connexion entrante sur le port géré par Postgresql avec firewall-cmd --zone=public --add-service=postgresql --permanent puis firewall-cmd --reload.

Mise à jour

Postgresql est installé depuis les dépôts officiel de debian, la mise à jour s'effectue donc via aptitude, comme les paquets systèmes (avec les commandes apt update puis apt upgrade en tant qu'utilisateur root).