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