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 $daily_stat_ctablecmd =  <<__EOD;
 147     CREATE TABLE DailyStat
 148     (Time INTEGER NOT NULL UNIQUE,
 149      CountIn INTEGER NOT NULL,
 150      CountOut INTEGER NOT NULL,
 151      BytesIn REAL NOT NULL,
 152      BytesOut REAL NOT NULL,
 153      VirusIn INTEGER NOT NULL,
 154      VirusOut INTEGER NOT NULL,
 155      SpamIn INTEGER NOT NULL,
 156      SpamOut INTEGER NOT NULL,
 157      BouncesIn INTEGER NOT NULL,
 158      BouncesOut INTEGER NOT NULL,
 159      GreylistCount INTEGER NOT NULL,
 160      SPFCount INTEGER NOT NULL,
 161      PTimeSum REAL NOT NULL,
 162      MTime INTEGER NOT NULL,
 163      RBLCount INTEGER DEFAULT 0 NOT NULL,
 164      PRIMARY KEY (Time));
 165
 166     CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
 167
 168 __EOD
 169
 170 my $domain_stat_ctablecmd =  <<__EOD;
 171     CREATE TABLE DomainStat
 172     (Time INTEGER NOT NULL,
 173      Domain VARCHAR(255) NOT NULL,
 174      CountIn INTEGER NOT NULL,
 175      CountOut INTEGER NOT NULL,
 176      BytesIn REAL NOT NULL,
 177      BytesOut REAL NOT NULL,
 178      VirusIn INTEGER NOT NULL,
 179      VirusOut INTEGER NOT NULL,
 180      SpamIn INTEGER NOT NULL,
 181      SpamOut INTEGER NOT NULL,
 182      BouncesIn INTEGER NOT NULL,
 183      BouncesOut INTEGER NOT NULL,
 184      PTimeSum REAL NOT NULL,
 185      MTime INTEGER NOT NULL,
 186      PRIMARY KEY (Time, Domain));
 187
 188     CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
 189 __EOD
 190
 191 my $statinfo_ctablecmd =  <<__EOD;
 192     CREATE TABLE StatInfo
 193     (Name VARCHAR(255) NOT NULL UNIQUE,
 194      IValue INTEGER,
 195      SValue VARCHAR(255),
 196      PRIMARY KEY (Name))
 197 __EOD
 198
 199 my $virusinfo_stat_ctablecmd = <<__EOD;
 200     CREATE TABLE VirusInfo
 201     (Time INTEGER NOT NULL,
 202      Name VARCHAR NOT NULL,
 203      Count INTEGER NOT NULL,
 204      MTime INTEGER NOT NULL,
 205      PRIMARY KEY (Time, Name));
 206
 207     CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
 208
 209 __EOD
 210
 211 # mail storage stable
 212 # QTypes
 213 # V - Virus quarantine
 214 # S - Spam quarantine
 215 # D - Delayed Mails - not implemented
 216 # A - Held for Audit - not implemented
 217 # Status
 218 # N - new
 219 # D - deleted
 220
 221 my $cmailstore_ctablecmd =  <<__EOD;
 222     CREATE TABLE CMailStore
 223     (CID INTEGER DEFAULT 0 NOT NULL,
 224      RID INTEGER NOT NULL,
 225      ID SERIAL UNIQUE,
 226      Time INTEGER NOT NULL,
 227      QType "char" NOT NULL,
 228      Bytes INTEGER NOT NULL,
 229      Spamlevel INTEGER NOT NULL,
 230      Info VARCHAR NULL,
 231      Sender VARCHAR(255) NOT NULL,
 232      Header VARCHAR NOT NULL,
 233      File VARCHAR(255) NOT NULL,
 234      PRIMARY KEY (CID, RID));
 235     CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
 236
 237     CREATE TABLE CMSReceivers
 238     (CMailStore_CID INTEGER NOT NULL,
 239      CMailStore_RID INTEGER NOT NULL,
 240      PMail VARCHAR(255) NOT NULL,
 241      Receiver VARCHAR(255),
 242      Status "char" NOT NULL,
 243      MTime INTEGER NOT NULL);
 244
 245     CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
 246
 247     CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
 248
 249 __EOD
 250
 251 my $cstatistic_ctablecmd =  <<__EOD;
 252     CREATE TABLE CStatistic
 253     (CID INTEGER DEFAULT 0 NOT NULL,
 254      RID INTEGER NOT NULL,
 255      ID SERIAL UNIQUE,
 256      Time INTEGER NOT NULL,
 257      Bytes INTEGER NOT NULL,
 258      Direction Boolean NOT NULL,
 259      Spamlevel INTEGER NOT NULL,
 260      VirusInfo VARCHAR(255) NULL,
 261      PTime INTEGER NOT NULL,
 262      Sender VARCHAR(255) NOT NULL,
 263      PRIMARY KEY (CID, RID));
 264
 265     CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
 266
 267     CREATE TABLE CReceivers
 268     (CStatistic_CID INTEGER NOT NULL,
 269      CStatistic_RID INTEGER NOT NULL,
 270      Receiver VARCHAR(255) NOT NULL,
 271      Blocked Boolean NOT NULL);
 272
 273     CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
 274 __EOD
 275
 276 # user preferences (black an whitelists, ...)
 277 # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
 278 # Data: arbitrary data
 279 my $userprefs_ctablecmd =  <<__EOD;
 280     CREATE TABLE UserPrefs
 281     (PMail VARCHAR,
 282      Name VARCHAR(255),
 283      Data VARCHAR,
 284      MTime INTEGER NOT NULL,
 285      PRIMARY KEY (PMail, Name));
 286
 287     CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
 288
 289 __EOD
 290
 291 sub cond_create_dbtable {
 292     my ($dbh, $name, $ctablecmd) = @_;
 293
 294     eval {
 295         $dbh->begin_work;
 296
 297         my $cmd = "SELECT tablename FROM pg_tables " .
 298             "WHERE tablename = lower ('$name')";
 299
 300         my $sth = $dbh->prepare ($cmd);
 301
 302         $sth->execute();
 303
 304         if (!(my $ref = $sth->fetchrow_hashref())) {
 305             $dbh->do ($ctablecmd);
 306         }
 307
 308         $sth->finish();
 309
 310         $dbh->commit;
 311     };
 312     if (my $err = $@) {
 313         $dbh->rollback;
 314         die $err;
 315     }
 316 }
 317
 318 sub create_ruledb {
 319     my ($dbname) = @_;
 320
 321     $dbname = $default_db_name if !$dbname;
 322
 323     my $silent_opts = { outfunc => sub {}, errfunc => sub {} };
 324     # make sure we have user 'root'
 325     eval { postgres_admin_cmd('createuser',  $silent_opts, '-D', 'root'); };
 326     # also create 'www-data' (and give it read-only access below)
 327     eval { postgres_admin_cmd('createuser',  $silent_opts, '-I', '-D', 'www-data'); };
 328
 329     # use sql_ascii to avoid any character set conversions, and be compatible with
 330     # older postgres versions (update from 8.1 must be possible)
 331
 332     postgres_admin_cmd('createdb', undef, '-E', 'sql_ascii', '-T', 'template0',
 333                        '--lc-collate=C', '--lc-ctype=C', $dbname);
 334
 335     my $dbh = open_ruledb($dbname);
 336
 337     # make sure 'www-data' can read all tables
 338     $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\"");
 339
 340     $dbh->do (
 341 <<EOD
 342               CREATE TABLE Attribut
 343               (Object_ID INTEGER NOT NULL,
 344                Name VARCHAR(20) NOT NULL,
 345                Value BYTEA NULL,
 346                PRIMARY KEY (Object_ID, Name));
 347
 348               CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
 349
 350               CREATE TABLE Object
 351               (ID SERIAL UNIQUE,
 352                ObjectType INTEGER NOT NULL,
 353                Objectgroup_ID INTEGER NOT NULL,
 354                Value BYTEA NULL,
 355                PRIMARY KEY (ID));
 356
 357               CREATE TABLE Objectgroup
 358               (ID SERIAL UNIQUE,
 359                Name VARCHAR(255) NOT NULL,
 360                Info VARCHAR(255) NULL,
 361                Class  VARCHAR(10) NOT NULL,
 362                PRIMARY KEY (ID));
 363
 364               CREATE TABLE Rule
 365               (ID SERIAL UNIQUE,
 366                Name VARCHAR(255) NULL,
 367                Priority INTEGER NOT NULL,
 368                Active INTEGER NOT NULL DEFAULT 0,
 369                Direction INTEGER NOT NULL DEFAULT 2,
 370                Count INTEGER NOT NULL DEFAULT 0,
 371                PRIMARY KEY (ID));
 372
 373               CREATE TABLE RuleGroup
 374               (Objectgroup_ID INTEGER NOT NULL,
 375                Rule_ID INTEGER NOT NULL,
 376                Grouptype INTEGER NOT NULL,
 377                PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
 378
 379               $cgreylist_ctablecmd;
 380
 381               $clusterinfo_ctablecmd;
 382
 383               $daily_stat_ctablecmd;
 384
 385               $domain_stat_ctablecmd;
 386
 387               $statinfo_ctablecmd;
 388
 389               $cmailstore_ctablecmd;
 390
 391               $cstatistic_ctablecmd;
 392
 393               $userprefs_ctablecmd;
 394
 395               $virusinfo_stat_ctablecmd;
 396 EOD
 397               );
 398
 399     return $dbh;
 400 }
 401
 402 sub cond_create_action_quarantine {
 403     my ($ruledb) = @_;
 404
 405     my $dbh = $ruledb->{dbh};
 406
 407     eval {
 408         my $sth = $dbh->prepare(
 409             "SELECT * FROM Objectgroup, Object " .
 410             "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " .
 411             "AND Object.objectgroup_id = Objectgroup.id");
 412
 413         my $otype = PMG::RuleDB::Quarantine::otype();
 414         if ($sth->execute($otype, 'action') <= 0) {
 415             my $obj = PMG::RuleDB::Quarantine->new ();
 416             my $txt = decode_entities(PMG::RuleDB::Quarantine->otype_text);
 417             my $quarantine = $ruledb->create_group_with_obj
 418                 ($obj, $txt, 'Move to quarantine.');
 419         }
 420     };
 421 }
 422
 423 sub cond_create_std_actions {
 424     my ($ruledb) = @_;
 425
 426     cond_create_action_quarantine($ruledb);
 427
 428     #cond_create_action_report_spam($ruledb);
 429 }
 430
 431
 432 sub upgradedb {
 433     my ($ruledb) = @_;
 434
 435     my $dbh = $ruledb->{dbh};
 436
 437     # make sure we do not use slow sequential scans when upgraing
 438     # database (before analyze can gather statistics)
 439     $dbh->do("set enable_seqscan = false");
 440
 441     my $tables = {
 442         'DailyStat'=> $daily_stat_ctablecmd,
 443         'DomainStat', $domain_stat_ctablecmd,
 444         'StatInfo', $statinfo_ctablecmd,
 445         'CMailStore', $cmailstore_ctablecmd,
 446         'UserPrefs', $userprefs_ctablecmd,
 447         'CGreylist', $cgreylist_ctablecmd,
 448         'CStatistic', $cstatistic_ctablecmd,
 449         'ClusterInfo', $clusterinfo_ctablecmd,
 450         'VirusInfo', $virusinfo_stat_ctablecmd,
 451     };
 452
 453     foreach my $table (keys %$tables) {
 454         cond_create_dbtable($dbh, $table, $tables->{$tables});
 455     }
 456
 457     cond_create_std_actions($ruledb);
 458
 459     # upgrade tables here if necessary
 460
 461     # update obsolete content type names
 462     eval {
 463         $dbh->do("UPDATE Object " .
 464                  "SET value = 'content-type:application/java-vm' ".
 465                  "WHERE objecttype = 3003 " .
 466                  "AND value = 'content-type:application/x-java-vm';");
 467     };
 468
 469     foreach my $table (keys %$tables) {
 470         eval { $dbh->do("ANALYZE $table"); };
 471         warn $@ if $@;
 472     }
 473 }
 474
 475 sub init_ruledb {
 476     my ($ruledb, $reset, $testmode) = @_;
 477
 478     my $dbh = $ruledb->{dbh};
 479
 480     if (!$reset) {
 481         # Greylist Objectgroup
 482         my $greylistgroup = PMG::RuleDB::Group->new
 483             ("GreyExclusion", "-", "greylist");
 484         $ruledb->save_group ($greylistgroup);
 485
 486     } else {
 487         # we do not touch greylist objects
 488         my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
 489             "objectgroup_id = objectgroup.id and class = 'greylist'";
 490
 491         $dbh->do ("DELETE FROM Rule; " .
 492                   "DELETE FROM RuleGroup; " .
 493                   "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
 494                   "DELETE FROM Object WHERE ID NOT IN ($glids); " .
 495                   "DELETE FROM Objectgroup WHERE class != 'greylist';");
 496     }
 497
 498     # WHO Objects
 499
 500      # Blacklist
 501     my $obj =  PMG::RuleDB::EMail->new ('nomail@fromthisdomain.com');
 502     my $blacklist = $ruledb->create_group_with_obj(
 503         $obj, 'Blacklist', 'Global blacklist');
 504
 505     # Whitelist
 506     $obj = PMG::RuleDB::EMail->new('mail@fromthisdomain.com');
 507     my $whitelist = $ruledb->create_group_with_obj(
 508         $obj, 'Whitelist', 'Global whitelist');
 509
 510     # WHEN Objects
 511
 512     # Working hours
 513     $obj = PMG::RuleDB::TimeFrame->new(8*60, 16*60);
 514     my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
 515                                                       'Usual office hours');
 516
 517     # WHAT Objects
 518
 519     # Images
 520     $obj = PMG::RuleDB::ContentTypeFilter->new('image/.*');
 521     my $img_content = $ruledb->create_group_with_obj(
 522         $obj, 'Images', 'All kinds of graphic files');
 523
 524     # Multimedia
 525     $obj = PMG::RuleDB::ContentTypeFilter->new('audio/.*');
 526     my $mm_content = $ruledb->create_group_with_obj(
 527         $obj, 'Multimedia', 'Audio and Video');
 528
 529     $obj = PMG::RuleDB::ContentTypeFilter->new('video/.*');
 530     $ruledb->group_add_object($mm_content, $obj);
 531
 532     # Office Files
 533     $obj = PMG::RuleDB::ContentTypeFilter->new('application/vnd\.ms-excel');
 534     my $office_content = $ruledb->create_group_with_obj(
 535         $obj, 'Office Files', 'Common Office Files');
 536
 537     $obj = PMG::RuleDB::ContentTypeFilter->new(
 538         'application/vnd\.ms-powerpoint');
 539
 540     $ruledb->group_add_object($office_content, $obj);
 541
 542     $obj = PMG::RuleDB::ContentTypeFilter->new('application/msword');
 543     $ruledb->group_add_object ($office_content, $obj);
 544
 545     $obj = PMG::RuleDB::ContentTypeFilter->new(
 546         'application/vnd\.openxmlformats-officedocument\..*');
 547     $ruledb->group_add_object($office_content, $obj);
 548
 549     $obj = PMG::RuleDB::ContentTypeFilter->new(
 550         'application/vnd\.oasis\.opendocument\..*');
 551     $ruledb->group_add_object($office_content, $obj);
 552
 553     $obj = PMG::RuleDB::ContentTypeFilter->new(
 554         'application/vnd\.stardivision\..*');
 555     $ruledb->group_add_object($office_content, $obj);
 556
 557     $obj = PMG::RuleDB::ContentTypeFilter->new(
 558         'application/vnd\.sun\.xml\..*');
 559     $ruledb->group_add_object($office_content, $obj);
 560
 561     # Dangerous Content
 562     $obj = PMG::RuleDB::ContentTypeFilter->new(
 563         'application/x-ms-dos-executable');
 564     my $exe_content = $ruledb->create_group_with_obj(
 565         $obj, 'Dangerous Content', 'executable files and partial messages');
 566
 567     $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-java');
 568     $ruledb->group_add_object($exe_content, $obj);
 569     $obj = PMG::RuleDB::ContentTypeFilter->new('application/javascript');
 570     $ruledb->group_add_object($exe_content, $obj);
 571     $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-executable');
 572     $ruledb->group_add_object($exe_content, $obj);
 573     $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-ms-dos-executable');
 574     $ruledb->group_add_object($exe_content, $obj);
 575     $obj = PMG::RuleDB::ContentTypeFilter->new('message/partial');
 576     $ruledb->group_add_object($exe_content, $obj);
 577     $obj = PMG::RuleDB::MatchFilename->new('.*\.(vbs|pif|lnk|shs|shb)');
 578     $ruledb->group_add_object($exe_content, $obj);
 579     $obj = PMG::RuleDB::MatchFilename->new('.*\.\{.+\}');
 580     $ruledb->group_add_object($exe_content, $obj);
 581
 582     # Virus
 583     $obj = PMG::RuleDB::Virus->new();
 584     my $virus = $ruledb->create_group_with_obj(
 585         $obj, 'Virus', 'Matches virus infected mail');
 586
 587     # WHAT Objects
 588
 589     # Spam
 590     $obj = PMG::RuleDB::Spam->new(3);
 591     my $spam3 = $ruledb->create_group_with_obj(
 592         $obj, 'Spam (Level 3)', 'Matches possible spam mail');
 593
 594     $obj = PMG::RuleDB::Spam->new(5);
 595     my $spam5 = $ruledb->create_group_with_obj(
 596         $obj, 'Spam (Level 5)', 'Matches possible spam mail');
 597
 598     $obj = PMG::RuleDB::Spam->new(10);
 599     my $spam10 = $ruledb->create_group_with_obj(
 600         $obj, 'Spam (Level 10)', 'Matches possible spam mail');
 601
 602     # ACTIONS
 603
 604     # Mark Spam
 605     $obj = PMG::RuleDB::ModField->new('X-SPAM-LEVEL', '__SPAM_INFO__');
 606     my $mod_spam_level = $ruledb->create_group_with_obj(
 607         $obj, 'Modify Spam Level',
 608         'Mark mail as spam by adding a header tag.');
 609
 610     # Mark Spam
 611     $obj = PMG::RuleDB::ModField->new('subject', 'SPAM: __SUBJECT__');
 612     my $mod_spam_subject = $ruledb->create_group_with_obj(
 613         $obj, 'Modify Spam Subject',
 614         'Mark mail as spam by modifying the subject.');
 615
 616     # Remove matching attachments
 617     $obj = PMG::RuleDB::Remove->new(0);
 618     my $remove = $ruledb->create_group_with_obj(
 619         $obj, 'Remove attachments', 'Remove matching attachments');
 620
 621     # Remove all attachments
 622     $obj = PMG::RuleDB::Remove->new(1);
 623     my $remove_all = $ruledb->create_group_with_obj(
 624         $obj, 'Remove all attachments', 'Remove all attachments');
 625
 626     # Accept
 627     $obj = PMG::RuleDB::Accept->new();
 628     my $accept = $ruledb->create_group_with_obj(
 629         $obj, 'Accept', 'Accept mail for Delivery');
 630
 631     # Block
 632     $obj = PMG::RuleDB::Block->new ();
 633     my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
 634
 635     # Quarantine
 636     $obj = PMG::RuleDB::Quarantine->new();
 637     my $quarantine = $ruledb->create_group_with_obj(
 638         $obj, 'Quarantine', 'Move mail to quarantine');
 639
 640     # Notify Admin
 641     $obj = PMG::RuleDB::Notify->new('__ADMIN__');
 642     my $notify_admin = $ruledb->create_group_with_obj(
 643         $obj, 'Notify Admin', 'Send notification');
 644
 645     # Notify Sender
 646     $obj = PMG::RuleDB::Notify->new('__SENDER__');
 647     my $notify_sender = $ruledb->create_group_with_obj(
 648         $obj, 'Notify Sender', 'Send notification');
 649
 650     # Add Disclaimer
 651     $obj = PMG::RuleDB::Disclaimer->new ();
 652     my $add_discl = $ruledb->create_group_with_obj(
 653         $obj, 'Disclaimer', 'Add Disclaimer');
 654
 655     # Attach original mail
 656     #$obj = Proxmox::RuleDB::Attach->new ();
 657     #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
 658     #                                         'Attach Original Mail');
 659
 660     ####################### RULES ##################################
 661
 662     ## Block Dangerous  Files
 663     my $rule = PMG::RuleDB::Rule->new ('Block Dangerous Files', 93, 1, 0);
 664     $ruledb->save_rule ($rule);
 665
 666     $ruledb->rule_add_what_group ($rule, $exe_content);
 667     $ruledb->rule_add_action ($rule, $remove);
 668
 669     ## Block Viruses
 670     $rule = PMG::RuleDB::Rule->new ('Block Viruses', 96, 1, 0);
 671     $ruledb->save_rule ($rule);
 672
 673     $ruledb->rule_add_what_group ($rule, $virus);
 674     $ruledb->rule_add_action ($rule, $notify_admin);
 675
 676     if ($testmode) {
 677         $ruledb->rule_add_action ($rule, $block);
 678     } else {
 679         $ruledb->rule_add_action ($rule, $quarantine);
 680     }
 681
 682     ## Virus Alert
 683     $rule = PMG::RuleDB::Rule->new ('Virus Alert', 96, 1, 1);
 684     $ruledb->save_rule ($rule);
 685
 686     $ruledb->rule_add_what_group ($rule, $virus);
 687     $ruledb->rule_add_action ($rule, $notify_sender);
 688     $ruledb->rule_add_action ($rule, $notify_admin);
 689     $ruledb->rule_add_action ($rule, $block);
 690
 691     ## Blacklist
 692     $rule = PMG::RuleDB::Rule->new ('Blacklist', 98, 1, 0);
 693     $ruledb->save_rule ($rule);
 694
 695     $ruledb->rule_add_from_group ($rule, $blacklist);
 696     $ruledb->rule_add_action ($rule, $block);
 697
 698     ## Modify header
 699     if (!$testmode) {
 700         $rule = PMG::RuleDB::Rule->new ('Modify Header', 90, 1, 0);
 701         $ruledb->save_rule ($rule);
 702         $ruledb->rule_add_action ($rule, $mod_spam_level);
 703     }
 704
 705     ## Whitelist
 706     $rule = PMG::RuleDB::Rule->new ('Whitelist', 85, 1, 0);
 707     $ruledb->save_rule ($rule);
 708
 709     $ruledb->rule_add_from_group ($rule, $whitelist);
 710     $ruledb->rule_add_action ($rule, $accept);
 711
 712     if ($testmode) {
 713         $rule = PMG::RuleDB::Rule->new ('Mark Spam', 80, 1, 0);
 714         $ruledb->save_rule ($rule);
 715
 716         $ruledb->rule_add_what_group ($rule, $spam10);
 717         $ruledb->rule_add_action ($rule, $mod_spam_level);
 718         $ruledb->rule_add_action ($rule, $mod_spam_subject);
 719     } else {
 720         # Quarantine/Mark Spam (Level 3)
 721         $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
 722         $ruledb->save_rule ($rule);
 723
 724         $ruledb->rule_add_what_group ($rule, $spam3);
 725         $ruledb->rule_add_action ($rule, $mod_spam_subject);
 726         $ruledb->rule_add_action ($rule, $quarantine);
 727         #$ruledb->rule_add_action ($rule, $count_spam);
 728     }
 729
 730     # Quarantine/Mark Spam (Level 5)
 731     $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 79, 0, 0);
 732     $ruledb->save_rule ($rule);
 733
 734     $ruledb->rule_add_what_group ($rule, $spam5);
 735     $ruledb->rule_add_action ($rule, $mod_spam_subject);
 736     $ruledb->rule_add_action ($rule, $quarantine);
 737
 738     ## Block Spam Level 10
 739     $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 78, 0, 0);
 740     $ruledb->save_rule ($rule);
 741
 742     $ruledb->rule_add_what_group ($rule, $spam10);
 743     $ruledb->rule_add_action ($rule, $block);
 744
 745     ## Block Outgoing Spam
 746     $rule = PMG::RuleDB::Rule->new ('Block outgoing Spam', 70, 0, 1);
 747     $ruledb->save_rule ($rule);
 748
 749     $ruledb->rule_add_what_group ($rule, $spam3);
 750     $ruledb->rule_add_action ($rule, $notify_admin);
 751     $ruledb->rule_add_action ($rule, $notify_sender);
 752     $ruledb->rule_add_action ($rule, $block);
 753
 754     ## Add disclaimer
 755     $rule = PMG::RuleDB::Rule->new ('Add Disclaimer', 60, 0, 1);
 756     $ruledb->save_rule ($rule);
 757     $ruledb->rule_add_action ($rule, $add_discl);
 758
 759     # Block Multimedia Files
 760     $rule = PMG::RuleDB::Rule->new ('Block Multimedia Files', 87, 0, 2);
 761     $ruledb->save_rule ($rule);
 762
 763     $ruledb->rule_add_what_group ($rule, $mm_content);
 764     $ruledb->rule_add_action ($rule, $remove);
 765
 766     #$ruledb->rule_add_from_group ($rule, $anybody);
 767     #$ruledb->rule_add_from_group ($rule, $trusted);
 768     #$ruledb->rule_add_to_group ($rule, $anybody);
 769     #$ruledb->rule_add_what_group ($rule, $ct_filter);
 770     #$ruledb->rule_add_action ($rule, $add_discl);
 771     #$ruledb->rule_add_action ($rule, $remove);
 772     #$ruledb->rule_add_action ($rule, $bcc);
 773     #$ruledb->rule_add_action ($rule, $storeq);
 774     #$ruledb->rule_add_action ($rule, $accept);
 775
 776     cond_create_std_actions ($ruledb);
 777 }
 778
 779 sub get_remote_time {
 780     my ($rdb) = @_;
 781
 782     my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
 783     $sth->execute();
 784     my $ctinfo = $sth->fetchrow_hashref();
 785     $sth->finish ();
 786
 787     return $ctinfo ? $ctinfo->{ctime} : 0;
 788 }
 789
 790 sub init_masterdb {
 791     my ($lcid, $database) = @_;
 792
 793     die "got unexpected cid for new master" if !$lcid;
 794
 795     my $dbh;
 796
 797     eval {
 798         $dbh = open_ruledb($database);
 799
 800         $dbh->begin_work;
 801
 802         print STDERR "update quarantine database\n";
 803         $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
 804                   "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
 805
 806         print STDERR "update statistic database\n";
 807         $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
 808                   "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
 809
 810         print STDERR "update greylist database\n";
 811         $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
 812
 813         $dbh->commit;
 814     };
 815     my $err = $@;
 816
 817     if ($dbh) {
 818         $dbh->rollback if $err;
 819         $dbh->disconnect();
 820     }
 821
 822     die $err if $err;
 823 }
 824
 825 sub purge_statistic_database {
 826     my ($dbh, $statlifetime) = @_;
 827
 828     return if $statlifetime <= 0;
 829
 830     my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
 831     my $end = timelocal(0, 0, 0, $mday, $mon, $year);
 832     my $start = $end - $statlifetime*86400;
 833
 834     # delete statistics older than $start
 835
 836     my $rows = 0;
 837
 838     eval {
 839         $dbh->begin_work;
 840
 841         my $sth = $dbh->prepare("DELETE FROM CStatistic WHERE time < $start");
 842         $sth->execute;
 843         $rows = $sth->rows;
 844         $sth->finish;
 845
 846         if ($rows > 0) {
 847             $sth = $dbh->prepare(
 848                 "DELETE FROM CReceivers WHERE NOT EXISTS " .
 849                 "(SELECT * FROM CStatistic WHERE CID = CStatistic_CID AND RID = CStatistic_RID)");
 850
 851             $sth->execute;
 852         }
 853         $dbh->commit;
 854     };
 855     if (my $err = $@) {
 856         $dbh->rollback;
 857         die $err;
 858     }
 859
 860     return $rows;
 861 }
 862
 863 sub purge_quarantine_database {
 864     my ($dbh, $qtype, $lifetime) = @_;
 865
 866     my $spooldir = $PMG::MailQueue::spooldir;
 867
 868     my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
 869     my $end = timelocal(0, 0, 0, $mday, $mon, $year);
 870     my $start = $end - $lifetime*86400;
 871
 872     my $sth = $dbh->prepare(
 873         "SELECT file FROM CMailStore WHERE time < $start AND QType = '$qtype'");
 874
 875     $sth->execute();
 876
 877     my $count = 0;
 878
 879     while (my $ref = $sth->fetchrow_hashref()) {
 880         my $filename = "$spooldir/$ref->{file}";
 881         $count++ if unlink($filename);
 882     }
 883
 884     $sth->finish();
 885
 886     $dbh->do(
 887         "DELETE FROM CMailStore WHERE time < $start AND QType = '$qtype';" .
 888         "DELETE FROM CMSReceivers WHERE NOT EXISTS " .
 889         "(SELECT * FROM CMailStore WHERE CID = CMailStore_CID AND RID = CMailStore_RID)");
 890
 891     return $count;
 892 }
 893
 894 sub copy_table {
 895     my ($ldb, $rdb, $table) = @_;
 896
 897     $table = lc($table);
 898
 899     my $sth = $ldb->column_info(undef, undef, $table, undef);
 900     my $attrs = $sth->fetchall_arrayref({});
 901
 902     my @col_arr;
 903     foreach my $ref (@$attrs) {
 904         push @col_arr, $ref->{COLUMN_NAME};
 905     }
 906
 907     $sth->finish();
 908
 909     my $cols = join(', ', @col_arr);
 910     $cols || die "unable to fetch column definitions of table '$table' : ERROR";
 911
 912     $rdb->do("COPY $table ($cols) TO STDOUT");
 913
 914     my $data = '';
 915
 916     eval {
 917         $ldb->do("COPY $table ($cols) FROM stdin");
 918
 919         while ($rdb->pg_getcopydata($data) >= 0) {
 920             $ldb->pg_putcopydata($data);
 921         }
 922
 923         $ldb->pg_putcopyend();
 924     };
 925     if (my $err = $@) {
 926         $ldb->pg_putcopyend();
 927         die $err;
 928     }
 929 }
 930
 931 sub copy_selected_data {
 932     my ($dbh, $select_sth, $table, $attrs, $callback) = @_;
 933
 934     my $count = 0;
 935
 936     my $insert_sth = $dbh->prepare(
 937         "INSERT INTO ${table}(" . join(',', @$attrs) . ') ' .
 938         'VALUES (' . join(',', ('?') x scalar(@$attrs)) . ')');
 939
 940     while (my $ref = $select_sth->fetchrow_hashref()) {
 941         $callback->($ref) if $callback;
 942         $count++;
 943         $insert_sth->execute(map { $ref->{$_} } @$attrs);
 944     }
 945
 946     return $count;
 947 }
 948
 949 sub update_master_clusterinfo {
 950     my ($clientcid) = @_;
 951
 952     my $dbh = open_ruledb();
 953
 954     $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
 955
 956     my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'VirusInfo');
 957
 958     foreach my $table (@mt) {
 959         $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
 960                   "EXTRACT(EPOCH FROM now())");
 961     }
 962 }
 963
 964 sub update_client_clusterinfo {
 965     my ($mastercid) = @_;
 966
 967     my $dbh = open_ruledb();
 968
 969     $dbh->do ("DELETE FROM StatInfo"); # not needed at node
 970
 971     $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
 972
 973     $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
 974               "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
 975
 976     $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
 977               "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
 978
 979     my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'VirusInfo');
 980
 981     foreach my $table (@mt) {
 982         $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
 983                   "COALESCE (max (mtime), 0) FROM $table");
 984     }
 985 }
 986
 987 sub create_clusterinfo_default {
 988     my ($dbh, $rcid, $name, $ivalue, $svalue) = @_;
 989
 990     my $sth = $dbh->prepare("SELECT * FROM ClusterInfo WHERE CID = ? AND Name = ?");
 991     $sth->execute($rcid, $name);
 992     if (!$sth->fetchrow_hashref()) {
 993         $dbh->do("INSERT INTO ClusterInfo (CID, Name, IValue, SValue) " .
 994                  "VALUES (?, ?, ?, ?)", undef,
 995                  $rcid, $name, $ivalue, $svalue);
 996     }
 997     $sth->finish();
 998 }
 999
1000 sub read_int_clusterinfo {
1001     my ($dbh, $rcid, $name) = @_;
1002
1003     my $sth = $dbh->prepare(
1004         "SELECT ivalue as value FROM ClusterInfo " .
1005         "WHERE cid = ? AND NAME = ?");
1006     $sth->execute($rcid, $name);
1007     my $cinfo = $sth->fetchrow_hashref();
1008     $sth->finish();
1009
1010     return $cinfo->{value};
1011 }
1012
1013 sub write_maxint_clusterinfo {
1014     my ($dbh, $rcid, $name, $value) = @_;
1015
1016     $dbh->do("UPDATE ClusterInfo SET ivalue = GREATEST(ivalue, ?) " .
1017              "WHERE cid = ? AND name = ?", undef,
1018              $value, $rcid, $name);
1019 }
1020
1021 sub init_nodedb {
1022     my ($cinfo) = @_;
1023
1024     my $ni = $cinfo->{master};
1025
1026     die "no master defined - unable to sync data from master\n" if !$ni;
1027
1028     my $master_ip = $ni->{ip};
1029     my $master_cid = $ni->{cid};
1030     my $master_name = $ni->{name};
1031
1032     my $fn = "/tmp/masterdb$$.tar";
1033     unlink $fn;
1034
1035     my $dbname = $default_db_name;
1036
1037     eval {
1038         print STDERR "copying master database from '${master_ip}'\n";
1039
1040         open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
1041
1042         postgres_admin_cmd(
1043             ['/usr/bin/ssh', '-o', 'BatchMode=yes',
1044              '-o', "HostKeyAlias=${master_name}",
1045              $master_ip, 'pg_dump'],
1046             { output => '>&' . fileno($fh) },
1047             $dbname, '-F', 'c');
1048
1049         close($fh);
1050
1051         my $size = -s $fn;
1052
1053         print STDERR "copying master database finished (got $size bytes)\n";
1054
1055         print STDERR "delete local database\n";
1056
1057         postgres_admin_cmd('dropdb', undef, $dbname , '--if-exists');
1058
1059         print STDERR "create new local database\n";
1060
1061         postgres_admin_cmd('createdb', undef, $dbname);
1062
1063         print STDERR "insert received data into local database\n";
1064
1065         my $mess;
1066         my $parser = sub {
1067             my $line = shift;
1068
1069             if ($line =~ m/restoring data for table \"(.+)\"/) {
1070                 print STDERR "restoring table $1\n";
1071             } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
1072                 $mess = "creating indexes";
1073                 print STDERR "$mess\n";
1074             }
1075         };
1076
1077         my $opts = {
1078             outfunc => $parser,
1079             errfunc => $parser,
1080             errmsg => "pg_restore failed"
1081         };
1082
1083         postgres_admin_cmd('pg_restore', $opts, '-d', $dbname, '-v', $fn);
1084
1085         print STDERR "run analyze to speed up database queries\n";
1086
1087         postgres_admin_cmd('psql', { input => 'analyze;' }, $dbname);
1088
1089         update_client_clusterinfo($master_cid);
1090     };
1091
1092     my $err = $@;
1093
1094     unlink $fn;
1095
1096     die $err if $err;
1097 }
1098
1099 sub cluster_sync_status {
1100     my ($cinfo) = @_;
1101
1102     my $dbh;
1103
1104     my $minmtime;
1105
1106     foreach my $ni (values %{$cinfo->{ids}}) {
1107         next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID
1108         $minmtime->{$ni->{cid}} = 0;
1109     }
1110
1111     eval {
1112         $dbh = open_ruledb();
1113
1114         my $sth = $dbh->prepare(
1115             "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " .
1116             "WHERE name = 'lastsync' AND ivalue > 0 " .
1117             "GROUP BY cid");
1118
1119         $sth->execute();
1120
1121         while (my $info = $sth->fetchrow_hashref()) {
1122             foreach my $ni (values %{$cinfo->{ids}}) {
1123                 next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID
1124                 if ($ni->{cid} == $info->{cid}) { # node exists
1125                     $minmtime->{$ni->{cid}} = $info->{minmtime};
1126                 }
1127             }
1128         }
1129
1130         $sth->finish();
1131     };
1132     my $err = $@;
1133
1134     $dbh->disconnect() if $dbh;
1135
1136     syslog('err', $err) if $err;
1137
1138     return $minmtime;
1139 }
1140
1141 sub load_mail_data {
1142     my ($dbh, $cid, $rid) = @_;
1143
1144     my $sth = $dbh->prepare(
1145         "SELECT * FROM CMailStore, CMSReceivers WHERE " .
1146         "CID = $cid and RID = $rid AND " .
1147         "CID = CMailStore_CID AND RID = CMailStore_RID");
1148     $sth->execute ();
1149
1150     my $res = $sth->fetchrow_hashref();
1151
1152     $sth->finish();
1153
1154     die "no such mail (C${cid}R${rid})\n" if !defined($res);
1155
1156     return $res;
1157 }
1158
1159 sub reload_ruledb {
1160     my $pid_file = '/var/run/pmg-smtp-filter.pid';
1161     my $pid = PVE::Tools::file_read_firstline($pid_file);
1162
1163     return 0 if !$pid;
1164
1165     return kill (10, $pid); # send SIGUSR1
1166 }
1167
1168 1;