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);
360 $dbname = $default_db_name if !$dbname;
362 my $silent_opts = { outfunc
=> sub {}, errfunc
=> sub {} };
363 # make sure we have user 'root'
364 eval { postgres_admin_cmd
('createuser', $silent_opts, '-D', 'root'); };
365 # also create 'www-data' (and give it read-only access below)
366 eval { postgres_admin_cmd
('createuser', $silent_opts, '-I', '-D', 'www-data'); };
368 # use sql_ascii to avoid any character set conversions, and be compatible with
369 # older postgres versions (update from 8.1 must be possible)
371 $createdb->($dbname);
373 my $dbh = open_ruledb
($dbname);
375 # make sure 'www-data' can read all tables
376 $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\"");
380 CREATE TABLE Attribut
381 (Object_ID INTEGER NOT NULL,
382 Name VARCHAR(20) NOT NULL,
384 PRIMARY KEY (Object_ID, Name));
386 CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
390 ObjectType INTEGER NOT NULL,
391 Objectgroup_ID INTEGER NOT NULL,
395 CREATE TABLE Objectgroup
397 Name VARCHAR(255) NOT NULL,
398 Info VARCHAR(255) NULL,
399 Class VARCHAR(10) NOT NULL,
404 Name VARCHAR(255) NULL,
405 Priority INTEGER NOT NULL,
406 Active INTEGER NOT NULL DEFAULT 0,
407 Direction INTEGER NOT NULL DEFAULT 2,
408 Count INTEGER NOT NULL DEFAULT 0,
411 CREATE TABLE RuleGroup
412 (Objectgroup_ID INTEGER NOT NULL,
413 Rule_ID INTEGER NOT NULL,
414 Grouptype INTEGER NOT NULL,
415 PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
417 $cgreylist_ctablecmd;
419 $clusterinfo_ctablecmd;
421 $local_stat_ctablecmd;
423 $daily_stat_ctablecmd;
425 $domain_stat_ctablecmd;
429 $cmailstore_ctablecmd;
431 $cstatistic_ctablecmd;
433 $userprefs_ctablecmd;
435 $virusinfo_stat_ctablecmd;
442 sub cond_create_action_quarantine
{
445 my $dbh = $ruledb->{dbh
};
448 my $sth = $dbh->prepare(
449 "SELECT * FROM Objectgroup, Object " .
450 "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " .
451 "AND Object.objectgroup_id = Objectgroup.id");
453 my $otype = PMG
::RuleDB
::Quarantine
::otype
();
454 if ($sth->execute($otype, 'action') <= 0) {
455 my $obj = PMG
::RuleDB
::Quarantine-
>new ();
456 my $txt = decode_entities
(PMG
::RuleDB
::Quarantine-
>otype_text);
457 my $quarantine = $ruledb->create_group_with_obj
458 ($obj, $txt, 'Move to quarantine.');
463 sub cond_create_std_actions
{
466 cond_create_action_quarantine
($ruledb);
468 #cond_create_action_report_spam($ruledb);
475 my $dbh = $ruledb->{dbh
};
477 # make sure we do not use slow sequential scans when upgraing
478 # database (before analyze can gather statistics)
479 $dbh->do("set enable_seqscan = false");
482 'LocalStat', $local_stat_ctablecmd,
483 'DailyStat', $daily_stat_ctablecmd,
484 'DomainStat', $domain_stat_ctablecmd,
485 'StatInfo', $statinfo_ctablecmd,
486 'CMailStore', $cmailstore_ctablecmd,
487 'UserPrefs', $userprefs_ctablecmd,
488 'CGreylist', $cgreylist_ctablecmd,
489 'CStatistic', $cstatistic_ctablecmd,
490 'ClusterInfo', $clusterinfo_ctablecmd,
491 'VirusInfo', $virusinfo_stat_ctablecmd,
494 foreach my $table (keys %$tables) {
495 cond_create_dbtable
($dbh, $table, $tables->{$table});
498 cond_create_std_actions
($ruledb);
500 # upgrade tables here if necessary
501 if (!database_column_exists
($dbh, 'LocalStat', 'PregreetCount')) {
502 $dbh->do("ALTER TABLE LocalStat ADD COLUMN " .
503 "PregreetCount INTEGER DEFAULT 0 NOT NULL");
506 eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); };
510 # add missing TicketID to CMSReceivers
511 if (!database_column_exists
($dbh, 'CMSReceivers', 'TicketID')) {
514 $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq");
515 $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " .
516 "TicketID INTEGER NOT NULL " .
517 "DEFAULT nextval('cmsreceivers_ticketid_seq')");
518 $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " .
519 "TicketID DROP DEFAULT");
520 $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq");
529 # update obsolete content type names
531 $dbh->do("UPDATE Object " .
532 "SET value = 'content-type:application/java-vm' ".
533 "WHERE objecttype = 3003 " .
534 "AND value = 'content-type:application/x-java-vm';");
537 foreach my $table (keys %$tables) {
538 eval { $dbh->do("ANALYZE $table"); };
546 my ($ruledb, $reset, $testmode) = @_;
548 my $dbh = $ruledb->{dbh
};
551 # Greylist Objectgroup
552 my $greylistgroup = PMG
::RuleDB
::Group-
>new
553 ("GreyExclusion", "-", "greylist");
554 $ruledb->save_group ($greylistgroup);
557 # we do not touch greylist objects
558 my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
559 "objectgroup_id = objectgroup.id and class = 'greylist'";
561 $dbh->do ("DELETE FROM Rule; " .
562 "DELETE FROM RuleGroup; " .
563 "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
564 "DELETE FROM Object WHERE ID NOT IN ($glids); " .
565 "DELETE FROM Objectgroup WHERE class != 'greylist';");
571 my $obj = PMG
::RuleDB
::EMail-
>new ('nomail@fromthisdomain.com');
572 my $blacklist = $ruledb->create_group_with_obj(
573 $obj, 'Blacklist', 'Global blacklist');
576 $obj = PMG
::RuleDB
::EMail-
>new('mail@fromthisdomain.com');
577 my $whitelist = $ruledb->create_group_with_obj(
578 $obj, 'Whitelist', 'Global whitelist');
583 $obj = PMG
::RuleDB
::TimeFrame-
>new(8*60, 16*60);
584 my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
585 'Usual office hours');
590 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('image/.*');
591 my $img_content = $ruledb->create_group_with_obj(
592 $obj, 'Images', 'All kinds of graphic files');
595 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('audio/.*');
596 my $mm_content = $ruledb->create_group_with_obj(
597 $obj, 'Multimedia', 'Audio and Video');
599 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('video/.*');
600 $ruledb->group_add_object($mm_content, $obj);
603 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/vnd\.ms-excel');
604 my $office_content = $ruledb->create_group_with_obj(
605 $obj, 'Office Files', 'Common Office Files');
607 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
608 'application/vnd\.ms-powerpoint');
610 $ruledb->group_add_object($office_content, $obj);
612 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/msword');
613 $ruledb->group_add_object ($office_content, $obj);
615 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
616 'application/vnd\.openxmlformats-officedocument\..*');
617 $ruledb->group_add_object($office_content, $obj);
619 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
620 'application/vnd\.oasis\.opendocument\..*');
621 $ruledb->group_add_object($office_content, $obj);
623 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
624 'application/vnd\.stardivision\..*');
625 $ruledb->group_add_object($office_content, $obj);
627 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
628 'application/vnd\.sun\.xml\..*');
629 $ruledb->group_add_object($office_content, $obj);
632 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
633 'application/x-ms-dos-executable');
634 my $exe_content = $ruledb->create_group_with_obj(
635 $obj, 'Dangerous Content', 'executable files and partial messages');
637 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-java');
638 $ruledb->group_add_object($exe_content, $obj);
639 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/javascript');
640 $ruledb->group_add_object($exe_content, $obj);
641 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-executable');
642 $ruledb->group_add_object($exe_content, $obj);
643 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-ms-dos-executable');
644 $ruledb->group_add_object($exe_content, $obj);
645 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('message/partial');
646 $ruledb->group_add_object($exe_content, $obj);
647 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.(vbs|pif|lnk|shs|shb)');
648 $ruledb->group_add_object($exe_content, $obj);
649 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.\{.+\}');
650 $ruledb->group_add_object($exe_content, $obj);
653 $obj = PMG
::RuleDB
::Virus-
>new();
654 my $virus = $ruledb->create_group_with_obj(
655 $obj, 'Virus', 'Matches virus infected mail');
660 $obj = PMG
::RuleDB
::Spam-
>new(3);
661 my $spam3 = $ruledb->create_group_with_obj(
662 $obj, 'Spam (Level 3)', 'Matches possible spam mail');
664 $obj = PMG
::RuleDB
::Spam-
>new(5);
665 my $spam5 = $ruledb->create_group_with_obj(
666 $obj, 'Spam (Level 5)', 'Matches possible spam mail');
668 $obj = PMG
::RuleDB
::Spam-
>new(10);
669 my $spam10 = $ruledb->create_group_with_obj(
670 $obj, 'Spam (Level 10)', 'Matches possible spam mail');
675 $obj = PMG
::RuleDB
::ModField-
>new('X-SPAM-LEVEL', '__SPAM_INFO__');
676 my $mod_spam_level = $ruledb->create_group_with_obj(
677 $obj, 'Modify Spam Level',
678 'Mark mail as spam by adding a header tag.');
681 $obj = PMG
::RuleDB
::ModField-
>new('subject', 'SPAM: __SUBJECT__');
682 my $mod_spam_subject = $ruledb->create_group_with_obj(
683 $obj, 'Modify Spam Subject',
684 'Mark mail as spam by modifying the subject.');
686 # Remove matching attachments
687 $obj = PMG
::RuleDB
::Remove-
>new(0);
688 my $remove = $ruledb->create_group_with_obj(
689 $obj, 'Remove attachments', 'Remove matching attachments');
691 # Remove all attachments
692 $obj = PMG
::RuleDB
::Remove-
>new(1);
693 my $remove_all = $ruledb->create_group_with_obj(
694 $obj, 'Remove all attachments', 'Remove all attachments');
697 $obj = PMG
::RuleDB
::Accept-
>new();
698 my $accept = $ruledb->create_group_with_obj(
699 $obj, 'Accept', 'Accept mail for Delivery');
702 $obj = PMG
::RuleDB
::Block-
>new ();
703 my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
706 $obj = PMG
::RuleDB
::Quarantine-
>new();
707 my $quarantine = $ruledb->create_group_with_obj(
708 $obj, 'Quarantine', 'Move mail to quarantine');
711 $obj = PMG
::RuleDB
::Notify-
>new('__ADMIN__');
712 my $notify_admin = $ruledb->create_group_with_obj(
713 $obj, 'Notify Admin', 'Send notification');
716 $obj = PMG
::RuleDB
::Notify-
>new('__SENDER__');
717 my $notify_sender = $ruledb->create_group_with_obj(
718 $obj, 'Notify Sender', 'Send notification');
721 $obj = PMG
::RuleDB
::Disclaimer-
>new ();
722 my $add_discl = $ruledb->create_group_with_obj(
723 $obj, 'Disclaimer', 'Add Disclaimer');
725 # Attach original mail
726 #$obj = Proxmox::RuleDB::Attach->new ();
727 #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
728 # 'Attach Original Mail');
730 ####################### RULES ##################################
732 ## Block Dangerous Files
733 my $rule = PMG
::RuleDB
::Rule-
>new ('Block Dangerous Files', 93, 1, 0);
734 $ruledb->save_rule ($rule);
736 $ruledb->rule_add_what_group ($rule, $exe_content);
737 $ruledb->rule_add_action ($rule, $remove);
740 $rule = PMG
::RuleDB
::Rule-
>new ('Block Viruses', 96, 1, 0);
741 $ruledb->save_rule ($rule);
743 $ruledb->rule_add_what_group ($rule, $virus);
744 $ruledb->rule_add_action ($rule, $notify_admin);
747 $ruledb->rule_add_action ($rule, $block);
749 $ruledb->rule_add_action ($rule, $quarantine);
753 $rule = PMG
::RuleDB
::Rule-
>new ('Virus Alert', 96, 1, 1);
754 $ruledb->save_rule ($rule);
756 $ruledb->rule_add_what_group ($rule, $virus);
757 $ruledb->rule_add_action ($rule, $notify_sender);
758 $ruledb->rule_add_action ($rule, $notify_admin);
759 $ruledb->rule_add_action ($rule, $block);
762 $rule = PMG
::RuleDB
::Rule-
>new ('Blacklist', 98, 1, 0);
763 $ruledb->save_rule ($rule);
765 $ruledb->rule_add_from_group ($rule, $blacklist);
766 $ruledb->rule_add_action ($rule, $block);
770 $rule = PMG
::RuleDB
::Rule-
>new ('Modify Header', 90, 1, 0);
771 $ruledb->save_rule ($rule);
772 $ruledb->rule_add_action ($rule, $mod_spam_level);
776 $rule = PMG
::RuleDB
::Rule-
>new ('Whitelist', 85, 1, 0);
777 $ruledb->save_rule ($rule);
779 $ruledb->rule_add_from_group ($rule, $whitelist);
780 $ruledb->rule_add_action ($rule, $accept);
783 $rule = PMG
::RuleDB
::Rule-
>new ('Mark Spam', 80, 1, 0);
784 $ruledb->save_rule ($rule);
786 $ruledb->rule_add_what_group ($rule, $spam10);
787 $ruledb->rule_add_action ($rule, $mod_spam_level);
788 $ruledb->rule_add_action ($rule, $mod_spam_subject);
790 # Quarantine/Mark Spam (Level 3)
791 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
792 $ruledb->save_rule ($rule);
794 $ruledb->rule_add_what_group ($rule, $spam3);
795 $ruledb->rule_add_action ($rule, $mod_spam_subject);
796 $ruledb->rule_add_action ($rule, $quarantine);
797 #$ruledb->rule_add_action ($rule, $count_spam);
800 # Quarantine/Mark Spam (Level 5)
801 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0);
802 $ruledb->save_rule ($rule);
804 $ruledb->rule_add_what_group ($rule, $spam5);
805 $ruledb->rule_add_action ($rule, $mod_spam_subject);
806 $ruledb->rule_add_action ($rule, $quarantine);
808 ## Block Spam Level 10
809 $rule = PMG
::RuleDB
::Rule-
>new ('Block Spam (Level 10)', 82, 0, 0);
810 $ruledb->save_rule ($rule);
812 $ruledb->rule_add_what_group ($rule, $spam10);
813 $ruledb->rule_add_action ($rule, $block);
815 ## Block Outgoing Spam
816 $rule = PMG
::RuleDB
::Rule-
>new ('Block outgoing Spam', 70, 0, 1);
817 $ruledb->save_rule ($rule);
819 $ruledb->rule_add_what_group ($rule, $spam3);
820 $ruledb->rule_add_action ($rule, $notify_admin);
821 $ruledb->rule_add_action ($rule, $notify_sender);
822 $ruledb->rule_add_action ($rule, $block);
825 $rule = PMG
::RuleDB
::Rule-
>new ('Add Disclaimer', 60, 0, 1);
826 $ruledb->save_rule ($rule);
827 $ruledb->rule_add_action ($rule, $add_discl);
829 # Block Multimedia Files
830 $rule = PMG
::RuleDB
::Rule-
>new ('Block Multimedia Files', 87, 0, 2);
831 $ruledb->save_rule ($rule);
833 $ruledb->rule_add_what_group ($rule, $mm_content);
834 $ruledb->rule_add_action ($rule, $remove);
836 #$ruledb->rule_add_from_group ($rule, $anybody);
837 #$ruledb->rule_add_from_group ($rule, $trusted);
838 #$ruledb->rule_add_to_group ($rule, $anybody);
839 #$ruledb->rule_add_what_group ($rule, $ct_filter);
840 #$ruledb->rule_add_action ($rule, $add_discl);
841 #$ruledb->rule_add_action ($rule, $remove);
842 #$ruledb->rule_add_action ($rule, $bcc);
843 #$ruledb->rule_add_action ($rule, $storeq);
844 #$ruledb->rule_add_action ($rule, $accept);
846 cond_create_std_actions
($ruledb);
851 sub get_remote_time
{
854 my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
856 my $ctinfo = $sth->fetchrow_hashref();
859 return $ctinfo ?
$ctinfo->{ctime
} : 0;
863 my ($lcid, $database) = @_;
865 die "got unexpected cid for new master" if !$lcid;
870 $dbh = open_ruledb
($database);
874 print STDERR
"update quarantine database\n";
875 $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
876 "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
878 print STDERR
"update statistic database\n";
879 $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
880 "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
882 print STDERR
"update greylist database\n";
883 $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
885 print STDERR
"update localstat database\n";
886 $dbh->do ("UPDATE LocalStat SET CID = $lcid WHERE CID = 0;");
893 $dbh->rollback if $err;
900 sub purge_statistic_database
{
901 my ($dbh, $statlifetime) = @_;
903 return if $statlifetime <= 0;
905 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
906 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
907 my $start = $end - $statlifetime*86400;
909 # delete statistics older than $start
916 my $sth = $dbh->prepare("DELETE FROM CStatistic WHERE time < $start");
922 $sth = $dbh->prepare(
923 "DELETE FROM CReceivers WHERE NOT EXISTS " .
924 "(SELECT * FROM CStatistic WHERE CID = CStatistic_CID AND RID = CStatistic_RID)");
938 sub purge_quarantine_database
{
939 my ($dbh, $qtype, $lifetime) = @_;
941 my $spooldir = $PMG::MailQueue
::spooldir
;
943 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
944 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
945 my $start = $end - $lifetime*86400;
947 my $sth = $dbh->prepare(
948 "SELECT file FROM CMailStore WHERE time < $start AND QType = '$qtype'");
954 while (my $ref = $sth->fetchrow_hashref()) {
955 my $filename = "$spooldir/$ref->{file}";
956 $count++ if unlink($filename);
962 "DELETE FROM CMailStore WHERE time < $start AND QType = '$qtype';" .
963 "DELETE FROM CMSReceivers WHERE NOT EXISTS " .
964 "(SELECT * FROM CMailStore WHERE CID = CMailStore_CID AND RID = CMailStore_RID)");
969 sub get_quarantine_count
{
970 my ($dbh, $qtype) = @_;
972 # Note;: We try to estimate used disk space - each mail
973 # is stored in an extra file ...
977 my $sth = $dbh->prepare(
978 "SELECT count(ID) as count, sum (ceil((Bytes+$bs-1)/$bs)*$bs) / (1024*1024) as mbytes, " .
979 "avg(Bytes) as avgbytes, avg(Spamlevel) as avgspam " .
980 "FROM CMailStore WHERE QType = ?");
982 $sth->execute($qtype);
984 my $ref = $sth->fetchrow_hashref();
988 foreach my $k (qw(count mbytes avgbytes avgspam)) {
996 my ($ldb, $rdb, $table) = @_;
1000 my $sth = $ldb->column_info(undef, undef, $table, undef);
1001 my $attrs = $sth->fetchall_arrayref({});
1004 foreach my $ref (@$attrs) {
1005 push @col_arr, $ref->{COLUMN_NAME
};
1010 my $cols = join(', ', @col_arr);
1011 $cols || die "unable to fetch column definitions of table '$table' : ERROR";
1013 $rdb->do("COPY $table ($cols) TO STDOUT");
1018 $ldb->do("COPY $table ($cols) FROM stdin");
1020 while ($rdb->pg_getcopydata($data) >= 0) {
1021 $ldb->pg_putcopydata($data);
1024 $ldb->pg_putcopyend();
1027 $ldb->pg_putcopyend();
1032 sub copy_selected_data
{
1033 my ($dbh, $select_sth, $table, $attrs, $callback) = @_;
1037 my $insert_sth = $dbh->prepare(
1038 "INSERT INTO ${table}(" . join(',', @$attrs) . ') ' .
1039 'VALUES (' . join(',', ('?') x
scalar(@$attrs)) . ')');
1041 while (my $ref = $select_sth->fetchrow_hashref()) {
1042 $callback->($ref) if $callback;
1044 $insert_sth->execute(map { $ref->{$_} } @$attrs);
1050 sub update_master_clusterinfo
{
1051 my ($clientcid) = @_;
1053 my $dbh = open_ruledb
();
1055 $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
1057 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1059 foreach my $table (@mt) {
1060 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
1061 "EXTRACT(EPOCH FROM now())");
1065 sub update_client_clusterinfo
{
1066 my ($mastercid) = @_;
1068 my $dbh = open_ruledb
();
1070 $dbh->do ("DELETE FROM StatInfo"); # not needed at node
1072 $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
1074 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
1075 "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
1077 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
1078 "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
1080 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1082 foreach my $table (@mt) {
1083 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
1084 "COALESCE (max (mtime), 0) FROM $table");
1088 sub create_clusterinfo_default
{
1089 my ($dbh, $rcid, $name, $ivalue, $svalue) = @_;
1091 my $sth = $dbh->prepare("SELECT * FROM ClusterInfo WHERE CID = ? AND Name = ?");
1092 $sth->execute($rcid, $name);
1093 if (!$sth->fetchrow_hashref()) {
1094 $dbh->do("INSERT INTO ClusterInfo (CID, Name, IValue, SValue) " .
1095 "VALUES (?, ?, ?, ?)", undef,
1096 $rcid, $name, $ivalue, $svalue);
1101 sub read_int_clusterinfo
{
1102 my ($dbh, $rcid, $name) = @_;
1104 my $sth = $dbh->prepare(
1105 "SELECT ivalue as value FROM ClusterInfo " .
1106 "WHERE cid = ? AND NAME = ?");
1107 $sth->execute($rcid, $name);
1108 my $cinfo = $sth->fetchrow_hashref();
1111 return $cinfo->{value
};
1114 sub write_maxint_clusterinfo
{
1115 my ($dbh, $rcid, $name, $value) = @_;
1117 $dbh->do("UPDATE ClusterInfo SET ivalue = GREATEST(ivalue, ?) " .
1118 "WHERE cid = ? AND name = ?", undef,
1119 $value, $rcid, $name);
1125 my $ni = $cinfo->{master
};
1127 die "no master defined - unable to sync data from master\n" if !$ni;
1129 my $master_ip = $ni->{ip
};
1130 my $master_cid = $ni->{cid
};
1131 my $master_name = $ni->{name
};
1133 my $fn = "/tmp/masterdb$$.tar";
1136 my $dbname = $default_db_name;
1139 print STDERR
"copying master database from '${master_ip}'\n";
1141 open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
1143 my $cmd = ['/usr/bin/ssh', '-o', 'BatchMode=yes',
1144 '-o', "HostKeyAlias=${master_name}", $master_ip,
1145 'pg_dump', $dbname, '-F', 'c' ];
1147 PVE
::Tools
::run_command
($cmd, output
=> '>&' . fileno($fh));
1153 print STDERR
"copying master database finished (got $size bytes)\n";
1155 print STDERR
"delete local database\n";
1157 postgres_admin_cmd
('dropdb', undef, $dbname , '--if-exists');
1159 print STDERR
"create new local database\n";
1161 $createdb->($dbname);
1163 print STDERR
"insert received data into local database\n";
1169 if ($line =~ m/restoring data for table \"(.+)\"/) {
1170 print STDERR
"restoring table $1\n";
1171 } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
1172 $mess = "creating indexes";
1173 print STDERR
"$mess\n";
1180 errmsg
=> "pg_restore failed"
1183 postgres_admin_cmd
('pg_restore', $opts, '-d', $dbname, '-v', $fn);
1185 print STDERR
"run analyze to speed up database queries\n";
1187 postgres_admin_cmd
('psql', { input
=> 'analyze;' }, $dbname);
1189 update_client_clusterinfo
($master_cid);
1199 sub cluster_sync_status
{
1206 foreach my $ni (values %{$cinfo->{ids
}}) {
1207 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1208 $minmtime->{$ni->{cid
}} = 0;
1212 $dbh = open_ruledb
();
1214 my $sth = $dbh->prepare(
1215 "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " .
1216 "WHERE name = 'lastsync' AND ivalue > 0 " .
1221 while (my $info = $sth->fetchrow_hashref()) {
1222 foreach my $ni (values %{$cinfo->{ids
}}) {
1223 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1224 if ($ni->{cid
} == $info->{cid
}) { # node exists
1225 $minmtime->{$ni->{cid
}} = $info->{minmtime
};
1234 $dbh->disconnect() if $dbh;
1236 syslog
('err', $err) if $err;
1241 sub load_mail_data
{
1242 my ($dbh, $cid, $rid, $ticketid) = @_;
1244 my $sth = $dbh->prepare(
1245 "SELECT * FROM CMailStore, CMSReceivers WHERE " .
1246 "CID = ? AND RID = ? AND TicketID = ? AND " .
1247 "CID = CMailStore_CID AND RID = CMailStore_RID");
1248 $sth->execute($cid, $rid, $ticketid);
1250 my $res = $sth->fetchrow_hashref();
1254 die "no such mail (C${cid}R${rid}T${ticketid})\n" if !defined($res);
1262 # Note: we pass $ruledb when modifying SMTP whitelist
1263 if (defined($ruledb)) {
1265 my $rulecache = PMG
::RuleCache-
>new($ruledb);
1266 PMG
::Config
::rewrite_postfix_whitelist
($rulecache);
1269 warn "problems updating SMTP whitelist - $err";
1273 my $pid_file = '/var/run/pmg-smtp-filter.pid';
1274 my $pid = PVE
::Tools
::file_read_firstline
($pid_file);
1278 return 0 if $pid !~ m/^(\d+)$/;
1279 $pid = $1; # untaint
1281 return kill (10, $pid); # send SIGUSR1