-- DBA create the database and give access permissions.
--
-- $Log: create.sql,v $
--- Revision 1.14 2003-07-09 07:14:59 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.
--
CREATE INDEX act_statuscode ON "action" USING btree (statuscode);
-SELECT setval ('"action_actionid_seq"', 19, true);
+SELECT setval ('"action_actionid_seq"', 21, true);
--
--
COPY "db_value" FROM stdin;
-db_version 41
+db_version 53
gcm_daemon_version 5
log_processing 0
+log_servicecheck 0
last_notification 0
\.
);
+CREATE INDEX history_pid_mod ON history(paramid, modified);
+
--
--
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);
+CREATE UNIQUE INDEX log_ab_log ON log_abuse (logid);
--
--
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);
--
--
checkname TEXT,
description TEXT,
time_between_executions INTERVAL,
- last_execution DATETIME,
+ last_execution timestamp,
execution_counter BIGINT,
notificationcounter BIGINT,
decreasinglist BOOLEAN default false,
checkid BIGINT,
sortorder INTEGER,
last_logid BIGINT default 0,
- historicboundary INTERVAL default 0,
+ historicboundary INTERVAL default '0',
use_logid BOOLEAN default false,
sql_query TEXT
);
--
--
+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,
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');
+
--
--
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"
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);
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);
--
--
"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);
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 object 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 DELETE ON object 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;
projects / gnucomo.git / blobdiff