Effectuer un audit PostgreSQL à l'aide de pgAudit

MySQL | PostgreSQL | SQL Server

Cette page décrit l'audit de base de données à l'aide de pgAudit. Cette extension vous aide à configurer les différents journaux fréquemment requis pour être en conformité avec les exigences de certifications gouvernementales, financières et ISO.

Pour obtenir des informations générales sur les extensions PostgreSQL dans Cloud SQL, consultez la page Extensions PostgreSQL.

Présentation

L'audit de base de données dans Cloud SQL pour PostgreSQL est disponible via l'extension Open Source pgAudit.

Grâce à cette extension, vous pouvez enregistrer et suivre de manière sélective les opérations SQL effectuées sur une instance de base de données spécifique. Elle vous fournit des fonctionnalités d'audit permettant de surveiller et d'enregistrer un sous-ensemble d'opérations donné.

L'extension pgAudit s'applique aux commandes et requêtes SQL exécutées. Elle diffère en cela des journaux d'audit Cloud, qui doivent être utilisés pour auditer les opérations d'administration et de maintenance effectuées sur une instance Cloud SQL.

Pour en savoir plus sur les journaux d'audit dans Cloud SQL, consultez la page Journaux d'audit.

Configurer l'audit de base de données dans Cloud SQL

La procédure à suivre pour créer des journaux d'audit à l'aide de l'extension pgAudit inclut les étapes suivantes :

Activer l'indicateur cloudsql.enable_pgaudit dans Cloud SQL
Exécuter une commande servant à créer l'extension pgAudit
Définir les valeurs de l'indicateur pgaudit.log

Après avoir configuré l'audit de la base de données, vous pouvez afficher les journaux et, si nécessaire, désactiver la journalisation.

Configurer l'audit

Cette section décrit les principes de base de la configuration d'opérations d'audit de bases de données.

Indicateur initial permettant d'activer l'audit

Dans Cloud SQL, les indicateurs de base de données vous servent à effectuer de nombreuses opérations, y compris l'ajustement des paramètres PostgreSQL et la configuration d'une instance. L'indicateur cloudsql.enable_pgaudit permet d'auditer une instance de base de données spécifique. Vous pouvez modifier la valeur de l'indicateur cloudsql.enable_pgaudit à l'aide de Google Cloud Console ou de la commande gcloud.

Suivez les instructions standards pour les indicateurs afin d'activer l'indicateur cloudsql.enable_pgaudit en définissant sa valeur sur on. Par exemple, pour utiliser la commande gcloud, spécifiez les informations suivantes en remplaçant [INSTANCE_NAME] par le nom de votre instance :

gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=on

L'indicateur cloudsql.enable_pgaudit est répertorié avec les autres indicateurs compatibles. Il est spécifique à Cloud SQL.

Exécuter la commande de création de l'extension pgAudit

Après avoir activé l'indicateur de base de données, exécutez la commande CREATE EXTENSION à l'aide d'un client psql compatible. La commande suivante crée l'extension pgAudit pour toutes les bases de données d'une instance Cloud SQL :

CREATE EXTENSION pgaudit;

Définir les valeurs de l'option `pgaudit.log`

Suivez les instructions standards pour les indicateurs afin de définir les valeurs de l'indicateur pgaudit.log.

Par exemple, pour activer l'audit pour l'ensemble des opérations de base de données sur une instance, vous pouvez exécuter la commande gcloud suivante :

  gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

Configurer d'autres paramètres de la base de données

Pour configurer les paramètres d'audit de la base de données, suivez les procédures décrites dans la section Personnaliser les journaux d'audit de base de données.

Afficher les journaux d'audit de base de données

Pour consulter les journaux d'audit, activez les journaux d'audit pour l'accès aux données dans votre projet. Les journaux pgAudit générés pour une instance donnée sont envoyés à Cloud Logging en tant que journaux d'audit pour l'accès aux données. Les utilisateurs peuvent afficher les journaux pgAudit générés dans l'explorateur de journaux.

Dans l'application Explorateur de journaux, vous pouvez afficher les journaux pgAudit en sélectionnant le filtre de journal cloudaudit.googleapis.com/data_access.

Vous pouvez également utiliser la requête suivante pour afficher tous les journaux pgAudit d'un projet Cloud SQL donné :

resource.type="cloudsql_database"
logName="projects/<your-project-name>/logs/cloudaudit.googleapis.com%2Fdata_access"
protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"

Format des journaux pour pgAudit

Chaque entrée de journal pgAudit dans les journaux d'audit pour l'accès aux données contient des champs représentant les informations collectées pour une requête.

Voici un exemple :

{
  protoPayload: {
    @type: "type.googleapis.com/google.cloud.audit.AuditLog"
    methodName: "cloudsql.instances.query"
    request: {
      @type: "type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"
      auditClass: "READ"
      auditType: "SESSION"
      chunkCount: "1"
      chunkIndex: "1"
      command: "SELECT"
      database: "finance"
      databaseSessionId: 2209692
      parameter: "[not logged]"
      statement: "SELECT * FROM revenue"
      statementId: 2
      substatementId: 1
      user: "alice"
    }
  }
}

Vous trouverez ci-dessous la description des champs disponibles dans les journaux d'audit pour l'accès aux données :

auditClass. Type d'instruction consigné. Les valeurs possibles sont READ, WRITE, FUNCTION, ROLE, DDL, MISC et MISC_SET.
auditType. SESSION ou OBJECT.
chunkCount. Les données fournies dans les champs parameter et statement peuvent être fragmentées. Le champ chunkCount indique le nombre total de fragments. Reportez-vous également à la description du champ chunkIndex.
chunkIndex. Indique le numéro d'index des fragments de données dans les champs parameter et statement (dans le conteneur request actuel). Le nombre initial est 1. Reportez-vous également à la description du champ chunkCount.
command. Par exemple, ALTER TABLE ou SELECT.
parameter. Le champ chunkIndex peut déterminer le contenu de ce champ. Reportez-vous à la description du champ chunkIndex. Si la valeur de pgaudit.log_parameter est définie, le champ parameter peut contenir les paramètres d'instruction sous la forme de données CSV entre guillemets, ou [none] si aucun paramètre n'est défini. Dans le cas contraire, ce champ contient la valeur [not logged] (non consigné).
statement. Instruction exécutée sur le backend. Le champ chunkIndex peut déterminer le contenu du champ statement. Reportez-vous à la description du champ chunkIndex.
statementId. Identifiant d'instruction unique pour cette session. Chaque ID d'instruction représente un appel de backend. Les ID d'instruction sont séquentiels et ce, même si certaines instructions ne sont pas consignées.
substatementId. ID séquentiel pour chaque sous-instruction de l'instruction principale.

Certains de ces champs sont également décrits dans la documentation de pgAudit.

Désactiver l'audit

Pour désactiver l'audit de base de données, définissez la valeur de l'indicateur cloudsql.enable_pgaudit sur off. Cette valeur peut être modifiée à l'aide de Google Cloud Console ou de la commande gcloud. Suivez les instructions standards pour les indicateurs afin de désactiver l'indicateur cloudsql.enable_pgaudit.

De plus, vous devez exécuter la commande DROP EXTENSION à l'aide d'un client psql compatible afin de supprimer l'état de l'extension :

DROP EXTENSION pgaudit;

Personnaliser les journaux d'audit de base de données dans Cloud SQL

Cette section décrit les méthodes permettant de personnaliser le comportement d'audit d'une instance de base de données.

Pour découvrir les autres fonctionnalités de l'extension, consultez la documentation de pgAudit.

Conditions requises pour les droits de super-utilisateur

Dans Cloud SQL, les extensions ne peuvent être créées que par des utilisateurs disposant du rôle cloudsqlsuperuser. Lorsque vous créez une instance PostgreSQL, l'utilisateur PostgreSQL par défaut est créé automatiquement (mais vous devez définir son mot de passe). L'utilisateur PostgreSQL par défaut dispose du rôle cloudsqlsuperuser. Pour en savoir plus, consultez la page Utilisateurs PostgreSQL.

Configurer l'audit pour toutes les opérations de base de données sur l'instance

Afin de configurer l'audit pour toutes les bases de données d'une instance, vous devez appliquer les paramètres pgAudit au niveau du système. Les paramètres d'audit au niveau du système ne peuvent être définis qu'en tant qu'indicateurs de base de données via Google Cloud Console ou la commande gcloud. Par exemple, pour activer l'audit pour l'ensemble des opérations de base de données sur une instance, vous pouvez utiliser la commande gcloud suivante :

  gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

Configurer des opérations spécifiques sur toutes les bases de données de l'instance

Vous pouvez auditer toutes les bases de données de l'instance à l'aide de Google Cloud Console ou de la commande gcloud. Par exemple, pour n'activer l'audit que pour les opérations de lecture et d'écriture sur l'instance, vous pouvez exécuter la commande gcloud suivante. Cet exemple utilise une syntaxe basée sur une liste pour spécifier plusieurs valeurs :

  gcloud sql instances patch [INSTANCE_NAME] \
  --database-flags ^:^cloudsql.enable_pgaudit=on:pgaudit.log=read,write

La commande écrase les indicateurs de base de données existants.

Configurer l'audit pour une base de données spécifique

Configurer l'audit pour une base de données spécifique nécessite de définir les paramètres pgAudit au niveau de la base de données. Par exemple, vous pouvez utiliser la commande SQL suivante pour activer l'audit de lecture/écriture pour une base de données nommée finance :

finance=> ALTER DATABASE finance SET pgaudit.log = 'read,write';

Configurer l'audit pour une relation

L'audit d'une relation est plus précis que l'audit d'une base de données spécifique.

Lorsque vous auditez une relation, un rôle d'auditeur unique est attribué au paramètre pgaudit.role. Tout objet ou relation attribué à ce rôle est alors consigné.

Par exemple, pour configurer l'audit pour toutes les requêtes SELECT sur la relation salary au sein de la base de données employee, vous pouvez utiliser les commandes suivantes :

employee=> CREATE ROLE auditor WITH NOLOGIN;
employee=> ALTER DATABASE employee SET pgaudit.role = 'auditor';
employee=> GRANT SELECT ON salary TO auditor;

Vous pouvez également auditer un sous-ensemble de colonnes pour une relation donnée.

Par exemple, la commande suivante configure la journalisation d'audit afin qu'elle ait lieu uniquement lorsque les colonnes income et tax_status font l'objet d'un accès à partir de la relation salary :

employee=> GRANT SELECT(income, tax_status) ON salary TO auditor;

Configurer l'audit pour un utilisateur de base de données

Vous pouvez activer l'audit pour un utilisateur spécifique en définissant le paramètre pgaudit.log au niveau d'un ROLE.

Par exemple, la commande SQL suivante active l'audit pour toutes les opérations de base de données exécutées par l'utilisateur Alice :

finance=> ALTER ROLE alice SET pgaudit.log = 'all';

Conseils pour la gestion de l'audit dans Cloud SQL

Lorsque vous personnalisez le comportement d'audit, gardez à l'esprit les points suivants :

Lorsque l'indicateur cloudsql.enable_pgaudit de la base de données est désactivé, la journalisation d'audit s'arrête immédiatement. Toutefois, les paramètres pgAudit appliqués (par exemple, les paramètres pgaudit.log) sont conservés, à moins d'être explicitement supprimés.
L'instance de base de données est redémarrée chaque fois que la valeur de l'indicateur de base de données correspondant à cloudsql.enable_pgaudit est modifiée.
Les utilisateurs de base de données créés via des commandes CREATE ROLE explicites ne disposent pas des autorisations nécessaires pour modifier les paramètres d'audit. Seuls les utilisateurs de base de données créés via la console Google Cloud et la commande gcloud peuvent modifier les paramètres d'audit.
Lorsque vous activez à la fois la journalisation d'audit de session et la journalisation d'audit des objets, des entrées sont ajoutées aux journaux pour les deux types de journalisation d'audit. La journalisation de session et la journalisation des objets ne s'annulent pas et n'influent pas l'une sur l'autre.

Limites de l'extension pgAudit dans Cloud SQL pour PostgreSQL

Les journaux d'audit sont temporairement écrits sur le disque de leur instance, occupant ainsi de l'espace disque avant l'envoi des journaux à Cloud Logging. Par conséquent, consultez toutes les informations suivantes avant d'utiliser cette fonctionnalité :

Le taux d'ingestion de journaux est de 4 Mo par seconde. Lorsque la charge de la génération des journaux dépasse le taux d'ingestion, voici ce qui peut se produire :
- Augmentation indésirable de l'utilisation du disque
- Espace disque susceptible d'être épuisé
Si vous avez activé cette fonctionnalité et que vous exécutez un grand nombre de requêtes conformes à vos critères d'audit, l'utilisation du disque peut augmenter trop rapidement.
Avant d'utiliser cette fonctionnalité, vous devez :
- activer l'augmentation automatique de l'espace de stockage ;
- surveiller l'utilisation globale du disque (la charge de la génération des journaux ne peut pas être surveillée de manière distincte). Utilisez la métrique cloudsql.googleapis.com/database/disk/utilization dans l'Explorateur de métriques ;
- si nécessaire, réduire l'utilisation du disque en exécutant moins de requêtes ou en limitant les audits.
Si l'espace disque disponible est épuisé, vous risquez de perdre les journaux d'audit de certaines requêtes.