X-Git-Url: http://www.andromeda.nl/gitweb/?a=blobdiff_plain;f=src%2Fdatabase%2Fcreate.sql;h=211da8701cb10304e43f43b33e1d29ff4b80fff7;hb=9feb4b819b2254ff385c8ddb14022c653d21ef37;hp=1b62eea636f1f2d046802c247ccf274f6bde252f;hpb=83dbd19fdbac87f29462b01010e4db751d692939;p=gnucomo.git diff --git a/src/database/create.sql b/src/database/create.sql index 1b62eea..211da87 100644 --- a/src/database/create.sql +++ b/src/database/create.sql @@ -12,7 +12,14 @@ -- DBA create the database and give access permissions. -- -- $Log: create.sql,v $ --- Revision 1.22 2007-11-03 10:30:10 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'. -- @@ -172,9 +179,10 @@ CREATE TABLE "db_value" COPY "db_value" FROM stdin; -db_version 48 +db_version 53 gcm_daemon_version 5 log_processing 0 +log_servicecheck 0 last_notification 0 \. @@ -238,6 +246,7 @@ CREATE TABLE log_abuse source inet ); +CREATE INDEX log_ab_src ON log_abuse (source, objectid); -- -- @@ -719,18 +728,18 @@ CREATE TABLE "type_of_issue" 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 -12 property out of range 5 The DYNAMIC property of a parameter is out of range 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); @@ -768,46 +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 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;