Auditando o acesso
Acompanhe quem pode ver cada informação.
Auditando o acesso é uma aula grátis de SQL Academy no CoddyKit. Esta é a aula 4 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de SQL Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de SQL Academy inclui 4 aulas no total.
Por que auditar o acesso
Saber quem acessou quais dados e quando é um pilar fundamental da segurança de bancos de dados. A auditoria cria uma trilha confiável de eventos para que você possa detectar acessos não autorizados, investigar incidentes e atender a requisitos de conformidade, como GDPR, HIPAA ou SOC 2.
Nesta lição, você aprenderá a projetar tabelas de auditoria, capturar eventos de acesso automaticamente com gatilhos, usar os recursos integrados de registro do PostgreSQL e consultar a trilha de auditoria para responder à pergunta: quem pode ver o quê?
Projetando uma tabela de registro de auditoria
O primeiro passo é uma tabela dedicada que registre todos os eventos relevantes. Um bom registro de auditoria armazena o nome da tabela, o tipo de operação, os valores antigos e novos, qual usuário realizou a ação e o horário exato.
O exemplo abaixo cria a tabela de uso geral audit_log, usando colunas JSONB para armazenar instantâneos das linhas — flexível o suficiente para lidar com qualquer tabela sem alterações no esquema.
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
db_user TEXT NOT NULL DEFAULT current_user,
app_user TEXT,
table_name TEXT NOT NULL,
operation TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
row_id BIGINT,
old_data JSONB,
new_data JSONB
);Registrando o usuário atual
O PostgreSQL fornece várias funções integradas para identificar quem está executando uma consulta. current_user retorna o nome do papel em vigor após qualquer SET ROLE. session_user sempre retorna o papel de login original, independentemente da troca de papel.
Para aplicativos que usam um único papel compartilhado do banco de dados, mas transmitem um usuário no nível do aplicativo por meio de SET LOCAL app.current_user, você pode ler essa configuração com current_setting().
-- Who is the database user right now?
SELECT current_user,
session_user;
-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;Escrevendo uma função de gatilho de auditoria
Uma função de gatilho é a maneira mais confiável de capturar eventos de alteração de dados porque é executada automaticamente — nenhum código do aplicativo pode ignorá-la. A função abaixo registra cada INSERT, UPDATE e DELETE em qualquer tabela à qual esteja associada, armazenando os valores antigo e novo da linha como JSONB.
Observe o uso de TG_TABLE_NAME (a tabela que acionou o gatilho) e de row_to_json() para converter os valores da linha em um formato que possa ser armazenado.
CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
INSERT INTO audit_log (
db_user,
app_user,
table_name,
operation,
row_id,
old_data,
new_data
) VALUES (
current_user,
current_setting('app.current_user', true),
TG_TABLE_NAME,
TG_OP,
COALESCE(NEW.id, OLD.id),
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
);
RETURN NULL;
END;
$$;Associando o gatilho a uma tabela
Depois que a função de gatilho existir, você a associa a cada tabela que deseja auditar com uma instrução CREATE TRIGGER. Usar AFTER garante que os dados tenham sido realmente gravados antes que a entrada de registro seja criada. A cláusula FOR EACH ROW executa o gatilho uma vez para cada linha modificada.
Aqui, o gatilho é aplicado a uma tabela hipotética patients, registrando automaticamente cada INSERT, UPDATE e DELETE.
CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();Auditando consultas SELECT
Os gatilhos de alteração de dados capturam apenas operações de escrita. Para auditar o acesso de leitura, você precisa de uma abordagem diferente. Uma opção é um gatilho no nível da instrução AFTER SELECT (com suporte no PostgreSQL 14+ em determinados contextos). Um padrão mais comum é registrar as leituras explicitamente dentro de uma função ou visualização que envolva a tabela confidencial.
O exemplo abaixo envolve uma tabela confidencial em uma função que registra cada leitura antes de retornar os resultados.
CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
-- Log the read access
INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
VALUES (
current_user,
current_setting('app.current_user', true),
'patients',
'SELECT',
p_id
);
RETURN QUERY
SELECT * FROM patients WHERE id = p_id;
END;
$$;Registro integrado do PostgreSQL
O postgresql.conf do PostgreSQL oferece um registro avançado no lado do servidor que não exige código do aplicativo. Definir log_min_duration_statement registra qualquer consulta que exceda um limite. Definir log_connections e log_disconnections registra quem entra e sai.
A consulta abaixo usa a visualização do sistema pg_stat_activity para ver as sessões atualmente ativas — uma forma leve de monitoramento de acesso em tempo real.
-- See who is currently connected and what they are running
SELECT pid,
usename AS db_user,
application_name,
client_addr,
state,
query_start,
LEFT(query, 80) AS current_query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start DESC;Consultando o registro de auditoria
Um registro de auditoria só é valioso se puder ser consultado de forma eficaz. As perguntas comuns incluem: qual usuário acessou um registro mais recentemente, o que mudou em uma linha ao longo do tempo e quantas leituras de dados sensíveis ocorreram nas últimas 24 horas.
A consulta abaixo encontra todos os usuários que acessaram um registro específico de paciente, ordenados do mais recente para o mais antigo.
SELECT event_time,
db_user,
app_user,
operation,
old_data,
new_data
FROM audit_log
WHERE table_name = 'patients'
AND row_id = 42
ORDER BY event_time DESC
LIMIT 20;Detectando padrões de acesso suspeitos
Depois que os dados de auditoria são coletados, você pode escrever consultas que sinalizem anomalias. Por exemplo, um usuário que de repente lê muito mais linhas do que o habitual, ou o mesmo registro sensível acessado várias vezes em um curto intervalo, pode indicar uma tentativa de exfiltração de dados.
A consulta abaixo conta eventos SELECT por usuário da aplicação na última hora e destaca qualquer pessoa que tenha lido mais de 100 linhas.
SELECT app_user,
COUNT(*) AS records_accessed
FROM audit_log
WHERE operation = 'SELECT'
AND event_time >= now() - INTERVAL '1 hour'
GROUP BY app_user
HAVING COUNT(*) > 100
ORDER BY records_accessed DESC;Protegendo o próprio registro de auditoria
Um registro de auditoria que possa ser modificado não é confiável. Você deve restringir seu acesso para que usuários comuns e papéis da aplicação não possam executar DELETE nem UPDATE em linhas. A abordagem mais segura é conceder apenas INSERT ao papel da aplicação e reservar SELECT a um papel de auditoria dedicado.
Você também pode impor a imutabilidade com um gatilho que gera uma exceção se alguém tentar executar UPDATE ou DELETE em uma linha de auditoria.
-- Only the app role may insert; nobody may update or delete
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT INSERT ON audit_log TO app_role;
GRANT SELECT ON audit_log TO auditor_role;
-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'audit_log rows are immutable';
RETURN NULL;
END;
$$;
CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();Auditando decisões de políticas de RLS
Quando a segurança em nível de linha está ativa, o PostgreSQL oculta silenciosamente as linhas em vez de gerar erros. Isso dificulta saber se um usuário tentou ler uma linha que não tinha permissão para ver. Uma técnica é adicionar uma política permissiva que sempre insira um registro de auditoria antes que a política restritiva filtre as linhas.
A consulta abaixo mostra como examinar quais políticas de RLS existem em uma tabela e a quais papéis elas se aplicam.
-- View all RLS policies on the patients table
SELECT polname AS policy_name,
polcmd AS command,
polroles::TEXT AS applies_to,
polqual::TEXT AS using_expression,
polwithcheck::TEXT AS with_check_expression
FROM pg_policy
WHERE polrelid = 'patients'::REGCLASS
ORDER BY polname;Verificação de conhecimento
Teste sua compreensão da auditoria de acesso em SQL.
Revisão da lição
Nesta lição, você explorou como criar um sistema completo de auditoria de acesso no PostgreSQL:
- Tabela do registro de auditoria — uma tabela baseada em JSONB que captura quem fez o quê e quando.
- Função de gatilho — registra automaticamente eventos INSERT, UPDATE e DELETE para qualquer tabela associada usando
TG_TABLE_NAME,row_to_json()ecurrent_user. - Auditoria de leituras — encapsula tabelas sensíveis em funções que registram eventos SELECT antes de retornar os dados.
- Monitoramento integrado —
pg_stat_activitymostra sessões ativas; o registro no servidor captura consultas sem alterações no código. - Detecção de anomalias — consultas agregadas sobre o registro de auditoria podem sinalizar um volume de acesso incomum.
- Imutabilidade — revogue UPDATE/DELETE na tabela de auditoria e adicione um gatilho bloqueador para impedir adulterações.
Um registro de auditoria bem projetado é sua ferramenta mais confiável para responder a quem acessou o quê e constitui a base de qualquer investigação de conformidade ou segurança.
Perguntas Frequentes
A aula “Auditando o acesso” é grátis?
Sim — o texto completo de “Auditando o acesso” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de SQL Academy, atualize para CoddyKit PRO. O curso de SQL Academy inclui 4 aulas no total.
O que vou aprender em “Auditando o acesso”?
Acompanhe quem pode ver cada informação. Você pratica SQL Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.
Preciso ter experiência prévia para começar SQL Academy?
Nenhuma experiência prévia é necessária. SQL Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 4 de 4.
Quanto tempo leva a aula “Auditando o acesso”?
A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.
Posso escrever e executar código nesta aula de SQL Academy?
Sim. Cada aula de SQL Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.
Todas as aulas deste curso
- Funções e privilégios
- Políticas de segurança em nível de linha
- Permissões em nível de coluna
- Auditando o acesso