X-Git-Url: http://www.andromeda.nl/gitweb/?a=blobdiff_plain;f=src%2Fdatabase%2Fcreate.sql;h=211da8701cb10304e43f43b33e1d29ff4b80fff7;hb=9feb4b819b2254ff385c8ddb14022c653d21ef37;hp=3ce709834229b872afeb2d5ec0b219cb5dd4867f;hpb=1c248ec8abfae3840e9cce9e2cdd87c326614d90;p=gnucomo.git diff --git a/src/database/create.sql b/src/database/create.sql index 3ce7098..211da87 100644 --- a/src/database/create.sql +++ b/src/database/create.sql @@ -12,7 +12,40 @@ -- DBA create the database and give access permissions. -- -- $Log: create.sql,v $ --- Revision 1.16 2003-08-16 14:29:02 arjen +-- Revision 1.24 2011-03-23 17:04:41 arjen +-- Added a new group daemon and created additional permissions to the database. +-- +-- Revision 1.23 2007/11/17 09:32:24 arjen +-- Minor fixes to make created and upgraded databases equal. +-- Cleanup of database destruction script. +-- +-- Revision 1.22 2007/11/03 10:30:10 arjen +-- Added the class definition for a filesystem parameter. +-- New issue type: 'property out of range'. +-- +-- Revision 1.21 2007/10/23 11:25:42 arjen +-- Fixed the Failure and warning issues. +-- +-- 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' +-- - 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. +-- +-- Revision 1.17 2003/09/02 12:49:47 arjen +-- BUGFIX: Secondary indices on log_notification were unique. +-- Additional information in the 'usr' table: 'display_name' and 'email'. +-- Added new issues and services. +-- +-- Revision 1.16 2003/08/16 14:29:02 arjen -- Fixed a few typos. -- -- Revision 1.15 2003/08/05 07:43:24 arjen @@ -102,7 +135,7 @@ CREATE UNIQUE INDEX act_actionname ON "action" USING btree (actionname); CREATE INDEX act_statuscode ON "action" USING btree (statuscode); -SELECT setval ('"action_actionid_seq"', 19, true); +SELECT setval ('"action_actionid_seq"', 21, true); -- -- @@ -146,9 +179,10 @@ CREATE TABLE "db_value" COPY "db_value" FROM stdin; -db_version 41 +db_version 53 gcm_daemon_version 5 log_processing 0 +log_servicecheck 0 last_notification 0 \. @@ -205,111 +239,14 @@ CREATE INDEX log_processed ON log USING btree (processed); SELECT setval ('"log_logid_seq"', 1, false); --- --- - -CREATE SEQUENCE "log_advid_seq"; - - -CREATE TABLE "log_adv" +CREATE TABLE log_abuse ( - "log_advid" bigint DEFAULT - nextval('"log_advid_seq"'::text) NOT NULL, - "logid" bigint NOT NULL, - "detailed_table" text + logid bigint, + objectid bigint, + source inet ); - -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 TIME, - xdelay TIME, - 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 INDEX log_ab_src ON log_abuse (source, objectid); -- -- @@ -321,9 +258,9 @@ CREATE TABLE "log_notification" CREATE UNIQUE INDEX lon_pk ON log_notification USING btree (notificationid, logid); -CREATE UNIQUE INDEX lon_notificationid ON log_notification USING btree (notificationid); +CREATE INDEX lon_notificationid ON log_notification USING btree (notificationid); -CREATE UNIQUE INDEX lon_logid ON log_notification USING btree (logid); +CREATE INDEX lon_logid ON log_notification USING btree (logid); -- -- @@ -375,7 +312,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, @@ -478,6 +415,20 @@ SELECT setval ('"object_objectid_seq"', 1, false); -- -- +CREATE TABLE "object_abuse" +( + objectid bigint, + source inet, + nr_abuses integer, + status text, + last_change timestamp, + + primary key (objectid, source) +); + +-- +-- + CREATE TABLE "object_issue" ( "objectid" bigint, @@ -608,6 +559,21 @@ CREATE TABLE "parameter_class" INSERT INTO parameter_class (name, property_name, description, property_type, notify) VALUES ('package', 'version', 'The installed version of the package', 'STATIC', 't'); +INSERT INTO parameter_class (name, property_name, description, property_type, notify) + VALUES ('filesystem', 'device', 'Volume on which the filesystem resides', 'STATIC', 't'); +INSERT INTO parameter_class (name, property_name, description, property_type, notify) + VALUES ('filesystem', 'size', 'Total size in kilobytes', 'STATIC', 't'); +INSERT INTO parameter_class (name, property_name, description, property_type, min, max, notify) + VALUES ('filesystem', 'used', 'Number of kilobytes used', 'DYNAMIC', 0, 1000000, 't'); +INSERT INTO parameter_class (name, property_name, description, property_type, min, max, notify) + VALUES ('filesystem', 'available', 'Number of kilobytes available', 'DYNAMIC', 1000, 1000000, 't'); +INSERT INTO parameter_class (name, property_name, description, property_type, notify) + VALUES ('filesystem', 'Inodes', 'Total number of i-nodes', 'STATIC', 't'); +INSERT INTO parameter_class (name, property_name, description, property_type, min, max, notify) + VALUES ('filesystem', 'Iused', 'Number of i-nodes used', 'DYNAMIC', 0, 1000000, 't'); +INSERT INTO parameter_class (name, property_name, description, property_type, min, max, notify) + VALUES ('filesystem', 'Ifree', 'Number of i-nodes available', 'DYNAMIC', 1000, 1000000, 't'); + -- -- @@ -669,12 +635,40 @@ kernel kernel 1 5 sshd sshd 1 5 su su 1 5 syslogd syslogd 1 5 +CROND Cron Daemon 1 5 +gnucomo Gnucomo Daemon 1 5 +sendmail Mail Transport Agent 1 5 +dhcpd DHCP Daemon 1 5 +rpc NFS Services 1 5 +named DNS Services 1 5 +xinetd Internet Daemon 1 5 +ipop Post Office Protocol 1 5 +mgetty Serial port login and fax 1 5 +login User login 1 5 +pam Authentication modules 1 5 +modprobe Kernel modules 1 5 \. 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" @@ -734,10 +728,18 @@ 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 +1 manual entry 4 A manual entry of a notification t \N \N \N \N +2 parameter created 3 A new parameter was created t \N \N \N \N +3 property modified 3 The STATIC property of a parameter was modified t \N \N \N \N +4 parameter removed 3 A parameter was removed t \N \N \N \N +5 service unknown 5 Service in log entry is unknown t \N \N \N \N +6 service not used 5 Service in log entry is not used t \N \N \N \N +7 abuses exceeded 5 Abuse treshold exceeded from an IP address t \N \N \N \N +8 unmatched log 5 Log entries could not be matched t \N \N \N \N +9 Error detected 5 An Error is reported in the log t \N \N \N \N +10 Failure detected 5 A Failure is reported in the log t \N \N \N \N +11 Warning detected 3 A Warning is reported in the log t \N \N \N \N +12 property out of range 5 The DYNAMIC property of a parameter is out of range t \N \N \N \N \. CREATE UNIQUE INDEX type_of_issue_type_of_issue_key ON type_of_issue USING btree (type_of_issueid); @@ -746,7 +748,7 @@ CREATE UNIQUE INDEX toi_name ON type_of_issue USING btree (name); CREATE INDEX toi_active ON type_of_issue USING btree (active); -SELECT setval ('"type_of_issue_type_of_issue_seq"', 4, true); +SELECT setval ('"type_of_issue_type_of_issue_seq"', 12, true); -- -- @@ -756,7 +758,9 @@ CREATE TABLE "usr" "username" text NOT NULL, "active_sessionid" bigint, "account_active" boolean, - "security_level" integer + "security_level" integer, + display_name text, + email text ); CREATE UNIQUE INDEX usr_username ON usr USING btree (username); @@ -773,38 +777,102 @@ CREATE INDEX usr_security_level ON usr USING btree (security_level); CREATE GROUP view; CREATE GROUP ops; CREATE GROUP admin; +CREATE GROUP daemon; + +-- The default user 'gnucomo' used by the daemons + +CREATE ROLE gnucomo LOGIN PASSWORD 'gnucomo' IN GROUP daemon; 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 db_value TO GROUP daemon; GRANT SELECT ON history TO GROUP view, GROUP ops, GROUP admin; +GRANT SELECT ON history TO GROUP daemon; 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 TO GROUP daemon; 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 notification TO GROUP daemon; +GRANT SELECT ON object TO GROUP view, GROUP ops, GROUP admin, GROUP daemon; +GRANT SELECT ON object_abuse TO GROUP view, GROUP ops, GROUP admin; +GRANT SELECT ON object_abuse TO GROUP daemon; 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_service TO GROUP daemon; +GRANT SELECT ON object_statistics TO GROUP view, GROUP ops, GROUP admin; +GRANT SELECT ON object_statistics TO GROUP daemon; GRANT SELECT ON object_user TO GROUP view, GROUP ops, GROUP admin; +GRANT SELECT ON object_user TO GROUP daemon; GRANT SELECT ON parameter TO GROUP view, GROUP ops, GROUP admin; +GRANT SELECT ON parameter TO GROUP daemon; GRANT SELECT ON parameter_class TO GROUP view, GROUP ops, GROUP admin; +GRANT SELECT ON parameter_class TO GROUP daemon; GRANT SELECT ON parameter_notification TO GROUP view, GROUP ops, GROUP admin; +GRANT SELECT ON parameter_notification TO GROUP daemon; 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 property TO GROUP daemon; GRANT SELECT ON service TO GROUP view, GROUP ops, GROUP admin; +GRANT SELECT ON service TO GROUP daemon; 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 type_of_issue TO GROUP daemon; GRANT SELECT ON usr TO GROUP view, GROUP ops, GROUP admin; +GRANT SELECT ON usr TO GROUP daemon; GRANT INSERT ON action_user TO GROUP ops, GROUP admin; +GRANT INSERT ON action_user TO GROUP daemon; +GRANT DELETE ON action_user TO GROUP daemon; +GRANT UPDATE ON action_user_actionstepid_seq TO GROUP ops, GROUP admin; +GRANT UPDATE ON action_user_actionstepid_seq TO GROUP daemon; +GRANT UPDATE ON db_value TO GROUP daemon; +GRANT INSERT ON log TO GROUP daemon; +GRANT DELETE ON log TO GROUP daemon; +GRANT UPDATE ON log_logid_seq TO GROUP daemon; +GRANT INSERT ON log_notification TO GROUP daemon; +GRANT SELECT ON log_notification TO GROUP daemon; +GRANT DELETE ON log_notification TO GROUP daemon; GRANT UPDATE ON notification TO GROUP ops, GROUP admin; +GRANT INSERT ON notification TO GROUP daemon; +GRANT DELETE ON notification TO GROUP daemon; GRANT INSERT ON object TO GROUP admin; GRANT UPDATE ON object TO GROUP ops, GROUP admin; +GRANT UPDATE ON object TO GROUP daemon; 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 INSERT ON log_abuse TO GROUP daemon; +GRANT INSERT ON object_abuse TO GROUP daemon; +GRANT UPDATE ON object_abuse TO GROUP daemon; +GRANT INSERT ON object_statistics TO GROUP daemon; +GRANT UPDATE ON object_statistics TO GROUP daemon; + +GRANT INSERT ON parameter_notification TO GROUP daemon; +GRANT INSERT ON parameter TO GROUP daemon; +GRANT UPDATE ON paramid_seq TO GROUP daemon; +GRANT UPDATE ON notification_notificationid_seq TO GROUP daemon; +GRANT SELECT ON notification_notificationid_seq TO GROUP daemon; +GRANT INSERT ON property TO GROUP daemon; +GRANT UPDATE ON property TO GROUP daemon; +GRANT INSERT ON property TO GROUP admin; +GRANT UPDATE ON property TO GROUP admin; +GRANT INSERT ON history TO GROUP daemon; +GRANT INSERT ON parameter_class TO GROUP admin; +GRANT UPDATE ON parameter_class TO GROUP admin; +GRANT DELETE ON parameter_class 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 SELECT ON service_pattern TO GROUP daemon; +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; +GRANT SELECT ON log_abuse TO GROUP daemon; +GRANT DELETE ON log_abuse TO GROUP daemon;