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