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