6 use POSIX
":sys_wait_h";
7 use POSIX
qw(:signal_h getuid);
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 ];
84 my $save_uid = POSIX
::getuid
();
85 my $pg_uid = getpwnam('postgres') || die "getpwnam postgres failed\n";
87 PVE
::Tools
::setresuid
(-1, $pg_uid, -1) ||
88 die "setresuid postgres ($pg_uid) failed - $!\n";
90 PVE
::Tools
::run_command
([@$cmd, '-U', 'postgres', @params], %$options);
92 PVE
::Tools
::setresuid
(-1, $save_uid, -1) ||
93 die "setresuid back failed - $!\n";
99 postgres_admin_cmd
('dropdb', undef, $dbname);
104 my $database_list = {};
109 my ($name, $owner) = map { PVE
::Tools
::trim
($_) } split(/\|/, $line);
110 return if !$name || !$owner;
112 $database_list->{$name} = { owner
=> $owner };
115 postgres_admin_cmd
('psql', { outfunc
=> $parser }, '--list', '--quiet', '--tuples-only');
117 return $database_list;
120 my $cgreylist_ctablecmd = <<__EOD;
121 CREATE TABLE CGreylist
122 (IPNet VARCHAR(16) NOT NULL,
123 Host INTEGER NOT NULL,
124 Sender VARCHAR(255) NOT NULL,
125 Receiver VARCHAR(255) NOT NULL,
126 Instance VARCHAR(255),
127 RCTime INTEGER NOT NULL,
128 ExTime INTEGER NOT NULL,
129 Delay INTEGER NOT NULL DEFAULT 0,
130 Blocked INTEGER NOT NULL,
131 Passed INTEGER NOT NULL,
132 CID INTEGER NOT NULL,
133 MTime INTEGER NOT NULL,
134 PRIMARY KEY (IPNet, Sender, Receiver));
136 CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender);
138 CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime);
140 CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime);
143 my $clusterinfo_ctablecmd = <<__EOD;
144 CREATE TABLE ClusterInfo
145 (CID INTEGER NOT NULL,
146 Name VARCHAR NOT NULL,
149 PRIMARY KEY (CID, Name))
152 my $local_stat_ctablecmd = <<__EOD;
153 CREATE TABLE LocalStat
154 (Time INTEGER NOT NULL,
155 RBLCount INTEGER DEFAULT 0 NOT NULL,
156 PregreetCount INTEGER DEFAULT 0 NOT NULL,
157 CID INTEGER NOT NULL,
158 MTime INTEGER NOT NULL,
159 PRIMARY KEY (Time, CID));
161 CREATE INDEX LocalStat_MTime_Index ON LocalStat (MTime);
165 my $daily_stat_ctablecmd = <<__EOD;
166 CREATE TABLE DailyStat
167 (Time INTEGER NOT NULL UNIQUE,
168 CountIn INTEGER NOT NULL,
169 CountOut INTEGER NOT NULL,
170 BytesIn REAL NOT NULL,
171 BytesOut REAL NOT NULL,
172 VirusIn INTEGER NOT NULL,
173 VirusOut INTEGER NOT NULL,
174 SpamIn INTEGER NOT NULL,
175 SpamOut INTEGER NOT NULL,
176 BouncesIn INTEGER NOT NULL,
177 BouncesOut INTEGER NOT NULL,
178 GreylistCount INTEGER NOT NULL,
179 SPFCount INTEGER NOT NULL,
180 PTimeSum REAL NOT NULL,
181 MTime INTEGER NOT NULL,
182 RBLCount INTEGER DEFAULT 0 NOT NULL,
185 CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
189 my $domain_stat_ctablecmd = <<__EOD;
190 CREATE TABLE DomainStat
191 (Time INTEGER NOT NULL,
192 Domain VARCHAR(255) NOT NULL,
193 CountIn INTEGER NOT NULL,
194 CountOut INTEGER NOT NULL,
195 BytesIn REAL NOT NULL,
196 BytesOut REAL NOT NULL,
197 VirusIn INTEGER NOT NULL,
198 VirusOut INTEGER NOT NULL,
199 SpamIn INTEGER NOT NULL,
200 SpamOut INTEGER NOT NULL,
201 BouncesIn INTEGER NOT NULL,
202 BouncesOut INTEGER NOT NULL,
203 PTimeSum REAL NOT NULL,
204 MTime INTEGER NOT NULL,
205 PRIMARY KEY (Time, Domain));
207 CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
210 my $statinfo_ctablecmd = <<__EOD;
211 CREATE TABLE StatInfo
212 (Name VARCHAR(255) NOT NULL UNIQUE,
218 my $virusinfo_stat_ctablecmd = <<__EOD;
219 CREATE TABLE VirusInfo
220 (Time INTEGER NOT NULL,
221 Name VARCHAR NOT NULL,
222 Count INTEGER NOT NULL,
223 MTime INTEGER NOT NULL,
224 PRIMARY KEY (Time, Name));
226 CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
232 # V - Virus quarantine
233 # S - Spam quarantine
234 # D - Delayed Mails - not implemented
235 # A - Held for Audit - not implemented
240 my $cmailstore_ctablecmd = <<__EOD;
241 CREATE TABLE CMailStore
242 (CID INTEGER DEFAULT 0 NOT NULL,
243 RID INTEGER NOT NULL,
245 Time INTEGER NOT NULL,
246 QType "char" NOT NULL,
247 Bytes INTEGER NOT NULL,
248 Spamlevel INTEGER NOT NULL,
250 Sender VARCHAR(255) NOT NULL,
251 Header VARCHAR NOT NULL,
252 File VARCHAR(255) NOT NULL,
253 PRIMARY KEY (CID, RID));
254 CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
256 CREATE TABLE CMSReceivers
257 (CMailStore_CID INTEGER NOT NULL,
258 CMailStore_RID INTEGER NOT NULL,
259 PMail VARCHAR(255) NOT NULL,
260 Receiver VARCHAR(255),
261 TicketID INTEGER NOT NULL,
262 Status "char" NOT NULL,
263 MTime INTEGER NOT NULL);
265 CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
267 CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
271 my $cstatistic_ctablecmd = <<__EOD;
272 CREATE TABLE CStatistic
273 (CID INTEGER DEFAULT 0 NOT NULL,
274 RID INTEGER NOT NULL,
276 Time INTEGER NOT NULL,
277 Bytes INTEGER NOT NULL,
278 Direction Boolean NOT NULL,
279 Spamlevel INTEGER NOT NULL,
280 VirusInfo VARCHAR(255) NULL,
281 PTime INTEGER NOT NULL,
282 Sender VARCHAR(255) NOT NULL,
283 PRIMARY KEY (CID, RID));
285 CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
287 CREATE TABLE CReceivers
288 (CStatistic_CID INTEGER NOT NULL,
289 CStatistic_RID INTEGER NOT NULL,
290 Receiver VARCHAR(255) NOT NULL,
291 Blocked Boolean NOT NULL);
293 CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
296 # user preferences (black an whitelists, ...)
297 # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
298 # Data: arbitrary data
299 my $userprefs_ctablecmd = <<__EOD;
300 CREATE TABLE UserPrefs
304 MTime INTEGER NOT NULL,
305 PRIMARY KEY (PMail, Name));
307 CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
311 sub cond_create_dbtable
{
312 my ($dbh, $name, $ctablecmd) = @_;
317 my $cmd = "SELECT tablename FROM pg_tables " .
318 "WHERE tablename = lower ('$name')";
320 my $sth = $dbh->prepare($cmd);
324 if (!(my $ref = $sth->fetchrow_hashref())) {
325 $dbh->do ($ctablecmd);
338 sub database_column_exists
{
339 my ($dbh, $table, $column) = @_;
341 my $sth = $dbh->prepare(
342 "SELECT column_name FROM information_schema.columns " .
343 "WHERE table_name = ? and column_name = ?");
344 $sth->execute(lc($table), lc($column));
345 my $res = $sth->fetchrow_hashref();
346 return defined($res);
365 $dbname = $default_db_name if !$dbname;
367 my $silent_opts = { outfunc
=> sub {}, errfunc
=> sub {} };
368 # make sure we have user 'root'
369 eval { postgres_admin_cmd
('createuser', $silent_opts, '-D', 'root'); };
370 # also create 'www-data' (and give it read-only access below)
371 eval { postgres_admin_cmd
('createuser', $silent_opts, '-I', '-D', 'www-data'); };
373 # use sql_ascii to avoid any character set conversions, and be compatible with
374 # older postgres versions (update from 8.1 must be possible)
376 $createdb->($dbname);
378 my $dbh = open_ruledb
($dbname);
380 # make sure 'www-data' can read all tables
381 $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\"");
385 CREATE TABLE Attribut
386 (Object_ID INTEGER NOT NULL,
387 Name VARCHAR(20) NOT NULL,
389 PRIMARY KEY (Object_ID, Name));
391 CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
395 ObjectType INTEGER NOT NULL,
396 Objectgroup_ID INTEGER NOT NULL,
400 CREATE TABLE Objectgroup
402 Name VARCHAR(255) NOT NULL,
403 Info VARCHAR(255) NULL,
404 Class VARCHAR(10) NOT NULL,
409 Name VARCHAR(255) NULL,
410 Priority INTEGER NOT NULL,
411 Active INTEGER NOT NULL DEFAULT 0,
412 Direction INTEGER NOT NULL DEFAULT 2,
413 Count INTEGER NOT NULL DEFAULT 0,
416 CREATE TABLE RuleGroup
417 (Objectgroup_ID INTEGER NOT NULL,
418 Rule_ID INTEGER NOT NULL,
419 Grouptype INTEGER NOT NULL,
420 PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
422 $cgreylist_ctablecmd;
424 $clusterinfo_ctablecmd;
426 $local_stat_ctablecmd;
428 $daily_stat_ctablecmd;
430 $domain_stat_ctablecmd;
434 $cmailstore_ctablecmd;
436 $cstatistic_ctablecmd;
438 $userprefs_ctablecmd;
440 $virusinfo_stat_ctablecmd;
447 sub cond_create_action_quarantine
{
450 my $dbh = $ruledb->{dbh
};
453 my $sth = $dbh->prepare(
454 "SELECT * FROM Objectgroup, Object " .
455 "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " .
456 "AND Object.objectgroup_id = Objectgroup.id");
458 my $otype = PMG
::RuleDB
::Quarantine
::otype
();
459 if ($sth->execute($otype, 'action') <= 0) {
460 my $obj = PMG
::RuleDB
::Quarantine-
>new ();
461 my $txt = decode_entities
(PMG
::RuleDB
::Quarantine-
>otype_text);
462 my $quarantine = $ruledb->create_group_with_obj
463 ($obj, $txt, 'Move to quarantine.');
468 sub cond_create_std_actions
{
471 cond_create_action_quarantine
($ruledb);
473 #cond_create_action_report_spam($ruledb);
480 my $dbh = $ruledb->{dbh
};
482 # make sure we do not use slow sequential scans when upgraing
483 # database (before analyze can gather statistics)
484 $dbh->do("set enable_seqscan = false");
487 'LocalStat', $local_stat_ctablecmd,
488 'DailyStat', $daily_stat_ctablecmd,
489 'DomainStat', $domain_stat_ctablecmd,
490 'StatInfo', $statinfo_ctablecmd,
491 'CMailStore', $cmailstore_ctablecmd,
492 'UserPrefs', $userprefs_ctablecmd,
493 'CGreylist', $cgreylist_ctablecmd,
494 'CStatistic', $cstatistic_ctablecmd,
495 'ClusterInfo', $clusterinfo_ctablecmd,
496 'VirusInfo', $virusinfo_stat_ctablecmd,
499 foreach my $table (keys %$tables) {
500 cond_create_dbtable
($dbh, $table, $tables->{$table});
503 cond_create_std_actions
($ruledb);
505 # upgrade tables here if necessary
506 if (!database_column_exists
($dbh, 'LocalStat', 'PregreetCount')) {
507 $dbh->do("ALTER TABLE LocalStat ADD COLUMN " .
508 "PregreetCount INTEGER DEFAULT 0 NOT NULL");
511 eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); };
515 # add missing TicketID to CMSReceivers
516 if (!database_column_exists
($dbh, 'CMSReceivers', 'TicketID')) {
519 $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq");
520 $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " .
521 "TicketID INTEGER NOT NULL " .
522 "DEFAULT nextval('cmsreceivers_ticketid_seq')");
523 $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " .
524 "TicketID DROP DEFAULT");
525 $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq");
534 # update obsolete content type names
536 $dbh->do("UPDATE Object " .
537 "SET value = 'content-type:application/java-vm' ".
538 "WHERE objecttype = 3003 " .
539 "AND value = 'content-type:application/x-java-vm';");
542 foreach my $table (keys %$tables) {
543 eval { $dbh->do("ANALYZE $table"); };
551 my ($ruledb, $reset, $testmode) = @_;
553 my $dbh = $ruledb->{dbh
};
556 # Greylist Objectgroup
557 my $greylistgroup = PMG
::RuleDB
::Group-
>new
558 ("GreyExclusion", "-", "greylist");
559 $ruledb->save_group ($greylistgroup);
562 # we do not touch greylist objects
563 my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
564 "objectgroup_id = objectgroup.id and class = 'greylist'";
566 $dbh->do ("DELETE FROM Rule; " .
567 "DELETE FROM RuleGroup; " .
568 "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
569 "DELETE FROM Object WHERE ID NOT IN ($glids); " .
570 "DELETE FROM Objectgroup WHERE class != 'greylist';");
576 my $obj = PMG
::RuleDB
::EMail-
>new ('nomail@fromthisdomain.com');
577 my $blacklist = $ruledb->create_group_with_obj(
578 $obj, 'Blacklist', 'Global blacklist');
581 $obj = PMG
::RuleDB
::EMail-
>new('mail@fromthisdomain.com');
582 my $whitelist = $ruledb->create_group_with_obj(
583 $obj, 'Whitelist', 'Global whitelist');
588 $obj = PMG
::RuleDB
::TimeFrame-
>new(8*60, 16*60);
589 my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
590 'Usual office hours');
595 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('image/.*');
596 my $img_content = $ruledb->create_group_with_obj(
597 $obj, 'Images', 'All kinds of graphic files');
600 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('audio/.*');
601 my $mm_content = $ruledb->create_group_with_obj(
602 $obj, 'Multimedia', 'Audio and Video');
604 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('video/.*');
605 $ruledb->group_add_object($mm_content, $obj);
608 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/vnd\.ms-excel');
609 my $office_content = $ruledb->create_group_with_obj(
610 $obj, 'Office Files', 'Common Office Files');
612 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
613 'application/vnd\.ms-powerpoint');
615 $ruledb->group_add_object($office_content, $obj);
617 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/msword');
618 $ruledb->group_add_object ($office_content, $obj);
620 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
621 'application/vnd\.openxmlformats-officedocument\..*');
622 $ruledb->group_add_object($office_content, $obj);
624 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
625 'application/vnd\.oasis\.opendocument\..*');
626 $ruledb->group_add_object($office_content, $obj);
628 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
629 'application/vnd\.stardivision\..*');
630 $ruledb->group_add_object($office_content, $obj);
632 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
633 'application/vnd\.sun\.xml\..*');
634 $ruledb->group_add_object($office_content, $obj);
637 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
638 'application/x-ms-dos-executable');
639 my $exe_content = $ruledb->create_group_with_obj(
640 $obj, 'Dangerous Content', 'executable files and partial messages');
642 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-java');
643 $ruledb->group_add_object($exe_content, $obj);
644 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/javascript');
645 $ruledb->group_add_object($exe_content, $obj);
646 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-executable');
647 $ruledb->group_add_object($exe_content, $obj);
648 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-ms-dos-executable');
649 $ruledb->group_add_object($exe_content, $obj);
650 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('message/partial');
651 $ruledb->group_add_object($exe_content, $obj);
652 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.(vbs|pif|lnk|shs|shb)');
653 $ruledb->group_add_object($exe_content, $obj);
654 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.\{.+\}');
655 $ruledb->group_add_object($exe_content, $obj);
658 $obj = PMG
::RuleDB
::Virus-
>new();
659 my $virus = $ruledb->create_group_with_obj(
660 $obj, 'Virus', 'Matches virus infected mail');
665 $obj = PMG
::RuleDB
::Spam-
>new(3);
666 my $spam3 = $ruledb->create_group_with_obj(
667 $obj, 'Spam (Level 3)', 'Matches possible spam mail');
669 $obj = PMG
::RuleDB
::Spam-
>new(5);
670 my $spam5 = $ruledb->create_group_with_obj(
671 $obj, 'Spam (Level 5)', 'Matches possible spam mail');
673 $obj = PMG
::RuleDB
::Spam-
>new(10);
674 my $spam10 = $ruledb->create_group_with_obj(
675 $obj, 'Spam (Level 10)', 'Matches possible spam mail');
680 $obj = PMG
::RuleDB
::ModField-
>new('X-SPAM-LEVEL', '__SPAM_INFO__');
681 my $mod_spam_level = $ruledb->create_group_with_obj(
682 $obj, 'Modify Spam Level',
683 'Mark mail as spam by adding a header tag.');
686 $obj = PMG
::RuleDB
::ModField-
>new('subject', 'SPAM: __SUBJECT__');
687 my $mod_spam_subject = $ruledb->create_group_with_obj(
688 $obj, 'Modify Spam Subject',
689 'Mark mail as spam by modifying the subject.');
691 # Remove matching attachments
692 $obj = PMG
::RuleDB
::Remove-
>new(0);
693 my $remove = $ruledb->create_group_with_obj(
694 $obj, 'Remove attachments', 'Remove matching attachments');
696 # Remove all attachments
697 $obj = PMG
::RuleDB
::Remove-
>new(1);
698 my $remove_all = $ruledb->create_group_with_obj(
699 $obj, 'Remove all attachments', 'Remove all attachments');
702 $obj = PMG
::RuleDB
::Accept-
>new();
703 my $accept = $ruledb->create_group_with_obj(
704 $obj, 'Accept', 'Accept mail for Delivery');
707 $obj = PMG
::RuleDB
::Block-
>new ();
708 my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
711 $obj = PMG
::RuleDB
::Quarantine-
>new();
712 my $quarantine = $ruledb->create_group_with_obj(
713 $obj, 'Quarantine', 'Move mail to quarantine');
716 $obj = PMG
::RuleDB
::Notify-
>new('__ADMIN__');
717 my $notify_admin = $ruledb->create_group_with_obj(
718 $obj, 'Notify Admin', 'Send notification');
721 $obj = PMG
::RuleDB
::Notify-
>new('__SENDER__');
722 my $notify_sender = $ruledb->create_group_with_obj(
723 $obj, 'Notify Sender', 'Send notification');
726 $obj = PMG
::RuleDB
::Disclaimer-
>new ();
727 my $add_discl = $ruledb->create_group_with_obj(
728 $obj, 'Disclaimer', 'Add Disclaimer');
730 # Attach original mail
731 #$obj = Proxmox::RuleDB::Attach->new ();
732 #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
733 # 'Attach Original Mail');
735 ####################### RULES ##################################
737 ## Block Dangerous Files
738 my $rule = PMG
::RuleDB
::Rule-
>new ('Block Dangerous Files', 93, 1, 0);
739 $ruledb->save_rule ($rule);
741 $ruledb->rule_add_what_group ($rule, $exe_content);
742 $ruledb->rule_add_action ($rule, $remove);
745 $rule = PMG
::RuleDB
::Rule-
>new ('Block Viruses', 96, 1, 0);
746 $ruledb->save_rule ($rule);
748 $ruledb->rule_add_what_group ($rule, $virus);
749 $ruledb->rule_add_action ($rule, $notify_admin);
752 $ruledb->rule_add_action ($rule, $block);
754 $ruledb->rule_add_action ($rule, $quarantine);
758 $rule = PMG
::RuleDB
::Rule-
>new ('Virus Alert', 96, 1, 1);
759 $ruledb->save_rule ($rule);
761 $ruledb->rule_add_what_group ($rule, $virus);
762 $ruledb->rule_add_action ($rule, $notify_sender);
763 $ruledb->rule_add_action ($rule, $notify_admin);
764 $ruledb->rule_add_action ($rule, $block);
767 $rule = PMG
::RuleDB
::Rule-
>new ('Blacklist', 98, 1, 0);
768 $ruledb->save_rule ($rule);
770 $ruledb->rule_add_from_group ($rule, $blacklist);
771 $ruledb->rule_add_action ($rule, $block);
775 $rule = PMG
::RuleDB
::Rule-
>new ('Modify Header', 90, 1, 0);
776 $ruledb->save_rule ($rule);
777 $ruledb->rule_add_action ($rule, $mod_spam_level);
781 $rule = PMG
::RuleDB
::Rule-
>new ('Whitelist', 85, 1, 0);
782 $ruledb->save_rule ($rule);
784 $ruledb->rule_add_from_group ($rule, $whitelist);
785 $ruledb->rule_add_action ($rule, $accept);
788 $rule = PMG
::RuleDB
::Rule-
>new ('Mark Spam', 80, 1, 0);
789 $ruledb->save_rule ($rule);
791 $ruledb->rule_add_what_group ($rule, $spam10);
792 $ruledb->rule_add_action ($rule, $mod_spam_level);
793 $ruledb->rule_add_action ($rule, $mod_spam_subject);
795 # Quarantine/Mark Spam (Level 3)
796 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
797 $ruledb->save_rule ($rule);
799 $ruledb->rule_add_what_group ($rule, $spam3);
800 $ruledb->rule_add_action ($rule, $mod_spam_subject);
801 $ruledb->rule_add_action ($rule, $quarantine);
802 #$ruledb->rule_add_action ($rule, $count_spam);
805 # Quarantine/Mark Spam (Level 5)
806 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0);
807 $ruledb->save_rule ($rule);
809 $ruledb->rule_add_what_group ($rule, $spam5);
810 $ruledb->rule_add_action ($rule, $mod_spam_subject);
811 $ruledb->rule_add_action ($rule, $quarantine);
813 ## Block Spam Level 10
814 $rule = PMG
::RuleDB
::Rule-
>new ('Block Spam (Level 10)', 82, 0, 0);
815 $ruledb->save_rule ($rule);
817 $ruledb->rule_add_what_group ($rule, $spam10);
818 $ruledb->rule_add_action ($rule, $block);
820 ## Block Outgoing Spam
821 $rule = PMG
::RuleDB
::Rule-
>new ('Block outgoing Spam', 70, 0, 1);
822 $ruledb->save_rule ($rule);
824 $ruledb->rule_add_what_group ($rule, $spam3);
825 $ruledb->rule_add_action ($rule, $notify_admin);
826 $ruledb->rule_add_action ($rule, $notify_sender);
827 $ruledb->rule_add_action ($rule, $block);
830 $rule = PMG
::RuleDB
::Rule-
>new ('Add Disclaimer', 60, 0, 1);
831 $ruledb->save_rule ($rule);
832 $ruledb->rule_add_action ($rule, $add_discl);
834 # Block Multimedia Files
835 $rule = PMG
::RuleDB
::Rule-
>new ('Block Multimedia Files', 87, 0, 2);
836 $ruledb->save_rule ($rule);
838 $ruledb->rule_add_what_group ($rule, $mm_content);
839 $ruledb->rule_add_action ($rule, $remove);
841 #$ruledb->rule_add_from_group ($rule, $anybody);
842 #$ruledb->rule_add_from_group ($rule, $trusted);
843 #$ruledb->rule_add_to_group ($rule, $anybody);
844 #$ruledb->rule_add_what_group ($rule, $ct_filter);
845 #$ruledb->rule_add_action ($rule, $add_discl);
846 #$ruledb->rule_add_action ($rule, $remove);
847 #$ruledb->rule_add_action ($rule, $bcc);
848 #$ruledb->rule_add_action ($rule, $storeq);
849 #$ruledb->rule_add_action ($rule, $accept);
851 cond_create_std_actions
($ruledb);
856 sub get_remote_time
{
859 my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
861 my $ctinfo = $sth->fetchrow_hashref();
864 return $ctinfo ?
$ctinfo->{ctime
} : 0;
868 my ($lcid, $database) = @_;
870 die "got unexpected cid for new master" if !$lcid;
875 $dbh = open_ruledb
($database);
879 print STDERR
"update quarantine database\n";
880 $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
881 "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
883 print STDERR
"update statistic database\n";
884 $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
885 "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
887 print STDERR
"update greylist database\n";
888 $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
890 print STDERR
"update localstat database\n";
891 $dbh->do ("UPDATE LocalStat SET CID = $lcid WHERE CID = 0;");
898 $dbh->rollback if $err;
905 sub purge_statistic_database
{
906 my ($dbh, $statlifetime) = @_;
908 return if $statlifetime <= 0;
910 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
911 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
912 my $start = $end - $statlifetime*86400;
914 # delete statistics older than $start
921 my $sth = $dbh->prepare("DELETE FROM CStatistic WHERE time < $start");
927 $sth = $dbh->prepare(
928 "DELETE FROM CReceivers WHERE NOT EXISTS " .
929 "(SELECT * FROM CStatistic WHERE CID = CStatistic_CID AND RID = CStatistic_RID)");
943 sub purge_quarantine_database
{
944 my ($dbh, $qtype, $lifetime) = @_;
946 my $spooldir = $PMG::MailQueue
::spooldir
;
948 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
949 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
950 my $start = $end - $lifetime*86400;
952 my $sth = $dbh->prepare(
953 "SELECT file FROM CMailStore WHERE time < $start AND QType = '$qtype'");
959 while (my $ref = $sth->fetchrow_hashref()) {
960 my $filename = "$spooldir/$ref->{file}";
961 $count++ if unlink($filename);
967 "DELETE FROM CMailStore WHERE time < $start AND QType = '$qtype';" .
968 "DELETE FROM CMSReceivers WHERE NOT EXISTS " .
969 "(SELECT * FROM CMailStore WHERE CID = CMailStore_CID AND RID = CMailStore_RID)");
974 sub get_quarantine_count
{
975 my ($dbh, $qtype) = @_;
977 # Note;: We try to estimate used disk space - each mail
978 # is stored in an extra file ...
982 my $sth = $dbh->prepare(
983 "SELECT count(ID) as count, sum (ceil((Bytes+$bs-1)/$bs)*$bs) / (1024*1024) as mbytes, " .
984 "avg(Bytes) as avgbytes, avg(Spamlevel) as avgspam " .
985 "FROM CMailStore WHERE QType = ?");
987 $sth->execute($qtype);
989 my $ref = $sth->fetchrow_hashref();
993 foreach my $k (qw(count mbytes avgbytes avgspam)) {
1001 my ($ldb, $rdb, $table) = @_;
1003 $table = lc($table);
1005 my $sth = $ldb->column_info(undef, undef, $table, undef);
1006 my $attrs = $sth->fetchall_arrayref({});
1009 foreach my $ref (@$attrs) {
1010 push @col_arr, $ref->{COLUMN_NAME
};
1015 my $cols = join(', ', @col_arr);
1016 $cols || die "unable to fetch column definitions of table '$table' : ERROR";
1018 $rdb->do("COPY $table ($cols) TO STDOUT");
1023 $ldb->do("COPY $table ($cols) FROM stdin");
1025 while ($rdb->pg_getcopydata($data) >= 0) {
1026 $ldb->pg_putcopydata($data);
1029 $ldb->pg_putcopyend();
1032 $ldb->pg_putcopyend();
1037 sub copy_selected_data
{
1038 my ($dbh, $select_sth, $table, $attrs, $callback) = @_;
1042 my $insert_sth = $dbh->prepare(
1043 "INSERT INTO ${table}(" . join(',', @$attrs) . ') ' .
1044 'VALUES (' . join(',', ('?') x
scalar(@$attrs)) . ')');
1046 while (my $ref = $select_sth->fetchrow_hashref()) {
1047 $callback->($ref) if $callback;
1049 $insert_sth->execute(map { $ref->{$_} } @$attrs);
1055 sub update_master_clusterinfo
{
1056 my ($clientcid) = @_;
1058 my $dbh = open_ruledb
();
1060 $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
1062 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1064 foreach my $table (@mt) {
1065 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
1066 "EXTRACT(EPOCH FROM now())");
1070 sub update_client_clusterinfo
{
1071 my ($mastercid) = @_;
1073 my $dbh = open_ruledb
();
1075 $dbh->do ("DELETE FROM StatInfo"); # not needed at node
1077 $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
1079 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
1080 "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
1082 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
1083 "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
1085 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1087 foreach my $table (@mt) {
1088 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
1089 "COALESCE (max (mtime), 0) FROM $table");
1093 sub create_clusterinfo_default
{
1094 my ($dbh, $rcid, $name, $ivalue, $svalue) = @_;
1096 my $sth = $dbh->prepare("SELECT * FROM ClusterInfo WHERE CID = ? AND Name = ?");
1097 $sth->execute($rcid, $name);
1098 if (!$sth->fetchrow_hashref()) {
1099 $dbh->do("INSERT INTO ClusterInfo (CID, Name, IValue, SValue) " .
1100 "VALUES (?, ?, ?, ?)", undef,
1101 $rcid, $name, $ivalue, $svalue);
1106 sub read_int_clusterinfo
{
1107 my ($dbh, $rcid, $name) = @_;
1109 my $sth = $dbh->prepare(
1110 "SELECT ivalue as value FROM ClusterInfo " .
1111 "WHERE cid = ? AND NAME = ?");
1112 $sth->execute($rcid, $name);
1113 my $cinfo = $sth->fetchrow_hashref();
1116 return $cinfo->{value
};
1119 sub write_maxint_clusterinfo
{
1120 my ($dbh, $rcid, $name, $value) = @_;
1122 $dbh->do("UPDATE ClusterInfo SET ivalue = GREATEST(ivalue, ?) " .
1123 "WHERE cid = ? AND name = ?", undef,
1124 $value, $rcid, $name);
1130 my $ni = $cinfo->{master
};
1132 die "no master defined - unable to sync data from master\n" if !$ni;
1134 my $master_ip = $ni->{ip
};
1135 my $master_cid = $ni->{cid
};
1136 my $master_name = $ni->{name
};
1138 my $fn = "/tmp/masterdb$$.tar";
1141 my $dbname = $default_db_name;
1144 print STDERR
"copying master database from '${master_ip}'\n";
1146 open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
1148 my $cmd = ['/usr/bin/ssh', '-o', 'BatchMode=yes',
1149 '-o', "HostKeyAlias=${master_name}", $master_ip,
1150 'pg_dump', $dbname, '-F', 'c' ];
1152 PVE
::Tools
::run_command
($cmd, output
=> '>&' . fileno($fh));
1158 print STDERR
"copying master database finished (got $size bytes)\n";
1160 print STDERR
"delete local database\n";
1162 postgres_admin_cmd
('dropdb', undef, $dbname , '--if-exists');
1164 print STDERR
"create new local database\n";
1166 $createdb->($dbname);
1168 print STDERR
"insert received data into local database\n";
1174 if ($line =~ m/restoring data for table \"(.+)\"/) {
1175 print STDERR
"restoring table $1\n";
1176 } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
1177 $mess = "creating indexes";
1178 print STDERR
"$mess\n";
1185 errmsg
=> "pg_restore failed"
1188 postgres_admin_cmd
('pg_restore', $opts, '-d', $dbname, '-v', $fn);
1190 print STDERR
"run analyze to speed up database queries\n";
1192 postgres_admin_cmd
('psql', { input
=> 'analyze;' }, $dbname);
1194 update_client_clusterinfo
($master_cid);
1204 sub cluster_sync_status
{
1211 foreach my $ni (values %{$cinfo->{ids
}}) {
1212 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1213 $minmtime->{$ni->{cid
}} = 0;
1217 $dbh = open_ruledb
();
1219 my $sth = $dbh->prepare(
1220 "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " .
1221 "WHERE name = 'lastsync' AND ivalue > 0 " .
1226 while (my $info = $sth->fetchrow_hashref()) {
1227 foreach my $ni (values %{$cinfo->{ids
}}) {
1228 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1229 if ($ni->{cid
} == $info->{cid
}) { # node exists
1230 $minmtime->{$ni->{cid
}} = $info->{minmtime
};
1239 $dbh->disconnect() if $dbh;
1241 syslog
('err', $err) if $err;
1246 sub load_mail_data
{
1247 my ($dbh, $cid, $rid, $ticketid) = @_;
1249 my $sth = $dbh->prepare(
1250 "SELECT * FROM CMailStore, CMSReceivers WHERE " .
1251 "CID = ? AND RID = ? AND TicketID = ? AND " .
1252 "CID = CMailStore_CID AND RID = CMailStore_RID");
1253 $sth->execute($cid, $rid, $ticketid);
1255 my $res = $sth->fetchrow_hashref();
1259 die "no such mail (C${cid}R${rid}T${ticketid})\n" if !defined($res);
1267 # Note: we pass $ruledb when modifying SMTP whitelist
1268 if (defined($ruledb)) {
1270 my $rulecache = PMG
::RuleCache-
>new($ruledb);
1271 PMG
::Config
::rewrite_postfix_whitelist
($rulecache);
1274 warn "problems updating SMTP whitelist - $err";
1278 my $pid_file = '/var/run/pmg-smtp-filter.pid';
1279 my $pid = PVE
::Tools
::file_read_firstline
($pid_file);
1283 return 0 if $pid !~ m/^(\d+)$/;
1284 $pid = $1; # untaint
1286 return kill (10, $pid); # send SIGUSR1