Snort Forums Archive

Archive Home » Snort Development » PostgreSQL concurrency problems

Please note that the categories listed below represent an archived version of our forums pages. To view the current version and be able to post and reply to threads, please register and login here to go to the full forums pages.

[ Notice: Full Version of This Topic ]

PostgreSQL concurrency problems

Posted by ebrownMGBK on December 01, 2005 10:48:14

I think spo_database.c needs modified transaction code for PostgreSQL. I run two instances of snort on one sensor, and they both log to the same database using different sensor_name's. The problem is that some of my events are not being recorded because of concurrency issues and duplication evidenced by these kinds of errors:

snort: database: postgresql_error: ERROR: current transaction is aborted, commands ignored until end of transaction block
snort: database: warning (SELECT sig_id FROM signature WHERE sig_name = 'WEB-MISC SSLv3 invalid Client_Hello attempt' AND sig_rev = 10 AND sig_sid = 2522 ) returned more than one result
snort: database: warning (SELECT ref_id FROM reference WHERE ref_system_id = 4 AND ref_tag = 'www.microsoft.com/technet/security/bulletin/MS04-011.mspx') returned more than one result
snort: database: Unable to insert unknown reference tag ('2004-0120') used in rule.
snort: database: Problem inserting a new signature 'WEB-MISC SSLv3 invalid Client_Hello attempt'
snort: database: Unable to insert the alert reference into the DB
snort: database: Unable to insert unknown reference tag ('12204') used in rule.
snort: database: postgresql_error: ERROR: duplicate key violates unique constraint "sig_reference_pkey"

Upon inspection, there are many duplicated entries in the signatures and reference tables (two of many entries!). I think something like this is happening with new rules:

sensor1 => checks for signature -> NO
sensor2 => checks for signature -> NO
sensor1 => inserts signature -> OK
sensor1 => checks for signature -> OK
sensor2 => inserts signature -> OK
sensor2 => checks for signature -> FAIL (because multiple rows are returned)
sensor2 => rollback (and then i lose the whole alert)

(all of this starts around line 1111 in spo_database.c)

First I was considering using a Serializable transaction (like BEGIN SERIALIZABLE...), but even that would not guarantee that the two instances cooperate. What we really need to do is have an ACCESS EXCLUSIVE lock on the two tables (only when new rules are being inserted?). This would force any concurrant instances to wait for the first instance to complete before performing checks...

Does anyone else agree?

It might have some performance rammifications I suppose. Consider someone trying to use ACID to view signatures or something while snort was inserting a new rule...

Perhaps we should consider some faster hacks in spo_database, perhaps extra checks, or some kind of process controlls like pthreads but on a process level?

Posted by ebrownMGBK on December 01, 2005 11:08:08

While cleaning up, I also noticed that in the signatures table, there are more than just duplicates... there might be something else going on that I'm not aware of.