New columns in the table 'log_adv_daemon_email': size, pri, relay,

[gnucomo.git] / src / database / create.sql

--*************************************************************************
--  (c) Copyright 2002, De Winter Information Solutions
-- This is free software; you can redistribute it and/or modify it under the
-- terms of the GNU General Public License, see the file COPYING.
--*************************************************************************/
--
-- Gnucomo database creation script.
--
--
-- This SQL script creates the initial tables for the Gnucomo database.
-- Before running this script with 'psql -f', you should have your
-- DBA create the database and give access permissions.
--
--  $Log: create.sql,v $
--  Revision 1.13  2003-03-29 08:27:05  arjen
--  New columns in the table 'log_adv_daemon_email': size, pri, relay,
--  status_details and dsn.
--  Added several indices for the table 'log_adv_daemon_email'.
--
--  Revision 1.12  2003/02/21 08:38:38  arjen
--  Added new table to the database: log_adv_daemon_email.
--
--  Revision 1.11  2003/02/16 08:24:38  arjen
--  Added a new entry to the action table: Notification was displayed in the listing
--
--  Revision 1.10  2003/02/14 06:32:27  arjen
--  Setup the groups and database permissions. The three groups
--  are: view, ops and admin.
--  Added a new entry to the action table.
--
--  Revision 1.9  2003/02/13 08:46:54  arjen
--  Added log, notification and parameter counters to the 'object' table.
--  Counting these things at the time a user interface needs them is
--  too slow. Other programs, like gcm_daemon en gcm_input should prepare
--  these counters for quick retrieval.
--
--  Revision 1.8  2003/02/08 07:36:41  arjen
--  Added new table to the database : log_adv_daemon
--
--  Revision 1.7  2003/02/05 09:29:08  arjen
--  Bug fix: action.statuscode was in upper case.
--
--  Revision 1.6  2003/01/20 07:29:48  arjen
--  Added new tables to the database: parameter_class and parameter_notification
--
--  Revision 1.5  2003/01/18 08:46:48  arjen
--  Added new records to the 'type_of_issue' table.
--  Changed semantics of actionid 9 in the 'action' table.
--
--

CREATE SEQUENCE "action_actionid_seq";

CREATE TABLE "action"
(
        "actionid" bigint DEFAULT nextval('"action_actionid_seq"'::text) NOT NULL,
        "actionname" text,
        "statuscode" character varying(3),
        "description" text
);


COPY "action" FROM stdin;
1       Entry in the system     new     This indicates that a notification has been entered into the system.
2       Display to user opn     The notification has been displayed to the user. It doesn't mean that the user actually read the notification, but he/she should be aware. For that reason we consider the notification to be OPEN
3       Remarks added   pen     Remarks have been added to the notification.
4       Priority changed manually       pen     The priority of the notification has been changed by the user.
5       Priority changed automatically  pen     The priority of the notification has been changed by the system. This can be the result of an action by the user or an automatical escalation.
6       Action taken    pen     An action has been taken.
7       Assignment to user      pen     The notification has been assigned to an user.
8       More information or research needed.    inv     The notification is relevant and will be handled, however more information or research will be needed. For that reason the status has been altered to UNDER INVESTIGATION.
9       Investigation completed pen     Investigation has been done. Information is available to fix the problem.
10      Job output no longer valid      cls     By making a newer job output a valid reference, this data has been obsoleted. Since it was a reference once the output isn't interesting anymore and thus the notification can be closed.
11      Action taken - please verify    cls     An action has been taken and things should have been resolved. Before the notification can be closed a verification has to be done. The new status is now VERIFY.
12      Action not verified     pen     A check has been done and the results were not good. New verification is needed. The status has been changed back to PEN.
13      Action verified cls     A check has been done and the results were approved. The notification has been closed.
15      SMS sent        opn     An SMS has been sent.
14      E-mail sent     opn     An e-mail has been sent.
16      Fax sent        opn     An fax has been sent.
17      Log entries shown       \N      The log entries relevant to the notification have been shown.
18      Notification closed     cls     The notification has been closed.
19      Notification reopend    opn     The notification has been reopend.
20      Redisplayed to user     \N      The notification has been presented to a user. This is not the first time
21      Displayed in list       \N      The notification has been presented in a list with other notifications.
\.

CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);

CREATE UNIQUE INDEX act_pk ON "action" USING btree (actionid);

CREATE UNIQUE INDEX act_actionname ON "action" USING btree (actionname);

CREATE INDEX act_statuscode ON "action" USING btree (statuscode);

SELECT setval ('"action_actionid_seq"', 19, true);

--
--

CREATE SEQUENCE "action_user_actionstepid_seq";

CREATE TABLE "action_user"
(
        "actionstepid" bigint DEFAULT
            nextval('"action_user_actionstepid_seq"'::text) NOT NULL,
        "actionid" bigint,
        "username" text,
        "notificationid" bigint,
        "timestamp" timestamp with time zone,
        "statuscode" character varying(3),
        "remarks" text
);

CREATE UNIQUE INDEX action_user_actionstepid_key ON action_user USING btree (actionstepid);

CREATE INDEX anu_actionid ON action_user USING btree (actionid);

CREATE INDEX anu_username ON action_user USING btree (username);

CREATE INDEX anu_notificationid ON action_user USING btree (notificationid);

CREATE INDEX anu_timestamp ON action_user USING btree ("timestamp");

CREATE INDEX anu_statuscode ON action_user USING btree (statuscode);

SELECT setval ('"action_user_actionstepid_seq"', 1, false);

--
--

CREATE TABLE "db_value"
(
        "setting" text,
        "setting_value" text
);


COPY "db_value" FROM stdin;
db_version      36
gcm_daemon_version      1
log_processing  0
last_notification       0
\.

--
--

CREATE TABLE "history"
(
   paramid            bigint,
   modified           timestamp,
   change_nature      text,       --  CREATED, MODIFIED or REMOVED
   changed_property   text,
   new_value          text,
   remark             text

);

--
--

CREATE SEQUENCE "log_logid_seq";

CREATE TABLE "log"
(
        "logid" bigint DEFAULT nextval('"log_logid_seq"'::text) NOT NULL,
        "objectid" bigint,
        "original_filename" text,
        "servicecode" text,
        "object_timestamp" timestamp with time zone,
        "timestamp" timestamp with time zone,
        "rawdata" text,
        "processed" boolean DEFAULT false,
        "recognized" boolean DEFAULT false
);

CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);

CREATE INDEX log_objectid ON log USING btree (objectid);

CREATE INDEX log_original_filename ON log USING btree (original_filename);

CREATE INDEX log_servicecode ON log USING btree (servicecode);

CREATE INDEX log_object_timestmap ON log USING btree ("timestamp");

CREATE INDEX log_timestmap ON log USING btree ("timestamp");

CREATE INDEX log_timestamp ON log USING btree (object_timestamp);

CREATE INDEX log_processed ON log USING btree (processed);

SELECT setval ('"log_logid_seq"', 1, false);


--
--

CREATE SEQUENCE "log_advid_seq";


CREATE TABLE "log_adv"
(
        "log_advid" bigint DEFAULT
              nextval('"log_advid_seq"'::text) NOT NULL,
        "logid" bigint NOT NULL,
        "detailed_table"    text
);


CREATE INDEX log_adv_logid ON log_adv USING btree (logid);

CREATE UNIQUE INDEX log_adv_log_advid ON log_adv USING btree (log_advid);

SELECT setval ('"log_advid_seq"', 1, false);

CREATE TABLE log_adv_kernel_network
(
       device_in         text,
       device_out        text,
       hw_address        text, 
       source_ip         INET,
       destination_ip    INET,
       packet_length     bigint, 
       tos_bit           text, 
       prec_bit          text,
       ttl               int,
       header_id         bigint,
       source_port       int, 
       destination_port  int,
       body_length       int,
       protocol          text, 
       body_len          int,
       window            text,
       urgp              int,
       syn               boolean DEFAULT false,
       type              int,
       code              int,
       sequence_number   int,
       res               text,
       rst               boolean,
       df                boolean
) INHERITS (log_adv);

CREATE TABLE log_adv_daemon
(
       service TEXT,
       event   TEXT
) INHERITS (log_adv);

CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service);

CREATE TABLE log_adv_daemon_email
(
     source_ip            INET,
     destination_ip       INET,
     internal_messageid   TEXT, 
     external_messageid   TEXT,
     to_email             TEXT,
     from_email           TEXT,
     delay                TIME,
     xdelay               TIME,
     mailer               TEXT,
     status               TEXT,
     pid                  INT,
     size                 INT,
     pri                  INT,
     relay                TEXT,
     status_details       TEXT,
     dsn                  TEXT
) INHERITS (log_adv_daemon);

CREATE INDEX log_adv_daemon_email_s_ip ON log_adv_daemon_email (source_ip);
CREATE INDEX log_adv_daemon_email_d_ip ON log_adv_daemon_email (destination_ip);
CREATE INDEX log_adv_daemon_email_to   ON log_adv_daemon_email (to_email);
CREATE INDEX log_adv_daemon_email_from ON log_adv_daemon_email (from_email);
CREATE INDEX log_adv_email_status      ON log_adv_daemon_email (status);


/*
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
CREATE FUNCTION "funct_processlog" () RETURNS opaque AS '
-- Initial date: September 18th 2002
-- Update: November 13th 2002
-- Author: Brenno J.S.A.A.F. de Winter
-- Abstract: This routine sets a flag
DECLARE
   var_value    VARCHAR;
   var_setting  VARCHAR;

BEGIN

    UPDATE db_value SET setting_value = ''TRUE'' WHERE setting = ''log_flag'';
    RETURN NULL;
END;
 ' LANGUAGE 'plpgsql';


CREATE TRIGGER "log_insert" AFTER INSERT ON "log"  FOR EACH ROW EXECUTE PROCEDURE "funct_processlog" ();
*/
--
--

CREATE TABLE "log_notification"
(
        "notificationid" bigint,
        "logid" bigint
);

CREATE UNIQUE INDEX lon_pk ON log_notification USING btree (notificationid, logid);

CREATE UNIQUE INDEX lon_notificationid ON log_notification USING btree (notificationid);

CREATE UNIQUE INDEX lon_logid ON log_notification USING btree (logid);

--
--

CREATE SEQUENCE "notification_notificationid_seq";

CREATE TABLE "notification"
(
        "notificationid" bigint DEFAULT
            nextval('"notification_notificationid_seq"'::text) NOT NULL,
        "objectid" bigint,
        "type_of_issueid" bigint,
        "timestamp" timestamp with time zone,
        "statuscode" character varying(3),
        "priority" integer,
        "escalation_count_timestamp" timestamp with time zone,
        "repeat_notification_timestamp" timestamp with time zone,
        "securitylevel_view" integer,
        "securitylevel_add" integer,
        "securitylevel_close" integer
);

CREATE UNIQUE INDEX notification_notificationid_key ON notification USING btree (notificationid);

CREATE INDEX not_objectid ON notification USING btree (objectid);

CREATE INDEX not_type_of_issueid ON notification USING btree (type_of_issueid);

CREATE INDEX not_timestamp ON notification USING btree ("timestamp");

CREATE INDEX not_statuscode ON notification USING btree (statuscode);

CREATE INDEX not_priority ON notification USING btree (priority);

CREATE INDEX not_escalation_count_timestamp ON notification USING btree (escalation_count_timestamp);

CREATE INDEX not_repeat_notification_timesta ON notification USING btree (repeat_notification_timestamp);

SELECT setval ('"notification_notificationid_seq"', 1, false);

--
--

CREATE SEQUENCE "object_objectid_seq";

CREATE TABLE "object"
(
        "objectid" bigint DEFAULT nextval('"object_objectid_seq"'::text) NOT NULL,
        "objectname" text,
        "objectcode" text,
        "scp_enabled" boolean,
        "scp_inet" inet,
        "mail_enabled" boolean,
        "mail_from" text,
        "sms_enabled" boolean,
        "sms_number" text,
        "fax_enabled" boolean,
        "fax_number" text,
        "object_description" text,
        "object_owner" text,
        "physical_location" text,
        "timezone" text,
        "remark" text,
        "os"     text,
        "os_version"   text,
        "log_count"    bigint,
        "notification_count"   bigint,
        "parameter_count"      bigint
);

CREATE UNIQUE INDEX object_objectid_key ON object USING btree (objectid);

CREATE UNIQUE INDEX obj_objectname ON object USING btree (objectname);

CREATE UNIQUE INDEX obj_objectcode ON object USING btree (objectcode);

CREATE INDEX obj_mail_from ON object USING btree (mail_from);

CREATE INDEX os ON object (os);

CREATE INDEX os_version ON object (os, os_version);

SELECT setval ('"object_objectid_seq"', 1, false);

--
--

CREATE TABLE "object_issue"
(
        "objectid" bigint,
        "type_of_issueid" bigint,
        "default_priority" integer,
        "escalation" boolean,
        "escalation_time" time without time zone,
        "max_priority" integer,
        "adjust_setting" text
);

CREATE UNIQUE INDEX obj_pk ON object_issue USING btree (objectid, type_of_issueid);

CREATE INDEX obj_objectid ON object_issue USING btree (objectid);

CREATE UNIQUE INDEX obj_type_of_notificationid ON object_issue USING btree (type_of_issueid);

--
--

CREATE TABLE "object_priority"
(
        "objectid" bigint,
        "priorityid" integer,
        "send_mail" boolean,
        "send_sms" boolean,
        "send_fax" boolean,
        "repeat_notification" boolean,
        "interval_for_repeat" time without time zone
);

CREATE UNIQUE INDEX obi_pk ON object_priority USING btree (objectid, priorityid);

CREATE INDEX obi_objectid ON object_priority USING btree (objectid);

CREATE INDEX obi_priorityid ON object_priority USING btree (priorityid);

--
--

CREATE TABLE "object_service"
(
        "objectid" bigint,
        "servicecode" text,
        "expected_interval" bigint,
        "last_entry" timestamp with time zone,
        "default_priority" integer,
        "maximum_priority" integer,
        "accepted" boolean
);

CREATE UNIQUE INDEX obs_pk ON object_service USING btree (objectid, servicecode);

CREATE INDEX obs_objectid ON object_service USING btree (objectid);

CREATE INDEX obs_servicecode ON object_service USING btree (servicecode);

CREATE INDEX obs_accepted ON object_service USING btree (accepted);

--
--

CREATE TABLE "object_user"
(
        "objectid" bigint,
        "username" text,
        "security_level" integer
);

CREATE UNIQUE INDEX ous_pk ON object_user USING btree (objectid, username);

CREATE INDEX ous_objectid ON object_user USING btree (objectid);

CREATE INDEX ous_username ON object_user USING btree (username);

CREATE INDEX ous_security_level ON object_user USING btree (security_level);

--
--

CREATE SEQUENCE "paramid_seq";

CREATE TABLE "parameter"
(
        "paramid" bigint DEFAULT nextval('"paramid_seq"'::text) NOT NULL,
        "objectid" bigint,
        "name" text,
        "class" text,
        "description" text,

        primary key (paramid)
);

CREATE UNIQUE INDEX param_obj_name ON parameter USING btree (objectid, name, class);

SELECT setval ('"paramid_seq"', 1, true);

--
--

CREATE TABLE "parameter_class"
(
   "name"             text,     --  Name of the class: see parameter.class
   "property_name"    text,
   "description"      text,
   "property_type"    text,     --  STATIC or DYNAMIC
   "min"              float,    --  Default minimum value
   "max"              float,    --  Default maximum value
   "notify"           boolean,  --  Notify if something changes ?

   primary key (name, property_name)
);

INSERT INTO parameter_class (name, property_name, description, property_type, notify)
  VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't');

--
--

CREATE TABLE "parameter_notification"
(
   "notificationid" bigint,
   "paramid"        bigint,

   primary key (notificationid, paramid)
);

--
--

CREATE TABLE "priority"
(
        "priority" integer,
        "send_mail" boolean,
        "send_sms" boolean,
        "send_fax" boolean,
        "repeat_notification" boolean,
        "interval_for_repeat" time without time zone
);


CREATE UNIQUE INDEX pri_pk ON priority USING btree (priority);

--
--

CREATE TABLE "property"
(
   paramid bigint,
   name    text,
   value   text,
   type    text,   --   STATIC or DYNAMIC
   min     float,
   max     float,

   primary key (paramid, name)
);

--
--

CREATE TABLE "service"
(
        "servicecode" text,
        "servicename" text,
        "default_priority" integer,
        "max_priority" integer
);

COPY "service" FROM stdin;
httpd   httpd   1       5
imap    imap    1       5
imapd   imapd   1       5
kernel  kernel  1       5
sshd    sshd    1       5
su      su      1       5
syslogd syslogd 1       5
\.

CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode);

CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename);

--

CREATE TABLE "status"
(
        "statuscode" character varying(3),
        "statusname" text,
        "open_notification" boolean,
        "description" text
);


COPY "status" FROM stdin;
new     new entry       t       Just detected, but nothing has been done yet
opn     open notification       t       The notification has been displayed to a user or a user has been notified. However nothing has been done yet.
pen     pending t       The notification is currently being worked on.
ver     waiting for verification        t       The notification has been worked on and is currently awaiting the approval/verification.
rej     rejected        f       The notification has been identified as a false postive and was reject. The notification is now closed
cls     closed  f       The notification has been closed
inv     needs investigation     t       The notification is currently under investigation and is awaiting additional details before one can work on this again.
\.

CREATE UNIQUE INDEX sta_pk ON status USING btree (statuscode);

CREATE UNIQUE INDEX sta_statusname ON status USING btree (statusname);

CREATE INDEX sta_open_notification ON status USING btree (open_notification);

--
--

CREATE TABLE supported_os
(
    os_name text,
    remarks text
);

CREATE UNIQUE INDEX spp_os ON supported_os (os_name);

--
--

CREATE SEQUENCE "type_of_issue_type_of_issue_seq";

CREATE TABLE "type_of_issue"
(
        "type_of_issueid" bigint DEFAULT
           nextval('"type_of_issue_type_of_issue_seq"'::text) NOT NULL,
        "name" text,
        "suggested_priority" text,
        "description" text,
        "active" boolean,
        automated_check       boolean,
        alert_level           int,
        last_run              timestamp,
        recheck_interval      timestamp
);


COPY "type_of_issue" FROM stdin;
1       manual entry    4       A manual entry of a notification        t
2       parameter created       3       A new parameter was created     t
3       property modified       3       The STATIC property of a parameter was modified t
4       parameter removed       3       A parameter was removed t
\.

CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);

CREATE UNIQUE INDEX toi_name ON type_of_issue USING btree (name);

CREATE INDEX toi_active ON type_of_issue USING btree (active);

SELECT setval ('"type_of_issue_type_of_issue_seq"', 4, true);

--
--

CREATE TABLE "usr"
(
        "username" text NOT NULL,
        "active_sessionid" bigint,
        "account_active" boolean,
        "security_level" integer
);

CREATE UNIQUE INDEX usr_username ON usr USING btree (username);

CREATE UNIQUE INDEX usr_active_sessionid ON usr USING btree (active_sessionid);

CREATE INDEX usr_account_active ON usr USING btree (account_active);

CREATE INDEX usr_security_level ON usr USING btree (security_level);

--
--  Set up user groups and grant permissions in the proper places.

CREATE GROUP view;
CREATE GROUP ops;
CREATE GROUP admin;

GRANT SELECT ON action TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON action_user TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON db_value TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON history TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON log TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON log_adv TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON log_adv_daemon TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON log_adv_kernel_network TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON log_notification TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON notification TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON object TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON object_issue TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON object_priority TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON object_service TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON object_user TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON parameter TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON parameter_class TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON parameter_notification TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON priority TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON property TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON service TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON status TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON supported_os TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON type_of_issue TO GROUP view, GROUP ops, GROUP admin;
GRANT SELECT ON usr TO GROUP view, GROUP ops, GROUP admin;

GRANT INSERT ON action_user TO GROUP ops, GROUP admin;
GRANT UPDATE ON notification TO GROUP ops, GROUP admin;
GRANT INSERT ON object GROUP admin;
GRANT UPDATE ON object TO GROUP ops, GROUP admin;
GRANT DELETE ON object GROUP admin;
GRANT UPDATE ON usr TO GROUP view, GROUP ops, GROUP admin;
GRANT INSERT ON usr TO GROUP admin;
GRANT DELETE ON usr TO GROUP admin;