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