3 /* This 'function' updates the gnucomo database to the correct version it will make changes to the
4 * database. It is called within an if-statement when needed.
5 * INPUT : NONE (everything is available within the class)
6 * OUTPUT : Success TRUE/FALSE
10 //Do while statement since all updateshave to be executed starting at the
11 //latest version. It's a walk through.
13 switch ($active_version)
16 //DROP the rule that prohibits updates (change of spec).
17 $local_sql = "DROP RULE log_update ON log";
18 $dbms->query($local_sql); //DISABLED FOR NEW ROLE LOG-tABLE ???
21 //In the log table processed should be false by default
22 $local_sql = "UPDATE log SET processed = false";
23 $dbms->query($local_sql);
26 //In the log_adv a column is added that indicates where the detailed
27 //data has been written to
28 $local_sql = "ALTER TABLE log_adv ADD COLUMN detailed_table VARCHAR(75)";
29 $dbms->query($local_sql);
32 //Create a log_adv_kernel_network table that recognizes the log-records
33 //that have come from the kernel-network interface (typically iptables).
34 $local_sql = "CREATE TABLE log_adv_kernel_network (";
35 $local_sql .= "device_in VARCHAR(15), device_out VARCHAR(15), hw_address MACADDR, ";
36 $local_sql .= "source_ip INET, destination_ip INET, packet_length BIGINT, ";
37 $local_sql .= "tos_bit VARCHAR(5), ";
38 $local_sql .= "prec_bit VARCHAR(5), ttl INT, header_id BIGINT, source_port INT, ";
39 $local_sql .= "destination_port INT, body_length INT, protocol VARCHAR(5), ";
40 $local_sql .= "body_len INT";
41 $local_sql .= ") INHERITS (log_adv)";
42 $dbms->query($local_sql);
45 //Add the operating system to the object-table
46 //COLUMN os (VARCHAR - 25)
47 //COLUMN os_version (VARCHAR - 15)
48 $local_sql = "ALTER TABLE object ADD COLUMN os TEXT";
49 $dbms->query($local_sql);
51 $local_sql = "ALTER TABLE object ADD COLUMN os_version TEXT";
52 $dbms->query($local_sql);
54 $local_sql = "CREATE INDEX os ON object (os)";
55 $dbms->query($local_sql);
57 $local_sql = "CREATE INDEX os_version ON object (os, os_version)";
58 $dbms->query($local_sql);
61 //Create a table with supported operating systems. Only after a implementation of a
62 //log-processing application a new OS will be added. This ensures the correctness of
64 $local_sql = "CREATE TABLE supported_os (os_name TEXT, remarks TEXT)";
65 $dbms->query($local_sql);
67 $local_sql = "CREATE UNIQUE INDEX spp_os ON supported_os (os_name)";
68 $dbms->query($local_sql);
71 //Create a column in the log table that indicates if the daemon did recognize the
72 //log-record. This makes it easier to trace which log-records still have to be added
74 $local_sql = "ALTER TABLE log ADD COLUMN recognized BOOLEAN";
75 $dbms->query($local_sql);
77 $local_sql = "ALTER TABLE log ALTER COLUMN recognized SET DEFAULT FALSE";
78 $dbms->query($local_sql);
81 //Set the default value of processed to FALSE
82 $local_sql = "ALTER TABLE log ALTER COLUMN processed SET DEFAULT FALSE";
83 $dbms->query($local_sql);
86 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN urgp INT";
87 $dbms->query($local_sql);
89 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN syn BOOLEAN";
90 $dbms->query($local_sql);
92 $local_sql = "ALTER TABLE log_adv_kernel_network ALTER COLUMN syn SET DEFAULT FALSE";
93 $dbms->query($local_sql);
97 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN type INT";
98 $dbms->query($local_sql);
100 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN code INT";
101 $dbms->query($local_sql);
103 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN sequence_number INT";
104 $dbms->query($local_sql);
107 //Recognize some more kernel_network fields
108 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN res varchar(5)";
109 $dbms->query($local_sql);
111 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN rst BOOLEAN";
112 $dbms->query($local_sql);
114 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN df BOOLEAN";
115 $dbms->query($local_sql);
118 //Add the gcm_daemon_version to the system
119 $local_sql = "INSERT INTO db_value VALUES ('gcm_daemon_version', '1')";
120 $dbms->query($local_sql);
123 //Create the table object_system_user
124 $local_sql = "CREATE TABLE object_system_user (objectid BIGINT, system_username TEXT, can_login BOOLEAN, can_be_root BOOLEAN)";
125 $dbms->query($local_sql);
127 $local_sql = "CREATE UNIQUE INDEX obsyus_id_name ON object_system_user (objectid, system_username)";
128 $dbms->query($local_sql);
131 $local_sql = "CREATE INDEX obsyus_id ON object_system_user (objectid)";
132 $dbms->query($local_sql);
135 $local_sql = "CREATE INDEX obsyus_name ON object_system_user (system_username)";
136 $dbms->query($local_sql);
139 //Make type_of_issue ready for automatic detection
140 $local_sql = "ALTER TABLE type_of_issue ADD COLUMN automated_check BOOLEAN";
141 $dbms->query($local_sql);
143 $local_sql = "ALTER TABLE type_of_issue ADD COLUMN alert_level INT";
144 $dbms->query($local_sql);
146 $local_sql = "ALTER TABLE type_of_issue ADD COLUMN last_run TIMESTAMP";
147 $dbms->query($local_sql);
149 $local_sql = "ALTER TABLE type_of_issue ADD COLUMN recheck_interval TIMESTAMP";
150 $dbms->query($local_sql);
153 $local_sql = "INSERT INTO db_value (setting, setting_value) VALUES ('log_processing', '0')";
154 $dbms->query($local_sql);
157 $local_sql = "ALTER TABLE log_adv_kernel_network DROP COLUMN hw_address";
158 $dbms->query($local_sql);
161 $local_sql = "ALTER TABLE log_adv_kernel_network ADD COLUMN hw_address TEXT";
162 $dbms->query($local_sql);
164 $local_sql = "DROP TABLE object_system_user";
165 $dbms->query($local_sql);
168 $local_sql = "UPDATE action SET actionname = 'Investigation completed', statuscode = 'PEN', description = 'Investigation has been done. Information is available to fix the problem.' ";
169 $local_sql .= "WHERE actionid = '9'";
170 $dbms->query($local_sql);
173 $local_sql = "insert into type_of_issue (name, suggested_priority, description, active)";
174 $local_sql .= " values ('parameter created',3,'A new parameter was created', true)";
175 $dbms->query($local_sql);
177 $local_sql = "insert into type_of_issue (name, suggested_priority, description, active)";
178 $local_sql .= " values ('property modified',3,'The STATIC property of a parameter was modified', true)";
179 $dbms->query($local_sql);
181 $local_sql = "insert into type_of_issue (name, suggested_priority, description, active)";
182 $local_sql .= " values ('parameter removed',3,'A parameter was removed', true) ";
183 $dbms->query($local_sql);
186 $local_sql = "CREATE TABLE parameter_notification (notificationid bigint, paramid bigint, primary key (notificationid, paramid))";
187 $dbms->query($local_sql);
189 $local_sql = "CREATE TABLE parameter_class (name text, property_name text, description text, property_type text, ";
190 $local_sql .= "min float, max float, notify boolean, primary key (name, property_name));";
191 $dbms->query($local_sql);
193 $local_sql = "INSERT INTO parameter_class (name, property_name, description, property_type, notify)";
194 $local_sql .= "VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't')";
195 $dbms->query($local_sql);
199 $local_sql = "UPDATE action SET statuscode = LOWER(statuscode)";
200 $dbms->query($local_sql);
203 //Create a log_adv_kernel_network table that recognizes the log-records
204 //that have come from the kernel-network interface (typically iptables).
205 $local_sql = "CREATE TABLE log_adv_daemon (";
206 $local_sql .= "service TEXT, event TEXT";
207 $local_sql .= ") INHERITS (log_adv)";
208 $dbms->query($local_sql);
211 $local_sql = "CREATE INDEX log_adv_daemon_service ON log_adv_daemon (service)";
212 $dbms->query($local_sql);
215 $local_sql = "ALTER TABLE object ADD COLUMN log_count BIGINT";
216 $dbms->query($local_sql);
218 $local_sql = "ALTER TABLE object ADD COLUMN notification_count BIGINT";
219 $dbms->query($local_sql);
221 $local_sql = "ALTER TABLE object ADD COLUMN parameter_count BIGINT";
222 $dbms->query($local_sql);
224 $local_sql = "UPDATE object SET log_count = '0', "
225 . "notification_count = '0', "
226 . "parameter_count = '0' ";
227 $dbms->query($local_sql);
230 $local_sql = "UPDATE type_of_issue SET name = TRIM(name)";
231 $dbms->query($local_sql);
234 $local_sql = "INSERT INTO db_value VALUES ('last_notification', '0')";
235 $dbms->query($local_sql);
238 $dbms->query("CREATE GROUP view");
239 $dbms->query("CREATE GROUP ops");
240 $dbms->query("CREATE GROUP admin");
242 $dbms->query("GRANT SELECT ON action TO GROUP view, GROUP ops, GROUP admin");
243 $dbms->query("GRANT SELECT ON action_user TO GROUP view, GROUP ops, GROUP admin");
244 $dbms->query("GRANT SELECT ON db_value TO GROUP view, GROUP ops, GROUP admin");
245 $dbms->query("GRANT SELECT ON history TO GROUP view, GROUP ops, GROUP admin");
246 $dbms->query("GRANT SELECT ON log TO GROUP view, GROUP ops, GROUP admin");
247 $dbms->query("GRANT SELECT ON log_adv TO GROUP view, GROUP ops, GROUP admin");
248 $dbms->query("GRANT SELECT ON log_adv_daemon TO GROUP view, GROUP ops, GROUP admin");
249 $dbms->query("GRANT SELECT ON log_adv_kernel_network TO GROUP view, GROUP ops, GROUP admin");
250 $dbms->query("GRANT SELECT ON log_notification TO GROUP view, GROUP ops, GROUP admin");
251 $dbms->query("GRANT SELECT ON notification TO GROUP view, GROUP ops, GROUP admin");
252 $dbms->query("GRANT SELECT ON object TO GROUP view, GROUP ops, GROUP admin");
253 $dbms->query("GRANT SELECT ON object_issue TO GROUP view, GROUP ops, GROUP admin");
254 $dbms->query("GRANT SELECT ON object_priority TO GROUP view, GROUP ops, GROUP admin");
255 $dbms->query("GRANT SELECT ON object_service TO GROUP view, GROUP ops, GROUP admin");
256 $dbms->query("GRANT SELECT ON object_user TO GROUP view, GROUP ops, GROUP admin");
257 $dbms->query("GRANT SELECT ON parameter TO GROUP view, GROUP ops, GROUP admin");
258 $dbms->query("GRANT SELECT ON parameter_class TO GROUP view, GROUP ops, GROUP admin");
259 $dbms->query("GRANT SELECT ON parameter_notification TO GROUP view, GROUP ops, GROUP admin");
260 $dbms->query("GRANT SELECT ON priority TO GROUP view, GROUP ops, GROUP admin");
261 $dbms->query("GRANT SELECT ON property TO GROUP view, GROUP ops, GROUP admin");
262 $dbms->query("GRANT SELECT ON service TO GROUP view, GROUP ops, GROUP admin");
263 $dbms->query("GRANT SELECT ON status TO GROUP view, GROUP ops, GROUP admin");
264 $dbms->query("GRANT SELECT ON supported_os TO GROUP view, GROUP ops, GROUP admin");
265 $dbms->query("GRANT SELECT ON type_of_issue TO GROUP view, GROUP ops, GROUP admin");
266 $dbms->query("GRANT SELECT ON usr TO GROUP view, GROUP ops, GROUP admin");
268 $dbms->query("GRANT INSERT ON action_user TO GROUP ops, GROUP admin");
269 $dbms->query("GRANT UPDATE ON notification TO GROUP ops, GROUP admin");
270 $dbms->query("GRANT INSERT ON object TO GROUP admin");
271 $dbms->query("GRANT UPDATE ON object TO GROUP ops, GROUP admin");
272 $dbms->query("GRANT DELETE ON object TO GROUP admin");
273 $dbms->query("GRANT UPDATE ON usr TO GROUP view, GROUP ops, GROUP admin");
274 $dbms->query("GRANT INSERT ON usr TO GROUP admin");
275 $dbms->query("GRANT DELETE ON usr TO GROUP admin");
278 $local_sql = "INSERT INTO action (actionname, description) VALUES ('Redisplayed to user', "
279 . "'The notification has been presented to a user. This is not the first time')";
280 $dbms->query ($local_sql);
283 $local_sql = "INSERT INTO action (actionname, description) VALUES ('Displayed in list', "
284 . "'The notification has been presented in a list with other notifications.')";
285 $dbms->query ($local_sql);
288 $local_sql = "CREATE TABLE log_adv_daemon_email (";
289 $local_sql .= "source_ip INET, destination_ip INET, internal_messageid TEXT, ";
290 $local_sql .= "external_messageid TEXT, to_email TEXT, from_email TEXT, delay TIME, xdelay TIME, ";
291 $local_sql .= "mailer TEXT, status TEXT, pid INT";
292 $local_sql .= ") INHERITS (log_adv_daemon)";
293 $dbms->query($local_sql);
296 $local_sql = "CREATE INDEX log_adv_daemon_email_s_ip ON log_adv_daemon_email (source_ip)";
297 $dbms->query($local_sql);
299 $local_sql = "CREATE INDEX log_adv_daemon_email_d_ip ON log_adv_daemon_email (destination_ip)";
300 $dbms->query($local_sql);
302 $local_sql = "CREATE INDEX log_adv_daemon_email_to ON log_adv_daemon_email (to_email)";
303 $dbms->query($local_sql);
305 $local_sql = "CREATE INDEX log_adv_daemon_email_from ON log_adv_daemon_email (from_email)";
306 $dbms->query($local_sql);
310 $local_sql = "ALTER TABLE log_adv_daemon_email ADD COLUMN size INT";
311 $dbms->query($local_sql);
314 $local_sql = "ALTER TABLE log_adv_daemon_email ADD COLUMN pri INT";
315 $dbms->query($local_sql);
317 $local_sql = "ALTER TABLE log_adv_daemon_email ADD COLUMN relay TEXT";
318 $dbms->query($local_sql);
320 $local_sql = "ALTER TABLE log_adv_daemon_email ADD COLUMN status_details TEXT";
321 $dbms->query($local_sql);
323 $local_sql = "CREATE INDEX log_adv_email_status ON log_adv_daemon_email (status)";
324 $dbms->query($local_sql);
327 $local_sql = "ALTER TABLE log_adv_daemon_email ADD COLUMN dsn TEXT";
328 $dbms->query($local_sql);
331 //Create a notification check
333 $local_sql = "CREATE SEQUENCE checkid_seq";
334 $dbms->query($local_sql);
336 $local_sql = "CREATE TABLE notification_check (";
337 $local_sql .= "checkid bigint DEFAULT nextval('checkid_seq'::text) NOT NULL,";
338 $local_sql .= "checkname TEXT, description TEXT, time_between_executions INTERVAL, ";
339 $local_sql .= "last_execution timestamp, execution_counter BIGINT,";
340 $local_sql .= "notificationcounter BIGINT)";
341 $dbms->query($local_sql);
343 $local_sql = "CREATE UNIQUE INDEX not_check_checkid ON notification_check (checkid)";
344 $dbms->query($local_sql);
346 $local_sql = "CREATE UNIQUE INDEX not_check_checkname ON notification_check (checkname)";
347 $dbms->query($local_sql);
349 $local_sql = "CREATE INDEX not_check_check_lastexec ON notification_check (last_execution)";
350 $dbms->query($local_sql);
353 $local_sql = "ALTER TABLE notification_check ADD COLUMN decreasinglist BOOLEAN";
354 $dbms->query($local_sql);
357 $local_sql = "ALTER TABLE notification_check ADD COLUMN type_of_issueid BIGINT";
358 $dbms->query($local_sql);
360 $local_sql = "ALTER TABLE notification_check ALTER COLUMN decreasinglist SET DEFAULT false";
361 $dbms->query($local_sql);
363 $local_sql = "CREATE SEQUENCE checklineid_seq";
364 $dbms->query($local_sql);
366 $local_sql = "CREATE TABLE notification_check_line (";
367 $local_sql .= "checklineid BIGINT DEFAULT nextval('checklineid_seq'::text) NOT NULL,";
368 $local_sql .= "checkid BIGINT, sortorder INTEGER, last_logid BIGINT, ";
369 $local_sql .= "historicboundary INTERVAL, use_logid BOOLEAN, sql_query TEXT";
371 $dbms->query($local_sql);
373 $local_sql = "ALTER TABLE notification_check_line ALTER COLUMN historicboundary SET DEFAULT '0'";
374 $dbms->query($local_sql);
376 $local_sql = "ALTER TABLE notification_check_line ALTER COLUMN use_logid SET DEFAULT false";
377 $dbms->query($local_sql);
379 $local_sql = "ALTER TABLE notification_check_line ALTER COLUMN last_logid SET DEFAULT 0";
380 $dbms->query($local_sql);
383 $local_sql = "CREATE UNIQUE INDEX notcheckline_checklineid ON notification_check_line (checklineid)";
384 $dbms->query($local_sql);
386 $local_sql = "CREATE INDEX notcheckline_checkid ON notification_check_line (checkid)";
387 $dbms->query($local_sql);
389 $local_sql = "CREATE INDEX notcheckline_sort ON notification_check_line (sortorder)";
390 $dbms->query($local_sql);
392 $local_sql = "CREATE INDEX notcheckline_check_sort ON notification_check_line (checkid, sortorder)";
393 $dbms->query($local_sql);
396 $local_sql = "CREATE TABLE notification_check_buffer (";
397 $local_sql .= "checkid BIGINT, sortorder INTEGER, pid INTEGER, logid bigint";
399 $dbms->query($local_sql);
401 $local_sql = "CREATE INDEX notcheckbuffer_checkid ON notification_check_buffer(checkid)";
402 $dbms->query($local_sql);
404 $local_sql = "CREATE INDEX notcheckbuffer_sort ON notification_check_buffer(sortorder)";
405 $dbms->query($local_sql);
407 $local_sql = "CREATE INDEX notcheckbuffer_pid ON notification_check_buffer(pid)";
408 $dbms->query($local_sql);
410 $local_sql = "CREATE INDEX notcheckbuffer_logid ON notification_check_buffer(logid)";
411 $dbms->query($local_sql);
414 $local_sql = "CREATE TABLE object_statistics (";
415 $local_sql .= "objectid bigint,";
416 $local_sql .= "statname text,";
417 $local_sql .= "statvalue double precision,";
418 $local_sql .= "primary key (objectid, statname)";
420 $dbms->query($local_sql);
422 $local_sql = "CREATE INDEX obj_stat_objid ON object_statistics USING btree (objectid)";
423 $dbms->query($local_sql);
426 $local_sql = "CREATE INDEX history_pid_mod ON history(paramid, modified)";
427 $dbms->query($local_sql);
431 // The secondary indices on log_notification should not be unique
433 $local_sql = "DROP INDEX lon_notificationid";
434 $dbms->query($local_sql);
435 $local_sql = "CREATE INDEX lon_notificationid ON log_notification USING btree (notificationid)";
436 $dbms->query($local_sql);
438 $local_sql = "DROP INDEX lon_logid";
439 $dbms->query($local_sql);
440 $local_sql = "CREATE INDEX lon_logid ON log_notification USING btree (logid)";
441 $dbms->query($local_sql);
443 // Additional information for the users
445 $local_sql = "ALTER TABLE usr ADD COLUMN display_name text";
446 $dbms->query($local_sql);
447 $local_sql = "ALTER TABLE usr ADD COLUMN email text";
448 $dbms->query($local_sql);
450 // Add issues for the services check
452 $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
453 $local_sql .= " VALUES ('service unknown',5,'Service in log entry is unknown', true) ";
454 $dbms->query($local_sql);
455 $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
456 $local_sql .= " VALUES ('service not used',5,'Service in log entry is not used', true) ";
457 $dbms->query($local_sql);
459 // Add a few more services
461 $local_sql = "INSERT INTO service VALUES ('CROND','Cron Daemon', 1, 5) ";
462 $dbms->query($local_sql);
463 $local_sql = "INSERT INTO service VALUES ('gnucomo','Gnucomo Daemon', 1, 5) ";
464 $dbms->query($local_sql);
465 $local_sql = "INSERT INTO service VALUES ('sendmail','Mail Transport Agent', 1, 5) ";
466 $dbms->query($local_sql);
467 $local_sql = "INSERT INTO service VALUES ('dhcpd','DHCP Daemon', 1, 5) ";
468 $dbms->query($local_sql);
469 $local_sql = "INSERT INTO service VALUES ('rpc','NFS Services', 1, 5) ";
470 $dbms->query($local_sql);
471 $local_sql = "INSERT INTO service VALUES ('named','DNS Services', 1, 5) ";
472 $dbms->query($local_sql);
473 $local_sql = "INSERT INTO service VALUES ('xinetd','Internet Daemon', 1, 5) ";
474 $dbms->query($local_sql);
475 $local_sql = "INSERT INTO service VALUES ('ipop','Post Office Protocol', 1, 5) ";
476 $dbms->query($local_sql);
477 $local_sql = "INSERT INTO service VALUES ('mgetty','Serial port login and fax', 1, 5) ";
478 $dbms->query($local_sql);
479 $local_sql = "INSERT INTO service VALUES ('login','User login', 1, 5) ";
480 $dbms->query($local_sql);
481 $local_sql = "INSERT INTO service VALUES ('pam','Authentication modules', 1, 5) ";
482 $dbms->query($local_sql);
483 $local_sql = "INSERT INTO service VALUES ('modprobe','Kernel modules', 1, 5) ";
484 $dbms->query($local_sql);
488 // We need to change the type of the 'delay' and 'xdelay' fields in
489 // the 'log_adv_daemon_email' table from type time to type interval.
490 // This can not be done directly; it requires recreating the entire
491 // table and copying data from the old table.
493 $local_sql = "ALTER TABLE log_adv_daemon_email RENAME TO old_table";
494 $dbms->query($local_sql);
496 $local_sql = "CREATE TABLE log_adv_daemon_email
500 internal_messageid TEXT,
501 external_messageid TEXT,
514 ) INHERITS (log_adv_daemon)";
515 $dbms->query($local_sql);
517 $local_sql = "INSERT INTO log_adv_daemon_email SELECT * FROM old_table";
518 $dbms->query($local_sql);
520 $local_sql = "DROP TABLE old_table";
521 $dbms->query($local_sql);
523 // The indices need to be recreated as well.
525 $local_sql = "CREATE INDEX log_adv_daemon_email_s_ip ON log_adv_daemon_email (source_ip)";
526 $dbms->query($local_sql);
528 $local_sql = "CREATE INDEX log_adv_daemon_email_d_ip ON log_adv_daemon_email (destination_ip)";
529 $dbms->query($local_sql);
531 $local_sql = "CREATE INDEX log_adv_daemon_email_to ON log_adv_daemon_email (to_email)";
532 $dbms->query($local_sql);
534 $local_sql = "CREATE INDEX log_adv_daemon_email_from ON log_adv_daemon_email (from_email)";
535 $dbms->query($local_sql);
537 $local_sql = "CREATE INDEX log_adv_email_status ON log_adv_daemon_email (status)";
538 $dbms->query($local_sql);
542 $local_sql = "CREATE TABLE object_abuse
549 primary key (objectid, source)
551 $dbms->query($local_sql);
552 $dbms->query("GRANT SELECT ON log_adv_daemon_email TO GROUP view, GROUP ops, GROUP admin");
553 $dbms->query("GRANT SELECT ON object_abuse TO GROUP view, GROUP ops, GROUP admin");
554 $dbms->query("GRANT INSERT ON object_abuse TO GROUP admin");
555 $dbms->query("GRANT UPDATE ON object_abuse TO GROUP ops, GROUP admin");
556 $dbms->query("GRANT DELETE ON object_abuse TO GROUP admin");
557 $dbms->query("GRANT SELECT ON object_statistics TO GROUP view, GROUP ops, GROUP admin");
559 $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
560 $local_sql .= " VALUES ('abuses exceeded',5,'Abuse treshold exceeded from an IP address', true) ";
561 $dbms->query($local_sql);
565 $local_sql = "CREATE TABLE service_pattern
573 primary key (service, rank)
575 $dbms->query($local_sql);
576 $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999999, '.+', 'notify', 'unmatched log')";
577 $dbms->query($local_sql);
578 $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
579 $local_sql .= " VALUES ('unmatched log',5,'Log entries could not be matched', true) ";
580 $dbms->query($local_sql);
584 $local_sql = "CREATE TABLE log_abuse
590 $dbms->query($local_sql);
594 $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999990, '[Ee][Rr][Rr][Oo][Rr]', 'notify', 'Error detected')";
595 $dbms->query($local_sql);
596 $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
597 $local_sql .= " VALUES ('Error detected',5,'An Error is reported in the log', true) ";
598 $dbms->query($local_sql);
600 $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999991, '[Ff][Aa][Ii][Ll]', 'notify', 'Failure detected')";
601 $dbms->query($local_sql);
602 $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
603 $local_sql .= " VALUES ('Failure detected',5,'A Failure is reported in the log', true) ";
604 $dbms->query($local_sql);
606 $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999992, '[Ww][Aa][Rr][Nn]', 'notify', 'Warning detected')";
607 $dbms->query($local_sql);
608 $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
609 $local_sql .= " VALUES ('Warning detected',3,'A Warning is reported in the log', true) ";
610 $dbms->query($local_sql);
614 $dbms->query("GRANT SELECT ON service_pattern TO GROUP view, GROUP ops, GROUP admin");
615 $dbms->query("GRANT INSERT ON service_pattern TO GROUP admin");
616 $dbms->query("GRANT UPDATE ON service_pattern TO GROUP ops, GROUP admin");
617 $dbms->query("GRANT DELETE ON service_pattern TO GROUP admin");
618 $dbms->query("GRANT SELECT ON log_abuse TO GROUP view, GROUP ops, GROUP admin");
621 $local_sql = "ALTER TABLE object_abuse ADD COLUMN last_change timestamp";
622 $dbms->query($local_sql);
626 $dbms->query("DROP SEQUENCE log_advid_seq");
627 $dbms->query("DROP TABLE log_adv_kernel_network");
628 $dbms->query("DROP TABLE log_adv_daemon_email");
629 $dbms->query("DROP TABLE log_adv_daemon");
630 $dbms->query("DROP TABLE log_adv");
634 $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)";
635 $local_sql .= " VALUES ('property out of range',5,'The DYNAMIC property of a parameter is out of range', true) ";
636 $dbms->query($local_sql);
638 $local_sql = "INSERT INTO parameter_class (name, property_name, description, property_type, notify)";
639 $local_sql .= " VALUES ('filesystem', 'device', 'Volume on which the filesystem resides', 'STATIC', 't')";
640 $dbms->query($local_sql);
641 $local_sql = "INSERT INTO parameter_class (name, property_name, description, property_type, notify)";
642 $local_sql .= " VALUES ('filesystem', 'size', 'Total size in kilobytes', 'STATIC', 't')";
643 $dbms->query($local_sql);
644 $local_sql = "INSERT INTO parameter_class (name, property_name, description, property_type, min, max, notify)";
645 $local_sql .= " VALUES ('filesystem', 'used', 'Number of kilobytes used', 'DYNAMIC', 0, 1000000, 't')";
646 $dbms->query($local_sql);
647 $local_sql = "INSERT INTO parameter_class (name, property_name, description, property_type, min, max, notify)";
648 $local_sql .= " VALUES ('filesystem', 'available', 'Number of kilobytes available', 'DYNAMIC', 1000, 1000000, 't')";
649 $dbms->query($local_sql);
653 $local_sql = "INSERT INTO parameter_class (name, property_name, description, property_type, notify)";
654 $local_sql .= " VALUES ('filesystem', 'Inodes', 'Total number of i-nodes', 'STATIC', 't')";
655 $dbms->query($local_sql);
656 $local_sql = "INSERT INTO parameter_class (name, property_name, description, property_type, min, max, notify)";
657 $local_sql .= " VALUES ('filesystem', 'Iused', 'Number of i-nodes used', 'DYNAMIC', 0, 1000000, 't')";
658 $dbms->query($local_sql);
659 $local_sql = "INSERT INTO parameter_class (name, property_name, description, property_type, min, max, notify)";
660 $local_sql .= " VALUES ('filesystem', 'Ifree', 'Number of i-nodes available', 'DYNAMIC', 1000, 1000000, 't')";
661 $dbms->query($local_sql);
665 $dbms->query("CREATE GROUP daemon");
666 $dbms->query("CREATE ROLE gnucomo LOGIN PASSWORD 'gnucomo' IN GROUP daemon");
668 $dbms->query("GRANT UPDATE ON action_user_actionstepid_seq TO GROUP ops, GROUP admin");
670 $dbms->query("GRANT INSERT ON property TO GROUP admin");
671 $dbms->query("GRANT UPDATE ON property TO GROUP admin");
672 $dbms->query("CREATE INDEX log_ab_src ON log_abuse (source, objectid)");
673 $dbms->query("CREATE UNIQUE INDEX log_ab_log ON log_abuse (logid)");
674 $dbms->query("GRANT INSERT ON action_user TO GROUP daemon");
675 $dbms->query("GRANT DELETE ON action_user TO GROUP daemon");
676 $dbms->query("GRANT INSERT ON log_abuse TO GROUP daemon");
677 $dbms->query("GRANT DELETE ON log_abuse TO GROUP daemon");
678 $dbms->query("GRANT SELECT ON log_abuse TO GROUP daemon");
679 $dbms->query("GRANT INSERT ON log_notification TO GROUP daemon");
680 $dbms->query("GRANT DELETE ON log_notification TO GROUP daemon");
681 $dbms->query("GRANT SELECT ON log_notification TO GROUP daemon");
682 $dbms->query("GRANT SELECT,INSERT,DELETE ON log TO GROUP daemon");
683 $dbms->query("GRANT SELECT,INSERT,DELETE ON notification TO GROUP daemon");
684 $dbms->query("GRANT SELECT,INSERT,DELETE,UPDATE ON parameter_class TO GROUP admin");
685 $dbms->query("GRANT SELECT,INSERT ON parameter TO GROUP daemon");
686 $dbms->query("GRANT SELECT,INSERT ON parameter_notification TO GROUP daemon");
687 $dbms->query("GRANT SELECT,INSERT ON history TO GROUP daemon");
688 $dbms->query("GRANT SELECT,INSERT,UPDATE ON object_abuse TO GROUP daemon");
689 $dbms->query("GRANT SELECT,INSERT,UPDATE ON object_statistics TO GROUP daemon");
690 $dbms->query("GRANT SELECT,INSERT,UPDATE ON property TO GROUP daemon");
691 $dbms->query("GRANT SELECT ON object_service TO GROUP daemon");
692 $dbms->query("GRANT SELECT ON object_user TO GROUP daemon");
693 $dbms->query("GRANT SELECT ON parameter_class TO GROUP daemon");
694 $dbms->query("GRANT SELECT ON service_pattern TO GROUP daemon");
695 $dbms->query("GRANT SELECT ON service TO GROUP daemon");
696 $dbms->query("GRANT SELECT ON type_of_issue TO GROUP daemon");
697 $dbms->query("GRANT SELECT ON usr TO GROUP daemon");
698 $dbms->query("GRANT SELECT,UPDATE ON notification_notificationid_seq TO GROUP daemon");
699 $dbms->query("GRANT SELECT,UPDATE ON db_value TO GROUP daemon");
700 $dbms->query("GRANT SELECT,UPDATE ON object TO GROUP daemon");
701 $dbms->query("GRANT UPDATE ON SEQUENCE action_user_actionstepid_seq TO daemon");
702 $dbms->query("GRANT UPDATE ON SEQUENCE log_logid_seq TO daemon");
703 $dbms->query("GRANT UPDATE ON SEQUENCE paramid_seq TO daemon");
708 //These columns have to be removed when a new version of PGSQL has become mainstream that supportsa DROP COLUMN
711 $local_sql = "ALTER TABLE log DROP COLUMN recognized";
712 $dbms->query($local_sql);
718 $local_sql = "UPDATE db_value SET setting_value = ".$db_version." WHERE setting = 'db_version'";
720 $dbms->query($local_sql);