src/PMG/DBTools.pm

   1 package PMG::DBTools;
   2
   3 use strict;
   4 use warnings;
   5
   6 use POSIX ":sys_wait_h";
   7 use POSIX qw(:signal_h getuid);
   8 use DBI;
   9 use Time::Local;
  10
  11 use PVE::SafeSyslog;
  12 use PVE::Tools;
  13
  14 use PMG::RuleDB;
  15 use PMG::MailQueue;
  16 use PMG::Config;
  17 use PMG::Utils qw(postgres_admin_cmd);
  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 '/run/pmgtunnel',
  43         # and the cluster 'cid' as port number. You can connect to the
  44         # socket with: host => /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 delete_ruledb {
  80     my ($dbname) = @_;
  81
  82     postgres_admin_cmd('dropdb', undef, $dbname);
  83 }
  84
  85 sub database_list {
  86
  87     my $database_list = {};
  88
  89     my $parser = sub {
  90         my $line = shift;
  91
  92         my ($name, $owner) = map { PVE::Tools::trim($_) } split(/\|/, $line);
  93         return if !$name || !$owner;
  94
  95         $database_list->{$name} = { owner => $owner };
  96     };
  97
  98     postgres_admin_cmd('psql', { outfunc => $parser }, '--list', '--quiet', '--tuples-only');
  99
 100     return $database_list;
 101 }
 102
 103 my $cgreylist_ctablecmd =  <<__EOD;
 104     CREATE TABLE CGreylist
 105     (IPNet VARCHAR(16) NOT NULL,
 106      Host INTEGER NOT NULL,
 107      Sender VARCHAR(255) NOT NULL,
 108      Receiver VARCHAR(255) NOT NULL,
 109      Instance VARCHAR(255),
 110      RCTime INTEGER NOT NULL,
 111      ExTime INTEGER NOT NULL,
 112      Delay INTEGER NOT NULL DEFAULT 0,
 113      Blocked INTEGER NOT NULL,
 114      Passed INTEGER NOT NULL,
 115      CID INTEGER NOT NULL,
 116      MTime INTEGER NOT NULL,
 117      PRIMARY KEY (IPNet, Sender, Receiver));
 118
 119     CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender);
 120
 121     CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime);
 122
 123     CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime);
 124 __EOD
 125
 126 my $clusterinfo_ctablecmd =  <<__EOD;
 127     CREATE TABLE ClusterInfo
 128     (CID INTEGER NOT NULL,
 129      Name VARCHAR NOT NULL,
 130      IValue INTEGER,
 131      SValue VARCHAR,
 132      PRIMARY KEY (CID, Name))
 133 __EOD
 134
 135 my $local_stat_ctablecmd =  <<__EOD;
 136     CREATE TABLE LocalStat
 137     (Time INTEGER NOT NULL,
 138      RBLCount INTEGER DEFAULT 0 NOT NULL,
 139      PregreetCount INTEGER DEFAULT 0 NOT NULL,
 140      CID INTEGER NOT NULL,
 141      MTime INTEGER NOT NULL,
 142      PRIMARY KEY (Time, CID));
 143
 144     CREATE INDEX LocalStat_MTime_Index ON LocalStat (MTime);
 145 __EOD
 146
 147
 148 my $daily_stat_ctablecmd =  <<__EOD;
 149     CREATE TABLE DailyStat
 150     (Time INTEGER NOT NULL UNIQUE,
 151      CountIn INTEGER NOT NULL,
 152      CountOut INTEGER NOT NULL,
 153      BytesIn REAL NOT NULL,
 154      BytesOut REAL NOT NULL,
 155      VirusIn INTEGER NOT NULL,
 156      VirusOut INTEGER NOT NULL,
 157      SpamIn INTEGER NOT NULL,
 158      SpamOut INTEGER NOT NULL,
 159      BouncesIn INTEGER NOT NULL,
 160      BouncesOut INTEGER NOT NULL,
 161      GreylistCount INTEGER NOT NULL,
 162      SPFCount INTEGER NOT NULL,
 163      PTimeSum REAL NOT NULL,
 164      MTime INTEGER NOT NULL,
 165      RBLCount INTEGER DEFAULT 0 NOT NULL,
 166      PRIMARY KEY (Time));
 167
 168     CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
 169
 170 __EOD
 171
 172 my $domain_stat_ctablecmd =  <<__EOD;
 173     CREATE TABLE DomainStat
 174     (Time INTEGER NOT NULL,
 175      Domain VARCHAR(255) NOT NULL,
 176      CountIn INTEGER NOT NULL,
 177      CountOut INTEGER NOT NULL,
 178      BytesIn REAL NOT NULL,
 179      BytesOut REAL NOT NULL,
 180      VirusIn INTEGER NOT NULL,
 181      VirusOut INTEGER NOT NULL,
 182      SpamIn INTEGER NOT NULL,
 183      SpamOut INTEGER NOT NULL,
 184      BouncesIn INTEGER NOT NULL,
 185      BouncesOut INTEGER NOT NULL,
 186      PTimeSum REAL NOT NULL,
 187      MTime INTEGER NOT NULL,
 188      PRIMARY KEY (Time, Domain));
 189
 190     CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
 191 __EOD
 192
 193 my $statinfo_ctablecmd =  <<__EOD;
 194     CREATE TABLE StatInfo
 195     (Name VARCHAR(255) NOT NULL UNIQUE,
 196      IValue INTEGER,
 197      SValue VARCHAR(255),
 198      PRIMARY KEY (Name))
 199 __EOD
 200
 201 my $virusinfo_stat_ctablecmd = <<__EOD;
 202     CREATE TABLE VirusInfo
 203     (Time INTEGER NOT NULL,
 204      Name VARCHAR NOT NULL,
 205      Count INTEGER NOT NULL,
 206      MTime INTEGER NOT NULL,
 207      PRIMARY KEY (Time, Name));
 208
 209     CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
 210
 211 __EOD
 212
 213 # mail storage table
 214 # QTypes
 215 # V - Virus quarantine
 216 # S - Spam quarantine
 217 # D - Delayed Mails - not implemented
 218 # A - Held for Audit - not implemented
 219 # Status
 220 # N - new
 221 # D - deleted
 222
 223 my $cmailstore_ctablecmd =  <<__EOD;
 224     CREATE TABLE CMailStore
 225     (CID INTEGER DEFAULT 0 NOT NULL,
 226      RID INTEGER NOT NULL,
 227      ID SERIAL UNIQUE,
 228      Time INTEGER NOT NULL,
 229      QType "char" NOT NULL,
 230      Bytes INTEGER NOT NULL,
 231      Spamlevel INTEGER NOT NULL,
 232      Info VARCHAR NULL,
 233      Sender VARCHAR(255) NOT NULL,
 234      Header VARCHAR NOT NULL,
 235      File VARCHAR(255) NOT NULL,
 236      PRIMARY KEY (CID, RID));
 237     CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
 238
 239     CREATE TABLE CMSReceivers
 240     (CMailStore_CID INTEGER NOT NULL,
 241      CMailStore_RID INTEGER NOT NULL,
 242      PMail VARCHAR(255) NOT NULL,
 243      Receiver VARCHAR(255),
 244      TicketID INTEGER NOT NULL,
 245      Status "char" NOT NULL,
 246      MTime INTEGER NOT NULL);
 247
 248     CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
 249
 250     CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
 251
 252 __EOD
 253
 254 my $cstatistic_ctablecmd =  <<__EOD;
 255     CREATE TABLE CStatistic
 256     (CID INTEGER DEFAULT 0 NOT NULL,
 257      RID INTEGER NOT NULL,
 258      ID SERIAL UNIQUE,
 259      Time INTEGER NOT NULL,
 260      Bytes INTEGER NOT NULL,
 261      Direction Boolean NOT NULL,
 262      Spamlevel INTEGER NOT NULL,
 263      VirusInfo VARCHAR(255) NULL,
 264      PTime INTEGER NOT NULL,
 265      Sender VARCHAR(255) NOT NULL,
 266      PRIMARY KEY (CID, RID));
 267
 268     CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
 269
 270     CREATE TABLE CReceivers
 271     (CStatistic_CID INTEGER NOT NULL,
 272      CStatistic_RID INTEGER NOT NULL,
 273      Receiver VARCHAR(255) NOT NULL,
 274      Blocked Boolean NOT NULL);
 275
 276     CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
 277 __EOD
 278
 279 # user preferences (black an whitelists, ...)
 280 # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
 281 # Data: arbitrary data
 282 my $userprefs_ctablecmd =  <<__EOD;
 283     CREATE TABLE UserPrefs
 284     (PMail VARCHAR,
 285      Name VARCHAR(255),
 286      Data VARCHAR,
 287      MTime INTEGER NOT NULL,
 288      PRIMARY KEY (PMail, Name));
 289
 290     CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
 291
 292 __EOD
 293
 294 sub cond_create_dbtable {
 295     my ($dbh, $name, $ctablecmd) = @_;
 296
 297     eval {
 298         $dbh->begin_work;
 299
 300         my $cmd = "SELECT tablename FROM pg_tables " .
 301             "WHERE tablename = lower ('$name')";
 302
 303         my $sth = $dbh->prepare($cmd);
 304
 305         $sth->execute();
 306
 307         if (!(my $ref = $sth->fetchrow_hashref())) {
 308             $dbh->do ($ctablecmd);
 309         }
 310
 311         $sth->finish();
 312
 313         $dbh->commit;
 314     };
 315     if (my $err = $@) {
 316         $dbh->rollback;
 317         die $err;
 318     }
 319 }
 320
 321 sub database_column_exists {
 322     my ($dbh, $table, $column) = @_;
 323
 324     my $sth = $dbh->prepare(
 325         "SELECT column_name FROM information_schema.columns " .
 326         "WHERE table_name = ? and column_name = ?");
 327     $sth->execute(lc($table), lc($column));
 328     my $res = $sth->fetchrow_hashref();
 329     return defined($res);
 330 }
 331
 332 my $createdb = sub {
 333     my ($dbname) = @_;
 334     postgres_admin_cmd(
 335         'createdb',
 336         undef,
 337         '-E', 'sql_ascii',
 338         '-T', 'template0',
 339         '--lc-collate=C',
 340         '--lc-ctype=C',
 341         $dbname,
 342     );
 343 };
 344
 345 sub create_ruledb {
 346     my ($dbname) = @_;
 347
 348     $dbname = $default_db_name if !$dbname;
 349
 350     my $silent_opts = { outfunc => sub {}, errfunc => sub {} };
 351     # make sure we have user 'root'
 352     eval { postgres_admin_cmd('createuser',  $silent_opts, '-D', 'root'); };
 353     # also create 'www-data' (and give it read-only access below)
 354     eval { postgres_admin_cmd('createuser',  $silent_opts, '-I', '-D', 'www-data'); };
 355
 356     # use sql_ascii to avoid any character set conversions, and be compatible with
 357     # older postgres versions (update from 8.1 must be possible)
 358
 359     $createdb->($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     # Move to attachment quarantine
 714     $obj = PMG::RuleDB::Remove->new(0, undef, undef, 1);
 715     my $attach_quar = $ruledb->create_group_with_obj(
 716         $obj, 'Attachment Quarantine (remove matching)', 'Remove matching attachments and move the whole mail to the attachment quarantine.');
 717
 718     # Remove all attachments
 719     $obj = PMG::RuleDB::Remove->new(1, undef, undef, 1);
 720     my $attach_quar_all = $ruledb->create_group_with_obj(
 721         $obj, 'Attachment Quarantine (remove all)', 'Remove all attachments and move the whole mail to the attachment quarantine.');
 722
 723     # Attach original mail
 724     #$obj = Proxmox::RuleDB::Attach->new ();
 725     #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
 726     #                                         'Attach Original Mail');
 727
 728     ####################### RULES ##################################
 729
 730     ## Block Dangerous  Files
 731     my $rule = PMG::RuleDB::Rule->new ('Block Dangerous Files', 93, 1, 0);
 732     $ruledb->save_rule ($rule);
 733
 734     $ruledb->rule_add_what_group ($rule, $exe_content);
 735     $ruledb->rule_add_action ($rule, $remove);
 736
 737     ## Block Viruses
 738     $rule = PMG::RuleDB::Rule->new ('Block Viruses', 96, 1, 0);
 739     $ruledb->save_rule ($rule);
 740
 741     $ruledb->rule_add_what_group ($rule, $virus);
 742     $ruledb->rule_add_action ($rule, $notify_admin);
 743
 744     if ($testmode) {
 745         $ruledb->rule_add_action ($rule, $block);
 746     } else {
 747         $ruledb->rule_add_action ($rule, $quarantine);
 748     }
 749
 750     ## Virus Alert
 751     $rule = PMG::RuleDB::Rule->new ('Virus Alert', 96, 1, 1);
 752     $ruledb->save_rule ($rule);
 753
 754     $ruledb->rule_add_what_group ($rule, $virus);
 755     $ruledb->rule_add_action ($rule, $notify_sender);
 756     $ruledb->rule_add_action ($rule, $notify_admin);
 757     $ruledb->rule_add_action ($rule, $block);
 758
 759     ## Blacklist
 760     $rule = PMG::RuleDB::Rule->new ('Blacklist', 98, 1, 0);
 761     $ruledb->save_rule ($rule);
 762
 763     $ruledb->rule_add_from_group ($rule, $blacklist);
 764     $ruledb->rule_add_action ($rule, $block);
 765
 766     ## Modify header
 767     if (!$testmode) {
 768         $rule = PMG::RuleDB::Rule->new ('Modify Header', 90, 1, 0);
 769         $ruledb->save_rule ($rule);
 770         $ruledb->rule_add_action ($rule, $mod_spam_level);
 771     }
 772
 773     ## Whitelist
 774     $rule = PMG::RuleDB::Rule->new ('Whitelist', 85, 1, 0);
 775     $ruledb->save_rule ($rule);
 776
 777     $ruledb->rule_add_from_group ($rule, $whitelist);
 778     $ruledb->rule_add_action ($rule, $accept);
 779
 780     if ($testmode) {
 781         $rule = PMG::RuleDB::Rule->new ('Mark Spam', 80, 1, 0);
 782         $ruledb->save_rule ($rule);
 783
 784         $ruledb->rule_add_what_group ($rule, $spam10);
 785         $ruledb->rule_add_action ($rule, $mod_spam_level);
 786         $ruledb->rule_add_action ($rule, $mod_spam_subject);
 787     } else {
 788         # Quarantine/Mark Spam (Level 3)
 789         $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
 790         $ruledb->save_rule ($rule);
 791
 792         $ruledb->rule_add_what_group ($rule, $spam3);
 793         $ruledb->rule_add_action ($rule, $mod_spam_subject);
 794         $ruledb->rule_add_action ($rule, $quarantine);
 795         #$ruledb->rule_add_action ($rule, $count_spam);
 796     }
 797
 798     # Quarantine/Mark Spam (Level 5)
 799     $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0);
 800     $ruledb->save_rule ($rule);
 801
 802     $ruledb->rule_add_what_group ($rule, $spam5);
 803     $ruledb->rule_add_action ($rule, $mod_spam_subject);
 804     $ruledb->rule_add_action ($rule, $quarantine);
 805
 806     ## Block Spam Level 10
 807     $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 82, 0, 0);
 808     $ruledb->save_rule ($rule);
 809
 810     $ruledb->rule_add_what_group ($rule, $spam10);
 811     $ruledb->rule_add_action ($rule, $block);
 812
 813     ## Block Outgoing Spam
 814     $rule = PMG::RuleDB::Rule->new ('Block outgoing Spam', 70, 0, 1);
 815     $ruledb->save_rule ($rule);
 816
 817     $ruledb->rule_add_what_group ($rule, $spam3);
 818     $ruledb->rule_add_action ($rule, $notify_admin);
 819     $ruledb->rule_add_action ($rule, $notify_sender);
 820     $ruledb->rule_add_action ($rule, $block);
 821
 822     ## Add disclaimer
 823     $rule = PMG::RuleDB::Rule->new ('Add Disclaimer', 60, 0, 1);
 824     $ruledb->save_rule ($rule);
 825     $ruledb->rule_add_action ($rule, $add_discl);
 826
 827     # Block Multimedia Files
 828     $rule = PMG::RuleDB::Rule->new ('Block Multimedia Files', 87, 0, 2);
 829     $ruledb->save_rule ($rule);
 830
 831     $ruledb->rule_add_what_group ($rule, $mm_content);
 832     $ruledb->rule_add_action ($rule, $remove);
 833
 834     # Quarantine Office Files
 835     $rule = PMG::RuleDB::Rule->new ('Quarantine Office Files', 89, 0, 0);
 836     $ruledb->save_rule ($rule);
 837
 838     $ruledb->rule_add_what_group ($rule, $office_content);
 839     $ruledb->rule_add_action ($rule, $attach_quar);
 840
 841     #$ruledb->rule_add_from_group ($rule, $anybody);
 842     #$ruledb->rule_add_from_group ($rule, $trusted);
 843     #$ruledb->rule_add_to_group ($rule, $anybody);
 844     #$ruledb->rule_add_what_group ($rule, $ct_filter);
 845     #$ruledb->rule_add_action ($rule, $add_discl);
 846     #$ruledb->rule_add_action ($rule, $remove);
 847     #$ruledb->rule_add_action ($rule, $bcc);
 848     #$ruledb->rule_add_action ($rule, $storeq);
 849     #$ruledb->rule_add_action ($rule, $accept);
 850
 851     cond_create_std_actions ($ruledb);
 852
 853     reload_ruledb();
 854 }
 855
 856 sub get_remote_time {
 857     my ($rdb) = @_;
 858
 859     my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
 860     $sth->execute();
 861     my $ctinfo = $sth->fetchrow_hashref();
 862     $sth->finish ();
 863
 864     return $ctinfo ? $ctinfo->{ctime} : 0;
 865 }
 866
 867 sub init_masterdb {
 868     my ($lcid, $database) = @_;
 869
 870     die "got unexpected cid for new master" if !$lcid;
 871
 872     my $dbh;
 873
 874     eval {
 875         $dbh = open_ruledb($database);
 876
 877         $dbh->begin_work;
 878
 879         print STDERR "update quarantine database\n";
 880         $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
 881                   "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
 882
 883         print STDERR "update statistic database\n";
 884         $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
 885                   "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
 886
 887         print STDERR "update greylist database\n";
 888         $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
 889
 890         print STDERR "update localstat database\n";
 891         $dbh->do ("UPDATE LocalStat SET CID = $lcid WHERE CID = 0;");
 892
 893         $dbh->commit;
 894     };
 895     my $err = $@;
 896
 897     if ($dbh) {
 898         $dbh->rollback if $err;
 899         $dbh->disconnect();
 900     }
 901
 902     die $err if $err;
 903 }
 904
 905 sub purge_statistic_database {
 906     my ($dbh, $statlifetime) = @_;
 907
 908     return if $statlifetime <= 0;
 909
 910     my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
 911     my $end = timelocal(0, 0, 0, $mday, $mon, $year);
 912     my $start = $end - $statlifetime*86400;
 913
 914     # delete statistics older than $start
 915
 916     my $rows = 0;
 917
 918     eval {
 919         $dbh->begin_work;
 920
 921         my $sth = $dbh->prepare("DELETE FROM CStatistic WHERE time < $start");
 922         $sth->execute;
 923         $rows = $sth->rows;
 924         $sth->finish;
 925
 926         if ($rows > 0) {
 927             $sth = $dbh->prepare(
 928                 "DELETE FROM CReceivers WHERE NOT EXISTS " .
 929                 "(SELECT * FROM CStatistic WHERE CID = CStatistic_CID AND RID = CStatistic_RID)");
 930
 931             $sth->execute;
 932         }
 933         $dbh->commit;
 934     };
 935     if (my $err = $@) {
 936         $dbh->rollback;
 937         die $err;
 938     }
 939
 940     return $rows;
 941 }
 942
 943 sub purge_quarantine_database {
 944     my ($dbh, $qtype, $lifetime) = @_;
 945
 946     my $spooldir = $PMG::MailQueue::spooldir;
 947
 948     my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
 949     my $end = timelocal(0, 0, 0, $mday, $mon, $year);
 950     my $start = $end - $lifetime*86400;
 951
 952     my $sth = $dbh->prepare(
 953         "SELECT file FROM CMailStore WHERE time < $start AND QType = '$qtype'");
 954
 955     $sth->execute();
 956
 957     my $count = 0;
 958
 959     while (my $ref = $sth->fetchrow_hashref()) {
 960         my $filename = "$spooldir/$ref->{file}";
 961         $count++ if unlink($filename);
 962     }
 963
 964     $sth->finish();
 965
 966     $dbh->do(
 967         "DELETE FROM CMailStore WHERE time < $start AND QType = '$qtype';" .
 968         "DELETE FROM CMSReceivers WHERE NOT EXISTS " .
 969         "(SELECT * FROM CMailStore WHERE CID = CMailStore_CID AND RID = CMailStore_RID)");
 970
 971     return $count;
 972 }
 973
 974 sub get_quarantine_count {
 975     my ($dbh, $qtype) = @_;
 976
 977     # Note;: We try to estimate used disk space - each mail
 978     # is stored in an extra file ...
 979
 980     my $bs = 4096;
 981
 982     my $sth = $dbh->prepare(
 983         "SELECT count(ID) as count,  sum (ceil((Bytes+$bs-1)/$bs)*$bs) / (1024*1024) as mbytes, " .
 984         "avg(Bytes) as avgbytes, avg(Spamlevel) as avgspam " .
 985         "FROM CMailStore WHERE QType = ?");
 986
 987     $sth->execute($qtype);
 988
 989     my $ref = $sth->fetchrow_hashref();
 990
 991     $sth->finish;
 992
 993     foreach my $k (qw(count mbytes avgbytes avgspam)) {
 994         $ref->{$k} //= 0;
 995     }
 996
 997     return $ref;
 998 }
 999
1000 sub copy_table {
1001     my ($ldb, $rdb, $table) = @_;
1002
1003     $table = lc($table);
1004
1005     my $sth = $ldb->column_info(undef, undef, $table, undef);
1006     my $attrs = $sth->fetchall_arrayref({});
1007
1008     my @col_arr;
1009     foreach my $ref (@$attrs) {
1010         push @col_arr, $ref->{COLUMN_NAME};
1011     }
1012
1013     $sth->finish();
1014
1015     my $cols = join(', ', @col_arr);
1016     $cols || die "unable to fetch column definitions of table '$table' : ERROR";
1017
1018     $rdb->do("COPY $table ($cols) TO STDOUT");
1019
1020     my $data = '';
1021
1022     eval {
1023         $ldb->do("COPY $table ($cols) FROM stdin");
1024
1025         while ($rdb->pg_getcopydata($data) >= 0) {
1026             $ldb->pg_putcopydata($data);
1027         }
1028
1029         $ldb->pg_putcopyend();
1030     };
1031     if (my $err = $@) {
1032         $ldb->pg_putcopyend();
1033         die $err;
1034     }
1035 }
1036
1037 sub copy_selected_data {
1038     my ($dbh, $select_sth, $table, $attrs, $callback) = @_;
1039
1040     my $count = 0;
1041
1042     my $insert_sth = $dbh->prepare(
1043         "INSERT INTO ${table}(" . join(',', @$attrs) . ') ' .
1044         'VALUES (' . join(',', ('?') x scalar(@$attrs)) . ')');
1045
1046     while (my $ref = $select_sth->fetchrow_hashref()) {
1047         $callback->($ref) if $callback;
1048         $count++;
1049         $insert_sth->execute(map { $ref->{$_} } @$attrs);
1050     }
1051
1052     return $count;
1053 }
1054
1055 sub update_master_clusterinfo {
1056     my ($clientcid) = @_;
1057
1058     my $dbh = open_ruledb();
1059
1060     $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
1061
1062     my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1063
1064     foreach my $table (@mt) {
1065         $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
1066                   "EXTRACT(EPOCH FROM now())");
1067     }
1068 }
1069
1070 sub update_client_clusterinfo {
1071     my ($mastercid) = @_;
1072
1073     my $dbh = open_ruledb();
1074
1075     $dbh->do ("DELETE FROM StatInfo"); # not needed at node
1076
1077     $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
1078
1079     $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
1080               "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
1081
1082     $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
1083               "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
1084
1085     my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1086
1087     foreach my $table (@mt) {
1088         $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
1089                   "COALESCE (max (mtime), 0) FROM $table");
1090     }
1091 }
1092
1093 sub create_clusterinfo_default {
1094     my ($dbh, $rcid, $name, $ivalue, $svalue) = @_;
1095
1096     my $sth = $dbh->prepare("SELECT * FROM ClusterInfo WHERE CID = ? AND Name = ?");
1097     $sth->execute($rcid, $name);
1098     if (!$sth->fetchrow_hashref()) {
1099         $dbh->do("INSERT INTO ClusterInfo (CID, Name, IValue, SValue) " .
1100                  "VALUES (?, ?, ?, ?)", undef,
1101                  $rcid, $name, $ivalue, $svalue);
1102     }
1103     $sth->finish();
1104 }
1105
1106 sub read_int_clusterinfo {
1107     my ($dbh, $rcid, $name) = @_;
1108
1109     my $sth = $dbh->prepare(
1110         "SELECT ivalue as value FROM ClusterInfo " .
1111         "WHERE cid = ? AND NAME = ?");
1112     $sth->execute($rcid, $name);
1113     my $cinfo = $sth->fetchrow_hashref();
1114     $sth->finish();
1115
1116     return $cinfo->{value};
1117 }
1118
1119 sub write_maxint_clusterinfo {
1120     my ($dbh, $rcid, $name, $value) = @_;
1121
1122     $dbh->do("UPDATE ClusterInfo SET ivalue = GREATEST(ivalue, ?) " .
1123              "WHERE cid = ? AND name = ?", undef,
1124              $value, $rcid, $name);
1125 }
1126
1127 sub init_nodedb {
1128     my ($cinfo) = @_;
1129
1130     my $ni = $cinfo->{master};
1131
1132     die "no master defined - unable to sync data from master\n" if !$ni;
1133
1134     my $master_ip = $ni->{ip};
1135     my $master_cid = $ni->{cid};
1136     my $master_name = $ni->{name};
1137
1138     my $fn = "/tmp/masterdb$$.tar";
1139     unlink $fn;
1140
1141     my $dbname = $default_db_name;
1142
1143     eval {
1144         print STDERR "copying master database from '${master_ip}'\n";
1145
1146         open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
1147
1148         my $cmd = ['/usr/bin/ssh', '-o', 'BatchMode=yes',
1149                    '-o', "HostKeyAlias=${master_name}", $master_ip,
1150                    'pg_dump', $dbname, '-F', 'c' ];
1151
1152         PVE::Tools::run_command($cmd, output => '>&' . fileno($fh));
1153
1154         close($fh);
1155
1156         my $size = -s $fn;
1157
1158         print STDERR "copying master database finished (got $size bytes)\n";
1159
1160         print STDERR "delete local database\n";
1161
1162         postgres_admin_cmd('dropdb', undef, $dbname , '--if-exists');
1163
1164         print STDERR "create new local database\n";
1165
1166         $createdb->($dbname);
1167
1168         print STDERR "insert received data into local database\n";
1169
1170         my $mess;
1171         my $parser = sub {
1172             my $line = shift;
1173
1174             if ($line =~ m/restoring data for table \"(.+)\"/) {
1175                 print STDERR "restoring table $1\n";
1176             } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
1177                 $mess = "creating indexes";
1178                 print STDERR "$mess\n";
1179             }
1180         };
1181
1182         my $opts = {
1183             outfunc => $parser,
1184             errfunc => $parser,
1185             errmsg => "pg_restore failed"
1186         };
1187
1188         postgres_admin_cmd('pg_restore', $opts, '-d', $dbname, '-v', $fn);
1189
1190         print STDERR "run analyze to speed up database queries\n";
1191
1192         postgres_admin_cmd('psql', { input => 'analyze;' }, $dbname);
1193
1194         update_client_clusterinfo($master_cid);
1195     };
1196
1197     my $err = $@;
1198
1199     unlink $fn;
1200
1201     die $err if $err;
1202 }
1203
1204 sub cluster_sync_status {
1205     my ($cinfo) = @_;
1206
1207     my $dbh;
1208
1209     my $minmtime;
1210
1211     foreach my $ni (values %{$cinfo->{ids}}) {
1212         next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID
1213         $minmtime->{$ni->{cid}} = 0;
1214     }
1215
1216     eval {
1217         $dbh = open_ruledb();
1218
1219         my $sth = $dbh->prepare(
1220             "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " .
1221             "WHERE name = 'lastsync' AND ivalue > 0 " .
1222             "GROUP BY cid");
1223
1224         $sth->execute();
1225
1226         while (my $info = $sth->fetchrow_hashref()) {
1227             foreach my $ni (values %{$cinfo->{ids}}) {
1228                 next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID
1229                 if ($ni->{cid} == $info->{cid}) { # node exists
1230                     $minmtime->{$ni->{cid}} = $info->{minmtime};
1231                 }
1232             }
1233         }
1234
1235         $sth->finish();
1236     };
1237     my $err = $@;
1238
1239     $dbh->disconnect() if $dbh;
1240
1241     syslog('err', $err) if $err;
1242
1243     return $minmtime;
1244 }
1245
1246 sub load_mail_data {
1247     my ($dbh, $cid, $rid, $ticketid) = @_;
1248
1249     my $sth = $dbh->prepare(
1250         "SELECT * FROM CMailStore, CMSReceivers WHERE " .
1251         "CID = ? AND RID = ? AND TicketID = ? AND " .
1252         "CID = CMailStore_CID AND RID = CMailStore_RID");
1253     $sth->execute($cid, $rid, $ticketid);
1254
1255     my $res = $sth->fetchrow_hashref();
1256
1257     $sth->finish();
1258
1259     die "no such mail (C${cid}R${rid}T${ticketid})\n" if !defined($res);
1260
1261     return $res;
1262 }
1263
1264 sub reload_ruledb {
1265     my ($ruledb) = @_;
1266
1267     # Note: we pass $ruledb when modifying SMTP whitelist
1268     if (defined($ruledb)) {
1269         eval {
1270             my $rulecache = PMG::RuleCache->new($ruledb);
1271             PMG::Config::rewrite_postfix_whitelist($rulecache);
1272         };
1273         if (my $err = $@) {
1274             warn "problems updating SMTP whitelist - $err";
1275         }
1276     }
1277
1278     PMG::Utils::reload_smtp_filter();
1279 }
1280
1281 1;