Re: Questions about database (PostgreSQL)

["Jason Robertson" <jason () ifutureinc com>]

Why not for postgres at least, use foreign keys.  
As I have to change the create_postgresql file each time 
to tie them in like that.. I use them primarily for cleaning the database.

This is what I have for that though using scheme 102 (I haven't had time to 
change 103, though the changes do get annoying) (I might actually just 
do an update script for this, instead)

-- Copyright (C) 2000 Carnegie Mellon University
--
-- Author(s): Jed Pickel <jed () pickel net>
--            Roman Danyliw <rdd () cert org>, <roman () danyliw com>
--            Todd Schrubb <tls () cert org>
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.

CREATE TABLE schema ( vseq        INT4     NOT NULL,
                      ctime       DATETIME NOT NULL,
                      PRIMARY KEY (vseq));
INSERT INTO schema  (vseq, ctime) VALUES ('102', now());

CREATE TABLE signature ( sig_id       SERIAL NOT NULL,
                         sig_name     TEXT NOT NULL,
                         sig_class_id INT8,
                         PRIMARY KEY (sig_id));
CREATE INDEX sig_name_idx ON signature (sig_name);
CREATE INDEX sig_class_id ON signature (sig_class_id);

CREATE TABLE sig_reference (sig_id  INT4  NOT NULL,
                            ref_seq INT4  NOT NULL,
                            ref_id  INT4  NOT NULL,
                            PRIMARY KEY(sig_id, ref_seq));

CREATE TABLE reference (  ref_id        SERIAL,
                          ref_system_id INT4 NOT NULL,
                          ref_tag       TEXT NOT NULL,
                          PRIMARY KEY (ref_id));

CREATE TABLE reference_system ( ref_system_id   SERIAL,
                                ref_system_name TEXT,
                                PRIMARY KEY (ref_system_id));

CREATE TABLE sig_class ( sig_class_id        INT8   NOT NULL,
                         sig_class_name      TEXT NOT NULL,
                         sig_class_desc      TEXT,
                         sig_class_priority  INT4   NOT NULL,
                         PRIMARY KEY (sig_class_id) );
CREATE INDEX sig_class_name_idx ON sig_class (sig_class_name);
CREATE INDEX sig_class_priority_idx ON sig_class (sig_class_priority);

INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES (1,'not-suspicious','Not 
Suspicious Traffic',0);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES (2,'unknown','Unknown 
Traffic',1);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(3,'bad-unknown','Potentially Bad Traffic', 2);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(4,'attempted-recon','Attempted Information Leak',3);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(5,'successful-recon-limited','Information Leak',4);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(6,'successful-recon-largescale','Large Scale Information Leak',5);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(7,'attempted-dos','Attempted Denial of Service',6);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(8,'successful-dos','Denial of Service',7);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(9,'attempted-user','Attempted User Privilege Gain',8);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(10,'unsuccessful-user','Unsuccessful User Privilege Gain',7);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(11,'successful-user','Successful User Privilege Gain',9);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(12,'attempted-admin','Attempted Administrator Privilege Gain',10);
INSERT INTO sig_class (sig_class_id,sig_class_name,sig_class_desc,sig_class_priority) VALUES 
(13,'successful-admin','Successful Administrator Privilege Gain',11);

CREATE TABLE event  ( sid         INT4 NOT NULL,
                      cid         INT8 NOT NULL,
                      signature   INT4 NOT NULL, 
                      timestamp   DATETIME NOT NULL,
                      PRIMARY KEY (sid,cid));
CREATE INDEX signature_idx ON event (signature);
CREATE INDEX timestamp_idx ON event (timestamp);

-- 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
);
CREATE INDEX ip_src_idx ON iphdr (ip_src);
CREATE INDEX ip_dst_idx ON iphdr (ip_dst);

-- 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
);
CREATE INDEX tcp_sport_idx ON tcphdr (tcp_sport);
CREATE INDEX tcp_dport_idx ON tcphdr (tcp_dport);
CREATE INDEX tcp_flags_idx ON tcphdr (tcp_flags);

-- 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
);
CREATE INDEX udp_sport_idx ON udphdr (udp_sport);
CREATE INDEX udp_dport_idx ON udphdr (udp_dport);

-- 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
);
CREATE INDEX icmp_type_idx ON icmphdr (icmp_type);

-- 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,
                      FOREIGN KEY (sid,cid)
                      REFERENCES event (sid,cid) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 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
create user snort with Password 'password';
grant ALL ON event, sensor,iphdr, tcphdr, udphdr, icmphdr, data, encoding, detail, opt, signature, sig_reference, 
reference, reference_system, sig_class  TO snort;


On 25 Jul 2001, at 22:39, Jed Pickel wrote:

Date sent:              Wed, 25 Jul 2001 22:39:36 -0400
From:                   Jed Pickel <jed () pickel net>
To:                     Deven Phillips <dphillips () viata com>
Copies to:              Snort Users <snort-users () sourceforge net>
Subject:                Re: [Snort-users] Questions about database (PostgreSQL)

> Hey Deven,
>
> If you are using version 1.8 -- a reference to the signature is stored
> in event.signature. That reference maps to signature.sig_id. The bulk
> of the rest of the tables are tied together with the primary key of
> signature id "sid" and count id "cid". Let me know if you have any
> other questions.
>
> I'll put together a diagram this next weekend and put it up on
> www.incident.org/snortdb to make some of the more subtle relations
> more obvious.
>
> Regards,
>
> * Jed
>
> On Wed, Jul 25, 2001 at 04:12:19PM -1000, Deven Phillips wrote:
> > Hi,
> >
> >     I am wondering what the table relationships are inthe Postgres database
> > portion of Snort. I am trying to tie it all together into a PHP/Web
> > front end, and I can't seem to relate a specific event to a specific
> > signature. It all seems to come out a little off. Can anyone help me???



---
Jason Robertson                
Network Analyst            
jason () ifutureinc com    
http://www.astroadvice.com      

_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
http://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users