1-- Copyright (C) 2000-2002 Carnegie Mellon University
2--
3-- Maintainer: Roman Danyliw <rdd@cert.org>, <roman@danyliw.com>
4--
5-- Original Author(s): Jed Pickel <jed@pickel.net>    (2000-2001)
6--                     Roman Danyliw <rdd@cert.org>
7--                     Todd Schrubb <tls@cert.org>
8--
9-- This program is free software; you can redistribute it and/or modify
10-- it under the terms of the GNU General Public License Version 2 as
11-- published by the Free Software Foundation.  You may not use, modify or
12-- distribute this program under any other version of the GNU General
13-- Public License.
14--
15-- This program is distributed in the hope that it will be useful,
16-- but WITHOUT ANY WARRANTY; without even the implied warranty of
17-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18-- GNU General Public License for more details.
19--
20-- You should have received a copy of the GNU General Public License
21-- along with this program; if not, write to the Free Software
22-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
23
24CREATE TABLE schema ( vseq        INT4     NOT NULL,
25                      ctime       TIMESTAMP with time zone NOT NULL,
26                      PRIMARY KEY (vseq));
27INSERT INTO schema  (vseq, ctime) VALUES ('107', now());
28
29CREATE TABLE signature ( sig_id       SERIAL NOT NULL,
30                         sig_name     TEXT NOT NULL,
31                         sig_class_id INT8,
32                         sig_priority INT8,
33                         sig_rev      INT8,
34                         sig_sid      INT8,
35                         sig_gid      INT8,
36                         PRIMARY KEY (sig_id));
37CREATE INDEX sig_name_idx ON signature (sig_name);
38CREATE INDEX sig_class_idx ON signature (sig_class_id);
39
40CREATE TABLE sig_reference (sig_id  INT4  NOT NULL,
41                            ref_seq INT4  NOT NULL,
42                            ref_id  INT4  NOT NULL,
43                            PRIMARY KEY(sig_id, ref_seq));
44
45CREATE TABLE reference (  ref_id        SERIAL,
46                          ref_system_id INT4 NOT NULL,
47                          ref_tag       TEXT NOT NULL,
48                          PRIMARY KEY (ref_id));
49
50CREATE TABLE reference_system ( ref_system_id   SERIAL,
51                                ref_system_name TEXT,
52                                PRIMARY KEY (ref_system_id));
53
54CREATE TABLE sig_class ( sig_class_id        SERIAL,
55                         sig_class_name      TEXT NOT NULL,
56                         PRIMARY KEY (sig_class_id) );
57CREATE INDEX sig_class_name_idx ON sig_class (sig_class_name);
58
59CREATE TABLE event  ( sid 	  INT4 NOT NULL,
60                      cid 	  INT8 NOT NULL,
61                      signature   INT4 NOT NULL,
62                      timestamp   timestamp with time zone NOT NULL,
63                      PRIMARY KEY (sid,cid));
64CREATE INDEX signature_idx ON event (signature);
65CREATE INDEX timestamp_idx ON event (timestamp);
66
67-- store info about the sensor supplying data
68CREATE TABLE sensor ( sid	  SERIAL,
69                      hostname    TEXT,
70                      interface   TEXT,
71                      filter	  TEXT,
72                      detail	  INT2,
73                      encoding	  INT2,
74                      last_cid    INT8 NOT NULL,
75                      PRIMARY KEY (sid));
76
77-- All of the fields of an ip header
78CREATE TABLE iphdr  ( sid 	  INT4 NOT NULL,
79                      cid 	  INT8 NOT NULL,
80                      ip_src      INT8 NOT NULL,
81                      ip_dst      INT8 NOT NULL,
82                      ip_ver      INT2,
83                      ip_hlen     INT2,
84                      ip_tos  	  INT2,
85                      ip_len 	  INT4,
86                      ip_id    	  INT4,
87                      ip_flags    INT2,
88                      ip_off      INT4,
89                      ip_ttl   	  INT2,
90                      ip_proto 	  INT2 NOT NULL,
91                      ip_csum 	  INT4,
92                      PRIMARY KEY (sid,cid));
93CREATE INDEX ip_src_idx ON iphdr (ip_src);
94CREATE INDEX ip_dst_idx ON iphdr (ip_dst);
95
96-- All of the fields of a tcp header
97CREATE TABLE tcphdr(  sid 	  INT4 NOT NULL,
98                      cid 	  INT8 NOT NULL,
99                      tcp_sport   INT4 NOT NULL,
100                      tcp_dport   INT4 NOT NULL,
101                      tcp_seq     INT8,
102                      tcp_ack     INT8,
103                      tcp_off     INT2,
104                      tcp_res     INT2,
105                      tcp_flags   INT2 NOT NULL,
106                      tcp_win     INT4,
107                      tcp_csum    INT4,
108                      tcp_urp     INT4,
109                      PRIMARY KEY (sid,cid));
110CREATE INDEX tcp_sport_idx ON tcphdr (tcp_sport);
111CREATE INDEX tcp_dport_idx ON tcphdr (tcp_dport);
112CREATE INDEX tcp_flags_idx ON tcphdr (tcp_flags);
113
114-- All of the fields of a udp header
115CREATE TABLE udphdr(  sid 	  INT4 NOT NULL,
116                      cid 	  INT8 NOT NULL,
117                      udp_sport   INT4 NOT NULL,
118                      udp_dport   INT4 NOT NULL,
119                      udp_len     INT4,
120                      udp_csum    INT4,
121                      PRIMARY KEY (sid,cid));
122CREATE INDEX udp_sport_idx ON udphdr (udp_sport);
123CREATE INDEX udp_dport_idx ON udphdr (udp_dport);
124
125-- All of the fields of an icmp header
126CREATE TABLE icmphdr( sid 	  INT4 NOT NULL,
127                      cid 	  INT8 NOT NULL,
128                      icmp_type   INT2 NOT NULL,
129                      icmp_code   INT2 NOT NULL,
130                      icmp_csum   INT4,
131                      icmp_id     INT4,
132                      icmp_seq    INT4,
133                      PRIMARY KEY (sid,cid));
134CREATE INDEX icmp_type_idx ON icmphdr (icmp_type);
135
136-- Protocol options
137CREATE TABLE opt    ( sid         INT4 NOT NULL,
138                      cid         INT8 NOT NULL,
139                      optid       INT2 NOT NULL,
140                      opt_proto   INT2 NOT NULL,
141                      opt_code    INT2 NOT NULL,
142                      opt_len     INT4,
143                      opt_data    TEXT,
144                      PRIMARY KEY (sid,cid,optid));
145
146-- Packet payload
147CREATE TABLE data   ( sid          INT4 NOT NULL,
148                      cid          INT8 NOT NULL,
149                      data_payload TEXT,
150                      PRIMARY KEY (sid,cid));
151
152-- encoding is a lookup table for storing encoding types
153CREATE TABLE encoding(encoding_type INT2 NOT NULL,
154                      encoding_text TEXT NOT NULL,
155                      PRIMARY KEY (encoding_type));
156INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex');
157INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64');
158INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii');
159
160-- detail is a lookup table for storing different detail levels
161CREATE TABLE detail  (detail_type INT2 NOT NULL,
162                      detail_text TEXT NOT NULL,
163                      PRIMARY KEY (detail_type));
164INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast');
165INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full');
166
167-- be sure to also use the snortdb-extra tables if you want
168-- mappings for tcp flags, protocols, and ports
169