pgAudit
Auditoria de Logs Open Source PostgreSQL

Introdução

A Extensão de Auditoria PostgreSQL (pgAudit) fornece registro detalhado de auditoria de sessão e/ou objeto por meio do recurso padrão de registro do PostgreSQL.

O objetivo do pgAudit é fornecer aos usuários do PostgreSQL a capacidade de produzir logs de auditoria frequentemente exigidos para cumprir certificações governamentais, financeiras ou ISO.

Uma auditoria é uma inspeção oficial das contas de um indivíduo ou organização, geralmente realizada por um órgão independente. As informações coletadas pelo pgAudit são propriamente chamadas de trilha de auditoria ou log de auditoria. O termo log de auditoria é usado nesta documentação.

Por que pgAudit?

O registro básico de instruções pode ser fornecido pelo recurso padrão de registro com log_statement = all. Isso é aceitável para monitoramento e outros usos, mas não fornece o nível de detalhe geralmente exigido para uma auditoria. Não basta ter uma lista de todas as operações realizadas contra o banco de dados. Também deve ser possível encontrar instruções específicas que sejam de interesse de um auditor. O recurso padrão de registro mostra o que o usuário solicitou, enquanto o pgAudit foca nos detalhes do que aconteceu enquanto o banco de dados estava atendendo à solicitação.

Por exemplo, um auditor pode querer verificar se uma tabela específica foi criada dentro de uma janela de manutenção documentada. Isso pode parecer uma tarefa simples para o grep, mas e se você for apresentado a algo como este exemplo (intencionalmente ofuscado):

DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

O registro padrão fornecerá isto:

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

Parece que encontrar a tabela de interesse pode exigir algum conhecimento do código em casos onde as tabelas são criadas dinamicamente. Isso não é ideal, pois seria preferível pesquisar apenas pelo nome da tabela. É aqui que o pgAudit entra. Para a mesma entrada, ele produzirá esta saída no log:

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

Não apenas o bloco DO é registrado, mas a subinstrução 2 contém o texto completo do CREATE TABLE com o tipo de instrução, tipo de objeto e nome totalmente qualificado para facilitar as buscas.

Ao registrar instruções SELECT e DML, o pgAudit pode ser configurado para registrar uma entrada separada para cada relação referenciada em uma instrução. Nenhuma análise é necessária para encontrar todas as instruções que tocam uma tabela específica. Na verdade, o objetivo é que o texto da instrução seja fornecido principalmente para perícia forense aprofundada e não deve ser necessário para uma auditoria.

Considerações de Uso

Dependendo das configurações, é possível que o pgAudit gere um volume enorme de registros. Tenha cuidado ao determinar exatamente o que precisa ser auditado no seu ambiente para evitar registrar demais.

Por exemplo, ao trabalhar em um ambiente OLAP, provavelmente não seria sensato auditar inserções em uma grande tabela de fatos. O tamanho do arquivo de log será provavelmente muitas vezes o tamanho real dos dados das inserções, pois o arquivo de log é expresso em texto. Como os logs geralmente são armazenados com o sistema operacional, isso pode levar à exaustão do espaço em disco muito rapidamente. Em casos onde não é possível limitar o registro de auditoria a certas tabelas, certifique-se de avaliar o impacto no desempenho durante os testes e alocar bastante espaço no volume de log. Isso também pode ser verdade para ambientes OLTP. Mesmo que o volume de inserções não seja tão alto, o impacto no desempenho do registro de auditoria ainda pode afetar visivelmente a latência.

Para limitar o número de relações auditadas para instruções SELECT e DML, considere usar o registro de auditoria de objeto (veja Auditoria de Objeto). O registro de auditoria de objeto permite a seleção das relações a serem registradas, permitindo a redução do volume geral de logs. No entanto, quando novas relações são adicionadas, elas devem ser explicitamente adicionadas ao registro de auditoria de objeto. Uma solução programática onde tabelas especificadas são excluídas do registro e todas as outras são incluídas pode ser uma boa opção neste caso.

Compatibilidade de Versão do PostgreSQL

O pgAudit suporta PostgreSQL 14 ou superior.

Para suportar novas funcionalidades introduzidas em cada versão do PostgreSQL, o pgAudit mantém um branch separado para cada versão principal do PostgreSQL (atualmente PostgreSQL 14 - 19) que será mantido de maneira similar ao projeto PostgreSQL.

Exceto por correções de bugs, nenhum desenvolvimento adicional é permitido para branches estáveis. Novos desenvolvimentos, se houver, serão estritamente para a próxima versão principal não lançada do PostgreSQL.

As versões do pgAudit se relacionam com as versões principais do PostgreSQL da seguinte forma:

pgAudit v19.X é destinado a suportar PostgreSQL 19.
pgAudit v18.X é destinado a suportar PostgreSQL 18.
pgAudit v17.X é destinado a suportar PostgreSQL 17.
pgAudit v16.X é destinado a suportar PostgreSQL 16.
pgAudit v1.7.X é destinado a suportar PostgreSQL 15.
pgAudit v1.6.X é destinado a suportar PostgreSQL 14.

Compilar e Instalar

O pgAudit pode ser compilado contra uma cópia instalada do PostgreSQL com pacotes de desenvolvimento usando PGXS. As instruções a seguir devem funcionar na maioria dos sistemas operacionais do tipo Unix.

Clone a extensão pgAudit:

git clone https://github.com/pgaudit/pgaudit.git

Altere para o diretório pgAudit:

cd pgaudit

Faça checkout do branch REL_19_STABLE (note que o branch estável pode não existir para versões não lançadas do PostgreSQL):

git checkout REL_19_STABLE

Construa e instale o pgAudit:

make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-19/bin/pg_config

Instruções para testes e desenvolvimento podem ser encontradas em test.

Configurações

As configurações podem ser modificadas apenas por um superusuário. Permitir que usuários normais alterem suas configurações anularia o propósito de um log de auditoria.

As configurações podem ser especificadas globalmente (em postgresql.conf ou usando ALTER SYSTEM ... SET), no nível do banco de dados (usando ALTER DATABASE ... SET) ou no nível da função (usando ALTER ROLE ... SET). Note que as configurações não são herdadas através da herança normal de funções e SET ROLE não alterará as configurações de pgAudit de um usuário. Esta é uma limitação do sistema de funções e não inerente ao pgAudit.

A extensão pgAudit deve ser carregada em shared_preload_libraries. Caso contrário, um erro será levantado no momento do carregamento e nenhum registro de auditoria ocorrerá.

Além disso, CREATE EXTENSION pgaudit deve ser chamado antes que pgaudit.log seja definido para garantir a funcionalidade adequada do pgaudit. A extensão instala gatilhos de evento que adicionam auditoria adicional para DDL. O pgAudit funcionará sem a extensão instalada, mas instruções DDL não terão informações sobre o tipo e nome do objeto.

Se a extensão pgaudit for removida e precisar ser recriada, então pgaudit.log deve ser desdefinido primeiro, caso contrário um erro será levantado.

pgaudit.log

Especifica quais classes de instruções serão registradas pelo registro de auditoria de sessão. Os valores possíveis são:

READ: SELECT e COPY quando a origem é uma relação ou uma consulta.
WRITE: INSERT, UPDATE, DELETE, TRUNCATE e COPY quando o destino é uma relação.
FUNCTION: Chamadas de função e blocos DO.
ROLE: Instruções relacionadas a funções e privilégios: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
DDL: Todo DDL que não está incluído na classe ROLE.
MISC: Comandos diversos, por exemplo, DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
MISC_SET: Comandos SET diversos, por exemplo, SET ROLE.
ALL: Inclui todos os acima.

Múltiplas classes podem ser fornecidas usando uma lista separada por vírgulas e classes podem ser subtraídas prefixando a classe com um sinal - (veja Registro de Auditoria de Sessão).

O padrão é none.

pgaudit.log_catalog

Especifica que o registro de sessão deve ser habilitado no caso em que todas as relações em uma instrução estão em pg_catalog. Desabilitar esta configuração reduzirá o ruído no log de ferramentas como psql e PgAdmin que consultam o catálogo intensamente.

O padrão é on.

pgaudit.log_client

Especifica se as mensagens de log serão visíveis para um processo cliente, como psql. Esta configuração geralmente deve ser deixada desabilitada, mas pode ser útil para depuração ou outros fins.

Note que pgaudit.log_level só é habilitado quando pgaudit.log_client está on.

O padrão é off.

pgaudit.log_level

Especifica o nível de log que será usado para entradas de log (veja Níveis de Severidade de Mensagem para níveis válidos), mas note que ERROR, FATAL e PANIC não são permitidos. Esta configuração é usada para testes de regressão e também pode ser útil para usuários finais para testes ou outros fins.

Note que pgaudit.log_level só é habilitado quando pgaudit.log_client está on; caso contrário, o padrão será usado.

O padrão é log.

pgaudit.log_parameter

Especifica que o registro de auditoria deve incluir os parâmetros que foram passados com a instrução. Quando os parâmetros estão presentes, eles serão incluídos no formato CSV após o texto da instrução.

O padrão é off.

pgaudit.log_parameter_max_size

Especifica que valores de parâmetro mais longos que esta configuração (em bytes) não devem ser registrados, mas substituídos por <long param suppressed>. Isso é definido em bytes, não em caracteres, portanto não leva em conta caracteres multibyte na codificação de um parâmetro de texto. Esta configuração não tem efeito se log_parameter estiver off. Se esta configuração for 0 (o padrão), todos os parâmetros são registrados independentemente do comprimento.

O padrão é 0.

pgaudit.log_relation

Especifica se o registro de auditoria de sessão deve criar uma entrada de log separada para cada relação (TABLE, VIEW, etc.) referenciada em uma instrução SELECT ou DML. Este é um atalho útil para registro exaustivo sem usar o registro de auditoria de objeto.

O padrão é off.

pgaudit.log_rows

Especifica que o registro de auditoria deve incluir o número de linhas recuperadas ou afetadas por uma instrução. Quando habilitado, o campo de linhas será incluído após o campo de parâmetros.

O padrão é off.

pgaudit.log_statement

Especifica se o registro incluirá o texto da instrução e os parâmetros (se habilitado). Dependendo dos requisitos, um log de auditoria pode não precisar disso e torna os logs menos verbosos.

O padrão é on.

pgaudit.log_statement_once

Especifica se o registro incluirá o texto da instrução e os parâmetros com a primeira entrada de log para uma combinação instrução/subinstrução ou com cada entrada. Habilitar esta configuração resultará em um registro menos verboso, mas pode tornar mais difícil determinar a instrução que gerou uma entrada de log, embora o par instrução/subinstrução junto com o id do processo deva ser suficiente para identificar o texto da instrução registrado com uma entrada anterior.

O padrão é off.

pgaudit.role

Especifica a função mestre a ser usada para o registro de auditoria de objeto. Múltiplas funções de auditoria podem ser definidas concedendo-as à função mestre. Isso permite que múltiplos grupos estejam encarregados de diferentes aspectos do registro de auditoria.

Não há valor padrão.

Registro de Auditoria de Sessão

O registro de auditoria de sessão fornece logs detalhados de todas as instruções executadas por um usuário no backend.

Configuração

O registro de sessão é habilitado com a configuração pgaudit.log.

Habilitar o registro de sessão para todo DML e DDL e registrar todas as relações em instruções DML:

set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;

Habilitar o registro de sessão para todos os comandos exceto MISC e levantar mensagens de log de auditoria como NOTICE:

set pgaudit.log = 'all, -misc';
set pgaudit.log_level = notice;

Exemplo

Neste exemplo, o registro de auditoria de sessão é usado para registrar instruções DDL e SELECT. Note que a instrução insert não é registrada, pois a classe WRITE não está habilitada.

SQL:

set pgaudit.log = 'read, ddl';

create table account
(
    id int,
    name text,
    password text,
    description text
);

insert into account (id, name, password, description)
             values (1, 'user1', 'HASH1', 'blah, blah');

select *
    from account;

Saída do Log:

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account
(
    id int,
    name text,
    password text,
    description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
    from account,,<not logged>

Registro de Auditoria de Objeto

O registro de auditoria de objeto registra instruções que afetam uma relação específica. Apenas comandos SELECT, INSERT, UPDATE e DELETE são suportados. TRUNCATE não está incluído no registro de auditoria de objeto.

O registro de auditoria de objeto é destinado a ser uma substituição mais granular para pgaudit.log = 'read, write'. Como tal, pode não fazer sentido usá-los em conjunto, mas um cenário possível seria usar o registro de sessão para capturar cada instrução e, em seguida, complementar isso com o registro de objeto para obter mais detalhes sobre relações específicas.

Configuração

O registro de auditoria em nível de objeto é implementado através do sistema de funções. A configuração pgaudit.role define a função que será usada para o registro de auditoria. Uma relação (TABLE, VIEW, etc.) será auditada quando a função de auditoria tiver permissões para o comando executado ou herdar as permissões de outra função. Isso permite que você tenha efetivamente múltiplas funções de auditoria, mesmo que haja uma única função mestre em qualquer contexto.

Defina pgaudit.role como auditor e conceda privilégios SELECT e DELETE na tabela account. Qualquer instrução SELECT ou DELETE na tabela account agora será registrada:

set pgaudit.role = 'auditor';

grant select, delete
   on public.account
   to auditor;

Exemplo

Neste exemplo, o registro de auditoria de objeto é usado para ilustrar como uma abordagem granular pode ser adotada para o registro de instruções SELECT e DML. Note que o registro na tabela account é controlado por permissões em nível de coluna, enquanto o registro na tabela account_role_map é em nível de tabela.

SQL:

set pgaudit.role = 'auditor';

create table account
(
    id int,
    name text,
    password text,
    description text
);

grant select (password)
   on public.account
   to auditor;

select id, name
  from account;

select password
  from account;

grant update (name, password)
   on public.account
   to auditor;

update account
   set description = 'yada, yada';

update account
   set password = 'HASH2';

create table account_role_map
(
    account_id int,
    role_id int
);

grant select
   on public.account_role_map
   to auditor;

select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id

Saída do Log:

AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password
  from account,<not logged>
AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account
   set password = 'HASH2',<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,
       account_role_map.role_id
  from account
       inner join account_role_map
            on account.id = account_role_map.account_id,<not logged>

Formato

As entradas de auditoria são escritas no recurso padrão de registro e contêm as seguintes colunas em formato separado por vírgula. A saída está em formato CSV compatível apenas se a parte do prefixo da linha de log de cada entrada de log for removida.

AUDIT_TYPE - SESSION ou OBJECT.
STATEMENT_ID - ID de instrução único para esta sessão. Cada ID de instrução representa uma chamada de backend. Os IDs de instrução são sequenciais, mesmo que algumas instruções não sejam registradas. Pode haver múltiplas entradas para um ID de instrução quando mais de uma relação é registrada.
SUBSTATEMENT_ID - ID sequencial para cada subinstrução dentro da instrução principal. Por exemplo, chamar uma função a partir de uma consulta. Os IDs de subinstrução são contínuos, mesmo que algumas subinstruções não sejam registradas. Pode haver múltiplas entradas para um ID de subinstrução quando mais de uma relação é registrada.
CLASS - por exemplo, READ, ROLE (veja pgaudit.log).
COMMAND - por exemplo, ALTER TABLE, SELECT.
OBJECT_TYPE - TABLE, INDEX, VIEW, etc. Disponível para instruções SELECT, DML e a maioria das instruções DDL.
OBJECT_NAME - O nome do objeto totalmente qualificado (por exemplo, public.account). Disponível para instruções SELECT, DML e a maioria das instruções DDL.
STATEMENT - Instrução executada no backend.
PARAMETER - Se pgaudit.log_parameter estiver definido, este campo conterá os parâmetros da instrução como CSV entre aspas ou <none> se não houver parâmetros. Caso contrário, o campo é <not logged>.

Use log_line_prefix para adicionar quaisquer outros campos necessários para atender aos requisitos do seu log de auditoria. Um prefixo de linha de log típico pode ser '%m %u %d [%p]: ' que forneceria a data/hora, nome do usuário, nome do banco de dados e id do processo para cada log de auditoria.

Ressalvas

O registro de auditoria é de melhor esforço e não é transacional. O pgAudit escreve entradas de auditoria através do recurso padrão de registro do PostgreSQL, que não envia cada entrada para o disco de forma síncrona com a transação que a produziu, nem propaga erros de gravação de volta para a sessão. Não há garantia de que uma transação confirmada terá uma entrada de log de auditoria correspondente. Se o servidor falhar ou perder energia, ou o destino do log se tornar indisponível (por exemplo, o volume de log ficar cheio) após uma transação ser confirmada, mas antes que suas entradas de auditoria sejam gravadas de forma durável, essas entradas podem ser perdidas. Por outro lado, uma instrução é registrada quando é executada, portanto uma entrada pode ser gravada mesmo que sua transação seja revertida posteriormente.

Renomeações de objetos são registradas sob o nome para o qual foram renomeadas. Por exemplo, renomear uma tabela produzirá o seguinte resultado:

ALTER TABLE test RENAME TO test2;

AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test RENAME TO test2,<not logged>

É possível que um comando seja registrado mais de uma vez. Por exemplo, quando uma tabela é criada com uma chave primária especificada no momento da criação, o índice da chave primária será registrado independentemente e outro log de auditoria será feito para o índice sob a entrada de criação. No entanto, as múltiplas entradas estarão contidas em um único ID de instrução.

Autovacuum e Autoanalyze não são registrados.

Instruções que são executadas após uma transação entrar em um estado abortado não serão registradas na auditoria. No entanto, a instrução que causou o erro e quaisquer instruções subsequentes executadas na transação abortada serão registradas como ERRORs pelo recurso padrão de registro.

Não é possível auditar superusuários de forma confiável com pgAudit. Uma solução é restringir o acesso a contas de superusuário e usar a extensão set_user para escalar permissões quando necessário.

Autores

A Extensão de Auditoria PostgreSQL é baseada no projeto pgaudit da 2ndQuadrant pgaudit project de autoria de Simon Riggs, Abhijit Menon-Sen e Ian Barwick e submetido como uma extensão ao núcleo do PostgreSQL. Desenvolvimento adicional foi feito por David Steele da Crunchy Data.

Ler mais

pgaudit v19beta3

Categorias

pgaudit v19beta3

pgAudit Auditoria de Logs Open Source PostgreSQL

Introdução

Por que pgAudit?

Considerações de Uso

Compatibilidade de Versão do PostgreSQL

Compilar e Instalar

Configurações

pgaudit.log

pgaudit.log_catalog

pgaudit.log_client

pgaudit.log_level

pgaudit.log_parameter

pgaudit.log_parameter_max_size

pgaudit.log_relation

pgaudit.log_rows

pgaudit.log_statement

pgaudit.log_statement_once

pgaudit.role

Registro de Auditoria de Sessão

Configuração

Exemplo

Registro de Auditoria de Objeto

Configuração

Exemplo

Formato

Ressalvas

Autores

Categorias

pgAudit
Auditoria de Logs Open Source PostgreSQL