Utilisation de pgAudit pour journaliser l'activité de la base de données

Les institutions financières, les agences gouvernementales et de nombreux secteurs doivent tenir des journaux d'audit pour se conformer aux exigences réglementaires. En utilisant l'extension d'audit PostgreSQL (pgAudit) avec votre cluster de bases de données Aurora PostgreSQL, vous pouvez capturer les enregistrements détaillés généralement utiles aux auditeurs ou pour répondre aux exigences réglementaires. Par exemple, vous pouvez configurer l'extension pgAudit pour suivre les modifications apportées à des bases de données et à des tables spécifiques, pour enregistrer l'utilisateur qui a effectué la modification et de nombreux autres détails.

L'extension pgAudit s'appuie sur les fonctionnalités de l'infrastructure de journalisation PostgreSQL native en étendant les messages de journal de manière plus détaillée. En d'autres termes, vous utilisez la même approche pour consulter votre journal d'audit que pour consulter les messages du journal. Pour plus d'informations sur la journalisation PostgreSQL, consultez Fichiers journaux de base de données Aurora PostgreSQL.

L'extension pgAudit supprime les données sensibles, telles que les mots de passe en texte clair, des journaux. Si votre cluster de bases de données Aurora PostgreSQL est configuré(e) pour enregistrer les instructions du langage de manipulation de données (DML) comme indiqué dans Activer la journalisation des requêtes pour votre cluster de bases de données Aurora PostgreSQL., vous pouvez éviter le problème de mot de passe en texte clair en utilisant l'extension PostgreSQL Audit.

Vous pouvez configurer l'audit sur vos instances de base de données avec une grande précision. Vous pouvez auditer toutes les bases de données et tous les utilisateurs. Vous pouvez également choisir de n'auditer que certaines bases de données, certains utilisateurs et d'autres objets. Vous pouvez également exclure explicitement certains utilisateurs et certaines bases de données de l'audit. Pour plus d'informations, consultez Exclusion d'utilisateurs ou de bases de données de la journalisation d'audit.

Compte tenu de la quantité de détails qui peuvent être capturés, nous vous recommandons, si vous utilisez pgAudit, de surveiller votre consommation de stockage.

L'extension pgAudit est prise en charge sur toutes les versions d'Aurora PostgreSQL disponibles. Pour une liste des versions de pgAudit prises en charge par la version d'Aurora PostgreSQL, consultez Versions d'extension pour Amazon Aurora PostgreSQL dans les Notes de mise à jour d'Aurora PostgreSQL.

Configuration de l'extension pgAudit

Pour configurer l'extension pgAudit sur votre cluster de bases de données Aurora PostgreSQL, vous devez d'abord ajouter pgAudit aux bibliothèques partagées sur le groupe de paramètres de cluster de bases de données personnalisé pour votre cluster de base de données Aurora PostgreSQL. Pour plus d'informations sur la création d'un groupe de paramètres de cluster de bases de données, consultez Utilisation des groupes de paramètres. Ensuite, vous installez l'extension pgAudit. Enfin, vous spécifiez les bases de données et les objets que vous souhaitez auditer. Les procédures de cette section vous guident. Pour ce faire, vous pouvez utiliser la AWS Management Console ou la AWS CLI.

Vous devez disposer d'autorisations en tant que rôle rds_superuser pour effectuer toutes ces tâches.

Les étapes suivantes supposent que votre cluster de bases de données Aurora PostgreSQL est associé(e) à un groupe de paramètres de cluster de bases de données personnalisé.

Console

Configurer l'extension pgAudit

1. Connectez-vous à la AWS Management Console et ouvrez la console Amazon RDS à l'adresse https://console.aws.amazon.com/rds/.

2. Dans le volet de navigation, choisissez votre instance d'écriture du cluster de bases de données Aurora PostgreSQL .

3. Ouvrez l'onglet Configuration pour votre instance d'écriture du cluster de bases de données Aurora PostgreSQL. Parmi les détails de l'instance, trouvez le lien Groupe de paramètres.

4. Cliquez sur le lien pour ouvrir les paramètres personnalisés associés à votre cluster de bases de données Aurora PostgreSQL.

5. Dans le champ de recherche Parameters (Paramètres), tapez shared_pre pour trouver le paramètre shared_preload_libraries.

6. Choisissez Edit parameters (Modifier les paramètres) pour accéder aux valeurs des propriétés.

7. Ajoutez pgaudit à la liste dans le champ Values (Valeurs). Utilisez une virgule pour séparer les éléments de la liste de valeurs.

   

8. Redémarrez l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL afin que vos modifications du paramètre shared_preload_libraries prennent effet.

9. Lorsque l'instance est disponible, vérifiez que pgAudit a été initialisé. Utilisez psql pour vous connecter à l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL, puis exécutez la commande suivante.

   SHOW shared_preload_libraries;
   shared_preload_libraries 
   --------------------------
   rdsutils,pgaudit
   (1 row) 

10. Une fois pgAudit initialisé, vous pouvez maintenant créer l'extension. Vous devez créer l'extension après avoir initialisé la bibliothèque, car l'extension pgaudit installe des déclencheurs d'événements pour auditer les instructions du langage de définition des données (DDL).

    CREATE EXTENSION pgaudit;

11. Fermez la session psql.

    labdb=> \q

12. Connectez-vous à la AWS Management Console et ouvrez la console Amazon RDS à l'adresse https://console.aws.amazon.com/rds/.

13. Trouvez le paramètre pgaudit.log dans la liste et définissez la valeur appropriée pour votre cas d'utilisation. Par exemple, la définition du paramètre pgaudit.log en write comme indiqué dans l'image suivante permet de capturer des insertions, des mises à jour, des suppressions et d'autres types de modifications dans le journal.

    

    Vous pouvez également choisir l'une des valeurs suivantes pour le paramètre pgaudit.log.

    • none – La valeur par défaut. Aucune modification de base de données n'est journalisée.

    • all – Journalise tout (lecture, écriture, fonction, rôle, ddl, divers).

    • ddl – Journalise toutes les instructions en langage de définition de données (DDL) qui ne sont pas incluses dans la classe ROLE.

    • function – Journalise les appels de fonction et les blocs DO.

    • misc – Journalise diverses commandes, telles que DISCARD, FETCH, CHECKPOINT, VACUUM et SET.

    • read – Journalise SELECT et COPY lorsque la source est une relation (comme une table) ou une requête.

    • role – Journalise les instructions relatives aux rôles et privilèges, telles que GRANT, REVOKE, CREATE ROLE, ALTER ROLE et DROP ROLE.

    • write – Journalise INSERT, UPDATE, DELETE, TRUNCATE et COPY lorsque la destination est une relation (table).

14. Sélectionnez Enregistrer les modifications.

15. Ouvrez la console Amazon RDS à l’adresse https://console.aws.amazon.com/rds/.

16. Sélectionnez votre instance d'écriture du cluster de bases de données Aurora PostgreSQL dans la liste des bases de données, puis choisissez Reboot (Redémarrer) dans le menu Actions.

AWS CLI

Configurer pgAudit

Pour configurer PgAudit à l'aide deAWS CLI, vous devez appeler l'modify-db-parameter-groupopération pour modifier les paramètres du journal d'audit dans votre groupe de paramètres personnalisé, comme indiqué dans la procédure suivante.

1. Utilisez la commande AWS CLI suivante pour ajouter pgaudit au paramètre shared_preload_libraries.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
      --region aws-region

2. Utilisez la commande AWS CLI suivante pour redémarrer l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL afin que la bibliothèque pgAudit soit initialisée.

   aws rds reboot-db-instance \
       --db-instance-identifier writer-instance \
       --region aws-region

3. Lorsque l'instance est disponible, vous pouvez vérifier que pgaudit a été initialisé. Utilisez psql pour vous connecter à l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL, puis exécutez la commande suivante.

   SHOW shared_preload_libraries;
   shared_preload_libraries 
   --------------------------
   rdsutils,pgaudit
   (1 row) 

   Une fois pgAudit initialisé, vous pouvez maintenant créer l'extension.

   CREATE EXTENSION pgaudit;

4. Fermez la session psql afin de pouvoir utiliser l'AWS CLI.

   labdb=> \q

5. Utilisez la commande AWS CLI suivante pour spécifier les classes d'instructions qui doivent être journalisées par journalisation des audits de session. L'exemple définit le paramètre pgaudit.log sur write, qui capture les insertions, les mises à jour et les suppressions dans le journal.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \
      --region aws-region

   Vous pouvez également choisir l'une des valeurs suivantes pour le paramètre pgaudit.log.

   • none – La valeur par défaut. Aucune modification de base de données n'est journalisée.

   • all – Journalise tout (lecture, écriture, fonction, rôle, ddl, divers).

   • ddl – Journalise toutes les instructions en langage de définition de données (DDL) qui ne sont pas incluses dans la classe ROLE.

   • function – Journalise les appels de fonction et les blocs DO.

   • misc – Journalise diverses commandes, telles que DISCARD, FETCH, CHECKPOINT, VACUUM et SET.

   • read – Journalise SELECT et COPY lorsque la source est une relation (comme une table) ou une requête.

   • role – Journalise les instructions relatives aux rôles et privilèges, telles que GRANT, REVOKE, CREATE ROLE, ALTER ROLE et DROP ROLE.

   • write – Journalise INSERT, UPDATE, DELETE, TRUNCATE et COPY lorsque la destination est une relation (table).

   Redémarrez l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL, à l'aide de la commande AWS CLI suivante.

   aws rds reboot-db-instance \
       --db-instance-identifier writer-instance \
       --region aws-region

Audit d'objets de base de données

Une fois que pgAudit est défini sur votre cluster de bases de données Aurora PostgreSQL et qu'il est configuré en fonction de vos besoins, des informations plus détaillées sont capturées dans le journal PostgreSQL. Par exemple, alors que la configuration de journalisation PostgreSQL par défaut identifie la date et l'heure auxquelles une modification a été apportée à une table de base de données, avec l'extension pgAudit, l'entrée du journal peut inclure le schéma, l'utilisateur qui a effectué la modification et d'autres détails en fonction de la manière dont les paramètres de l'extension sont configurés. Vous pouvez configurer l'audit pour suivre les modifications de différentes manières.

• Pour chaque session, par utilisateur. Au niveau de la session, vous pouvez capturer le texte de commande complet.

• Pour chaque objet, par utilisateur et par base de données.

La fonctionnalité d'audit des objets est activée lorsque vous créez le rôle rds_pgaudit sur votre système, puis que vous ajoutez ce rôle au paramètre pgaudit.role dans votre groupe de paramètres personnalisé. Par défaut, le paramètre pgaudit.role n'est pas défini et la seule valeur autorisée est rds_pgaudit. Les étapes suivantes supposent que pgaudit a été initialisé et que vous avez créé l'extension pgaudit en suivant la procédure décrite dans Configuration de l'extension pgAudit.

Comme le montre cet exemple, la ligne « LOG: AUDIT: SESSION » fournit des informations sur la table et son schéma, entre autres détails.

Configurer l'audit d'objets

1. Utilisez psql pour vous connecter à l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL..

   psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb

2. Créez un rôle de base de données appelé rds_pgaudit à l'aide de la commande suivante.

   labdb=> CREATE ROLE rds_pgaudit;
   CREATE ROLE
   labdb=> 

3. Fermez la session psql.

   labdb=> \q

   Dans les étapes suivantes, utilisez l'AWS CLI pour modifier les paramètres du journal d'audit dans votre groupe de paramètres personnalisé.

4. Utilisez la commande AWS CLI suivante pour définir le paramètre pgaudit.role à rds_pgaudit. Par défaut, ce paramètre est vide et rds_pgaudit est la seule valeur autorisée.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
      --region aws-region

5. Utilisez la commande AWS CLI suivante pour redémarrer l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL afin que les modifications apportées aux paramètres prennent effet.

   aws rds reboot-db-instance \
       --db-instance-identifier writer-instance \
       --region aws-region

6. Exécutez la commande suivante pour confirmer que pgaudit.role est défini sur rds_pgaudit.

   SHOW pgaudit.role;
   pgaudit.role 
   ------------------
   rds_pgaudit 

Pour tester la journalisation pgAudit, vous pouvez exécuter plusieurs exemples de commandes que vous souhaitez auditer. Par exemple, vous pouvez exécuter les commandes suivantes.

CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id 
----
(0 rows)

Les journaux de base de données doivent contenir une entrée similaire à ce qui suit.

...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...

Pour obtenir des informations sur l'affichage des journaux, veuillez consulter Surveillance des fichiers journaux Amazon Aurora.

Pour en savoir plus sur l'extension PgAudit, consultez PgAudit on. GitHub

Exclusion d'utilisateurs ou de bases de données de la journalisation d'audit

Comme indiqué dans Fichiers journaux de base de données Aurora PostgreSQL, les journaux PostgreSQL consomment de l'espace de stockage. L'utilisation de l'extension pgAudit augmente le volume de données collectées dans vos journaux à des degrés divers, en fonction des modifications que vous suivez. Vous n'avez peut-être pas besoin d'auditer chaque utilisateur ou base de données de votre cluster de bases de données Aurora PostgreSQL.

Pour minimiser les impacts sur votre stockage et éviter de capturer inutilement des enregistrements d'audit, vous pouvez exclure les utilisateurs et les bases de données de l'audit. Vous pouvez également modifier la journalisation au cours d'une session donnée. Les exemples suivants montrent comment procéder.

Note

Les paramètres au niveau de la session ont priorité sur les paramètres du groupe de paramètres du cluster de bases de données personnalisé pour l'instance d'écriture du cluster de bases de données Aurora PostgreSQL. Si vous ne souhaitez pas que les utilisateurs de base de données contournent vos paramètres de configuration de journalisation des audits, veillez à modifier leurs autorisations.

Supposons que votre cluster de bases de données Aurora PostgreSQL soit configuré(e) pour auditer le même niveau d'activité pour tous les utilisateurs et bases de données. Vous pouvez ensuite décider de ne pas auditer l'utilisateur myuser. Vous pouvez désactiver l'audit pour myuser à l'aide de la commande SQL suivante.

ALTER USER myuser SET pgaudit.log TO 'NONE';

Vous pouvez ensuite utiliser la requête suivante pour vérifier la colonne user_specific_settings pour pgaudit.log afin de confirmer que le paramètre est défini sur NONE.

SELECT
    usename AS user_name,
    useconfig AS user_specific_settings
FROM
    pg_user
WHERE
    usename = 'myuser';

Vous devez voir la sortie suivante.

 user_name | user_specific_settings
-----------+------------------------
 myuser    | {pgaudit.log=NONE}
(1 row)

Vous pouvez désactiver la journalisation pour un utilisateur donné au cours de sa session avec la base de données à l'aide de la commande suivante.

ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';

Utilisez la requête suivante pour vérifier la colonne des paramètres du fichier pgaudit.log pour une combinaison utilisateur et base de données spécifique.

SELECT
    usename AS "user_name",
    datname AS "database_name",
    pg_catalog.array_to_string(setconfig, E'\n') AS "settings"
FROM
    pg_catalog.pg_db_role_setting s
    LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
    LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole
WHERE
    usename = 'myuser'
    AND datname = 'mydatabase'
ORDER BY
    1,
    2;

Vous voyez des résultats similaires à ce qui suit.

  user_name | database_name |     settings
-----------+---------------+------------------
 myuser    | mydatabase    | pgaudit.log=none
(1 row)

Après avoir désactivé l'audit pour myuser, vous décidez de ne pas suivre les modifications apportées à mydatabase. Vous pouvez désactiver l'audit pour cette base de données spécifique à l'aide de la commande suivante.

ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';

Utilisez ensuite la requête suivante pour vérifier la colonne database_specific_settings afin de confirmer que le fichier pgaudit.log est défini sur NONE.

SELECT
a.datname AS database_name,
b.setconfig AS database_specific_settings
FROM
pg_database a
FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase
WHERE
a.datname = 'mydatabase';

Vous devez voir la sortie suivante.

 database_name | database_specific_settings
---------------+----------------------------
 mydatabase    | {pgaudit.log=NONE}
(1 row)

Pour rétablir les paramètres par défaut pour myuser, utilisez la commande suivante :

ALTER USER myuser RESET pgaudit.log;

Pour rétablir les paramètres par défaut pour une base de données, utilisez la commande suivante.

ALTER DATABASE mydatabase RESET pgaudit.log;

Pour rétablir les paramètres par défaut pour l'utilisateur et la base de données, utilisez la commande suivante.

ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;

Vous pouvez également capturer des événements spécifiques dans le journal en définissant pgaudit.log pour l'une des autres valeurs autorisées pour le paramètre pgaudit.log. Pour plus d'informations, consultez Liste des paramètres autorisés pour le paramètre pgaudit.log.

ALTER USER myuser SET pgaudit.log TO 'read';
ALTER DATABASE mydatabase SET pgaudit.log TO 'function';
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'

Référence pour l'extension pgAudit

Vous pouvez spécifier le niveau de détail que vous souhaitez pour votre journal d'audit en modifiant un ou plusieurs des paramètres répertoriés dans cette section.

Contrôle du comportement de pgAudit

Vous pouvez contrôler la journalisation d'audit en modifiant un ou plusieurs des paramètres répertoriés dans la table suivante.

Paramètre	Description
pgaudit.log	Spécifie quelles classes d'instructions seront journalisées par la journalisation de l'audit de session. Les valeurs autorisées incluent ddl, function, misc, read, role, write, none, all. Pour plus d'informations, consultez Liste des paramètres autorisés pour le paramètre pgaudit.log.
pgaudit.log_catalog	Lorsque cette option est activée (définie sur 1), cela ajoute des instructions à la piste d'audit si toutes les relations d'une instruction se trouvent dans pg_catalog.
pgaudit.log_level	Spécifie le niveau de journal qui sera utilisé pour les entrées de journal. Valeurs autorisées : debug5, debug4, debug3, debug2, debug1, info, notice, warning, log
pgaudit.log_parameter	Lorsque cette option est activée (définie sur 1), les paramètres transmis avec l'instruction sont capturés dans le journal d'audit.
pgaudit.log_relation	Lorsque cette option est activée (définie sur 1), le journal d'audit de session crée une entrée de journal distincte pour chaque relation (TABLE, VIEW, etc.) référencée dans une instruction SELECT ou DML.
pgaudit.log_statement_once	Spécifie si la journalisation inclura le texte de l'instruction et les paramètres avec la première entrée de journal pour une combinaison instruction/sous-instruction ou avec chaque entrée.
pgaudit.role	Spécifie le rôle principal à utiliser pour la journalisation de l'audit des objets. La seule entrée autorisée est rds_pgaudit.

Liste des paramètres autorisés pour le paramètre pgaudit.log

Valeur	Description
none	Il s’agit de l’option par défaut. Aucune modification de base de données n'est journalisée.
Tout	Journalise tout (lecture, écriture, fonction, rôle, ddl, divers).
ddl	Journalise toutes les instructions en langage de définition de données (DDL) qui ne sont pas incluses dans la classe ROLE.
fonction	Journalise les appels de fonction et les blocs DO.
Misc	Journalise diverses commandes, telles que DISCARD, FETCH, CHECKPOINT, VACUUM et SET.
lire	Journalise SELECT et COPY lorsque la source est une relation (comme une table) ou une requête.
rôle	Journalise les instructions relatives aux rôles et privilèges, telles que GRANT, REVOKE, CREATE ROLE, ALTER ROLE et DROP ROLE.
write	Journalise INSERT, UPDATE, DELETE, TRUNCATE et COPY lorsque la destination est une relation (table).

Pour journaliser plusieurs types d'événements avec l'audit de session, utilisez une liste séparée par des virgules. Pour journaliser tous les types d'événements, définissez pgaudit.log à la valeur ALL. Redémarrez l'instance de base de données pour appliquer les modifications.

Avec les audits d'objet, vous pouvez affiner la journalisation d'audit pour que celle-ci fonctionne avec des relations spécifiques. Par exemple, vous pouvez spécifier que vous souhaitez une journalisation d'audit pour les opérations READ sur une ou plusieurs tables.