Fazer auditoria para PostgreSQL usando pgAudit

MySQL   |  PostgreSQL   |  SQL Server

Nesta página, descrevemos a auditoria de banco de dados usando a extensão pgAudit, que ajuda a configurar muitos dos registros frequentemente necessários para cumprir as certificações governamentais, financeiras e ISO.

Importante: os registros de auditoria são gravados temporariamente no disco da instância, ocupando espaço em disco. O espaço em disco é afetado antes que os registros sejam enviados para o Cloud Logging, conforme descrito nesta página. Portanto, antes de usar esse recurso, consulte a seção Limitações.

Para informações gerais sobre as extensões do PostgreSQL no Cloud SQL, consulte Extensões do PostgreSQL.

Visão geral

A auditoria do banco de dados no Cloud SQL para PostgreSQL está disponível pela extensão pgAudit de código aberto.

Ao usar essa extensão, é possível registrar e rastrear seletivamente as operações SQL realizadas em uma determinada instância de banco de dados. Com a extensão, você tem recursos de auditoria para monitorar e registrar um subconjunto selecionado de operações.

A extensão pgAudit se aplica aos comandos e consultas SQL executados. Por outro lado, os registros de auditoria do Cloud precisam ser usados para auditar operações administrativas e de manutenção realizadas em uma instância do Cloud SQL.

Consulte a página Registros de auditoria para ver mais informações sobre a geração de registros de auditoria no Cloud SQL.

Configurar auditoria de banco de dados no Cloud SQL

Observação: ativar a extensão pgAudit pode aumentar os requisitos de armazenamento de dados se ocorrer uma interrupção do serviço. Para garantir a durabilidade dos registros de pgAudit em caso de problemas inesperados de armazenamento, recomendamos ativar os aumentos automáticos no armazenamento ao usar o pgAudit.

As etapas para a geração de registros de auditoria usando a extensão pgAudit incluem:

1. Como ativar a sinalização cloudsql.enable_pgaudit no Cloud SQL.
2. Como executar um comando para criar a extensão pgAudit.
3. Definir valores para a sinalização pgaudit.log.

Importante: o Cloud SQL não é compatível com o uso do Terraform para criar a extensão pgAudit. Use um cliente psql para criar essa extensão.

O valor padrão da sinalização pgaudit.log é none. Para usar a extensão pgAudit, você precisa definir valores para essa sinalização.

Depois de configurar a auditoria do banco de dados, será possível visualizar os registros e, se necessário, desativar a geração de registros.

Configurar auditoria

Nesta seção, você verá os conceitos básicos da configuração de operações de auditoria do banco de dados.

Sinalização inicial para ativar a auditoria

No Cloud SQL, você usa sinalizações de banco de dados para muitas operações, incluindo o ajuste dos parâmetros do PostgreSQL e a configuração de uma instância. A sinalização cloudsql.enable_pgaudit permite a auditoria para uma determinada instância de banco de dados. É possível alterar o valor da sinalização cloudsql.enable_pgaudit por meio do Console do Cloud ou do comando gcloud.

Observação: alterar o valor da sinalização cloudsql.enable_pgaudit reinicia a instância.

Use as instruções padrão para sinalizações para ativar a sinalização cloudsql.enable_pgaudit, definindo o valor como on. Por exemplo, para usar o comando gcloud, especifique o seguinte, substituindo o nome da instância por [INSTANCE_NAME]:

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

A sinalização cloudsql.enable_pgaudit é listada com as outras sinalizações compatíveis e é específica para o Cloud SQL.

Executar o comando para criar a extensão pgAudit

Depois de ativar a sinalização do banco de dados, execute o comando CREATE EXTENSION usando um cliente psql compatível. O comando a seguir cria a extensão pgAudit para todos os bancos de dados em uma instância do Cloud SQL:

CREATE EXTENSION pgaudit;

Definir valores para a sinalização pgaudit.log

Use as instruções padrão para sinalizações para definir valores para a sinalização pgaudit.log.

Por exemplo, para ativar a auditoria de todas as operações de banco de dados em uma instância, use o seguinte comando gcloud:

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

Definir outras configurações do banco de dados

Para definir as configurações de auditoria do banco de dados, siga os procedimentos na seção Como personalizar a geração de registros de auditoria do banco de dados.

Ver registros de auditoria de banco de dados

Para visualizar os registros de auditoria, ative os registros de auditoria de acesso a dados do seu projeto. Os registros pgAudit gerados para uma determinada instância são enviados para o Cloud Logging como registros de auditoria de acesso a dados. Os usuários podem visualizar os registros pgAudit gerados pelo aplicativo Explorador de registros.

No aplicativo Explorador de registros, os registros pgAudit podem ser visualizados ao selecionar o filtro de registro cloudaudit.googleapis.com/data_access.

Como alternativa, você pode usar a seguinte consulta para mostrar todos os registros pgAuditoria para determinado projeto do Cloud SQL:

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"

Formato de registro de pgAudit

Cada entrada de registro pgAudit nos registros de auditoria de acesso a dados tem campos que representam as informações coletadas para uma consulta.

Exemplo:

{
  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"
    }
  }
}

Veja a seguir as descrições dos campos nos registros de auditoria de acesso a dados:

• auditClass. O tipo da instrução que é registrada. Os valores possíveis são READ, WRITE, FUNCTION, ROLE, DDL, MISC, e MISC_SET.
• auditType. SESSION ou OBJECT.
• chunkCount. A divisão pode ocorrer nos dados fornecidos nos campos parameter e statement. O campo chunkCount indica o número total de partes. Consulte também a descrição do campo chunkIndex.
• chunkIndex. Especifica o número de índice dos blocos de dados nos campos parameter e statement (no contêiner request atual). O número inicial é 1. Consulte também a descrição do campo chunkCount.
• comando. Por exemplo, ALTER TABLE ou SELECT.
• parâmetro. O campo chunkIndex determina o conteúdo desse campo. Veja a descrição do campo chunkIndex. Se o valor de pgaudit.log_parameter estiver definido, o campo parameter conterá os parâmetros de instrução como dados CSV entre aspas. Se não houver parâmetros, este campo conterá [none]. Caso contrário, este campo conterá [not logged].
• instrução. Instrução executada no back-end. O campo chunkIndex determina o conteúdo do campo statement. Veja a descrição do campo chunkIndex.
• statementId. ID de instrução exclusivo para esta sessão. Cada ID de instrução representa uma chamada de back-end. Os IDs de instruções são sequenciais, mesmo que algumas instruções não sejam registradas.
• substatementId. ID sequencial para cada instrução dentro da instrução principal.

Alguns desses campos também estão descritos na documentação de pgAudit (em inglês).

Desativar auditoria

Para desativar a auditoria do banco de dados, defina o valor da sinalização cloudsql.enable_pgaudit como off. O valor pode ser alterado por meio do Console do Google Cloud ou do comando gcloud. Use as instruções padrão para sinalizações para desativar a sinalização cloudsql.enable_pgaudit.

Observação: alterar o valor do atributo cloudsql.enable_pgaudit da sinalização causa uma reinicialização da instância.

Além disso, execute o comando DROP EXTENSION usando um cliente psql compatível para remover o estado da extensão:

DROP EXTENSION pgaudit;

Personalizar a geração de registros de auditoria de banco de dados no Cloud SQL

Nesta seção, você verá maneiras de personalizar o comportamento de auditoria de uma instância de banco de dados.

Importante: antes de usar os procedimentos nesta seção, verifique se você seguiu as etapas para configurar a auditoria do banco de dados, incluindo Como executar o comando para criar a extensão pgAudit.

Para ver outros recursos da extensão, leia a documentação pgAudit (em inglês).

Requisito para privilégios de superusuário

No Cloud SQL, as extensões só podem ser criadas por usuários que fazem parte do papel cloudsqlsuperuser. Quando você cria uma nova instância do PostgreSQL, o usuário padrão do PostgreSQL é criado para você, ainda que seja necessário definir a senha do usuário. O usuário padrão do PostgreSQL faz parte do papel cloudsqlsuperuser. Para mais informações, consulte Usuários do PostgreSQL.

Configurar a auditoria de todas as operações de banco de dados na instância

Para configurar a auditoria de todos os bancos de dados em uma instância, é preciso aplicar as configurações de pgAudit no nível do sistema. Os parâmetros de auditoria no nível do sistema só podem ser definidos como sinalizações do banco de dados por meio do Console do Google Cloud ou do comando gcloud. Por exemplo, para ativar a auditoria de todas as operações de banco de dados em uma instância, use o seguinte comando gcloud:

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

Configurar operações específicas em todos os bancos de dados de instâncias

Para auditoria em todos os bancos de dados da instância, use o Console do Google Cloud ou o comando gcloud. Por exemplo, para ativar a auditoria apenas para operações de leitura e gravação na instância, é possível usar o seguinte comando gcloud. Este exemplo usa uma sintaxe baseada em lista para especificar vários valores:

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

O comando substitui as sinalizações do banco de dados existentes.

Observação: para adicionar novas sinalizações e manter as atuais, especifique os valores de todas as sinalizações. As sinalizações não especificadas são definidas com os valores padrão. Para informações sobre como configurar sinalizações, incluindo por meio do Console do Google Cloud, consulte Como definir uma sinalização do banco de dados.

Configurar auditoria de um banco de dados específico

Para configurar a auditoria de um banco de dados específico, defina os parâmetros pgAudit no nível do banco de dados. Por exemplo, o comando SQL a seguir pode ser usado para ativar a auditoria de leitura/gravação de um banco de dados chamado finance:

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

Configurar a auditoria de uma relação

A auditoria de uma relação é mais restrita do que a auditoria de um banco de dados específico.

Quando você faz auditoria de uma relação, um papel de auditor exclusivo é atribuído ao parâmetro pgaudit.role. Qualquer objeto ou relação concedido a esse papel é registrado.

Por exemplo, para configurar a auditoria de todas as consultas SELECT na relação salary no banco de dados employee, use estes comandos:

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

Também é possível auditar um subconjunto de colunas para uma determinada relação.

Por exemplo, o comando a seguir configura a geração de registros de auditoria para ocorrer somente quando as colunas income e tax_status são acessadas a partir do relacionamento salary:

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

Configurar a auditoria de um usuário do banco de dados

É possível ativar a auditoria de um usuário específico definindo o parâmetro pgaudit.log por nível ROLE.

Por exemplo, o comando SQL a seguir define a auditoria para todas as operações de banco de dados executadas pelo usuário Alice:

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

Dicas para gerenciamento de auditoria no Cloud SQL

Ao personalizar o comportamento da auditoria, lembre-se do seguinte:

• Quando a sinalização do banco de dados cloudsql.enable_pgaudit é desativada, a geração de registros de auditoria é interrompida imediatamente. No entanto, as configurações pgAudit aplicadas (por exemplo, as configurações de parâmetro pgaudit.log) são preservadas, a menos que sejam removidas explicitamente.
• A instância do banco de dados é reiniciada sempre que o valor da sinalização do banco de dados para cloudsql.enable_pgaudit é alterado.
• Os usuários do banco de dados criados por meio de comandos CREATE ROLE explícitos não têm o privilégio para modificar configurações de auditoria. Somente os usuários do banco de dados criados por meio do Console do Cloud e o comando gcloud podem modificar as configurações de auditoria.
• Quando você ativa os registros de auditoria de sessão e de objeto, as instruções relativas a ambos são adicionadas aos registros. Os registros de sessão e objeto não se cancelam nem modificam entre si.

Limitações da extensão pgAudit no Cloud SQL para PostgreSQL

Os registros de auditoria são gravados temporariamente no disco da instância, ocupando espaço em disco antes de serem enviados para o Cloud Logging. Portanto analise todas as informações a seguir antes de usar esse recurso:

• A taxa de ingestão de registros é de 4 MB por segundo. Quando a carga da geração de registros excede a taxa de ingestão, pode ocorrer o seguinte:
  • Pode haver um crescimento indesejado no uso do disco.
  • O espaço em disco pode ser esgotado.
• Se você tiver ativado esse recurso e executar muitas consultas que atendam aos critérios de auditoria, o uso do disco poderá crescer rapidamente.
• Antes de usar esse recurso, planeje:
  • Ativar aumento automático de armazenamento.
  • monitorar o uso geral do disco. O carregamento da geração de registros não pode ser monitorado separadamente. Use a métrica cloudsql.googleapis.com/database/disk/utilization no Metrics Explorer.
  • Se necessário, reduza o uso do disco executando menos consultas ou reduzindo a auditoria.
• Se o espaço disponível em disco estiver esgotado, os registros de auditoria de algumas consultas poderão ser perdidos.