Auditoria de acesso e log das ações dos usuários

Imagina que seu banco de dados é tipo um clube com regras de entrada super rígidas. Só entra quem é da casa, mas a gente quer saber quem chegou, quando, quanto tempo ficou e o que fez lá dentro. É pra isso que serve a auditoria no PostgreSQL. Com ela, dá pra:

1. Monitorar as ações dos usuários. Por exemplo, quem e em que horário executou uma query importante.

2. Detectar atividade suspeita. Pode ser alguém tentando ler dados que não deveria acessar.

3. Cumprir exigências legais e padrões. Em várias áreas (tipo finanças ou saúde) é obrigatório manter uma auditoria detalhada das ações dos usuários.

4. Entender o sistema por dentro. O log ajuda a sacar quais queries rolam mais, onde pode ter gargalo e como otimizar a performance.

Configurando os parâmetros de log

O PostgreSQL deixa você configurar a auditoria das ações usando parâmetros de configuração como log_statement e log_connections. Bora ver como funciona.

1. log_statement: o que vamos registrar?

O parâmetro log_statement define quais queries SQL vão ser gravadas nos logs. Isso é bem útil pra entender o que tá rolando no sistema.

Opções de valor pra log_statement:

• none — não registrar nada (boa sorte pra quem curte viver no limite).
• ddl — registrar só comandos que mudam a estrutura do banco (tipo CREATE TABLE).
• mod — registrar comandos que alteram dados (tipo INSERT, UPDATE, DELETE).
• all — registrar tudo mesmo.

Exemplo de configuração: pra mudar esse parâmetro, você precisa editar o arquivo postgresql.conf:

# Configurando log de todas as queries SQL
log_statement = 'all'

Depois disso, salva as mudanças e reinicia o servidor PostgreSQL:

pg_ctl reload

Agora seu clube vai registrar no log cada ação, desde pedir uma bebida até os passinhos na pista.

2. log_connections: quem tá entrando no clube?

O parâmetro log_connections serve pra registrar nos logs cada nova conexão com o banco. Tem também o log_disconnections, que marca quando a conexão é encerrada.

Exemplo de configuração: De novo, edita o arquivo postgresql.conf:

# Logando conexões
log_connections = on

# Logando desconexões
log_disconnections = on

Pra que serve isso? Por exemplo, você pode ver no log que seu gerente ficou duas horas tentando conectar com a senha errada. Sim, às 3 da manhã.

Analisando os logs: o que dá pra encontrar?

Depois de configurar log_statement e log_connections, o PostgreSQL começa a escrever os logs. Olha um exemplo de como pode ficar um arquivo de log com log_statement = 'mod' ativado:

2023-11-01 12:45:01 UTC [12345] LOG:  connection authorized: user=admin database=university
2023-11-01 12:46:15 UTC [12345] STATEMENT:  INSERT INTO students (name, age) VALUES ('Alice', 22);
2023-11-01 12:47:30 UTC [12345] STATEMENT:  UPDATE students SET age = 23 WHERE name = 'Alice';
2023-11-01 12:48:45 UTC [12345] LOG:  disconnection: session time: 2:45 connection: 1/5

Pontos interessantes:

1. Quem conectou: user=admin database=university — esse é o chefão.
2. O que fez: inseriu uma linha com o nome Alice e atualizou a idade dela.
3. Quando saiu: 12:48:45, depois de dois minutos e meio de atividade.

Exemplos práticos: como usar auditoria na vida real

Bora ver alguns cenários onde auditoria e log podem salvar sua pele.

Cenário 1: monitorando alterações nos dados

Você tá desconfiado que alguém tá mudando registros na tabela students. Olha como configurar a auditoria:

1. Coloca log_statement = 'mod' pra registrar todas as queries que alteram dados.
2. Análise os logs:

cat /var/log/postgresql/postgresql.log | grep "UPDATE students"

Agora dá pra ver quem fez o quê e quando.

Cenário 2: detectando conexões suspeitas

Se aparecerem muitas conexões de IPs diferentes nos logs, pode ser treta. Pra analisar:

1. Olha os logs de conexão (log_connections).
2. Filtra por IP:

cat /var/log/postgresql/postgresql.log | grep "connection authorized"

Cenário 3: analisando a performance das queries

Quer entender por que seu servidor tá lento? Um jeito fácil é ativar log_statement = 'all' por um tempinho (tipo uma hora), pegar os logs e ver onde o servidor tá gastando mais tempo.

Melhores práticas

Não loga tudo sem critério. Configura o log_statement pra registrar só o que importa — DDL e alterações de dados.

Automatiza a análise dos logs. Usa scripts ou ferramentas pra ler e analisar os logs direto, tipo grep, awk ou até ELK Stack (Elasticsearch, Logstash, Kibana).

Gerencie o tamanho dos logs. Configura rotação de logs pelos parâmetros do PostgreSQL ou com ferramentas do sistema operacional (tipo logrotate no Linux).

Espero que agora você se sinta o xerife do seu banco de dados. Log e auditoria não são só proteção, mas também um jeito massa de entender melhor o que tá rolando no seu sistema. Bora ver como usar isso nos projetos de verdade!