X-Git-Url: http://www.andromeda.nl/gitweb/?a=blobdiff_plain;f=src%2Fdatabase%2Fcreate.sql;h=e89b7ed1125012ce8a3da5c16064e31dc08d5d33;hb=fac85e60fba231c5856191bd672b45d60b49d2b0;hp=b2105ae68121a54d472b638ffa63befe4684f258;hpb=ab249f98844bcefa2af67d5a8fd62d1eff3d573c;p=gnucomo.git diff --git a/src/database/create.sql b/src/database/create.sql index b2105ae..e89b7ed 100644 --- a/src/database/create.sql +++ b/src/database/create.sql @@ -12,7 +12,66 @@ -- DBA create the database and give access permissions. -- -- $Log: create.sql,v $ --- Revision 1.9 2003-02-13 08:46:54 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 +-- Added index to the history table. +-- +-- Revision 1.14 2003/07/09 07:14:59 arjen +-- New database tables: notification_check, notification_check_buffer, +-- notification_check_line and object_statistics. +-- +-- Revision 1.13 2003/03/29 08:27:05 arjen +-- New columns in the table 'log_adv_daemon_email': size, pri, relay, +-- status_details and dsn. +-- Added several indices for the table 'log_adv_daemon_email'. +-- +-- Revision 1.12 2003/02/21 08:38:38 arjen +-- Added new table to the database: log_adv_daemon_email. +-- +-- Revision 1.11 2003/02/16 08:24:38 arjen +-- Added a new entry to the action table: Notification was displayed in the listing +-- +-- 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 @@ -64,6 +123,8 @@ COPY "action" FROM stdin; 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. +20 Redisplayed to user \N The notification has been presented to a user. This is not the first time +21 Displayed in list \N The notification has been presented in a list with other notifications. \. CREATE UNIQUE INDEX action_actionid_key ON "action" USING btree (actionid); @@ -74,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); -- -- @@ -118,9 +179,10 @@ CREATE TABLE "db_value" COPY "db_value" FROM stdin; -db_version 28 -gcm_daemon_version 1 +db_version 53 +gcm_daemon_version 5 log_processing 0 +log_servicecheck 0 last_notification 0 \. @@ -138,6 +200,8 @@ CREATE TABLE "history" ); +CREATE INDEX history_pid_mod ON history(paramid, modified); + -- -- @@ -175,84 +239,15 @@ 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 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); +CREATE UNIQUE INDEX log_ab_log ON log_abuse (logid); -- -- @@ -264,9 +259,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); -- -- @@ -310,6 +305,73 @@ SELECT setval ('"notification_notificationid_seq"', 1, false); -- -- +CREATE SEQUENCE checkid_seq; + +CREATE TABLE notification_check +( + checkid bigint DEFAULT nextval('checkid_seq'::text) NOT NULL, + checkname TEXT, + description TEXT, + time_between_executions INTERVAL, + last_execution timestamp, + execution_counter BIGINT, + notificationcounter BIGINT, + decreasinglist BOOLEAN default false, + type_of_issueid BIGINT +); + +CREATE UNIQUE INDEX not_check_checkid ON notification_check (checkid); + +CREATE UNIQUE INDEX not_check_checkname ON notification_check (checkname); + +CREATE INDEX not_check_check_lastexec ON notification_check (last_execution); + +-- +-- + +CREATE TABLE notification_check_buffer +( + checkid BIGINT, + sortorder INTEGER, + pid INTEGER, + logid bigint +); + +CREATE INDEX notcheckbuffer_checkid ON notification_check_buffer(checkid); + +CREATE INDEX notcheckbuffer_sort ON notification_check_buffer(sortorder); + +CREATE INDEX notcheckbuffer_pid ON notification_check_buffer(pid); + +CREATE INDEX notcheckbuffer_logid ON notification_check_buffer(logid); + +-- +-- + +CREATE SEQUENCE checklineid_seq; + +CREATE TABLE notification_check_line +( + checklineid BIGINT DEFAULT nextval('checklineid_seq'::text) NOT NULL, + checkid BIGINT, + sortorder INTEGER, + last_logid BIGINT default 0, + historicboundary INTERVAL default '0', + use_logid BOOLEAN default false, + sql_query TEXT +); + +CREATE UNIQUE INDEX notcheckline_checklineid ON notification_check_line (checklineid); + +CREATE INDEX notcheckline_checkid ON notification_check_line (checkid); + +CREATE INDEX notcheckline_sort ON notification_check_line (sortorder); + +CREATE INDEX notcheckline_check_sort ON notification_check_line (checkid, sortorder); + +-- +-- + CREATE SEQUENCE "object_objectid_seq"; CREATE TABLE "object" @@ -354,6 +416,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, @@ -416,6 +492,20 @@ CREATE INDEX obs_accepted ON object_service USING btree (accepted); -- -- +CREATE TABLE object_statistics +( + objectid bigint, + statname text, + statvalue double precision, + + primary key (objectid, statname) +); + +CREATE INDEX obj_stat_objid ON object_statistics USING btree (objectid); + +-- +-- + CREATE TABLE "object_user" ( "objectid" bigint, @@ -470,6 +560,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'); + -- -- @@ -531,12 +636,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" @@ -596,10 +729,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); @@ -608,7 +749,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); -- -- @@ -618,7 +759,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); @@ -629,3 +772,108 @@ CREATE INDEX usr_account_active ON usr USING btree (account_active); 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; +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 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 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;