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