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