-- DBA create the database and give access permissions.
--
-- $Log: create.sql,v $
--- Revision 1.9 2003-02-13 08:46:54 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.
+--
+-- 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
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);
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 28
-gcm_daemon_version 1
+db_version 48
+gcm_daemon_version 5
log_processing 0
last_notification 0
\.
);
+CREATE INDEX history_pid_mod ON history(paramid, modified);
+
--
--
SELECT setval ('"log_logid_seq"', 1, false);
+CREATE TABLE log_abuse
+(
+ logid bigint,
+ objectid bigint,
+ source inet
+);
+
--
--
CREATE TABLE log_adv_daemon
(
service TEXT,
- event 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 '
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);
--
--
--
--
+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"
--
--
+CREATE TABLE "object_abuse"
+(
+ objectid bigint,
+ source inet,
+ nr_abuses integer,
+ status text,
+
+ primary key (objectid, source)
+);
+
+--
+--
+
CREATE TABLE "object_issue"
(
"objectid" bigint,
--
--
+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,
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 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);
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"', 6, 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 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;
+
+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 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;
+GRANT SELECT ON parameter_notification TO GROUP view, GROUP ops, GROUP admin;
+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 service TO GROUP view, GROUP ops, GROUP admin;
+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 usr TO GROUP view, GROUP ops, GROUP admin;
+
+GRANT INSERT ON action_user 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;
+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;
projects / gnucomo.git / blobdiff