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