6 use POSIX
":sys_wait_h";
19 our $default_db_name = "Proxmox_ruledb";
21 our $cgreylist_merge_sql =
22 'INSERT INTO CGREYLIST (IPNet,Host,Sender,Receiver,Instance,RCTime,' .
23 'ExTime,Delay,Blocked,Passed,MTime,CID) ' .
24 'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ' .
25 'ON CONFLICT (IPNet,Sender,Receiver) DO UPDATE SET ' .
26 'Host = CASE WHEN CGREYLIST.MTime >= excluded.MTime THEN CGREYLIST.Host ELSE excluded.Host END,' .
27 'CID = GREATEST(CGREYLIST.CID, excluded.CID), RCTime = LEAST(CGREYLIST.RCTime, excluded.RCTime),' .
28 'ExTime = GREATEST(CGREYLIST.ExTime, excluded.ExTime),' .
29 'Delay = GREATEST(CGREYLIST.Delay, excluded.Delay),' .
30 'Blocked = GREATEST(CGREYLIST.Blocked, excluded.Blocked),' .
31 'Passed = GREATEST(CGREYLIST.Passed, excluded.Passed)';
34 my ($database, $host, $port) = @_;
38 $database //= $default_db_name;
42 # Note: pmgtunnel uses UDP sockets inside directory '/var/run/pmgtunnel',
43 # and the cluster 'cid' as port number. You can connect to the
44 # socket with: host => /var/run/pmgtunnel, port => $cid
46 my $dsn = "dbi:Pg:dbname=$database;host=$host;port=$port;";
49 # only low level alarm interface works for DBI->connect
50 my $mask = POSIX
::SigSet-
>new(SIGALRM
);
51 my $action = POSIX
::SigAction-
>new(sub { die "connect timeout\n" }, $mask);
52 my $oldaction = POSIX
::SigAction-
>new();
53 sigaction
(SIGALRM
, $action, $oldaction);
59 $rdb = DBI-
>connect($dsn, 'root', undef,
60 { PrintError
=> 0, RaiseError
=> 1 });
64 sigaction
(SIGALRM
, $oldaction); # restore original handler
70 my $dsn = "DBI:Pg:dbname=$database;host=/var/run/postgresql;port=$port";
72 my $dbh = DBI-
>connect($dsn, $> == 0 ?
'root' : 'www-data', undef,
73 { PrintError
=> 0, RaiseError
=> 1 });
79 sub postgres_admin_cmd
{
80 my ($cmd, $options, @params) = @_;
82 $cmd = ref($cmd) ?
$cmd : [ $cmd ];
83 my $uid = getpwnam('postgres') || die "getpwnam postgres failed\n";
86 $! && die "setuid postgres ($uid) failed - $!\n";
88 PVE
::Tools
::run_command
([@$cmd, '-U', 'postgres', @params], %$options);
94 postgres_admin_cmd
('dropdb', undef, $dbname);
99 my $database_list = {};
104 my ($name, $owner) = map { PVE
::Tools
::trim
($_) } split(/\|/, $line);
105 return if !$name || !$owner;
107 $database_list->{$name} = { owner
=> $owner };
110 postgres_admin_cmd
('psql', { outfunc
=> $parser }, '--list', '--quiet', '--tuples-only');
112 return $database_list;
115 my $cgreylist_ctablecmd = <<__EOD;
116 CREATE TABLE CGreylist
117 (IPNet VARCHAR(16) NOT NULL,
118 Host INTEGER NOT NULL,
119 Sender VARCHAR(255) NOT NULL,
120 Receiver VARCHAR(255) NOT NULL,
121 Instance VARCHAR(255),
122 RCTime INTEGER NOT NULL,
123 ExTime INTEGER NOT NULL,
124 Delay INTEGER NOT NULL DEFAULT 0,
125 Blocked INTEGER NOT NULL,
126 Passed INTEGER NOT NULL,
127 CID INTEGER NOT NULL,
128 MTime INTEGER NOT NULL,
129 PRIMARY KEY (IPNet, Sender, Receiver));
131 CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender);
133 CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime);
135 CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime);
138 my $clusterinfo_ctablecmd = <<__EOD;
139 CREATE TABLE ClusterInfo
140 (CID INTEGER NOT NULL,
141 Name VARCHAR NOT NULL,
144 PRIMARY KEY (CID, Name))
147 my $local_stat_ctablecmd = <<__EOD;
148 CREATE TABLE LocalStat
149 (Time INTEGER NOT NULL,
150 RBLCount INTEGER DEFAULT 0 NOT NULL,
151 PregreetCount INTEGER DEFAULT 0 NOT NULL,
152 CID INTEGER NOT NULL,
153 MTime INTEGER NOT NULL,
154 PRIMARY KEY (Time, CID));
156 CREATE INDEX LocalStat_MTime_Index ON LocalStat (MTime);
160 my $daily_stat_ctablecmd = <<__EOD;
161 CREATE TABLE DailyStat
162 (Time INTEGER NOT NULL UNIQUE,
163 CountIn INTEGER NOT NULL,
164 CountOut INTEGER NOT NULL,
165 BytesIn REAL NOT NULL,
166 BytesOut REAL NOT NULL,
167 VirusIn INTEGER NOT NULL,
168 VirusOut INTEGER NOT NULL,
169 SpamIn INTEGER NOT NULL,
170 SpamOut INTEGER NOT NULL,
171 BouncesIn INTEGER NOT NULL,
172 BouncesOut INTEGER NOT NULL,
173 GreylistCount INTEGER NOT NULL,
174 SPFCount INTEGER NOT NULL,
175 PTimeSum REAL NOT NULL,
176 MTime INTEGER NOT NULL,
177 RBLCount INTEGER DEFAULT 0 NOT NULL,
180 CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
184 my $domain_stat_ctablecmd = <<__EOD;
185 CREATE TABLE DomainStat
186 (Time INTEGER NOT NULL,
187 Domain VARCHAR(255) NOT NULL,
188 CountIn INTEGER NOT NULL,
189 CountOut INTEGER NOT NULL,
190 BytesIn REAL NOT NULL,
191 BytesOut REAL NOT NULL,
192 VirusIn INTEGER NOT NULL,
193 VirusOut INTEGER NOT NULL,
194 SpamIn INTEGER NOT NULL,
195 SpamOut INTEGER NOT NULL,
196 BouncesIn INTEGER NOT NULL,
197 BouncesOut INTEGER NOT NULL,
198 PTimeSum REAL NOT NULL,
199 MTime INTEGER NOT NULL,
200 PRIMARY KEY (Time, Domain));
202 CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
205 my $statinfo_ctablecmd = <<__EOD;
206 CREATE TABLE StatInfo
207 (Name VARCHAR(255) NOT NULL UNIQUE,
213 my $virusinfo_stat_ctablecmd = <<__EOD;
214 CREATE TABLE VirusInfo
215 (Time INTEGER NOT NULL,
216 Name VARCHAR NOT NULL,
217 Count INTEGER NOT NULL,
218 MTime INTEGER NOT NULL,
219 PRIMARY KEY (Time, Name));
221 CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
227 # V - Virus quarantine
228 # S - Spam quarantine
229 # D - Delayed Mails - not implemented
230 # A - Held for Audit - not implemented
235 my $cmailstore_ctablecmd = <<__EOD;
236 CREATE TABLE CMailStore
237 (CID INTEGER DEFAULT 0 NOT NULL,
238 RID INTEGER NOT NULL,
240 Time INTEGER NOT NULL,
241 QType "char" NOT NULL,
242 Bytes INTEGER NOT NULL,
243 Spamlevel INTEGER NOT NULL,
245 Sender VARCHAR(255) NOT NULL,
246 Header VARCHAR NOT NULL,
247 File VARCHAR(255) NOT NULL,
248 PRIMARY KEY (CID, RID));
249 CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
251 CREATE TABLE CMSReceivers
252 (CMailStore_CID INTEGER NOT NULL,
253 CMailStore_RID INTEGER NOT NULL,
254 PMail VARCHAR(255) NOT NULL,
255 Receiver VARCHAR(255),
256 TicketID INTEGER NOT NULL,
257 Status "char" NOT NULL,
258 MTime INTEGER NOT NULL);
260 CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
262 CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
266 my $cstatistic_ctablecmd = <<__EOD;
267 CREATE TABLE CStatistic
268 (CID INTEGER DEFAULT 0 NOT NULL,
269 RID INTEGER NOT NULL,
271 Time INTEGER NOT NULL,
272 Bytes INTEGER NOT NULL,
273 Direction Boolean NOT NULL,
274 Spamlevel INTEGER NOT NULL,
275 VirusInfo VARCHAR(255) NULL,
276 PTime INTEGER NOT NULL,
277 Sender VARCHAR(255) NOT NULL,
278 PRIMARY KEY (CID, RID));
280 CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
282 CREATE TABLE CReceivers
283 (CStatistic_CID INTEGER NOT NULL,
284 CStatistic_RID INTEGER NOT NULL,
285 Receiver VARCHAR(255) NOT NULL,
286 Blocked Boolean NOT NULL);
288 CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
291 # user preferences (black an whitelists, ...)
292 # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
293 # Data: arbitrary data
294 my $userprefs_ctablecmd = <<__EOD;
295 CREATE TABLE UserPrefs
299 MTime INTEGER NOT NULL,
300 PRIMARY KEY (PMail, Name));
302 CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
306 sub cond_create_dbtable
{
307 my ($dbh, $name, $ctablecmd) = @_;
312 my $cmd = "SELECT tablename FROM pg_tables " .
313 "WHERE tablename = lower ('$name')";
315 my $sth = $dbh->prepare($cmd);
319 if (!(my $ref = $sth->fetchrow_hashref())) {
320 $dbh->do ($ctablecmd);
333 sub database_column_exists
{
334 my ($dbh, $table, $column) = @_;
336 my $sth = $dbh->prepare(
337 "SELECT column_name FROM information_schema.columns " .
338 "WHERE table_name = ? and column_name = ?");
339 $sth->execute(lc($table), lc($column));
340 my $res = $sth->fetchrow_hashref();
341 return defined($res);
347 $dbname = $default_db_name if !$dbname;
349 my $silent_opts = { outfunc
=> sub {}, errfunc
=> sub {} };
350 # make sure we have user 'root'
351 eval { postgres_admin_cmd
('createuser', $silent_opts, '-D', 'root'); };
352 # also create 'www-data' (and give it read-only access below)
353 eval { postgres_admin_cmd
('createuser', $silent_opts, '-I', '-D', 'www-data'); };
355 # use sql_ascii to avoid any character set conversions, and be compatible with
356 # older postgres versions (update from 8.1 must be possible)
358 postgres_admin_cmd
('createdb', undef, '-E', 'sql_ascii', '-T', 'template0',
359 '--lc-collate=C', '--lc-ctype=C', $dbname);
361 my $dbh = open_ruledb
($dbname);
363 # make sure 'www-data' can read all tables
364 $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\"");
368 CREATE TABLE Attribut
369 (Object_ID INTEGER NOT NULL,
370 Name VARCHAR(20) NOT NULL,
372 PRIMARY KEY (Object_ID, Name));
374 CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
378 ObjectType INTEGER NOT NULL,
379 Objectgroup_ID INTEGER NOT NULL,
383 CREATE TABLE Objectgroup
385 Name VARCHAR(255) NOT NULL,
386 Info VARCHAR(255) NULL,
387 Class VARCHAR(10) NOT NULL,
392 Name VARCHAR(255) NULL,
393 Priority INTEGER NOT NULL,
394 Active INTEGER NOT NULL DEFAULT 0,
395 Direction INTEGER NOT NULL DEFAULT 2,
396 Count INTEGER NOT NULL DEFAULT 0,
399 CREATE TABLE RuleGroup
400 (Objectgroup_ID INTEGER NOT NULL,
401 Rule_ID INTEGER NOT NULL,
402 Grouptype INTEGER NOT NULL,
403 PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
405 $cgreylist_ctablecmd;
407 $clusterinfo_ctablecmd;
409 $local_stat_ctablecmd;
411 $daily_stat_ctablecmd;
413 $domain_stat_ctablecmd;
417 $cmailstore_ctablecmd;
419 $cstatistic_ctablecmd;
421 $userprefs_ctablecmd;
423 $virusinfo_stat_ctablecmd;
430 sub cond_create_action_quarantine
{
433 my $dbh = $ruledb->{dbh
};
436 my $sth = $dbh->prepare(
437 "SELECT * FROM Objectgroup, Object " .
438 "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " .
439 "AND Object.objectgroup_id = Objectgroup.id");
441 my $otype = PMG
::RuleDB
::Quarantine
::otype
();
442 if ($sth->execute($otype, 'action') <= 0) {
443 my $obj = PMG
::RuleDB
::Quarantine-
>new ();
444 my $txt = decode_entities
(PMG
::RuleDB
::Quarantine-
>otype_text);
445 my $quarantine = $ruledb->create_group_with_obj
446 ($obj, $txt, 'Move to quarantine.');
451 sub cond_create_std_actions
{
454 cond_create_action_quarantine
($ruledb);
456 #cond_create_action_report_spam($ruledb);
463 my $dbh = $ruledb->{dbh
};
465 # make sure we do not use slow sequential scans when upgraing
466 # database (before analyze can gather statistics)
467 $dbh->do("set enable_seqscan = false");
470 'LocalStat', $local_stat_ctablecmd,
471 'DailyStat', $daily_stat_ctablecmd,
472 'DomainStat', $domain_stat_ctablecmd,
473 'StatInfo', $statinfo_ctablecmd,
474 'CMailStore', $cmailstore_ctablecmd,
475 'UserPrefs', $userprefs_ctablecmd,
476 'CGreylist', $cgreylist_ctablecmd,
477 'CStatistic', $cstatistic_ctablecmd,
478 'ClusterInfo', $clusterinfo_ctablecmd,
479 'VirusInfo', $virusinfo_stat_ctablecmd,
482 foreach my $table (keys %$tables) {
483 cond_create_dbtable
($dbh, $table, $tables->{$table});
486 cond_create_std_actions
($ruledb);
488 # upgrade tables here if necessary
489 if (!database_column_exists
($dbh, 'LocalStat', 'PregreetCount')) {
490 $dbh->do("ALTER TABLE LocalStat ADD COLUMN " .
491 "PregreetCount INTEGER DEFAULT 0 NOT NULL");
494 eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); };
498 # add missing TicketID to CMSReceivers
499 if (!database_column_exists
($dbh, 'CMSReceivers', 'TicketID')) {
502 $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq");
503 $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " .
504 "TicketID INTEGER NOT NULL " .
505 "DEFAULT nextval('cmsreceivers_ticketid_seq')");
506 $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " .
507 "TicketID DROP DEFAULT");
508 $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq");
517 # update obsolete content type names
519 $dbh->do("UPDATE Object " .
520 "SET value = 'content-type:application/java-vm' ".
521 "WHERE objecttype = 3003 " .
522 "AND value = 'content-type:application/x-java-vm';");
525 foreach my $table (keys %$tables) {
526 eval { $dbh->do("ANALYZE $table"); };
534 my ($ruledb, $reset, $testmode) = @_;
536 my $dbh = $ruledb->{dbh
};
539 # Greylist Objectgroup
540 my $greylistgroup = PMG
::RuleDB
::Group-
>new
541 ("GreyExclusion", "-", "greylist");
542 $ruledb->save_group ($greylistgroup);
545 # we do not touch greylist objects
546 my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
547 "objectgroup_id = objectgroup.id and class = 'greylist'";
549 $dbh->do ("DELETE FROM Rule; " .
550 "DELETE FROM RuleGroup; " .
551 "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
552 "DELETE FROM Object WHERE ID NOT IN ($glids); " .
553 "DELETE FROM Objectgroup WHERE class != 'greylist';");
559 my $obj = PMG
::RuleDB
::EMail-
>new ('nomail@fromthisdomain.com');
560 my $blacklist = $ruledb->create_group_with_obj(
561 $obj, 'Blacklist', 'Global blacklist');
564 $obj = PMG
::RuleDB
::EMail-
>new('mail@fromthisdomain.com');
565 my $whitelist = $ruledb->create_group_with_obj(
566 $obj, 'Whitelist', 'Global whitelist');
571 $obj = PMG
::RuleDB
::TimeFrame-
>new(8*60, 16*60);
572 my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
573 'Usual office hours');
578 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('image/.*');
579 my $img_content = $ruledb->create_group_with_obj(
580 $obj, 'Images', 'All kinds of graphic files');
583 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('audio/.*');
584 my $mm_content = $ruledb->create_group_with_obj(
585 $obj, 'Multimedia', 'Audio and Video');
587 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('video/.*');
588 $ruledb->group_add_object($mm_content, $obj);
591 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/vnd\.ms-excel');
592 my $office_content = $ruledb->create_group_with_obj(
593 $obj, 'Office Files', 'Common Office Files');
595 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
596 'application/vnd\.ms-powerpoint');
598 $ruledb->group_add_object($office_content, $obj);
600 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/msword');
601 $ruledb->group_add_object ($office_content, $obj);
603 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
604 'application/vnd\.openxmlformats-officedocument\..*');
605 $ruledb->group_add_object($office_content, $obj);
607 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
608 'application/vnd\.oasis\.opendocument\..*');
609 $ruledb->group_add_object($office_content, $obj);
611 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
612 'application/vnd\.stardivision\..*');
613 $ruledb->group_add_object($office_content, $obj);
615 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
616 'application/vnd\.sun\.xml\..*');
617 $ruledb->group_add_object($office_content, $obj);
620 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
621 'application/x-ms-dos-executable');
622 my $exe_content = $ruledb->create_group_with_obj(
623 $obj, 'Dangerous Content', 'executable files and partial messages');
625 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-java');
626 $ruledb->group_add_object($exe_content, $obj);
627 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/javascript');
628 $ruledb->group_add_object($exe_content, $obj);
629 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-executable');
630 $ruledb->group_add_object($exe_content, $obj);
631 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-ms-dos-executable');
632 $ruledb->group_add_object($exe_content, $obj);
633 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('message/partial');
634 $ruledb->group_add_object($exe_content, $obj);
635 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.(vbs|pif|lnk|shs|shb)');
636 $ruledb->group_add_object($exe_content, $obj);
637 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.\{.+\}');
638 $ruledb->group_add_object($exe_content, $obj);
641 $obj = PMG
::RuleDB
::Virus-
>new();
642 my $virus = $ruledb->create_group_with_obj(
643 $obj, 'Virus', 'Matches virus infected mail');
648 $obj = PMG
::RuleDB
::Spam-
>new(3);
649 my $spam3 = $ruledb->create_group_with_obj(
650 $obj, 'Spam (Level 3)', 'Matches possible spam mail');
652 $obj = PMG
::RuleDB
::Spam-
>new(5);
653 my $spam5 = $ruledb->create_group_with_obj(
654 $obj, 'Spam (Level 5)', 'Matches possible spam mail');
656 $obj = PMG
::RuleDB
::Spam-
>new(10);
657 my $spam10 = $ruledb->create_group_with_obj(
658 $obj, 'Spam (Level 10)', 'Matches possible spam mail');
663 $obj = PMG
::RuleDB
::ModField-
>new('X-SPAM-LEVEL', '__SPAM_INFO__');
664 my $mod_spam_level = $ruledb->create_group_with_obj(
665 $obj, 'Modify Spam Level',
666 'Mark mail as spam by adding a header tag.');
669 $obj = PMG
::RuleDB
::ModField-
>new('subject', 'SPAM: __SUBJECT__');
670 my $mod_spam_subject = $ruledb->create_group_with_obj(
671 $obj, 'Modify Spam Subject',
672 'Mark mail as spam by modifying the subject.');
674 # Remove matching attachments
675 $obj = PMG
::RuleDB
::Remove-
>new(0);
676 my $remove = $ruledb->create_group_with_obj(
677 $obj, 'Remove attachments', 'Remove matching attachments');
679 # Remove all attachments
680 $obj = PMG
::RuleDB
::Remove-
>new(1);
681 my $remove_all = $ruledb->create_group_with_obj(
682 $obj, 'Remove all attachments', 'Remove all attachments');
685 $obj = PMG
::RuleDB
::Accept-
>new();
686 my $accept = $ruledb->create_group_with_obj(
687 $obj, 'Accept', 'Accept mail for Delivery');
690 $obj = PMG
::RuleDB
::Block-
>new ();
691 my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
694 $obj = PMG
::RuleDB
::Quarantine-
>new();
695 my $quarantine = $ruledb->create_group_with_obj(
696 $obj, 'Quarantine', 'Move mail to quarantine');
699 $obj = PMG
::RuleDB
::Notify-
>new('__ADMIN__');
700 my $notify_admin = $ruledb->create_group_with_obj(
701 $obj, 'Notify Admin', 'Send notification');
704 $obj = PMG
::RuleDB
::Notify-
>new('__SENDER__');
705 my $notify_sender = $ruledb->create_group_with_obj(
706 $obj, 'Notify Sender', 'Send notification');
709 $obj = PMG
::RuleDB
::Disclaimer-
>new ();
710 my $add_discl = $ruledb->create_group_with_obj(
711 $obj, 'Disclaimer', 'Add Disclaimer');
713 # Attach original mail
714 #$obj = Proxmox::RuleDB::Attach->new ();
715 #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
716 # 'Attach Original Mail');
718 ####################### RULES ##################################
720 ## Block Dangerous Files
721 my $rule = PMG
::RuleDB
::Rule-
>new ('Block Dangerous Files', 93, 1, 0);
722 $ruledb->save_rule ($rule);
724 $ruledb->rule_add_what_group ($rule, $exe_content);
725 $ruledb->rule_add_action ($rule, $remove);
728 $rule = PMG
::RuleDB
::Rule-
>new ('Block Viruses', 96, 1, 0);
729 $ruledb->save_rule ($rule);
731 $ruledb->rule_add_what_group ($rule, $virus);
732 $ruledb->rule_add_action ($rule, $notify_admin);
735 $ruledb->rule_add_action ($rule, $block);
737 $ruledb->rule_add_action ($rule, $quarantine);
741 $rule = PMG
::RuleDB
::Rule-
>new ('Virus Alert', 96, 1, 1);
742 $ruledb->save_rule ($rule);
744 $ruledb->rule_add_what_group ($rule, $virus);
745 $ruledb->rule_add_action ($rule, $notify_sender);
746 $ruledb->rule_add_action ($rule, $notify_admin);
747 $ruledb->rule_add_action ($rule, $block);
750 $rule = PMG
::RuleDB
::Rule-
>new ('Blacklist', 98, 1, 0);
751 $ruledb->save_rule ($rule);
753 $ruledb->rule_add_from_group ($rule, $blacklist);
754 $ruledb->rule_add_action ($rule, $block);
758 $rule = PMG
::RuleDB
::Rule-
>new ('Modify Header', 90, 1, 0);
759 $ruledb->save_rule ($rule);
760 $ruledb->rule_add_action ($rule, $mod_spam_level);
764 $rule = PMG
::RuleDB
::Rule-
>new ('Whitelist', 85, 1, 0);
765 $ruledb->save_rule ($rule);
767 $ruledb->rule_add_from_group ($rule, $whitelist);
768 $ruledb->rule_add_action ($rule, $accept);
771 $rule = PMG
::RuleDB
::Rule-
>new ('Mark Spam', 80, 1, 0);
772 $ruledb->save_rule ($rule);
774 $ruledb->rule_add_what_group ($rule, $spam10);
775 $ruledb->rule_add_action ($rule, $mod_spam_level);
776 $ruledb->rule_add_action ($rule, $mod_spam_subject);
778 # Quarantine/Mark Spam (Level 3)
779 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
780 $ruledb->save_rule ($rule);
782 $ruledb->rule_add_what_group ($rule, $spam3);
783 $ruledb->rule_add_action ($rule, $mod_spam_subject);
784 $ruledb->rule_add_action ($rule, $quarantine);
785 #$ruledb->rule_add_action ($rule, $count_spam);
788 # Quarantine/Mark Spam (Level 5)
789 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0);
790 $ruledb->save_rule ($rule);
792 $ruledb->rule_add_what_group ($rule, $spam5);
793 $ruledb->rule_add_action ($rule, $mod_spam_subject);
794 $ruledb->rule_add_action ($rule, $quarantine);
796 ## Block Spam Level 10
797 $rule = PMG
::RuleDB
::Rule-
>new ('Block Spam (Level 10)', 82, 0, 0);
798 $ruledb->save_rule ($rule);
800 $ruledb->rule_add_what_group ($rule, $spam10);
801 $ruledb->rule_add_action ($rule, $block);
803 ## Block Outgoing Spam
804 $rule = PMG
::RuleDB
::Rule-
>new ('Block outgoing Spam', 70, 0, 1);
805 $ruledb->save_rule ($rule);
807 $ruledb->rule_add_what_group ($rule, $spam3);
808 $ruledb->rule_add_action ($rule, $notify_admin);
809 $ruledb->rule_add_action ($rule, $notify_sender);
810 $ruledb->rule_add_action ($rule, $block);
813 $rule = PMG
::RuleDB
::Rule-
>new ('Add Disclaimer', 60, 0, 1);
814 $ruledb->save_rule ($rule);
815 $ruledb->rule_add_action ($rule, $add_discl);
817 # Block Multimedia Files
818 $rule = PMG
::RuleDB
::Rule-
>new ('Block Multimedia Files', 87, 0, 2);
819 $ruledb->save_rule ($rule);
821 $ruledb->rule_add_what_group ($rule, $mm_content);
822 $ruledb->rule_add_action ($rule, $remove);
824 #$ruledb->rule_add_from_group ($rule, $anybody);
825 #$ruledb->rule_add_from_group ($rule, $trusted);
826 #$ruledb->rule_add_to_group ($rule, $anybody);
827 #$ruledb->rule_add_what_group ($rule, $ct_filter);
828 #$ruledb->rule_add_action ($rule, $add_discl);
829 #$ruledb->rule_add_action ($rule, $remove);
830 #$ruledb->rule_add_action ($rule, $bcc);
831 #$ruledb->rule_add_action ($rule, $storeq);
832 #$ruledb->rule_add_action ($rule, $accept);
834 cond_create_std_actions
($ruledb);
839 sub get_remote_time
{
842 my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
844 my $ctinfo = $sth->fetchrow_hashref();
847 return $ctinfo ?
$ctinfo->{ctime
} : 0;
851 my ($lcid, $database) = @_;
853 die "got unexpected cid for new master" if !$lcid;
858 $dbh = open_ruledb
($database);
862 print STDERR
"update quarantine database\n";
863 $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
864 "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
866 print STDERR
"update statistic database\n";
867 $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
868 "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
870 print STDERR
"update greylist database\n";
871 $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
873 print STDERR
"update localstat database\n";
874 $dbh->do ("UPDATE LocalStat SET CID = $lcid WHERE CID = 0;");
881 $dbh->rollback if $err;
888 sub purge_statistic_database
{
889 my ($dbh, $statlifetime) = @_;
891 return if $statlifetime <= 0;
893 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
894 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
895 my $start = $end - $statlifetime*86400;
897 # delete statistics older than $start
904 my $sth = $dbh->prepare("DELETE FROM CStatistic WHERE time < $start");
910 $sth = $dbh->prepare(
911 "DELETE FROM CReceivers WHERE NOT EXISTS " .
912 "(SELECT * FROM CStatistic WHERE CID = CStatistic_CID AND RID = CStatistic_RID)");
926 sub purge_quarantine_database
{
927 my ($dbh, $qtype, $lifetime) = @_;
929 my $spooldir = $PMG::MailQueue
::spooldir
;
931 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
932 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
933 my $start = $end - $lifetime*86400;
935 my $sth = $dbh->prepare(
936 "SELECT file FROM CMailStore WHERE time < $start AND QType = '$qtype'");
942 while (my $ref = $sth->fetchrow_hashref()) {
943 my $filename = "$spooldir/$ref->{file}";
944 $count++ if unlink($filename);
950 "DELETE FROM CMailStore WHERE time < $start AND QType = '$qtype';" .
951 "DELETE FROM CMSReceivers WHERE NOT EXISTS " .
952 "(SELECT * FROM CMailStore WHERE CID = CMailStore_CID AND RID = CMailStore_RID)");
957 sub get_quarantine_count
{
958 my ($dbh, $qtype) = @_;
960 # Note;: We try to estimate used disk space - each mail
961 # is stored in an extra file ...
965 my $sth = $dbh->prepare(
966 "SELECT count(ID) as count, sum (ceil((Bytes+$bs-1)/$bs)*$bs) / (1024*1024) as mbytes, " .
967 "avg(Bytes) as avgbytes, avg(Spamlevel) as avgspam " .
968 "FROM CMailStore WHERE QType = ?");
970 $sth->execute($qtype);
972 my $ref = $sth->fetchrow_hashref();
976 foreach my $k (qw(count mbytes avgbytes avgspam)) {
984 my ($ldb, $rdb, $table) = @_;
988 my $sth = $ldb->column_info(undef, undef, $table, undef);
989 my $attrs = $sth->fetchall_arrayref({});
992 foreach my $ref (@$attrs) {
993 push @col_arr, $ref->{COLUMN_NAME
};
998 my $cols = join(', ', @col_arr);
999 $cols || die "unable to fetch column definitions of table '$table' : ERROR";
1001 $rdb->do("COPY $table ($cols) TO STDOUT");
1006 $ldb->do("COPY $table ($cols) FROM stdin");
1008 while ($rdb->pg_getcopydata($data) >= 0) {
1009 $ldb->pg_putcopydata($data);
1012 $ldb->pg_putcopyend();
1015 $ldb->pg_putcopyend();
1020 sub copy_selected_data
{
1021 my ($dbh, $select_sth, $table, $attrs, $callback) = @_;
1025 my $insert_sth = $dbh->prepare(
1026 "INSERT INTO ${table}(" . join(',', @$attrs) . ') ' .
1027 'VALUES (' . join(',', ('?') x
scalar(@$attrs)) . ')');
1029 while (my $ref = $select_sth->fetchrow_hashref()) {
1030 $callback->($ref) if $callback;
1032 $insert_sth->execute(map { $ref->{$_} } @$attrs);
1038 sub update_master_clusterinfo
{
1039 my ($clientcid) = @_;
1041 my $dbh = open_ruledb
();
1043 $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
1045 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1047 foreach my $table (@mt) {
1048 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
1049 "EXTRACT(EPOCH FROM now())");
1053 sub update_client_clusterinfo
{
1054 my ($mastercid) = @_;
1056 my $dbh = open_ruledb
();
1058 $dbh->do ("DELETE FROM StatInfo"); # not needed at node
1060 $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
1062 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
1063 "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
1065 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
1066 "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
1068 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1070 foreach my $table (@mt) {
1071 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
1072 "COALESCE (max (mtime), 0) FROM $table");
1076 sub create_clusterinfo_default
{
1077 my ($dbh, $rcid, $name, $ivalue, $svalue) = @_;
1079 my $sth = $dbh->prepare("SELECT * FROM ClusterInfo WHERE CID = ? AND Name = ?");
1080 $sth->execute($rcid, $name);
1081 if (!$sth->fetchrow_hashref()) {
1082 $dbh->do("INSERT INTO ClusterInfo (CID, Name, IValue, SValue) " .
1083 "VALUES (?, ?, ?, ?)", undef,
1084 $rcid, $name, $ivalue, $svalue);
1089 sub read_int_clusterinfo
{
1090 my ($dbh, $rcid, $name) = @_;
1092 my $sth = $dbh->prepare(
1093 "SELECT ivalue as value FROM ClusterInfo " .
1094 "WHERE cid = ? AND NAME = ?");
1095 $sth->execute($rcid, $name);
1096 my $cinfo = $sth->fetchrow_hashref();
1099 return $cinfo->{value
};
1102 sub write_maxint_clusterinfo
{
1103 my ($dbh, $rcid, $name, $value) = @_;
1105 $dbh->do("UPDATE ClusterInfo SET ivalue = GREATEST(ivalue, ?) " .
1106 "WHERE cid = ? AND name = ?", undef,
1107 $value, $rcid, $name);
1113 my $ni = $cinfo->{master
};
1115 die "no master defined - unable to sync data from master\n" if !$ni;
1117 my $master_ip = $ni->{ip
};
1118 my $master_cid = $ni->{cid
};
1119 my $master_name = $ni->{name
};
1121 my $fn = "/tmp/masterdb$$.tar";
1124 my $dbname = $default_db_name;
1127 print STDERR
"copying master database from '${master_ip}'\n";
1129 open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
1131 my $cmd = ['/usr/bin/ssh', '-o', 'BatchMode=yes',
1132 '-o', "HostKeyAlias=${master_name}", $master_ip,
1133 'pg_dump', $dbname, '-F', 'c' ];
1135 PVE
::Tools
::run_command
($cmd, output
=> '>&' . fileno($fh));
1141 print STDERR
"copying master database finished (got $size bytes)\n";
1143 print STDERR
"delete local database\n";
1145 postgres_admin_cmd
('dropdb', undef, $dbname , '--if-exists');
1147 print STDERR
"create new local database\n";
1149 postgres_admin_cmd
('createdb', undef, $dbname);
1151 print STDERR
"insert received data into local database\n";
1157 if ($line =~ m/restoring data for table \"(.+)\"/) {
1158 print STDERR
"restoring table $1\n";
1159 } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
1160 $mess = "creating indexes";
1161 print STDERR
"$mess\n";
1168 errmsg
=> "pg_restore failed"
1171 postgres_admin_cmd
('pg_restore', $opts, '-d', $dbname, '-v', $fn);
1173 print STDERR
"run analyze to speed up database queries\n";
1175 postgres_admin_cmd
('psql', { input
=> 'analyze;' }, $dbname);
1177 update_client_clusterinfo
($master_cid);
1187 sub cluster_sync_status
{
1194 foreach my $ni (values %{$cinfo->{ids
}}) {
1195 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1196 $minmtime->{$ni->{cid
}} = 0;
1200 $dbh = open_ruledb
();
1202 my $sth = $dbh->prepare(
1203 "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " .
1204 "WHERE name = 'lastsync' AND ivalue > 0 " .
1209 while (my $info = $sth->fetchrow_hashref()) {
1210 foreach my $ni (values %{$cinfo->{ids
}}) {
1211 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1212 if ($ni->{cid
} == $info->{cid
}) { # node exists
1213 $minmtime->{$ni->{cid
}} = $info->{minmtime
};
1222 $dbh->disconnect() if $dbh;
1224 syslog
('err', $err) if $err;
1229 sub load_mail_data
{
1230 my ($dbh, $cid, $rid, $ticketid) = @_;
1232 my $sth = $dbh->prepare(
1233 "SELECT * FROM CMailStore, CMSReceivers WHERE " .
1234 "CID = ? AND RID = ? AND TicketID = ? AND " .
1235 "CID = CMailStore_CID AND RID = CMailStore_RID");
1236 $sth->execute($cid, $rid, $ticketid);
1238 my $res = $sth->fetchrow_hashref();
1242 die "no such mail (C${cid}R${rid}T${ticketid})\n" if !defined($res);
1250 # Note: we pass $ruledb when modifying SMTP whitelist
1251 if (defined($ruledb)) {
1253 my $rulecache = PMG
::RuleCache-
>new($ruledb);
1254 PMG
::Config
::rewrite_postfix_whitelist
($rulecache);
1257 warn "problems updating SMTP whitelist - $err";
1261 my $pid_file = '/var/run/pmg-smtp-filter.pid';
1262 my $pid = PVE
::Tools
::file_read_firstline
($pid_file);
1266 return 0 if $pid !~ m/^(\d+)$/;
1267 $pid = $1; # untaint
1269 return kill (10, $pid); # send SIGUSR1