SQL Academy · Lezione

Verificare gli accessi

Tracci chi può vedere cosa

Verificare gli accessi è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Perché registrare gli accessi

Sapere chi ha avuto accesso a quali dati e quando è un pilastro della sicurezza dei database. L'audit crea una traccia affidabile degli eventi, che consente di rilevare accessi non autorizzati, indagare sugli incidenti e soddisfare requisiti di conformità come GDPR, HIPAA o SOC 2.

In questa lezione imparerà a progettare tabelle di audit, acquisire automaticamente gli eventi di accesso con i trigger, usare le funzionalità di logging integrate di PostgreSQL e interrogare la traccia di audit per rispondere alla domanda: chi può vedere cosa?

Progettare una tabella del registro di audit

Il primo passo consiste in una tabella dedicata che registri ogni evento rilevante. Un buon registro di audit memorizza il nome della tabella, il tipo di operazione, i valori precedenti e nuovi, l'utente che ha eseguito l'azione e il timestamp esatto.

L'esempio seguente crea una tabella audit_log generica usando colonne JSONB per memorizzare istantanee delle righe, sufficientemente flessibili da gestire qualsiasi tabella senza modifiche allo schema.

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
);

Registrare l'utente corrente

PostgreSQL mette a disposizione diverse funzioni integrate per identificare chi sta eseguendo una query. current_user restituisce il nome del ruolo attivo dopo qualsiasi SET ROLE. session_user restituisce sempre il ruolo con cui è stato effettuato l'accesso iniziale, indipendentemente dai cambi di ruolo.

Per le applicazioni che usano un singolo ruolo DB condiviso ma trasmettono un utente a livello applicativo tramite SET LOCAL app.current_user, è possibile leggere questa impostazione con 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;

Scrivere una funzione trigger di audit

Una funzione trigger è il modo più affidabile per acquisire gli eventi di modifica dei dati, perché si attiva automaticamente: nessun codice applicativo può aggirarla. La funzione seguente registra ogni INSERT, UPDATE e DELETE eseguito su qualsiasi tabella a cui è associata, memorizzando i valori precedenti e nuovi della riga come JSONB.

Noti l'uso di TG_TABLE_NAME, cioè la tabella che ha attivato il trigger, e di row_to_json() per convertire i valori della riga in un formato memorizzabile.

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;
$$;

Associare il trigger a una tabella

Una volta creata la funzione trigger, la si associa a ogni tabella che si desidera sottoporre ad audit con un'istruzione CREATE TRIGGER. L'uso di AFTER garantisce che i dati siano stati effettivamente scritti prima della creazione della voce di log. La clausola FOR EACH ROW attiva il trigger una volta per ogni riga modificata.

Qui il trigger viene applicato a una tabella ipotetica patients, registrando automaticamente ogni 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();

Registrare le query SELECT

I trigger per le modifiche ai dati acquisiscono solo le operazioni di scrittura. Per sottoporre ad audit l'accesso in lettura serve un approccio diverso. Un'opzione è un trigger a livello di istruzione AFTER SELECT (supportato in PostgreSQL 14+ in determinati contesti). Un approccio più comune consiste nel registrare esplicitamente le letture all'interno di una funzione o di una vista che incapsula la tabella sensibile.

L'esempio seguente incapsula una tabella sensibile in una funzione che registra ogni lettura prima di restituire i risultati.

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;
$$;

Logging integrato di PostgreSQL

Il file postgresql.conf di PostgreSQL offre un potente logging lato server che non richiede codice applicativo. L'impostazione log_min_duration_statement registra qualsiasi query che superi una determinata soglia. Le impostazioni log_connections e log_disconnections registrano chi accede e chi si disconnette.

La query seguente usa la vista di sistema pg_stat_activity per visualizzare le sessioni attive al momento, una forma leggera di monitoraggio degli accessi in tempo reale.

-- 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;

Interrogare il registro di audit

Un registro di audit è utile solo se è possibile interrogarlo in modo efficace. Tra le domande più comuni vi sono: quale utente ha effettuato più di recente l'accesso a un record, quali modifiche ha subito una riga nel tempo e quante letture di dati sensibili sono state eseguite nelle ultime 24 ore.

La query riportata di seguito individua tutti gli utenti che hanno effettuato l'accesso a uno specifico record di un paziente, ordinandoli dal più recente al meno recente.

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;

Rilevare modelli di accesso sospetti

Una volta raccolti i dati di audit, è possibile scrivere query che segnalano le anomalie. Ad esempio, un utente che improvvisamente legge molte più righe del solito, oppure lo stesso record sensibile a più riprese in un breve intervallo di tempo, potrebbe indicare un tentativo di esfiltrazione dei dati.

La query riportata di seguito conta gli eventi SELECT per ogni utente dell'applicazione nell'ultima ora ed evidenzia chi ha letto più di 100 righe.

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;

Proteggere il registro di audit

Un registro di audit che può essere modificato non è affidabile. È necessario limitarne l'accesso in modo che gli utenti ordinari e i ruoli dell'applicazione non possano eliminare né aggiornare le righe. L'approccio più sicuro consiste nel concedere al ruolo dell'applicazione esclusivamente INSERT e nel riservare SELECT a un ruolo di auditor dedicato.

È inoltre possibile imporre l'immutabilità con un trigger che genera un'eccezione se qualcuno tenta di aggiornare o eliminare una riga di audit.

-- 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();

Sottoporre ad audit le decisioni delle policy RLS

Quando è attiva la Row-Level Security, PostgreSQL nasconde silenziosamente le righe invece di generare errori. Di conseguenza, è difficile sapere se un utente ha tentato di leggere una riga che non era autorizzato a visualizzare. Una tecnica consiste nell'aggiungere una policy permissiva che inserisca sempre un record di audit prima che la policy restrittiva filtri le righe.

La query riportata di seguito mostra come verificare quali policy RLS sono presenti in una tabella e a quali ruoli si applicano.

-- 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 delle conoscenze

Verifichi la propria comprensione dell'audit degli accessi in SQL.

Riepilogo della lezione

In questa lezione ha esplorato come creare un sistema completo di audit degli accessi in PostgreSQL:

Tabella del registro di audit — una tabella basata su JSONB che registra chi ha eseguito quale operazione e quando.
Funzione trigger — registra automaticamente gli eventi INSERT, UPDATE e DELETE per qualsiasi tabella a cui è associata, utilizzando TG_TABLE_NAME, row_to_json() e current_user.
Audit delle letture — racchiude le tabelle sensibili in funzioni che registrano gli eventi SELECT prima di restituire i dati.
Monitoraggio integrato — pg_stat_activity mostra le sessioni attive; il logging lato server acquisisce le query senza modifiche al codice.
Rilevamento delle anomalie — le query di aggregazione sul registro di audit possono segnalare volumi di accesso insoliti.
Immutabilità — revoca UPDATE/DELETE sulla tabella di audit e aggiunge un trigger bloccante per impedire manomissioni.

Una traccia di audit ben progettata è lo strumento più affidabile per rispondere alla domanda chi ha avuto accesso a cosa e costituisce la base di qualsiasi indagine di conformità o sicurezza.

Domande Frequenti

La lezione «Verificare gli accessi» è gratuita?

Sì — il testo completo di «Verificare gli accessi» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Verificare gli accessi»?

Tracci chi può vedere cosa Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «Verificare gli accessi»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a SQL Academy