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::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
  84     my $save_uid = POSIX::getuid();
  85     my $pg_uid = getpwnam('postgres') || die "getpwnam postgres failed\n";
  86
  87     PVE::Tools::setresuid(-1, $pg_uid, -1) ||
  88         die "setresuid postgres ($pg_uid) failed - $!\n";
  89
  90     PVE::Tools::run_command([@$cmd, '-U', 'postgres', @params], %$options);
  91
  92     PVE::Tools::setresuid(-1, $save_uid, -1) ||
  93         die "setresuid back failed - $!\n";
  94 }
  95
  96 sub delete_ruledb {
  97     my ($dbname) = @_;
  98
  99     postgres_admin_cmd('dropdb', undef, $dbname);
 100 }
 101
 102 sub database_list {
 103
 104     my $database_list = {};
 105
 106     my $parser = sub {
 107         my $line = shift;
 108
 109         my ($name, $owner) = map { PVE::Tools::trim($_) } split(/\|/, $line);
 110         return if !$name || !$owner;
 111
 112         $database_list->{$name} = { owner => $owner };
 113     };
 114
 115     postgres_admin_cmd('psql', { outfunc => $parser }, '--list', '--quiet', '--tuples-only');
 116
 117     return $database_list;
 118 }
 119
 120 my $cgreylist_ctablecmd =  <<__EOD;
 121     CREATE TABLE CGreylist
 122     (IPNet VARCHAR(16) NOT NULL,
 123      Host INTEGER NOT NULL,
 124      Sender VARCHAR(255) NOT NULL,
 125      Receiver VARCHAR(255) NOT NULL,
 126      Instance VARCHAR(255),
 127      RCTime INTEGER NOT NULL,
 128      ExTime INTEGER NOT NULL,
 129      Delay INTEGER NOT NULL DEFAULT 0,
 130      Blocked INTEGER NOT NULL,
 131      Passed INTEGER NOT NULL,
 132      CID INTEGER NOT NULL,
 133      MTime INTEGER NOT NULL,
 134      PRIMARY KEY (IPNet, Sender, Receiver));
 135
 136     CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender);
 137
 138     CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime);
 139
 140     CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime);
 141 __EOD
 142
 143 my $clusterinfo_ctablecmd =  <<__EOD;
 144     CREATE TABLE ClusterInfo
 145     (CID INTEGER NOT NULL,
 146      Name VARCHAR NOT NULL,
 147      IValue INTEGER,
 148      SValue VARCHAR,
 149      PRIMARY KEY (CID, Name))
 150 __EOD
 151
 152 my $local_stat_ctablecmd =  <<__EOD;
 153     CREATE TABLE LocalStat
 154     (Time INTEGER NOT NULL,
 155      RBLCount INTEGER DEFAULT 0 NOT NULL,
 156      PregreetCount INTEGER DEFAULT 0 NOT NULL,
 157      CID INTEGER NOT NULL,
 158      MTime INTEGER NOT NULL,
 159      PRIMARY KEY (Time, CID));
 160
 161     CREATE INDEX LocalStat_MTime_Index ON LocalStat (MTime);
 162 __EOD
 163
 164
 165 my $daily_stat_ctablecmd =  <<__EOD;
 166     CREATE TABLE DailyStat
 167     (Time INTEGER NOT NULL UNIQUE,
 168      CountIn INTEGER NOT NULL,
 169      CountOut INTEGER NOT NULL,
 170      BytesIn REAL NOT NULL,
 171      BytesOut REAL NOT NULL,
 172      VirusIn INTEGER NOT NULL,
 173      VirusOut INTEGER NOT NULL,
 174      SpamIn INTEGER NOT NULL,
 175      SpamOut INTEGER NOT NULL,
 176      BouncesIn INTEGER NOT NULL,
 177      BouncesOut INTEGER NOT NULL,
 178      GreylistCount INTEGER NOT NULL,
 179      SPFCount INTEGER NOT NULL,
 180      PTimeSum REAL NOT NULL,
 181      MTime INTEGER NOT NULL,
 182      RBLCount INTEGER DEFAULT 0 NOT NULL,
 183      PRIMARY KEY (Time));
 184
 185     CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
 186
 187 __EOD
 188
 189 my $domain_stat_ctablecmd =  <<__EOD;
 190     CREATE TABLE DomainStat
 191     (Time INTEGER NOT NULL,
 192      Domain VARCHAR(255) NOT NULL,
 193      CountIn INTEGER NOT NULL,
 194      CountOut INTEGER NOT NULL,
 195      BytesIn REAL NOT NULL,
 196      BytesOut REAL NOT NULL,
 197      VirusIn INTEGER NOT NULL,
 198      VirusOut INTEGER NOT NULL,
 199      SpamIn INTEGER NOT NULL,
 200      SpamOut INTEGER NOT NULL,
 201      BouncesIn INTEGER NOT NULL,
 202      BouncesOut INTEGER NOT NULL,
 203      PTimeSum REAL NOT NULL,
 204      MTime INTEGER NOT NULL,
 205      PRIMARY KEY (Time, Domain));
 206
 207     CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
 208 __EOD
 209
 210 my $statinfo_ctablecmd =  <<__EOD;
 211     CREATE TABLE StatInfo
 212     (Name VARCHAR(255) NOT NULL UNIQUE,
 213      IValue INTEGER,
 214      SValue VARCHAR(255),
 215      PRIMARY KEY (Name))
 216 __EOD
 217
 218 my $virusinfo_stat_ctablecmd = <<__EOD;
 219     CREATE TABLE VirusInfo
 220     (Time INTEGER NOT NULL,
 221      Name VARCHAR NOT NULL,
 222      Count INTEGER NOT NULL,
 223      MTime INTEGER NOT NULL,
 224      PRIMARY KEY (Time, Name));
 225
 226     CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
 227
 228 __EOD
 229
 230 # mail storage table
 231 # QTypes
 232 # V - Virus quarantine
 233 # S - Spam quarantine
 234 # D - Delayed Mails - not implemented
 235 # A - Held for Audit - not implemented
 236 # Status
 237 # N - new
 238 # D - deleted
 239
 240 my $cmailstore_ctablecmd =  <<__EOD;
 241     CREATE TABLE CMailStore
 242     (CID INTEGER DEFAULT 0 NOT NULL,
 243      RID INTEGER NOT NULL,
 244      ID SERIAL UNIQUE,
 245      Time INTEGER NOT NULL,
 246      QType "char" NOT NULL,
 247      Bytes INTEGER NOT NULL,
 248      Spamlevel INTEGER NOT NULL,
 249      Info VARCHAR NULL,
 250      Sender VARCHAR(255) NOT NULL,
 251      Header VARCHAR NOT NULL,
 252      File VARCHAR(255) NOT NULL,
 253      PRIMARY KEY (CID, RID));
 254     CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
 255
 256     CREATE TABLE CMSReceivers
 257     (CMailStore_CID INTEGER NOT NULL,
 258      CMailStore_RID INTEGER NOT NULL,
 259      PMail VARCHAR(255) NOT NULL,
 260      Receiver VARCHAR(255),
 261      TicketID INTEGER NOT NULL,
 262      Status "char" NOT NULL,
 263      MTime INTEGER NOT NULL);
 264
 265     CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
 266
 267     CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
 268
 269 __EOD
 270
 271 my $cstatistic_ctablecmd =  <<__EOD;
 272     CREATE TABLE CStatistic
 273     (CID INTEGER DEFAULT 0 NOT NULL,
 274      RID INTEGER NOT NULL,
 275      ID SERIAL UNIQUE,
 276      Time INTEGER NOT NULL,
 277      Bytes INTEGER NOT NULL,
 278      Direction Boolean NOT NULL,
 279      Spamlevel INTEGER NOT NULL,
 280      VirusInfo VARCHAR(255) NULL,
 281      PTime INTEGER NOT NULL,
 282      Sender VARCHAR(255) NOT NULL,
 283      PRIMARY KEY (CID, RID));
 284
 285     CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
 286
 287     CREATE TABLE CReceivers
 288     (CStatistic_CID INTEGER NOT NULL,
 289      CStatistic_RID INTEGER NOT NULL,
 290      Receiver VARCHAR(255) NOT NULL,
 291      Blocked Boolean NOT NULL);
 292
 293     CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
 294 __EOD
 295
 296 # user preferences (black an whitelists, ...)
 297 # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
 298 # Data: arbitrary data
 299 my $userprefs_ctablecmd =  <<__EOD;
 300     CREATE TABLE UserPrefs
 301     (PMail VARCHAR,
 302      Name VARCHAR(255),
 303      Data VARCHAR,
 304      MTime INTEGER NOT NULL,
 305      PRIMARY KEY (PMail, Name));
 306
 307     CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
 308
 309 __EOD
 310
 311 sub cond_create_dbtable {
 312     my ($dbh, $name, $ctablecmd) = @_;
 313
 314     eval {
 315         $dbh->begin_work;
 316
 317         my $cmd = "SELECT tablename FROM pg_tables " .
 318             "WHERE tablename = lower ('$name')";
 319
 320         my $sth = $dbh->prepare($cmd);
 321
 322         $sth->execute();
 323
 324         if (!(my $ref = $sth->fetchrow_hashref())) {
 325             $dbh->do ($ctablecmd);
 326         }
 327
 328         $sth->finish();
 329
 330         $dbh->commit;
 331     };
 332     if (my $err = $@) {
 333         $dbh->rollback;
 334         die $err;
 335     }
 336 }
 337
 338 sub database_column_exists {
 339     my ($dbh, $table, $column) = @_;
 340
 341     my $sth = $dbh->prepare(
 342         "SELECT column_name FROM information_schema.columns " .
 343         "WHERE table_name = ? and column_name = ?");
 344     $sth->execute(lc($table), lc($column));
 345     my $res = $sth->fetchrow_hashref();
 346     return defined($res);
 347 }
 348
 349 my $createdb = sub {
 350     my ($dbname) = @_;
 351     postgres_admin_cmd(
 352         'createdb',
 353         undef,
 354         '-E', 'sql_ascii',
 355         '-T', 'template0',
 356         '--lc-collate=C',
 357         '--lc-ctype=C',
 358         $dbname,
 359     );
 360 };
 361
 362 sub create_ruledb {
 363     my ($dbname) = @_;
 364
 365     $dbname = $default_db_name if !$dbname;
 366
 367     my $silent_opts = { outfunc => sub {}, errfunc => sub {} };
 368     # make sure we have user 'root'
 369     eval { postgres_admin_cmd('createuser',  $silent_opts, '-D', 'root'); };
 370     # also create 'www-data' (and give it read-only access below)
 371     eval { postgres_admin_cmd('createuser',  $silent_opts, '-I', '-D', 'www-data'); };
 372
 373     # use sql_ascii to avoid any character set conversions, and be compatible with
 374     # older postgres versions (update from 8.1 must be possible)
 375
 376     $createdb->($dbname);
 377
 378     my $dbh = open_ruledb($dbname);
 379
 380     # make sure 'www-data' can read all tables
 381     $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\"");
 382
 383     $dbh->do (
 384 <<EOD
 385               CREATE TABLE Attribut
 386               (Object_ID INTEGER NOT NULL,
 387                Name VARCHAR(20) NOT NULL,
 388                Value BYTEA NULL,
 389                PRIMARY KEY (Object_ID, Name));
 390
 391               CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
 392
 393               CREATE TABLE Object
 394               (ID SERIAL UNIQUE,
 395                ObjectType INTEGER NOT NULL,
 396                Objectgroup_ID INTEGER NOT NULL,
 397                Value BYTEA NULL,
 398                PRIMARY KEY (ID));
 399
 400               CREATE TABLE Objectgroup
 401               (ID SERIAL UNIQUE,
 402                Name VARCHAR(255) NOT NULL,
 403                Info VARCHAR(255) NULL,
 404                Class  VARCHAR(10) NOT NULL,
 405                PRIMARY KEY (ID));
 406
 407               CREATE TABLE Rule
 408               (ID SERIAL UNIQUE,
 409                Name VARCHAR(255) NULL,
 410                Priority INTEGER NOT NULL,
 411                Active INTEGER NOT NULL DEFAULT 0,
 412                Direction INTEGER NOT NULL DEFAULT 2,
 413                Count INTEGER NOT NULL DEFAULT 0,
 414                PRIMARY KEY (ID));
 415
 416               CREATE TABLE RuleGroup
 417               (Objectgroup_ID INTEGER NOT NULL,
 418                Rule_ID INTEGER NOT NULL,
 419                Grouptype INTEGER NOT NULL,
 420                PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
 421
 422               $cgreylist_ctablecmd;
 423
 424               $clusterinfo_ctablecmd;
 425
 426               $local_stat_ctablecmd;
 427
 428               $daily_stat_ctablecmd;
 429
 430               $domain_stat_ctablecmd;
 431
 432               $statinfo_ctablecmd;
 433
 434               $cmailstore_ctablecmd;
 435
 436               $cstatistic_ctablecmd;
 437
 438               $userprefs_ctablecmd;
 439
 440               $virusinfo_stat_ctablecmd;
 441 EOD
 442               );
 443
 444     return $dbh;
 445 }
 446
 447 sub cond_create_action_quarantine {
 448     my ($ruledb) = @_;
 449
 450     my $dbh = $ruledb->{dbh};
 451
 452     eval {
 453         my $sth = $dbh->prepare(
 454             "SELECT * FROM Objectgroup, Object " .
 455             "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " .
 456             "AND Object.objectgroup_id = Objectgroup.id");
 457
 458         my $otype = PMG::RuleDB::Quarantine::otype();
 459         if ($sth->execute($otype, 'action') <= 0) {
 460             my $obj = PMG::RuleDB::Quarantine->new ();
 461             my $txt = decode_entities(PMG::RuleDB::Quarantine->otype_text);
 462             my $quarantine = $ruledb->create_group_with_obj
 463                 ($obj, $txt, 'Move to quarantine.');
 464         }
 465     };
 466 }
 467
 468 sub cond_create_std_actions {
 469     my ($ruledb) = @_;
 470
 471     cond_create_action_quarantine($ruledb);
 472
 473     #cond_create_action_report_spam($ruledb);
 474 }
 475
 476
 477 sub upgradedb {
 478     my ($ruledb) = @_;
 479
 480     my $dbh = $ruledb->{dbh};
 481
 482     # make sure we do not use slow sequential scans when upgraing
 483     # database (before analyze can gather statistics)
 484     $dbh->do("set enable_seqscan = false");
 485
 486     my $tables = {
 487         'LocalStat', $local_stat_ctablecmd,
 488         'DailyStat', $daily_stat_ctablecmd,
 489         'DomainStat', $domain_stat_ctablecmd,
 490         'StatInfo', $statinfo_ctablecmd,
 491         'CMailStore', $cmailstore_ctablecmd,
 492         'UserPrefs', $userprefs_ctablecmd,
 493         'CGreylist', $cgreylist_ctablecmd,
 494         'CStatistic', $cstatistic_ctablecmd,
 495         'ClusterInfo', $clusterinfo_ctablecmd,
 496         'VirusInfo', $virusinfo_stat_ctablecmd,
 497     };
 498
 499     foreach my $table (keys %$tables) {
 500         cond_create_dbtable($dbh, $table, $tables->{$table});
 501     }
 502
 503     cond_create_std_actions($ruledb);
 504
 505     # upgrade tables here if necessary
 506     if (!database_column_exists($dbh, 'LocalStat', 'PregreetCount')) {
 507         $dbh->do("ALTER TABLE LocalStat ADD COLUMN " .
 508                  "PregreetCount INTEGER DEFAULT 0 NOT NULL");
 509     }
 510
 511     eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); };
 512     # ignore errors here
 513
 514
 515     # add missing TicketID to CMSReceivers
 516     if (!database_column_exists($dbh, 'CMSReceivers', 'TicketID')) {
 517         eval {
 518             $dbh->begin_work;
 519             $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq");
 520             $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " .
 521                      "TicketID INTEGER NOT NULL " .
 522                      "DEFAULT nextval('cmsreceivers_ticketid_seq')");
 523             $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " .
 524                      "TicketID DROP DEFAULT");
 525             $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq");
 526             $dbh->commit;
 527         };
 528         if (my $err = $@) {
 529             $dbh->rollback;
 530             die $err;
 531         }
 532     }
 533
 534     # update obsolete content type names
 535     eval {
 536         $dbh->do("UPDATE Object " .
 537                  "SET value = 'content-type:application/java-vm' ".
 538                  "WHERE objecttype = 3003 " .
 539                  "AND value = 'content-type:application/x-java-vm';");
 540     };
 541
 542     foreach my $table (keys %$tables) {
 543         eval { $dbh->do("ANALYZE $table"); };
 544         warn $@ if $@;
 545     }
 546
 547     reload_ruledb();
 548 }
 549
 550 sub init_ruledb {
 551     my ($ruledb, $reset, $testmode) = @_;
 552
 553     my $dbh = $ruledb->{dbh};
 554
 555     if (!$reset) {
 556         # Greylist Objectgroup
 557         my $greylistgroup = PMG::RuleDB::Group->new
 558             ("GreyExclusion", "-", "greylist");
 559         $ruledb->save_group ($greylistgroup);
 560
 561     } else {
 562         # we do not touch greylist objects
 563         my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
 564             "objectgroup_id = objectgroup.id and class = 'greylist'";
 565
 566         $dbh->do ("DELETE FROM Rule; " .
 567                   "DELETE FROM RuleGroup; " .
 568                   "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
 569                   "DELETE FROM Object WHERE ID NOT IN ($glids); " .
 570                   "DELETE FROM Objectgroup WHERE class != 'greylist';");
 571     }
 572
 573     # WHO Objects
 574
 575      # Blacklist
 576     my $obj =  PMG::RuleDB::EMail->new ('nomail@fromthisdomain.com');
 577     my $blacklist = $ruledb->create_group_with_obj(
 578         $obj, 'Blacklist', 'Global blacklist');
 579
 580     # Whitelist
 581     $obj = PMG::RuleDB::EMail->new('mail@fromthisdomain.com');
 582     my $whitelist = $ruledb->create_group_with_obj(
 583         $obj, 'Whitelist', 'Global whitelist');
 584
 585     # WHEN Objects
 586
 587     # Working hours
 588     $obj = PMG::RuleDB::TimeFrame->new(8*60, 16*60);
 589     my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
 590                                                       'Usual office hours');
 591
 592     # WHAT Objects
 593
 594     # Images
 595     $obj = PMG::RuleDB::ContentTypeFilter->new('image/.*');
 596     my $img_content = $ruledb->create_group_with_obj(
 597         $obj, 'Images', 'All kinds of graphic files');
 598
 599     # Multimedia
 600     $obj = PMG::RuleDB::ContentTypeFilter->new('audio/.*');
 601     my $mm_content = $ruledb->create_group_with_obj(
 602         $obj, 'Multimedia', 'Audio and Video');
 603
 604     $obj = PMG::RuleDB::ContentTypeFilter->new('video/.*');
 605     $ruledb->group_add_object($mm_content, $obj);
 606
 607     # Office Files
 608     $obj = PMG::RuleDB::ContentTypeFilter->new('application/vnd\.ms-excel');
 609     my $office_content = $ruledb->create_group_with_obj(
 610         $obj, 'Office Files', 'Common Office Files');
 611
 612     $obj = PMG::RuleDB::ContentTypeFilter->new(
 613         'application/vnd\.ms-powerpoint');
 614
 615     $ruledb->group_add_object($office_content, $obj);
 616
 617     $obj = PMG::RuleDB::ContentTypeFilter->new('application/msword');
 618     $ruledb->group_add_object ($office_content, $obj);
 619
 620     $obj = PMG::RuleDB::ContentTypeFilter->new(
 621         'application/vnd\.openxmlformats-officedocument\..*');
 622     $ruledb->group_add_object($office_content, $obj);
 623
 624     $obj = PMG::RuleDB::ContentTypeFilter->new(
 625         'application/vnd\.oasis\.opendocument\..*');
 626     $ruledb->group_add_object($office_content, $obj);
 627
 628     $obj = PMG::RuleDB::ContentTypeFilter->new(
 629         'application/vnd\.stardivision\..*');
 630     $ruledb->group_add_object($office_content, $obj);
 631
 632     $obj = PMG::RuleDB::ContentTypeFilter->new(
 633         'application/vnd\.sun\.xml\..*');
 634     $ruledb->group_add_object($office_content, $obj);
 635
 636     # Dangerous Content
 637     $obj = PMG::RuleDB::ContentTypeFilter->new(
 638         'application/x-ms-dos-executable');
 639     my $exe_content = $ruledb->create_group_with_obj(
 640         $obj, 'Dangerous Content', 'executable files and partial messages');
 641
 642     $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-java');
 643     $ruledb->group_add_object($exe_content, $obj);
 644     $obj = PMG::RuleDB::ContentTypeFilter->new('application/javascript');
 645     $ruledb->group_add_object($exe_content, $obj);
 646     $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-executable');
 647     $ruledb->group_add_object($exe_content, $obj);
 648     $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-ms-dos-executable');
 649     $ruledb->group_add_object($exe_content, $obj);
 650     $obj = PMG::RuleDB::ContentTypeFilter->new('message/partial');
 651     $ruledb->group_add_object($exe_content, $obj);
 652     $obj = PMG::RuleDB::MatchFilename->new('.*\.(vbs|pif|lnk|shs|shb)');
 653     $ruledb->group_add_object($exe_content, $obj);
 654     $obj = PMG::RuleDB::MatchFilename->new('.*\.\{.+\}');
 655     $ruledb->group_add_object($exe_content, $obj);
 656
 657     # Virus
 658     $obj = PMG::RuleDB::Virus->new();
 659     my $virus = $ruledb->create_group_with_obj(
 660         $obj, 'Virus', 'Matches virus infected mail');
 661
 662     # WHAT Objects
 663
 664     # Spam
 665     $obj = PMG::RuleDB::Spam->new(3);
 666     my $spam3 = $ruledb->create_group_with_obj(
 667         $obj, 'Spam (Level 3)', 'Matches possible spam mail');
 668
 669     $obj = PMG::RuleDB::Spam->new(5);
 670     my $spam5 = $ruledb->create_group_with_obj(
 671         $obj, 'Spam (Level 5)', 'Matches possible spam mail');
 672
 673     $obj = PMG::RuleDB::Spam->new(10);
 674     my $spam10 = $ruledb->create_group_with_obj(
 675         $obj, 'Spam (Level 10)', 'Matches possible spam mail');
 676
 677     # ACTIONS
 678
 679     # Mark Spam
 680     $obj = PMG::RuleDB::ModField->new('X-SPAM-LEVEL', '__SPAM_INFO__');
 681     my $mod_spam_level = $ruledb->create_group_with_obj(
 682         $obj, 'Modify Spam Level',
 683         'Mark mail as spam by adding a header tag.');
 684
 685     # Mark Spam
 686     $obj = PMG::RuleDB::ModField->new('subject', 'SPAM: __SUBJECT__');
 687     my $mod_spam_subject = $ruledb->create_group_with_obj(
 688         $obj, 'Modify Spam Subject',
 689         'Mark mail as spam by modifying the subject.');
 690
 691     # Remove matching attachments
 692     $obj = PMG::RuleDB::Remove->new(0);
 693     my $remove = $ruledb->create_group_with_obj(
 694         $obj, 'Remove attachments', 'Remove matching attachments');
 695
 696     # Remove all attachments
 697     $obj = PMG::RuleDB::Remove->new(1);
 698     my $remove_all = $ruledb->create_group_with_obj(
 699         $obj, 'Remove all attachments', 'Remove all attachments');
 700
 701     # Accept
 702     $obj = PMG::RuleDB::Accept->new();
 703     my $accept = $ruledb->create_group_with_obj(
 704         $obj, 'Accept', 'Accept mail for Delivery');
 705
 706     # Block
 707     $obj = PMG::RuleDB::Block->new ();
 708     my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
 709
 710     # Quarantine
 711     $obj = PMG::RuleDB::Quarantine->new();
 712     my $quarantine = $ruledb->create_group_with_obj(
 713         $obj, 'Quarantine', 'Move mail to quarantine');
 714
 715     # Notify Admin
 716     $obj = PMG::RuleDB::Notify->new('__ADMIN__');
 717     my $notify_admin = $ruledb->create_group_with_obj(
 718         $obj, 'Notify Admin', 'Send notification');
 719
 720     # Notify Sender
 721     $obj = PMG::RuleDB::Notify->new('__SENDER__');
 722     my $notify_sender = $ruledb->create_group_with_obj(
 723         $obj, 'Notify Sender', 'Send notification');
 724
 725     # Add Disclaimer
 726     $obj = PMG::RuleDB::Disclaimer->new ();
 727     my $add_discl = $ruledb->create_group_with_obj(
 728         $obj, 'Disclaimer', 'Add Disclaimer');
 729
 730     # Attach original mail
 731     #$obj = Proxmox::RuleDB::Attach->new ();
 732     #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
 733     #                                         'Attach Original Mail');
 734
 735     ####################### RULES ##################################
 736
 737     ## Block Dangerous  Files
 738     my $rule = PMG::RuleDB::Rule->new ('Block Dangerous Files', 93, 1, 0);
 739     $ruledb->save_rule ($rule);
 740
 741     $ruledb->rule_add_what_group ($rule, $exe_content);
 742     $ruledb->rule_add_action ($rule, $remove);
 743
 744     ## Block Viruses
 745     $rule = PMG::RuleDB::Rule->new ('Block Viruses', 96, 1, 0);
 746     $ruledb->save_rule ($rule);
 747
 748     $ruledb->rule_add_what_group ($rule, $virus);
 749     $ruledb->rule_add_action ($rule, $notify_admin);
 750
 751     if ($testmode) {
 752         $ruledb->rule_add_action ($rule, $block);
 753     } else {
 754         $ruledb->rule_add_action ($rule, $quarantine);
 755     }
 756
 757     ## Virus Alert
 758     $rule = PMG::RuleDB::Rule->new ('Virus Alert', 96, 1, 1);
 759     $ruledb->save_rule ($rule);
 760
 761     $ruledb->rule_add_what_group ($rule, $virus);
 762     $ruledb->rule_add_action ($rule, $notify_sender);
 763     $ruledb->rule_add_action ($rule, $notify_admin);
 764     $ruledb->rule_add_action ($rule, $block);
 765
 766     ## Blacklist
 767     $rule = PMG::RuleDB::Rule->new ('Blacklist', 98, 1, 0);
 768     $ruledb->save_rule ($rule);
 769
 770     $ruledb->rule_add_from_group ($rule, $blacklist);
 771     $ruledb->rule_add_action ($rule, $block);
 772
 773     ## Modify header
 774     if (!$testmode) {
 775         $rule = PMG::RuleDB::Rule->new ('Modify Header', 90, 1, 0);
 776         $ruledb->save_rule ($rule);
 777         $ruledb->rule_add_action ($rule, $mod_spam_level);
 778     }
 779
 780     ## Whitelist
 781     $rule = PMG::RuleDB::Rule->new ('Whitelist', 85, 1, 0);
 782     $ruledb->save_rule ($rule);
 783
 784     $ruledb->rule_add_from_group ($rule, $whitelist);
 785     $ruledb->rule_add_action ($rule, $accept);
 786
 787     if ($testmode) {
 788         $rule = PMG::RuleDB::Rule->new ('Mark Spam', 80, 1, 0);
 789         $ruledb->save_rule ($rule);
 790
 791         $ruledb->rule_add_what_group ($rule, $spam10);
 792         $ruledb->rule_add_action ($rule, $mod_spam_level);
 793         $ruledb->rule_add_action ($rule, $mod_spam_subject);
 794     } else {
 795         # Quarantine/Mark Spam (Level 3)
 796         $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
 797         $ruledb->save_rule ($rule);
 798
 799         $ruledb->rule_add_what_group ($rule, $spam3);
 800         $ruledb->rule_add_action ($rule, $mod_spam_subject);
 801         $ruledb->rule_add_action ($rule, $quarantine);
 802         #$ruledb->rule_add_action ($rule, $count_spam);
 803     }
 804
 805     # Quarantine/Mark Spam (Level 5)
 806     $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0);
 807     $ruledb->save_rule ($rule);
 808
 809     $ruledb->rule_add_what_group ($rule, $spam5);
 810     $ruledb->rule_add_action ($rule, $mod_spam_subject);
 811     $ruledb->rule_add_action ($rule, $quarantine);
 812
 813     ## Block Spam Level 10
 814     $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 82, 0, 0);
 815     $ruledb->save_rule ($rule);
 816
 817     $ruledb->rule_add_what_group ($rule, $spam10);
 818     $ruledb->rule_add_action ($rule, $block);
 819
 820     ## Block Outgoing Spam
 821     $rule = PMG::RuleDB::Rule->new ('Block outgoing Spam', 70, 0, 1);
 822     $ruledb->save_rule ($rule);
 823
 824     $ruledb->rule_add_what_group ($rule, $spam3);
 825     $ruledb->rule_add_action ($rule, $notify_admin);
 826     $ruledb->rule_add_action ($rule, $notify_sender);
 827     $ruledb->rule_add_action ($rule, $block);
 828
 829     ## Add disclaimer
 830     $rule = PMG::RuleDB::Rule->new ('Add Disclaimer', 60, 0, 1);
 831     $ruledb->save_rule ($rule);
 832     $ruledb->rule_add_action ($rule, $add_discl);
 833
 834     # Block Multimedia Files
 835     $rule = PMG::RuleDB::Rule->new ('Block Multimedia Files', 87, 0, 2);
 836     $ruledb->save_rule ($rule);
 837
 838     $ruledb->rule_add_what_group ($rule, $mm_content);
 839     $ruledb->rule_add_action ($rule, $remove);
 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     my $pid_file = '/var/run/pmg-smtp-filter.pid';
1279     my $pid = PVE::Tools::file_read_firstline($pid_file);
1280
1281     return 0 if !$pid;
1282
1283     return 0 if $pid !~ m/^(\d+)$/;
1284     $pid = $1; # untaint
1285
1286     return kill (10, $pid); # send SIGUSR1
1287 }
1288
1289 1;