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.12 2003-02-21 08:38:38 arjen
16 -- Added new table to the database: log_adv_daemon_email.
18 -- Revision 1.11 2003/02/16 08:24:38 arjen
19 -- Added a new entry to the action table: Notification was displayed in the listing
21 -- Revision 1.10 2003/02/14 06:32:27 arjen
22 -- Setup the groups and database permissions. The three groups
23 -- are: view, ops and admin.
24 -- Added a new entry to the action table.
26 -- Revision 1.9 2003/02/13 08:46:54 arjen
27 -- Added log, notification and parameter counters to the 'object' table.
28 -- Counting these things at the time a user interface needs them is
29 -- too slow. Other programs, like gcm_daemon en gcm_input should prepare
30 -- these counters for quick retrieval.
32 -- Revision 1.8 2003/02/08 07:36:41 arjen
33 -- Added new table to the database : log_adv_daemon
35 -- Revision 1.7 2003/02/05 09:29:08 arjen
36 -- Bug fix: action.statuscode was in upper case.
38 -- Revision 1.6 2003/01/20 07:29:48 arjen
39 -- Added new tables to the database: parameter_class and parameter_notification
41 -- Revision 1.5 2003/01/18 08:46:48 arjen
42 -- Added new records to the 'type_of_issue' table.
43 -- Changed semantics of actionid 9 in the 'action' table.
47 CREATE SEQUENCE "action_actionid_seq";
51 "actionid" bigint DEFAULT nextval('"action_actionid_seq"'::text) NOT NULL,
53 "statuscode" character varying(3),
58 COPY "action" FROM stdin;
59 1 Entry in the system new This indicates that a notification has been entered into the system.
60 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
61 3 Remarks added pen Remarks have been added to the notification.
62 4 Priority changed manually pen The priority of the notification has been changed by the user.
63 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.
64 6 Action taken pen An action has been taken.
65 7 Assignment to user pen The notification has been assigned to an user.
66 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.
67 9 Investigation completed pen Investigation has been done. Information is available to fix the problem.
68 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.
69 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.
70 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.
71 13 Action verified cls A check has been done and the results were approved. The notification has been closed.
72 15 SMS sent opn An SMS has been sent.
73 14 E-mail sent opn An e-mail has been sent.
74 16 Fax sent opn An fax has been sent.
75 17 Log entries shown \N The log entries relevant to the notification have been shown.
76 18 Notification closed cls The notification has been closed.
77 19 Notification reopend opn The notification has been reopend.
78 20 Redisplayed to user \N The notification has been presented to a user. This is not the first time
79 21 Displayed in list \N The notification has been presented in a list with other notifications.
82 CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);
84 CREATE UNIQUE INDEX act_pk ON "action" USING btree (actionid);
86 CREATE UNIQUE INDEX act_actionname ON "action" USING btree (actionname);
88 CREATE INDEX act_statuscode ON "action" USING btree (statuscode);
90 SELECT setval ('"action_actionid_seq"', 19, true);
95 CREATE SEQUENCE "action_user_actionstepid_seq";
97 CREATE TABLE "action_user"
99 "actionstepid" bigint DEFAULT
100 nextval('"action_user_actionstepid_seq"'::text) NOT NULL,
103 "notificationid" bigint,
104 "timestamp" timestamp with time zone,
105 "statuscode" character varying(3),
109 CREATE UNIQUE INDEX action_user_actionstepid_key ON action_user USING btree (actionstepid);
111 CREATE INDEX anu_actionid ON action_user USING btree (actionid);
113 CREATE INDEX anu_username ON action_user USING btree (username);
115 CREATE INDEX anu_notificationid ON action_user USING btree (notificationid);
117 CREATE INDEX anu_timestamp ON action_user USING btree ("timestamp");
119 CREATE INDEX anu_statuscode ON action_user USING btree (statuscode);
121 SELECT setval ('"action_user_actionstepid_seq"', 1, false);
126 CREATE TABLE "db_value"
133 COPY "db_value" FROM stdin;
143 CREATE TABLE "history"
147 change_nature text, -- CREATED, MODIFIED or REMOVED
148 changed_property text,
157 CREATE SEQUENCE "log_logid_seq";
161 "logid" bigint DEFAULT nextval('"log_logid_seq"'::text) NOT NULL,
163 "original_filename" text,
165 "object_timestamp" timestamp with time zone,
166 "timestamp" timestamp with time zone,
168 "processed" boolean DEFAULT false,
169 "recognized" boolean DEFAULT false
172 CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);
174 CREATE INDEX log_objectid ON log USING btree (objectid);
176 CREATE INDEX log_original_filename ON log USING btree (original_filename);
178 CREATE INDEX log_servicecode ON log USING btree (servicecode);
180 CREATE INDEX log_object_timestmap ON log USING btree ("timestamp");
182 CREATE INDEX log_timestmap ON log USING btree ("timestamp");
184 CREATE INDEX log_timestamp ON log USING btree (object_timestamp);
186 CREATE INDEX log_processed ON log USING btree (processed);
188 SELECT setval ('"log_logid_seq"', 1, false);
194 CREATE SEQUENCE "log_advid_seq";
197 CREATE TABLE "log_adv"
199 "log_advid" bigint DEFAULT
200 nextval('"log_advid_seq"'::text) NOT NULL,
201 "logid" bigint NOT NULL,
202 "detailed_table" text
206 CREATE INDEX log_adv_logid ON log_adv USING btree (logid);
208 CREATE UNIQUE INDEX log_adv_log_advid ON log_adv USING btree (log_advid);
210 SELECT setval ('"log_advid_seq"', 1, false);
212 CREATE TABLE log_adv_kernel_network
219 packet_length bigint,
225 destination_port int,
231 syn boolean DEFAULT false,
238 ) INHERITS (log_adv);
240 CREATE TABLE log_adv_daemon
244 ) INHERITS (log_adv);
246 CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service);
248 CREATE TABLE log_adv_daemon_email
252 internal_messageid TEXT,
253 external_messageid TEXT,
261 ) INHERITS (log_adv_daemon);
264 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
265 CREATE FUNCTION "funct_processlog" () RETURNS opaque AS '
266 -- Initial date: September 18th 2002
267 -- Update: November 13th 2002
268 -- Author: Brenno J.S.A.A.F. de Winter
269 -- Abstract: This routine sets a flag
276 UPDATE db_value SET setting_value = ''TRUE'' WHERE setting = ''log_flag'';
279 ' LANGUAGE 'plpgsql';
282 CREATE TRIGGER "log_insert" AFTER INSERT ON "log" FOR EACH ROW EXECUTE PROCEDURE "funct_processlog" ();
287 CREATE TABLE "log_notification"
289 "notificationid" bigint,
293 CREATE UNIQUE INDEX lon_pk ON log_notification USING btree (notificationid, logid);
295 CREATE UNIQUE INDEX lon_notificationid ON log_notification USING btree (notificationid);
297 CREATE UNIQUE INDEX lon_logid ON log_notification USING btree (logid);
302 CREATE SEQUENCE "notification_notificationid_seq";
304 CREATE TABLE "notification"
306 "notificationid" bigint DEFAULT
307 nextval('"notification_notificationid_seq"'::text) NOT NULL,
309 "type_of_issueid" bigint,
310 "timestamp" timestamp with time zone,
311 "statuscode" character varying(3),
313 "escalation_count_timestamp" timestamp with time zone,
314 "repeat_notification_timestamp" timestamp with time zone,
315 "securitylevel_view" integer,
316 "securitylevel_add" integer,
317 "securitylevel_close" integer
320 CREATE UNIQUE INDEX notification_notificationid_key ON notification USING btree (notificationid);
322 CREATE INDEX not_objectid ON notification USING btree (objectid);
324 CREATE INDEX not_type_of_issueid ON notification USING btree (type_of_issueid);
326 CREATE INDEX not_timestamp ON notification USING btree ("timestamp");
328 CREATE INDEX not_statuscode ON notification USING btree (statuscode);
330 CREATE INDEX not_priority ON notification USING btree (priority);
332 CREATE INDEX not_escalation_count_timestamp ON notification USING btree (escalation_count_timestamp);
334 CREATE INDEX not_repeat_notification_timesta ON notification USING btree (repeat_notification_timestamp);
336 SELECT setval ('"notification_notificationid_seq"', 1, false);
341 CREATE SEQUENCE "object_objectid_seq";
343 CREATE TABLE "object"
345 "objectid" bigint DEFAULT nextval('"object_objectid_seq"'::text) NOT NULL,
348 "scp_enabled" boolean,
350 "mail_enabled" boolean,
352 "sms_enabled" boolean,
354 "fax_enabled" boolean,
356 "object_description" text,
358 "physical_location" text,
364 "notification_count" bigint,
365 "parameter_count" bigint
368 CREATE UNIQUE INDEX object_objectid_key ON object USING btree (objectid);
370 CREATE UNIQUE INDEX obj_objectname ON object USING btree (objectname);
372 CREATE UNIQUE INDEX obj_objectcode ON object USING btree (objectcode);
374 CREATE INDEX obj_mail_from ON object USING btree (mail_from);
376 CREATE INDEX os ON object (os);
378 CREATE INDEX os_version ON object (os, os_version);
380 SELECT setval ('"object_objectid_seq"', 1, false);
385 CREATE TABLE "object_issue"
388 "type_of_issueid" bigint,
389 "default_priority" integer,
390 "escalation" boolean,
391 "escalation_time" time without time zone,
392 "max_priority" integer,
393 "adjust_setting" text
396 CREATE UNIQUE INDEX obj_pk ON object_issue USING btree (objectid, type_of_issueid);
398 CREATE INDEX obj_objectid ON object_issue USING btree (objectid);
400 CREATE UNIQUE INDEX obj_type_of_notificationid ON object_issue USING btree (type_of_issueid);
405 CREATE TABLE "object_priority"
408 "priorityid" integer,
412 "repeat_notification" boolean,
413 "interval_for_repeat" time without time zone
416 CREATE UNIQUE INDEX obi_pk ON object_priority USING btree (objectid, priorityid);
418 CREATE INDEX obi_objectid ON object_priority USING btree (objectid);
420 CREATE INDEX obi_priorityid ON object_priority USING btree (priorityid);
425 CREATE TABLE "object_service"
429 "expected_interval" bigint,
430 "last_entry" timestamp with time zone,
431 "default_priority" integer,
432 "maximum_priority" integer,
436 CREATE UNIQUE INDEX obs_pk ON object_service USING btree (objectid, servicecode);
438 CREATE INDEX obs_objectid ON object_service USING btree (objectid);
440 CREATE INDEX obs_servicecode ON object_service USING btree (servicecode);
442 CREATE INDEX obs_accepted ON object_service USING btree (accepted);
447 CREATE TABLE "object_user"
451 "security_level" integer
454 CREATE UNIQUE INDEX ous_pk ON object_user USING btree (objectid, username);
456 CREATE INDEX ous_objectid ON object_user USING btree (objectid);
458 CREATE INDEX ous_username ON object_user USING btree (username);
460 CREATE INDEX ous_security_level ON object_user USING btree (security_level);
465 CREATE SEQUENCE "paramid_seq";
467 CREATE TABLE "parameter"
469 "paramid" bigint DEFAULT nextval('"paramid_seq"'::text) NOT NULL,
475 primary key (paramid)
478 CREATE UNIQUE INDEX param_obj_name ON parameter USING btree (objectid, name, class);
480 SELECT setval ('"paramid_seq"', 1, true);
485 CREATE TABLE "parameter_class"
487 "name" text, -- Name of the class: see parameter.class
488 "property_name" text,
490 "property_type" text, -- STATIC or DYNAMIC
491 "min" float, -- Default minimum value
492 "max" float, -- Default maximum value
493 "notify" boolean, -- Notify if something changes ?
495 primary key (name, property_name)
498 INSERT INTO parameter_class (name, property_name, description, property_type, notify)
499 VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't');
504 CREATE TABLE "parameter_notification"
506 "notificationid" bigint,
509 primary key (notificationid, paramid)
515 CREATE TABLE "priority"
521 "repeat_notification" boolean,
522 "interval_for_repeat" time without time zone
526 CREATE UNIQUE INDEX pri_pk ON priority USING btree (priority);
531 CREATE TABLE "property"
536 type text, -- STATIC or DYNAMIC
540 primary key (paramid, name)
546 CREATE TABLE "service"
550 "default_priority" integer,
551 "max_priority" integer
554 COPY "service" FROM stdin;
564 CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode);
566 CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename);
570 CREATE TABLE "status"
572 "statuscode" character varying(3),
574 "open_notification" boolean,
579 COPY "status" FROM stdin;
580 new new entry t Just detected, but nothing has been done yet
581 opn open notification t The notification has been displayed to a user or a user has been notified. However nothing has been done yet.
582 pen pending t The notification is currently being worked on.
583 ver waiting for verification t The notification has been worked on and is currently awaiting the approval/verification.
584 rej rejected f The notification has been identified as a false postive and was reject. The notification is now closed
585 cls closed f The notification has been closed
586 inv needs investigation t The notification is currently under investigation and is awaiting additional details before one can work on this again.
589 CREATE UNIQUE INDEX sta_pk ON status USING btree (statuscode);
591 CREATE UNIQUE INDEX sta_statusname ON status USING btree (statusname);
593 CREATE INDEX sta_open_notification ON status USING btree (open_notification);
598 CREATE TABLE supported_os
604 CREATE UNIQUE INDEX spp_os ON supported_os (os_name);
609 CREATE SEQUENCE "type_of_issue_type_of_issue_seq";
611 CREATE TABLE "type_of_issue"
613 "type_of_issueid" bigint DEFAULT
614 nextval('"type_of_issue_type_of_issue_seq"'::text) NOT NULL,
616 "suggested_priority" text,
619 automated_check boolean,
622 recheck_interval timestamp
626 COPY "type_of_issue" FROM stdin;
627 1 manual entry 4 A manual entry of a notification t
628 2 parameter created 3 A new parameter was created t
629 3 property modified 3 The STATIC property of a parameter was modified t
630 4 parameter removed 3 A parameter was removed t
633 CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);
635 CREATE UNIQUE INDEX toi_name ON type_of_issue USING btree (name);
637 CREATE INDEX toi_active ON type_of_issue USING btree (active);
639 SELECT setval ('"type_of_issue_type_of_issue_seq"', 4, true);
646 "username" text NOT NULL,
647 "active_sessionid" bigint,
648 "account_active" boolean,
649 "security_level" integer
652 CREATE UNIQUE INDEX usr_username ON usr USING btree (username);
654 CREATE UNIQUE INDEX usr_active_sessionid ON usr USING btree (active_sessionid);
656 CREATE INDEX usr_account_active ON usr USING btree (account_active);
658 CREATE INDEX usr_security_level ON usr USING btree (security_level);
661 -- Set up user groups and grant permissions in the proper places.
667 GRANT SELECT ON action TO GROUP view, GROUP ops, GROUP admin;
668 GRANT SELECT ON action_user TO GROUP view, GROUP ops, GROUP admin;
669 GRANT SELECT ON db_value TO GROUP view, GROUP ops, GROUP admin;
670 GRANT SELECT ON history TO GROUP view, GROUP ops, GROUP admin;
671 GRANT SELECT ON log TO GROUP view, GROUP ops, GROUP admin;
672 GRANT SELECT ON log_adv TO GROUP view, GROUP ops, GROUP admin;
673 GRANT SELECT ON log_adv_daemon TO GROUP view, GROUP ops, GROUP admin;
674 GRANT SELECT ON log_adv_kernel_network TO GROUP view, GROUP ops, GROUP admin;
675 GRANT SELECT ON log_notification TO GROUP view, GROUP ops, GROUP admin;
676 GRANT SELECT ON notification TO GROUP view, GROUP ops, GROUP admin;
677 GRANT SELECT ON object TO GROUP view, GROUP ops, GROUP admin;
678 GRANT SELECT ON object_issue TO GROUP view, GROUP ops, GROUP admin;
679 GRANT SELECT ON object_priority TO GROUP view, GROUP ops, GROUP admin;
680 GRANT SELECT ON object_service TO GROUP view, GROUP ops, GROUP admin;
681 GRANT SELECT ON object_user TO GROUP view, GROUP ops, GROUP admin;
682 GRANT SELECT ON parameter TO GROUP view, GROUP ops, GROUP admin;
683 GRANT SELECT ON parameter_class TO GROUP view, GROUP ops, GROUP admin;
684 GRANT SELECT ON parameter_notification TO GROUP view, GROUP ops, GROUP admin;
685 GRANT SELECT ON priority TO GROUP view, GROUP ops, GROUP admin;
686 GRANT SELECT ON property TO GROUP view, GROUP ops, GROUP admin;
687 GRANT SELECT ON service TO GROUP view, GROUP ops, GROUP admin;
688 GRANT SELECT ON status TO GROUP view, GROUP ops, GROUP admin;
689 GRANT SELECT ON supported_os TO GROUP view, GROUP ops, GROUP admin;
690 GRANT SELECT ON type_of_issue TO GROUP view, GROUP ops, GROUP admin;
691 GRANT SELECT ON usr TO GROUP view, GROUP ops, GROUP admin;
693 GRANT INSERT ON action_user TO GROUP ops, GROUP admin;
694 GRANT UPDATE ON notification TO GROUP ops, GROUP admin;
695 GRANT INSERT ON object GROUP admin;
696 GRANT UPDATE ON object TO GROUP ops, GROUP admin;
697 GRANT DELETE ON object GROUP admin;
698 GRANT UPDATE ON usr TO GROUP view, GROUP ops, GROUP admin;
699 GRANT INSERT ON usr TO GROUP admin;
700 GRANT DELETE ON usr TO GROUP admin;