6 use POSIX
":sys_wait_h";
16 our $default_db_name = "Proxmox_ruledb";
19 my ($database, $host, $port) = @_;
21 $port = 5432 if !$port;
23 $database = $default_db_name if !$database;
27 my $dsn = "dbi:Pg:dbname=$database;host=$host;port=$port;";
30 # only low level alarm interface works for DBI->connect
31 my $mask = POSIX
::SigSet-
>new(SIGALRM
);
32 my $action = POSIX
::SigAction-
>new(sub { die "connect timeout\n" }, $mask);
33 my $oldaction = POSIX
::SigAction-
>new();
34 sigaction
(SIGALRM
, $action, $oldaction);
40 $rdb = DBI-
>connect($dsn, "postgres", undef,
41 { PrintError
=> 0, RaiseError
=> 1 });
45 sigaction
(SIGALRM
, $oldaction); # restore original handler
51 my $dsn = "DBI:Pg:dbname=$database";
53 my $dbh = DBI-
>connect($dsn, "postgres", undef,
54 { PrintError
=> 0, RaiseError
=> 1 });
63 PVE
::Tools
::run_command
(['dropdb', '-U', 'postgres', $dbname]);
68 my $database_list = {};
73 my ($name, $owner) = map { PVE
::Tools
::trim
($_) } split(/\|/, $line);
74 return if !$name || !$owner;
76 $database_list->{$name} = { owner
=> $owner };
79 my $cmd = ['psql', '-U', 'postgres', '--list', '--quiet', '--tuples-only'];
81 PVE
::Tools
::run_command
($cmd, outfunc
=> $parser);
83 return $database_list;
86 my $dbfunction_maxint = <<__EOD;
87 CREATE OR REPLACE FUNCTION maxint (INTEGER, INTEGER) RETURNS INTEGER AS
88 'BEGIN IF \$1 > \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql;
91 my $dbfunction_minint = <<__EOD;
92 CREATE OR REPLACE FUNCTION minint (INTEGER, INTEGER) RETURNS INTEGER AS
93 'BEGIN IF \$1 < \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql;
96 # merge function to avoid update/insert race condition
97 # see: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
98 my $dbfunction_merge_greylist = <<__EOD;
99 CREATE OR REPLACE FUNCTION merge_greylist (in_ipnet VARCHAR, in_host INTEGER, in_sender VARCHAR,
100 in_receiver VARCHAR, in_instance VARCHAR,
101 in_rctime INTEGER, in_extime INTEGER, in_delay INTEGER,
102 in_blocked INTEGER, in_passed INTEGER, in_mtime INTEGER,
103 in_cid INTEGER) RETURNS INTEGER AS
106 UPDATE CGreylist SET Host = CASE WHEN MTime >= in_mtime THEN Host ELSE in_host END,
107 CID = maxint (CID, in_cid), RCTime = minint (rctime, in_rctime),
108 ExTime = maxint (extime, in_extime),
109 Delay = maxint (delay, in_delay),
110 Blocked = maxint (blocked, in_blocked),
111 Passed = maxint (passed, in_passed)
112 WHERE IPNet = in_ipnet AND Sender = in_sender AND Receiver = in_receiver;
119 INSERT INTO CGREYLIST (IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, MTime, CID)
120 VALUES (in_ipnet, in_host, in_sender, in_receiver, in_instance, in_rctime, in_extime,
121 in_delay, in_blocked, in_passed, in_mtime, in_cid);
123 EXCEPTION WHEN unique_violation THEN
124 -- do nothing - continue loop
127 END;' LANGUAGE plpgsql;
130 my $cgreylist_ctablecmd = <<__EOD;
131 CREATE TABLE CGreylist
132 (IPNet VARCHAR(16) NOT NULL,
133 Host INTEGER NOT NULL,
134 Sender VARCHAR(255) NOT NULL,
135 Receiver VARCHAR(255) NOT NULL,
136 Instance VARCHAR(255),
137 RCTime INTEGER NOT NULL,
138 ExTime INTEGER NOT NULL,
139 Delay INTEGER NOT NULL DEFAULT 0,
140 Blocked INTEGER NOT NULL,
141 Passed INTEGER NOT NULL,
142 CID INTEGER NOT NULL,
143 MTime INTEGER NOT NULL,
144 PRIMARY KEY (IPNet, Sender, Receiver));
146 CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender);
148 CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime);
150 CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime);
153 my $clusterinfo_ctablecmd = <<__EOD;
154 CREATE TABLE ClusterInfo
155 (CID INTEGER NOT NULL,
156 Name VARCHAR NOT NULL,
159 PRIMARY KEY (CID, Name))
162 my $daily_stat_ctablecmd = <<__EOD;
163 CREATE TABLE DailyStat
164 (Time INTEGER NOT NULL UNIQUE,
165 CountIn INTEGER NOT NULL,
166 CountOut INTEGER NOT NULL,
167 BytesIn REAL NOT NULL,
168 BytesOut REAL NOT NULL,
169 VirusIn INTEGER NOT NULL,
170 VirusOut INTEGER NOT NULL,
171 SpamIn INTEGER NOT NULL,
172 SpamOut INTEGER NOT NULL,
173 BouncesIn INTEGER NOT NULL,
174 BouncesOut INTEGER NOT NULL,
175 GreylistCount INTEGER NOT NULL,
176 SPFCount INTEGER NOT NULL,
177 PTimeSum REAL NOT NULL,
178 MTime INTEGER NOT NULL,
179 RBLCount INTEGER DEFAULT 0 NOT NULL,
182 CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
186 my $domain_stat_ctablecmd = <<__EOD;
187 CREATE TABLE DomainStat
188 (Time INTEGER NOT NULL,
189 Domain VARCHAR(255) NOT NULL,
190 CountIn INTEGER NOT NULL,
191 CountOut INTEGER NOT NULL,
192 BytesIn REAL NOT NULL,
193 BytesOut REAL NOT NULL,
194 VirusIn INTEGER NOT NULL,
195 VirusOut INTEGER NOT NULL,
196 SpamIn INTEGER NOT NULL,
197 SpamOut INTEGER NOT NULL,
198 BouncesIn INTEGER NOT NULL,
199 BouncesOut INTEGER NOT NULL,
200 PTimeSum REAL NOT NULL,
201 MTime INTEGER NOT NULL,
202 PRIMARY KEY (Time, Domain));
204 CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
207 my $statinfo_ctablecmd = <<__EOD;
208 CREATE TABLE StatInfo
209 (Name VARCHAR(255) NOT NULL UNIQUE,
215 my $virusinfo_stat_ctablecmd = <<__EOD;
216 CREATE TABLE VirusInfo
217 (Time INTEGER NOT NULL,
218 Name VARCHAR NOT NULL,
219 Count INTEGER NOT NULL,
220 MTime INTEGER NOT NULL,
221 PRIMARY KEY (Time, Name));
223 CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
227 # mail storage stable
229 # V - Virus quarantine
230 # S - Spam quarantine
231 # D - Delayed Mails - not implemented
232 # A - Held for Audit - not implemented
237 my $cmailstore_ctablecmd = <<__EOD;
238 CREATE TABLE CMailStore
239 (CID INTEGER DEFAULT 0 NOT NULL,
240 RID INTEGER NOT NULL,
242 Time INTEGER NOT NULL,
243 QType "char" NOT NULL,
244 Bytes INTEGER NOT NULL,
245 Spamlevel INTEGER NOT NULL,
247 Sender VARCHAR(255) NOT NULL,
248 Header VARCHAR NOT NULL,
249 File VARCHAR(255) NOT NULL,
250 PRIMARY KEY (CID, RID));
251 CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
253 CREATE TABLE CMSReceivers
254 (CMailStore_CID INTEGER NOT NULL,
255 CMailStore_RID INTEGER NOT NULL,
256 PMail VARCHAR(255) NOT NULL,
257 Receiver VARCHAR(255),
258 TicketID INTEGER NOT NULL,
259 Status "char" NOT NULL,
260 MTime INTEGER NOT NULL);
262 CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
264 CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
268 my $cstatistic_ctablecmd = <<__EOD;
269 CREATE TABLE CStatistic
270 (CID INTEGER DEFAULT 0 NOT NULL,
271 RID INTEGER NOT NULL,
273 Time INTEGER NOT NULL,
274 Bytes INTEGER NOT NULL,
275 Direction Boolean NOT NULL,
276 Spamlevel INTEGER NOT NULL,
277 VirusInfo VARCHAR(255) NULL,
278 PTime INTEGER NOT NULL,
279 Sender VARCHAR(255) NOT NULL,
280 PRIMARY KEY (CID, RID));
282 CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
284 CREATE TABLE CReceivers
285 (CStatistic_CID INTEGER NOT NULL,
286 CStatistic_RID INTEGER NOT NULL,
287 Receiver VARCHAR(255) NOT NULL,
288 Blocked Boolean NOT NULL);
290 CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
293 # user preferences (black an whitelists, ...)
294 # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
295 # Data: arbitrary data
296 my $userprefs_ctablecmd = <<__EOD;
297 CREATE TABLE UserPrefs
301 MTime INTEGER NOT NULL,
302 PRIMARY KEY (PMail, Name));
304 CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
308 sub cond_create_dbtable
{
309 my ($dbh, $name, $ctablecmd) = @_;
314 my $cmd = "SELECT tablename FROM pg_tables " .
315 "WHERE tablename = lower ('$name')";
317 my $sth = $dbh->prepare ($cmd);
321 if (!(my $ref = $sth->fetchrow_hashref())) {
322 $dbh->do ($ctablecmd);
338 $dbname = $default_db_name if !$dbname;
340 # use sql_ascii to avoid any character set conversions, and be compatible with
341 # older postgres versions (update from 8.1 must be possible)
342 my $cmd = [ 'createdb', '-U', 'postgres', '-E', 'sql_ascii',
343 '-T', 'template0', '--lc-collate=C', '--lc-ctype=C', $dbname ];
345 PVE
::Tools
::run_command
($cmd);
347 my $dbh = open_ruledb
($dbname);
349 #$dbh->do ($dbloaddrivers_sql);
350 #$dbh->do ($dbfunction_update_modtime);
352 $dbh->do ($dbfunction_minint);
354 $dbh->do ($dbfunction_maxint);
356 $dbh->do ($dbfunction_merge_greylist);
360 CREATE TABLE Attribut
361 (Object_ID INTEGER NOT NULL,
362 Name VARCHAR(20) NOT NULL,
364 PRIMARY KEY (Object_ID, Name));
366 CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
370 ObjectType INTEGER NOT NULL,
371 Objectgroup_ID INTEGER NOT NULL,
375 CREATE TABLE Objectgroup
377 Name VARCHAR(255) NOT NULL,
378 Info VARCHAR(255) NULL,
379 Class VARCHAR(10) NOT NULL,
384 Name VARCHAR(255) NULL,
385 Priority INTEGER NOT NULL,
386 Active INTEGER NOT NULL DEFAULT 0,
387 Direction INTEGER NOT NULL DEFAULT 2,
388 Count INTEGER NOT NULL DEFAULT 0,
391 CREATE TABLE RuleGroup
392 (Objectgroup_ID INTEGER NOT NULL,
393 Rule_ID INTEGER NOT NULL,
394 Grouptype INTEGER NOT NULL,
395 PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
397 $cgreylist_ctablecmd;
399 $clusterinfo_ctablecmd;
401 $daily_stat_ctablecmd;
403 $domain_stat_ctablecmd;
407 $cmailstore_ctablecmd;
409 $cstatistic_ctablecmd;
411 $userprefs_ctablecmd;
413 $virusinfo_stat_ctablecmd;
420 sub cond_create_action_quarantine
{
423 my $dbh = $ruledb->{dbh
};
426 my $sth = $dbh->prepare(
427 "SELECT * FROM Objectgroup, Object " .
428 "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " .
429 "AND Object.objectgroup_id = Objectgroup.id");
431 my $otype = PMG
::RuleDB
::Quarantine
::otype
();
432 if ($sth->execute($otype, 'action') <= 0) {
433 my $obj = PMG
::RuleDB
::Quarantine-
>new ();
434 my $txt = decode_entities
(PMG
::RuleDB
::Quarantine-
>otype_text);
435 my $quarantine = $ruledb->create_group_with_obj
436 ($obj, $txt, 'Move to quarantine.');
441 sub cond_create_std_actions
{
444 cond_create_action_quarantine
($ruledb);
446 #cond_create_action_report_spam($ruledb);
450 sub upgrade_mailstore_db
{
456 my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('MailStore')";
458 my $sth = $dbh->prepare($cmd);
460 my $ref = $sth->fetchrow_hashref();
463 if ($ref) { # table exists
465 $cmd = "INSERT INTO CMailStore " .
466 "(CID, RID, ID, Time, QType, Bytes, Spamlevel, Info, Header, Sender, File) " .
467 "SELECT 0, ID, ID, Time, QType, Bytes, Spamlevel, Info, Header, Sender, File FROM MailStore";
471 $cmd = "INSERT INTO CMSReceivers " .
472 "(CMailStore_CID, CMailStore_RID, PMail, Receiver, TicketID, Status, MTime) " .
473 "SELECT 0, MailStore_ID, PMail, Receiver, TicketID, Status, 0 FROM MSReceivers";
477 $dbh->do("SELECT setval ('cmailstore_id_seq', nextval ('mailstore_id_seq'))");
479 $dbh->do("DROP TABLE MailStore");
480 $dbh->do("DROP TABLE MSReceivers");
491 sub upgrade_dailystat_db
{
494 eval { # make sure we have MTime
495 $dbh->do("ALTER TABLE DailyStat ADD COLUMN MTime INTEGER;" .
496 "UPDATE DailyStat SET MTime = EXTRACT (EPOCH FROM now());");
499 eval { # make sure we have correct constraints for MTime
500 $dbh->do ("ALTER TABLE DailyStat ALTER COLUMN MTime SET NOT NULL;");
503 eval { # make sure we have RBLCount
504 $dbh->do ("ALTER TABLE DailyStat ADD COLUMN RBLCount INTEGER;" .
505 "UPDATE DailyStat SET RBLCount = 0;");
508 eval { # make sure we have correct constraints for RBLCount
509 $dbh->do ("ALTER TABLE DailyStat ALTER COLUMN RBLCount SET DEFAULT 0;" .
510 "ALTER TABLE DailyStat ALTER COLUMN RBLCount SET NOT NULL;");
516 my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('DailyStat_MTime_Index')";
518 my $sth = $dbh->prepare($cmd);
520 my $ref = $sth->fetchrow_hashref();
523 if (!$ref) { # index does not exist
524 $dbh->do ("CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime)");
535 sub upgrade_domainstat_db
{
538 eval { # make sure we have MTime
539 $dbh->do("ALTER TABLE DomainStat ADD COLUMN MTime INTEGER;" .
540 "UPDATE DomainStat SET MTime = EXTRACT (EPOCH FROM now());" .
541 "ALTER TABLE DomainStat ALTER COLUMN MTime SET NOT NULL;");
547 my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('DomainStat_MTime_Index')";
549 my $sth = $dbh->prepare($cmd);
551 my $ref = $sth->fetchrow_hashref();
554 if (!$ref) { # index does not exist
555 $dbh->do ("CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime)");
566 sub upgrade_statistic_db
{
572 my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('Statistic')";
574 my $sth = $dbh->prepare($cmd);
576 my $ref = $sth->fetchrow_hashref();
579 if ($ref) { # old table exists
581 my $timezone = tz_local_offset
();;
583 $dbh->do("INSERT INTO VirusInfo (Time, Name, Count, MTime) " .
584 "SELECT ((time + $timezone) / 86400) * 86400 as day, virusinfo, " .
585 "count (virusinfo), max (Time) FROM Statistic " .
586 "WHERE virusinfo IS NOT NULL GROUP BY day, virusinfo");
588 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime (time());
589 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
590 my $start = $end - 3600*24*7; # / days
592 $cmd = "INSERT INTO CStatistic " .
593 "(CID, RID, ID, Time, Bytes, Direction, Spamlevel, VirusInfo, PTime, Sender) " .
594 "SELECT 0, ID, ID, Time, Bytes, Direction, Spamlevel, VirusInfo, PTime, Sender FROM Statistic " .
595 "WHERE time >= $start";
599 $dbh->do("SELECT setval ('cstatistic_id_seq', nextval ('statistic_id_seq'))");
601 $dbh->do("INSERT INTO StatInfo (name, ivalue) VALUES ('virusinfo_index', " .
602 "nextval ('statistic_id_seq'))");
604 $cmd = "INSERT INTO CReceivers (CStatistic_CID, CStatistic_RID, Receiver, Blocked) " .
605 "SELECT 0, Mail_ID, Receiver, Blocked FROM Receivers " .
606 "WHERE EXISTS (SELECT * FROM CStatistic WHERE CID = 0 AND RID = Mail_ID)";
610 $dbh->do("DROP TABLE Statistic");
611 $dbh->do("DROP TABLE Receivers");
622 sub upgrade_greylist_db
{
628 my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('Greylist')";
630 my $sth = $dbh->prepare($cmd);
632 my $ref = $sth->fetchrow_hashref();
635 if ($ref) { # table exists
637 $cmd = "INSERT INTO CGreylist " .
638 "(IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, MTime, CID) " .
639 "SELECT IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, RCTime, 0 FROM Greylist";
643 $dbh->do("DROP TABLE Greylist");
654 sub upgrade_userprefs_db
{
658 $dbh->do("ALTER TABLE UserPrefs ADD COLUMN MTime INTEGER;" .
659 "UPDATE UserPrefs SET MTime = EXTRACT (EPOCH FROM now());" .
660 "ALTER TABLE UserPrefs ALTER COLUMN MTime SET NOT NULL;");
667 my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('UserPrefs_MTime_Index')";
669 my $sth = $dbh->prepare($cmd);
671 my $ref = $sth->fetchrow_hashref();
674 if (!$ref) { # index does not exist
675 $dbh->do("CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime)");
689 my $dbh = $ruledb->{dbh
};
691 $dbh->do($dbfunction_minint);
693 $dbh->do($dbfunction_maxint);
695 $dbh->do($dbfunction_merge_greylist);
697 # make sure we do not use slow sequential scans when upgraing
698 # database (before analyze can gather statistics)
699 $dbh->do("set enable_seqscan = false");
701 cond_create_dbtable
($dbh, 'DailyStat', $daily_stat_ctablecmd);
702 cond_create_dbtable
($dbh, 'DomainStat', $domain_stat_ctablecmd);
703 cond_create_dbtable
($dbh, 'StatInfo', $statinfo_ctablecmd);
704 cond_create_dbtable
($dbh, 'CMailStore', $cmailstore_ctablecmd);
705 cond_create_dbtable
($dbh, 'UserPrefs', $userprefs_ctablecmd);
706 cond_create_dbtable
($dbh, 'CGreylist', $cgreylist_ctablecmd);
707 cond_create_dbtable
($dbh, 'CStatistic', $cstatistic_ctablecmd);
708 cond_create_dbtable
($dbh, 'ClusterInfo', $clusterinfo_ctablecmd);
709 cond_create_dbtable
($dbh, 'VirusInfo', $virusinfo_stat_ctablecmd);
711 cond_create_std_actions
($ruledb);
713 upgrade_mailstore_db
($dbh);
715 upgrade_statistic_db
($dbh);
717 upgrade_userprefs_db
($dbh);
719 upgrade_greylist_db
($dbh);
721 upgrade_dailystat_db
($dbh);
723 upgrade_domainstat_db
($dbh);
725 # update obsolete content type names
727 $dbh->do("UPDATE Object " .
728 "SET value = 'content-type:application/java-vm' ".
729 "WHERE objecttype = 3003 " .
730 "AND value = 'content-type:application/x-java-vm';");
734 $dbh->do ("ANALYZE");
739 my ($ruledb, $reset, $testmode) = @_;
741 my $dbh = $ruledb->{dbh
};
744 # Greylist Objectgroup
745 my $greylistgroup = PMG
::RuleDB
::Group-
>new
746 ("GreyExclusion", "-", "greylist");
747 $ruledb->save_group ($greylistgroup);
750 # we do not touch greylist objects
751 my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
752 "objectgroup_id = objectgroup.id and class = 'greylist'";
754 $dbh->do ("DELETE FROM Rule; " .
755 "DELETE FROM RuleGroup; " .
756 "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
757 "DELETE FROM Object WHERE ID NOT IN ($glids); " .
758 "DELETE FROM Objectgroup WHERE class != 'greylist';");
764 my $obj = PMG
::RuleDB
::EMail-
>new ('nomail@fromthisdomain.com');
765 my $blacklist = $ruledb->create_group_with_obj(
766 $obj, 'Blacklist', 'Global blacklist');
769 $obj = PMG
::RuleDB
::EMail-
>new('mail@fromthisdomain.com');
770 my $whitelist = $ruledb->create_group_with_obj(
771 $obj, 'Whitelist', 'Global whitelist');
776 $obj = PMG
::RuleDB
::TimeFrame-
>new(8*60, 16*60);
777 my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
778 'Usual office hours');
783 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('image/.*');
784 my $img_content = $ruledb->create_group_with_obj(
785 $obj, 'Images', 'All kinds of graphic files');
788 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('audio/.*');
789 my $mm_content = $ruledb->create_group_with_obj(
790 $obj, 'Multimedia', 'Audio and Video');
792 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('video/.*');
793 $ruledb->group_add_object($mm_content, $obj);
796 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/vnd\.ms-excel');
797 my $office_content = $ruledb->create_group_with_obj(
798 $obj, 'Office Files', 'Common Office Files');
800 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
801 'application/vnd\.ms-powerpoint');
803 $ruledb->group_add_object($office_content, $obj);
805 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/msword');
806 $ruledb->group_add_object ($office_content, $obj);
808 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
809 'application/vnd\.openxmlformats-officedocument\..*');
810 $ruledb->group_add_object($office_content, $obj);
812 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
813 'application/vnd\.oasis\.opendocument\..*');
814 $ruledb->group_add_object($office_content, $obj);
816 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
817 'application/vnd\.stardivision\..*');
818 $ruledb->group_add_object($office_content, $obj);
820 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
821 'application/vnd\.sun\.xml\..*');
822 $ruledb->group_add_object($office_content, $obj);
825 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
826 'application/x-ms-dos-executable');
827 my $exe_content = $ruledb->create_group_with_obj(
828 $obj, 'Dangerous Content', 'executable files and partial messages');
830 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-java');
831 $ruledb->group_add_object($exe_content, $obj);
832 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/javascript');
833 $ruledb->group_add_object($exe_content, $obj);
834 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-executable');
835 $ruledb->group_add_object($exe_content, $obj);
836 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-ms-dos-executable');
837 $ruledb->group_add_object($exe_content, $obj);
838 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('message/partial');
839 $ruledb->group_add_object($exe_content, $obj);
840 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.(vbs|pif|lnk|shs|shb)');
841 $ruledb->group_add_object($exe_content, $obj);
842 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.\{.+\}');
843 $ruledb->group_add_object($exe_content, $obj);
846 $obj = PMG
::RuleDB
::Virus-
>new();
847 my $virus = $ruledb->create_group_with_obj(
848 $obj, 'Virus', 'Matches virus infected mail');
853 $obj = PMG
::RuleDB
::Spam-
>new(3);
854 my $spam3 = $ruledb->create_group_with_obj(
855 $obj, 'Spam (Level 3)', 'Matches possible spam mail');
857 $obj = PMG
::RuleDB
::Spam-
>new(5);
858 my $spam5 = $ruledb->create_group_with_obj(
859 $obj, 'Spam (Level 5)', 'Matches possible spam mail');
861 $obj = PMG
::RuleDB
::Spam-
>new(10);
862 my $spam10 = $ruledb->create_group_with_obj(
863 $obj, 'Spam (Level 10)', 'Matches possible spam mail');
868 $obj = PMG
::RuleDB
::ModField-
>new('X-SPAM-LEVEL', '__SPAM_INFO__');
869 my $mod_spam_level = $ruledb->create_group_with_obj(
870 $obj, 'Modify Spam Level',
871 'Mark mail as spam by adding a header tag.');
874 $obj = PMG
::RuleDB
::ModField-
>new('subject', 'SPAM: __SUBJECT__');
875 my $mod_spam_subject = $ruledb->create_group_with_obj(
876 $obj, 'Modify Spam Subject',
877 'Mark mail as spam by modifying the subject.');
879 # Remove matching attachments
880 $obj = PMG
::RuleDB
::Remove-
>new(0);
881 my $remove = $ruledb->create_group_with_obj(
882 $obj, 'Remove attachments', 'Remove matching attachments');
884 # Remove all attachments
885 $obj = PMG
::RuleDB
::Remove-
>new(1);
886 my $remove_all = $ruledb->create_group_with_obj(
887 $obj, 'Remove all attachments', 'Remove all attachments');
890 $obj = PMG
::RuleDB
::Accept-
>new();
891 my $accept = $ruledb->create_group_with_obj(
892 $obj, 'Accept', 'Accept mail for Delivery');
895 $obj = PMG
::RuleDB
::Block-
>new ();
896 my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
899 $obj = PMG
::RuleDB
::Quarantine-
>new();
900 my $quarantine = $ruledb->create_group_with_obj(
901 $obj, 'Quarantine', 'Move mail to quarantine');
904 $obj = PMG
::RuleDB
::Notify-
>new('__ADMIN__');
905 my $notify_admin = $ruledb->create_group_with_obj(
906 $obj, 'Notify Admin', 'Send notification');
909 $obj = PMG
::RuleDB
::Notify-
>new('__SENDER__');
910 my $notify_sender = $ruledb->create_group_with_obj(
911 $obj, 'Notify Sender', 'Send notification');
914 $obj = PMG
::RuleDB
::Disclaimer-
>new ();
915 my $add_discl = $ruledb->create_group_with_obj(
916 $obj, 'Disclaimer', 'Add Disclaimer');
918 # Attach original mail
919 #$obj = Proxmox::RuleDB::Attach->new ();
920 #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
921 # 'Attach Original Mail');
923 ####################### RULES ##################################
925 ## Block Dangerous Files
926 my $rule = PMG
::RuleDB
::Rule-
>new ('Block Dangerous Files', 93, 1, 0);
927 $ruledb->save_rule ($rule);
929 $ruledb->rule_add_what_group ($rule, $exe_content);
930 $ruledb->rule_add_action ($rule, $remove);
933 $rule = PMG
::RuleDB
::Rule-
>new ('Block Viruses', 96, 1, 0);
934 $ruledb->save_rule ($rule);
936 $ruledb->rule_add_what_group ($rule, $virus);
937 $ruledb->rule_add_action ($rule, $notify_admin);
940 $ruledb->rule_add_action ($rule, $block);
942 $ruledb->rule_add_action ($rule, $quarantine);
946 $rule = PMG
::RuleDB
::Rule-
>new ('Virus Alert', 96, 1, 1);
947 $ruledb->save_rule ($rule);
949 $ruledb->rule_add_what_group ($rule, $virus);
950 $ruledb->rule_add_action ($rule, $notify_sender);
951 $ruledb->rule_add_action ($rule, $notify_admin);
952 $ruledb->rule_add_action ($rule, $block);
955 $rule = PMG
::RuleDB
::Rule-
>new ('Blacklist', 98, 1, 0);
956 $ruledb->save_rule ($rule);
958 $ruledb->rule_add_from_group ($rule, $blacklist);
959 $ruledb->rule_add_action ($rule, $block);
963 $rule = PMG
::RuleDB
::Rule-
>new ('Modify Header', 90, 1, 0);
964 $ruledb->save_rule ($rule);
965 $ruledb->rule_add_action ($rule, $mod_spam_level);
969 $rule = PMG
::RuleDB
::Rule-
>new ('Whitelist', 85, 1, 0);
970 $ruledb->save_rule ($rule);
972 $ruledb->rule_add_from_group ($rule, $whitelist);
973 $ruledb->rule_add_action ($rule, $accept);
976 $rule = PMG
::RuleDB
::Rule-
>new ('Mark Spam', 80, 1, 0);
977 $ruledb->save_rule ($rule);
979 $ruledb->rule_add_what_group ($rule, $spam10);
980 $ruledb->rule_add_action ($rule, $mod_spam_level);
981 $ruledb->rule_add_action ($rule, $mod_spam_subject);
983 # Quarantine/Mark Spam (Level 3)
984 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
985 $ruledb->save_rule ($rule);
987 $ruledb->rule_add_what_group ($rule, $spam3);
988 $ruledb->rule_add_action ($rule, $mod_spam_subject);
989 $ruledb->rule_add_action ($rule, $quarantine);
990 #$ruledb->rule_add_action ($rule, $count_spam);
993 # Quarantine/Mark Spam (Level 5)
994 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 5)', 79, 0, 0);
995 $ruledb->save_rule ($rule);
997 $ruledb->rule_add_what_group ($rule, $spam5);
998 $ruledb->rule_add_action ($rule, $mod_spam_subject);
999 $ruledb->rule_add_action ($rule, $quarantine);
1001 ## Block Spam Level 10
1002 $rule = PMG
::RuleDB
::Rule-
>new ('Block Spam (Level 10)', 78, 0, 0);
1003 $ruledb->save_rule ($rule);
1005 $ruledb->rule_add_what_group ($rule, $spam10);
1006 $ruledb->rule_add_action ($rule, $block);
1008 ## Block Outgoing Spam
1009 $rule = PMG
::RuleDB
::Rule-
>new ('Block outgoing Spam', 70, 0, 1);
1010 $ruledb->save_rule ($rule);
1012 $ruledb->rule_add_what_group ($rule, $spam3);
1013 $ruledb->rule_add_action ($rule, $notify_admin);
1014 $ruledb->rule_add_action ($rule, $notify_sender);
1015 $ruledb->rule_add_action ($rule, $block);
1018 $rule = PMG
::RuleDB
::Rule-
>new ('Add Disclaimer', 60, 0, 1);
1019 $ruledb->save_rule ($rule);
1020 $ruledb->rule_add_action ($rule, $add_discl);
1022 # Block Multimedia Files
1023 $rule = PMG
::RuleDB
::Rule-
>new ('Block Multimedia Files', 87, 0, 2);
1024 $ruledb->save_rule ($rule);
1026 $ruledb->rule_add_what_group ($rule, $mm_content);
1027 $ruledb->rule_add_action ($rule, $remove);
1029 #$ruledb->rule_add_from_group ($rule, $anybody);
1030 #$ruledb->rule_add_from_group ($rule, $trusted);
1031 #$ruledb->rule_add_to_group ($rule, $anybody);
1032 #$ruledb->rule_add_what_group ($rule, $ct_filter);
1033 #$ruledb->rule_add_action ($rule, $add_discl);
1034 #$ruledb->rule_add_action ($rule, $remove);
1035 #$ruledb->rule_add_action ($rule, $bcc);
1036 #$ruledb->rule_add_action ($rule, $storeq);
1037 #$ruledb->rule_add_action ($rule, $accept);
1039 cond_create_std_actions
($ruledb);
1042 sub get_remote_time
{
1045 my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
1047 my $ctinfo = $sth->fetchrow_hashref();
1050 return $ctinfo ?
$ctinfo->{ctime
} : 0;
1054 my ($lcid, $database) = @_;
1056 die "got unexpected cid for new master" if !$lcid;
1061 $dbh = open_ruledb
($database);
1065 print STDERR
"update quarantine database\n";
1066 $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
1067 "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
1069 print STDERR
"update statistic database\n";
1070 $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
1071 "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
1073 print STDERR
"update greylist database\n";
1074 $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
1081 $dbh->rollback if $err;
1088 sub update_master_clusterinfo
{
1089 my ($clientcid) = @_;
1091 my $dbh = open_ruledb
();
1093 $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
1095 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'VirusInfo');
1097 foreach my $table (@mt) {
1098 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
1099 "EXTRACT(EPOCH FROM now())");
1103 sub update_client_clusterinfo
{
1104 my ($mastercid) = @_;
1106 my $dbh = open_ruledb
();
1108 $dbh->do ("DELETE FROM StatInfo"); # not needed at node
1110 $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
1112 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
1113 "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
1115 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
1116 "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
1118 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'VirusInfo');
1120 foreach my $table (@mt) {
1121 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
1122 "COALESCE (max (mtime), 0) FROM $table");
1129 my $ni = $cinfo->{master
};
1131 die "no master defined - unable to sync data from master\n" if !$ni;
1133 my $master_ip = $ni->{ip
};
1134 my $master_cid = $ni->{cid
};
1135 my $master_name = $ni->{name
};
1137 my $fn = "/tmp/masterdb$$.tar";
1140 my $dbname = $default_db_name;
1143 print STDERR
"copying master database from '${master_ip}'\n";
1145 my $cmd = [['/usr/bin/ssh', '-o', 'BatchMode=yes',
1146 '-o', "HostKeyAlias=${master_name}",
1147 $master_ip, 'pg_dump', '-U', 'postgres',
1148 $dbname, '-F', 'c', \
">$fn" ]];
1150 PVE
::Tools
::run_command
($cmd);
1154 print STDERR
"copying master database finished (got $size bytes)\n";
1156 print STDERR
"delete local database\n";
1158 $cmd = [ 'dropdb', '-U', 'postgres', $dbname , '--if-exists'];
1159 PVE
::Tools
::run_command
($cmd);
1161 print STDERR
"create new local database\n";
1163 $cmd = ['createdb', '-U', 'postgres', $dbname];
1164 PVE
::Tools
::run_command
($cmd);
1166 print STDERR
"insert received data into local database\n";
1172 if ($line =~ m/restoring data for table \"(.+)\"/) {
1173 print STDERR
"restoring table $1\n";
1174 } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
1175 $mess = "creating indexes";
1176 print STDERR
"$mess\n";
1180 $cmd = ['pg_restore', '-U', 'postgres', '-d', $dbname, '-v', $fn];
1181 PVE
::Tools
::run_command
($cmd, outfunc
=> $parser, errfunc
=> $parser,
1182 errmsg
=> "pg_restore failed");
1184 print STDERR
"run analyze to speed up database queries\n";
1186 $cmd = ['psql', '-U', 'postgres', $dbname];
1187 PVE
::Tools
::run_command
($cmd);
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;
1237 syslog
('err', PMG
::Utils
::msgquote
($err));