1 --*************************************************************************
2 -- (c) Copyright 2002, De Winter Information Solutions
3 -- This is free software; you can redistribute it and/or modify it under the
4 -- terms of the GNU General Public License, see the file COPYING.
5 --*************************************************************************/
7 -- Gnucomo database creation script.
10 -- This SQL script creates the initial tables for the Gnucomo database.
11 -- Before running this script with 'psql -f', you should have your
12 -- DBA create the database and give access permissions.
14 -- $Log: create.sql,v $
15 -- Revision 1.16 2003-08-16 14:29:02 arjen
18 -- Revision 1.15 2003/08/05 07:43:24 arjen
19 -- Added index to the history table.
21 -- Revision 1.14 2003/07/09 07:14:59 arjen
22 -- New database tables: notification_check, notification_check_buffer,
23 -- notification_check_line and object_statistics.
25 -- Revision 1.13 2003/03/29 08:27:05 arjen
26 -- New columns in the table 'log_adv_daemon_email': size, pri, relay,
27 -- status_details and dsn.
28 -- Added several indices for the table 'log_adv_daemon_email'.
30 -- Revision 1.12 2003/02/21 08:38:38 arjen
31 -- Added new table to the database: log_adv_daemon_email.
33 -- Revision 1.11 2003/02/16 08:24:38 arjen
34 -- Added a new entry to the action table: Notification was displayed in the listing
36 -- Revision 1.10 2003/02/14 06:32:27 arjen
37 -- Setup the groups and database permissions. The three groups
38 -- are: view, ops and admin.
39 -- Added a new entry to the action table.
41 -- Revision 1.9 2003/02/13 08:46:54 arjen
42 -- Added log, notification and parameter counters to the 'object' table.
43 -- Counting these things at the time a user interface needs them is
44 -- too slow. Other programs, like gcm_daemon en gcm_input should prepare
45 -- these counters for quick retrieval.
47 -- Revision 1.8 2003/02/08 07:36:41 arjen
48 -- Added new table to the database : log_adv_daemon
50 -- Revision 1.7 2003/02/05 09:29:08 arjen
51 -- Bug fix: action.statuscode was in upper case.
53 -- Revision 1.6 2003/01/20 07:29:48 arjen
54 -- Added new tables to the database: parameter_class and parameter_notification
56 -- Revision 1.5 2003/01/18 08:46:48 arjen
57 -- Added new records to the 'type_of_issue' table.
58 -- Changed semantics of actionid 9 in the 'action' table.
62 CREATE SEQUENCE "action_actionid_seq";
66 "actionid" bigint DEFAULT nextval('"action_actionid_seq"'::text) NOT NULL,
68 "statuscode" character varying(3),
73 COPY "action" FROM stdin;
74 1 Entry in the system new This indicates that a notification has been entered into the system.
75 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
76 3 Remarks added pen Remarks have been added to the notification.
77 4 Priority changed manually pen The priority of the notification has been changed by the user.
78 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.
79 6 Action taken pen An action has been taken.
80 7 Assignment to user pen The notification has been assigned to an user.
81 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.
82 9 Investigation completed pen Investigation has been done. Information is available to fix the problem.
83 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.
84 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.
85 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.
86 13 Action verified cls A check has been done and the results were approved. The notification has been closed.
87 15 SMS sent opn An SMS has been sent.
88 14 E-mail sent opn An e-mail has been sent.
89 16 Fax sent opn An fax has been sent.
90 17 Log entries shown \N The log entries relevant to the notification have been shown.
91 18 Notification closed cls The notification has been closed.
92 19 Notification reopend opn The notification has been reopend.
93 20 Redisplayed to user \N The notification has been presented to a user. This is not the first time
94 21 Displayed in list \N The notification has been presented in a list with other notifications.
97 CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);
99 CREATE UNIQUE INDEX act_pk ON "action" USING btree (actionid);
101 CREATE UNIQUE INDEX act_actionname ON "action" USING btree (actionname);
103 CREATE INDEX act_statuscode ON "action" USING btree (statuscode);
105 SELECT setval ('"action_actionid_seq"', 19, true);
110 CREATE SEQUENCE "action_user_actionstepid_seq";
112 CREATE TABLE "action_user"
114 "actionstepid" bigint DEFAULT
115 nextval('"action_user_actionstepid_seq"'::text) NOT NULL,
118 "notificationid" bigint,
119 "timestamp" timestamp with time zone,
120 "statuscode" character varying(3),
124 CREATE UNIQUE INDEX action_user_actionstepid_key ON action_user USING btree (actionstepid);
126 CREATE INDEX anu_actionid ON action_user USING btree (actionid);
128 CREATE INDEX anu_username ON action_user USING btree (username);
130 CREATE INDEX anu_notificationid ON action_user USING btree (notificationid);
132 CREATE INDEX anu_timestamp ON action_user USING btree ("timestamp");
134 CREATE INDEX anu_statuscode ON action_user USING btree (statuscode);
136 SELECT setval ('"action_user_actionstepid_seq"', 1, false);
141 CREATE TABLE "db_value"
148 COPY "db_value" FROM stdin;
158 CREATE TABLE "history"
162 change_nature text, -- CREATED, MODIFIED or REMOVED
163 changed_property text,
169 CREATE INDEX history_pid_mod ON history(paramid, modified);
174 CREATE SEQUENCE "log_logid_seq";
178 "logid" bigint DEFAULT nextval('"log_logid_seq"'::text) NOT NULL,
180 "original_filename" text,
182 "object_timestamp" timestamp with time zone,
183 "timestamp" timestamp with time zone,
185 "processed" boolean DEFAULT false,
186 "recognized" boolean DEFAULT false
189 CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);
191 CREATE INDEX log_objectid ON log USING btree (objectid);
193 CREATE INDEX log_original_filename ON log USING btree (original_filename);
195 CREATE INDEX log_servicecode ON log USING btree (servicecode);
197 CREATE INDEX log_object_timestmap ON log USING btree ("timestamp");
199 CREATE INDEX log_timestmap ON log USING btree ("timestamp");
201 CREATE INDEX log_timestamp ON log USING btree (object_timestamp);
203 CREATE INDEX log_processed ON log USING btree (processed);
205 SELECT setval ('"log_logid_seq"', 1, false);
211 CREATE SEQUENCE "log_advid_seq";
214 CREATE TABLE "log_adv"
216 "log_advid" bigint DEFAULT
217 nextval('"log_advid_seq"'::text) NOT NULL,
218 "logid" bigint NOT NULL,
219 "detailed_table" text
223 CREATE INDEX log_adv_logid ON log_adv USING btree (logid);
225 CREATE UNIQUE INDEX log_adv_log_advid ON log_adv USING btree (log_advid);
227 SELECT setval ('"log_advid_seq"', 1, false);
229 CREATE TABLE log_adv_kernel_network
236 packet_length bigint,
242 destination_port int,
248 syn boolean DEFAULT false,
255 ) INHERITS (log_adv);
257 CREATE TABLE log_adv_daemon
261 ) INHERITS (log_adv);
263 CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service);
265 CREATE TABLE log_adv_daemon_email
269 internal_messageid TEXT,
270 external_messageid TEXT,
283 ) INHERITS (log_adv_daemon);
285 CREATE INDEX log_adv_daemon_email_s_ip ON log_adv_daemon_email (source_ip);
286 CREATE INDEX log_adv_daemon_email_d_ip ON log_adv_daemon_email (destination_ip);
287 CREATE INDEX log_adv_daemon_email_to ON log_adv_daemon_email (to_email);
288 CREATE INDEX log_adv_daemon_email_from ON log_adv_daemon_email (from_email);
289 CREATE INDEX log_adv_email_status ON log_adv_daemon_email (status);
293 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
294 CREATE FUNCTION "funct_processlog" () RETURNS opaque AS '
295 -- Initial date: September 18th 2002
296 -- Update: November 13th 2002
297 -- Author: Brenno J.S.A.A.F. de Winter
298 -- Abstract: This routine sets a flag
305 UPDATE db_value SET setting_value = ''TRUE'' WHERE setting = ''log_flag'';
308 ' LANGUAGE 'plpgsql';
311 CREATE TRIGGER "log_insert" AFTER INSERT ON "log" FOR EACH ROW EXECUTE PROCEDURE "funct_processlog" ();
316 CREATE TABLE "log_notification"
318 "notificationid" bigint,
322 CREATE UNIQUE INDEX lon_pk ON log_notification USING btree (notificationid, logid);
324 CREATE UNIQUE INDEX lon_notificationid ON log_notification USING btree (notificationid);
326 CREATE UNIQUE INDEX lon_logid ON log_notification USING btree (logid);
331 CREATE SEQUENCE "notification_notificationid_seq";
333 CREATE TABLE "notification"
335 "notificationid" bigint DEFAULT
336 nextval('"notification_notificationid_seq"'::text) NOT NULL,
338 "type_of_issueid" bigint,
339 "timestamp" timestamp with time zone,
340 "statuscode" character varying(3),
342 "escalation_count_timestamp" timestamp with time zone,
343 "repeat_notification_timestamp" timestamp with time zone,
344 "securitylevel_view" integer,
345 "securitylevel_add" integer,
346 "securitylevel_close" integer
349 CREATE UNIQUE INDEX notification_notificationid_key ON notification USING btree (notificationid);
351 CREATE INDEX not_objectid ON notification USING btree (objectid);
353 CREATE INDEX not_type_of_issueid ON notification USING btree (type_of_issueid);
355 CREATE INDEX not_timestamp ON notification USING btree ("timestamp");
357 CREATE INDEX not_statuscode ON notification USING btree (statuscode);
359 CREATE INDEX not_priority ON notification USING btree (priority);
361 CREATE INDEX not_escalation_count_timestamp ON notification USING btree (escalation_count_timestamp);
363 CREATE INDEX not_repeat_notification_timesta ON notification USING btree (repeat_notification_timestamp);
365 SELECT setval ('"notification_notificationid_seq"', 1, false);
370 CREATE SEQUENCE checkid_seq;
372 CREATE TABLE notification_check
374 checkid bigint DEFAULT nextval('checkid_seq'::text) NOT NULL,
377 time_between_executions INTERVAL,
378 last_execution DATETIME,
379 execution_counter BIGINT,
380 notificationcounter BIGINT,
381 decreasinglist BOOLEAN default false,
382 type_of_issueid BIGINT
385 CREATE UNIQUE INDEX not_check_checkid ON notification_check (checkid);
387 CREATE UNIQUE INDEX not_check_checkname ON notification_check (checkname);
389 CREATE INDEX not_check_check_lastexec ON notification_check (last_execution);
394 CREATE TABLE notification_check_buffer
402 CREATE INDEX notcheckbuffer_checkid ON notification_check_buffer(checkid);
404 CREATE INDEX notcheckbuffer_sort ON notification_check_buffer(sortorder);
406 CREATE INDEX notcheckbuffer_pid ON notification_check_buffer(pid);
408 CREATE INDEX notcheckbuffer_logid ON notification_check_buffer(logid);
413 CREATE SEQUENCE checklineid_seq;
415 CREATE TABLE notification_check_line
417 checklineid BIGINT DEFAULT nextval('checklineid_seq'::text) NOT NULL,
420 last_logid BIGINT default 0,
421 historicboundary INTERVAL default '0',
422 use_logid BOOLEAN default false,
426 CREATE UNIQUE INDEX notcheckline_checklineid ON notification_check_line (checklineid);
428 CREATE INDEX notcheckline_checkid ON notification_check_line (checkid);
430 CREATE INDEX notcheckline_sort ON notification_check_line (sortorder);
432 CREATE INDEX notcheckline_check_sort ON notification_check_line (checkid, sortorder);
437 CREATE SEQUENCE "object_objectid_seq";
439 CREATE TABLE "object"
441 "objectid" bigint DEFAULT nextval('"object_objectid_seq"'::text) NOT NULL,
444 "scp_enabled" boolean,
446 "mail_enabled" boolean,
448 "sms_enabled" boolean,
450 "fax_enabled" boolean,
452 "object_description" text,
454 "physical_location" text,
460 "notification_count" bigint,
461 "parameter_count" bigint
464 CREATE UNIQUE INDEX object_objectid_key ON object USING btree (objectid);
466 CREATE UNIQUE INDEX obj_objectname ON object USING btree (objectname);
468 CREATE UNIQUE INDEX obj_objectcode ON object USING btree (objectcode);
470 CREATE INDEX obj_mail_from ON object USING btree (mail_from);
472 CREATE INDEX os ON object (os);
474 CREATE INDEX os_version ON object (os, os_version);
476 SELECT setval ('"object_objectid_seq"', 1, false);
481 CREATE TABLE "object_issue"
484 "type_of_issueid" bigint,
485 "default_priority" integer,
486 "escalation" boolean,
487 "escalation_time" time without time zone,
488 "max_priority" integer,
489 "adjust_setting" text
492 CREATE UNIQUE INDEX obj_pk ON object_issue USING btree (objectid, type_of_issueid);
494 CREATE INDEX obj_objectid ON object_issue USING btree (objectid);
496 CREATE UNIQUE INDEX obj_type_of_notificationid ON object_issue USING btree (type_of_issueid);
501 CREATE TABLE "object_priority"
504 "priorityid" integer,
508 "repeat_notification" boolean,
509 "interval_for_repeat" time without time zone
512 CREATE UNIQUE INDEX obi_pk ON object_priority USING btree (objectid, priorityid);
514 CREATE INDEX obi_objectid ON object_priority USING btree (objectid);
516 CREATE INDEX obi_priorityid ON object_priority USING btree (priorityid);
521 CREATE TABLE "object_service"
525 "expected_interval" bigint,
526 "last_entry" timestamp with time zone,
527 "default_priority" integer,
528 "maximum_priority" integer,
532 CREATE UNIQUE INDEX obs_pk ON object_service USING btree (objectid, servicecode);
534 CREATE INDEX obs_objectid ON object_service USING btree (objectid);
536 CREATE INDEX obs_servicecode ON object_service USING btree (servicecode);
538 CREATE INDEX obs_accepted ON object_service USING btree (accepted);
543 CREATE TABLE object_statistics
547 statvalue double precision,
549 primary key (objectid, statname)
552 CREATE INDEX obj_stat_objid ON object_statistics USING btree (objectid);
557 CREATE TABLE "object_user"
561 "security_level" integer
564 CREATE UNIQUE INDEX ous_pk ON object_user USING btree (objectid, username);
566 CREATE INDEX ous_objectid ON object_user USING btree (objectid);
568 CREATE INDEX ous_username ON object_user USING btree (username);
570 CREATE INDEX ous_security_level ON object_user USING btree (security_level);
575 CREATE SEQUENCE "paramid_seq";
577 CREATE TABLE "parameter"
579 "paramid" bigint DEFAULT nextval('"paramid_seq"'::text) NOT NULL,
585 primary key (paramid)
588 CREATE UNIQUE INDEX param_obj_name ON parameter USING btree (objectid, name, class);
590 SELECT setval ('"paramid_seq"', 1, true);
595 CREATE TABLE "parameter_class"
597 "name" text, -- Name of the class: see parameter.class
598 "property_name" text,
600 "property_type" text, -- STATIC or DYNAMIC
601 "min" float, -- Default minimum value
602 "max" float, -- Default maximum value
603 "notify" boolean, -- Notify if something changes ?
605 primary key (name, property_name)
608 INSERT INTO parameter_class (name, property_name, description, property_type, notify)
609 VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't');
614 CREATE TABLE "parameter_notification"
616 "notificationid" bigint,
619 primary key (notificationid, paramid)
625 CREATE TABLE "priority"
631 "repeat_notification" boolean,
632 "interval_for_repeat" time without time zone
636 CREATE UNIQUE INDEX pri_pk ON priority USING btree (priority);
641 CREATE TABLE "property"
646 type text, -- STATIC or DYNAMIC
650 primary key (paramid, name)
656 CREATE TABLE "service"
660 "default_priority" integer,
661 "max_priority" integer
664 COPY "service" FROM stdin;
674 CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode);
676 CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename);
680 CREATE TABLE "status"
682 "statuscode" character varying(3),
684 "open_notification" boolean,
689 COPY "status" FROM stdin;
690 new new entry t Just detected, but nothing has been done yet
691 opn open notification t The notification has been displayed to a user or a user has been notified. However nothing has been done yet.
692 pen pending t The notification is currently being worked on.
693 ver waiting for verification t The notification has been worked on and is currently awaiting the approval/verification.
694 rej rejected f The notification has been identified as a false postive and was reject. The notification is now closed
695 cls closed f The notification has been closed
696 inv needs investigation t The notification is currently under investigation and is awaiting additional details before one can work on this again.
699 CREATE UNIQUE INDEX sta_pk ON status USING btree (statuscode);
701 CREATE UNIQUE INDEX sta_statusname ON status USING btree (statusname);
703 CREATE INDEX sta_open_notification ON status USING btree (open_notification);
708 CREATE TABLE supported_os
714 CREATE UNIQUE INDEX spp_os ON supported_os (os_name);
719 CREATE SEQUENCE "type_of_issue_type_of_issue_seq";
721 CREATE TABLE "type_of_issue"
723 "type_of_issueid" bigint DEFAULT
724 nextval('"type_of_issue_type_of_issue_seq"'::text) NOT NULL,
726 "suggested_priority" text,
729 automated_check boolean,
732 recheck_interval timestamp
736 COPY "type_of_issue" FROM stdin;
737 1 manual entry 4 A manual entry of a notification t
738 2 parameter created 3 A new parameter was created t
739 3 property modified 3 The STATIC property of a parameter was modified t
740 4 parameter removed 3 A parameter was removed t
743 CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);
745 CREATE UNIQUE INDEX toi_name ON type_of_issue USING btree (name);
747 CREATE INDEX toi_active ON type_of_issue USING btree (active);
749 SELECT setval ('"type_of_issue_type_of_issue_seq"', 4, true);
756 "username" text NOT NULL,
757 "active_sessionid" bigint,
758 "account_active" boolean,
759 "security_level" integer
762 CREATE UNIQUE INDEX usr_username ON usr USING btree (username);
764 CREATE UNIQUE INDEX usr_active_sessionid ON usr USING btree (active_sessionid);
766 CREATE INDEX usr_account_active ON usr USING btree (account_active);
768 CREATE INDEX usr_security_level ON usr USING btree (security_level);
771 -- Set up user groups and grant permissions in the proper places.
777 GRANT SELECT ON action TO GROUP view, GROUP ops, GROUP admin;
778 GRANT SELECT ON action_user TO GROUP view, GROUP ops, GROUP admin;
779 GRANT SELECT ON db_value TO GROUP view, GROUP ops, GROUP admin;
780 GRANT SELECT ON history TO GROUP view, GROUP ops, GROUP admin;
781 GRANT SELECT ON log TO GROUP view, GROUP ops, GROUP admin;
782 GRANT SELECT ON log_adv TO GROUP view, GROUP ops, GROUP admin;
783 GRANT SELECT ON log_adv_daemon TO GROUP view, GROUP ops, GROUP admin;
784 GRANT SELECT ON log_adv_kernel_network TO GROUP view, GROUP ops, GROUP admin;
785 GRANT SELECT ON log_notification TO GROUP view, GROUP ops, GROUP admin;
786 GRANT SELECT ON notification TO GROUP view, GROUP ops, GROUP admin;
787 GRANT SELECT ON object TO GROUP view, GROUP ops, GROUP admin;
788 GRANT SELECT ON object_issue TO GROUP view, GROUP ops, GROUP admin;
789 GRANT SELECT ON object_priority TO GROUP view, GROUP ops, GROUP admin;
790 GRANT SELECT ON object_service TO GROUP view, GROUP ops, GROUP admin;
791 GRANT SELECT ON object_user TO GROUP view, GROUP ops, GROUP admin;
792 GRANT SELECT ON parameter TO GROUP view, GROUP ops, GROUP admin;
793 GRANT SELECT ON parameter_class TO GROUP view, GROUP ops, GROUP admin;
794 GRANT SELECT ON parameter_notification TO GROUP view, GROUP ops, GROUP admin;
795 GRANT SELECT ON priority TO GROUP view, GROUP ops, GROUP admin;
796 GRANT SELECT ON property TO GROUP view, GROUP ops, GROUP admin;
797 GRANT SELECT ON service TO GROUP view, GROUP ops, GROUP admin;
798 GRANT SELECT ON status TO GROUP view, GROUP ops, GROUP admin;
799 GRANT SELECT ON supported_os TO GROUP view, GROUP ops, GROUP admin;
800 GRANT SELECT ON type_of_issue TO GROUP view, GROUP ops, GROUP admin;
801 GRANT SELECT ON usr TO GROUP view, GROUP ops, GROUP admin;
803 GRANT INSERT ON action_user TO GROUP ops, GROUP admin;
804 GRANT UPDATE ON notification TO GROUP ops, GROUP admin;
805 GRANT INSERT ON object TO GROUP admin;
806 GRANT UPDATE ON object TO GROUP ops, GROUP admin;
807 GRANT DELETE ON object TO GROUP admin;
808 GRANT UPDATE ON usr TO GROUP view, GROUP ops, GROUP admin;
809 GRANT INSERT ON usr TO GROUP admin;
810 GRANT DELETE ON usr TO GROUP admin;