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