-- $Id: db-schema.sql,v 1.1 2002/03/18 22:43:04 fpmip Exp $

-- Author : Tycho Fruru <tycho@fruru.com>
-- Copyright (C) 2001 Tycho Fruru <tycho@fruru.com>
-- Copyright (C) 2001 Conostix S.A. Luxembourg - Tycho Fruru <tycho@conostix.com>

-- 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.  See <http://www.fsf.org/copyleft/gpl.txt>.
--
-- 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.

-- users give us the possibility of having several sites with grouped
-- agents
create table ipfc_schema
(
	component	text,
	version		text,
	id		text,
	date		timestamp default now()
);

insert into ipfc_schema (component,version,id) 
	values ('db-schema',
		'20020212-01',
		'$Id: db-schema.sql,v 1.1 2002/03/18 22:43:04 fpmip Exp $');

create table users 
(
	login_name 	text 
			    primary key,
	fullname	text,
	email		text,
	creation_date	timestamp 
			    default now(),
	start_date	timestamp 
			    default now(),
	end_date	timestamp 
			    default '9999-01-01',
	user_enabled	boolean 
			    default 't'
);

-- a list of all understood agent types
create table agent_types
(
	type_name 	text 
			    unique,	-- name to use to determine table name
	name		text		-- freeform type description
);

-- Every agent is defined in table agents
-- 
-- agent_url is the url which describes the base directory where that agent's
--     information can be found
-- agent_name is a free-form name, hopefully descriptive for the agent

create table agents
(
	agent_id 	serial
			    primary key,
	agent_type_name text 
			    references agent_types(type_name)
			    not null,
	agent_name 	text,
	agent_location  text,
	agent_enabled	boolean 
			    default 't'
);

create table agent_group_descriptions
(
	group_id	int 
			    primary key,
	group_description text
);

create table agent_groups
(
	group_id	int 
			    references agent_group_descriptions(group_id)
			    not null,
	agent_id	int 
			    references agents(agent_id)
			    not null
);

create table agent_url_ids
(
	agent_id	int 
			    references agents(agent_id)
			    not null,
	agent_url_id	text 
			    not null
);

create table user_rights
(
	login_name	text 
			    references users(login_name)
			    not null,
	agent_id	int 
			    references agents(agent_id)
			    not null,
	can_view	boolean not null
				default 'f',
	can_manage	boolean not null
				default 'f',
	can_annotate	boolean not null
				default 'f',
	can_purgelogs	boolean not null
				default 'f',
	user_enabled	boolean not null
				default 't'
);

-- Table to manage table names for the different event/alert/status categories
create table table_names
(
	table_id	serial,
	agent_type_name text
			    references agent_types(type_name)
			    not null,
	entry_type	text
			    not null,
	entry_subtype	text
			    not null,
	entry_version	int
			    default 0
			    not null,
	entry_transport	text
			    not null,
--	table_name	text
--			    not null,
	transport_function_name
			 text
			    not null, -- name of the function to decapsulate the transport encoding
	function_name	 text
			    not null, -- name of the function to process 1 entry of this format

	unique(agent_type_name,entry_type,entry_subtype,entry_version)
);

create sequence logfile_id_seq;

-- Table to manage logfiles (to see whether logfiles are complete)
create table logfiles
(
	logfile_id	int8
			    default nextval('logfile_id_seq'),
	agent_id	int 
			    references agents(agent_id)
		   	    not null, 	
	wrapper_id	smallint 
			    default 0 
			    not null,	-- number of the wrapper sending events for this agent_id
	generation_id	text
			    not null, 		-- should be increased every time module wrapper is restarted (eg. unix time)
	sequence_id	int
			    not null, 	-- should be increased every time new logfile is sent
	transac_id	text,			-- internal transaction ID, to please Alex ;-]
	agent_time	timestamp,		-- date @ agent
	read_time	timestamp 
			    default now(),
	
	unique(agent_id,wrapper_id,generation_id,sequence_id)
);

create unique index logfiles_logfile_id_idx on logfiles(logfile_id);

--

create table alert_queue
(
	event_id	int8
);
create unique index alert_queue_idx on alert_queue(event_id);

create table alert_queue_finished
(
	event_id	int8
);
create unique index alert_queue_finished_idx on alert_queue_finished(event_id);

--
create sequence event_id_seq;

create table events
(
	event_id 	int8 
			    not null
			    default nextval('event_id_seq'),
	agent_id 	int 
			    references agents(agent_id)
			    not null,
	table_id	int
			    references table_names(table_id)
			    not null,		-- the type of the event
	time 		timestamp
			    default now(),
	logfile_id	int8
			    default currval('logfile_id_seq')
			    not null,
	message		text			-- a (agent type independent) message
);

create unique index events_event_id_index on events(event_id);
create index events_time_index on events(time);
create index events_agent_id_index on events(agent_id);
create index events_logfile_id_index on events(logfile_id);
create index events_message_index on events(message);
--

create table attributes
(
	attr_id		int
			  unique,
	attr_name	text
			  not null
			  unique,
	attr_description
			text,
	attr_text	boolean
			  default 'f',
	attr_int8	boolean
			  default 'f',
	attr_int	boolean
			  default 'f',
	attr_timestamp  boolean
			  default 'f',
	attr_date	boolean
			  default 'f',
	attr_bytea	boolean
			  default 'f',
	attr_numeric	boolean
			  default 'f',
	attr_boolean	boolean
			  default 'f',
	attr_null	boolean
			  default 'f',
	attr_hash	boolean
			  default 'f'
);

create unique index attributes_id on attributes(attr_id);
create unique index attributes_name on attributes(attr_name);


create sequence text_ref_id_seq;
create table text_ref
(
	id		int8
			    not null
			    default nextval('text_ref_id_seq'),
	val		text
);

create unique index text_ref_id  on text_ref(id);
create unique index text_ref_val on text_ref(val);

create table attr_text
(
	event_id	int8
			    not null
			    references events(event_id)
			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id),
--	val		text
	val		int8
			    not null
			    references text_ref(id)
);

create index attr_text_event	  on attr_text(event_id);
create index attr_text_attr	  on attr_text(attr_id);
create index attr_text_event_attr on attr_text(event_id,attr_id);
create index attr_text_attr_val	  on attr_text(attr_id,val);

create table attr_int
(
	event_id	int8
			    not null
			    references events(event_id)
			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id),
	val		int
);

create index attr_int_event	 on attr_int(event_id);
create index attr_int_attr 	 on attr_int(attr_id);
create index attr_int_event_attr on attr_int(event_id,attr_id);
create index attr_int_attr_val 	 on attr_int(attr_id,val);

create table attr_int8
(
	event_id	int8
			    not null
			    references events(event_id)
    			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id),
	val		int
);

create index attr_int8_event 	  on attr_int8(event_id);
create index attr_int8_attr 	  on attr_int8(attr_id);
create index attr_int8_event_attr on attr_int8(event_id,attr_id);
create index attr_int8_attr_val	  on attr_int8(attr_id,val);

create table attr_timestamp
(
	event_id	int8
			    not null
			    references events(event_id)
			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id),
	val		timestamp
);

create index attr_timestamp_event      on attr_timestamp(event_id);
create index attr_timestamp_attr       on attr_timestamp(attr_id);
create index attr_timestamp_event_attr on attr_timestamp(event_id,attr_id);
create index attr_timestamp_attr_val   on attr_timestamp(attr_id,val);

create table attr_date
(
	event_id	int8
			    not null
			    references events(event_id)
			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id),
	val		date
);

create index attr_date_event 	  on attr_date(event_id);
create index attr_date_attr 	  on attr_date(attr_id);
create index attr_date_event_attr on attr_date(event_id,attr_id);
create index attr_date_attr_val	  on attr_date(attr_id,val);

create table attr_bytea
(
	event_id	int8
			    not null
			    references events(event_id)
			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id),
	val		bytea
);

create index attr_bytea_event      on attr_bytea(event_id);
create index attr_bytea_attr       on attr_bytea(attr_id);
create index attr_bytea_event_attr on attr_bytea(event_id,attr_id);

create table attr_boolean
(
	event_id	int8
			    not null
			    references events(event_id)
			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id),
	val		boolean
);

create index attr_boolean_event      on attr_boolean(event_id);
create index attr_boolean_attr       on attr_boolean(attr_id);
create index attr_boolean_event_attr on attr_boolean(event_id,attr_id);

create table attr_numeric
(
	event_id	int8
			    not null
			    references events(event_id)
			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id),
	val		numeric
);

create index attr_numeric_event      on attr_numeric(event_id);
create index attr_numeric_attr       on attr_numeric(attr_id);
create index attr_numeric_event_attr on attr_numeric(event_id,attr_id);

create table attr_null
(
	event_id	int8
			    not null
			    references events(event_id)
			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id)
);

create index attr_null_event	  on attr_null(event_id);
create index attr_null_attr	  on attr_null(attr_id);
create index attr_null_event_attr on attr_null(event_id,attr_id);


create table attr_hash
(
	event_id	int8
			    not null
			    references events(event_id)
			    default currval('event_id_seq'),
	attr_id		int
			    not null
			    references attributes(attr_id),
	key		int8
			    not null,
--			    references text_ref(id),
	val		int8
			    not null
--			    references text_ref(id)
);

create index attr_hash_event	  on attr_hash(event_id);
create index attr_hash_attr       on attr_hash(attr_id);
create index attr_hash_event_attr on attr_hash(event_id,attr_id);



insert into attributes (attr_id,attr_text,attr_name)    values ('1','t','src_host');
insert into attributes (attr_id,attr_int,attr_name)     values ('2','t','ip_proto');
insert into attributes (attr_id,attr_int,attr_name)     values ('3','t','src_port');
insert into attributes (attr_id,attr_text,attr_name)    values ('4','t','dst_host');
insert into attributes (attr_id,attr_int,attr_name)     values ('5','t','dst_port');
insert into attributes (attr_id,attr_text,attr_name)    values ('6','t','firewall_host');
insert into attributes (attr_id,attr_text,attr_name)    values ('7','t','in_interface');
insert into attributes (attr_id,attr_text,attr_name)    values ('8','t','out_interface');
insert into attributes (attr_id,attr_text,attr_name)    values ('9','t','interface');
insert into attributes (attr_id,attr_boolean,attr_name) values ('10','t','accept');
insert into attributes (attr_id,attr_boolean,attr_name) values ('11','t','drop');
insert into attributes (attr_id,attr_boolean,attr_name) values ('12','t','reject');
insert into attributes (attr_id,attr_text,attr_name)    values ('13','t','syslog_date');
insert into attributes (attr_id,attr_text,attr_name)    values ('14','t','host');
insert into attributes (attr_id,attr_text,attr_name)	values ('15','t','logging_program');
insert into attributes (attr_id,attr_int,attr_name)	values ('16','t','process_id');
insert into attributes (attr_id,attr_bytea,attr_name)	values ('17','t','snort_dump');
insert into attributes (attr_id,attr_text,attr_name)	values ('18','t','virtual_host');
insert into attributes (attr_id,attr_text,attr_name)	values ('19','t','remote_logname');
insert into attributes (attr_id,attr_text,attr_name)	values ('20','t','remote_username');
insert into attributes (attr_id,attr_int,attr_name)     values ('21','t','http_status');
insert into attributes (attr_id,attr_int,attr_name)     values ('22','t','bytes_sent');
insert into attributes (attr_id,attr_text,attr_name)    values ('23','t','referer');
insert into attributes (attr_id,attr_text,attr_name)    values ('24','t','user_agent');
insert into attributes (attr_id,attr_text,attr_name)    values ('25','t','url');
insert into attributes (attr_id,attr_text,attr_name)	values ('26','t','syslog_message');
insert into attributes (attr_id,attr_int,attr_name)	values ('27','t','retry_time');
insert into attributes (attr_id,attr_text,attr_name)	values ('28','t','mon_alert_type');
insert into attributes (attr_id,attr_text,attr_name)	values ('29','t','mon_group');
insert into attributes (attr_id,attr_text,attr_name)	values ('30','t','mon_service');
insert into attributes (attr_id,attr_text,attr_name)    values ('31','t','mon_summary');
insert into attributes (attr_id,attr_text,attr_name)    values ('32','t','mon_members');
insert into attributes (attr_id,attr_text,attr_name)	values ('33','t','syslog_facility');
insert into attributes (attr_id,attr_text,attr_name)	values ('34','t','syslog_priority');



/*
create table chkp_log41_1
(
	rule		int,
	info		int8,
	encrypt		boolean,
	remote_username	int8,
	ip_length	int,
	foreign key (agent_id) references agents(agent_id),
	foreign key (logfile_id) references logfiles(logfile_id)	
) inherits (events_firewall);

create table netfilter_log_1
(
	prefix		int8,
	in_mac_addr	int8,
	length		int,
	ip_tos		int,
	ip_prec		int,
	ip_ttl		int,
	ip_id		int,
	ip_ce		boolean default 'f',
	ip_df		boolean default 'f',
	ip_mf		boolean default 'f',
	ip_frag		int,
	ip_opt		int8,
	incomplete_hdr  boolean,
	tcp_seq		int8,	-- int is not good for seq and ack (they are unsigned !)
	tcp_ackseq	int8,
	tcp_window	int,
	tcp_reserved_bits int8,
	tcp_cwr		boolean,
	tcp_ece		boolean,
	tcp_urg		boolean,
	tcp_ack		boolean,
	tcp_psh		boolean,
	tcp_rst		boolean,
	tcp_syn		boolean,
	tcp_fin		boolean,
	tcp_urgp	int8,
	tcp_opt		int8,
	udp_length	int,
	icmp_type	int,
	icmp_code	int,
	ipsec_spi	int8
) inherits (events_firewall);
*/
