src/PMG/DBTools.pm

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