6 use POSIX
":sys_wait_h";
7 use POSIX
qw(:signal_h getuid);
18 use PMG
::Utils
qw(postgres_admin_cmd);
20 our $default_db_name = "Proxmox_ruledb";
22 our $cgreylist_merge_sql =
23 'INSERT INTO CGREYLIST (IPNet,Host,Sender,Receiver,Instance,RCTime,' .
24 'ExTime,Delay,Blocked,Passed,MTime,CID) ' .
25 'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ' .
26 'ON CONFLICT (IPNet,Sender,Receiver) DO UPDATE SET ' .
27 'Host = CASE WHEN CGREYLIST.MTime >= excluded.MTime THEN CGREYLIST.Host ELSE excluded.Host END,' .
28 'CID = GREATEST(CGREYLIST.CID, excluded.CID), RCTime = LEAST(CGREYLIST.RCTime, excluded.RCTime),' .
29 'ExTime = GREATEST(CGREYLIST.ExTime, excluded.ExTime),' .
30 'Delay = GREATEST(CGREYLIST.Delay, excluded.Delay),' .
31 'Blocked = GREATEST(CGREYLIST.Blocked, excluded.Blocked),' .
32 'Passed = GREATEST(CGREYLIST.Passed, excluded.Passed)';
35 my ($database, $host, $port) = @_;
39 $database //= $default_db_name;
43 # Note: pmgtunnel uses UDP sockets inside directory '/run/pmgtunnel',
44 # and the cluster 'cid' as port number. You can connect to the
45 # socket with: host => /run/pmgtunnel, port => $cid
47 my $dsn = "dbi:Pg:dbname=$database;host=$host;port=$port;";
50 # only low level alarm interface works for DBI->connect
51 my $mask = POSIX
::SigSet-
>new(SIGALRM
);
52 my $action = POSIX
::SigAction-
>new(sub { die "connect timeout\n" }, $mask);
53 my $oldaction = POSIX
::SigAction-
>new();
54 sigaction
(SIGALRM
, $action, $oldaction);
60 $rdb = DBI-
>connect($dsn, 'root', undef,
61 { PrintError
=> 0, RaiseError
=> 1 });
65 sigaction
(SIGALRM
, $oldaction); # restore original handler
71 my $dsn = "DBI:Pg:dbname=$database;host=/var/run/postgresql;port=$port";
73 my $dbh = DBI-
>connect($dsn, $> == 0 ?
'root' : 'www-data', undef,
74 { PrintError
=> 0, RaiseError
=> 1 });
83 postgres_admin_cmd
('dropdb', undef, $dbname);
88 my $database_list = {};
93 my ($name, $owner) = map { PVE
::Tools
::trim
($_) } split(/\|/, $line);
94 return if !$name || !$owner;
96 $database_list->{$name} = { owner
=> $owner };
99 postgres_admin_cmd
('psql', { outfunc
=> $parser }, '--list', '--quiet', '--tuples-only');
101 return $database_list;
104 my $cgreylist_ctablecmd = <<__EOD;
105 CREATE TABLE CGreylist
106 (IPNet VARCHAR(16) NOT NULL,
107 Host INTEGER NOT NULL,
108 Sender VARCHAR(255) NOT NULL,
109 Receiver VARCHAR(255) NOT NULL,
110 Instance VARCHAR(255),
111 RCTime INTEGER NOT NULL,
112 ExTime INTEGER NOT NULL,
113 Delay INTEGER NOT NULL DEFAULT 0,
114 Blocked INTEGER NOT NULL,
115 Passed INTEGER NOT NULL,
116 CID INTEGER NOT NULL,
117 MTime INTEGER NOT NULL,
118 PRIMARY KEY (IPNet, Sender, Receiver));
120 CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender);
122 CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime);
124 CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime);
127 my $clusterinfo_ctablecmd = <<__EOD;
128 CREATE TABLE ClusterInfo
129 (CID INTEGER NOT NULL,
130 Name VARCHAR NOT NULL,
133 PRIMARY KEY (CID, Name))
136 my $local_stat_ctablecmd = <<__EOD;
137 CREATE TABLE LocalStat
138 (Time INTEGER NOT NULL,
139 RBLCount INTEGER DEFAULT 0 NOT NULL,
140 PregreetCount INTEGER DEFAULT 0 NOT NULL,
141 CID INTEGER NOT NULL,
142 MTime INTEGER NOT NULL,
143 PRIMARY KEY (Time, CID));
145 CREATE INDEX LocalStat_MTime_Index ON LocalStat (MTime);
149 my $daily_stat_ctablecmd = <<__EOD;
150 CREATE TABLE DailyStat
151 (Time INTEGER NOT NULL UNIQUE,
152 CountIn INTEGER NOT NULL,
153 CountOut INTEGER NOT NULL,
154 BytesIn REAL NOT NULL,
155 BytesOut REAL NOT NULL,
156 VirusIn INTEGER NOT NULL,
157 VirusOut INTEGER NOT NULL,
158 SpamIn INTEGER NOT NULL,
159 SpamOut INTEGER NOT NULL,
160 BouncesIn INTEGER NOT NULL,
161 BouncesOut INTEGER NOT NULL,
162 GreylistCount INTEGER NOT NULL,
163 SPFCount INTEGER NOT NULL,
164 PTimeSum REAL NOT NULL,
165 MTime INTEGER NOT NULL,
166 RBLCount INTEGER DEFAULT 0 NOT NULL,
169 CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
173 my $domain_stat_ctablecmd = <<__EOD;
174 CREATE TABLE DomainStat
175 (Time INTEGER NOT NULL,
176 Domain VARCHAR(255) NOT NULL,
177 CountIn INTEGER NOT NULL,
178 CountOut INTEGER NOT NULL,
179 BytesIn REAL NOT NULL,
180 BytesOut REAL NOT NULL,
181 VirusIn INTEGER NOT NULL,
182 VirusOut INTEGER NOT NULL,
183 SpamIn INTEGER NOT NULL,
184 SpamOut INTEGER NOT NULL,
185 BouncesIn INTEGER NOT NULL,
186 BouncesOut INTEGER NOT NULL,
187 PTimeSum REAL NOT NULL,
188 MTime INTEGER NOT NULL,
189 PRIMARY KEY (Time, Domain));
191 CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
194 my $statinfo_ctablecmd = <<__EOD;
195 CREATE TABLE StatInfo
196 (Name VARCHAR(255) NOT NULL UNIQUE,
202 my $virusinfo_stat_ctablecmd = <<__EOD;
203 CREATE TABLE VirusInfo
204 (Time INTEGER NOT NULL,
205 Name VARCHAR NOT NULL,
206 Count INTEGER NOT NULL,
207 MTime INTEGER NOT NULL,
208 PRIMARY KEY (Time, Name));
210 CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
216 # V - Virus quarantine
217 # S - Spam quarantine
218 # D - Delayed Mails - not implemented
219 # A - Held for Audit - not implemented
224 my $cmailstore_ctablecmd = <<__EOD;
225 CREATE TABLE CMailStore
226 (CID INTEGER DEFAULT 0 NOT NULL,
227 RID INTEGER NOT NULL,
229 Time INTEGER NOT NULL,
230 QType "char" NOT NULL,
231 Bytes INTEGER NOT NULL,
232 Spamlevel INTEGER NOT NULL,
234 Sender VARCHAR(255) NOT NULL,
235 Header VARCHAR NOT NULL,
236 File VARCHAR(255) NOT NULL,
237 PRIMARY KEY (CID, RID));
238 CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
240 CREATE TABLE CMSReceivers
241 (CMailStore_CID INTEGER NOT NULL,
242 CMailStore_RID INTEGER NOT NULL,
243 PMail VARCHAR(255) NOT NULL,
244 Receiver VARCHAR(255),
245 TicketID INTEGER NOT NULL,
246 Status "char" NOT NULL,
247 MTime INTEGER NOT NULL);
249 CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
251 CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
255 my $cstatistic_ctablecmd = <<__EOD;
256 CREATE TABLE CStatistic
257 (CID INTEGER DEFAULT 0 NOT NULL,
258 RID INTEGER NOT NULL,
260 Time INTEGER NOT NULL,
261 Bytes INTEGER NOT NULL,
262 Direction Boolean NOT NULL,
263 Spamlevel INTEGER NOT NULL,
264 VirusInfo VARCHAR(255) NULL,
265 PTime INTEGER NOT NULL,
266 Sender VARCHAR(255) NOT NULL,
267 PRIMARY KEY (CID, RID));
269 CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
271 CREATE TABLE CReceivers
272 (CStatistic_CID INTEGER NOT NULL,
273 CStatistic_RID INTEGER NOT NULL,
274 Receiver VARCHAR(255) NOT NULL,
275 Blocked Boolean NOT NULL);
277 CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
280 # user preferences (black an whitelists, ...)
281 # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
282 # Data: arbitrary data
283 my $userprefs_ctablecmd = <<__EOD;
284 CREATE TABLE UserPrefs
288 MTime INTEGER NOT NULL,
289 PRIMARY KEY (PMail, Name));
291 CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
295 sub cond_create_dbtable
{
296 my ($dbh, $name, $ctablecmd) = @_;
301 my $cmd = "SELECT tablename FROM pg_tables " .
302 "WHERE tablename = lower ('$name')";
304 my $sth = $dbh->prepare($cmd);
308 if (!(my $ref = $sth->fetchrow_hashref())) {
309 $dbh->do ($ctablecmd);
322 sub database_column_exists
{
323 my ($dbh, $table, $column) = @_;
325 my $sth = $dbh->prepare(
326 "SELECT column_name FROM information_schema.columns " .
327 "WHERE table_name = ? and column_name = ?");
328 $sth->execute(lc($table), lc($column));
329 my $res = $sth->fetchrow_hashref();
330 return defined($res);
349 $dbname = $default_db_name if !$dbname;
351 my $silent_opts = { outfunc
=> sub {}, errfunc
=> sub {} };
352 # make sure we have user 'root'
353 eval { postgres_admin_cmd
('createuser', $silent_opts, '-D', 'root'); };
354 # also create 'www-data' (and give it read-only access below)
355 eval { postgres_admin_cmd
('createuser', $silent_opts, '-I', '-D', 'www-data'); };
357 # use sql_ascii to avoid any character set conversions, and be compatible with
358 # older postgres versions (update from 8.1 must be possible)
360 $createdb->($dbname);
362 my $dbh = open_ruledb
($dbname);
364 # make sure 'www-data' can read all tables
365 $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\"");
369 CREATE TABLE Attribut
370 (Object_ID INTEGER NOT NULL,
371 Name VARCHAR(20) NOT NULL,
373 PRIMARY KEY (Object_ID, Name));
375 CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
379 ObjectType INTEGER NOT NULL,
380 Objectgroup_ID INTEGER NOT NULL,
384 CREATE TABLE Objectgroup
386 Name VARCHAR(255) NOT NULL,
387 Info VARCHAR(255) NULL,
388 Class VARCHAR(10) NOT NULL,
393 Name VARCHAR(255) NULL,
394 Priority INTEGER NOT NULL,
395 Active INTEGER NOT NULL DEFAULT 0,
396 Direction INTEGER NOT NULL DEFAULT 2,
397 Count INTEGER NOT NULL DEFAULT 0,
400 CREATE TABLE RuleGroup
401 (Objectgroup_ID INTEGER NOT NULL,
402 Rule_ID INTEGER NOT NULL,
403 Grouptype INTEGER NOT NULL,
404 PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
406 $cgreylist_ctablecmd;
408 $clusterinfo_ctablecmd;
410 $local_stat_ctablecmd;
412 $daily_stat_ctablecmd;
414 $domain_stat_ctablecmd;
418 $cmailstore_ctablecmd;
420 $cstatistic_ctablecmd;
422 $userprefs_ctablecmd;
424 $virusinfo_stat_ctablecmd;
431 sub cond_create_action_quarantine
{
434 my $dbh = $ruledb->{dbh
};
437 my $sth = $dbh->prepare(
438 "SELECT * FROM Objectgroup, Object " .
439 "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " .
440 "AND Object.objectgroup_id = Objectgroup.id");
442 my $otype = PMG
::RuleDB
::Quarantine
::otype
();
443 if ($sth->execute($otype, 'action') <= 0) {
444 my $obj = PMG
::RuleDB
::Quarantine-
>new ();
445 my $txt = decode_entities
(PMG
::RuleDB
::Quarantine-
>otype_text);
446 my $quarantine = $ruledb->create_group_with_obj
447 ($obj, $txt, 'Move to quarantine.');
452 sub cond_create_std_actions
{
455 cond_create_action_quarantine
($ruledb);
457 #cond_create_action_report_spam($ruledb);
464 my $dbh = $ruledb->{dbh
};
466 # make sure we do not use slow sequential scans when upgraing
467 # database (before analyze can gather statistics)
468 $dbh->do("set enable_seqscan = false");
471 'LocalStat', $local_stat_ctablecmd,
472 'DailyStat', $daily_stat_ctablecmd,
473 'DomainStat', $domain_stat_ctablecmd,
474 'StatInfo', $statinfo_ctablecmd,
475 'CMailStore', $cmailstore_ctablecmd,
476 'UserPrefs', $userprefs_ctablecmd,
477 'CGreylist', $cgreylist_ctablecmd,
478 'CStatistic', $cstatistic_ctablecmd,
479 'ClusterInfo', $clusterinfo_ctablecmd,
480 'VirusInfo', $virusinfo_stat_ctablecmd,
483 foreach my $table (keys %$tables) {
484 cond_create_dbtable
($dbh, $table, $tables->{$table});
487 cond_create_std_actions
($ruledb);
489 # upgrade tables here if necessary
490 if (!database_column_exists
($dbh, 'LocalStat', 'PregreetCount')) {
491 $dbh->do("ALTER TABLE LocalStat ADD COLUMN " .
492 "PregreetCount INTEGER DEFAULT 0 NOT NULL");
495 eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); };
499 # add missing TicketID to CMSReceivers
500 if (!database_column_exists
($dbh, 'CMSReceivers', 'TicketID')) {
503 $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq");
504 $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " .
505 "TicketID INTEGER NOT NULL " .
506 "DEFAULT nextval('cmsreceivers_ticketid_seq')");
507 $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " .
508 "TicketID DROP DEFAULT");
509 $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq");
518 # update obsolete content type names
520 $dbh->do("UPDATE Object " .
521 "SET value = 'content-type:application/java-vm' ".
522 "WHERE objecttype = 3003 " .
523 "AND value = 'content-type:application/x-java-vm';");
526 foreach my $table (keys %$tables) {
527 eval { $dbh->do("ANALYZE $table"); };
535 my ($ruledb, $reset, $testmode) = @_;
537 my $dbh = $ruledb->{dbh
};
540 # Greylist Objectgroup
541 my $greylistgroup = PMG
::RuleDB
::Group-
>new
542 ("GreyExclusion", "-", "greylist");
543 $ruledb->save_group ($greylistgroup);
546 # we do not touch greylist objects
547 my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
548 "objectgroup_id = objectgroup.id and class = 'greylist'";
550 $dbh->do ("DELETE FROM Rule; " .
551 "DELETE FROM RuleGroup; " .
552 "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
553 "DELETE FROM Object WHERE ID NOT IN ($glids); " .
554 "DELETE FROM Objectgroup WHERE class != 'greylist';");
560 my $obj = PMG
::RuleDB
::EMail-
>new ('nomail@fromthisdomain.com');
561 my $blacklist = $ruledb->create_group_with_obj(
562 $obj, 'Blacklist', 'Global blacklist');
565 $obj = PMG
::RuleDB
::EMail-
>new('mail@fromthisdomain.com');
566 my $whitelist = $ruledb->create_group_with_obj(
567 $obj, 'Whitelist', 'Global whitelist');
572 $obj = PMG
::RuleDB
::TimeFrame-
>new(8*60, 16*60);
573 my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
574 'Usual office hours');
579 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('image/.*');
580 my $img_content = $ruledb->create_group_with_obj(
581 $obj, 'Images', 'All kinds of graphic files');
584 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('audio/.*');
585 my $mm_content = $ruledb->create_group_with_obj(
586 $obj, 'Multimedia', 'Audio and Video');
588 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('video/.*');
589 $ruledb->group_add_object($mm_content, $obj);
592 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/vnd\.ms-excel');
593 my $office_content = $ruledb->create_group_with_obj(
594 $obj, 'Office Files', 'Common Office Files');
596 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
597 'application/vnd\.ms-powerpoint');
599 $ruledb->group_add_object($office_content, $obj);
601 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/msword');
602 $ruledb->group_add_object ($office_content, $obj);
604 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
605 'application/vnd\.openxmlformats-officedocument\..*');
606 $ruledb->group_add_object($office_content, $obj);
608 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
609 'application/vnd\.oasis\.opendocument\..*');
610 $ruledb->group_add_object($office_content, $obj);
612 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
613 'application/vnd\.stardivision\..*');
614 $ruledb->group_add_object($office_content, $obj);
616 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
617 'application/vnd\.sun\.xml\..*');
618 $ruledb->group_add_object($office_content, $obj);
621 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
622 'application/x-ms-dos-executable');
623 my $exe_content = $ruledb->create_group_with_obj(
624 $obj, 'Dangerous Content', 'executable files and partial messages');
626 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-java');
627 $ruledb->group_add_object($exe_content, $obj);
628 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/javascript');
629 $ruledb->group_add_object($exe_content, $obj);
630 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-executable');
631 $ruledb->group_add_object($exe_content, $obj);
632 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-ms-dos-executable');
633 $ruledb->group_add_object($exe_content, $obj);
634 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('message/partial');
635 $ruledb->group_add_object($exe_content, $obj);
636 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.(vbs|pif|lnk|shs|shb)');
637 $ruledb->group_add_object($exe_content, $obj);
638 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.\{.+\}');
639 $ruledb->group_add_object($exe_content, $obj);
642 $obj = PMG
::RuleDB
::Virus-
>new();
643 my $virus = $ruledb->create_group_with_obj(
644 $obj, 'Virus', 'Matches virus infected mail');
649 $obj = PMG
::RuleDB
::Spam-
>new(3);
650 my $spam3 = $ruledb->create_group_with_obj(
651 $obj, 'Spam (Level 3)', 'Matches possible spam mail');
653 $obj = PMG
::RuleDB
::Spam-
>new(5);
654 my $spam5 = $ruledb->create_group_with_obj(
655 $obj, 'Spam (Level 5)', 'Matches possible spam mail');
657 $obj = PMG
::RuleDB
::Spam-
>new(10);
658 my $spam10 = $ruledb->create_group_with_obj(
659 $obj, 'Spam (Level 10)', 'Matches possible spam mail');
664 $obj = PMG
::RuleDB
::ModField-
>new('X-SPAM-LEVEL', '__SPAM_INFO__');
665 my $mod_spam_level = $ruledb->create_group_with_obj(
666 $obj, 'Modify Spam Level',
667 'Mark mail as spam by adding a header tag.');
670 $obj = PMG
::RuleDB
::ModField-
>new('subject', 'SPAM: __SUBJECT__');
671 my $mod_spam_subject = $ruledb->create_group_with_obj(
672 $obj, 'Modify Spam Subject',
673 'Mark mail as spam by modifying the subject.');
675 # Remove matching attachments
676 $obj = PMG
::RuleDB
::Remove-
>new(0);
677 my $remove = $ruledb->create_group_with_obj(
678 $obj, 'Remove attachments', 'Remove matching attachments');
680 # Remove all attachments
681 $obj = PMG
::RuleDB
::Remove-
>new(1);
682 my $remove_all = $ruledb->create_group_with_obj(
683 $obj, 'Remove all attachments', 'Remove all attachments');
686 $obj = PMG
::RuleDB
::Accept-
>new();
687 my $accept = $ruledb->create_group_with_obj(
688 $obj, 'Accept', 'Accept mail for Delivery');
691 $obj = PMG
::RuleDB
::Block-
>new ();
692 my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
695 $obj = PMG
::RuleDB
::Quarantine-
>new();
696 my $quarantine = $ruledb->create_group_with_obj(
697 $obj, 'Quarantine', 'Move mail to quarantine');
700 $obj = PMG
::RuleDB
::Notify-
>new('__ADMIN__');
701 my $notify_admin = $ruledb->create_group_with_obj(
702 $obj, 'Notify Admin', 'Send notification');
705 $obj = PMG
::RuleDB
::Notify-
>new('__SENDER__');
706 my $notify_sender = $ruledb->create_group_with_obj(
707 $obj, 'Notify Sender', 'Send notification');
710 $obj = PMG
::RuleDB
::Disclaimer-
>new ();
711 my $add_discl = $ruledb->create_group_with_obj(
712 $obj, 'Disclaimer', 'Add Disclaimer');
714 # Attach original mail
715 #$obj = Proxmox::RuleDB::Attach->new ();
716 #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
717 # 'Attach Original Mail');
719 ####################### RULES ##################################
721 ## Block Dangerous Files
722 my $rule = PMG
::RuleDB
::Rule-
>new ('Block Dangerous Files', 93, 1, 0);
723 $ruledb->save_rule ($rule);
725 $ruledb->rule_add_what_group ($rule, $exe_content);
726 $ruledb->rule_add_action ($rule, $remove);
729 $rule = PMG
::RuleDB
::Rule-
>new ('Block Viruses', 96, 1, 0);
730 $ruledb->save_rule ($rule);
732 $ruledb->rule_add_what_group ($rule, $virus);
733 $ruledb->rule_add_action ($rule, $notify_admin);
736 $ruledb->rule_add_action ($rule, $block);
738 $ruledb->rule_add_action ($rule, $quarantine);
742 $rule = PMG
::RuleDB
::Rule-
>new ('Virus Alert', 96, 1, 1);
743 $ruledb->save_rule ($rule);
745 $ruledb->rule_add_what_group ($rule, $virus);
746 $ruledb->rule_add_action ($rule, $notify_sender);
747 $ruledb->rule_add_action ($rule, $notify_admin);
748 $ruledb->rule_add_action ($rule, $block);
751 $rule = PMG
::RuleDB
::Rule-
>new ('Blacklist', 98, 1, 0);
752 $ruledb->save_rule ($rule);
754 $ruledb->rule_add_from_group ($rule, $blacklist);
755 $ruledb->rule_add_action ($rule, $block);
759 $rule = PMG
::RuleDB
::Rule-
>new ('Modify Header', 90, 1, 0);
760 $ruledb->save_rule ($rule);
761 $ruledb->rule_add_action ($rule, $mod_spam_level);
765 $rule = PMG
::RuleDB
::Rule-
>new ('Whitelist', 85, 1, 0);
766 $ruledb->save_rule ($rule);
768 $ruledb->rule_add_from_group ($rule, $whitelist);
769 $ruledb->rule_add_action ($rule, $accept);
772 $rule = PMG
::RuleDB
::Rule-
>new ('Mark Spam', 80, 1, 0);
773 $ruledb->save_rule ($rule);
775 $ruledb->rule_add_what_group ($rule, $spam10);
776 $ruledb->rule_add_action ($rule, $mod_spam_level);
777 $ruledb->rule_add_action ($rule, $mod_spam_subject);
779 # Quarantine/Mark Spam (Level 3)
780 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
781 $ruledb->save_rule ($rule);
783 $ruledb->rule_add_what_group ($rule, $spam3);
784 $ruledb->rule_add_action ($rule, $mod_spam_subject);
785 $ruledb->rule_add_action ($rule, $quarantine);
786 #$ruledb->rule_add_action ($rule, $count_spam);
789 # Quarantine/Mark Spam (Level 5)
790 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0);
791 $ruledb->save_rule ($rule);
793 $ruledb->rule_add_what_group ($rule, $spam5);
794 $ruledb->rule_add_action ($rule, $mod_spam_subject);
795 $ruledb->rule_add_action ($rule, $quarantine);
797 ## Block Spam Level 10
798 $rule = PMG
::RuleDB
::Rule-
>new ('Block Spam (Level 10)', 82, 0, 0);
799 $ruledb->save_rule ($rule);
801 $ruledb->rule_add_what_group ($rule, $spam10);
802 $ruledb->rule_add_action ($rule, $block);
804 ## Block Outgoing Spam
805 $rule = PMG
::RuleDB
::Rule-
>new ('Block outgoing Spam', 70, 0, 1);
806 $ruledb->save_rule ($rule);
808 $ruledb->rule_add_what_group ($rule, $spam3);
809 $ruledb->rule_add_action ($rule, $notify_admin);
810 $ruledb->rule_add_action ($rule, $notify_sender);
811 $ruledb->rule_add_action ($rule, $block);
814 $rule = PMG
::RuleDB
::Rule-
>new ('Add Disclaimer', 60, 0, 1);
815 $ruledb->save_rule ($rule);
816 $ruledb->rule_add_action ($rule, $add_discl);
818 # Block Multimedia Files
819 $rule = PMG
::RuleDB
::Rule-
>new ('Block Multimedia Files', 87, 0, 2);
820 $ruledb->save_rule ($rule);
822 $ruledb->rule_add_what_group ($rule, $mm_content);
823 $ruledb->rule_add_action ($rule, $remove);
825 #$ruledb->rule_add_from_group ($rule, $anybody);
826 #$ruledb->rule_add_from_group ($rule, $trusted);
827 #$ruledb->rule_add_to_group ($rule, $anybody);
828 #$ruledb->rule_add_what_group ($rule, $ct_filter);
829 #$ruledb->rule_add_action ($rule, $add_discl);
830 #$ruledb->rule_add_action ($rule, $remove);
831 #$ruledb->rule_add_action ($rule, $bcc);
832 #$ruledb->rule_add_action ($rule, $storeq);
833 #$ruledb->rule_add_action ($rule, $accept);
835 cond_create_std_actions
($ruledb);
840 sub get_remote_time
{
843 my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
845 my $ctinfo = $sth->fetchrow_hashref();
848 return $ctinfo ?
$ctinfo->{ctime
} : 0;
852 my ($lcid, $database) = @_;
854 die "got unexpected cid for new master" if !$lcid;
859 $dbh = open_ruledb
($database);
863 print STDERR
"update quarantine database\n";
864 $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
865 "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
867 print STDERR
"update statistic database\n";
868 $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
869 "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
871 print STDERR
"update greylist database\n";
872 $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
874 print STDERR
"update localstat database\n";
875 $dbh->do ("UPDATE LocalStat SET CID = $lcid WHERE CID = 0;");
882 $dbh->rollback if $err;
889 sub purge_statistic_database
{
890 my ($dbh, $statlifetime) = @_;
892 return if $statlifetime <= 0;
894 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
895 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
896 my $start = $end - $statlifetime*86400;
898 # delete statistics older than $start
905 my $sth = $dbh->prepare("DELETE FROM CStatistic WHERE time < $start");
911 $sth = $dbh->prepare(
912 "DELETE FROM CReceivers WHERE NOT EXISTS " .
913 "(SELECT * FROM CStatistic WHERE CID = CStatistic_CID AND RID = CStatistic_RID)");
927 sub purge_quarantine_database
{
928 my ($dbh, $qtype, $lifetime) = @_;
930 my $spooldir = $PMG::MailQueue
::spooldir
;
932 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
933 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
934 my $start = $end - $lifetime*86400;
936 my $sth = $dbh->prepare(
937 "SELECT file FROM CMailStore WHERE time < $start AND QType = '$qtype'");
943 while (my $ref = $sth->fetchrow_hashref()) {
944 my $filename = "$spooldir/$ref->{file}";
945 $count++ if unlink($filename);
951 "DELETE FROM CMailStore WHERE time < $start AND QType = '$qtype';" .
952 "DELETE FROM CMSReceivers WHERE NOT EXISTS " .
953 "(SELECT * FROM CMailStore WHERE CID = CMailStore_CID AND RID = CMailStore_RID)");
958 sub get_quarantine_count
{
959 my ($dbh, $qtype) = @_;
961 # Note;: We try to estimate used disk space - each mail
962 # is stored in an extra file ...
966 my $sth = $dbh->prepare(
967 "SELECT count(ID) as count, sum (ceil((Bytes+$bs-1)/$bs)*$bs) / (1024*1024) as mbytes, " .
968 "avg(Bytes) as avgbytes, avg(Spamlevel) as avgspam " .
969 "FROM CMailStore WHERE QType = ?");
971 $sth->execute($qtype);
973 my $ref = $sth->fetchrow_hashref();
977 foreach my $k (qw(count mbytes avgbytes avgspam)) {
985 my ($ldb, $rdb, $table) = @_;
989 my $sth = $ldb->column_info(undef, undef, $table, undef);
990 my $attrs = $sth->fetchall_arrayref({});
993 foreach my $ref (@$attrs) {
994 push @col_arr, $ref->{COLUMN_NAME
};
999 my $cols = join(', ', @col_arr);
1000 $cols || die "unable to fetch column definitions of table '$table' : ERROR";
1002 $rdb->do("COPY $table ($cols) TO STDOUT");
1007 $ldb->do("COPY $table ($cols) FROM stdin");
1009 while ($rdb->pg_getcopydata($data) >= 0) {
1010 $ldb->pg_putcopydata($data);
1013 $ldb->pg_putcopyend();
1016 $ldb->pg_putcopyend();
1021 sub copy_selected_data
{
1022 my ($dbh, $select_sth, $table, $attrs, $callback) = @_;
1026 my $insert_sth = $dbh->prepare(
1027 "INSERT INTO ${table}(" . join(',', @$attrs) . ') ' .
1028 'VALUES (' . join(',', ('?') x
scalar(@$attrs)) . ')');
1030 while (my $ref = $select_sth->fetchrow_hashref()) {
1031 $callback->($ref) if $callback;
1033 $insert_sth->execute(map { $ref->{$_} } @$attrs);
1039 sub update_master_clusterinfo
{
1040 my ($clientcid) = @_;
1042 my $dbh = open_ruledb
();
1044 $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
1046 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1048 foreach my $table (@mt) {
1049 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
1050 "EXTRACT(EPOCH FROM now())");
1054 sub update_client_clusterinfo
{
1055 my ($mastercid) = @_;
1057 my $dbh = open_ruledb
();
1059 $dbh->do ("DELETE FROM StatInfo"); # not needed at node
1061 $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
1063 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
1064 "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
1066 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
1067 "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
1069 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
1071 foreach my $table (@mt) {
1072 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
1073 "COALESCE (max (mtime), 0) FROM $table");
1077 sub create_clusterinfo_default
{
1078 my ($dbh, $rcid, $name, $ivalue, $svalue) = @_;
1080 my $sth = $dbh->prepare("SELECT * FROM ClusterInfo WHERE CID = ? AND Name = ?");
1081 $sth->execute($rcid, $name);
1082 if (!$sth->fetchrow_hashref()) {
1083 $dbh->do("INSERT INTO ClusterInfo (CID, Name, IValue, SValue) " .
1084 "VALUES (?, ?, ?, ?)", undef,
1085 $rcid, $name, $ivalue, $svalue);
1090 sub read_int_clusterinfo
{
1091 my ($dbh, $rcid, $name) = @_;
1093 my $sth = $dbh->prepare(
1094 "SELECT ivalue as value FROM ClusterInfo " .
1095 "WHERE cid = ? AND NAME = ?");
1096 $sth->execute($rcid, $name);
1097 my $cinfo = $sth->fetchrow_hashref();
1100 return $cinfo->{value
};
1103 sub write_maxint_clusterinfo
{
1104 my ($dbh, $rcid, $name, $value) = @_;
1106 $dbh->do("UPDATE ClusterInfo SET ivalue = GREATEST(ivalue, ?) " .
1107 "WHERE cid = ? AND name = ?", undef,
1108 $value, $rcid, $name);
1114 my $ni = $cinfo->{master
};
1116 die "no master defined - unable to sync data from master\n" if !$ni;
1118 my $master_ip = $ni->{ip
};
1119 my $master_cid = $ni->{cid
};
1120 my $master_name = $ni->{name
};
1122 my $fn = "/tmp/masterdb$$.tar";
1125 my $dbname = $default_db_name;
1128 print STDERR
"copying master database from '${master_ip}'\n";
1130 open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
1132 my $cmd = ['/usr/bin/ssh', '-o', 'BatchMode=yes',
1133 '-o', "HostKeyAlias=${master_name}", $master_ip,
1134 'pg_dump', $dbname, '-F', 'c' ];
1136 PVE
::Tools
::run_command
($cmd, output
=> '>&' . fileno($fh));
1142 print STDERR
"copying master database finished (got $size bytes)\n";
1144 print STDERR
"delete local database\n";
1146 postgres_admin_cmd
('dropdb', undef, $dbname , '--if-exists');
1148 print STDERR
"create new local database\n";
1150 $createdb->($dbname);
1152 print STDERR
"insert received data into local database\n";
1158 if ($line =~ m/restoring data for table \"(.+)\"/) {
1159 print STDERR
"restoring table $1\n";
1160 } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
1161 $mess = "creating indexes";
1162 print STDERR
"$mess\n";
1169 errmsg
=> "pg_restore failed"
1172 postgres_admin_cmd
('pg_restore', $opts, '-d', $dbname, '-v', $fn);
1174 print STDERR
"run analyze to speed up database queries\n";
1176 postgres_admin_cmd
('psql', { input
=> 'analyze;' }, $dbname);
1178 update_client_clusterinfo
($master_cid);
1188 sub cluster_sync_status
{
1195 foreach my $ni (values %{$cinfo->{ids
}}) {
1196 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1197 $minmtime->{$ni->{cid
}} = 0;
1201 $dbh = open_ruledb
();
1203 my $sth = $dbh->prepare(
1204 "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " .
1205 "WHERE name = 'lastsync' AND ivalue > 0 " .
1210 while (my $info = $sth->fetchrow_hashref()) {
1211 foreach my $ni (values %{$cinfo->{ids
}}) {
1212 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1213 if ($ni->{cid
} == $info->{cid
}) { # node exists
1214 $minmtime->{$ni->{cid
}} = $info->{minmtime
};
1223 $dbh->disconnect() if $dbh;
1225 syslog
('err', $err) if $err;
1230 sub load_mail_data
{
1231 my ($dbh, $cid, $rid, $ticketid) = @_;
1233 my $sth = $dbh->prepare(
1234 "SELECT * FROM CMailStore, CMSReceivers WHERE " .
1235 "CID = ? AND RID = ? AND TicketID = ? AND " .
1236 "CID = CMailStore_CID AND RID = CMailStore_RID");
1237 $sth->execute($cid, $rid, $ticketid);
1239 my $res = $sth->fetchrow_hashref();
1243 die "no such mail (C${cid}R${rid}T${ticketid})\n" if !defined($res);
1251 # Note: we pass $ruledb when modifying SMTP whitelist
1252 if (defined($ruledb)) {
1254 my $rulecache = PMG
::RuleCache-
>new($ruledb);
1255 PMG
::Config
::rewrite_postfix_whitelist
($rulecache);
1258 warn "problems updating SMTP whitelist - $err";
1262 my $pid_file = '/run/pmg-smtp-filter.pid';
1263 my $pid = PVE
::Tools
::file_read_firstline
($pid_file);
1267 return 0 if $pid !~ m/^(\d+)$/;
1268 $pid = $1; # untaint
1270 return kill (10, $pid); # send SIGUSR1