src/PMG/DBTools.pm

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