Thread: [Secureideas-base-devel] schemas
Brought to you by:
secureideas,
sinukas
From: Michael S. <mst...@ma...> - 2005-07-12 22:01:14
|
Here are some ideas I've been banging around with, for a discussion-starter. This schema is mostly like one I've been using for a while, with fairly good results. It's fast in operation, but truely hideous conceptually. :-) It also doesn't work real well with ipv6. It does handle associations between tagged packets and events. CREATE TABLE headers ( sid integer NOT NULL REFERENCES sensors(sid), cid serial NOT NULL PRIMARY KEY, signature integer NOT NULL, "timestamp" timestamp with time zone NOT NULL, ip_src uint32 NOT NULL, ip_dst uint32 NOT NULL, ip_ver uint8, ip_hlen uint8, ip_tos uint8, ip_flags uint8, ip_len uint16, ip_id uint16, ip_off uint16, ip_ttl uint8, ip_proto uint8, ip_csum uint16, sport uint16, dport uint16, seq uint32, len uint32, csum uint16, proto1 uint8, proto2 uint8, proto3 uint8, proto4 uint16, proto5 uint16 ); CREATE TABLE data ( cid integer NOT NULL PRIMARY KEY REFERENCES headers(cid), data_payload text ); CREATE TABLE tagged ( -- cid of tagged packet: cid integer NOT NULL PRIMARY KEY REFERENCES headers(cid), -- cid of event the tagged packet is associated with: tcid integer NOT NULL REFERENCES headers(cid) ); CREATE VIEW event AS SELECT headers.sid, headers.cid, headers.signature, headers."timestamp" FROM headers; CREATE VIEW acid_event AS SELECT h.sid, h.cid, h.signature, s.sig_name, s.sig_class_id, s.sig_priority, h."timestamp", h.ip_src, h.ip_dst, h.ip_proto, h.sport AS layer4_sport, h.dpo rt AS layer4_dport FROM (headers h LEFT JOIN signature s ON ((h.signature = s.sig_id))); There are other views for compatability with the other tables that are in the old schema, I only put a couple in as examples. In my environment 99% of data is tcp, and duplicating a large chunk of data between the acid_event table and the ip & tcp tables increased the memory footprint of the database & created more index housekeeping for no real benefit. That (shoving all packet types into one table) is probably not the right approach for a general schema, but was an easy hack to improve performance without touching the base code. What would be good long-term is a major simplification of the acid_event concept. Especially, having millions of copies of the sig_name in a big database is just silly. What I'd really like to see is the acid_event table go away completely; I had a different schema that abandoned acid_event altogether and replaced it with some UNIONs & JOINs in a view. The (implementation specific) problem with that approach was that postgres had trouble optimizing certain queries against the resultant big, ugly mess. If acid_event was completely abandoned and the JOINs were done as necessary at the application level that approach would perform well and be a lot cleaner than what I have above. Ditching the sid as part of the unique identifier was done as a side effect of referential integrity. If you have RI you can use a sequence for the cid, at which point dragging the sid around is just a waste of space. Chaning the sid handling also offers some flexibility--you can combine multiple physical sensors into a single logical sensor with the same sid. Mike Stone |
From: Kevin J. <kjo...@se...> - 2005-07-17 12:30:03
|
On Tue, 2005-07-12 at 18:00 -0400, Michael Stone wrote: > Here are some ideas I've been banging around with, for a > discussion-starter. This schema is mostly like one I've been using for a > while, with fairly good results. It's fast in operation, but truely > hideous conceptually. :-) It also doesn't work real well with ipv6. It > does handle associations between tagged packets and events.=20 >=20 What type of database are you running this on? > There are other views for compatability with the other tables that are > in the old schema, I only put a couple in as examples. In my environment > 99% of data is tcp, and duplicating a large chunk of data between the > acid_event table and the ip & tcp tables increased the memory footprint > of the database & created more index housekeeping for no real benefit. > That (shoving all packet types into one table) is probably not the right > approach for a general schema, but was an easy hack to improve > performance without touching the base code. > What would be good long-term is a major simplification of the acid_event > concept. Especially, having millions of copies of the sig_name in a big > database is just silly. What I'd really like to see is the acid_event > table go away completely; I had a different schema that abandoned > acid_event altogether and replaced it with some UNIONs & JOINs in a > view. The (implementation specific) problem with that approach was that > postgres had trouble optimizing certain queries against the resultant > big, ugly mess. If acid_event was completely abandoned and the JOINs > were done as necessary at the application level that approach would > perform well and be a lot cleaner than what I have above.=20 >=20 We are looking at the need for ACID_event also. I do not think that it will be part of the final resulting schema.<g> > Ditching the sid as part of the unique identifier was done as a > side effect of referential integrity. If you have RI you can use a > sequence for the cid, at which point dragging the sid around is just a > waste of space. Chaning the sid handling also offers some > flexibility--you can combine multiple physical sensors into a single > logical sensor with the same sid. >=20 This makes sense. One question, why would you want to combine sensors? > Mike Stone Thanks Kevin |
From: Michael S. <ms...@ma...> - 2005-07-17 14:49:51
|
On Sun, Jul 17, 2005 at 08:30:05AM -0400, Kevin Johnson wrote: >What type of database are you running this on? Postgres, but the concepts should hold for any sql-compliant db. >This makes sense. One question, why would you want to combine sensors? Because snort doesn't thread. There's a number of places where I run multiple snort instances on a single smp box (each looking at different traffic), but I'd like to think of all of them as a single logical sensor. Mike Stone |