Comment puis-je utiliser l’extension pgaudit pour auditer mon instance de base de données Amazon RDS qui exécute PostgreSQL ?

Résolution

Pour journaliser les activités exécutées sur votre instance de base de données PostgreSQL, vous pouvez définir différents paramètres. Pour auditer différentes bases de données, rôles, tables ou colonnes, vous pouvez utiliser l’extension pgaudit. Une fois l’extension pgaudit activée, vous pouvez configurer le paramètre pgaudit.log pour auditer des bases de données, rôles, tables et colonnes spécifiques.

Activation de l’extension pgaudit sur une instance Amazon RDS exécutant PostgreSQL

1.    Créez un rôle de base de données spécifique appelé rds_pgaudit en exécutant la commande suivante :

CREATE ROLE rds_pgaudit;
CREATE ROLE

2.    Modifiez les paramètres ci-dessous dans le groupe de paramètres personnalisés de la base de données associée à votre instance de base de données :
Ajouter pgaudit à shared_preload_libraries
Configurer pgaudit.role sur rds_pgaudit, le rôle créé à l’étape 1

3.    Redémarrez l’instance pour appliquer les modifications apportées au groupe de paramètres.

4.    Vérifiez que pgaudit est initialisé en exécutant la commande suivante :

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

5.    Créez l’extension pgaudit en exécutant la commande suivante :

CREATE EXTENSION pgaudit;
CREATE EXTENSION

6.    Vérifiez que pgaudit.role est défini sur rds_pgauditt en exécutant la commande suivante :

show pgaudit.role;
pgaudit.role
------------------
rds_pgaudit

7.    Configurez le paramètre pgaudit.log pour auditer l’un des éléments suivants :

• ALL permet d’auditer les commandes suivantes.
• MISC permet d’auditer diverses commandes, telles que DISCARD, FETCH, CHECKPOINT, VACUUM ou SET.
• DDL permet d’auditer tous les langages de description de données (DDL) qui ne sont pas inclus dans la classe ROLE.
• ROLE permet d’auditer les instructions relatives aux rôles et aux privilèges, telles que GRANT, REVOKE ou CREATE/ALTER/DROP ROLE.
• FUNCTION permet d’auditer les appels de fonction et les blocs DO.
• WRITE permet d’auditer les commandes INSERT, UPDATE, DELETE, TRUNCATE et COPY lorsque la destination est une relation.
• READ permet d’auditer les commandes SELECT et COPY lorsque la source est une relation ou une requête.

Selon ce que vous souhaitez auditer, définissez la valeur du paramètre pgaudit.log pour une base de données, un rôle ou une table.

Utilisation de l’extension pgaudit pour auditer des bases de données

1.    Pour définir la valeur du paramètre pgaudit.log pour une base de données, un rôle ou une table, définissez le paramètre pgaudit.log sur aucun au niveau du groupe de paramètres :

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

2. Exécutez la commande suivante pour remplacer la configuration système avec ce paramètre pour cette base de données uniquement :

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

Cette action remplace la valeur du paramètre pgaudit.logg sur Tous, de façon à n’auditer que la base de données test_database dans l’instance de base de données RDS.

3.    Connectez-vous pour à test_database et exécutez la requête suivante :

select * from test_table;

La sortie du journal d’erreurs est semblable à la suivante :

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>

Utilisation de l’extension pgaudit pour auditer des rôles

Tout comme le paramètre pgaudit.log est configuré au niveau de la base de données, le rôle est modifié en définissant le paramètre pgaudit.log sur une valeur différente. Dans les exemples de commandes suivants, les rôles test1 et test2 sont modifiés afin d’obtenir différentes configurations de pgaudit.log.

1.    Définissez des valeurs différentes du paramètre pgaudit.logg pour les rôles test1 et test2 en exécutant les commandes suivantes :

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

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

2.    Vérifiez que les modifications sont apportées au niveau du rôle en exécutant 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

3.    Exécutez les requêtes suivantes pour test1 et test2 :

CREATE TABLE test_table (id int);
CREATE TABLE

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

La sortie du journal est semblable à ce qui suit pour 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>
...

La sortie du journal est semblable à ce qui suit pour test2 après avoir exécuté les mêmes requêtes :

...
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>
...

Remarque : il n'existe aucune entrée d’audit pour la requête SELECT parce que le paramètre pgaudit.log pour test2 est configuré sur DDL uniquement.

Utilisation de l’extension pgaudit pour auditer des tables

La configuration du paramètre pgaudit.log permet d’auditer et de journaliser les instructions qui affectent une relation spécifique. L’extension pgaudit ne permet de journaliser que les commandes SELECT, INSERT, UPDATE et DELETE. La commande TRUNCATE n’est pas incluse dans la journalisation d’audit de l’objet. Si vous autorisez le rôle rds_pgaudit à accéder à une opération (telle que SELECT, DELETE, INSERT ou UPDATE) sur la table que vous souhaitez auditer, tout audit d’autorisation journalise l’instruction correspondante. L’exemple suivant autorise le rôle rds_pgaudit à accéder aux opérations SELECT et DELETE, de façon à auditer toutes les instructions SELECT et DELETE exécutées sur test_table.

1.    Autorisez le rôle rds_pgaudit à accéder à SELECT et DELETE en exécutant la commande suivante :

grant select, delete on test_table to rds_pgaudit;

2.    Vérifiez que la journalisation d’audit est correctement configurée en exécutant 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 semblable à ce qui suit :

2019-06-25 17:13:02
UTC: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>

Utilisation de l’extension pgaudit pour auditer des colonnes

Vous pouvez également définir l’audit au niveau de la colonne d’une table spécifique. Cela peut s’avérer utile, par exemple, lorsqu’il existe des données sensibles dans une seule colonne. Dans l'exemple de commande suivant, la table de paie créée comporte une colonne de données sensibles sur les salaires à auditer :

create table payroll
(    
    name text,
    salary text
);

1.    Autorisez le rôle rds_pgaudit à accéder à SELECT dans la colonne Salaires afin d’auditer toute instruction SELECT exécutée sur cette colonne :

grant select (salary)  on payroll to rds_pgaudit;

2.    Exécutez une instruction SELECT sur toutes les colonnes de la table, y compris la colonne salaires :

select * from payroll;

Dans l’exemple de sortie suivant, toute instruction SELECT qui inclut la colonne salaires est auditée. À l’inverse, toute instruction SELECT qui n’inclut pas la colonne salaires n’est pas auditée.

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 PostgreSQL