Auditer les accès
Suivez qui peut voir quoi.
Auditer les accès est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.
Pourquoi auditer les accès
Savoir qui a accédé à quelles données et à quel moment est une pierre angulaire de la sécurité des bases de données. L'audit crée une piste fiable des événements afin de détecter les accès non autorisés, d'enquêter sur les incidents et de satisfaire aux exigences de conformité telles que GDPR, HIPAA ou SOC 2.
Dans cette leçon, vous apprendrez à concevoir des tables d'audit, à capturer automatiquement les événements d'accès avec des déclencheurs, à utiliser les fonctionnalités de journalisation intégrées de PostgreSQL et à interroger le journal d'audit pour répondre à la question : qui peut voir quoi ?
Concevoir une table de journal d'audit
La première étape consiste à créer une table dédiée qui enregistre chaque événement important. Un bon journal d'audit conserve le nom de la table, le type d'opération, les anciennes et nouvelles valeurs, l'utilisateur ayant effectué l'action et l'horodatage exact.
L'exemple ci-dessous crée une table audit_log polyvalente utilisant des colonnes JSONB pour stocker des instantanés de lignes — suffisamment flexible pour gérer n'importe quelle table sans modifier le schéma.
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
db_user TEXT NOT NULL DEFAULT current_user,
app_user TEXT,
table_name TEXT NOT NULL,
operation TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
row_id BIGINT,
old_data JSONB,
new_data JSONB
);Enregistrer l'utilisateur actuel
PostgreSQL fournit plusieurs fonctions intégrées permettant d'identifier l'auteur d'une requête. current_user renvoie le nom du rôle actif après tout SET ROLE. session_user renvoie toujours le rôle de connexion d'origine, quel que soit le changement de rôle.
Pour les applications qui utilisent un rôle de base de données partagé unique, mais transmettent un utilisateur au niveau de l'application via SET LOCAL app.current_user, vous pouvez lire ce paramètre avec current_setting().
-- Who is the database user right now?
SELECT current_user,
session_user;
-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;Écrire une fonction de déclencheur d'audit
Une fonction de déclencheur est le moyen le plus fiable de capturer les événements de modification des données, car elle se déclenche automatiquement — aucun code de l'application ne peut la contourner. La fonction ci-dessous journalise chaque INSERT, UPDATE et DELETE sur toute table à laquelle elle est attachée, en stockant les anciennes et nouvelles valeurs de ligne au format JSONB.
Remarquez l'utilisation de TG_TABLE_NAME (la table qui a déclenché le déclencheur) et de row_to_json() pour convertir les valeurs de ligne dans un format pouvant être stocké.
CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
INSERT INTO audit_log (
db_user,
app_user,
table_name,
operation,
row_id,
old_data,
new_data
) VALUES (
current_user,
current_setting('app.current_user', true),
TG_TABLE_NAME,
TG_OP,
COALESCE(NEW.id, OLD.id),
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
);
RETURN NULL;
END;
$$;Associer le déclencheur à une table
Une fois la fonction de déclencheur créée, vous l'associez à chaque table que vous souhaitez auditer à l'aide d'une instruction CREATE TRIGGER. L'utilisation de AFTER garantit que les données ont bien été écrites avant la création de l'entrée du journal. La clause FOR EACH ROW déclenche le déclencheur une fois par ligne modifiée.
Ici, le déclencheur est appliqué à une table hypothétique patients, afin d'enregistrer automatiquement chaque INSERT, UPDATE et DELETE.
CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();Auditer les requêtes SELECT
Les déclencheurs de modification des données ne capturent que les écritures. Pour auditer les accès en lecture, vous avez besoin d'une autre approche. Une possibilité consiste à utiliser un déclencheur au niveau de l'instruction AFTER SELECT (pris en charge dans PostgreSQL 14+ dans certains contextes). Une méthode plus courante consiste à consigner explicitement les lectures dans une fonction ou une vue qui encapsule la table sensible.
L'exemple ci-dessous encapsule une table sensible dans une fonction qui consigne chaque lecture avant de renvoyer les résultats.
CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
-- Log the read access
INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
VALUES (
current_user,
current_setting('app.current_user', true),
'patients',
'SELECT',
p_id
);
RETURN QUERY
SELECT * FROM patients WHERE id = p_id;
END;
$$;Journalisation intégrée de PostgreSQL
Le fichier postgresql.conf de PostgreSQL offre une journalisation puissante côté serveur qui ne nécessite aucun code d'application. Le paramétrage de log_min_duration_statement consigne toute requête dépassant un seuil. Le paramétrage de log_connections et log_disconnections enregistre les connexions et les déconnexions.
La requête ci-dessous utilise la vue système pg_stat_activity pour afficher les sessions actuellement actives — une forme légère de surveillance des accès en temps réel.
-- See who is currently connected and what they are running
SELECT pid,
usename AS db_user,
application_name,
client_addr,
state,
query_start,
LEFT(query, 80) AS current_query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start DESC;Interroger le journal d’audit
Un journal d’audit n’a de valeur que si vous pouvez l’interroger efficacement. Les questions courantes sont notamment les suivantes : quel utilisateur a accédé à un enregistrement le plus récemment, quelles modifications une ligne a-t-elle subies au fil du temps et combien de lectures de données sensibles ont eu lieu au cours des dernières 24 heures.
La requête ci-dessous recherche tous les utilisateurs qui ont accédé à un enregistrement patient spécifique, en commençant par le plus récent.
SELECT event_time,
db_user,
app_user,
operation,
old_data,
new_data
FROM audit_log
WHERE table_name = 'patients'
AND row_id = 42
ORDER BY event_time DESC
LIMIT 20;Détecter les schémas d’accès suspects
Une fois les données d’audit collectées, vous pouvez écrire des requêtes qui signalent les anomalies. Par exemple, un utilisateur qui lit soudainement bien plus de lignes que d’habitude, ou le même enregistrement sensible auquel on accède plusieurs fois sur une courte période, peut indiquer une tentative d’exfiltration de données.
La requête ci-dessous compte les événements SELECT pour chaque utilisateur de l’application au cours de la dernière heure et met en évidence toute personne ayant lu plus de 100 lignes.
SELECT app_user,
COUNT(*) AS records_accessed
FROM audit_log
WHERE operation = 'SELECT'
AND event_time >= now() - INTERVAL '1 hour'
GROUP BY app_user
HAVING COUNT(*) > 100
ORDER BY records_accessed DESC;Protéger le journal d’audit lui-même
Un journal d’audit qui peut être modifié n’est pas fiable. Vous devez le sécuriser afin que les utilisateurs ordinaires et les rôles de l’application ne puissent pas supprimer ou mettre à jour les lignes. L’approche la plus sûre consiste à accorder uniquement INSERT au rôle de l’application et à réserver SELECT à un rôle d’auditeur dédié.
Vous pouvez également imposer l’immutabilité à l’aide d’un déclencheur qui lève une exception si quiconque tente de modifier ou de supprimer une ligne d’audit.
-- Only the app role may insert; nobody may update or delete
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT INSERT ON audit_log TO app_role;
GRANT SELECT ON audit_log TO auditor_role;
-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'audit_log rows are immutable';
RETURN NULL;
END;
$$;
CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();Auditer les décisions des politiques RLS
Lorsque la sécurité au niveau des lignes est active, PostgreSQL masque silencieusement les lignes au lieu de lever des erreurs. Il est donc difficile de savoir si un utilisateur a tenté de lire une ligne qu’il n’était pas autorisé à voir. Une technique consiste à ajouter une politique permissive qui insère toujours un enregistrement d’audit avant que la politique restrictive ne filtre les lignes.
La requête ci-dessous montre comment examiner les politiques RLS qui existent sur une table et les rôles auxquels elles s’appliquent.
-- View all RLS policies on the patients table
SELECT polname AS policy_name,
polcmd AS command,
polroles::TEXT AS applies_to,
polqual::TEXT AS using_expression,
polwithcheck::TEXT AS with_check_expression
FROM pg_policy
WHERE polrelid = 'patients'::REGCLASS
ORDER BY polname;Vérification des connaissances
Testez votre compréhension de l’audit des accès en SQL.
Récapitulatif de la leçon
Dans cette leçon, vous avez découvert comment construire un système complet d’audit des accès dans PostgreSQL :
- Table du journal d’audit — une table fondée sur JSONB qui enregistre qui a fait quoi et quand.
- Fonction de déclencheur — journalise automatiquement les événements INSERT, UPDATE et DELETE pour toute table associée à l’aide de
TG_TABLE_NAME,row_to_json()etcurrent_user. - Audit des lectures — encapsulez les tables sensibles dans des fonctions qui journalisent les événements SELECT avant de renvoyer les données.
- Surveillance intégrée —
pg_stat_activityaffiche les sessions en cours ; la journalisation côté serveur capture les requêtes sans modification du code. - Détection des anomalies — les requêtes d’agrégation sur le journal d’audit peuvent signaler un volume d’accès inhabituel.
- Immutabilité — révoquez UPDATE/DELETE sur la table d’audit et ajoutez un déclencheur bloquant pour empêcher toute altération.
Une piste d’audit bien conçue est votre outil le plus fiable pour répondre aux questions qui a accédé à quoi et constitue le socle de toute enquête de conformité ou de sécurité.
Questions Fréquemment Posées
La leçon « Auditer les accès » est-elle gratuite ?
Oui — le texte complet de « Auditer les accès » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Auditer les accès » ?
Suivez qui peut voir quoi. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer SQL Academy ?
Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.
Combien de temps prend la leçon « Auditer les accès » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?
Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Rôles et privilèges
- Politiques de sécurité au niveau des lignes
- Autorisations au niveau des colonnes
- Auditer les accès