Comment utiliser l'extension pgAudit pour auditer mon instance de base de données Amazon RDS for PostgreSQL ?

Résolution

Pour journaliser les activités exécutées sur votre instance de base de données RDS for PostgreSQL, vous pouvez définir différents paramètres. Une fois l’extension pgAudit activée, vous pouvez configurer le paramètre pgaudit.log pour auditer des bases de données, des rôles, des tables et des colonnes spécifiques. Tout d'abord, définissez la valeur du paramètre pgaudit.log sur aucun dans le groupe de paramètres d'une base de données, d'un rôle ou d'une table :

> show pgaudit.log;+---------------+
| pgaudit.log   |
|---------------|
| none          |
+---------------+
SHOW

Utiliser l’extension pgAudit pour auditer des bases de données

Procédez comme suit :

1. Exécutez la commande suivante pour remplacer la configuration système du paramètre pgaudit.log dans votre base de données uniquement :

   ALTER DATABASE test_database set pgaudit.log='All';

   Remarque : Remplacez test_database par le nom de votre base de données. La commande précédente change la valeur du paramètre pgaudit.log sur Tout afin que pgAudit n'audite que test_database.
2. Connectez-vous à votre base de données, puis exécutez la requête suivante pour auditer la base de données :

   select * from aud_table;

   Remarque : Remplacez aud_table par la table que vous souhaitez auditer.
   La sortie du journal d’erreurs est similaire à ce qui suit :

   2019-06-25 19:21:35 UTC:192.0.2.7(39330):testpar@test_database:[21638]:LOG: AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not logged>

Utiliser l’extension pgAudit pour auditer des rôles

Vous pouvez également modifier les rôles dans le paramètre pgaudit.log afin qu'ils aient des valeurs différentes. Pour définir différentes valeurs pour le paramètre pgaudit.log de vos rôles, exécutez les commandes suivantes :

ALTER ROLE test1 set pgaudit.log='All';

ALTER ROLE test2 set pgaudit.log='DDL';

Remarque : Remplacez test1 et test2 par vos noms de rôles.

Pour vérifier si les modifications sont apportées au niveau du rôle, exécutez la requête suivante :

> select rolname,rolconfig from pg_roles where rolname in ('test1','                  test2');
+-----------+----------------------+
| rolname   | rolconfig            |
|-----------+----------------------|
| test1     | [u'pgaudit.log=All'] |
| test2     | [u'pgaudit.log=DDL'] |
+-----------+----------------------+
SELECT 2
Time: 0.010s

Remarque : Remplacez test1 et test2 par vos noms de rôles.

Pour tester le comportement de journalisation de pgAudit de vos rôles, exécutez les requêtes suivantes pour chaque rôle :

CREATE TABLE test_table (id int);CREATE TABLE

select * from test_table;id
----
(0 rows)

La sortie du journal pour deux rôles différents est similaire aux exemples suivants.

test1

...2019-06-26 14:51:12 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>

2019-06-26 14:51:18 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not
logged>
...

test2

...2019-06-26 14:53:54 UTC:192.0.2.7(44772):test2@postgres:[5517]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>
...

Dans l'exemple de sortie de test2, il n'existe aucune entrée d'audit pour la requête SELECT car le paramètre pgaudit.log pour test2 est défini uniquement sur DDL. Ce test vérifie que le rôle test2 journalise uniquement les opérations DDL.

Utiliser l’extension pgAudit pour auditer des tables

Remarque : L'extension pgAudit ne peut journaliser que les commandes SELECT, INSERT, UPDATE et DELETE. L'extension ne peut pas auditer TRUNCATE.

Pour auditer vos tables, exécutez la commande suivante pour autoriser le rôle rds_pgaudit à accéder aux commandes :

grant select, delete on test_table to rds_pgaudit;

Remarque : La commande précédente permet au rôle rds_pgaudit d'accéder à SELECT et DELETE. Remplacez select et delete par les commandes que vous souhaitez auditer.

Pour vérifier que la journalisation des audits est configurée correctement, exécutez une instruction sur la table. L'exemple de commande suivant exécute une instruction DELETE sur test_table :

Time: 0.008s DELETE 1
>delete from test_table where pid=5050;

La sortie de l’instruction DELETE est similaire à ce qui suit :

2019-06-25 17:13:02UTC:192.0.2.7(41810):postgresql104saz@postgresql104saz:[24976]:LOG:
AUDIT: OBJECT,3,1,WRITE,DELETE,TABLE,public.t1,delete from test_table where
pid=5050,<not logged>

Utiliser extension pgAudit pour auditer des colonnes

Vous pouvez auditer les colonnes d'une table spécifique, par exemple lorsque des données sensibles ne figurent que dans une seule colonne. L'exemple de commande suivant crée un tableau de paie contenant une colonne sensible avec des données sur les salaires qui doivent être auditées :

create table payroll(    
    name text,
    salary text
);

Pour auditer la colonne des salaires, exécutez d'abord la commande suivante pour accorder au rôle rds_pgaudit l'accès à SELECT dans la colonne salaire :

grant select (salary)  on payroll to rds_pgaudit;

Puis, exécutez la commande suivante pour SÉLECTIONNER toutes les colonnes du tableau, y compris la colonne salaire :

select * from payroll;

Si la requête SELECT n’inclut pas la colonne salaire, pgAudit n'audite pas cette colonne.

Dans l’exemple de sortie suivant, pgAudit n’audite aucune requête SELECT qui inclut la colonne salaire :

2019-06-25 18:25:02
UTC:192.0.2.7(42056):postgresql104saz@postgresql104saz:[4118]:LOG:
AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.payroll,select * from
payroll,<not logged>

Informations connexes

Tâches DBA courantes pour Amazon RDS for PostgreSQL

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