Audit d'accès et journalisation des actions des utilisateurs

Imagine que ta base de données, c’est un club super select avec des règles d’entrée hyper strictes. Seuls les membres peuvent entrer, mais on veut savoir qui est venu, quand, combien de temps il est resté, et ce qu’il a fait. C’est exactement à ça que sert l’audit dans PostgreSQL. Grâce à ça, tu peux :

1. Suivre les actions des utilisateurs. Par exemple, qui a exécuté une requête importante et à quel moment.

2. Détecter une activité suspecte. Ça peut être quelqu’un qui essaie de lire des données auxquelles il ne devrait pas avoir accès.

3. Respecter les exigences légales et les standards. Dans beaucoup de secteurs (genre finance ou santé), il faut tenir un audit détaillé des actions des utilisateurs.

4. Comprendre le système de l’intérieur. La journalisation aide à voir quelles requêtes sont les plus fréquentes, où ça coince et comment optimiser les perfs.

Configurer les paramètres de journalisation

PostgreSQL te permet de configurer l’audit des actions via des paramètres de config comme log_statement et log_connections. On va voir ça de plus près.

1. log_statement : qu’est-ce qu’on enregistre ?

Le paramètre log_statement définit quelles requêtes SQL seront écrites dans les logs. C’est super utile pour comprendre ce qui se passe dans le système.

Les valeurs possibles pour log_statement :

• none — on n’enregistre rien (parfait si tu veux vivre dangereusement).
• ddl — on log seulement les commandes qui modifient la structure de la base (genre CREATE TABLE).
• mod — on log les commandes qui modifient les données (INSERT, UPDATE, DELETE).
• all — on log absolument tout.

Exemple de config : pour changer ce paramètre, il faut éditer le fichier postgresql.conf :

# On configure la journalisation de toutes les requêtes SQL
log_statement = 'all'

Après ça, sauvegarde les modifs et redémarre le serveur PostgreSQL :

pg_ctl reload

Maintenant, ton club va tout noter dans le journal, du mojito commandé aux pas de danse.

2. log_connections : qui entre dans le club ?

Le paramètre log_connections sert à écrire dans les logs chaque nouvelle connexion à la base. Il y a aussi le paramètre log_disconnections qui note quand une connexion se ferme.

Exemple de config : Encore une fois, on édite postgresql.conf :

# On log les connexions
log_connections = on

# On log les déconnexions
log_disconnections = on

À quoi ça sert ? Par exemple, tu pourras voir dans les logs que ton manager a essayé de se connecter pendant deux heures avec un mauvais mot de passe. Oui, à 3h du mat.

Analyse des logs : qu’est-ce qu’on peut trouver ?

Une fois que t’as configuré log_statement et log_connections, PostgreSQL commence à écrire les logs. Voilà à quoi peut ressembler un fichier log avec log_statement = 'mod' activé :

2023-11-01 12:45:01 UTC [12345] LOG:  connection authorized: user=admin database=university
2023-11-01 12:46:15 UTC [12345] STATEMENT:  INSERT INTO students (name, age) VALUES ('Alice', 22);
2023-11-01 12:47:30 UTC [12345] STATEMENT:  UPDATE students SET age = 23 WHERE name = 'Alice';
2023-11-01 12:48:45 UTC [12345] LOG:  disconnection: session time: 2:45 connection: 1/5

Points intéressants :

1. Qui s’est connecté : user=admin database=university — c’est notre boss.
2. Ce qu’il a fait : il a inséré une ligne avec le nom Alice et a mis à jour son âge.
3. Quand il est parti : 12:48:45, après deux minutes et demie d’activité.

Exemples pratiques : comment utiliser l’audit en vrai

Voyons quelques scénarios où l’audit et la journalisation peuvent vraiment servir.

Scénario 1 : suivi des modifications de données

Tu soupçonnes que quelqu’un modifie des enregistrements dans la table students. Voilà comment tu peux configurer l’audit :

1. Mets log_statement = 'mod' pour enregistrer toutes les requêtes de modif.
2. Analyse les logs :

cat /var/log/postgresql/postgresql.log | grep "UPDATE students"

Maintenant tu peux voir qui, quand et quelles modifs ont été faites.

Scénario 2 : détection de connexions suspectes

Si tu vois plein de connexions depuis différentes IP dans les logs, c’est peut-être louche. Pour analyser ça :

1. Regarde les logs de connexion (log_connections).
2. Filtre par adresse IP :

cat /var/log/postgresql/postgresql.log | grep "connection authorized"

Scénario 3 : analyse des perfs des requêtes

Tu veux piger pourquoi ton serveur rame ? Un moyen simple : active log_statement = 'all' pendant une courte période (genre une heure), récupère les logs et regarde où le serveur passe le plus de temps.

Best practices

Ne log pas tout n’importe comment. Configure log_statement pour n’enregistrer que les actions importantes — DDL et modifs de données.

Automatise l’analyse des logs. Utilise des scripts ou des outils pour lire et analyser les logs régulièrement, comme grep, awk ou même ELK Stack (Elasticsearch, Logstash, Kibana).

Gère la taille des logs. Mets en place la rotation des logs via les paramètres PostgreSQL ou les outils du système (genre logrotate sous Linux).

J’espère que tu te sens maintenant comme le shérif de ta base de données. La journalisation et l’audit, c’est pas juste pour la sécu, c’est aussi un super moyen de mieux comprendre ce qui se passe dans ton système. On verra comment utiliser tout ça dans des vrais projets !