Log_adv and derived tables removed.

[gnucomo.git] / src / database / create.sql

diff --git a/src/database/create.sql b/src/database/create.sql
index 99f9561..a17028a 100644 (file)
--- a/src/database/create.sql
+++ b/src/database/create.sql
@@ -12,7 +12,11 @@
 -- DBA create the database and give access permissions.
 --
 --  $Log: create.sql,v $
---  Revision 1.19  2005-06-04 07:09:47  arjen
+--  Revision 1.20  2007-01-11 13:47:35  arjen
+--  Log_adv and derived tables removed.
+--  Create notifications from log entries with pattern matching.
+--
+--  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'
@@ -230,111 +234,6 @@ CREATE TABLE log_abuse
 --
 --
 
-CREATE SEQUENCE "log_advid_seq";
-
-
-CREATE TABLE "log_adv"
-(
-       "log_advid" bigint DEFAULT
-              nextval('"log_advid_seq"'::text) NOT NULL,
-       "logid" bigint NOT NULL,
-       "detailed_table"    text
-);
-
-
-CREATE INDEX log_adv_logid ON log_adv USING btree (logid);
-
-CREATE UNIQUE INDEX log_adv_log_advid ON log_adv USING btree (log_advid);
-
-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 TABLE log_adv_daemon_email
-(
-     source_ip            INET,
-     destination_ip       INET,
-     internal_messageid   TEXT, 
-     external_messageid   TEXT,
-     to_email             TEXT,
-     from_email           TEXT,
-     delay                interval,
-     xdelay               interval,
-     mailer               TEXT,
-     status               TEXT,
-     pid                  INT,
-     size                 INT,
-     pri                  INT,
-     relay                TEXT,
-     status_details       TEXT,
-     dsn                  TEXT
-) INHERITS (log_adv_daemon);
-
-CREATE INDEX log_adv_daemon_email_s_ip ON log_adv_daemon_email (source_ip);
-CREATE INDEX log_adv_daemon_email_d_ip ON log_adv_daemon_email (destination_ip);
-CREATE INDEX log_adv_daemon_email_to   ON log_adv_daemon_email (to_email);
-CREATE INDEX log_adv_daemon_email_from ON log_adv_daemon_email (from_email);
-CREATE INDEX log_adv_email_status      ON log_adv_daemon_email (status);
-
-
-/*
-CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
-CREATE FUNCTION "funct_processlog" () RETURNS opaque AS '
--- Initial date: September 18th 2002
--- Update: November 13th 2002
--- Author: Brenno J.S.A.A.F. de Winter
--- Abstract: This routine sets a flag
-DECLARE
-   var_value   VARCHAR;
-   var_setting VARCHAR;
-
-BEGIN
-
-    UPDATE db_value SET setting_value = ''TRUE'' WHERE setting = ''log_flag'';
-    RETURN NULL;
-END;
- ' LANGUAGE 'plpgsql';
-
-
-CREATE TRIGGER "log_insert" AFTER INSERT ON "log"  FOR EACH ROW EXECUTE PROCEDURE "funct_processlog" ();
-*/
---
---
-
 CREATE TABLE "log_notification"
 (
        "notificationid" bigint,
@@ -506,6 +405,7 @@ CREATE TABLE "object_abuse"
         source     inet,
         nr_abuses  integer,
         status     text,
+        last_change   timestamp,
 
         primary key (objectid, source)
 );
@@ -851,10 +751,6 @@ 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_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;
@@ -876,6 +772,7 @@ 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 action_user_actionstepid_seq TO GROUP ops, GROUP admin;
 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;
@@ -886,3 +783,8 @@ 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;
+GRANT SELECT ON service_pattern TO GROUP view, GROUP ops, GROUP admin;
+GRANT INSERT ON service_pattern TO GROUP admin;
+GRANT UPDATE ON service_pattern TO GROUP ops, GROUP admin;
+GRANT DELETE ON service_pattern TO GROUP admin;
+GRANT SELECT ON log_abuse TO GROUP view, GROUP ops, GROUP admin;