]>
Commit | Line | Data |
---|---|---|
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 | use Time::Local; | |
10 | ||
11 | use PVE::SafeSyslog; | |
12 | use PVE::Tools; | |
13 | ||
14 | use PMG::Utils; | |
15 | use PMG::RuleDB; | |
16 | use PMG::MailQueue; | |
17 | use PMG::Config; | |
18 | ||
19 | our $default_db_name = "Proxmox_ruledb"; | |
20 | ||
21 | our $cgreylist_merge_sql = | |
22 | 'INSERT INTO CGREYLIST (IPNet,Host,Sender,Receiver,Instance,RCTime,' . | |
23 | 'ExTime,Delay,Blocked,Passed,MTime,CID) ' . | |
24 | 'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ' . | |
25 | 'ON CONFLICT (IPNet,Sender,Receiver) DO UPDATE SET ' . | |
26 | 'Host = CASE WHEN CGREYLIST.MTime >= excluded.MTime THEN CGREYLIST.Host ELSE excluded.Host END,' . | |
27 | 'CID = GREATEST(CGREYLIST.CID, excluded.CID), RCTime = LEAST(CGREYLIST.RCTime, excluded.RCTime),' . | |
28 | 'ExTime = GREATEST(CGREYLIST.ExTime, excluded.ExTime),' . | |
29 | 'Delay = GREATEST(CGREYLIST.Delay, excluded.Delay),' . | |
30 | 'Blocked = GREATEST(CGREYLIST.Blocked, excluded.Blocked),' . | |
31 | 'Passed = GREATEST(CGREYLIST.Passed, excluded.Passed)'; | |
32 | ||
33 | sub open_ruledb { | |
34 | my ($database, $host, $port) = @_; | |
35 | ||
36 | $port //= 5432; | |
37 | ||
38 | $database //= $default_db_name; | |
39 | ||
40 | if ($host) { | |
41 | ||
42 | # Note: pmgtunnel uses UDP sockets inside directory '/var/run/pmgtunnel', | |
43 | # and the cluster 'cid' as port number. You can connect to the | |
44 | # socket with: host => /var/run/pmgtunnel, port => $cid | |
45 | ||
46 | my $dsn = "dbi:Pg:dbname=$database;host=$host;port=$port;"; | |
47 | ||
48 | my $timeout = 5; | |
49 | # only low level alarm interface works for DBI->connect | |
50 | my $mask = POSIX::SigSet->new(SIGALRM); | |
51 | my $action = POSIX::SigAction->new(sub { die "connect timeout\n" }, $mask); | |
52 | my $oldaction = POSIX::SigAction->new(); | |
53 | sigaction(SIGALRM, $action, $oldaction); | |
54 | ||
55 | my $rdb; | |
56 | ||
57 | eval { | |
58 | alarm($timeout); | |
59 | $rdb = DBI->connect($dsn, 'root', undef, | |
60 | { PrintError => 0, RaiseError => 1 }); | |
61 | alarm(0); | |
62 | }; | |
63 | alarm(0); | |
64 | sigaction(SIGALRM, $oldaction); # restore original handler | |
65 | ||
66 | die $@ if $@; | |
67 | ||
68 | return $rdb; | |
69 | } else { | |
70 | my $dsn = "DBI:Pg:dbname=$database;host=/var/run/postgresql;port=$port"; | |
71 | ||
72 | my $dbh = DBI->connect($dsn, $> == 0 ? 'root' : 'www-data', undef, | |
73 | { PrintError => 0, RaiseError => 1 }); | |
74 | ||
75 | return $dbh; | |
76 | } | |
77 | } | |
78 | ||
79 | sub postgres_admin_cmd { | |
80 | my ($cmd, $options, @params) = @_; | |
81 | ||
82 | $cmd = ref($cmd) ? $cmd : [ $cmd ]; | |
83 | my $uid = getpwnam('postgres') || die "getpwnam postgres failed\n"; | |
84 | ||
85 | local $> = $uid; | |
86 | $! && die "setuid postgres ($uid) failed - $!\n"; | |
87 | ||
88 | PVE::Tools::run_command([@$cmd, '-U', 'postgres', @params], %$options); | |
89 | } | |
90 | ||
91 | sub delete_ruledb { | |
92 | my ($dbname) = @_; | |
93 | ||
94 | postgres_admin_cmd('dropdb', undef, $dbname); | |
95 | } | |
96 | ||
97 | sub database_list { | |
98 | ||
99 | my $database_list = {}; | |
100 | ||
101 | my $parser = sub { | |
102 | my $line = shift; | |
103 | ||
104 | my ($name, $owner) = map { PVE::Tools::trim($_) } split(/\|/, $line); | |
105 | return if !$name || !$owner; | |
106 | ||
107 | $database_list->{$name} = { owner => $owner }; | |
108 | }; | |
109 | ||
110 | postgres_admin_cmd('psql', { outfunc => $parser }, '--list', '--quiet', '--tuples-only'); | |
111 | ||
112 | return $database_list; | |
113 | } | |
114 | ||
115 | my $cgreylist_ctablecmd = <<__EOD; | |
116 | CREATE TABLE CGreylist | |
117 | (IPNet VARCHAR(16) NOT NULL, | |
118 | Host INTEGER NOT NULL, | |
119 | Sender VARCHAR(255) NOT NULL, | |
120 | Receiver VARCHAR(255) NOT NULL, | |
121 | Instance VARCHAR(255), | |
122 | RCTime INTEGER NOT NULL, | |
123 | ExTime INTEGER NOT NULL, | |
124 | Delay INTEGER NOT NULL DEFAULT 0, | |
125 | Blocked INTEGER NOT NULL, | |
126 | Passed INTEGER NOT NULL, | |
127 | CID INTEGER NOT NULL, | |
128 | MTime INTEGER NOT NULL, | |
129 | PRIMARY KEY (IPNet, Sender, Receiver)); | |
130 | ||
131 | CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender); | |
132 | ||
133 | CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime); | |
134 | ||
135 | CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime); | |
136 | __EOD | |
137 | ||
138 | my $clusterinfo_ctablecmd = <<__EOD; | |
139 | CREATE TABLE ClusterInfo | |
140 | (CID INTEGER NOT NULL, | |
141 | Name VARCHAR NOT NULL, | |
142 | IValue INTEGER, | |
143 | SValue VARCHAR, | |
144 | PRIMARY KEY (CID, Name)) | |
145 | __EOD | |
146 | ||
147 | my $local_stat_ctablecmd = <<__EOD; | |
148 | CREATE TABLE LocalStat | |
149 | (Time INTEGER NOT NULL, | |
150 | RBLCount INTEGER DEFAULT 0 NOT NULL, | |
151 | PregreetCount INTEGER DEFAULT 0 NOT NULL, | |
152 | CID INTEGER NOT NULL, | |
153 | MTime INTEGER NOT NULL, | |
154 | PRIMARY KEY (Time, CID)); | |
155 | ||
156 | CREATE INDEX LocalStat_MTime_Index ON LocalStat (MTime); | |
157 | __EOD | |
158 | ||
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 table | |
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 | |
305 | ||
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); | |
316 | ||
317 | $sth->execute(); | |
318 | ||
319 | if (!(my $ref = $sth->fetchrow_hashref())) { | |
320 | $dbh->do ($ctablecmd); | |
321 | } | |
322 | ||
323 | $sth->finish(); | |
324 | ||
325 | $dbh->commit; | |
326 | }; | |
327 | if (my $err = $@) { | |
328 | $dbh->rollback; | |
329 | die $err; | |
330 | } | |
331 | } | |
332 | ||
333 | sub database_column_exists { | |
334 | my ($dbh, $table, $column) = @_; | |
335 | ||
336 | my $sth = $dbh->prepare( | |
337 | "SELECT column_name FROM information_schema.columns " . | |
338 | "WHERE table_name = ? and column_name = ?"); | |
339 | $sth->execute(lc($table), lc($column)); | |
340 | my $res = $sth->fetchrow_hashref(); | |
341 | return defined($res); | |
342 | } | |
343 | ||
344 | sub create_ruledb { | |
345 | my ($dbname) = @_; | |
346 | ||
347 | $dbname = $default_db_name if !$dbname; | |
348 | ||
349 | my $silent_opts = { outfunc => sub {}, errfunc => sub {} }; | |
350 | # make sure we have user 'root' | |
351 | eval { postgres_admin_cmd('createuser', $silent_opts, '-D', 'root'); }; | |
352 | # also create 'www-data' (and give it read-only access below) | |
353 | eval { postgres_admin_cmd('createuser', $silent_opts, '-I', '-D', 'www-data'); }; | |
354 | ||
355 | # use sql_ascii to avoid any character set conversions, and be compatible with | |
356 | # older postgres versions (update from 8.1 must be possible) | |
357 | ||
358 | postgres_admin_cmd('createdb', undef, '-E', 'sql_ascii', '-T', 'template0', | |
359 | '--lc-collate=C', '--lc-ctype=C', $dbname); | |
360 | ||
361 | my $dbh = open_ruledb($dbname); | |
362 | ||
363 | # make sure 'www-data' can read all tables | |
364 | $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\""); | |
365 | ||
366 | $dbh->do ( | |
367 | <<EOD | |
368 | CREATE TABLE Attribut | |
369 | (Object_ID INTEGER NOT NULL, | |
370 | Name VARCHAR(20) NOT NULL, | |
371 | Value BYTEA NULL, | |
372 | PRIMARY KEY (Object_ID, Name)); | |
373 | ||
374 | CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID); | |
375 | ||
376 | CREATE TABLE Object | |
377 | (ID SERIAL UNIQUE, | |
378 | ObjectType INTEGER NOT NULL, | |
379 | Objectgroup_ID INTEGER NOT NULL, | |
380 | Value BYTEA NULL, | |
381 | PRIMARY KEY (ID)); | |
382 | ||
383 | CREATE TABLE Objectgroup | |
384 | (ID SERIAL UNIQUE, | |
385 | Name VARCHAR(255) NOT NULL, | |
386 | Info VARCHAR(255) NULL, | |
387 | Class VARCHAR(10) NOT NULL, | |
388 | PRIMARY KEY (ID)); | |
389 | ||
390 | CREATE TABLE Rule | |
391 | (ID SERIAL UNIQUE, | |
392 | Name VARCHAR(255) NULL, | |
393 | Priority INTEGER NOT NULL, | |
394 | Active INTEGER NOT NULL DEFAULT 0, | |
395 | Direction INTEGER NOT NULL DEFAULT 2, | |
396 | Count INTEGER NOT NULL DEFAULT 0, | |
397 | PRIMARY KEY (ID)); | |
398 | ||
399 | CREATE TABLE RuleGroup | |
400 | (Objectgroup_ID INTEGER NOT NULL, | |
401 | Rule_ID INTEGER NOT NULL, | |
402 | Grouptype INTEGER NOT NULL, | |
403 | PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype)); | |
404 | ||
405 | $cgreylist_ctablecmd; | |
406 | ||
407 | $clusterinfo_ctablecmd; | |
408 | ||
409 | $local_stat_ctablecmd; | |
410 | ||
411 | $daily_stat_ctablecmd; | |
412 | ||
413 | $domain_stat_ctablecmd; | |
414 | ||
415 | $statinfo_ctablecmd; | |
416 | ||
417 | $cmailstore_ctablecmd; | |
418 | ||
419 | $cstatistic_ctablecmd; | |
420 | ||
421 | $userprefs_ctablecmd; | |
422 | ||
423 | $virusinfo_stat_ctablecmd; | |
424 | EOD | |
425 | ); | |
426 | ||
427 | return $dbh; | |
428 | } | |
429 | ||
430 | sub cond_create_action_quarantine { | |
431 | my ($ruledb) = @_; | |
432 | ||
433 | my $dbh = $ruledb->{dbh}; | |
434 | ||
435 | eval { | |
436 | my $sth = $dbh->prepare( | |
437 | "SELECT * FROM Objectgroup, Object " . | |
438 | "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " . | |
439 | "AND Object.objectgroup_id = Objectgroup.id"); | |
440 | ||
441 | my $otype = PMG::RuleDB::Quarantine::otype(); | |
442 | if ($sth->execute($otype, 'action') <= 0) { | |
443 | my $obj = PMG::RuleDB::Quarantine->new (); | |
444 | my $txt = decode_entities(PMG::RuleDB::Quarantine->otype_text); | |
445 | my $quarantine = $ruledb->create_group_with_obj | |
446 | ($obj, $txt, 'Move to quarantine.'); | |
447 | } | |
448 | }; | |
449 | } | |
450 | ||
451 | sub cond_create_std_actions { | |
452 | my ($ruledb) = @_; | |
453 | ||
454 | cond_create_action_quarantine($ruledb); | |
455 | ||
456 | #cond_create_action_report_spam($ruledb); | |
457 | } | |
458 | ||
459 | ||
460 | sub upgradedb { | |
461 | my ($ruledb) = @_; | |
462 | ||
463 | my $dbh = $ruledb->{dbh}; | |
464 | ||
465 | # make sure we do not use slow sequential scans when upgraing | |
466 | # database (before analyze can gather statistics) | |
467 | $dbh->do("set enable_seqscan = false"); | |
468 | ||
469 | my $tables = { | |
470 | 'LocalStat', $local_stat_ctablecmd, | |
471 | 'DailyStat', $daily_stat_ctablecmd, | |
472 | 'DomainStat', $domain_stat_ctablecmd, | |
473 | 'StatInfo', $statinfo_ctablecmd, | |
474 | 'CMailStore', $cmailstore_ctablecmd, | |
475 | 'UserPrefs', $userprefs_ctablecmd, | |
476 | 'CGreylist', $cgreylist_ctablecmd, | |
477 | 'CStatistic', $cstatistic_ctablecmd, | |
478 | 'ClusterInfo', $clusterinfo_ctablecmd, | |
479 | 'VirusInfo', $virusinfo_stat_ctablecmd, | |
480 | }; | |
481 | ||
482 | foreach my $table (keys %$tables) { | |
483 | cond_create_dbtable($dbh, $table, $tables->{$table}); | |
484 | } | |
485 | ||
486 | cond_create_std_actions($ruledb); | |
487 | ||
488 | # upgrade tables here if necessary | |
489 | if (!database_column_exists($dbh, 'LocalStat', 'PregreetCount')) { | |
490 | $dbh->do("ALTER TABLE LocalStat ADD COLUMN " . | |
491 | "PregreetCount INTEGER DEFAULT 0 NOT NULL"); | |
492 | } | |
493 | ||
494 | eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); }; | |
495 | # ignore errors here | |
496 | ||
497 | ||
498 | # add missing TicketID to CMSReceivers | |
499 | if (!database_column_exists($dbh, 'CMSReceivers', 'TicketID')) { | |
500 | eval { | |
501 | $dbh->begin_work; | |
502 | $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq"); | |
503 | $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " . | |
504 | "TicketID INTEGER NOT NULL " . | |
505 | "DEFAULT nextval('cmsreceivers_ticketid_seq')"); | |
506 | $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " . | |
507 | "TicketID DROP DEFAULT"); | |
508 | $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq"); | |
509 | $dbh->commit; | |
510 | }; | |
511 | if (my $err = $@) { | |
512 | $dbh->rollback; | |
513 | die $err; | |
514 | } | |
515 | } | |
516 | ||
517 | # update obsolete content type names | |
518 | eval { | |
519 | $dbh->do("UPDATE Object " . | |
520 | "SET value = 'content-type:application/java-vm' ". | |
521 | "WHERE objecttype = 3003 " . | |
522 | "AND value = 'content-type:application/x-java-vm';"); | |
523 | }; | |
524 | ||
525 | foreach my $table (keys %$tables) { | |
526 | eval { $dbh->do("ANALYZE $table"); }; | |
527 | warn $@ if $@; | |
528 | } | |
529 | ||
530 | reload_ruledb(); | |
531 | } | |
532 | ||
533 | sub init_ruledb { | |
534 | my ($ruledb, $reset, $testmode) = @_; | |
535 | ||
536 | my $dbh = $ruledb->{dbh}; | |
537 | ||
538 | if (!$reset) { | |
539 | # Greylist Objectgroup | |
540 | my $greylistgroup = PMG::RuleDB::Group->new | |
541 | ("GreyExclusion", "-", "greylist"); | |
542 | $ruledb->save_group ($greylistgroup); | |
543 | ||
544 | } else { | |
545 | # we do not touch greylist objects | |
546 | my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " . | |
547 | "objectgroup_id = objectgroup.id and class = 'greylist'"; | |
548 | ||
549 | $dbh->do ("DELETE FROM Rule; " . | |
550 | "DELETE FROM RuleGroup; " . | |
551 | "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " . | |
552 | "DELETE FROM Object WHERE ID NOT IN ($glids); " . | |
553 | "DELETE FROM Objectgroup WHERE class != 'greylist';"); | |
554 | } | |
555 | ||
556 | # WHO Objects | |
557 | ||
558 | # Blacklist | |
559 | my $obj = PMG::RuleDB::EMail->new ('nomail@fromthisdomain.com'); | |
560 | my $blacklist = $ruledb->create_group_with_obj( | |
561 | $obj, 'Blacklist', 'Global blacklist'); | |
562 | ||
563 | # Whitelist | |
564 | $obj = PMG::RuleDB::EMail->new('mail@fromthisdomain.com'); | |
565 | my $whitelist = $ruledb->create_group_with_obj( | |
566 | $obj, 'Whitelist', 'Global whitelist'); | |
567 | ||
568 | # WHEN Objects | |
569 | ||
570 | # Working hours | |
571 | $obj = PMG::RuleDB::TimeFrame->new(8*60, 16*60); | |
572 | my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' , | |
573 | 'Usual office hours'); | |
574 | ||
575 | # WHAT Objects | |
576 | ||
577 | # Images | |
578 | $obj = PMG::RuleDB::ContentTypeFilter->new('image/.*'); | |
579 | my $img_content = $ruledb->create_group_with_obj( | |
580 | $obj, 'Images', 'All kinds of graphic files'); | |
581 | ||
582 | # Multimedia | |
583 | $obj = PMG::RuleDB::ContentTypeFilter->new('audio/.*'); | |
584 | my $mm_content = $ruledb->create_group_with_obj( | |
585 | $obj, 'Multimedia', 'Audio and Video'); | |
586 | ||
587 | $obj = PMG::RuleDB::ContentTypeFilter->new('video/.*'); | |
588 | $ruledb->group_add_object($mm_content, $obj); | |
589 | ||
590 | # Office Files | |
591 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/vnd\.ms-excel'); | |
592 | my $office_content = $ruledb->create_group_with_obj( | |
593 | $obj, 'Office Files', 'Common Office Files'); | |
594 | ||
595 | $obj = PMG::RuleDB::ContentTypeFilter->new( | |
596 | 'application/vnd\.ms-powerpoint'); | |
597 | ||
598 | $ruledb->group_add_object($office_content, $obj); | |
599 | ||
600 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/msword'); | |
601 | $ruledb->group_add_object ($office_content, $obj); | |
602 | ||
603 | $obj = PMG::RuleDB::ContentTypeFilter->new( | |
604 | 'application/vnd\.openxmlformats-officedocument\..*'); | |
605 | $ruledb->group_add_object($office_content, $obj); | |
606 | ||
607 | $obj = PMG::RuleDB::ContentTypeFilter->new( | |
608 | 'application/vnd\.oasis\.opendocument\..*'); | |
609 | $ruledb->group_add_object($office_content, $obj); | |
610 | ||
611 | $obj = PMG::RuleDB::ContentTypeFilter->new( | |
612 | 'application/vnd\.stardivision\..*'); | |
613 | $ruledb->group_add_object($office_content, $obj); | |
614 | ||
615 | $obj = PMG::RuleDB::ContentTypeFilter->new( | |
616 | 'application/vnd\.sun\.xml\..*'); | |
617 | $ruledb->group_add_object($office_content, $obj); | |
618 | ||
619 | # Dangerous Content | |
620 | $obj = PMG::RuleDB::ContentTypeFilter->new( | |
621 | 'application/x-ms-dos-executable'); | |
622 | my $exe_content = $ruledb->create_group_with_obj( | |
623 | $obj, 'Dangerous Content', 'executable files and partial messages'); | |
624 | ||
625 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-java'); | |
626 | $ruledb->group_add_object($exe_content, $obj); | |
627 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/javascript'); | |
628 | $ruledb->group_add_object($exe_content, $obj); | |
629 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-executable'); | |
630 | $ruledb->group_add_object($exe_content, $obj); | |
631 | $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-ms-dos-executable'); | |
632 | $ruledb->group_add_object($exe_content, $obj); | |
633 | $obj = PMG::RuleDB::ContentTypeFilter->new('message/partial'); | |
634 | $ruledb->group_add_object($exe_content, $obj); | |
635 | $obj = PMG::RuleDB::MatchFilename->new('.*\.(vbs|pif|lnk|shs|shb)'); | |
636 | $ruledb->group_add_object($exe_content, $obj); | |
637 | $obj = PMG::RuleDB::MatchFilename->new('.*\.\{.+\}'); | |
638 | $ruledb->group_add_object($exe_content, $obj); | |
639 | ||
640 | # Virus | |
641 | $obj = PMG::RuleDB::Virus->new(); | |
642 | my $virus = $ruledb->create_group_with_obj( | |
643 | $obj, 'Virus', 'Matches virus infected mail'); | |
644 | ||
645 | # WHAT Objects | |
646 | ||
647 | # Spam | |
648 | $obj = PMG::RuleDB::Spam->new(3); | |
649 | my $spam3 = $ruledb->create_group_with_obj( | |
650 | $obj, 'Spam (Level 3)', 'Matches possible spam mail'); | |
651 | ||
652 | $obj = PMG::RuleDB::Spam->new(5); | |
653 | my $spam5 = $ruledb->create_group_with_obj( | |
654 | $obj, 'Spam (Level 5)', 'Matches possible spam mail'); | |
655 | ||
656 | $obj = PMG::RuleDB::Spam->new(10); | |
657 | my $spam10 = $ruledb->create_group_with_obj( | |
658 | $obj, 'Spam (Level 10)', 'Matches possible spam mail'); | |
659 | ||
660 | # ACTIONS | |
661 | ||
662 | # Mark Spam | |
663 | $obj = PMG::RuleDB::ModField->new('X-SPAM-LEVEL', '__SPAM_INFO__'); | |
664 | my $mod_spam_level = $ruledb->create_group_with_obj( | |
665 | $obj, 'Modify Spam Level', | |
666 | 'Mark mail as spam by adding a header tag.'); | |
667 | ||
668 | # Mark Spam | |
669 | $obj = PMG::RuleDB::ModField->new('subject', 'SPAM: __SUBJECT__'); | |
670 | my $mod_spam_subject = $ruledb->create_group_with_obj( | |
671 | $obj, 'Modify Spam Subject', | |
672 | 'Mark mail as spam by modifying the subject.'); | |
673 | ||
674 | # Remove matching attachments | |
675 | $obj = PMG::RuleDB::Remove->new(0); | |
676 | my $remove = $ruledb->create_group_with_obj( | |
677 | $obj, 'Remove attachments', 'Remove matching attachments'); | |
678 | ||
679 | # Remove all attachments | |
680 | $obj = PMG::RuleDB::Remove->new(1); | |
681 | my $remove_all = $ruledb->create_group_with_obj( | |
682 | $obj, 'Remove all attachments', 'Remove all attachments'); | |
683 | ||
684 | # Accept | |
685 | $obj = PMG::RuleDB::Accept->new(); | |
686 | my $accept = $ruledb->create_group_with_obj( | |
687 | $obj, 'Accept', 'Accept mail for Delivery'); | |
688 | ||
689 | # Block | |
690 | $obj = PMG::RuleDB::Block->new (); | |
691 | my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail'); | |
692 | ||
693 | # Quarantine | |
694 | $obj = PMG::RuleDB::Quarantine->new(); | |
695 | my $quarantine = $ruledb->create_group_with_obj( | |
696 | $obj, 'Quarantine', 'Move mail to quarantine'); | |
697 | ||
698 | # Notify Admin | |
699 | $obj = PMG::RuleDB::Notify->new('__ADMIN__'); | |
700 | my $notify_admin = $ruledb->create_group_with_obj( | |
701 | $obj, 'Notify Admin', 'Send notification'); | |
702 | ||
703 | # Notify Sender | |
704 | $obj = PMG::RuleDB::Notify->new('__SENDER__'); | |
705 | my $notify_sender = $ruledb->create_group_with_obj( | |
706 | $obj, 'Notify Sender', 'Send notification'); | |
707 | ||
708 | # Add Disclaimer | |
709 | $obj = PMG::RuleDB::Disclaimer->new (); | |
710 | my $add_discl = $ruledb->create_group_with_obj( | |
711 | $obj, 'Disclaimer', 'Add Disclaimer'); | |
712 | ||
713 | # Attach original mail | |
714 | #$obj = Proxmox::RuleDB::Attach->new (); | |
715 | #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail', | |
716 | # 'Attach Original Mail'); | |
717 | ||
718 | ####################### RULES ################################## | |
719 | ||
720 | ## Block Dangerous Files | |
721 | my $rule = PMG::RuleDB::Rule->new ('Block Dangerous Files', 93, 1, 0); | |
722 | $ruledb->save_rule ($rule); | |
723 | ||
724 | $ruledb->rule_add_what_group ($rule, $exe_content); | |
725 | $ruledb->rule_add_action ($rule, $remove); | |
726 | ||
727 | ## Block Viruses | |
728 | $rule = PMG::RuleDB::Rule->new ('Block Viruses', 96, 1, 0); | |
729 | $ruledb->save_rule ($rule); | |
730 | ||
731 | $ruledb->rule_add_what_group ($rule, $virus); | |
732 | $ruledb->rule_add_action ($rule, $notify_admin); | |
733 | ||
734 | if ($testmode) { | |
735 | $ruledb->rule_add_action ($rule, $block); | |
736 | } else { | |
737 | $ruledb->rule_add_action ($rule, $quarantine); | |
738 | } | |
739 | ||
740 | ## Virus Alert | |
741 | $rule = PMG::RuleDB::Rule->new ('Virus Alert', 96, 1, 1); | |
742 | $ruledb->save_rule ($rule); | |
743 | ||
744 | $ruledb->rule_add_what_group ($rule, $virus); | |
745 | $ruledb->rule_add_action ($rule, $notify_sender); | |
746 | $ruledb->rule_add_action ($rule, $notify_admin); | |
747 | $ruledb->rule_add_action ($rule, $block); | |
748 | ||
749 | ## Blacklist | |
750 | $rule = PMG::RuleDB::Rule->new ('Blacklist', 98, 1, 0); | |
751 | $ruledb->save_rule ($rule); | |
752 | ||
753 | $ruledb->rule_add_from_group ($rule, $blacklist); | |
754 | $ruledb->rule_add_action ($rule, $block); | |
755 | ||
756 | ## Modify header | |
757 | if (!$testmode) { | |
758 | $rule = PMG::RuleDB::Rule->new ('Modify Header', 90, 1, 0); | |
759 | $ruledb->save_rule ($rule); | |
760 | $ruledb->rule_add_action ($rule, $mod_spam_level); | |
761 | } | |
762 | ||
763 | ## Whitelist | |
764 | $rule = PMG::RuleDB::Rule->new ('Whitelist', 85, 1, 0); | |
765 | $ruledb->save_rule ($rule); | |
766 | ||
767 | $ruledb->rule_add_from_group ($rule, $whitelist); | |
768 | $ruledb->rule_add_action ($rule, $accept); | |
769 | ||
770 | if ($testmode) { | |
771 | $rule = PMG::RuleDB::Rule->new ('Mark Spam', 80, 1, 0); | |
772 | $ruledb->save_rule ($rule); | |
773 | ||
774 | $ruledb->rule_add_what_group ($rule, $spam10); | |
775 | $ruledb->rule_add_action ($rule, $mod_spam_level); | |
776 | $ruledb->rule_add_action ($rule, $mod_spam_subject); | |
777 | } else { | |
778 | # Quarantine/Mark Spam (Level 3) | |
779 | $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0); | |
780 | $ruledb->save_rule ($rule); | |
781 | ||
782 | $ruledb->rule_add_what_group ($rule, $spam3); | |
783 | $ruledb->rule_add_action ($rule, $mod_spam_subject); | |
784 | $ruledb->rule_add_action ($rule, $quarantine); | |
785 | #$ruledb->rule_add_action ($rule, $count_spam); | |
786 | } | |
787 | ||
788 | # Quarantine/Mark Spam (Level 5) | |
789 | $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0); | |
790 | $ruledb->save_rule ($rule); | |
791 | ||
792 | $ruledb->rule_add_what_group ($rule, $spam5); | |
793 | $ruledb->rule_add_action ($rule, $mod_spam_subject); | |
794 | $ruledb->rule_add_action ($rule, $quarantine); | |
795 | ||
796 | ## Block Spam Level 10 | |
797 | $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 82, 0, 0); | |
798 | $ruledb->save_rule ($rule); | |
799 | ||
800 | $ruledb->rule_add_what_group ($rule, $spam10); | |
801 | $ruledb->rule_add_action ($rule, $block); | |
802 | ||
803 | ## Block Outgoing Spam | |
804 | $rule = PMG::RuleDB::Rule->new ('Block outgoing Spam', 70, 0, 1); | |
805 | $ruledb->save_rule ($rule); | |
806 | ||
807 | $ruledb->rule_add_what_group ($rule, $spam3); | |
808 | $ruledb->rule_add_action ($rule, $notify_admin); | |
809 | $ruledb->rule_add_action ($rule, $notify_sender); | |
810 | $ruledb->rule_add_action ($rule, $block); | |
811 | ||
812 | ## Add disclaimer | |
813 | $rule = PMG::RuleDB::Rule->new ('Add Disclaimer', 60, 0, 1); | |
814 | $ruledb->save_rule ($rule); | |
815 | $ruledb->rule_add_action ($rule, $add_discl); | |
816 | ||
817 | # Block Multimedia Files | |
818 | $rule = PMG::RuleDB::Rule->new ('Block Multimedia Files', 87, 0, 2); | |
819 | $ruledb->save_rule ($rule); | |
820 | ||
821 | $ruledb->rule_add_what_group ($rule, $mm_content); | |
822 | $ruledb->rule_add_action ($rule, $remove); | |
823 | ||
824 | #$ruledb->rule_add_from_group ($rule, $anybody); | |
825 | #$ruledb->rule_add_from_group ($rule, $trusted); | |
826 | #$ruledb->rule_add_to_group ($rule, $anybody); | |
827 | #$ruledb->rule_add_what_group ($rule, $ct_filter); | |
828 | #$ruledb->rule_add_action ($rule, $add_discl); | |
829 | #$ruledb->rule_add_action ($rule, $remove); | |
830 | #$ruledb->rule_add_action ($rule, $bcc); | |
831 | #$ruledb->rule_add_action ($rule, $storeq); | |
832 | #$ruledb->rule_add_action ($rule, $accept); | |
833 | ||
834 | cond_create_std_actions ($ruledb); | |
835 | ||
836 | reload_ruledb(); | |
837 | } | |
838 | ||
839 | sub get_remote_time { | |
840 | my ($rdb) = @_; | |
841 | ||
842 | my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;"); | |
843 | $sth->execute(); | |
844 | my $ctinfo = $sth->fetchrow_hashref(); | |
845 | $sth->finish (); | |
846 | ||
847 | return $ctinfo ? $ctinfo->{ctime} : 0; | |
848 | } | |
849 | ||
850 | sub init_masterdb { | |
851 | my ($lcid, $database) = @_; | |
852 | ||
853 | die "got unexpected cid for new master" if !$lcid; | |
854 | ||
855 | my $dbh; | |
856 | ||
857 | eval { | |
858 | $dbh = open_ruledb($database); | |
859 | ||
860 | $dbh->begin_work; | |
861 | ||
862 | print STDERR "update quarantine database\n"; | |
863 | $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" . | |
864 | "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;"); | |
865 | ||
866 | print STDERR "update statistic database\n"; | |
867 | $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" . | |
868 | "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;"); | |
869 | ||
870 | print STDERR "update greylist database\n"; | |
871 | $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;"); | |
872 | ||
873 | print STDERR "update localstat database\n"; | |
874 | $dbh->do ("UPDATE LocalStat SET CID = $lcid WHERE CID = 0;"); | |
875 | ||
876 | $dbh->commit; | |
877 | }; | |
878 | my $err = $@; | |
879 | ||
880 | if ($dbh) { | |
881 | $dbh->rollback if $err; | |
882 | $dbh->disconnect(); | |
883 | } | |
884 | ||
885 | die $err if $err; | |
886 | } | |
887 | ||
888 | sub purge_statistic_database { | |
889 | my ($dbh, $statlifetime) = @_; | |
890 | ||
891 | return if $statlifetime <= 0; | |
892 | ||
893 | my (undef, undef, undef, $mday, $mon, $year) = localtime(time()); | |
894 | my $end = timelocal(0, 0, 0, $mday, $mon, $year); | |
895 | my $start = $end - $statlifetime*86400; | |
896 | ||
897 | # delete statistics older than $start | |
898 | ||
899 | my $rows = 0; | |
900 | ||
901 | eval { | |
902 | $dbh->begin_work; | |
903 | ||
904 | my $sth = $dbh->prepare("DELETE FROM CStatistic WHERE time < $start"); | |
905 | $sth->execute; | |
906 | $rows = $sth->rows; | |
907 | $sth->finish; | |
908 | ||
909 | if ($rows > 0) { | |
910 | $sth = $dbh->prepare( | |
911 | "DELETE FROM CReceivers WHERE NOT EXISTS " . | |
912 | "(SELECT * FROM CStatistic WHERE CID = CStatistic_CID AND RID = CStatistic_RID)"); | |
913 | ||
914 | $sth->execute; | |
915 | } | |
916 | $dbh->commit; | |
917 | }; | |
918 | if (my $err = $@) { | |
919 | $dbh->rollback; | |
920 | die $err; | |
921 | } | |
922 | ||
923 | return $rows; | |
924 | } | |
925 | ||
926 | sub purge_quarantine_database { | |
927 | my ($dbh, $qtype, $lifetime) = @_; | |
928 | ||
929 | my $spooldir = $PMG::MailQueue::spooldir; | |
930 | ||
931 | my (undef, undef, undef, $mday, $mon, $year) = localtime(time()); | |
932 | my $end = timelocal(0, 0, 0, $mday, $mon, $year); | |
933 | my $start = $end - $lifetime*86400; | |
934 | ||
935 | my $sth = $dbh->prepare( | |
936 | "SELECT file FROM CMailStore WHERE time < $start AND QType = '$qtype'"); | |
937 | ||
938 | $sth->execute(); | |
939 | ||
940 | my $count = 0; | |
941 | ||
942 | while (my $ref = $sth->fetchrow_hashref()) { | |
943 | my $filename = "$spooldir/$ref->{file}"; | |
944 | $count++ if unlink($filename); | |
945 | } | |
946 | ||
947 | $sth->finish(); | |
948 | ||
949 | $dbh->do( | |
950 | "DELETE FROM CMailStore WHERE time < $start AND QType = '$qtype';" . | |
951 | "DELETE FROM CMSReceivers WHERE NOT EXISTS " . | |
952 | "(SELECT * FROM CMailStore WHERE CID = CMailStore_CID AND RID = CMailStore_RID)"); | |
953 | ||
954 | return $count; | |
955 | } | |
956 | ||
957 | sub get_quarantine_count { | |
958 | my ($dbh, $qtype) = @_; | |
959 | ||
960 | # Note;: We try to estimate used disk space - each mail | |
961 | # is stored in an extra file ... | |
962 | ||
963 | my $bs = 4096; | |
964 | ||
965 | my $sth = $dbh->prepare( | |
966 | "SELECT count(ID) as count, sum (ceil((Bytes+$bs-1)/$bs)*$bs) / (1024*1024) as mbytes, " . | |
967 | "avg(Bytes) as avgbytes, avg(Spamlevel) as avgspam " . | |
968 | "FROM CMailStore WHERE QType = ?"); | |
969 | ||
970 | $sth->execute($qtype); | |
971 | ||
972 | my $ref = $sth->fetchrow_hashref(); | |
973 | ||
974 | $sth->finish; | |
975 | ||
976 | foreach my $k (qw(count mbytes avgbytes avgspam)) { | |
977 | $ref->{$k} //= 0; | |
978 | } | |
979 | ||
980 | return $ref; | |
981 | } | |
982 | ||
983 | sub copy_table { | |
984 | my ($ldb, $rdb, $table) = @_; | |
985 | ||
986 | $table = lc($table); | |
987 | ||
988 | my $sth = $ldb->column_info(undef, undef, $table, undef); | |
989 | my $attrs = $sth->fetchall_arrayref({}); | |
990 | ||
991 | my @col_arr; | |
992 | foreach my $ref (@$attrs) { | |
993 | push @col_arr, $ref->{COLUMN_NAME}; | |
994 | } | |
995 | ||
996 | $sth->finish(); | |
997 | ||
998 | my $cols = join(', ', @col_arr); | |
999 | $cols || die "unable to fetch column definitions of table '$table' : ERROR"; | |
1000 | ||
1001 | $rdb->do("COPY $table ($cols) TO STDOUT"); | |
1002 | ||
1003 | my $data = ''; | |
1004 | ||
1005 | eval { | |
1006 | $ldb->do("COPY $table ($cols) FROM stdin"); | |
1007 | ||
1008 | while ($rdb->pg_getcopydata($data) >= 0) { | |
1009 | $ldb->pg_putcopydata($data); | |
1010 | } | |
1011 | ||
1012 | $ldb->pg_putcopyend(); | |
1013 | }; | |
1014 | if (my $err = $@) { | |
1015 | $ldb->pg_putcopyend(); | |
1016 | die $err; | |
1017 | } | |
1018 | } | |
1019 | ||
1020 | sub copy_selected_data { | |
1021 | my ($dbh, $select_sth, $table, $attrs, $callback) = @_; | |
1022 | ||
1023 | my $count = 0; | |
1024 | ||
1025 | my $insert_sth = $dbh->prepare( | |
1026 | "INSERT INTO ${table}(" . join(',', @$attrs) . ') ' . | |
1027 | 'VALUES (' . join(',', ('?') x scalar(@$attrs)) . ')'); | |
1028 | ||
1029 | while (my $ref = $select_sth->fetchrow_hashref()) { | |
1030 | $callback->($ref) if $callback; | |
1031 | $count++; | |
1032 | $insert_sth->execute(map { $ref->{$_} } @$attrs); | |
1033 | } | |
1034 | ||
1035 | return $count; | |
1036 | } | |
1037 | ||
1038 | sub update_master_clusterinfo { | |
1039 | my ($clientcid) = @_; | |
1040 | ||
1041 | my $dbh = open_ruledb(); | |
1042 | ||
1043 | $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid"); | |
1044 | ||
1045 | my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo'); | |
1046 | ||
1047 | foreach my $table (@mt) { | |
1048 | $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " . | |
1049 | "EXTRACT(EPOCH FROM now())"); | |
1050 | } | |
1051 | } | |
1052 | ||
1053 | sub update_client_clusterinfo { | |
1054 | my ($mastercid) = @_; | |
1055 | ||
1056 | my $dbh = open_ruledb(); | |
1057 | ||
1058 | $dbh->do ("DELETE FROM StatInfo"); # not needed at node | |
1059 | ||
1060 | $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid"); | |
1061 | ||
1062 | $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " . | |
1063 | "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid"); | |
1064 | ||
1065 | $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " . | |
1066 | "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid"); | |
1067 | ||
1068 | my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo'); | |
1069 | ||
1070 | foreach my $table (@mt) { | |
1071 | $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " . | |
1072 | "COALESCE (max (mtime), 0) FROM $table"); | |
1073 | } | |
1074 | } | |
1075 | ||
1076 | sub create_clusterinfo_default { | |
1077 | my ($dbh, $rcid, $name, $ivalue, $svalue) = @_; | |
1078 | ||
1079 | my $sth = $dbh->prepare("SELECT * FROM ClusterInfo WHERE CID = ? AND Name = ?"); | |
1080 | $sth->execute($rcid, $name); | |
1081 | if (!$sth->fetchrow_hashref()) { | |
1082 | $dbh->do("INSERT INTO ClusterInfo (CID, Name, IValue, SValue) " . | |
1083 | "VALUES (?, ?, ?, ?)", undef, | |
1084 | $rcid, $name, $ivalue, $svalue); | |
1085 | } | |
1086 | $sth->finish(); | |
1087 | } | |
1088 | ||
1089 | sub read_int_clusterinfo { | |
1090 | my ($dbh, $rcid, $name) = @_; | |
1091 | ||
1092 | my $sth = $dbh->prepare( | |
1093 | "SELECT ivalue as value FROM ClusterInfo " . | |
1094 | "WHERE cid = ? AND NAME = ?"); | |
1095 | $sth->execute($rcid, $name); | |
1096 | my $cinfo = $sth->fetchrow_hashref(); | |
1097 | $sth->finish(); | |
1098 | ||
1099 | return $cinfo->{value}; | |
1100 | } | |
1101 | ||
1102 | sub write_maxint_clusterinfo { | |
1103 | my ($dbh, $rcid, $name, $value) = @_; | |
1104 | ||
1105 | $dbh->do("UPDATE ClusterInfo SET ivalue = GREATEST(ivalue, ?) " . | |
1106 | "WHERE cid = ? AND name = ?", undef, | |
1107 | $value, $rcid, $name); | |
1108 | } | |
1109 | ||
1110 | sub init_nodedb { | |
1111 | my ($cinfo) = @_; | |
1112 | ||
1113 | my $ni = $cinfo->{master}; | |
1114 | ||
1115 | die "no master defined - unable to sync data from master\n" if !$ni; | |
1116 | ||
1117 | my $master_ip = $ni->{ip}; | |
1118 | my $master_cid = $ni->{cid}; | |
1119 | my $master_name = $ni->{name}; | |
1120 | ||
1121 | my $fn = "/tmp/masterdb$$.tar"; | |
1122 | unlink $fn; | |
1123 | ||
1124 | my $dbname = $default_db_name; | |
1125 | ||
1126 | eval { | |
1127 | print STDERR "copying master database from '${master_ip}'\n"; | |
1128 | ||
1129 | open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n"; | |
1130 | ||
1131 | my $cmd = ['/usr/bin/ssh', '-o', 'BatchMode=yes', | |
1132 | '-o', "HostKeyAlias=${master_name}", $master_ip, | |
1133 | 'pg_dump', $dbname, '-F', 'c' ]; | |
1134 | ||
1135 | PVE::Tools::run_command($cmd, output => '>&' . fileno($fh)); | |
1136 | ||
1137 | close($fh); | |
1138 | ||
1139 | my $size = -s $fn; | |
1140 | ||
1141 | print STDERR "copying master database finished (got $size bytes)\n"; | |
1142 | ||
1143 | print STDERR "delete local database\n"; | |
1144 | ||
1145 | postgres_admin_cmd('dropdb', undef, $dbname , '--if-exists'); | |
1146 | ||
1147 | print STDERR "create new local database\n"; | |
1148 | ||
1149 | postgres_admin_cmd('createdb', undef, $dbname); | |
1150 | ||
1151 | print STDERR "insert received data into local database\n"; | |
1152 | ||
1153 | my $mess; | |
1154 | my $parser = sub { | |
1155 | my $line = shift; | |
1156 | ||
1157 | if ($line =~ m/restoring data for table \"(.+)\"/) { | |
1158 | print STDERR "restoring table $1\n"; | |
1159 | } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) { | |
1160 | $mess = "creating indexes"; | |
1161 | print STDERR "$mess\n"; | |
1162 | } | |
1163 | }; | |
1164 | ||
1165 | my $opts = { | |
1166 | outfunc => $parser, | |
1167 | errfunc => $parser, | |
1168 | errmsg => "pg_restore failed" | |
1169 | }; | |
1170 | ||
1171 | postgres_admin_cmd('pg_restore', $opts, '-d', $dbname, '-v', $fn); | |
1172 | ||
1173 | print STDERR "run analyze to speed up database queries\n"; | |
1174 | ||
1175 | postgres_admin_cmd('psql', { input => 'analyze;' }, $dbname); | |
1176 | ||
1177 | update_client_clusterinfo($master_cid); | |
1178 | }; | |
1179 | ||
1180 | my $err = $@; | |
1181 | ||
1182 | unlink $fn; | |
1183 | ||
1184 | die $err if $err; | |
1185 | } | |
1186 | ||
1187 | sub cluster_sync_status { | |
1188 | my ($cinfo) = @_; | |
1189 | ||
1190 | my $dbh; | |
1191 | ||
1192 | my $minmtime; | |
1193 | ||
1194 | foreach my $ni (values %{$cinfo->{ids}}) { | |
1195 | next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID | |
1196 | $minmtime->{$ni->{cid}} = 0; | |
1197 | } | |
1198 | ||
1199 | eval { | |
1200 | $dbh = open_ruledb(); | |
1201 | ||
1202 | my $sth = $dbh->prepare( | |
1203 | "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " . | |
1204 | "WHERE name = 'lastsync' AND ivalue > 0 " . | |
1205 | "GROUP BY cid"); | |
1206 | ||
1207 | $sth->execute(); | |
1208 | ||
1209 | while (my $info = $sth->fetchrow_hashref()) { | |
1210 | foreach my $ni (values %{$cinfo->{ids}}) { | |
1211 | next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID | |
1212 | if ($ni->{cid} == $info->{cid}) { # node exists | |
1213 | $minmtime->{$ni->{cid}} = $info->{minmtime}; | |
1214 | } | |
1215 | } | |
1216 | } | |
1217 | ||
1218 | $sth->finish(); | |
1219 | }; | |
1220 | my $err = $@; | |
1221 | ||
1222 | $dbh->disconnect() if $dbh; | |
1223 | ||
1224 | syslog('err', $err) if $err; | |
1225 | ||
1226 | return $minmtime; | |
1227 | } | |
1228 | ||
1229 | sub load_mail_data { | |
1230 | my ($dbh, $cid, $rid, $ticketid) = @_; | |
1231 | ||
1232 | my $sth = $dbh->prepare( | |
1233 | "SELECT * FROM CMailStore, CMSReceivers WHERE " . | |
1234 | "CID = ? AND RID = ? AND TicketID = ? AND " . | |
1235 | "CID = CMailStore_CID AND RID = CMailStore_RID"); | |
1236 | $sth->execute($cid, $rid, $ticketid); | |
1237 | ||
1238 | my $res = $sth->fetchrow_hashref(); | |
1239 | ||
1240 | $sth->finish(); | |
1241 | ||
1242 | die "no such mail (C${cid}R${rid}T${ticketid})\n" if !defined($res); | |
1243 | ||
1244 | return $res; | |
1245 | } | |
1246 | ||
1247 | sub reload_ruledb { | |
1248 | my ($ruledb) = @_; | |
1249 | ||
1250 | # Note: we pass $ruledb when modifying SMTP whitelist | |
1251 | if (defined($ruledb)) { | |
1252 | eval { | |
1253 | my $rulecache = PMG::RuleCache->new($ruledb); | |
1254 | PMG::Config::rewrite_postfix_whitelist($rulecache); | |
1255 | }; | |
1256 | if (my $err = $@) { | |
1257 | warn "problems updating SMTP whitelist - $err"; | |
1258 | } | |
1259 | } | |
1260 | ||
1261 | my $pid_file = '/var/run/pmg-smtp-filter.pid'; | |
1262 | my $pid = PVE::Tools::file_read_firstline($pid_file); | |
1263 | ||
1264 | return 0 if !$pid; | |
1265 | ||
1266 | return 0 if $pid !~ m/^(\d+)$/; | |
1267 | $pid = $1; # untaint | |
1268 | ||
1269 | return kill (10, $pid); # send SIGUSR1 | |
1270 | } | |
1271 | ||
1272 | 1; |