[POSTGRESQL] Creare una tabella di log come si deve

[Shadow976]

Carissimi,
ho l'intenzione di creare una tabella nel mio database nella quale tutte le azioni svolte dagli utenti (d'ora innanzi con utenti intenderò gli utenti del mio software, non quelli che si connettono al database) vengano tracciate, con data, ora, ip di provenienza e (qualora disponibile, ovvero non nei casi di login con credenziali errate) utente. Vi chiedo qualche consiglio al proposito.

Premetto che sto utilizzando Postgre ma come al solito vi chiederei caldamente di indicarmi soluzioni non vincolate ad un particolare tipo di database in quanto vorrei adattare il mio progetto anche ad altre basi di dati (mySql, DB2 ecc).

1. USO DI TABELLE NASCOSTE O CONNESSIONI DIVERSE DA QUELLE DEL SOFTWARE
O una persona accede direttamente al tool di amministrazione del database con credenziali di amministratore, ed allora può vedere tutto, o una persona usa il mio software, ed allora può vedere solo quello che il mio software le permette di vedere, quindi non certo la tabella di log in questione, la cui vista riserverò solo all'utente administrator del mio programma. Per questo motivo pposso creare questa tabella senza particolari connessioni diverse da quella principale, e con le stesse modalità con le quali ho creato le altre. Condividete questa mia affermazione o secondo le più rigorose regole di sicurezza dovrei agire diversamente?

2. ESEGUIRE LA SUA SCRITTURA DAL MIO PROGRAMMA O DA UN TRIGGER?
Credo che un conto sia chiedere alla mia applicazione, ogni volta in cui esegue una operazione, di inviare una query aggiuntiva al database raddoppiando di fatto il carico del suo lavoro; altro conto sia fare in modo che la base di dati, in automatico, provveda in merito con un bel trigger. Se siete d'accordo con questa mia riflessione, e se vi risulta che i trigger siano presenti nei più importanti database anche diversi da Postgre (DB2, MySql, Sql Server ecc) allora vi chiedo, essendo un pò ignorante in materia, potete indicarmi una function che esegua una query di insert? Non vi chiedo di indicarmi ovviamente la sintassi della query (che naturalmente conosco), né quella del trigger (dal mio tool di amministrazione basta semplicemente selezionare la funzione e flaggare gli eventi per i quali si desidera scatenarla) ma solo della funzione in sé, ovvero qualcosa tipo function (esegui la query che ho già pronta). Purtroppo in PostgreAdmin ho diversi linguaggi a disposizione (SQL, C, PGSQL). Volendo adattare la mia function a diversi db, ho scelto il linguaggio SQL; condividete la mia scelta? Ma se provo anche con una funzione banalissima vedo un messaggio di errore. Sono sicuro, nelle function di un database deve esserci una particolare sintassi che mi sfugge. Come potrei ad esempio scrivere una function che esegua un banale INSERT INTO tbl_prova (nome) VALUES ('Antonio')?

[standard]

1. Non mi farei troppi problemi. A meno che tu non voglia utilizzare quel log per ripristinare la situazione puntuale di un entità in maniera automatica, un log è un dato in read-only.
Anzi, prevedere un tool/mappe per leggere quella tabella è già un iniziativa "lussuosa". In genere un HelpDesk di secondo o terzo livello accede direttamente al DB.

2. Questo tipo di Audit Control secondo me dovrebbe avere le seguenti caratteristiche:
a) essere attivabile/disattivabile per entità
b) essere attivabile/disattivabile in maniera applicativa
c) eventuali errori non dovrebbero compromettere il normale utilizzo dell'applicazione

In progetti medio-ampi l'amministrazione del DB è gestita da un team (DBA administrators) differente da chi sviluppa e gestisce il business (e/o da chi ne fa manutenzione).
Associare questo tipo di funzionalità (del tutto applicativa e non di gestione del DB) all'uso di trigger potrebbe renderti la vita non semplice per le caratteristiche a) e b).
Inoltre, nella mia esperienza, spesso DBA sconsigliano l'uso di trigger (che potrebbero creare lock o sospensione del servizio DB in caso di anomalie) per gestire funzionalità applicative. Quindi anche per la caratteristica c) potrebbe non essere buona idea passare per i trigger

[Shadow976]

Standard, grazie per le tue interessantissime riflessioni. Correggimi se non le ho ben comprese.

1) Per la tabella log posso procedere alla creazione come farei per qualsiasi altra tabella, e posso agire su di essa con la stessa normalissima connessione che uso in tutto il resto del programma.

2) In generale, potendo scegliere trigger si o trigger no sarebbe meglio ove possibile evitarli. Meglio quindi una normale azione di scrittura del log con una query normale, come farei con qualsiasi altra tabella.

Posto ciò, ti domando:

Ad ogni operazione di login al programma sia esso a buon fine o meno, ad ogni operazione sul database, così come ad ogni operazionedi scrittura o modifica sul db (chissà se è il caso di farlo anche per le letture, forse è esagerato?), dovrei ogni volta inviare una query di insert al db. Il carico di lavoro sarebbe raddoppiato rispetto alle normali operazioni! Come snellire questa procedura? Quali le soluzioni adottate dai software più quotati? Forse immagazzinare tali operazioni in una apposita function immagazzinata nel db in modo che il software richiami semplicemente la function e non invii direttamente la query?

[standard]

1) Hai ben compreso
2) Hai ben compreso, ma ci tengo a sottolineare che non è una regola assoluta, solo quello che è frutto della mia esperienza. La stessa che m'ha insegnato che ogni scenario ha le proprie caratteristiche e le proprie soluzioni migliori.

Non mi dilungo in altre riflessioni. Ti riporto invece una soluzione implementata in un gestionale su cui ho lavorato (implementato con standard J2EE, ma i concetti potrebbero essere riadattati nelle tecnologie che usi).

Un audit control semplice non centra molto con le logiche di business, ma è una feature tecnica utile maggiormente all'AM e al data-fix.
Per questo è bene tenere i moduli che li gestiscono, separati quanto più possibile dai moduli applicativi. La strada dei trigger in teoria era(è) una buona soluzione perchè separata dal resto del sofware applicativo (poi, in pratica, ti può creare più problemi che benefici).

Il gestionale di cui ti accennavo utilizzava degli interceptor che intercettavano le operazioni di update e scrivevano su una tabella di audit separata quello che era avvenuto.
Questi interceptor si potevano applicare (o togliere) alle singole entità tramite file di properties. Questo è bene, perchè tramite semplici configurazioni puoi decidere cosa e quando loggare

Le operazioni di lettura non venivano audit-controllate, ma semplicemente loggate su AS. Come hai già intuito questa feature introduce un overhead all'azione di business che è bene tenere basso.

Il tutto dipende ovviamente da quelli che sono i tuoi obiettivi di monitoraggio.

[Shadow976]

La soluzione è quasi trovata.

1) Normale tabella con normale connessione e normale visibilità
2) Nessun uso dei trigger;
3) Per diminuire il carico di lavoro, tracciatura delle sole scritture e modifiche
4) Ovviamente tracciatura anche delle operazioni di login (anche se non a buon fine) e logout dal software
5) Nessuna tracciatura delle operazioni di lettura

Le operazioni di lettura oltre ad essere MOLTO più numerose e quindi a sovraccaricare in modo immane il sistema di log sono anche meno critiche ed importanti; non sono quelle che possono danneggiare il sistema (almeno non tipicamente) e riguardo le letture non autorizzate avrei comunque il log delle operazioni di login e logout (si badi bene, al mio programma e non al database!).

Ciò posto, se condividi, ti chiedo: ho un reale guadagno di prestazioni se memorizzo tale sistema di log in function residenti sul database, da richiamare tramite applicazione ad ogni scrittura / modifica, o a questo punto per semplificare ulteriormente posso limitarmi ad inviare al db una doppia query per ognuna di queste operazioni (ovvero, la classe che invia la query di scrittura dei dati ne invierebbe subito anche una seconda di scrittura sulla tabella log)?

PS (ho parlato in termini un pò volgari; è ovvio che nell'ottica della separazione da te indicata e che condivido in pieno non si invierebbe materialmente una seconda query ma si istanzierebbe una classe, separata dalle altre, che si occuperebbe SOLO della gestione di tale sistema di log. In tale classe, e solo in essa, risiederebbero ovviamente le query di scrittura del log)

[standard]

Originariamente inviato da Shadow976
Ciò posto, se condividi, ti chiedo: ho un reale guadagno di prestazioni se memorizzo tale sistema di log in function residenti sul database, da richiamare tramite applicazione ad ogni scrittura / modifica, o a questo punto per semplificare ulteriormente posso limitarmi ad inviare al db una doppia query per ognuna di queste operazioni (ovvero, la classe che invia la query di scrittura dei dati ne invierebbe subito anche una seconda di scrittura sulla tabella log)?

pro:
1. le funzioni salvate sul DB sono in genere sempre più performanti del resto delle procedure
2. la scelta di usare funzioni salvate sul DB separa notevolmente la gestione dal resto del tuo software (sto supponendo che il resto sia deployato fuori dal DB)

contro:
1. minor portabilità. scrivendo codice sul DB (con caratteristiche proprietarie), il porting del tuo sistema su un altro DB potrebbe significare riscrivere queste funzioni/procedure sul nuovo DB
2. minor flessibilità. Nel caso l'amministrazione del DB sia in gestione ad un gruppo differente da chi sviluppa/manutiene la business application potresti rallentare operazioni di modifica e configurazione di queste procedure
3. maggiore complessità. L'utilizzo di linguaggi e piattaforme differenti per lo sviluppo del tuo software presuppone che tu ed il tuo team abbiate differenti skill di programmazione e sistemi. Oltre al fatto che l'istallazione e la configurazione del tuo applicativo potrebbe complicarsi

[Shadow976]

E, adattando al mio caso:

1. Poiché per me è molto importante sviluppare tenendomi del tutto indipendente dal particolare db utilizzato
2. Poiché stiamo parlando di una query stupidissima in termini di carico di lavoro (scrive una sola, dico una sola riga, di quattro o cinque campi! Se penso a query che ne upgradano migliaia o che leggono liste enormi, o che fanno una select tra miliardi di record mi viene da ridere...), e poiché quindi il vantaggio in tal senso sarebbe irrisorio...

Propendo per non usare una stored procedure. Condividi? Mi basta un tuo cenno ed il quesito può considerarsi concluso. Grazie tantissimo per le tue risposte.

[standard]

Per quel che leggo, condivido.
Ovviamente la conoscenza completa dell'applicativo e dello scenario in cui è calato è tua.

[Shadow976]

Certo, ci mancherebbe. Grazie standard, quesito concluso.