Auditoria do PostgreSQL com o pgAudit

MySQL   |  PostgreSQL   |  SQL Server

Esta página descreve a auditoria de bases de dados através da extensão pgAudit, que ajuda a configurar muitos dos registos frequentemente necessários para agir em conformidade com as certificações governamentais, financeiras e ISO.

Importante: os registos de auditoria são escritos temporariamente no disco da respetiva instância, ocupando espaço em disco. O espaço em disco é afetado antes de os registos serem enviados para o Cloud Logging, conforme descrito nesta página. Por conseguinte, antes de usar esta funcionalidade, reveja a secção Limitações.

Para obter informações gerais acerca das extensões do PostgreSQL no Cloud SQL, consulte o artigo Extensões do PostgreSQL.

Vista geral

A auditoria de bases de dados no Cloud SQL para PostgreSQL está disponível através da extensão pgAudit de código aberto.

Com esta extensão, pode registar e acompanhar seletivamente as operações SQL realizadas numa determinada instância da base de dados. A extensão oferece-lhe capacidades de auditoria para monitorizar e registar um subconjunto selecionado de operações.

A extensão pgAudit aplica-se a comandos e consultas SQL executados. Por outro lado, os registos de auditoria do Cloud devem ser usados para auditar operações administrativas e de manutenção realizadas numa instância do Cloud SQL.

Consulte a página Registos de auditoria para obter mais informações sobre o registo de auditoria no Cloud SQL.

Configure a auditoria de bases de dados no Cloud SQL

Nota: a ativação da extensão pgAudit pode levar a um aumento dos requisitos de armazenamento de dados se ocorrer uma interrupção do serviço. Para garantir a durabilidade dos registos do pgAudit em caso de problemas de armazenamento inesperados, recomendamos que ative os aumentos automáticos de armazenamento quando usar o pgAudit.

Os passos para o registo de auditoria através da extensão pgAudit incluem:

1. Ativar o sinalizador cloudsql.enable_pgaudit no Cloud SQL.
2. Executar um comando para criar a extensão pgAudit.
3. Definir valores para a flag pgaudit.log.

Importante: o Cloud SQL não suporta a utilização do Terraform para criar a extensão pgAudit. Use um cliente psql para criar esta extensão.

O valor predefinido para a flag pgaudit.log é none. Para usar a extensão pgAudit, tem de definir valores para esta flag.

Depois de configurar a auditoria da base de dados, pode ver os registos e, se necessário, desativar o registo.

Configure a auditoria

Esta secção descreve os princípios básicos da configuração de operações de auditoria de bases de dados.

Sinalização inicial para ativar a auditoria

No Cloud SQL, usa flags de base de dados para muitas operações, incluindo o ajuste dos parâmetros do PostgreSQL e a configuração de uma instância. A flag cloudsql.enable_pgaudit ativa a auditoria para uma determinada instância da base de dados. Pode alterar o valor da flag cloudsql.enable_pgaudit através da consola Google Cloud ou do comando gcloud.

Nota: a alteração do valor da flag cloudsql.enable_pgaudit reinicia a instância.

Use as instruções padrão para flags para ativar a flag 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 flag cloudsql.enable_pgaudit está listada com as outras flags suportadas e é específica do Cloud SQL.

Execute o comando para criar a extensão pgAudit

Depois de ativar a flag da base de dados, execute o comando CREATE EXTENSION com um cliente psql compatível. O comando seguinte cria a extensão pgAudit para todas as bases de dados numa instância do Cloud SQL:

CREATE EXTENSION pgaudit;

Defina valores para a flag pgaudit.log

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

Por exemplo, para ativar a auditoria de todas as operações da base de dados numa instância, pode usar o seguinte comando gcloud:

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

Configure outras definições para a base de dados

Para configurar as definições de auditoria da base de dados, siga os procedimentos descritos na secção Personalizar o registo de auditoria da base de dados.

Veja os registos de auditoria da base de dados

Para ver os registos de auditoria, ative os registos de auditoria de acesso a dados para o seu projeto. Os registos pgAudit gerados para uma determinada instância são enviados para o Cloud Logging como registos de auditoria de acesso a dados. Os utilizadores podem ver os registos pgAudit gerados através da aplicação Logs Explorer.

Na aplicação Explorador de registos, pode ver os registos do pgAudit selecionando o filtro de registos cloudaudit.googleapis.com/data_access.

Em alternativa, pode usar a seguinte consulta para mostrar todos os registos do pgAudit para um 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 registo para pgAudit

Cada entrada do registo pgAudit nos registos de auditoria de acesso a dados tem campos que representam as informações recolhidas para uma consulta.

Vejamos um 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"
    }
  }
}

Seguem-se as descrições dos campos nos registos de auditoria de acesso a dados:

• auditClass. O tipo de declaração registada. Os valores possíveis são READ, WRITE, FUNCTION, ROLE, DDL, MISC e MISC_SET.
• auditType. SESSION ou OBJECT.
• chunkCount. A divisão em partes pode ocorrer nos dados fornecidos nos campos parameter e statement. O campo chunkCount indica o número total de blocos. Consulte também a descrição do campo chunkIndex.
• chunkIndex. Especifica o número de índice dos fragmentos de dados nos campos parameter e statement (no contentor request atual). O número inicial é 1. Consulte também a descrição do campo chunkCount.
• command. Por exemplo, ALTER TABLE ou SELECT.
• parâmetro. O campo chunkIndex pode determinar o conteúdo deste campo. Consulte a descrição do campo chunkIndex. Se o valor de pgaudit.log_parameter estiver definido, o campo parameter pode conter os parâmetros da declaração como dados CSV entre aspas. Se não existirem parâmetros, este campo contém [none]. Caso contrário, este campo contém [not logged].
• declaração. Declaração que foi executada no back-end. O campo chunkIndex pode determinar o conteúdo do campo statement. Consulte a descrição do campo chunkIndex.
• statementId. ID do extrato exclusivo para esta sessão. Cada ID da declaração representa uma chamada de back-end. Os IDs dos extratos são sequenciais, mesmo que alguns extratos não sejam registados.
• substatementId. ID sequencial de cada subdeclaração na declaração principal.

Alguns destes campos também estão descritos na documentação do pgAudit.

Desative a auditoria

Para desativar a auditoria da base de dados, defina o valor da flag cloudsql.enable_pgaudit como off. O valor pode ser alterado através da Google Cloud consola ou do comando gcloud. Use as instruções padrão para flags para desativar a flag cloudsql.enable_pgaudit.

Nota: alterar o valor da flag cloudsql.enable_pgaudit faz com que a instância seja reiniciada.

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

DROP EXTENSION pgaudit;

Personalize o registo de auditoria da base de dados no Cloud SQL

Esta secção descreve formas de personalizar o comportamento de auditoria de uma instância da base de dados.

Importante: antes de usar os procedimentos nesta secção, certifique-se de que seguiu inicialmente os passos para configurar a auditoria da base de dados, incluindo executar o comando para criar a extensão pgAudit.

Para ver capacidades adicionais da extensão, reveja a documentação do pgAudit.

Requisito de privilégios de superutilizador

No Cloud SQL, as extensões só podem ser criadas por utilizadores que façam parte da função cloudsqlsuperuser. Quando cria uma nova instância do PostgreSQL, o utilizador do PostgreSQL predefinido é criado automaticamente (embora tenha de definir a palavra-passe do utilizador). O utilizador predefinido do PostgreSQL faz parte da função cloudsqlsuperuser. Para mais informações, consulte o artigo Utilizadores do PostgreSQL.

Configure a auditoria para todas as operações da base de dados na instância

Para configurar a auditoria para todas as bases de dados numa instância, tem de aplicar as definições do pgAudit ao nível do sistema. Os parâmetros de auditoria ao nível do sistema só podem ser definidos como flags da base de dados através da Google Cloud consola ou do comando gcloud. Por exemplo, para ativar a auditoria de todas as operações da base de dados numa instância, pode usar o seguinte comando gcloud:

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

Configure operações específicas em todas as bases de dados de instâncias

Para a auditoria em todas as bases de dados de instâncias, pode usar aGoogle Cloud consola ou o comando gcloud. Por exemplo, para ativar a auditoria apenas para operações de leitura e escrita na instância, pode usar o seguinte comando gcloud. Este exemplo usa uma sintaxe baseada em listas 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 flags da base de dados existentes.

Nota: para adicionar novas flags e manter as existentes, especifique os valores de todas as flags. Todas as flags não especificadas são definidas para os respetivos valores predefinidos. Para obter informações sobre a definição de flags, inclusive através da Google Cloud consola, consulte Definir uma flag de base de dados.

Configure a auditoria para uma base de dados específica

Para configurar a auditoria para uma base de dados específica, defina os parâmetros do pgAudit ao nível da base de dados. Por exemplo, pode usar o seguinte comando SQL para ativar a auditoria de leitura/escrita para uma base de dados denominada finance:

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

Configure a auditoria para uma relação

A auditoria de uma relação é mais restrita do que a auditoria de uma base de dados específica.

Quando faz uma auditoria de uma relação, é atribuída uma função de auditor exclusiva ao parâmetro pgaudit.role. Qualquer objeto ou relação concedida a esta função é registada.

Por exemplo, para configurar a auditoria de todas as consultas SELECT na relação salary na base de dados employee, pode usar os seguintes comandos:

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

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

Por exemplo, o seguinte comando configura o registo de auditoria para ocorrer apenas quando as colunas income e tax_status são acedidas a partir da relação salary:

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

Configure a auditoria para um utilizador da base de dados

Pode ativar a auditoria para um utilizador específico definindo o parâmetro pgaudit.log ao nível de ROLE.

Por exemplo, o seguinte comando SQL define a auditoria para todas as operações da base de dados executadas pelo utilizador Alice:

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

Sugestões para a gestão de auditorias no Cloud SQL

Quando personalizar o comportamento de auditoria, lembre-se do seguinte:

• Quando a flag da base de dados cloudsql.enable_pgaudit está desativada, o registo de auditoria é interrompido imediatamente. No entanto, as definições de pgAudit aplicadas (por exemplo, as definições de parâmetros pgaudit.log) são preservadas, a menos que sejam removidas explicitamente.
• A instância de base de dados é reiniciada sempre que o valor do sinalizador da base de dados para cloudsql.enable_pgaudit é alterado.
• Os utilizadores da base de dados criados através de comandos CREATE ROLE explícitos não têm o privilégio de modificar as definições de auditoria. Apenas os utilizadores da base de dados criados através da consola e do comando gcloud podem modificar as definições de auditoria. Google Cloud
• Quando ativa o registo de auditoria de sessões e o registo de auditoria de objetos, as declarações relativas a ambos são adicionadas aos registos. O registo de sessões e o registo de objetos não se cancelam nem se modificam mutuamente.

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

Os registos de auditoria são escritos temporariamente no disco da respetiva instância, ocupando espaço no disco antes de serem enviados para o Cloud Logging. Por conseguinte, reveja todas as seguintes informações antes de usar esta funcionalidade:

• A taxa de carregamento de registos é de 4 MB por segundo. Quando o carregamento da geração de registos excede a taxa de carregamento, podem ocorrer os seguintes problemas:
  • Pode ocorrer um crescimento indesejado na utilização do disco.
  • O espaço em disco pode ficar esgotado.
• Se ativou esta funcionalidade e executar muitas consultas que cumprem os seus critérios de auditoria, a utilização do disco pode aumentar demasiado rapidamente.
• Antes de usar esta funcionalidade, planeie:
  • Ative os aumentos automáticos de armazenamento.
  • Monitorize a utilização geral do disco. Não é possível monitorizar separadamente a carga da geração de registos. Use a métrica cloudsql.googleapis.com/database/disk/utilization no explorador de métricas.
  • Se necessário, reduza a utilização do disco executando menos consultas ou reduzindo a auditoria.
• Se o espaço em disco disponível estiver esgotado, os registos de auditoria de algumas consultas podem ser perdidos.