From: arjen Date: Sat, 4 Jun 2005 07:09:47 +0000 (+0000) Subject: - Fixed field declaration for PostgreSQL 7.4 X-Git-Tag: V_0_0_10~27 X-Git-Url: http://www.andromeda.nl/gitweb/?p=gnucomo.git;a=commitdiff_plain;h=c27b04be397d113b38a3c39598dfd1d64281a94a - Fixed field declaration for PostgreSQL 7.4 - New tables: log_abuse, object_abuse and service_pattern - Added general service patterns in the service 'ANY' - Added new issue types. --- diff --git a/src/database/create.sql b/src/database/create.sql index 33909f0..99f9561 100644 --- a/src/database/create.sql +++ b/src/database/create.sql @@ -12,7 +12,13 @@ -- DBA create the database and give access permissions. -- -- $Log: create.sql,v $ --- Revision 1.18 2003-12-03 08:06:57 arjen +-- Revision 1.19 2005-06-04 07:09:47 arjen +-- - Fixed field declaration for PostgreSQL 7.4 +-- - New tables: log_abuse, object_abuse and service_pattern +-- - Added general service patterns in the service 'ANY' +-- - Added new issue types. +-- +-- Revision 1.18 2003/12/03 08:06:57 arjen -- Changed the type of log_adv_daemon_email.delay and log_adv_daemon_email.xdelay -- from time to interval. These delays can be more than 24 hours. -- @@ -155,7 +161,7 @@ CREATE TABLE "db_value" COPY "db_value" FROM stdin; -db_version 44 +db_version 48 gcm_daemon_version 5 log_processing 0 last_notification 0 @@ -214,6 +220,13 @@ CREATE INDEX log_processed ON log USING btree (processed); SELECT setval ('"log_logid_seq"', 1, false); +CREATE TABLE log_abuse +( + logid bigint, + objectid bigint, + source inet +); + -- -- @@ -384,7 +397,7 @@ CREATE TABLE notification_check checkname TEXT, description TEXT, time_between_executions INTERVAL, - last_execution DATETIME, + last_execution timestamp, execution_counter BIGINT, notificationcounter BIGINT, decreasinglist BOOLEAN default false, @@ -487,6 +500,19 @@ SELECT setval ('"object_objectid_seq"', 1, false); -- -- +CREATE TABLE "object_abuse" +( + objectid bigint, + source inet, + nr_abuses integer, + status text, + + primary key (objectid, source) +); + +-- +-- + CREATE TABLE "object_issue" ( "objectid" bigint, @@ -696,6 +722,22 @@ CREATE UNIQUE INDEX ser_pk ON service USING btree (servicecode); CREATE UNIQUE INDEX ser_servicename ON service USING btree (servicename); +CREATE TABLE service_pattern +( + service text, + rank int, + pattern text, + action text, + argument text, + + primary key (service, rank) +); + +INSERT INTO service_pattern VALUES ('ANY', 999999, '.+', 'notify', 'unmatched log'); +INSERT INTO service_pattern VALUES ('ANY', 999990, '[Ee][Rr][Rr][Oo][Rr]', 'notify', 'Error detected'); +INSERT INTO service_pattern VALUES ('ANY', 999991, '[Ff][Aa][Ii][Ll]', 'notify', 'Failure detected'); +INSERT INTO service_pattern VALUES ('ANY', 999992, '[Ww][Aa][Rr][Nn]', 'notify', 'Warning detected'); + -- CREATE TABLE "status" @@ -755,12 +797,17 @@ CREATE TABLE "type_of_issue" 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 -5 service unknown 5 Service in log entry is unknown t -6 service not used 5 Service in log entry is not used t +1 manual entry 4 A manual entry of a notification t t 1 \N \N +2 parameter created 3 A new parameter was created t t 1 \N \N +3 property modified 3 The STATIC property of a parameter was modified t t 1 \N \N +4 parameter removed 3 A parameter was removed t t 1 \N \N +5 service unknown 5 Service in log entry is unknown t t 1 \N \N +6 service not used 5 Service in log entry is not used t t 1 \N \N +7 abuses exceeded 5 Abuse treshold exceeded from an IP address t t 1 \N \N +8 unmatched log 5 Log entries could not be matched t t 1 \N \N +9 Error detected 5 An Error is reported in the log t t 1 \N \N +9 Failure detected 5 A Failure is reported in the log t t 1 \N \N +10 Warning detected 3 A Warning is reported in the log t t 1 \N \N \. CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid); @@ -806,13 +853,16 @@ 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_daemon_email 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_abuse 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_statistics 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; @@ -830,6 +880,9 @@ GRANT UPDATE ON notification TO GROUP ops, GROUP admin; GRANT INSERT ON object TO GROUP admin; GRANT UPDATE ON object TO GROUP ops, GROUP admin; GRANT DELETE ON object TO GROUP admin; +GRANT INSERT ON object_abuse TO GROUP admin; +GRANT UPDATE ON object_abuse TO GROUP ops, GROUP admin; +GRANT DELETE ON object_abuse TO 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; diff --git a/src/database/destroy.sql b/src/database/destroy.sql index 3b7f389..d466a9f 100644 --- a/src/database/destroy.sql +++ b/src/database/destroy.sql @@ -31,6 +31,7 @@ DROP TABLE "log"; DROP SEQUENCE "log_advid_seq"; +DROP TABLE log_abuse; DROP TABLE log_adv_kernel_network; DROP TABLE log_adv_daemon_email; DROP TABLE log_adv_daemon; @@ -56,6 +57,8 @@ DROP SEQUENCE "object_objectid_seq"; DROP TABLE "object"; +DROP TABLE "object_abuse"; + DROP TABLE "object_issue"; DROP TABLE "object_priority"; @@ -82,6 +85,8 @@ DROP TABLE "property"; DROP TABLE "service"; +DROP TABLE service_pattern + DROP TABLE "status"; DROP TABLE supported_os; diff --git a/src/gcm_daemon/classes/gnucomo_db_version.php b/src/gcm_daemon/classes/gnucomo_db_version.php index f4a4d42..9ce5cfb 100644 --- a/src/gcm_daemon/classes/gnucomo_db_version.php +++ b/src/gcm_daemon/classes/gnucomo_db_version.php @@ -14,8 +14,8 @@ { case 1: //DROP the rule that prohibits updates (change of spec). - $local_sql = "DROP RULE log_update"; - $dbms->query($local_sql); //DISABLED FOR NEW ROLE LOG-tABLE ??? + //$local_sql = "DROP RULE log_update"; + //$dbms->query($local_sql); //DISABLED FOR NEW ROLE LOG-tABLE ??? case 2: //In the log table processed should be false by default @@ -339,7 +339,7 @@ case 36: $local_sql = "CREATE TABLE notification_check ("; $local_sql .= "checkid bigint DEFAULT nextval('checkid_seq'::text) NOT NULL,"; $local_sql .= "checkname TEXT, description TEXT, time_between_executions INTERVAL, "; - $local_sql .= "last_execution DATETIME, execution_counter BIGINT,"; + $local_sql .= "last_execution timestamp, execution_counter BIGINT,"; $local_sql .= "notificationcounter BIGINT)"; $dbms->query($local_sql); @@ -540,6 +540,78 @@ case 43: $local_sql = "CREATE INDEX log_adv_email_status ON log_adv_daemon_email (status)"; $dbms->query($local_sql); + case 44: + + $local_sql = "CREATE TABLE object_abuse + ( + objectid bigint, + source inet, + nr_abuses integer, + status text, + + primary key (objectid, source) + );"; + $dbms->query($local_sql); + $dbms->query("GRANT SELECT ON log_adv_daemon_email TO GROUP view, GROUP ops, GROUP admin"); + $dbms->query("GRANT SELECT ON object_abuse TO GROUP view, GROUP ops, GROUP admin"); + $dbms->query("GRANT INSERT ON object_abuse TO GROUP admin"); + $dbms->query("GRANT UPDATE ON object_abuse TO GROUP ops, GROUP admin"); + $dbms->query("GRANT DELETE ON object_abuse TO GROUP admin"); + $dbms->query("GRANT SELECT ON object_statistics TO GROUP view, GROUP ops, GROUP admin"); + + $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)"; + $local_sql .= " VALUES ('abuses exceeded',5,'Abuse treshold exceeded from an IP address', true) "; + $dbms->query($local_sql); + + case 45: + + $local_sql = "CREATE TABLE service_pattern + ( + service text, + rank int, + pattern text, + action text, + argument text, + + primary key (service, rank) + )"; + $dbms->query($local_sql); + $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999999, '.+', 'notify', 'unmatched log')"; + $dbms->query($local_sql); + $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)"; + $local_sql .= " VALUES ('unmatched log',5,'Log entries could not be matched', true) "; + $dbms->query($local_sql); + + case 46: + + $local_sql = "CREATE TABLE log_abuse + ( + logid bigint, + objectid bigint, + source inet + )"; + $dbms->query($local_sql); + + case 47: + + $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999990, '[Ee][Rr][Rr][Oo][Rr]', 'notify', 'Error detected')"; + $dbms->query($local_sql); + $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)"; + $local_sql .= " VALUES ('Error detected',5,'An Error is reported in the log', true) "; + $dbms->query($local_sql); + + $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999991, '[Ff][Aa][Ii][Ll]', 'notify', 'Failure detected')"; + $dbms->query($local_sql); + $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)"; + $local_sql .= " VALUES ('Failure detected',5,'A Failure is reported in the log', true) "; + $dbms->query($local_sql); + + $local_sql = "INSERT INTO service_pattern VALUES ('ANY', 999992, '[Ww][Aa][Rr][Nn]', 'notify', 'Warning detected')"; + $dbms->query($local_sql); + $local_sql = "INSERT INTO type_of_issue (name, suggested_priority, description, active)"; + $local_sql .= " VALUES ('Warning detected',3,'A Warning is reported in the log', true) "; + $dbms->query($local_sql); + //These columns have to be removed when a new version of PGSQL has become mainstream that supportsa DROP COLUMN /*