[Snort-devel] database schema: postgresql

Edward Balas ebalas at ...1183...
Tue Mar 5 12:26:08 EST 2002


Hey all,

After looking at the schema as defined for postgresql, I was a bit
suprized to find that there is no referential integrity  present.
Further I have noticed some funny behavior when applicatons(acid)
attempt to delete specific events, ie it takes them 3 or so transactions
for each event.  I had an episode the other day where 56k bogus alerts
found their way into the database... The delete queries as generated by
acid ran for a very long time, before I bailed and tried an alternative
approach.

I ginned up a modified schema that included referential integrety and also
used cascading deletes.  With this schema I am able to delete all events
of a given signature with one transaction, where the deletes cascade from
the signature to all referencing events, even down to the iphdr and other
such tables.  This also means I can delete a specific event and have the
rows in other tables that reference the cid,sid pair automatically
removed.


More information about the Snort-devel mailing list