[Snort-users] Postgresql database

Jason Robertson jason at ...734...
Mon Oct 30 20:27:49 EST 2000


Okay I was going through, the database file, create_postgresql, one thing I 
noticed is that REFERENCES were not used, and since I am using 
Postgresql 7.0 here, and it does support foreign keys and references, I set 
this up (though it was a pain in the rear)

Well now I have it setup so I can just do
delete from events where timestamp < date('today');

and it will clear the database quickly.. or any other choices I may have..


CREATE TABLE event  ( sid         INT4 NOT NULL,
                      cid         INT8 NOT NULL,
                      signature   TEXT NOT NULL, 
                      timestamp   DATETIME NOT NULL,
                      PRIMARY KEY (sid,cid));

-- store info about the sensor supplying data
CREATE TABLE sensor ( sid         SERIAL,
                      hostname    TEXT,
                      interface   TEXT,
                      filter      TEXT,
                      detail      INT2,
                      encoding    INT2,
                      PRIMARY KEY (sid));

-- All of the fields of an ip header
CREATE TABLE iphdr  ( sid         INT4 NOT NULL, 
                      cid         INT8 NOT NULL,
                      ip_src      INT8 NOT NULL,
                      ip_src0     INT2,
                      ip_src1     INT2,
                      ip_src2     INT2,
                      ip_src3     INT2,
                      ip_dst      INT8 NOT NULL,
                      ip_dst0     INT2,
                      ip_dst1     INT2,
                      ip_dst2     INT2,
                      ip_dst3     INT2,
                      ip_ver      INT2,
                      ip_hlen     INT2,
                      ip_tos      INT2,
                      ip_len      INT4,
                      ip_id       INT4,
                      ip_flags    INT2,
                      ip_off      INT4,
                      ip_ttl      INT2,
                      ip_proto    INT2 NOT NULL,
                      ip_csum     INT4,
                      FOREIGN KEY (sid,cid)
                      REFERENCES event (sid,cid) ON DELETE CASCADE ON 
UPDATE CASCADE
);

-- All of the fields of a tcp header
CREATE TABLE tcphdr(  sid         INT4 NOT NULL,
                      cid         INT8 NOT NULL,
                      tcp_sport   INT4 NOT NULL,
                      tcp_dport   INT4 NOT NULL,
                      tcp_seq     INT8,
                      tcp_ack     INT8,
                      tcp_off     INT2,
                      tcp_res     INT2,
                      tcp_flags   INT2 NOT NULL,
                      tcp_win     INT4,
                      tcp_csum    INT4,
                      tcp_urp     INT4,
                      FOREIGN KEY (sid,cid)
                      REFERENCES event (sid,cid) ON DELETE CASCADE ON 
UPDATE CASCADE
);

-- All of the fields of a udp header
CREATE TABLE udphdr(  sid         INT4 NOT NULL,
                      cid         INT8 NOT NULL,
                      udp_sport   INT4 NOT NULL,
                      udp_dport   INT4 NOT NULL,
                      udp_len     INT4,
                      udp_csum    INT4,
                      FOREIGN KEY (sid,cid)
                      REFERENCES event (sid,cid) ON DELETE CASCADE ON 
UPDATE CASCADE
);

-- All of the fields of an icmp header
CREATE TABLE icmphdr( sid         INT4 NOT NULL,
                      cid         INT8 NOT NULL,
                      icmp_type   INT2 NOT NULL,
                      icmp_code   INT2 NOT NULL,
                      icmp_csum   INT4, 
                      icmp_id     INT4,
                      icmp_seq    INT4,
                      FOREIGN KEY (sid,cid)
                      REFERENCES event (sid,cid) ON DELETE CASCADE ON 
UPDATE CASCADE
);
-- Protocol options
CREATE TABLE opt    ( sid         INT4 NOT NULL,
                      cid         INT8 NOT NULL,
                      optid       INT2 NOT NULL,
                      opt_proto   INT2 NOT NULL,
                      opt_code    INT2 NOT NULL,
                      opt_len     INT4,
                      opt_data    TEXT,
                      PRIMARY KEY (sid,cid,optid));

-- Packet payload
CREATE TABLE data   ( sid          INT4 NOT NULL,
                      cid          INT8 NOT NULL,
                      data_payload TEXT,
                      FOREIGN KEY (sid,cid)
                      REFERENCES event (sid,cid) ON DELETE CASCADE ON 
UPDATE CASCADE
);
-- encoding is a lookup table for storing encoding types
CREATE TABLE encoding(encoding_type INT2 NOT NULL,
                      encoding_text TEXT NOT NULL,
                      PRIMARY KEY (encoding_type));
INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 
'base64');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii');

-- detail is a lookup table for storing different detail levels
CREATE TABLE detail  (detail_type INT2 NOT NULL,
                      detail_text TEXT NOT NULL,
                      PRIMARY KEY (detail_type));
INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast');
INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full');

-- be sure to also use the snortdb-extra tables if you want
-- mappings for tcp flags, protocols, and ports

---
Jason Robertson                
Network Analyst            
jason at ...734...    
http://www.astroadvice.com      



More information about the Snort-users mailing list