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