-- DBA create the database and give access permissions.
--
-- $Log: create.sql,v $
--- Revision 1.21 2007-10-23 11:25:42 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
COPY "db_value" FROM stdin;
-db_version 48
+db_version 53
gcm_daemon_version 5
log_processing 0
+log_servicecheck 0
last_notification 0
\.
source inet
);
+CREATE INDEX log_ab_src ON log_abuse (source, objectid);
+CREATE UNIQUE INDEX log_ab_log ON log_abuse (logid);
--
--
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');
+
--
--
COPY "type_of_issue" FROM stdin;
-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
-10 Failure detected 5 A Failure is reported in the log t t 1 \N \N
-11 Warning detected 3 A Warning is reported in the log t t 1 \N \N
+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"', 11, true);
+SELECT setval ('"type_of_issue_type_of_issue_seq"', 12, true);
--
--
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 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;
projects / gnucomo.git / blobdiff