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.18 2003-12-03 08:06:57 arjen
16 -- Changed the type of log_adv_daemon_email.delay and log_adv_daemon_email.xdelay
17 -- from time to interval. These delays can be more than 24 hours.
19 -- Revision 1.17 2003/09/02 12:49:47 arjen
20 -- BUGFIX: Secondary indices on log_notification were unique.
21 -- Additional information in the 'usr' table: 'display_name' and 'email'.
22 -- Added new issues and services.
24 -- Revision 1.16 2003/08/16 14:29:02 arjen
27 -- Revision 1.15 2003/08/05 07:43:24 arjen
28 -- Added index to the history table.
30 -- Revision 1.14 2003/07/09 07:14:59 arjen
31 -- New database tables: notification_check, notification_check_buffer,
32 -- notification_check_line and object_statistics.
34 -- Revision 1.13 2003/03/29 08:27:05 arjen
35 -- New columns in the table 'log_adv_daemon_email': size, pri, relay,
36 -- status_details and dsn.
37 -- Added several indices for the table 'log_adv_daemon_email'.
39 -- Revision 1.12 2003/02/21 08:38:38 arjen
40 -- Added new table to the database: log_adv_daemon_email.
42 -- Revision 1.11 2003/02/16 08:24:38 arjen
43 -- Added a new entry to the action table: Notification was displayed in the listing
45 -- Revision 1.10 2003/02/14 06:32:27 arjen
46 -- Setup the groups and database permissions. The three groups
47 -- are: view, ops and admin.
48 -- Added a new entry to the action table.
50 -- Revision 1.9 2003/02/13 08:46:54 arjen
51 -- Added log, notification and parameter counters to the 'object' table.
52 -- Counting these things at the time a user interface needs them is
53 -- too slow. Other programs, like gcm_daemon en gcm_input should prepare
54 -- these counters for quick retrieval.
56 -- Revision 1.8 2003/02/08 07:36:41 arjen
57 -- Added new table to the database : log_adv_daemon
59 -- Revision 1.7 2003/02/05 09:29:08 arjen
60 -- Bug fix: action.statuscode was in upper case.
62 -- Revision 1.6 2003/01/20 07:29:48 arjen
63 -- Added new tables to the database: parameter_class and parameter_notification
65 -- Revision 1.5 2003/01/18 08:46:48 arjen
66 -- Added new records to the 'type_of_issue' table.
67 -- Changed semantics of actionid 9 in the 'action' table.
71 CREATE SEQUENCE "action_actionid_seq";
75 "actionid" bigint DEFAULT nextval('"action_actionid_seq"'::text) NOT NULL,
77 "statuscode" character varying(3),
82 COPY "action" FROM stdin;
83 1 Entry in the system new This indicates that a notification has been entered into the system.
84 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
85 3 Remarks added pen Remarks have been added to the notification.
86 4 Priority changed manually pen The priority of the notification has been changed by the user.
87 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.
88 6 Action taken pen An action has been taken.
89 7 Assignment to user pen The notification has been assigned to an user.
90 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.
91 9 Investigation completed pen Investigation has been done. Information is available to fix the problem.
92 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.
93 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.
94 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.
95 13 Action verified cls A check has been done and the results were approved. The notification has been closed.
96 15 SMS sent opn An SMS has been sent.
97 14 E-mail sent opn An e-mail has been sent.
98 16 Fax sent opn An fax has been sent.
99 17 Log entries shown \N The log entries relevant to the notification have been shown.
100 18 Notification closed cls The notification has been closed.
101 19 Notification reopend opn The notification has been reopend.
102 20 Redisplayed to user \N The notification has been presented to a user. This is not the first time
103 21 Displayed in list \N The notification has been presented in a list with other notifications.
106 CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);
108 CREATE UNIQUE INDEX act_pk ON "action" USING btree (actionid);
110 CREATE UNIQUE INDEX act_actionname ON "action" USING btree (actionname);
112 CREATE INDEX act_statuscode ON "action" USING btree (statuscode);
114 SELECT setval ('"action_actionid_seq"', 21, true);
119 CREATE SEQUENCE "action_user_actionstepid_seq";
121 CREATE TABLE "action_user"
123 "actionstepid" bigint DEFAULT
124 nextval('"action_user_actionstepid_seq"'::text) NOT NULL,
127 "notificationid" bigint,
128 "timestamp" timestamp with time zone,
129 "statuscode" character varying(3),
133 CREATE UNIQUE INDEX action_user_actionstepid_key ON action_user USING btree (actionstepid);
135 CREATE INDEX anu_actionid ON action_user USING btree (actionid);
137 CREATE INDEX anu_username ON action_user USING btree (username);
139 CREATE INDEX anu_notificationid ON action_user USING btree (notificationid);
141 CREATE INDEX anu_timestamp ON action_user USING btree ("timestamp");
143 CREATE INDEX anu_statuscode ON action_user USING btree (statuscode);
145 SELECT setval ('"action_user_actionstepid_seq"', 1, false);
150 CREATE TABLE "db_value"
157 COPY "db_value" FROM stdin;
167 CREATE TABLE "history"
171 change_nature text, -- CREATED, MODIFIED or REMOVED
172 changed_property text,
178 CREATE INDEX history_pid_mod ON history(paramid, modified);
183 CREATE SEQUENCE "log_logid_seq";
187 "logid" bigint DEFAULT nextval('"log_logid_seq"'::text) NOT NULL,
189 "original_filename" text,
191 "object_timestamp" timestamp with time zone,
192 "timestamp" timestamp with time zone,
194 "processed" boolean DEFAULT false,
195 "recognized" boolean DEFAULT false
198 CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);
200 CREATE INDEX log_objectid ON log USING btree (objectid);
202 CREATE INDEX log_original_filename ON log USING btree (original_filename);
204 CREATE INDEX log_servicecode ON log USING btree (servicecode);
206 CREATE INDEX log_object_timestmap ON log USING btree ("timestamp");
208 CREATE INDEX log_timestmap ON log USING btree ("timestamp");
210 CREATE INDEX log_timestamp ON log USING btree (object_timestamp);
212 CREATE INDEX log_processed ON log USING btree (processed);
214 SELECT setval ('"log_logid_seq"', 1, false);
220 CREATE SEQUENCE "log_advid_seq";
223 CREATE TABLE "log_adv"
225 "log_advid" bigint DEFAULT
226 nextval('"log_advid_seq"'::text) NOT NULL,
227 "logid" bigint NOT NULL,
228 "detailed_table" text
232 CREATE INDEX log_adv_logid ON log_adv USING btree (logid);
234 CREATE UNIQUE INDEX log_adv_log_advid ON log_adv USING btree (log_advid);
236 SELECT setval ('"log_advid_seq"', 1, false);
238 CREATE TABLE log_adv_kernel_network
245 packet_length bigint,
251 destination_port int,
257 syn boolean DEFAULT false,
264 ) INHERITS (log_adv);
266 CREATE TABLE log_adv_daemon
270 ) INHERITS (log_adv);
272 CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service);
274 CREATE TABLE log_adv_daemon_email
278 internal_messageid TEXT,
279 external_messageid TEXT,
292 ) INHERITS (log_adv_daemon);
294 CREATE INDEX log_adv_daemon_email_s_ip ON log_adv_daemon_email (source_ip);
295 CREATE INDEX log_adv_daemon_email_d_ip ON log_adv_daemon_email (destination_ip);
296 CREATE INDEX log_adv_daemon_email_to ON log_adv_daemon_email (to_email);
297 CREATE INDEX log_adv_daemon_email_from ON log_adv_daemon_email (from_email);
298 CREATE INDEX log_adv_email_status ON log_adv_daemon_email (status);
302 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
303 CREATE FUNCTION "funct_processlog" () RETURNS opaque AS '
304 -- Initial date: September 18th 2002
305 -- Update: November 13th 2002
306 -- Author: Brenno J.S.A.A.F. de Winter
307 -- Abstract: This routine sets a flag
314 UPDATE db_value SET setting_value = ''TRUE'' WHERE setting = ''log_flag'';
317 ' LANGUAGE 'plpgsql';
320 CREATE TRIGGER "log_insert" AFTER INSERT ON "log" FOR EACH ROW EXECUTE PROCEDURE "funct_processlog" ();
325 CREATE TABLE "log_notification"
327 "notificationid" bigint,
331 CREATE UNIQUE INDEX lon_pk ON log_notification USING btree (notificationid, logid);
333 CREATE INDEX lon_notificationid ON log_notification USING btree (notificationid);
335 CREATE INDEX lon_logid ON log_notification USING btree (logid);
340 CREATE SEQUENCE "notification_notificationid_seq";
342 CREATE TABLE "notification"
344 "notificationid" bigint DEFAULT
345 nextval('"notification_notificationid_seq"'::text) NOT NULL,
347 "type_of_issueid" bigint,
348 "timestamp" timestamp with time zone,
349 "statuscode" character varying(3),
351 "escalation_count_timestamp" timestamp with time zone,
352 "repeat_notification_timestamp" timestamp with time zone,
353 "securitylevel_view" integer,
354 "securitylevel_add" integer,
355 "securitylevel_close" integer
358 CREATE UNIQUE INDEX notification_notificationid_key ON notification USING btree (notificationid);
360 CREATE INDEX not_objectid ON notification USING btree (objectid);
362 CREATE INDEX not_type_of_issueid ON notification USING btree (type_of_issueid);
364 CREATE INDEX not_timestamp ON notification USING btree ("timestamp");
366 CREATE INDEX not_statuscode ON notification USING btree (statuscode);
368 CREATE INDEX not_priority ON notification USING btree (priority);
370 CREATE INDEX not_escalation_count_timestamp ON notification USING btree (escalation_count_timestamp);
372 CREATE INDEX not_repeat_notification_timesta ON notification USING btree (repeat_notification_timestamp);
374 SELECT setval ('"notification_notificationid_seq"', 1, false);
379 CREATE SEQUENCE checkid_seq;
381 CREATE TABLE notification_check
383 checkid bigint DEFAULT nextval('checkid_seq'::text) NOT NULL,
386 time_between_executions INTERVAL,
387 last_execution DATETIME,
388 execution_counter BIGINT,
389 notificationcounter BIGINT,
390 decreasinglist BOOLEAN default false,
391 type_of_issueid BIGINT
394 CREATE UNIQUE INDEX not_check_checkid ON notification_check (checkid);
396 CREATE UNIQUE INDEX not_check_checkname ON notification_check (checkname);
398 CREATE INDEX not_check_check_lastexec ON notification_check (last_execution);
403 CREATE TABLE notification_check_buffer
411 CREATE INDEX notcheckbuffer_checkid ON notification_check_buffer(checkid);
413 CREATE INDEX notcheckbuffer_sort ON notification_check_buffer(sortorder);
415 CREATE INDEX notcheckbuffer_pid ON notification_check_buffer(pid);
417 CREATE INDEX notcheckbuffer_logid ON notification_check_buffer(logid);
422 CREATE SEQUENCE checklineid_seq;
424 CREATE TABLE notification_check_line
426 checklineid BIGINT DEFAULT nextval('checklineid_seq'::text) NOT NULL,
429 last_logid BIGINT default 0,
430 historicboundary INTERVAL default '0',
431 use_logid BOOLEAN default false,
435 CREATE UNIQUE INDEX notcheckline_checklineid ON notification_check_line (checklineid);
437 CREATE INDEX notcheckline_checkid ON notification_check_line (checkid);
439 CREATE INDEX notcheckline_sort ON notification_check_line (sortorder);
441 CREATE INDEX notcheckline_check_sort ON notification_check_line (checkid, sortorder);
446 CREATE SEQUENCE "object_objectid_seq";
448 CREATE TABLE "object"
450 "objectid" bigint DEFAULT nextval('"object_objectid_seq"'::text) NOT NULL,
453 "scp_enabled" boolean,
455 "mail_enabled" boolean,
457 "sms_enabled" boolean,
459 "fax_enabled" boolean,
461 "object_description" text,
463 "physical_location" text,
469 "notification_count" bigint,
470 "parameter_count" bigint
473 CREATE UNIQUE INDEX object_objectid_key ON object USING btree (objectid);
475 CREATE UNIQUE INDEX obj_objectname ON object USING btree (objectname);
477 CREATE UNIQUE INDEX obj_objectcode ON object USING btree (objectcode);
479 CREATE INDEX obj_mail_from ON object USING btree (mail_from);
481 CREATE INDEX os ON object (os);
483 CREATE INDEX os_version ON object (os, os_version);
485 SELECT setval ('"object_objectid_seq"', 1, false);
490 CREATE TABLE "object_issue"
493 "type_of_issueid" bigint,
494 "default_priority" integer,
495 "escalation" boolean,
496 "escalation_time" time without time zone,
497 "max_priority" integer,
498 "adjust_setting" text
501 CREATE UNIQUE INDEX obj_pk ON object_issue USING btree (objectid, type_of_issueid);
503 CREATE INDEX obj_objectid ON object_issue USING btree (objectid);
505 CREATE UNIQUE INDEX obj_type_of_notificationid ON object_issue USING btree (type_of_issueid);
510 CREATE TABLE "object_priority"
513 "priorityid" integer,
517 "repeat_notification" boolean,
518 "interval_for_repeat" time without time zone
521 CREATE UNIQUE INDEX obi_pk ON object_priority USING btree (objectid, priorityid);
523 CREATE INDEX obi_objectid ON object_priority USING btree (objectid);
525 CREATE INDEX obi_priorityid ON object_priority USING btree (priorityid);
530 CREATE TABLE "object_service"
534 "expected_interval" bigint,
535 "last_entry" timestamp with time zone,
536 "default_priority" integer,
537 "maximum_priority" integer,
541 CREATE UNIQUE INDEX obs_pk ON object_service USING btree (objectid, servicecode);
543 CREATE INDEX obs_objectid ON object_service USING btree (objectid);
545 CREATE INDEX obs_servicecode ON object_service USING btree (servicecode);
547 CREATE INDEX obs_accepted ON object_service USING btree (accepted);
552 CREATE TABLE object_statistics
556 statvalue double precision,
558 primary key (objectid, statname)
561 CREATE INDEX obj_stat_objid ON object_statistics USING btree (objectid);
566 CREATE TABLE "object_user"
570 "security_level" integer
573 CREATE UNIQUE INDEX ous_pk ON object_user USING btree (objectid, username);
575 CREATE INDEX ous_objectid ON object_user USING btree (objectid);
577 CREATE INDEX ous_username ON object_user USING btree (username);
579 CREATE INDEX ous_security_level ON object_user USING btree (security_level);
584 CREATE SEQUENCE "paramid_seq";
586 CREATE TABLE "parameter"
588 "paramid" bigint DEFAULT nextval('"paramid_seq"'::text) NOT NULL,
594 primary key (paramid)
597 CREATE UNIQUE INDEX param_obj_name ON parameter USING btree (objectid, name, class);
599 SELECT setval ('"paramid_seq"', 1, true);
604 CREATE TABLE "parameter_class"
606 "name" text, -- Name of the class: see parameter.class
607 "property_name" text,
609 "property_type" text, -- STATIC or DYNAMIC
610 "min" float, -- Default minimum value
611 "max" float, -- Default maximum value
612 "notify" boolean, -- Notify if something changes ?
614 primary key (name, property_name)
617 INSERT INTO parameter_class (name, property_name, description, property_type, notify)
618 VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't');
623 CREATE TABLE "parameter_notification"
625 "notificationid" bigint,
628 primary key (notificationid, paramid)
634 CREATE TABLE "priority"
640 "repeat_notification" boolean,
641 "interval_for_repeat" time without time zone
645 CREATE UNIQUE INDEX pri_pk ON priority USING btree (priority);
650 CREATE TABLE "property"
655 type text, -- STATIC or DYNAMIC
659 primary key (paramid, name)
665 CREATE TABLE "service"
669 "default_priority" integer,
670 "max_priority" integer
673 COPY "service" FROM stdin;
681 CROND Cron Daemon 1 5
682 gnucomo Gnucomo Daemon 1 5
683 sendmail Mail Transport Agent 1 5
684 dhcpd DHCP Daemon 1 5
686 named DNS Services 1 5
687 xinetd Internet Daemon 1 5
688 ipop Post Office Protocol 1 5
689 mgetty Serial port login and fax 1 5
691 pam Authentication modules 1 5
692 modprobe Kernel modules 1 5
695 CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode);
697 CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename);
701 CREATE TABLE "status"
703 "statuscode" character varying(3),
705 "open_notification" boolean,
710 COPY "status" FROM stdin;
711 new new entry t Just detected, but nothing has been done yet
712 opn open notification t The notification has been displayed to a user or a user has been notified. However nothing has been done yet.
713 pen pending t The notification is currently being worked on.
714 ver waiting for verification t The notification has been worked on and is currently awaiting the approval/verification.
715 rej rejected f The notification has been identified as a false postive and was reject. The notification is now closed
716 cls closed f The notification has been closed
717 inv needs investigation t The notification is currently under investigation and is awaiting additional details before one can work on this again.
720 CREATE UNIQUE INDEX sta_pk ON status USING btree (statuscode);
722 CREATE UNIQUE INDEX sta_statusname ON status USING btree (statusname);
724 CREATE INDEX sta_open_notification ON status USING btree (open_notification);
729 CREATE TABLE supported_os
735 CREATE UNIQUE INDEX spp_os ON supported_os (os_name);
740 CREATE SEQUENCE "type_of_issue_type_of_issue_seq";
742 CREATE TABLE "type_of_issue"
744 "type_of_issueid" bigint DEFAULT
745 nextval('"type_of_issue_type_of_issue_seq"'::text) NOT NULL,
747 "suggested_priority" text,
750 automated_check boolean,
753 recheck_interval timestamp
757 COPY "type_of_issue" FROM stdin;
758 1 manual entry 4 A manual entry of a notification t
759 2 parameter created 3 A new parameter was created t
760 3 property modified 3 The STATIC property of a parameter was modified t
761 4 parameter removed 3 A parameter was removed t
762 5 service unknown 5 Service in log entry is unknown t
763 6 service not used 5 Service in log entry is not used t
766 CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid);
768 CREATE UNIQUE INDEX toi_name ON type_of_issue USING btree (name);
770 CREATE INDEX toi_active ON type_of_issue USING btree (active);
772 SELECT setval ('"type_of_issue_type_of_issue_seq"', 6, true);
779 "username" text NOT NULL,
780 "active_sessionid" bigint,
781 "account_active" boolean,
782 "security_level" integer,
787 CREATE UNIQUE INDEX usr_username ON usr USING btree (username);
789 CREATE UNIQUE INDEX usr_active_sessionid ON usr USING btree (active_sessionid);
791 CREATE INDEX usr_account_active ON usr USING btree (account_active);
793 CREATE INDEX usr_security_level ON usr USING btree (security_level);
796 -- Set up user groups and grant permissions in the proper places.
802 GRANT SELECT ON action TO GROUP view, GROUP ops, GROUP admin;
803 GRANT SELECT ON action_user TO GROUP view, GROUP ops, GROUP admin;
804 GRANT SELECT ON db_value TO GROUP view, GROUP ops, GROUP admin;
805 GRANT SELECT ON history TO GROUP view, GROUP ops, GROUP admin;
806 GRANT SELECT ON log TO GROUP view, GROUP ops, GROUP admin;
807 GRANT SELECT ON log_adv TO GROUP view, GROUP ops, GROUP admin;
808 GRANT SELECT ON log_adv_daemon TO GROUP view, GROUP ops, GROUP admin;
809 GRANT SELECT ON log_adv_kernel_network TO GROUP view, GROUP ops, GROUP admin;
810 GRANT SELECT ON log_notification TO GROUP view, GROUP ops, GROUP admin;
811 GRANT SELECT ON notification TO GROUP view, GROUP ops, GROUP admin;
812 GRANT SELECT ON object TO GROUP view, GROUP ops, GROUP admin;
813 GRANT SELECT ON object_issue TO GROUP view, GROUP ops, GROUP admin;
814 GRANT SELECT ON object_priority TO GROUP view, GROUP ops, GROUP admin;
815 GRANT SELECT ON object_service TO GROUP view, GROUP ops, GROUP admin;
816 GRANT SELECT ON object_user TO GROUP view, GROUP ops, GROUP admin;
817 GRANT SELECT ON parameter TO GROUP view, GROUP ops, GROUP admin;
818 GRANT SELECT ON parameter_class TO GROUP view, GROUP ops, GROUP admin;
819 GRANT SELECT ON parameter_notification TO GROUP view, GROUP ops, GROUP admin;
820 GRANT SELECT ON priority TO GROUP view, GROUP ops, GROUP admin;
821 GRANT SELECT ON property TO GROUP view, GROUP ops, GROUP admin;
822 GRANT SELECT ON service TO GROUP view, GROUP ops, GROUP admin;
823 GRANT SELECT ON status TO GROUP view, GROUP ops, GROUP admin;
824 GRANT SELECT ON supported_os TO GROUP view, GROUP ops, GROUP admin;
825 GRANT SELECT ON type_of_issue TO GROUP view, GROUP ops, GROUP admin;
826 GRANT SELECT ON usr TO GROUP view, GROUP ops, GROUP admin;
828 GRANT INSERT ON action_user TO GROUP ops, GROUP admin;
829 GRANT UPDATE ON notification TO GROUP ops, GROUP admin;
830 GRANT INSERT ON object TO GROUP admin;
831 GRANT UPDATE ON object TO GROUP ops, GROUP admin;
832 GRANT DELETE ON object TO GROUP admin;
833 GRANT UPDATE ON usr TO GROUP view, GROUP ops, GROUP admin;
834 GRANT INSERT ON usr TO GROUP admin;
835 GRANT DELETE ON usr TO GROUP admin;