]>
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 | ||
987ee804 DM |
14 | our $default_db_name = "Proxmox_ruledb"; |
15 | ||
a355f100 DM |
16 | sub open_ruledb { |
17 | my ($database, $host, $port) = @_; | |
18 | ||
19 | $port = 5432 if !$port; | |
20 | ||
987ee804 | 21 | $database = $default_db_name if !$database; |
a355f100 DM |
22 | |
23 | if ($host) { | |
24 | ||
25 | my $dsn = "dbi:Pg:dbname=$database;host=$host;port=$port;"; | |
26 | ||
27 | my $timeout = 5; | |
28 | # only low level alarm interface works for DBI->connect | |
29 | my $mask = POSIX::SigSet->new(SIGALRM); | |
30 | my $action = POSIX::SigAction->new(sub { die "connect timeout\n" }, $mask); | |
31 | my $oldaction = POSIX::SigAction->new(); | |
32 | sigaction(SIGALRM, $action, $oldaction); | |
33 | ||
34 | my $rdb; | |
35 | ||
36 | eval { | |
37 | alarm($timeout); | |
38 | $rdb = DBI->connect($dsn, "postgres", undef, | |
39 | { PrintError => 0, RaiseError => 1 }); | |
40 | alarm(0); | |
41 | }; | |
42 | alarm(0); | |
43 | sigaction(SIGALRM, $oldaction); # restore original handler | |
f1bf78ff | 44 | |
a355f100 DM |
45 | die $@ if $@; |
46 | ||
47 | return $rdb; | |
48 | } else { | |
49 | my $dsn = "DBI:Pg:dbname=$database"; | |
50 | ||
f1bf78ff | 51 | my $dbh = DBI->connect($dsn, "postgres", undef, |
a355f100 DM |
52 | { PrintError => 0, RaiseError => 1 }); |
53 | ||
54 | return $dbh; | |
55 | } | |
56 | } | |
57 | ||
58 | sub delete_ruledb { | |
59 | my ($dbname) = @_; | |
60 | ||
61 | PVE::Tools::run_command(['dropdb', '-U', 'postgres', $dbname]); | |
62 | } | |
63 | ||
f1bf78ff DM |
64 | sub database_list { |
65 | ||
66 | my $database_list = {}; | |
67 | ||
68 | my $parser = sub { | |
69 | my $line = shift; | |
70 | ||
71 | my ($name, $owner) = map { PVE::Tools::trim($_) } split(/\|/, $line); | |
72 | return if !$name || !$owner; | |
73 | ||
74 | $database_list->{$name} = { owner => $owner }; | |
75 | }; | |
76 | ||
77 | my $cmd = ['psql', '-U', 'postgres', '--list', '--quiet', '--tuples-only']; | |
78 | ||
79 | PVE::Tools::run_command($cmd, outfunc => $parser); | |
80 | ||
81 | return $database_list; | |
82 | } | |
83 | ||
84 | my $dbfunction_maxint = <<__EOD; | |
85 | CREATE OR REPLACE FUNCTION maxint (INTEGER, INTEGER) RETURNS INTEGER AS | |
86 | 'BEGIN IF \$1 > \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql; | |
87 | __EOD | |
88 | ||
89 | my $dbfunction_minint = <<__EOD; | |
90 | CREATE OR REPLACE FUNCTION minint (INTEGER, INTEGER) RETURNS INTEGER AS | |
91 | 'BEGIN IF \$1 < \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql; | |
92 | __EOD | |
93 | ||
94 | # merge function to avoid update/insert race condition | |
95 | # see: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING | |
96 | my $dbfunction_merge_greylist = <<__EOD; | |
97 | CREATE OR REPLACE FUNCTION merge_greylist (in_ipnet VARCHAR, in_host INTEGER, in_sender VARCHAR, | |
98 | in_receiver VARCHAR, in_instance VARCHAR, | |
99 | in_rctime INTEGER, in_extime INTEGER, in_delay INTEGER, | |
100 | in_blocked INTEGER, in_passed INTEGER, in_mtime INTEGER, | |
101 | in_cid INTEGER) RETURNS INTEGER AS | |
102 | 'BEGIN | |
103 | LOOP | |
104 | UPDATE CGreylist SET Host = CASE WHEN MTime >= in_mtime THEN Host ELSE in_host END, | |
105 | CID = maxint (CID, in_cid), RCTime = minint (rctime, in_rctime), | |
106 | ExTime = maxint (extime, in_extime), | |
107 | Delay = maxint (delay, in_delay), | |
108 | Blocked = maxint (blocked, in_blocked), | |
109 | Passed = maxint (passed, in_passed) | |
110 | WHERE IPNet = in_ipnet AND Sender = in_sender AND Receiver = in_receiver; | |
111 | ||
112 | IF found THEN | |
113 | RETURN 0; | |
114 | END IF; | |
115 | ||
116 | BEGIN | |
117 | INSERT INTO CGREYLIST (IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, MTime, CID) | |
118 | VALUES (in_ipnet, in_host, in_sender, in_receiver, in_instance, in_rctime, in_extime, | |
119 | in_delay, in_blocked, in_passed, in_mtime, in_cid); | |
120 | RETURN 1; | |
121 | EXCEPTION WHEN unique_violation THEN | |
122 | -- do nothing - continue loop | |
123 | END; | |
124 | END LOOP; | |
125 | END;' LANGUAGE plpgsql; | |
126 | __EOD | |
127 | ||
128 | my $cgreylist_ctablecmd = <<__EOD; | |
129 | CREATE TABLE CGreylist | |
130 | (IPNet VARCHAR(16) NOT NULL, | |
131 | Host INTEGER NOT NULL, | |
132 | Sender VARCHAR(255) NOT NULL, | |
133 | Receiver VARCHAR(255) NOT NULL, | |
134 | Instance VARCHAR(255), | |
135 | RCTime INTEGER NOT NULL, | |
136 | ExTime INTEGER NOT NULL, | |
137 | Delay INTEGER NOT NULL DEFAULT 0, | |
138 | Blocked INTEGER NOT NULL, | |
139 | Passed INTEGER NOT NULL, | |
140 | CID INTEGER NOT NULL, | |
141 | MTime INTEGER NOT NULL, | |
142 | PRIMARY KEY (IPNet, Sender, Receiver)); | |
143 | ||
144 | CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender); | |
145 | ||
146 | CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime); | |
147 | ||
148 | CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime); | |
149 | __EOD | |
150 | ||
151 | my $clusterinfo_ctablecmd = <<__EOD; | |
152 | CREATE TABLE ClusterInfo | |
153 | (CID INTEGER NOT NULL, | |
154 | Name VARCHAR NOT NULL, | |
155 | IValue INTEGER, | |
156 | SValue VARCHAR, | |
157 | PRIMARY KEY (CID, Name)) | |
158 | __EOD | |
159 | ||
160 | my $daily_stat_ctablecmd = <<__EOD; | |
161 | CREATE TABLE DailyStat | |
162 | (Time INTEGER NOT NULL UNIQUE, | |
163 | CountIn INTEGER NOT NULL, | |
164 | CountOut INTEGER NOT NULL, | |
165 | BytesIn REAL NOT NULL, | |
166 | BytesOut REAL NOT NULL, | |
167 | VirusIn INTEGER NOT NULL, | |
168 | VirusOut INTEGER NOT NULL, | |
169 | SpamIn INTEGER NOT NULL, | |
170 | SpamOut INTEGER NOT NULL, | |
171 | BouncesIn INTEGER NOT NULL, | |
172 | BouncesOut INTEGER NOT NULL, | |
173 | GreylistCount INTEGER NOT NULL, | |
174 | SPFCount INTEGER NOT NULL, | |
175 | PTimeSum REAL NOT NULL, | |
176 | MTime INTEGER NOT NULL, | |
177 | RBLCount INTEGER DEFAULT 0 NOT NULL, | |
178 | PRIMARY KEY (Time)); | |
179 | ||
180 | CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime); | |
181 | ||
182 | __EOD | |
183 | ||
184 | my $domain_stat_ctablecmd = <<__EOD; | |
185 | CREATE TABLE DomainStat | |
186 | (Time INTEGER NOT NULL, | |
187 | Domain VARCHAR(255) NOT NULL, | |
188 | CountIn INTEGER NOT NULL, | |
189 | CountOut INTEGER NOT NULL, | |
190 | BytesIn REAL NOT NULL, | |
191 | BytesOut REAL NOT NULL, | |
192 | VirusIn INTEGER NOT NULL, | |
193 | VirusOut INTEGER NOT NULL, | |
194 | SpamIn INTEGER NOT NULL, | |
195 | SpamOut INTEGER NOT NULL, | |
196 | BouncesIn INTEGER NOT NULL, | |
197 | BouncesOut INTEGER NOT NULL, | |
198 | PTimeSum REAL NOT NULL, | |
199 | MTime INTEGER NOT NULL, | |
200 | PRIMARY KEY (Time, Domain)); | |
201 | ||
202 | CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime); | |
203 | __EOD | |
204 | ||
205 | my $statinfo_ctablecmd = <<__EOD; | |
206 | CREATE TABLE StatInfo | |
207 | (Name VARCHAR(255) NOT NULL UNIQUE, | |
208 | IValue INTEGER, | |
209 | SValue VARCHAR(255), | |
210 | PRIMARY KEY (Name)) | |
211 | __EOD | |
212 | ||
213 | my $virusinfo_stat_ctablecmd = <<__EOD; | |
214 | CREATE TABLE VirusInfo | |
215 | (Time INTEGER NOT NULL, | |
216 | Name VARCHAR NOT NULL, | |
217 | Count INTEGER NOT NULL, | |
218 | MTime INTEGER NOT NULL, | |
219 | PRIMARY KEY (Time, Name)); | |
220 | ||
221 | CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime); | |
222 | ||
223 | __EOD | |
224 | ||
225 | # mail storage stable | |
226 | # QTypes | |
227 | # V - Virus quarantine | |
228 | # S - Spam quarantine | |
229 | # D - Delayed Mails - not implemented | |
230 | # A - Held for Audit - not implemented | |
231 | # Status | |
232 | # N - new | |
233 | # D - deleted | |
234 | ||
235 | my $cmailstore_ctablecmd = <<__EOD; | |
236 | CREATE TABLE CMailStore | |
237 | (CID INTEGER DEFAULT 0 NOT NULL, | |
238 | RID INTEGER NOT NULL, | |
239 | ID SERIAL UNIQUE, | |
240 | Time INTEGER NOT NULL, | |
241 | QType "char" NOT NULL, | |
242 | Bytes INTEGER NOT NULL, | |
243 | Spamlevel INTEGER NOT NULL, | |
244 | Info VARCHAR NULL, | |
245 | Sender VARCHAR(255) NOT NULL, | |
246 | Header VARCHAR NOT NULL, | |
247 | File VARCHAR(255) NOT NULL, | |
248 | PRIMARY KEY (CID, RID)); | |
249 | CREATE INDEX CMailStore_Time_Index ON CMailStore (Time); | |
250 | ||
251 | CREATE TABLE CMSReceivers | |
252 | (CMailStore_CID INTEGER NOT NULL, | |
253 | CMailStore_RID INTEGER NOT NULL, | |
254 | PMail VARCHAR(255) NOT NULL, | |
255 | Receiver VARCHAR(255), | |
256 | TicketID INTEGER NOT NULL, | |
257 | Status "char" NOT NULL, | |
258 | MTime INTEGER NOT NULL); | |
259 | ||
260 | CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID); | |
261 | ||
262 | CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime); | |
263 | ||
264 | __EOD | |
265 | ||
266 | my $cstatistic_ctablecmd = <<__EOD; | |
267 | CREATE TABLE CStatistic | |
268 | (CID INTEGER DEFAULT 0 NOT NULL, | |
269 | RID INTEGER NOT NULL, | |
270 | ID SERIAL UNIQUE, | |
271 | Time INTEGER NOT NULL, | |
272 | Bytes INTEGER NOT NULL, | |
273 | Direction Boolean NOT NULL, | |
274 | Spamlevel INTEGER NOT NULL, | |
275 | VirusInfo VARCHAR(255) NULL, | |
276 | PTime INTEGER NOT NULL, | |
277 | Sender VARCHAR(255) NOT NULL, | |
278 | PRIMARY KEY (CID, RID)); | |
279 | ||
280 | CREATE INDEX CStatistic_Time_Index ON CStatistic (Time); | |
281 | ||
282 | CREATE TABLE CReceivers | |
283 | (CStatistic_CID INTEGER NOT NULL, | |
284 | CStatistic_RID INTEGER NOT NULL, | |
285 | Receiver VARCHAR(255) NOT NULL, | |
286 | Blocked Boolean NOT NULL); | |
287 | ||
288 | CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID); | |
289 | __EOD | |
290 | ||
291 | # user preferences (black an whitelists, ...) | |
292 | # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist) | |
293 | # Data: arbitrary data | |
294 | my $userprefs_ctablecmd = <<__EOD; | |
295 | CREATE TABLE UserPrefs | |
296 | (PMail VARCHAR, | |
297 | Name VARCHAR(255), | |
298 | Data VARCHAR, | |
299 | MTime INTEGER NOT NULL, | |
300 | PRIMARY KEY (PMail, Name)); | |
301 | ||
302 | CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime); | |
303 | ||
304 | __EOD | |
758c7b6b | 305 | |
0a580593 DM |
306 | sub cond_create_dbtable { |
307 | my ($dbh, $name, $ctablecmd) = @_; | |
308 | ||
309 | eval { | |
310 | $dbh->begin_work; | |
311 | ||
312 | my $cmd = "SELECT tablename FROM pg_tables " . | |
313 | "WHERE tablename = lower ('$name')"; | |
314 | ||
315 | my $sth = $dbh->prepare ($cmd); | |
758c7b6b | 316 | |
0a580593 DM |
317 | $sth->execute(); |
318 | ||
319 | if (!(my $ref = $sth->fetchrow_hashref())) { | |
320 | $dbh->do ($ctablecmd); | |
321 | } | |
758c7b6b | 322 | |
0a580593 DM |
323 | $sth->finish(); |
324 | ||
325 | $dbh->commit; | |
326 | }; | |
327 | if (my $err = $@) { | |
328 | $dbh->rollback; | |
9ef3f143 | 329 | die $err; |
0a580593 DM |
330 | } |
331 | } | |
f1bf78ff DM |
332 | |
333 | sub create_ruledb { | |
334 | my ($dbname) = @_; | |
335 | ||
987ee804 | 336 | $dbname = $default_db_name if !$dbname; |
f1bf78ff DM |
337 | |
338 | # use sql_ascii to avoid any character set conversions, and be compatible with | |
339 | # older postgres versions (update from 8.1 must be possible) | |
340 | my $cmd = [ 'createdb', '-U', 'postgres', '-E', 'sql_ascii', | |
341 | '-T', 'template0', '--lc-collate=C', '--lc-ctype=C', $dbname ]; | |
342 | ||
343 | PVE::Tools::run_command($cmd); | |
344 | ||
345 | my $dbh = open_ruledb($dbname); | |
346 | ||
347 | #$dbh->do ($dbloaddrivers_sql); | |
348 | #$dbh->do ($dbfunction_update_modtime); | |
349 | ||
350 | $dbh->do ($dbfunction_minint); | |
351 | ||
352 | $dbh->do ($dbfunction_maxint); | |
353 | ||
354 | $dbh->do ($dbfunction_merge_greylist); | |
355 | ||
356 | $dbh->do ( | |
357 | <<EOD | |
358 | CREATE TABLE Attribut | |
359 | (Object_ID INTEGER NOT NULL, | |
360 | Name VARCHAR(20) NOT NULL, | |
361 | Value BYTEA NULL, | |
362 | PRIMARY KEY (Object_ID, Name)); | |
363 | ||
364 | CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID); | |
365 | ||
366 | CREATE TABLE Object | |
367 | (ID SERIAL UNIQUE, | |
368 | ObjectType INTEGER NOT NULL, | |
369 | Objectgroup_ID INTEGER NOT NULL, | |
370 | Value BYTEA NULL, | |
371 | PRIMARY KEY (ID)); | |
372 | ||
373 | CREATE TABLE Objectgroup | |
374 | (ID SERIAL UNIQUE, | |
375 | Name VARCHAR(255) NOT NULL, | |
376 | Info VARCHAR(255) NULL, | |
377 | Class VARCHAR(10) NOT NULL, | |
378 | PRIMARY KEY (ID)); | |
379 | ||
380 | CREATE TABLE Rule | |
381 | (ID SERIAL UNIQUE, | |
382 | Name VARCHAR(255) NULL, | |
383 | Priority INTEGER NOT NULL, | |
384 | Active INTEGER NOT NULL DEFAULT 0, | |
385 | Direction INTEGER NOT NULL DEFAULT 2, | |
386 | Count INTEGER NOT NULL DEFAULT 0, | |
387 | PRIMARY KEY (ID)); | |
388 | ||
389 | CREATE TABLE RuleGroup | |
390 | (Objectgroup_ID INTEGER NOT NULL, | |
391 | Rule_ID INTEGER NOT NULL, | |
392 | Grouptype INTEGER NOT NULL, | |
393 | PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype)); | |
394 | ||
395 | $cgreylist_ctablecmd; | |
396 | ||
397 | $clusterinfo_ctablecmd; | |
398 | ||
399 | $daily_stat_ctablecmd; | |
400 | ||
401 | $domain_stat_ctablecmd; | |
402 | ||
403 | $statinfo_ctablecmd; | |
404 | ||
405 | $cmailstore_ctablecmd; | |
406 | ||
407 | $cstatistic_ctablecmd; | |
408 | ||
409 | $userprefs_ctablecmd; | |
410 | ||
411 | $virusinfo_stat_ctablecmd; | |
412 | EOD | |
413 | ); | |
414 | ||
415 | return $dbh; | |
416 | } | |
417 | ||
0a580593 DM |
418 | sub cond_create_action_quarantine { |
419 | my ($ruledb) = @_; | |
420 | ||
421 | my $dbh = $ruledb->{dbh}; | |
422 | ||
423 | eval { | |
424 | my $sth = $dbh->prepare( | |
425 | "SELECT * FROM Objectgroup, Object " . | |
426 | "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " . | |
427 | "AND Object.objectgroup_id = Objectgroup.id"); | |
428 | ||
429 | my $otype = PMG::RuleDB::Quarantine::otype(); | |
430 | if ($sth->execute($otype, 'action') <= 0) { | |
431 | my $obj = PMG::RuleDB::Quarantine->new (); | |
432 | my $txt = decode_entities(PMG::RuleDB::Quarantine->otype_text); | |
433 | my $quarantine = $ruledb->create_group_with_obj | |
9578dcd7 | 434 | ($obj, $txt, 'Move to quarantine.'); |
0a580593 DM |
435 | } |
436 | }; | |
437 | } | |
438 | ||
439 | sub cond_create_std_actions { | |
440 | my ($ruledb) = @_; | |
441 | ||
442 | cond_create_action_quarantine($ruledb); | |
443 | ||
444 | #cond_create_action_report_spam($ruledb); | |
445 | } | |
446 | ||
447 | ||
e7c865af DM |
448 | sub upgrade_mailstore_db { |
449 | my ($dbh) = @_; | |
450 | ||
451 | eval { | |
452 | $dbh->begin_work; | |
453 | ||
454 | my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('MailStore')"; | |
455 | ||
456 | my $sth = $dbh->prepare($cmd); | |
457 | $sth->execute(); | |
458 | my $ref = $sth->fetchrow_hashref(); | |
459 | $sth->finish(); | |
460 | ||
461 | if ($ref) { # table exists | |
462 | ||
463 | $cmd = "INSERT INTO CMailStore " . | |
464 | "(CID, RID, ID, Time, QType, Bytes, Spamlevel, Info, Header, Sender, File) " . | |
465 | "SELECT 0, ID, ID, Time, QType, Bytes, Spamlevel, Info, Header, Sender, File FROM MailStore"; | |
466 | ||
467 | $dbh->do($cmd); | |
468 | ||
469 | $cmd = "INSERT INTO CMSReceivers " . | |
470 | "(CMailStore_CID, CMailStore_RID, PMail, Receiver, TicketID, Status, MTime) " . | |
471 | "SELECT 0, MailStore_ID, PMail, Receiver, TicketID, Status, 0 FROM MSReceivers"; | |
472 | ||
473 | $dbh->do($cmd); | |
474 | ||
475 | $dbh->do("SELECT setval ('cmailstore_id_seq', nextval ('mailstore_id_seq'))"); | |
476 | ||
477 | $dbh->do("DROP TABLE MailStore"); | |
478 | $dbh->do("DROP TABLE MSReceivers"); | |
479 | } | |
480 | ||
481 | $dbh->commit; | |
482 | }; | |
483 | if (my $err = $@) { | |
484 | $dbh->rollback; | |
485 | die $err; | |
486 | } | |
487 | } | |
488 | ||
489 | sub upgrade_dailystat_db { | |
490 | my ($dbh) = @_; | |
491 | ||
492 | eval { # make sure we have MTime | |
493 | $dbh->do("ALTER TABLE DailyStat ADD COLUMN MTime INTEGER;" . | |
494 | "UPDATE DailyStat SET MTime = EXTRACT (EPOCH FROM now());"); | |
495 | }; | |
496 | ||
497 | eval { # make sure we have correct constraints for MTime | |
498 | $dbh->do ("ALTER TABLE DailyStat ALTER COLUMN MTime SET NOT NULL;"); | |
499 | }; | |
500 | ||
501 | eval { # make sure we have RBLCount | |
502 | $dbh->do ("ALTER TABLE DailyStat ADD COLUMN RBLCount INTEGER;" . | |
503 | "UPDATE DailyStat SET RBLCount = 0;"); | |
504 | }; | |
505 | ||
506 | eval { # make sure we have correct constraints for RBLCount | |
507 | $dbh->do ("ALTER TABLE DailyStat ALTER COLUMN RBLCount SET DEFAULT 0;" . | |
508 | "ALTER TABLE DailyStat ALTER COLUMN RBLCount SET NOT NULL;"); | |
509 | }; | |
510 | ||
511 | eval { | |
512 | $dbh->begin_work; | |
513 | ||
514 | my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('DailyStat_MTime_Index')"; | |
515 | ||
516 | my $sth = $dbh->prepare($cmd); | |
517 | $sth->execute(); | |
518 | my $ref = $sth->fetchrow_hashref(); | |
519 | $sth->finish(); | |
520 | ||
521 | if (!$ref) { # index does not exist | |
522 | $dbh->do ("CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime)"); | |
523 | } | |
524 | ||
525 | $dbh->commit; | |
526 | }; | |
527 | if (my $err = $@) { | |
528 | $dbh->rollback; | |
529 | die $err; | |
530 | } | |
531 | } | |
532 | ||
533 | sub upgrade_domainstat_db { | |
534 | my ($dbh) = @_; | |
535 | ||
536 | eval { # make sure we have MTime | |
537 | $dbh->do("ALTER TABLE DomainStat ADD COLUMN MTime INTEGER;" . | |
538 | "UPDATE DomainStat SET MTime = EXTRACT (EPOCH FROM now());" . | |
539 | "ALTER TABLE DomainStat ALTER COLUMN MTime SET NOT NULL;"); | |
540 | }; | |
541 | ||
542 | eval { | |
543 | $dbh->begin_work; | |
544 | ||
545 | my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('DomainStat_MTime_Index')"; | |
546 | ||
547 | my $sth = $dbh->prepare($cmd); | |
548 | $sth->execute(); | |
549 | my $ref = $sth->fetchrow_hashref(); | |
550 | $sth->finish(); | |
551 | ||
552 | if (!$ref) { # index does not exist | |
553 | $dbh->do ("CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime)"); | |
554 | } | |
555 | ||
556 | $dbh->commit; | |
557 | }; | |
558 | if (my $err = $@) { | |
559 | $dbh->rollback; | |
560 | die $@; | |
561 | } | |
562 | } | |
563 | ||
564 | sub upgrade_statistic_db { | |
565 | my ($dbh) = @_; | |
566 | ||
567 | eval { | |
568 | $dbh->begin_work; | |
569 | ||
570 | my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('Statistic')"; | |
571 | ||
572 | my $sth = $dbh->prepare($cmd); | |
573 | $sth->execute(); | |
574 | my $ref = $sth->fetchrow_hashref(); | |
575 | $sth->finish(); | |
576 | ||
577 | if ($ref) { # old table exists | |
578 | ||
579 | my $timezone = tz_local_offset();; | |
580 | ||
581 | $dbh->do("INSERT INTO VirusInfo (Time, Name, Count, MTime) " . | |
582 | "SELECT ((time + $timezone) / 86400) * 86400 as day, virusinfo, " . | |
583 | "count (virusinfo), max (Time) FROM Statistic " . | |
584 | "WHERE virusinfo IS NOT NULL GROUP BY day, virusinfo"); | |
585 | ||
586 | my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime (time()); | |
587 | my $end = timelocal(0, 0, 0, $mday, $mon, $year); | |
588 | my $start = $end - 3600*24*7; # / days | |
589 | ||
590 | $cmd = "INSERT INTO CStatistic " . | |
591 | "(CID, RID, ID, Time, Bytes, Direction, Spamlevel, VirusInfo, PTime, Sender) " . | |
592 | "SELECT 0, ID, ID, Time, Bytes, Direction, Spamlevel, VirusInfo, PTime, Sender FROM Statistic " . | |
593 | "WHERE time >= $start"; | |
594 | ||
595 | $dbh->do($cmd); | |
596 | ||
597 | $dbh->do("SELECT setval ('cstatistic_id_seq', nextval ('statistic_id_seq'))"); | |
598 | ||
599 | $dbh->do("INSERT INTO StatInfo (name, ivalue) VALUES ('virusinfo_index', " . | |
600 | "nextval ('statistic_id_seq'))"); | |
601 | ||
602 | $cmd = "INSERT INTO CReceivers (CStatistic_CID, CStatistic_RID, Receiver, Blocked) " . | |
603 | "SELECT 0, Mail_ID, Receiver, Blocked FROM Receivers " . | |
604 | "WHERE EXISTS (SELECT * FROM CStatistic WHERE CID = 0 AND RID = Mail_ID)"; | |
605 | ||
606 | $dbh->do($cmd); | |
607 | ||
608 | $dbh->do("DROP TABLE Statistic"); | |
609 | $dbh->do("DROP TABLE Receivers"); | |
610 | } | |
611 | ||
612 | $dbh->commit; | |
613 | }; | |
614 | if (my $err = $@) { | |
615 | $dbh->rollback; | |
616 | die $err; | |
617 | } | |
618 | } | |
619 | ||
620 | sub upgrade_greylist_db { | |
621 | my ($dbh) = @_; | |
622 | ||
623 | eval { | |
624 | $dbh->begin_work; | |
625 | ||
626 | my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('Greylist')"; | |
627 | ||
628 | my $sth = $dbh->prepare($cmd); | |
629 | $sth->execute(); | |
630 | my $ref = $sth->fetchrow_hashref(); | |
631 | $sth->finish(); | |
632 | ||
633 | if ($ref) { # table exists | |
634 | ||
635 | $cmd = "INSERT INTO CGreylist " . | |
636 | "(IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, MTime, CID) " . | |
637 | "SELECT IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, RCTime, 0 FROM Greylist"; | |
638 | ||
639 | $dbh->do($cmd); | |
640 | ||
641 | $dbh->do("DROP TABLE Greylist"); | |
642 | } | |
643 | ||
644 | $dbh->commit; | |
645 | }; | |
646 | if (my $err = $@) { | |
647 | $dbh->rollback; | |
648 | die $err; | |
649 | } | |
650 | } | |
651 | ||
652 | sub upgrade_userprefs_db { | |
653 | my ($dbh) = @_; | |
654 | ||
655 | eval { | |
656 | $dbh->do("ALTER TABLE UserPrefs ADD COLUMN MTime INTEGER;" . | |
657 | "UPDATE UserPrefs SET MTime = EXTRACT (EPOCH FROM now());" . | |
658 | "ALTER TABLE UserPrefs ALTER COLUMN MTime SET NOT NULL;"); | |
659 | }; | |
660 | ||
661 | ||
662 | eval { | |
663 | $dbh->begin_work; | |
664 | ||
665 | my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('UserPrefs_MTime_Index')"; | |
666 | ||
667 | my $sth = $dbh->prepare($cmd); | |
668 | $sth->execute(); | |
669 | my $ref = $sth->fetchrow_hashref(); | |
670 | $sth->finish(); | |
671 | ||
672 | if (!$ref) { # index does not exist | |
673 | $dbh->do("CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime)"); | |
674 | } | |
675 | ||
676 | $dbh->commit; | |
677 | }; | |
678 | if ($@) { | |
679 | $dbh->rollback; | |
680 | die $@; | |
681 | } | |
682 | } | |
683 | ||
0a580593 DM |
684 | sub upgradedb { |
685 | my ($ruledb) = @_; | |
686 | ||
687 | my $dbh = $ruledb->{dbh}; | |
688 | ||
e7c865af | 689 | $dbh->do($dbfunction_minint); |
0a580593 | 690 | |
e7c865af | 691 | $dbh->do($dbfunction_maxint); |
0a580593 | 692 | |
e7c865af | 693 | $dbh->do($dbfunction_merge_greylist); |
0a580593 | 694 | |
758c7b6b | 695 | # make sure we do not use slow sequential scans when upgraing |
0a580593 DM |
696 | # database (before analyze can gather statistics) |
697 | $dbh->do("set enable_seqscan = false"); | |
698 | ||
e7c865af DM |
699 | cond_create_dbtable($dbh, 'DailyStat', $daily_stat_ctablecmd); |
700 | cond_create_dbtable($dbh, 'DomainStat', $domain_stat_ctablecmd); | |
701 | cond_create_dbtable($dbh, 'StatInfo', $statinfo_ctablecmd); | |
702 | cond_create_dbtable($dbh, 'CMailStore', $cmailstore_ctablecmd); | |
703 | cond_create_dbtable($dbh, 'UserPrefs', $userprefs_ctablecmd); | |
704 | cond_create_dbtable($dbh, 'CGreylist', $cgreylist_ctablecmd); | |
705 | cond_create_dbtable($dbh, 'CStatistic', $cstatistic_ctablecmd); | |
706 | cond_create_dbtable($dbh, 'ClusterInfo', $clusterinfo_ctablecmd); | |
707 | cond_create_dbtable($dbh, 'VirusInfo', $virusinfo_stat_ctablecmd); | |
0a580593 | 708 | |
e7c865af | 709 | cond_create_std_actions($ruledb); |
0a580593 | 710 | |
e7c865af | 711 | upgrade_mailstore_db($dbh); |
0a580593 | 712 | |
e7c865af | 713 | upgrade_statistic_db($dbh); |
0a580593 | 714 | |
e7c865af | 715 | upgrade_userprefs_db($dbh); |
0a580593 | 716 | |
e7c865af | 717 | upgrade_greylist_db($dbh); |
0a580593 | 718 | |
e7c865af | 719 | upgrade_dailystat_db($dbh); |
0a580593 | 720 | |
e7c865af | 721 | upgrade_domainstat_db($dbh); |
0a580593 DM |
722 | |
723 | # update obsolete content type names | |
724 | eval { | |
e7c865af DM |
725 | $dbh->do("UPDATE Object " . |
726 | "SET value = 'content-type:application/java-vm' ". | |
727 | "WHERE objecttype = 3003 " . | |
728 | "AND value = 'content-type:application/x-java-vm';"); | |
0a580593 DM |
729 | }; |
730 | ||
731 | eval { | |
732 | $dbh->do ("ANALYZE"); | |
733 | }; | |
734 | } | |
735 | ||
736 | sub init_ruledb { | |
737 | my ($ruledb, $reset, $testmode) = @_; | |
738 | ||
739 | my $dbh = $ruledb->{dbh}; | |
740 | ||
741 | if (!$reset) { | |
742 | # Greylist Objectgroup | |
743 | my $greylistgroup = PMG::RuleDB::Group->new | |
744 | ("GreyExclusion", "-", "greylist"); | |
745 | $ruledb->save_group ($greylistgroup); | |
746 | ||
747 | } else { | |
748 | # we do not touch greylist objects | |
749 | my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " . | |
750 | "objectgroup_id = objectgroup.id and class = 'greylist'"; | |
751 | ||
752 | $dbh->do ("DELETE FROM Rule; " . | |
753 | "DELETE FROM RuleGroup; " . | |
754 | "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " . | |
755 | "DELETE FROM Object WHERE ID NOT IN ($glids); " . | |
756 | "DELETE FROM Objectgroup WHERE class != 'greylist';"); | |
757 | } | |
758 | ||
759 | # WHO Objects | |
760 | ||
761 | # Blacklist | |
762 | my $obj = PMG::RuleDB::EMail->new ('nomail@fromthisdomain.com'); | |
763 | my $blacklist = $ruledb->create_group_with_obj( | |
764 | $obj, 'Blacklist', 'Global blacklist'); | |
758c7b6b | 765 | |
0a580593 DM |
766 | # Whitelist |
767 | $obj = PMG::RuleDB::EMail->new('mail@fromthisdomain.com'); | |
768 | my $whitelist = $ruledb->create_group_with_obj( | |
769 | $obj, 'Whitelist', 'Global whitelist'); | |
770 | ||
771 | # WHEN Objects | |
772 | ||
773 | # Working hours | |
a29b9649 | 774 | $obj = PMG::RuleDB::TimeFrame->new(8*60, 16*60); |
0a580593 DM |
775 | my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' , |
776 | 'Usual office hours'); | |
777 | ||
778 | # WHAT Objects | |
779 | ||
0a580593 | 780 | # Images |
a29b9649 DM |
781 | $obj = PMG::RuleDB::ContentTypeFilter->new('image/.*'); |
782 | my $img_content = $ruledb->create_group_with_obj( | |
783 | $obj, 'Images', 'All kinds of graphic files'); | |
784 | ||
0a580593 | 785 | # Multimedia |
a29b9649 DM |
786 | $obj = PMG::RuleDB::ContentTypeFilter->new('audio/.*'); |
787 | my $mm_content = $ruledb->create_group_with_obj( | |
788 | $obj, 'Multimedia', 'Audio and Video'); | |
0a580593 | 789 | |
a29b9649 DM |
790 | $obj = PMG::RuleDB::ContentTypeFilter->new('video/.*'); |
791 | $ruledb->group_add_object($mm_content, $obj); | |
0a580593 DM |
792 | |
793 | # Office Files | |
a29b9649 DM |
794 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/vnd\.ms-excel'); |
795 | my $office_content = $ruledb->create_group_with_obj( | |
796 | $obj, 'Office Files', 'Common Office Files'); | |
758c7b6b | 797 | |
a29b9649 DM |
798 | $obj = PMG::RuleDB::ContentTypeFilter->new( |
799 | 'application/vnd\.ms-powerpoint'); | |
758c7b6b | 800 | |
a29b9649 | 801 | $ruledb->group_add_object($office_content, $obj); |
758c7b6b | 802 | |
a29b9649 | 803 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/msword'); |
0a580593 | 804 | $ruledb->group_add_object ($office_content, $obj); |
758c7b6b | 805 | |
a29b9649 DM |
806 | $obj = PMG::RuleDB::ContentTypeFilter->new( |
807 | 'application/vnd\.openxmlformats-officedocument\..*'); | |
808 | $ruledb->group_add_object($office_content, $obj); | |
758c7b6b | 809 | |
a29b9649 DM |
810 | $obj = PMG::RuleDB::ContentTypeFilter->new( |
811 | 'application/vnd\.oasis\.opendocument\..*'); | |
812 | $ruledb->group_add_object($office_content, $obj); | |
0a580593 | 813 | |
a29b9649 DM |
814 | $obj = PMG::RuleDB::ContentTypeFilter->new( |
815 | 'application/vnd\.stardivision\..*'); | |
816 | $ruledb->group_add_object($office_content, $obj); | |
758c7b6b | 817 | |
a29b9649 DM |
818 | $obj = PMG::RuleDB::ContentTypeFilter->new( |
819 | 'application/vnd\.sun\.xml\..*'); | |
820 | $ruledb->group_add_object($office_content, $obj); | |
758c7b6b | 821 | |
0a580593 | 822 | # Dangerous Content |
a29b9649 DM |
823 | $obj = PMG::RuleDB::ContentTypeFilter->new( |
824 | 'application/x-ms-dos-executable'); | |
825 | my $exe_content = $ruledb->create_group_with_obj( | |
826 | $obj, 'Dangerous Content', 'executable files and partial messages'); | |
758c7b6b | 827 | |
a29b9649 DM |
828 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-java'); |
829 | $ruledb->group_add_object($exe_content, $obj); | |
830 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/javascript'); | |
831 | $ruledb->group_add_object($exe_content, $obj); | |
832 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-executable'); | |
833 | $ruledb->group_add_object($exe_content, $obj); | |
79ef2452 DM |
834 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-ms-dos-executable'); |
835 | $ruledb->group_add_object($exe_content, $obj); | |
a29b9649 DM |
836 | $obj = PMG::RuleDB::ContentTypeFilter->new('message/partial'); |
837 | $ruledb->group_add_object($exe_content, $obj); | |
838 | $obj = PMG::RuleDB::MatchFilename->new('.*\.(vbs|pif|lnk|shs|shb)'); | |
839 | $ruledb->group_add_object($exe_content, $obj); | |
be6c5e4a | 840 | $obj = PMG::RuleDB::MatchFilename->new('.*\.\{.+\}'); |
a29b9649 | 841 | $ruledb->group_add_object($exe_content, $obj); |
0a580593 DM |
842 | |
843 | # Virus | |
758c7b6b DM |
844 | $obj = PMG::RuleDB::Virus->new(); |
845 | my $virus = $ruledb->create_group_with_obj( | |
846 | $obj, 'Virus', 'Matches virus infected mail'); | |
847 | ||
0a580593 DM |
848 | # WHAT Objects |
849 | ||
850 | # Spam | |
758c7b6b DM |
851 | $obj = PMG::RuleDB::Spam->new(3); |
852 | my $spam3 = $ruledb->create_group_with_obj( | |
853 | $obj, 'Spam (Level 3)', 'Matches possible spam mail'); | |
e7c865af | 854 | |
758c7b6b DM |
855 | $obj = PMG::RuleDB::Spam->new(5); |
856 | my $spam5 = $ruledb->create_group_with_obj( | |
857 | $obj, 'Spam (Level 5)', 'Matches possible spam mail'); | |
e7c865af | 858 | |
758c7b6b DM |
859 | $obj = PMG::RuleDB::Spam->new(10); |
860 | my $spam10 = $ruledb->create_group_with_obj( | |
861 | $obj, 'Spam (Level 10)', 'Matches possible spam mail'); | |
0a580593 DM |
862 | |
863 | # ACTIONS | |
864 | ||
758c7b6b DM |
865 | # Mark Spam |
866 | $obj = PMG::RuleDB::ModField->new('X-SPAM-LEVEL', '__SPAM_INFO__'); | |
867 | my $mod_spam_level = $ruledb->create_group_with_obj( | |
e7c865af | 868 | $obj, 'Modify Spam Level', |
758c7b6b | 869 | 'Mark mail as spam by adding a header tag.'); |
0a580593 DM |
870 | |
871 | # Mark Spam | |
758c7b6b DM |
872 | $obj = PMG::RuleDB::ModField->new('subject', 'SPAM: __SUBJECT__'); |
873 | my $mod_spam_subject = $ruledb->create_group_with_obj( | |
e7c865af | 874 | $obj, 'Modify Spam Subject', |
758c7b6b | 875 | 'Mark mail as spam by modifying the subject.'); |
e7c865af | 876 | |
0a580593 | 877 | # Remove matching attachments |
758c7b6b DM |
878 | $obj = PMG::RuleDB::Remove->new(0); |
879 | my $remove = $ruledb->create_group_with_obj( | |
880 | $obj, 'Remove attachments', 'Remove matching attachments'); | |
e7c865af | 881 | |
0a580593 | 882 | # Remove all attachments |
758c7b6b DM |
883 | $obj = PMG::RuleDB::Remove->new(1); |
884 | my $remove_all = $ruledb->create_group_with_obj( | |
885 | $obj, 'Remove all attachments', 'Remove all attachments'); | |
0a580593 DM |
886 | |
887 | # Accept | |
758c7b6b DM |
888 | $obj = PMG::RuleDB::Accept->new(); |
889 | my $accept = $ruledb->create_group_with_obj( | |
890 | $obj, 'Accept', 'Accept mail for Delivery'); | |
0a580593 DM |
891 | |
892 | # Block | |
758c7b6b DM |
893 | $obj = PMG::RuleDB::Block->new (); |
894 | my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail'); | |
0a580593 DM |
895 | |
896 | # Quarantine | |
758c7b6b DM |
897 | $obj = PMG::RuleDB::Quarantine->new(); |
898 | my $quarantine = $ruledb->create_group_with_obj( | |
899 | $obj, 'Quarantine', 'Move mail to quarantine'); | |
0a580593 DM |
900 | |
901 | # Notify Admin | |
758c7b6b DM |
902 | $obj = PMG::RuleDB::Notify->new('__ADMIN__'); |
903 | my $notify_admin = $ruledb->create_group_with_obj( | |
904 | $obj, 'Notify Admin', 'Send notification'); | |
0a580593 DM |
905 | |
906 | # Notify Sender | |
758c7b6b DM |
907 | $obj = PMG::RuleDB::Notify->new('__SENDER__'); |
908 | my $notify_sender = $ruledb->create_group_with_obj( | |
909 | $obj, 'Notify Sender', 'Send notification'); | |
0a580593 DM |
910 | |
911 | # Add Disclaimer | |
758c7b6b DM |
912 | $obj = PMG::RuleDB::Disclaimer->new (); |
913 | my $add_discl = $ruledb->create_group_with_obj( | |
914 | $obj, 'Disclaimer', 'Add Disclaimer'); | |
0a580593 DM |
915 | |
916 | # Attach original mail | |
917 | #$obj = Proxmox::RuleDB::Attach->new (); | |
758c7b6b | 918 | #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail', |
0a580593 DM |
919 | # 'Attach Original Mail'); |
920 | ||
921 | ####################### RULES ################################## | |
922 | ||
923 | ## Block Dangerous Files | |
a29b9649 | 924 | my $rule = PMG::RuleDB::Rule->new ('Block Dangerous Files', 93, 1, 0); |
0a580593 DM |
925 | $ruledb->save_rule ($rule); |
926 | ||
927 | $ruledb->rule_add_what_group ($rule, $exe_content); | |
928 | $ruledb->rule_add_action ($rule, $remove); | |
929 | ||
930 | ## Block Viruses | |
a29b9649 | 931 | $rule = PMG::RuleDB::Rule->new ('Block Viruses', 96, 1, 0); |
0a580593 DM |
932 | $ruledb->save_rule ($rule); |
933 | ||
934 | $ruledb->rule_add_what_group ($rule, $virus); | |
935 | $ruledb->rule_add_action ($rule, $notify_admin); | |
758c7b6b | 936 | |
0a580593 DM |
937 | if ($testmode) { |
938 | $ruledb->rule_add_action ($rule, $block); | |
939 | } else { | |
940 | $ruledb->rule_add_action ($rule, $quarantine); | |
941 | } | |
942 | ||
943 | ## Virus Alert | |
a29b9649 | 944 | $rule = PMG::RuleDB::Rule->new ('Virus Alert', 96, 1, 1); |
0a580593 DM |
945 | $ruledb->save_rule ($rule); |
946 | ||
947 | $ruledb->rule_add_what_group ($rule, $virus); | |
948 | $ruledb->rule_add_action ($rule, $notify_sender); | |
949 | $ruledb->rule_add_action ($rule, $notify_admin); | |
950 | $ruledb->rule_add_action ($rule, $block); | |
758c7b6b | 951 | |
0a580593 | 952 | ## Blacklist |
a29b9649 | 953 | $rule = PMG::RuleDB::Rule->new ('Blacklist', 98, 1, 0); |
0a580593 DM |
954 | $ruledb->save_rule ($rule); |
955 | ||
956 | $ruledb->rule_add_from_group ($rule, $blacklist); | |
957 | $ruledb->rule_add_action ($rule, $block); | |
958 | ||
959 | ## Modify header | |
960 | if (!$testmode) { | |
a29b9649 | 961 | $rule = PMG::RuleDB::Rule->new ('Modify Header', 90, 1, 0); |
0a580593 DM |
962 | $ruledb->save_rule ($rule); |
963 | $ruledb->rule_add_action ($rule, $mod_spam_level); | |
964 | } | |
965 | ||
966 | ## Whitelist | |
a29b9649 | 967 | $rule = PMG::RuleDB::Rule->new ('Whitelist', 85, 1, 0); |
0a580593 DM |
968 | $ruledb->save_rule ($rule); |
969 | ||
970 | $ruledb->rule_add_from_group ($rule, $whitelist); | |
971 | $ruledb->rule_add_action ($rule, $accept); | |
972 | ||
973 | if ($testmode) { | |
a29b9649 | 974 | $rule = PMG::RuleDB::Rule->new ('Mark Spam', 80, 1, 0); |
0a580593 DM |
975 | $ruledb->save_rule ($rule); |
976 | ||
977 | $ruledb->rule_add_what_group ($rule, $spam10); | |
978 | $ruledb->rule_add_action ($rule, $mod_spam_level); | |
979 | $ruledb->rule_add_action ($rule, $mod_spam_subject); | |
980 | } else { | |
981 | # Quarantine/Mark Spam (Level 3) | |
a29b9649 | 982 | $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0); |
0a580593 DM |
983 | $ruledb->save_rule ($rule); |
984 | ||
985 | $ruledb->rule_add_what_group ($rule, $spam3); | |
986 | $ruledb->rule_add_action ($rule, $mod_spam_subject); | |
987 | $ruledb->rule_add_action ($rule, $quarantine); | |
988 | #$ruledb->rule_add_action ($rule, $count_spam); | |
989 | } | |
990 | ||
991 | # Quarantine/Mark Spam (Level 5) | |
a29b9649 | 992 | $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 79, 0, 0); |
0a580593 DM |
993 | $ruledb->save_rule ($rule); |
994 | ||
995 | $ruledb->rule_add_what_group ($rule, $spam5); | |
996 | $ruledb->rule_add_action ($rule, $mod_spam_subject); | |
997 | $ruledb->rule_add_action ($rule, $quarantine); | |
998 | ||
999 | ## Block Spam Level 10 | |
a29b9649 | 1000 | $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 78, 0, 0); |
0a580593 DM |
1001 | $ruledb->save_rule ($rule); |
1002 | ||
1003 | $ruledb->rule_add_what_group ($rule, $spam10); | |
1004 | $ruledb->rule_add_action ($rule, $block); | |
1005 | ||
1006 | ## Block Outgoing Spam | |
a29b9649 | 1007 | $rule = PMG::RuleDB::Rule->new ('Block outgoing Spam', 70, 0, 1); |
0a580593 DM |
1008 | $ruledb->save_rule ($rule); |
1009 | ||
1010 | $ruledb->rule_add_what_group ($rule, $spam3); | |
1011 | $ruledb->rule_add_action ($rule, $notify_admin); | |
1012 | $ruledb->rule_add_action ($rule, $notify_sender); | |
1013 | $ruledb->rule_add_action ($rule, $block); | |
1014 | ||
1015 | ## Add disclaimer | |
a29b9649 | 1016 | $rule = PMG::RuleDB::Rule->new ('Add Disclaimer', 60, 0, 1); |
0a580593 DM |
1017 | $ruledb->save_rule ($rule); |
1018 | $ruledb->rule_add_action ($rule, $add_discl); | |
1019 | ||
1020 | # Block Multimedia Files | |
a29b9649 | 1021 | $rule = PMG::RuleDB::Rule->new ('Block Multimedia Files', 87, 0, 2); |
0a580593 DM |
1022 | $ruledb->save_rule ($rule); |
1023 | ||
1024 | $ruledb->rule_add_what_group ($rule, $mm_content); | |
1025 | $ruledb->rule_add_action ($rule, $remove); | |
1026 | ||
1027 | #$ruledb->rule_add_from_group ($rule, $anybody); | |
1028 | #$ruledb->rule_add_from_group ($rule, $trusted); | |
1029 | #$ruledb->rule_add_to_group ($rule, $anybody); | |
1030 | #$ruledb->rule_add_what_group ($rule, $ct_filter); | |
1031 | #$ruledb->rule_add_action ($rule, $add_discl); | |
1032 | #$ruledb->rule_add_action ($rule, $remove); | |
1033 | #$ruledb->rule_add_action ($rule, $bcc); | |
1034 | #$ruledb->rule_add_action ($rule, $storeq); | |
1035 | #$ruledb->rule_add_action ($rule, $accept); | |
1036 | ||
1037 | cond_create_std_actions ($ruledb); | |
1038 | } | |
1039 | ||
8f4e102b DM |
1040 | sub get_remote_time { |
1041 | my ($rdb) = @_; | |
1042 | ||
1043 | my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;"); | |
1044 | $sth->execute(); | |
1045 | my $ctinfo = $sth->fetchrow_hashref(); | |
1046 | $sth->finish (); | |
1047 | ||
1048 | return $ctinfo ? $ctinfo->{ctime} : 0; | |
1049 | } | |
1050 | ||
cfdf6608 DM |
1051 | sub init_masterdb { |
1052 | my ($lcid, $database) = @_; | |
1053 | ||
1054 | die "got unexpected cid for new master" if !$lcid; | |
1055 | ||
1056 | my $dbh; | |
1057 | ||
1058 | eval { | |
1059 | $dbh = open_ruledb($database); | |
1060 | ||
1061 | $dbh->begin_work; | |
1062 | ||
1063 | print STDERR "update quarantine database\n"; | |
1064 | $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" . | |
1065 | "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;"); | |
1066 | ||
1067 | print STDERR "update statistic database\n"; | |
1068 | $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" . | |
1069 | "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;"); | |
1070 | ||
1071 | print STDERR "update greylist database\n"; | |
1072 | $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;"); | |
1073 | ||
1074 | $dbh->commit; | |
1075 | }; | |
1076 | my $err = $@; | |
1077 | ||
1078 | if ($dbh) { | |
1079 | $dbh->rollback if $err; | |
1080 | $dbh->disconnect(); | |
1081 | } | |
1082 | ||
1083 | die $err if $err; | |
1084 | } | |
1085 | ||
987ee804 DM |
1086 | sub update_master_clusterinfo { |
1087 | my ($clientcid) = @_; | |
1088 | ||
1089 | my $dbh = open_ruledb(); | |
1090 | ||
1091 | $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid"); | |
1092 | ||
1093 | my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'VirusInfo'); | |
1094 | ||
1095 | foreach my $table (@mt) { | |
1096 | $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " . | |
1097 | "EXTRACT(EPOCH FROM now())"); | |
1098 | } | |
1099 | } | |
1100 | ||
1101 | sub update_client_clusterinfo { | |
1102 | my ($mastercid) = @_; | |
1103 | ||
1104 | my $dbh = open_ruledb(); | |
1105 | ||
1106 | $dbh->do ("DELETE FROM StatInfo"); # not needed at node | |
1107 | ||
1108 | $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid"); | |
1109 | ||
1110 | $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " . | |
1111 | "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid"); | |
1112 | ||
1113 | $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " . | |
1114 | "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid"); | |
1115 | ||
1116 | my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'VirusInfo'); | |
1117 | ||
1118 | foreach my $table (@mt) { | |
1119 | $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " . | |
1120 | "COALESCE (max (mtime), 0) FROM $table"); | |
1121 | } | |
1122 | } | |
1123 | ||
1124 | sub init_nodedb { | |
1125 | my ($cinfo) = @_; | |
1126 | ||
1127 | my $ni = $cinfo->{master}; | |
1128 | ||
1129 | die "no master defined - unable to sync data from master\n" if !$ni; | |
1130 | ||
1131 | my $master_ip = $ni->{ip}; | |
1132 | my $master_cid = $ni->{cid}; | |
22c16ff6 | 1133 | my $master_name = $ni->{name}; |
987ee804 DM |
1134 | |
1135 | my $fn = "/tmp/masterdb$$.tar"; | |
1136 | unlink $fn; | |
1137 | ||
1138 | my $dbname = $default_db_name; | |
1139 | ||
1140 | eval { | |
1141 | print STDERR "copying master database from '${master_ip}'\n"; | |
1142 | ||
22c16ff6 DM |
1143 | my $cmd = [['/usr/bin/ssh', '-o', 'BatchMode=yes', |
1144 | '-o', "HostKeyAlias=${master_name}", | |
1145 | $master_ip, 'pg_dump', '-U', 'postgres', | |
987ee804 | 1146 | $dbname, '-F', 'c', \ ">$fn" ]]; |
22c16ff6 | 1147 | |
987ee804 DM |
1148 | PVE::Tools::run_command($cmd); |
1149 | ||
1150 | my $size = -s $fn; | |
1151 | ||
1152 | print STDERR "copying master database finished (got $size bytes)\n"; | |
1153 | ||
1154 | print STDERR "delete local database\n"; | |
1155 | ||
1156 | $cmd = [ 'dropdb', '-U', 'postgres', $dbname , '--if-exists']; | |
1157 | PVE::Tools::run_command($cmd); | |
1158 | ||
1159 | print STDERR "create new local database\n"; | |
1160 | ||
1161 | $cmd = ['createdb', '-U', 'postgres', $dbname]; | |
1162 | PVE::Tools::run_command($cmd); | |
1163 | ||
1164 | print STDERR "insert received data into local database\n"; | |
1165 | ||
1166 | my $mess; | |
1167 | my $parser = sub { | |
1168 | my $line = shift; | |
1169 | ||
1170 | if ($line =~ m/restoring data for table \"(.+)\"/) { | |
1171 | print STDERR "restoring table $1\n"; | |
1172 | } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) { | |
1173 | $mess = "creating indexes"; | |
1174 | print STDERR "$mess\n"; | |
1175 | } | |
1176 | }; | |
1177 | ||
1178 | $cmd = ['pg_restore', '-U', 'postgres', '-d', $dbname, '-v', $fn]; | |
1179 | PVE::Tools::run_command($cmd, outfunc => $parser, errfunc => $parser, | |
1180 | errmsg => "pg_restore failed"); | |
1181 | ||
1182 | print STDERR "run analyze to speed up database queries\n"; | |
1183 | ||
1184 | $cmd = ['psql', '-U', 'postgres', $dbname]; | |
1185 | PVE::Tools::run_command($cmd); | |
1186 | ||
1187 | update_client_clusterinfo($master_cid); | |
1188 | }; | |
1189 | ||
1190 | my $err = $@; | |
1191 | ||
1192 | unlink $fn; | |
1193 | ||
1194 | die $err if $err; | |
1195 | } | |
1196 | ||
cfdf6608 | 1197 | |
a355f100 | 1198 | 1; |