Salta el contingut
 

Auditoria

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Introducció

L'auditoria d'una base de dades consisteix a registrar les operacions que duen a terme els usuaris dins del sistema gestor. Gràcies a això, l'administrador de la base de dades pot conéixer amb detall com s'utilitza la informació i quines accions fa cada usuari.

Es tracta d'una mesura de seguretat passiva, ja que normalment s'utilitza quan ja s'ha produït un accés indegut o una operació incorrecta, per identificar el responsable o determinar quina acció ha provocat l'error. És a dir, l'auditoria no impedeix aquests accessos o operacions no autoritzades, sinó que permet analitzar què ha ocorregut per aplicar les mesures correctores adequades.

Mitjançant l'auditoria és possible detectar diversos tipus d'anomalies, com ara:

  • Accessos no autoritzats al sistema: accessos d'usuaris que no haurien de tindre permís o que es fan en franges horàries inusuals.
  • Atacs de desbordament de memòria (buffer overflow): consisteixen a introduir més dades de les que un programa espera, cosa que pot permetre a un atacant escriure en zones de memòria no reservades.
  • Injecció de codi SQL: atac que consisteix a inserir ordres no autoritzades dins del codi que executen les aplicacions que accedeixen a la base de dades.

Segons el detall de la informació que es registra, es distingeixen dos nivells d'auditoria:

  • A nivell de sessió: permet obtindre informació de cada sessió, com ara els usuaris connectats a les bases de dades, les taules a les quals accedeixen o el lloc des d'on s'han connectat.
  • A nivell d'objectes: és un nivell més detallat, que permet conéixer els accessos i les operacions efectuades sobre objectes concrets de la base de dades, habitualment taules o columnes.

La informació generada per l'auditoria no s'ha d'emmagatzemar mai en el mateix disc on es troben el sistema operatiu del servidor o el SGBD.

L'opció més segura és utilitzar un disc independent per emmagatzemar la informació d'auditoria o bé sistemes d'emmagatzematge en xarxa.

Auditoria en PostgreSQL

A diferència d'altres SGBD, PostgreSQL no emmagatzema els registres d'auditoria en taules del diccionari de dades, sinó en el fitxer de registre del servidor (server log), en format text (o JSON, CSV…). A més del registre bàsic que ofereix el servidor, per a una auditoria detallada i estructurada s'utilitza habitualment l'extensió pgAudit.

Registre bàsic amb log_statement

La manera més senzilla d'auditar és demanar al servidor que escriga en el registre les sentències executades i les connexions. Això es configura amb uns quants paràmetres de postgresql.conf:

  • log_statement: indica quines sentències s'escriuen en el registre.

    Valors de log_statement
    Valor Significat
    none No es registra cap sentència (valor per defecte)
    ddl Registra les sentències de definició (CREATE, ALTER, DROP…)
    mod Registra ddl més les sentències de modificació de dades (INSERT, UPDATE, DELETE…)
    all Registra totes les sentències

  • log_connections: registra els inicis de sessió.

  • log_disconnections: registra els tancaments de sessió.

Per exemple, aquesta configuració registra les sentències de definició i de modificació, i també les connexions i desconnexions:

log_statement = 'mod'
log_connections = on
log_disconnections = on

Aquests paràmetres són de context sighup, de manera que s'apliquen recarregant la configuració, sense reiniciar el servidor:

SELECT pg_reload_conf();

Documentació oficial: Error Reporting and Logging – PostgreSQL

Auditoria de sessió amb pg_stat_activity

La vista pg_stat_activity permet consultar, en temps real, les sessions actives al servidor:

SELECT pid, usename, client_addr, state, query, query_start
FROM pg_stat_activity;

Aquesta vista no és un historial: només mostra l'estat actual de les connexions.

Extensió pgAudit

pgAudit és una extensió que proporciona una auditoria detallada de sessió i d'objectes. No s'inclou per defecte amb PostgreSQL, de manera que cal instal·lar-la al servidor.

A continuació, cal carregar-la com a biblioteca precarregada del servidor, cosa que requereix reiniciar-lo:

shared_preload_libraries = 'pgaudit'

Finalment, cal habilitar l'extensió a la base de dades:

CREATE EXTENSION pgaudit;

Documentació oficial: pgAudit — PostgreSQL Audit Extension – pgAudit

Auditoria de sessió

El paràmetre pgaudit.log estableix les classes d'operacions que es registren per a totes les sessions. S'hi poden indicar diverses classes separades per comes:

SET pgaudit.log = 'READ, WRITE, ROLE, DDL, FUNCTION, MISC';
Classes de pgaudit.log
Classe Operacions que registra
READ SELECT
WRITE INSERT, UPDATE, DELETE
ROLE GRANT, REVOKE i gestió de rols i usuaris
DDL Sentències DDL no incloses en altres classes
FUNCTION Crides a funcions i blocs DO
MISC Altres ordres (DISCARD, LOCK…)
Exemple: Auditoria de sessió amb pgAudit

Es configura l'auditoria de les operacions de modificació i de definició i es comprova què s'escriu en el registre.

  1. Es configura l'auditoria de les operacions de modificació i de definició:

    SET pgaudit.log = 'WRITE, DDL';

  2. Es crea una taula i s'hi insereix una fila:

    CREATE TABLE audit_table_test (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO audit_table_test (name) VALUES ('Test Name');

  3. Es consulta el registre del servidor (log_directory), on pgAudit escriu una línia per cada operació auditada amb el prefix AUDIT:

    2026-01-15 12:20:45 UTC LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.audit_table_test,"CREATE TABLE audit_table_test (...)",<not logged>
2026-01-15 12:20:46 UTC LOG:  AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.audit_table_test,"INSERT INTO audit_table_test (name) VALUES ('Test Name')",<not logged>

S'observa que cada línia indica la classe (DDL, WRITE), el tipus d'ordre, l'objecte afectat i la sentència completa.

Auditoria d'objectes

L'auditoria d'objectes de pgAudit es basa en el sistema de permisos de PostgreSQL. Es defineix un rol (que no cal que puga iniciar sessió) amb permisos sobre els objectes a auditar, i s'indica aquest rol en el paràmetre pgaudit.role. D'aquesta manera, només es registren les operacions cobertes pels permisos del rol.

CREATE ROLE auditor NOLOGIN;
GRANT INSERT, DELETE ON empleats TO auditor;
SET pgaudit.role = 'auditor';

Amb aquesta configuració, s'auditen únicament les operacions INSERT i DELETE sobre la taula empleats, independentment de l'usuari que les execute.

Auditoria a mida amb triggers

De vegades cal una auditoria de gra fi que va més enllà del que ofereix pgAudit, per exemple, registrar només els canvis d'una columna concreta o amb condicions complexes. En aquests casos, l'alternativa habitual en PostgreSQL és implementar-la amb triggers que escriguen en una taula d'auditoria pròpia (vegeu Triggers).

A més, aquesta opció permet consultar l'historial d'auditoria amb SQL, en lloc d'haver de revisar el fitxer de registre del servidor.

Resum: on trobar la informació d'auditoria en PostgreSQL

Cada mecanisme d'auditoria guarda la informació en un lloc diferent:

Mecanisme On es consulta
log_statement, log_connections Fitxer de registre del servidor (log_directory)
pg_stat_activity Vista de catàleg (estat actual de les sessions)
pgAudit (sessió i objectes) Fitxer de registre del servidor, amb prefix AUDIT
Triggers d'auditoria a mida Taula pròpia, consultable amb SQL
📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez