]>
Commit | Line | Data |
---|---|---|
a355f100 DM |
1 | package PMG::DBTools; |
2 | ||
3 | use strict; | |
4 | use warnings; | |
5 | ||
6 | use POSIX ":sys_wait_h"; | |
7 | use POSIX ':signal_h'; | |
8 | use DBI; | |
9 | ||
10 | use PVE::Tools; | |
11 | ||
0a580593 DM |
12 | use PMG::RuleDB; |
13 | ||
a355f100 DM |
14 | sub open_ruledb { |
15 | my ($database, $host, $port) = @_; | |
16 | ||
17 | $port = 5432 if !$port; | |
18 | ||
19 | $database = "Proxmox_ruledb" if !$database; | |
20 | ||
21 | if ($host) { | |
22 | ||
23 | my $dsn = "dbi:Pg:dbname=$database;host=$host;port=$port;"; | |
24 | ||
25 | my $timeout = 5; | |
26 | # only low level alarm interface works for DBI->connect | |
27 | my $mask = POSIX::SigSet->new(SIGALRM); | |
28 | my $action = POSIX::SigAction->new(sub { die "connect timeout\n" }, $mask); | |
29 | my $oldaction = POSIX::SigAction->new(); | |
30 | sigaction(SIGALRM, $action, $oldaction); | |
31 | ||
32 | my $rdb; | |
33 | ||
34 | eval { | |
35 | alarm($timeout); | |
36 | $rdb = DBI->connect($dsn, "postgres", undef, | |
37 | { PrintError => 0, RaiseError => 1 }); | |
38 | alarm(0); | |
39 | }; | |
40 | alarm(0); | |
41 | sigaction(SIGALRM, $oldaction); # restore original handler | |
f1bf78ff | 42 | |
a355f100 DM |
43 | die $@ if $@; |
44 | ||
45 | return $rdb; | |
46 | } else { | |
47 | my $dsn = "DBI:Pg:dbname=$database"; | |
48 | ||
f1bf78ff | 49 | my $dbh = DBI->connect($dsn, "postgres", undef, |
a355f100 DM |
50 | { PrintError => 0, RaiseError => 1 }); |
51 | ||
52 | return $dbh; | |
53 | } | |
54 | } | |
55 | ||
56 | sub delete_ruledb { | |
57 | my ($dbname) = @_; | |
58 | ||
59 | PVE::Tools::run_command(['dropdb', '-U', 'postgres', $dbname]); | |
60 | } | |
61 | ||
f1bf78ff DM |
62 | sub database_list { |
63 | ||
64 | my $database_list = {}; | |
65 | ||
66 | my $parser = sub { | |
67 | my $line = shift; | |
68 | ||
69 | my ($name, $owner) = map { PVE::Tools::trim($_) } split(/\|/, $line); | |
70 | return if !$name || !$owner; | |
71 | ||
72 | $database_list->{$name} = { owner => $owner }; | |
73 | }; | |
74 | ||
75 | my $cmd = ['psql', '-U', 'postgres', '--list', '--quiet', '--tuples-only']; | |
76 | ||
77 | PVE::Tools::run_command($cmd, outfunc => $parser); | |
78 | ||
79 | return $database_list; | |
80 | } | |
81 | ||
82 | my $dbfunction_maxint = <<__EOD; | |
83 | CREATE OR REPLACE FUNCTION maxint (INTEGER, INTEGER) RETURNS INTEGER AS | |
84 | 'BEGIN IF \$1 > \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql; | |
85 | __EOD | |
86 | ||
87 | my $dbfunction_minint = <<__EOD; | |
88 | CREATE OR REPLACE FUNCTION minint (INTEGER, INTEGER) RETURNS INTEGER AS | |
89 | 'BEGIN IF \$1 < \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql; | |
90 | __EOD | |
91 | ||
92 | # merge function to avoid update/insert race condition | |
93 | # see: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING | |
94 | my $dbfunction_merge_greylist = <<__EOD; | |
95 | CREATE OR REPLACE FUNCTION merge_greylist (in_ipnet VARCHAR, in_host INTEGER, in_sender VARCHAR, | |
96 | in_receiver VARCHAR, in_instance VARCHAR, | |
97 | in_rctime INTEGER, in_extime INTEGER, in_delay INTEGER, | |
98 | in_blocked INTEGER, in_passed INTEGER, in_mtime INTEGER, | |
99 | in_cid INTEGER) RETURNS INTEGER AS | |
100 | 'BEGIN | |
101 | LOOP | |
102 | UPDATE CGreylist SET Host = CASE WHEN MTime >= in_mtime THEN Host ELSE in_host END, | |
103 | CID = maxint (CID, in_cid), RCTime = minint (rctime, in_rctime), | |
104 | ExTime = maxint (extime, in_extime), | |
105 | Delay = maxint (delay, in_delay), | |
106 | Blocked = maxint (blocked, in_blocked), | |
107 | Passed = maxint (passed, in_passed) | |
108 | WHERE IPNet = in_ipnet AND Sender = in_sender AND Receiver = in_receiver; | |
109 | ||
110 | IF found THEN | |
111 | RETURN 0; | |
112 | END IF; | |
113 | ||
114 | BEGIN | |
115 | INSERT INTO CGREYLIST (IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, MTime, CID) | |
116 | VALUES (in_ipnet, in_host, in_sender, in_receiver, in_instance, in_rctime, in_extime, | |
117 | in_delay, in_blocked, in_passed, in_mtime, in_cid); | |
118 | RETURN 1; | |
119 | EXCEPTION WHEN unique_violation THEN | |
120 | -- do nothing - continue loop | |
121 | END; | |
122 | END LOOP; | |
123 | END;' LANGUAGE plpgsql; | |
124 | __EOD | |
125 | ||
126 | my $cgreylist_ctablecmd = <<__EOD; | |
127 | CREATE TABLE CGreylist | |
128 | (IPNet VARCHAR(16) NOT NULL, | |
129 | Host INTEGER NOT NULL, | |
130 | Sender VARCHAR(255) NOT NULL, | |
131 | Receiver VARCHAR(255) NOT NULL, | |
132 | Instance VARCHAR(255), | |
133 | RCTime INTEGER NOT NULL, | |
134 | ExTime INTEGER NOT NULL, | |
135 | Delay INTEGER NOT NULL DEFAULT 0, | |
136 | Blocked INTEGER NOT NULL, | |
137 | Passed INTEGER NOT NULL, | |
138 | CID INTEGER NOT NULL, | |
139 | MTime INTEGER NOT NULL, | |
140 | PRIMARY KEY (IPNet, Sender, Receiver)); | |
141 | ||
142 | CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender); | |
143 | ||
144 | CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime); | |
145 | ||
146 | CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime); | |
147 | __EOD | |
148 | ||
149 | my $clusterinfo_ctablecmd = <<__EOD; | |
150 | CREATE TABLE ClusterInfo | |
151 | (CID INTEGER NOT NULL, | |
152 | Name VARCHAR NOT NULL, | |
153 | IValue INTEGER, | |
154 | SValue VARCHAR, | |
155 | PRIMARY KEY (CID, Name)) | |
156 | __EOD | |
157 | ||
158 | my $daily_stat_ctablecmd = <<__EOD; | |
159 | CREATE TABLE DailyStat | |
160 | (Time INTEGER NOT NULL UNIQUE, | |
161 | CountIn INTEGER NOT NULL, | |
162 | CountOut INTEGER NOT NULL, | |
163 | BytesIn REAL NOT NULL, | |
164 | BytesOut REAL NOT NULL, | |
165 | VirusIn INTEGER NOT NULL, | |
166 | VirusOut INTEGER NOT NULL, | |
167 | SpamIn INTEGER NOT NULL, | |
168 | SpamOut INTEGER NOT NULL, | |
169 | BouncesIn INTEGER NOT NULL, | |
170 | BouncesOut INTEGER NOT NULL, | |
171 | GreylistCount INTEGER NOT NULL, | |
172 | SPFCount INTEGER NOT NULL, | |
173 | PTimeSum REAL NOT NULL, | |
174 | MTime INTEGER NOT NULL, | |
175 | RBLCount INTEGER DEFAULT 0 NOT NULL, | |
176 | PRIMARY KEY (Time)); | |
177 | ||
178 | CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime); | |
179 | ||
180 | __EOD | |
181 | ||
182 | my $domain_stat_ctablecmd = <<__EOD; | |
183 | CREATE TABLE DomainStat | |
184 | (Time INTEGER NOT NULL, | |
185 | Domain VARCHAR(255) NOT NULL, | |
186 | CountIn INTEGER NOT NULL, | |
187 | CountOut INTEGER NOT NULL, | |
188 | BytesIn REAL NOT NULL, | |
189 | BytesOut REAL NOT NULL, | |
190 | VirusIn INTEGER NOT NULL, | |
191 | VirusOut INTEGER NOT NULL, | |
192 | SpamIn INTEGER NOT NULL, | |
193 | SpamOut INTEGER NOT NULL, | |
194 | BouncesIn INTEGER NOT NULL, | |
195 | BouncesOut INTEGER NOT NULL, | |
196 | PTimeSum REAL NOT NULL, | |
197 | MTime INTEGER NOT NULL, | |
198 | PRIMARY KEY (Time, Domain)); | |
199 | ||
200 | CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime); | |
201 | __EOD | |
202 | ||
203 | my $statinfo_ctablecmd = <<__EOD; | |
204 | CREATE TABLE StatInfo | |
205 | (Name VARCHAR(255) NOT NULL UNIQUE, | |
206 | IValue INTEGER, | |
207 | SValue VARCHAR(255), | |
208 | PRIMARY KEY (Name)) | |
209 | __EOD | |
210 | ||
211 | my $virusinfo_stat_ctablecmd = <<__EOD; | |
212 | CREATE TABLE VirusInfo | |
213 | (Time INTEGER NOT NULL, | |
214 | Name VARCHAR NOT NULL, | |
215 | Count INTEGER NOT NULL, | |
216 | MTime INTEGER NOT NULL, | |
217 | PRIMARY KEY (Time, Name)); | |
218 | ||
219 | CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime); | |
220 | ||
221 | __EOD | |
222 | ||
223 | # mail storage stable | |
224 | # QTypes | |
225 | # V - Virus quarantine | |
226 | # S - Spam quarantine | |
227 | # D - Delayed Mails - not implemented | |
228 | # A - Held for Audit - not implemented | |
229 | # Status | |
230 | # N - new | |
231 | # D - deleted | |
232 | ||
233 | my $cmailstore_ctablecmd = <<__EOD; | |
234 | CREATE TABLE CMailStore | |
235 | (CID INTEGER DEFAULT 0 NOT NULL, | |
236 | RID INTEGER NOT NULL, | |
237 | ID SERIAL UNIQUE, | |
238 | Time INTEGER NOT NULL, | |
239 | QType "char" NOT NULL, | |
240 | Bytes INTEGER NOT NULL, | |
241 | Spamlevel INTEGER NOT NULL, | |
242 | Info VARCHAR NULL, | |
243 | Sender VARCHAR(255) NOT NULL, | |
244 | Header VARCHAR NOT NULL, | |
245 | File VARCHAR(255) NOT NULL, | |
246 | PRIMARY KEY (CID, RID)); | |
247 | CREATE INDEX CMailStore_Time_Index ON CMailStore (Time); | |
248 | ||
249 | CREATE TABLE CMSReceivers | |
250 | (CMailStore_CID INTEGER NOT NULL, | |
251 | CMailStore_RID INTEGER NOT NULL, | |
252 | PMail VARCHAR(255) NOT NULL, | |
253 | Receiver VARCHAR(255), | |
254 | TicketID INTEGER NOT NULL, | |
255 | Status "char" NOT NULL, | |
256 | MTime INTEGER NOT NULL); | |
257 | ||
258 | CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID); | |
259 | ||
260 | CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime); | |
261 | ||
262 | __EOD | |
263 | ||
264 | my $cstatistic_ctablecmd = <<__EOD; | |
265 | CREATE TABLE CStatistic | |
266 | (CID INTEGER DEFAULT 0 NOT NULL, | |
267 | RID INTEGER NOT NULL, | |
268 | ID SERIAL UNIQUE, | |
269 | Time INTEGER NOT NULL, | |
270 | Bytes INTEGER NOT NULL, | |
271 | Direction Boolean NOT NULL, | |
272 | Spamlevel INTEGER NOT NULL, | |
273 | VirusInfo VARCHAR(255) NULL, | |
274 | PTime INTEGER NOT NULL, | |
275 | Sender VARCHAR(255) NOT NULL, | |
276 | PRIMARY KEY (CID, RID)); | |
277 | ||
278 | CREATE INDEX CStatistic_Time_Index ON CStatistic (Time); | |
279 | ||
280 | CREATE TABLE CReceivers | |
281 | (CStatistic_CID INTEGER NOT NULL, | |
282 | CStatistic_RID INTEGER NOT NULL, | |
283 | Receiver VARCHAR(255) NOT NULL, | |
284 | Blocked Boolean NOT NULL); | |
285 | ||
286 | CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID); | |
287 | __EOD | |
288 | ||
289 | # user preferences (black an whitelists, ...) | |
290 | # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist) | |
291 | # Data: arbitrary data | |
292 | my $userprefs_ctablecmd = <<__EOD; | |
293 | CREATE TABLE UserPrefs | |
294 | (PMail VARCHAR, | |
295 | Name VARCHAR(255), | |
296 | Data VARCHAR, | |
297 | MTime INTEGER NOT NULL, | |
298 | PRIMARY KEY (PMail, Name)); | |
299 | ||
300 | CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime); | |
301 | ||
302 | __EOD | |
0a580593 DM |
303 | |
304 | sub cond_create_dbtable { | |
305 | my ($dbh, $name, $ctablecmd) = @_; | |
306 | ||
307 | eval { | |
308 | $dbh->begin_work; | |
309 | ||
310 | my $cmd = "SELECT tablename FROM pg_tables " . | |
311 | "WHERE tablename = lower ('$name')"; | |
312 | ||
313 | my $sth = $dbh->prepare ($cmd); | |
314 | ||
315 | $sth->execute(); | |
316 | ||
317 | if (!(my $ref = $sth->fetchrow_hashref())) { | |
318 | $dbh->do ($ctablecmd); | |
319 | } | |
320 | ||
321 | $sth->finish(); | |
322 | ||
323 | $dbh->commit; | |
324 | }; | |
325 | if (my $err = $@) { | |
326 | $dbh->rollback; | |
327 | croak $err; | |
328 | } | |
329 | } | |
f1bf78ff DM |
330 | |
331 | sub create_ruledb { | |
332 | my ($dbname) = @_; | |
333 | ||
334 | $dbname = "Proxmox_ruledb" if !$dbname; | |
335 | ||
336 | # use sql_ascii to avoid any character set conversions, and be compatible with | |
337 | # older postgres versions (update from 8.1 must be possible) | |
338 | my $cmd = [ 'createdb', '-U', 'postgres', '-E', 'sql_ascii', | |
339 | '-T', 'template0', '--lc-collate=C', '--lc-ctype=C', $dbname ]; | |
340 | ||
341 | PVE::Tools::run_command($cmd); | |
342 | ||
343 | my $dbh = open_ruledb($dbname); | |
344 | ||
345 | #$dbh->do ($dbloaddrivers_sql); | |
346 | #$dbh->do ($dbfunction_update_modtime); | |
347 | ||
348 | $dbh->do ($dbfunction_minint); | |
349 | ||
350 | $dbh->do ($dbfunction_maxint); | |
351 | ||
352 | $dbh->do ($dbfunction_merge_greylist); | |
353 | ||
354 | $dbh->do ( | |
355 | <<EOD | |
356 | CREATE TABLE Attribut | |
357 | (Object_ID INTEGER NOT NULL, | |
358 | Name VARCHAR(20) NOT NULL, | |
359 | Value BYTEA NULL, | |
360 | PRIMARY KEY (Object_ID, Name)); | |
361 | ||
362 | CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID); | |
363 | ||
364 | CREATE TABLE Object | |
365 | (ID SERIAL UNIQUE, | |
366 | ObjectType INTEGER NOT NULL, | |
367 | Objectgroup_ID INTEGER NOT NULL, | |
368 | Value BYTEA NULL, | |
369 | PRIMARY KEY (ID)); | |
370 | ||
371 | CREATE TABLE Objectgroup | |
372 | (ID SERIAL UNIQUE, | |
373 | Name VARCHAR(255) NOT NULL, | |
374 | Info VARCHAR(255) NULL, | |
375 | Class VARCHAR(10) NOT NULL, | |
376 | PRIMARY KEY (ID)); | |
377 | ||
378 | CREATE TABLE Rule | |
379 | (ID SERIAL UNIQUE, | |
380 | Name VARCHAR(255) NULL, | |
381 | Priority INTEGER NOT NULL, | |
382 | Active INTEGER NOT NULL DEFAULT 0, | |
383 | Direction INTEGER NOT NULL DEFAULT 2, | |
384 | Count INTEGER NOT NULL DEFAULT 0, | |
385 | PRIMARY KEY (ID)); | |
386 | ||
387 | CREATE TABLE RuleGroup | |
388 | (Objectgroup_ID INTEGER NOT NULL, | |
389 | Rule_ID INTEGER NOT NULL, | |
390 | Grouptype INTEGER NOT NULL, | |
391 | PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype)); | |
392 | ||
393 | $cgreylist_ctablecmd; | |
394 | ||
395 | $clusterinfo_ctablecmd; | |
396 | ||
397 | $daily_stat_ctablecmd; | |
398 | ||
399 | $domain_stat_ctablecmd; | |
400 | ||
401 | $statinfo_ctablecmd; | |
402 | ||
403 | $cmailstore_ctablecmd; | |
404 | ||
405 | $cstatistic_ctablecmd; | |
406 | ||
407 | $userprefs_ctablecmd; | |
408 | ||
409 | $virusinfo_stat_ctablecmd; | |
410 | EOD | |
411 | ); | |
412 | ||
413 | return $dbh; | |
414 | } | |
415 | ||
0a580593 DM |
416 | sub cond_create_action_quarantine { |
417 | my ($ruledb) = @_; | |
418 | ||
419 | my $dbh = $ruledb->{dbh}; | |
420 | ||
421 | eval { | |
422 | my $sth = $dbh->prepare( | |
423 | "SELECT * FROM Objectgroup, Object " . | |
424 | "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " . | |
425 | "AND Object.objectgroup_id = Objectgroup.id"); | |
426 | ||
427 | my $otype = PMG::RuleDB::Quarantine::otype(); | |
428 | if ($sth->execute($otype, 'action') <= 0) { | |
429 | my $obj = PMG::RuleDB::Quarantine->new (); | |
430 | my $txt = decode_entities(PMG::RuleDB::Quarantine->otype_text); | |
431 | my $quarantine = $ruledb->create_group_with_obj | |
432 | ($obj, $txt, PMG::RuleDB::Quarantine->oinfo); | |
433 | } | |
434 | }; | |
435 | } | |
436 | ||
437 | sub cond_create_std_actions { | |
438 | my ($ruledb) = @_; | |
439 | ||
440 | cond_create_action_quarantine($ruledb); | |
441 | ||
442 | #cond_create_action_report_spam($ruledb); | |
443 | } | |
444 | ||
445 | ||
446 | sub upgradedb { | |
447 | my ($ruledb) = @_; | |
448 | ||
449 | my $dbh = $ruledb->{dbh}; | |
450 | ||
451 | $dbh->do ($dbfunction_minint); | |
452 | ||
453 | $dbh->do ($dbfunction_maxint); | |
454 | ||
455 | $dbh->do ($dbfunction_merge_greylist); | |
456 | ||
457 | # make sure we do not use slow sequential scans when upgraing | |
458 | # database (before analyze can gather statistics) | |
459 | $dbh->do("set enable_seqscan = false"); | |
460 | ||
461 | cond_create_dbtable ($dbh, 'DailyStat', $daily_stat_ctablecmd); | |
462 | cond_create_dbtable ($dbh, 'DomainStat', $domain_stat_ctablecmd); | |
463 | cond_create_dbtable ($dbh, 'StatInfo', $statinfo_ctablecmd); | |
464 | cond_create_dbtable ($dbh, 'CMailStore', $cmailstore_ctablecmd); | |
465 | cond_create_dbtable ($dbh, 'UserPrefs', $userprefs_ctablecmd); | |
466 | cond_create_dbtable ($dbh, 'CGreylist', $cgreylist_ctablecmd); | |
467 | cond_create_dbtable ($dbh, 'CStatistic', $cstatistic_ctablecmd); | |
468 | cond_create_dbtable ($dbh, 'ClusterInfo', $clusterinfo_ctablecmd); | |
469 | cond_create_dbtable ($dbh, 'VirusInfo', $virusinfo_stat_ctablecmd); | |
470 | ||
471 | cond_create_std_actions ($ruledb); | |
472 | ||
473 | upgrade_mailstore_db ($dbh); | |
474 | ||
475 | upgrade_statistic_db ($dbh); | |
476 | ||
477 | upgrade_userprefs_db ($dbh); | |
478 | ||
479 | upgrade_greylist_db ($dbh); | |
480 | ||
481 | upgrade_dailystat_db ($dbh); | |
482 | ||
483 | upgrade_domainstat_db ($dbh); | |
484 | ||
485 | # update obsolete content type names | |
486 | eval { | |
487 | $dbh->do ("UPDATE Object " . | |
488 | "SET value = 'content-type:application/java-vm' ". | |
489 | "WHERE objecttype = 3003 " . | |
490 | "AND value = 'content-type:application/x-java-vm';"); | |
491 | }; | |
492 | ||
493 | eval { | |
494 | $dbh->do ("ANALYZE"); | |
495 | }; | |
496 | } | |
497 | ||
498 | sub init_ruledb { | |
499 | my ($ruledb, $reset, $testmode) = @_; | |
500 | ||
501 | my $dbh = $ruledb->{dbh}; | |
502 | ||
503 | if (!$reset) { | |
504 | # Greylist Objectgroup | |
505 | my $greylistgroup = PMG::RuleDB::Group->new | |
506 | ("GreyExclusion", "-", "greylist"); | |
507 | $ruledb->save_group ($greylistgroup); | |
508 | ||
509 | } else { | |
510 | # we do not touch greylist objects | |
511 | my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " . | |
512 | "objectgroup_id = objectgroup.id and class = 'greylist'"; | |
513 | ||
514 | $dbh->do ("DELETE FROM Rule; " . | |
515 | "DELETE FROM RuleGroup; " . | |
516 | "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " . | |
517 | "DELETE FROM Object WHERE ID NOT IN ($glids); " . | |
518 | "DELETE FROM Objectgroup WHERE class != 'greylist';"); | |
519 | } | |
520 | ||
521 | # WHO Objects | |
522 | ||
523 | # Blacklist | |
524 | my $obj = PMG::RuleDB::EMail->new ('nomail@fromthisdomain.com'); | |
525 | my $blacklist = $ruledb->create_group_with_obj( | |
526 | $obj, 'Blacklist', 'Global blacklist'); | |
527 | ||
528 | # Whitelist | |
529 | $obj = PMG::RuleDB::EMail->new('mail@fromthisdomain.com'); | |
530 | my $whitelist = $ruledb->create_group_with_obj( | |
531 | $obj, 'Whitelist', 'Global whitelist'); | |
532 | ||
533 | # WHEN Objects | |
534 | ||
535 | # Working hours | |
a29b9649 | 536 | $obj = PMG::RuleDB::TimeFrame->new(8*60, 16*60); |
0a580593 DM |
537 | my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' , |
538 | 'Usual office hours'); | |
539 | ||
540 | # WHAT Objects | |
541 | ||
0a580593 | 542 | # Images |
a29b9649 DM |
543 | $obj = PMG::RuleDB::ContentTypeFilter->new('image/.*'); |
544 | my $img_content = $ruledb->create_group_with_obj( | |
545 | $obj, 'Images', 'All kinds of graphic files'); | |
546 | ||
0a580593 | 547 | # Multimedia |
a29b9649 DM |
548 | $obj = PMG::RuleDB::ContentTypeFilter->new('audio/.*'); |
549 | my $mm_content = $ruledb->create_group_with_obj( | |
550 | $obj, 'Multimedia', 'Audio and Video'); | |
0a580593 | 551 | |
a29b9649 DM |
552 | $obj = PMG::RuleDB::ContentTypeFilter->new('video/.*'); |
553 | $ruledb->group_add_object($mm_content, $obj); | |
0a580593 DM |
554 | |
555 | # Office Files | |
a29b9649 DM |
556 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/vnd\.ms-excel'); |
557 | my $office_content = $ruledb->create_group_with_obj( | |
558 | $obj, 'Office Files', 'Common Office Files'); | |
559 | ||
560 | $obj = PMG::RuleDB::ContentTypeFilter->new( | |
561 | 'application/vnd\.ms-powerpoint'); | |
562 | ||
563 | $ruledb->group_add_object($office_content, $obj); | |
564 | ||
565 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/msword'); | |
0a580593 DM |
566 | $ruledb->group_add_object ($office_content, $obj); |
567 | ||
a29b9649 DM |
568 | $obj = PMG::RuleDB::ContentTypeFilter->new( |
569 | 'application/vnd\.openxmlformats-officedocument\..*'); | |
570 | $ruledb->group_add_object($office_content, $obj); | |
571 | ||
572 | $obj = PMG::RuleDB::ContentTypeFilter->new( | |
573 | 'application/vnd\.oasis\.opendocument\..*'); | |
574 | $ruledb->group_add_object($office_content, $obj); | |
0a580593 | 575 | |
a29b9649 DM |
576 | $obj = PMG::RuleDB::ContentTypeFilter->new( |
577 | 'application/vnd\.stardivision\..*'); | |
578 | $ruledb->group_add_object($office_content, $obj); | |
579 | ||
580 | $obj = PMG::RuleDB::ContentTypeFilter->new( | |
581 | 'application/vnd\.sun\.xml\..*'); | |
582 | $ruledb->group_add_object($office_content, $obj); | |
583 | ||
0a580593 | 584 | # Dangerous Content |
a29b9649 DM |
585 | $obj = PMG::RuleDB::ContentTypeFilter->new( |
586 | 'application/x-ms-dos-executable'); | |
587 | my $exe_content = $ruledb->create_group_with_obj( | |
588 | $obj, 'Dangerous Content', 'executable files and partial messages'); | |
589 | ||
590 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-java'); | |
591 | $ruledb->group_add_object($exe_content, $obj); | |
592 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/javascript'); | |
593 | $ruledb->group_add_object($exe_content, $obj); | |
594 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-executable'); | |
595 | $ruledb->group_add_object($exe_content, $obj); | |
596 | $obj = PMG::RuleDB::ContentTypeFilter->new('message/partial'); | |
597 | $ruledb->group_add_object($exe_content, $obj); | |
598 | $obj = PMG::RuleDB::MatchFilename->new('.*\.(vbs|pif|lnk|shs|shb)'); | |
599 | $ruledb->group_add_object($exe_content, $obj); | |
600 | $obj = PMG::RuleDB::MatchFilename->new('.*\.{.+}'); | |
601 | $ruledb->group_add_object($exe_content, $obj); | |
0a580593 DM |
602 | |
603 | # Virus | |
604 | $obj = Proxmox::RuleDB::Virus->new (); | |
605 | my $virus = $ruledb->create_group_with_obj ($obj, 'Virus', | |
606 | 'Matches virus infected mail'); | |
607 | # WHAT Objects | |
608 | ||
609 | # Spam | |
610 | $obj = Proxmox::RuleDB::Spam->new (3); | |
611 | my $spam3 = $ruledb->create_group_with_obj ($obj, 'Spam (Level 3)', | |
612 | 'Matches possible spam mail'); | |
613 | $obj = Proxmox::RuleDB::Spam->new (5); | |
614 | my $spam5 = $ruledb->create_group_with_obj ($obj, 'Spam (Level 5)', | |
615 | 'Matches possible spam mail'); | |
616 | $obj = Proxmox::RuleDB::Spam->new (10); | |
617 | my $spam10 = $ruledb->create_group_with_obj ($obj, 'Spam (Level 10)', | |
618 | 'Matches possible spam mail'); | |
619 | ||
620 | ||
621 | # $obj = Proxmox::RuleDB::MatchField->new ('content-type', 'application/pdf'); | |
622 | # $ct_filter = $ruledb->create_group_with_obj ($obj, 'Content Type Filter', | |
623 | # 'Content Type Filter'); | |
624 | ||
625 | ||
626 | # ACTIONS | |
627 | ||
628 | # Mark Spam | |
629 | $obj = Proxmox::RuleDB::ModField->new ('X-SPAM-LEVEL', '__SPAM_INFO__'); | |
630 | my $mod_spam_level = $ruledb->create_group_with_obj ($obj, 'Modify Spam Level', | |
631 | 'Mark mail as spam by adding a header tag.'); | |
632 | ||
633 | # Mark Spam | |
634 | $obj = Proxmox::RuleDB::ModField->new ('subject', 'SPAM: __SUBJECT__'); | |
635 | my $mod_spam_subject = $ruledb->create_group_with_obj ($obj, 'Modify Spam Subject', | |
636 | 'Mark mail as spam by modifying the subject.'); | |
637 | # Remove matching attachments | |
638 | $obj = Proxmox::RuleDB::Remove->new (0); | |
639 | my $remove = $ruledb->create_group_with_obj ($obj, 'Remove attachments', | |
640 | 'Remove matching attachments'); | |
641 | # Remove all attachments | |
642 | $obj = Proxmox::RuleDB::Remove->new (1); | |
643 | my $remove_all = $ruledb->create_group_with_obj ($obj, | |
644 | 'Remove all attachments', | |
645 | 'Remove all attachments'); | |
646 | ||
647 | # Accept | |
648 | $obj = Proxmox::RuleDB::Accept->new (); | |
649 | my $accept = $ruledb->create_group_with_obj ($obj, 'Accept', 'Accept mail for Delivery'); | |
650 | ||
651 | # Block | |
652 | $obj = Proxmox::RuleDB::Block->new (); | |
653 | my $block = $ruledb->create_group_with_obj ($obj, 'Block', 'Block mail'); | |
654 | ||
655 | # Quarantine | |
656 | $obj = Proxmox::RuleDB::Quarantine->new (); | |
657 | my $quarantine = $ruledb->create_group_with_obj ($obj, 'Quarantine', 'Move mail to quarantine'); | |
658 | ||
659 | # Spam Counter | |
660 | #$obj = Proxmox::RuleDB::Counter->new (0); | |
661 | #my $count_spam = $ruledb->create_group_with_obj ($obj, 'Count Spam', | |
662 | # 'Count spam mails'); | |
663 | # Virus Counter | |
664 | #$obj = Proxmox::RuleDB::Counter->new (0); | |
665 | #my $count_virus = $ruledb->create_group_with_obj ($obj, 'Count Viruses', | |
666 | # 'Count virus mails'); | |
667 | # BCC dietmar | |
668 | #$obj = Proxmox::RuleDB::BCC->new ('dietmar@maurer-it.com'); | |
669 | #$bcc = $ruledb->create_group_with_obj ($obj, 'BCC dietmar', 'send bcc'); | |
670 | ||
671 | # Store in quarantine | |
672 | #$obj = Proxmox::RuleDB::Store->new ('quarantine', 'O'); | |
673 | #$storeq = $ruledb->create_group_with_obj ($obj, 'Quarantine', ' Store in quarantine'); | |
674 | ||
675 | # Notify Admin | |
676 | $obj = Proxmox::RuleDB::Notify->new ('__ADMIN__'); | |
677 | my $notify_admin = $ruledb->create_group_with_obj ($obj, 'Notify Admin', | |
678 | 'Send notification'); | |
679 | ||
680 | # Notify Sender | |
681 | $obj = Proxmox::RuleDB::Notify->new ('__SENDER__'); | |
682 | my $notify_sender = $ruledb->create_group_with_obj ($obj, 'Notify Sender', | |
683 | 'Send notification'); | |
684 | ||
685 | # Add Disclaimer | |
686 | $obj = Proxmox::RuleDB::Disclaimer->new (); | |
687 | my $add_discl = $ruledb->create_group_with_obj ($obj, 'Disclaimer', | |
688 | 'Add Disclaimer'); | |
689 | ||
690 | # Attach original mail | |
691 | #$obj = Proxmox::RuleDB::Attach->new (); | |
692 | #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail', | |
693 | # 'Attach Original Mail'); | |
694 | ||
695 | ####################### RULES ################################## | |
696 | ||
697 | ## Block Dangerous Files | |
a29b9649 | 698 | my $rule = PMG::RuleDB::Rule->new ('Block Dangerous Files', 93, 1, 0); |
0a580593 DM |
699 | $ruledb->save_rule ($rule); |
700 | ||
701 | $ruledb->rule_add_what_group ($rule, $exe_content); | |
702 | $ruledb->rule_add_action ($rule, $remove); | |
703 | ||
704 | ## Block Viruses | |
a29b9649 | 705 | $rule = PMG::RuleDB::Rule->new ('Block Viruses', 96, 1, 0); |
0a580593 DM |
706 | $ruledb->save_rule ($rule); |
707 | ||
708 | $ruledb->rule_add_what_group ($rule, $virus); | |
709 | $ruledb->rule_add_action ($rule, $notify_admin); | |
710 | ||
711 | if ($testmode) { | |
712 | $ruledb->rule_add_action ($rule, $block); | |
713 | } else { | |
714 | $ruledb->rule_add_action ($rule, $quarantine); | |
715 | } | |
716 | ||
717 | ## Virus Alert | |
a29b9649 | 718 | $rule = PMG::RuleDB::Rule->new ('Virus Alert', 96, 1, 1); |
0a580593 DM |
719 | $ruledb->save_rule ($rule); |
720 | ||
721 | $ruledb->rule_add_what_group ($rule, $virus); | |
722 | $ruledb->rule_add_action ($rule, $notify_sender); | |
723 | $ruledb->rule_add_action ($rule, $notify_admin); | |
724 | $ruledb->rule_add_action ($rule, $block); | |
725 | ||
726 | ## Blacklist | |
a29b9649 | 727 | $rule = PMG::RuleDB::Rule->new ('Blacklist', 98, 1, 0); |
0a580593 DM |
728 | $ruledb->save_rule ($rule); |
729 | ||
730 | $ruledb->rule_add_from_group ($rule, $blacklist); | |
731 | $ruledb->rule_add_action ($rule, $block); | |
732 | ||
733 | ## Modify header | |
734 | if (!$testmode) { | |
a29b9649 | 735 | $rule = PMG::RuleDB::Rule->new ('Modify Header', 90, 1, 0); |
0a580593 DM |
736 | $ruledb->save_rule ($rule); |
737 | $ruledb->rule_add_action ($rule, $mod_spam_level); | |
738 | } | |
739 | ||
740 | ## Whitelist | |
a29b9649 | 741 | $rule = PMG::RuleDB::Rule->new ('Whitelist', 85, 1, 0); |
0a580593 DM |
742 | $ruledb->save_rule ($rule); |
743 | ||
744 | $ruledb->rule_add_from_group ($rule, $whitelist); | |
745 | $ruledb->rule_add_action ($rule, $accept); | |
746 | ||
747 | if ($testmode) { | |
a29b9649 | 748 | $rule = PMG::RuleDB::Rule->new ('Mark Spam', 80, 1, 0); |
0a580593 DM |
749 | $ruledb->save_rule ($rule); |
750 | ||
751 | $ruledb->rule_add_what_group ($rule, $spam10); | |
752 | $ruledb->rule_add_action ($rule, $mod_spam_level); | |
753 | $ruledb->rule_add_action ($rule, $mod_spam_subject); | |
754 | } else { | |
755 | # Quarantine/Mark Spam (Level 3) | |
a29b9649 | 756 | $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0); |
0a580593 DM |
757 | $ruledb->save_rule ($rule); |
758 | ||
759 | $ruledb->rule_add_what_group ($rule, $spam3); | |
760 | $ruledb->rule_add_action ($rule, $mod_spam_subject); | |
761 | $ruledb->rule_add_action ($rule, $quarantine); | |
762 | #$ruledb->rule_add_action ($rule, $count_spam); | |
763 | } | |
764 | ||
765 | # Quarantine/Mark Spam (Level 5) | |
a29b9649 | 766 | $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 79, 0, 0); |
0a580593 DM |
767 | $ruledb->save_rule ($rule); |
768 | ||
769 | $ruledb->rule_add_what_group ($rule, $spam5); | |
770 | $ruledb->rule_add_action ($rule, $mod_spam_subject); | |
771 | $ruledb->rule_add_action ($rule, $quarantine); | |
772 | ||
773 | ## Block Spam Level 10 | |
a29b9649 | 774 | $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 78, 0, 0); |
0a580593 DM |
775 | $ruledb->save_rule ($rule); |
776 | ||
777 | $ruledb->rule_add_what_group ($rule, $spam10); | |
778 | $ruledb->rule_add_action ($rule, $block); | |
779 | ||
780 | ## Block Outgoing Spam | |
a29b9649 | 781 | $rule = PMG::RuleDB::Rule->new ('Block outgoing Spam', 70, 0, 1); |
0a580593 DM |
782 | $ruledb->save_rule ($rule); |
783 | ||
784 | $ruledb->rule_add_what_group ($rule, $spam3); | |
785 | $ruledb->rule_add_action ($rule, $notify_admin); | |
786 | $ruledb->rule_add_action ($rule, $notify_sender); | |
787 | $ruledb->rule_add_action ($rule, $block); | |
788 | ||
789 | ## Add disclaimer | |
a29b9649 | 790 | $rule = PMG::RuleDB::Rule->new ('Add Disclaimer', 60, 0, 1); |
0a580593 DM |
791 | $ruledb->save_rule ($rule); |
792 | $ruledb->rule_add_action ($rule, $add_discl); | |
793 | ||
794 | # Block Multimedia Files | |
a29b9649 | 795 | $rule = PMG::RuleDB::Rule->new ('Block Multimedia Files', 87, 0, 2); |
0a580593 DM |
796 | $ruledb->save_rule ($rule); |
797 | ||
798 | $ruledb->rule_add_what_group ($rule, $mm_content); | |
799 | $ruledb->rule_add_action ($rule, $remove); | |
800 | ||
801 | #$ruledb->rule_add_from_group ($rule, $anybody); | |
802 | #$ruledb->rule_add_from_group ($rule, $trusted); | |
803 | #$ruledb->rule_add_to_group ($rule, $anybody); | |
804 | #$ruledb->rule_add_what_group ($rule, $ct_filter); | |
805 | #$ruledb->rule_add_action ($rule, $add_discl); | |
806 | #$ruledb->rule_add_action ($rule, $remove); | |
807 | #$ruledb->rule_add_action ($rule, $bcc); | |
808 | #$ruledb->rule_add_action ($rule, $storeq); | |
809 | #$ruledb->rule_add_action ($rule, $accept); | |
810 | ||
811 | cond_create_std_actions ($ruledb); | |
812 | } | |
813 | ||
a355f100 | 814 | 1; |