PMG/DBTools.pm

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