-- Before running this script with 'psql -f', you should have your
-- DBA create the database and give access permissions.
--
--- $log$
+-- $Log: create.sql,v $
+-- 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";
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 Make output reference REF The automated output from an object was sent to gnucomo. The input has been identified as a valid reference for the future. For that reason the status is now REFERENCE
-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.
+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.
+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
\.
CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid);
COPY "db_value" FROM stdin;
-db_version 1
+db_version 28
+gcm_daemon_version 1
+log_processing 0
+last_notification 0
\.
--
"object_timestamp" timestamp with time zone,
"timestamp" timestamp with time zone,
"rawdata" text,
- "processed" boolean
+ "processed" boolean DEFAULT false,
+ "recognized" boolean DEFAULT false
);
CREATE UNIQUE INDEX log_logid_key ON log USING btree (logid);
SELECT setval ('"log_logid_seq"', 1, false);
-CREATE RULE log_update AS ON UPDATE TO log DO INSTEAD NOTHING;
-
--
--
(
"log_advid" bigint DEFAULT
nextval('"log_advid_seq"'::text) NOT NULL,
- "logid" bigint NOT NULL
+ "logid" bigint NOT NULL,
+ "detailed_table" text
);
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 TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
"object_owner" text,
"physical_location" text,
"timezone" text,
- "remark" 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 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 "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,
--
--
+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"
"name" text,
"suggested_priority" text,
"description" text,
- "active" boolean
+ "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 INDEX toi_active ON type_of_issue USING btree (active);
-SELECT setval ('"type_of_issue_type_of_issue_seq"', 1, true);
+SELECT setval ('"type_of_issue_type_of_issue_seq"', 4, true);
--
--
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;