6 use POSIX
":sys_wait_h";
16 our $default_db_name = "Proxmox_ruledb";
19 my ($database, $host, $port) = @_;
23 $database //= $default_db_name;
27 # Note: pmgtunnel uses UDP sockets inside directory '/var/run/pmgtunnel',
28 # and the cluster 'cid' as port number. You can connect to the
29 # socket with: host => /var/run/pmgtunnel, port => $cid
31 my $dsn = "dbi:Pg:dbname=$database;host=$host;port=$port;";
34 # only low level alarm interface works for DBI->connect
35 my $mask = POSIX
::SigSet-
>new(SIGALRM
);
36 my $action = POSIX
::SigAction-
>new(sub { die "connect timeout\n" }, $mask);
37 my $oldaction = POSIX
::SigAction-
>new();
38 sigaction
(SIGALRM
, $action, $oldaction);
44 $rdb = DBI-
>connect($dsn, 'root', undef,
45 { PrintError
=> 0, RaiseError
=> 1 });
49 sigaction
(SIGALRM
, $oldaction); # restore original handler
55 my $dsn = "DBI:Pg:dbname=$database;host=/var/run/postgresql;port=$port";
57 my $dbh = DBI-
>connect($dsn, $> == 0 ?
'root' : 'www-data', undef,
58 { PrintError
=> 0, RaiseError
=> 1 });
64 sub postgres_admin_cmd
{
65 my ($cmd, $options, @params) = @_;
67 $cmd = ref($cmd) ?
$cmd : [ $cmd ];
68 my $uid = getpwnam('postgres') || die "getpwnam postgres failed\n";
71 $! && die "setuid postgres ($uid) failed - $!\n";
73 PVE
::Tools
::run_command
([@$cmd, '-U', 'postgres', @params], %$options);
79 postgres_admin_cmd
('dropdb', undef, $dbname);
84 my $database_list = {};
89 my ($name, $owner) = map { PVE
::Tools
::trim
($_) } split(/\|/, $line);
90 return if !$name || !$owner;
92 $database_list->{$name} = { owner
=> $owner };
95 postgres_admin_cmd
('psql', { outfunc
=> $parser }, '--list', '--quiet', '--tuples-only');
97 return $database_list;
100 my $dbfunction_maxint = <<__EOD;
101 CREATE OR REPLACE FUNCTION maxint (INTEGER, INTEGER) RETURNS INTEGER AS
102 'BEGIN IF \$1 > \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql;
105 my $dbfunction_minint = <<__EOD;
106 CREATE OR REPLACE FUNCTION minint (INTEGER, INTEGER) RETURNS INTEGER AS
107 'BEGIN IF \$1 < \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql;
110 # merge function to avoid update/insert race condition
111 # see: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
112 my $dbfunction_merge_greylist = <<__EOD;
113 CREATE OR REPLACE FUNCTION merge_greylist (in_ipnet VARCHAR, in_host INTEGER, in_sender VARCHAR,
114 in_receiver VARCHAR, in_instance VARCHAR,
115 in_rctime INTEGER, in_extime INTEGER, in_delay INTEGER,
116 in_blocked INTEGER, in_passed INTEGER, in_mtime INTEGER,
117 in_cid INTEGER) RETURNS INTEGER AS
120 UPDATE CGreylist SET Host = CASE WHEN MTime >= in_mtime THEN Host ELSE in_host END,
121 CID = maxint (CID, in_cid), RCTime = minint (rctime, in_rctime),
122 ExTime = maxint (extime, in_extime),
123 Delay = maxint (delay, in_delay),
124 Blocked = maxint (blocked, in_blocked),
125 Passed = maxint (passed, in_passed)
126 WHERE IPNet = in_ipnet AND Sender = in_sender AND Receiver = in_receiver;
133 INSERT INTO CGREYLIST (IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, MTime, CID)
134 VALUES (in_ipnet, in_host, in_sender, in_receiver, in_instance, in_rctime, in_extime,
135 in_delay, in_blocked, in_passed, in_mtime, in_cid);
137 EXCEPTION WHEN unique_violation THEN
138 -- do nothing - continue loop
141 END;' LANGUAGE plpgsql;
144 my $cgreylist_ctablecmd = <<__EOD;
145 CREATE TABLE CGreylist
146 (IPNet VARCHAR(16) NOT NULL,
147 Host INTEGER NOT NULL,
148 Sender VARCHAR(255) NOT NULL,
149 Receiver VARCHAR(255) NOT NULL,
150 Instance VARCHAR(255),
151 RCTime INTEGER NOT NULL,
152 ExTime INTEGER NOT NULL,
153 Delay INTEGER NOT NULL DEFAULT 0,
154 Blocked INTEGER NOT NULL,
155 Passed INTEGER NOT NULL,
156 CID INTEGER NOT NULL,
157 MTime INTEGER NOT NULL,
158 PRIMARY KEY (IPNet, Sender, Receiver));
160 CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender);
162 CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime);
164 CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime);
167 my $clusterinfo_ctablecmd = <<__EOD;
168 CREATE TABLE ClusterInfo
169 (CID INTEGER NOT NULL,
170 Name VARCHAR NOT NULL,
173 PRIMARY KEY (CID, Name))
176 my $daily_stat_ctablecmd = <<__EOD;
177 CREATE TABLE DailyStat
178 (Time INTEGER NOT NULL UNIQUE,
179 CountIn INTEGER NOT NULL,
180 CountOut INTEGER NOT NULL,
181 BytesIn REAL NOT NULL,
182 BytesOut REAL NOT NULL,
183 VirusIn INTEGER NOT NULL,
184 VirusOut INTEGER NOT NULL,
185 SpamIn INTEGER NOT NULL,
186 SpamOut INTEGER NOT NULL,
187 BouncesIn INTEGER NOT NULL,
188 BouncesOut INTEGER NOT NULL,
189 GreylistCount INTEGER NOT NULL,
190 SPFCount INTEGER NOT NULL,
191 PTimeSum REAL NOT NULL,
192 MTime INTEGER NOT NULL,
193 RBLCount INTEGER DEFAULT 0 NOT NULL,
196 CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
200 my $domain_stat_ctablecmd = <<__EOD;
201 CREATE TABLE DomainStat
202 (Time INTEGER NOT NULL,
203 Domain VARCHAR(255) NOT NULL,
204 CountIn INTEGER NOT NULL,
205 CountOut INTEGER NOT NULL,
206 BytesIn REAL NOT NULL,
207 BytesOut REAL NOT NULL,
208 VirusIn INTEGER NOT NULL,
209 VirusOut INTEGER NOT NULL,
210 SpamIn INTEGER NOT NULL,
211 SpamOut INTEGER NOT NULL,
212 BouncesIn INTEGER NOT NULL,
213 BouncesOut INTEGER NOT NULL,
214 PTimeSum REAL NOT NULL,
215 MTime INTEGER NOT NULL,
216 PRIMARY KEY (Time, Domain));
218 CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
221 my $statinfo_ctablecmd = <<__EOD;
222 CREATE TABLE StatInfo
223 (Name VARCHAR(255) NOT NULL UNIQUE,
229 my $virusinfo_stat_ctablecmd = <<__EOD;
230 CREATE TABLE VirusInfo
231 (Time INTEGER NOT NULL,
232 Name VARCHAR NOT NULL,
233 Count INTEGER NOT NULL,
234 MTime INTEGER NOT NULL,
235 PRIMARY KEY (Time, Name));
237 CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
241 # mail storage stable
243 # V - Virus quarantine
244 # S - Spam quarantine
245 # D - Delayed Mails - not implemented
246 # A - Held for Audit - not implemented
251 my $cmailstore_ctablecmd = <<__EOD;
252 CREATE TABLE CMailStore
253 (CID INTEGER DEFAULT 0 NOT NULL,
254 RID INTEGER NOT NULL,
256 Time INTEGER NOT NULL,
257 QType "char" NOT NULL,
258 Bytes INTEGER NOT NULL,
259 Spamlevel INTEGER NOT NULL,
261 Sender VARCHAR(255) NOT NULL,
262 Header VARCHAR NOT NULL,
263 File VARCHAR(255) NOT NULL,
264 PRIMARY KEY (CID, RID));
265 CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
267 CREATE TABLE CMSReceivers
268 (CMailStore_CID INTEGER NOT NULL,
269 CMailStore_RID INTEGER NOT NULL,
270 PMail VARCHAR(255) NOT NULL,
271 Receiver VARCHAR(255),
272 TicketID INTEGER NOT NULL,
273 Status "char" NOT NULL,
274 MTime INTEGER NOT NULL);
276 CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
278 CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
282 my $cstatistic_ctablecmd = <<__EOD;
283 CREATE TABLE CStatistic
284 (CID INTEGER DEFAULT 0 NOT NULL,
285 RID INTEGER NOT NULL,
287 Time INTEGER NOT NULL,
288 Bytes INTEGER NOT NULL,
289 Direction Boolean NOT NULL,
290 Spamlevel INTEGER NOT NULL,
291 VirusInfo VARCHAR(255) NULL,
292 PTime INTEGER NOT NULL,
293 Sender VARCHAR(255) NOT NULL,
294 PRIMARY KEY (CID, RID));
296 CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
298 CREATE TABLE CReceivers
299 (CStatistic_CID INTEGER NOT NULL,
300 CStatistic_RID INTEGER NOT NULL,
301 Receiver VARCHAR(255) NOT NULL,
302 Blocked Boolean NOT NULL);
304 CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
307 # user preferences (black an whitelists, ...)
308 # Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
309 # Data: arbitrary data
310 my $userprefs_ctablecmd = <<__EOD;
311 CREATE TABLE UserPrefs
315 MTime INTEGER NOT NULL,
316 PRIMARY KEY (PMail, Name));
318 CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
322 sub cond_create_dbtable
{
323 my ($dbh, $name, $ctablecmd) = @_;
328 my $cmd = "SELECT tablename FROM pg_tables " .
329 "WHERE tablename = lower ('$name')";
331 my $sth = $dbh->prepare ($cmd);
335 if (!(my $ref = $sth->fetchrow_hashref())) {
336 $dbh->do ($ctablecmd);
352 $dbname = $default_db_name if !$dbname;
354 my $silent_opts = { outfunc
=> sub {}, errfunc
=> sub {} };
355 # make sure we have user 'root'
356 eval { postgres_admin_cmd
('createuser', $silent_opts, '-D', 'root'); };
357 # also create 'www-data' (and give it read-only access below)
358 eval { postgres_admin_cmd
('createuser', $silent_opts, '-I', '-D', 'www-data'); };
360 # use sql_ascii to avoid any character set conversions, and be compatible with
361 # older postgres versions (update from 8.1 must be possible)
363 postgres_admin_cmd
('createdb', undef, '-E', 'sql_ascii', '-T', 'template0',
364 '--lc-collate=C', '--lc-ctype=C', $dbname);
366 my $dbh = open_ruledb
($dbname);
368 # make sure 'www-data' can read all tables
369 $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\"");
371 #$dbh->do ($dbloaddrivers_sql);
372 #$dbh->do ($dbfunction_update_modtime);
374 $dbh->do ($dbfunction_minint);
376 $dbh->do ($dbfunction_maxint);
378 $dbh->do ($dbfunction_merge_greylist);
382 CREATE TABLE Attribut
383 (Object_ID INTEGER NOT NULL,
384 Name VARCHAR(20) NOT NULL,
386 PRIMARY KEY (Object_ID, Name));
388 CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
392 ObjectType INTEGER NOT NULL,
393 Objectgroup_ID INTEGER NOT NULL,
397 CREATE TABLE Objectgroup
399 Name VARCHAR(255) NOT NULL,
400 Info VARCHAR(255) NULL,
401 Class VARCHAR(10) NOT NULL,
406 Name VARCHAR(255) NULL,
407 Priority INTEGER NOT NULL,
408 Active INTEGER NOT NULL DEFAULT 0,
409 Direction INTEGER NOT NULL DEFAULT 2,
410 Count INTEGER NOT NULL DEFAULT 0,
413 CREATE TABLE RuleGroup
414 (Objectgroup_ID INTEGER NOT NULL,
415 Rule_ID INTEGER NOT NULL,
416 Grouptype INTEGER NOT NULL,
417 PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
419 $cgreylist_ctablecmd;
421 $clusterinfo_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);
472 sub upgrade_mailstore_db
{
478 my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('MailStore')";
480 my $sth = $dbh->prepare($cmd);
482 my $ref = $sth->fetchrow_hashref();
485 if ($ref) { # table exists
487 $cmd = "INSERT INTO CMailStore " .
488 "(CID, RID, ID, Time, QType, Bytes, Spamlevel, Info, Header, Sender, File) " .
489 "SELECT 0, ID, ID, Time, QType, Bytes, Spamlevel, Info, Header, Sender, File FROM MailStore";
493 $cmd = "INSERT INTO CMSReceivers " .
494 "(CMailStore_CID, CMailStore_RID, PMail, Receiver, TicketID, Status, MTime) " .
495 "SELECT 0, MailStore_ID, PMail, Receiver, TicketID, Status, 0 FROM MSReceivers";
499 $dbh->do("SELECT setval ('cmailstore_id_seq', nextval ('mailstore_id_seq'))");
501 $dbh->do("DROP TABLE MailStore");
502 $dbh->do("DROP TABLE MSReceivers");
513 sub upgrade_dailystat_db
{
516 eval { # make sure we have MTime
517 $dbh->do("ALTER TABLE DailyStat ADD COLUMN MTime INTEGER;" .
518 "UPDATE DailyStat SET MTime = EXTRACT (EPOCH FROM now());");
521 eval { # make sure we have correct constraints for MTime
522 $dbh->do ("ALTER TABLE DailyStat ALTER COLUMN MTime SET NOT NULL;");
525 eval { # make sure we have RBLCount
526 $dbh->do ("ALTER TABLE DailyStat ADD COLUMN RBLCount INTEGER;" .
527 "UPDATE DailyStat SET RBLCount = 0;");
530 eval { # make sure we have correct constraints for RBLCount
531 $dbh->do ("ALTER TABLE DailyStat ALTER COLUMN RBLCount SET DEFAULT 0;" .
532 "ALTER TABLE DailyStat ALTER COLUMN RBLCount SET NOT NULL;");
538 my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('DailyStat_MTime_Index')";
540 my $sth = $dbh->prepare($cmd);
542 my $ref = $sth->fetchrow_hashref();
545 if (!$ref) { # index does not exist
546 $dbh->do ("CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime)");
557 sub upgrade_domainstat_db
{
560 eval { # make sure we have MTime
561 $dbh->do("ALTER TABLE DomainStat ADD COLUMN MTime INTEGER;" .
562 "UPDATE DomainStat SET MTime = EXTRACT (EPOCH FROM now());" .
563 "ALTER TABLE DomainStat ALTER COLUMN MTime SET NOT NULL;");
569 my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('DomainStat_MTime_Index')";
571 my $sth = $dbh->prepare($cmd);
573 my $ref = $sth->fetchrow_hashref();
576 if (!$ref) { # index does not exist
577 $dbh->do ("CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime)");
588 sub upgrade_statistic_db
{
594 my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('Statistic')";
596 my $sth = $dbh->prepare($cmd);
598 my $ref = $sth->fetchrow_hashref();
601 if ($ref) { # old table exists
603 my $timezone = tz_local_offset
();;
605 $dbh->do("INSERT INTO VirusInfo (Time, Name, Count, MTime) " .
606 "SELECT ((time + $timezone) / 86400) * 86400 as day, virusinfo, " .
607 "count (virusinfo), max (Time) FROM Statistic " .
608 "WHERE virusinfo IS NOT NULL GROUP BY day, virusinfo");
610 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime (time());
611 my $end = timelocal
(0, 0, 0, $mday, $mon, $year);
612 my $start = $end - 3600*24*7; # / days
614 $cmd = "INSERT INTO CStatistic " .
615 "(CID, RID, ID, Time, Bytes, Direction, Spamlevel, VirusInfo, PTime, Sender) " .
616 "SELECT 0, ID, ID, Time, Bytes, Direction, Spamlevel, VirusInfo, PTime, Sender FROM Statistic " .
617 "WHERE time >= $start";
621 $dbh->do("SELECT setval ('cstatistic_id_seq', nextval ('statistic_id_seq'))");
623 $dbh->do("INSERT INTO StatInfo (name, ivalue) VALUES ('virusinfo_index', " .
624 "nextval ('statistic_id_seq'))");
626 $cmd = "INSERT INTO CReceivers (CStatistic_CID, CStatistic_RID, Receiver, Blocked) " .
627 "SELECT 0, Mail_ID, Receiver, Blocked FROM Receivers " .
628 "WHERE EXISTS (SELECT * FROM CStatistic WHERE CID = 0 AND RID = Mail_ID)";
632 $dbh->do("DROP TABLE Statistic");
633 $dbh->do("DROP TABLE Receivers");
644 sub upgrade_greylist_db
{
650 my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('Greylist')";
652 my $sth = $dbh->prepare($cmd);
654 my $ref = $sth->fetchrow_hashref();
657 if ($ref) { # table exists
659 $cmd = "INSERT INTO CGreylist " .
660 "(IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, MTime, CID) " .
661 "SELECT IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, RCTime, 0 FROM Greylist";
665 $dbh->do("DROP TABLE Greylist");
676 sub upgrade_userprefs_db
{
680 $dbh->do("ALTER TABLE UserPrefs ADD COLUMN MTime INTEGER;" .
681 "UPDATE UserPrefs SET MTime = EXTRACT (EPOCH FROM now());" .
682 "ALTER TABLE UserPrefs ALTER COLUMN MTime SET NOT NULL;");
689 my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('UserPrefs_MTime_Index')";
691 my $sth = $dbh->prepare($cmd);
693 my $ref = $sth->fetchrow_hashref();
696 if (!$ref) { # index does not exist
697 $dbh->do("CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime)");
711 my $dbh = $ruledb->{dbh
};
713 $dbh->do($dbfunction_minint);
715 $dbh->do($dbfunction_maxint);
717 $dbh->do($dbfunction_merge_greylist);
719 # make sure we do not use slow sequential scans when upgraing
720 # database (before analyze can gather statistics)
721 $dbh->do("set enable_seqscan = false");
724 'DailyStat'=> $daily_stat_ctablecmd,
725 'DomainStat', $domain_stat_ctablecmd,
726 'StatInfo', $statinfo_ctablecmd,
727 'CMailStore', $cmailstore_ctablecmd,
728 'UserPrefs', $userprefs_ctablecmd,
729 'CGreylist', $cgreylist_ctablecmd,
730 'CStatistic', $cstatistic_ctablecmd,
731 'ClusterInfo', $clusterinfo_ctablecmd,
732 'VirusInfo', $virusinfo_stat_ctablecmd,
735 foreach my $table (keys %$tables) {
736 cond_create_dbtable
($dbh, $table, $tables->{$tables});
739 cond_create_std_actions
($ruledb);
741 upgrade_mailstore_db
($dbh);
743 upgrade_statistic_db
($dbh);
745 upgrade_userprefs_db
($dbh);
747 upgrade_greylist_db
($dbh);
749 upgrade_dailystat_db
($dbh);
751 upgrade_domainstat_db
($dbh);
753 # update obsolete content type names
755 $dbh->do("UPDATE Object " .
756 "SET value = 'content-type:application/java-vm' ".
757 "WHERE objecttype = 3003 " .
758 "AND value = 'content-type:application/x-java-vm';");
761 foreach my $table (keys %$tables) {
762 eval { $dbh->do("ANALYZE $table"); };
768 my ($ruledb, $reset, $testmode) = @_;
770 my $dbh = $ruledb->{dbh
};
773 # Greylist Objectgroup
774 my $greylistgroup = PMG
::RuleDB
::Group-
>new
775 ("GreyExclusion", "-", "greylist");
776 $ruledb->save_group ($greylistgroup);
779 # we do not touch greylist objects
780 my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
781 "objectgroup_id = objectgroup.id and class = 'greylist'";
783 $dbh->do ("DELETE FROM Rule; " .
784 "DELETE FROM RuleGroup; " .
785 "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
786 "DELETE FROM Object WHERE ID NOT IN ($glids); " .
787 "DELETE FROM Objectgroup WHERE class != 'greylist';");
793 my $obj = PMG
::RuleDB
::EMail-
>new ('nomail@fromthisdomain.com');
794 my $blacklist = $ruledb->create_group_with_obj(
795 $obj, 'Blacklist', 'Global blacklist');
798 $obj = PMG
::RuleDB
::EMail-
>new('mail@fromthisdomain.com');
799 my $whitelist = $ruledb->create_group_with_obj(
800 $obj, 'Whitelist', 'Global whitelist');
805 $obj = PMG
::RuleDB
::TimeFrame-
>new(8*60, 16*60);
806 my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
807 'Usual office hours');
812 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('image/.*');
813 my $img_content = $ruledb->create_group_with_obj(
814 $obj, 'Images', 'All kinds of graphic files');
817 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('audio/.*');
818 my $mm_content = $ruledb->create_group_with_obj(
819 $obj, 'Multimedia', 'Audio and Video');
821 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('video/.*');
822 $ruledb->group_add_object($mm_content, $obj);
825 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/vnd\.ms-excel');
826 my $office_content = $ruledb->create_group_with_obj(
827 $obj, 'Office Files', 'Common Office Files');
829 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
830 'application/vnd\.ms-powerpoint');
832 $ruledb->group_add_object($office_content, $obj);
834 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/msword');
835 $ruledb->group_add_object ($office_content, $obj);
837 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
838 'application/vnd\.openxmlformats-officedocument\..*');
839 $ruledb->group_add_object($office_content, $obj);
841 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
842 'application/vnd\.oasis\.opendocument\..*');
843 $ruledb->group_add_object($office_content, $obj);
845 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
846 'application/vnd\.stardivision\..*');
847 $ruledb->group_add_object($office_content, $obj);
849 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
850 'application/vnd\.sun\.xml\..*');
851 $ruledb->group_add_object($office_content, $obj);
854 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new(
855 'application/x-ms-dos-executable');
856 my $exe_content = $ruledb->create_group_with_obj(
857 $obj, 'Dangerous Content', 'executable files and partial messages');
859 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-java');
860 $ruledb->group_add_object($exe_content, $obj);
861 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/javascript');
862 $ruledb->group_add_object($exe_content, $obj);
863 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-executable');
864 $ruledb->group_add_object($exe_content, $obj);
865 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('application/x-ms-dos-executable');
866 $ruledb->group_add_object($exe_content, $obj);
867 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new('message/partial');
868 $ruledb->group_add_object($exe_content, $obj);
869 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.(vbs|pif|lnk|shs|shb)');
870 $ruledb->group_add_object($exe_content, $obj);
871 $obj = PMG
::RuleDB
::MatchFilename-
>new('.*\.\{.+\}');
872 $ruledb->group_add_object($exe_content, $obj);
875 $obj = PMG
::RuleDB
::Virus-
>new();
876 my $virus = $ruledb->create_group_with_obj(
877 $obj, 'Virus', 'Matches virus infected mail');
882 $obj = PMG
::RuleDB
::Spam-
>new(3);
883 my $spam3 = $ruledb->create_group_with_obj(
884 $obj, 'Spam (Level 3)', 'Matches possible spam mail');
886 $obj = PMG
::RuleDB
::Spam-
>new(5);
887 my $spam5 = $ruledb->create_group_with_obj(
888 $obj, 'Spam (Level 5)', 'Matches possible spam mail');
890 $obj = PMG
::RuleDB
::Spam-
>new(10);
891 my $spam10 = $ruledb->create_group_with_obj(
892 $obj, 'Spam (Level 10)', 'Matches possible spam mail');
897 $obj = PMG
::RuleDB
::ModField-
>new('X-SPAM-LEVEL', '__SPAM_INFO__');
898 my $mod_spam_level = $ruledb->create_group_with_obj(
899 $obj, 'Modify Spam Level',
900 'Mark mail as spam by adding a header tag.');
903 $obj = PMG
::RuleDB
::ModField-
>new('subject', 'SPAM: __SUBJECT__');
904 my $mod_spam_subject = $ruledb->create_group_with_obj(
905 $obj, 'Modify Spam Subject',
906 'Mark mail as spam by modifying the subject.');
908 # Remove matching attachments
909 $obj = PMG
::RuleDB
::Remove-
>new(0);
910 my $remove = $ruledb->create_group_with_obj(
911 $obj, 'Remove attachments', 'Remove matching attachments');
913 # Remove all attachments
914 $obj = PMG
::RuleDB
::Remove-
>new(1);
915 my $remove_all = $ruledb->create_group_with_obj(
916 $obj, 'Remove all attachments', 'Remove all attachments');
919 $obj = PMG
::RuleDB
::Accept-
>new();
920 my $accept = $ruledb->create_group_with_obj(
921 $obj, 'Accept', 'Accept mail for Delivery');
924 $obj = PMG
::RuleDB
::Block-
>new ();
925 my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
928 $obj = PMG
::RuleDB
::Quarantine-
>new();
929 my $quarantine = $ruledb->create_group_with_obj(
930 $obj, 'Quarantine', 'Move mail to quarantine');
933 $obj = PMG
::RuleDB
::Notify-
>new('__ADMIN__');
934 my $notify_admin = $ruledb->create_group_with_obj(
935 $obj, 'Notify Admin', 'Send notification');
938 $obj = PMG
::RuleDB
::Notify-
>new('__SENDER__');
939 my $notify_sender = $ruledb->create_group_with_obj(
940 $obj, 'Notify Sender', 'Send notification');
943 $obj = PMG
::RuleDB
::Disclaimer-
>new ();
944 my $add_discl = $ruledb->create_group_with_obj(
945 $obj, 'Disclaimer', 'Add Disclaimer');
947 # Attach original mail
948 #$obj = Proxmox::RuleDB::Attach->new ();
949 #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
950 # 'Attach Original Mail');
952 ####################### RULES ##################################
954 ## Block Dangerous Files
955 my $rule = PMG
::RuleDB
::Rule-
>new ('Block Dangerous Files', 93, 1, 0);
956 $ruledb->save_rule ($rule);
958 $ruledb->rule_add_what_group ($rule, $exe_content);
959 $ruledb->rule_add_action ($rule, $remove);
962 $rule = PMG
::RuleDB
::Rule-
>new ('Block Viruses', 96, 1, 0);
963 $ruledb->save_rule ($rule);
965 $ruledb->rule_add_what_group ($rule, $virus);
966 $ruledb->rule_add_action ($rule, $notify_admin);
969 $ruledb->rule_add_action ($rule, $block);
971 $ruledb->rule_add_action ($rule, $quarantine);
975 $rule = PMG
::RuleDB
::Rule-
>new ('Virus Alert', 96, 1, 1);
976 $ruledb->save_rule ($rule);
978 $ruledb->rule_add_what_group ($rule, $virus);
979 $ruledb->rule_add_action ($rule, $notify_sender);
980 $ruledb->rule_add_action ($rule, $notify_admin);
981 $ruledb->rule_add_action ($rule, $block);
984 $rule = PMG
::RuleDB
::Rule-
>new ('Blacklist', 98, 1, 0);
985 $ruledb->save_rule ($rule);
987 $ruledb->rule_add_from_group ($rule, $blacklist);
988 $ruledb->rule_add_action ($rule, $block);
992 $rule = PMG
::RuleDB
::Rule-
>new ('Modify Header', 90, 1, 0);
993 $ruledb->save_rule ($rule);
994 $ruledb->rule_add_action ($rule, $mod_spam_level);
998 $rule = PMG
::RuleDB
::Rule-
>new ('Whitelist', 85, 1, 0);
999 $ruledb->save_rule ($rule);
1001 $ruledb->rule_add_from_group ($rule, $whitelist);
1002 $ruledb->rule_add_action ($rule, $accept);
1005 $rule = PMG
::RuleDB
::Rule-
>new ('Mark Spam', 80, 1, 0);
1006 $ruledb->save_rule ($rule);
1008 $ruledb->rule_add_what_group ($rule, $spam10);
1009 $ruledb->rule_add_action ($rule, $mod_spam_level);
1010 $ruledb->rule_add_action ($rule, $mod_spam_subject);
1012 # Quarantine/Mark Spam (Level 3)
1013 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
1014 $ruledb->save_rule ($rule);
1016 $ruledb->rule_add_what_group ($rule, $spam3);
1017 $ruledb->rule_add_action ($rule, $mod_spam_subject);
1018 $ruledb->rule_add_action ($rule, $quarantine);
1019 #$ruledb->rule_add_action ($rule, $count_spam);
1022 # Quarantine/Mark Spam (Level 5)
1023 $rule = PMG
::RuleDB
::Rule-
>new ('Quarantine/Mark Spam (Level 5)', 79, 0, 0);
1024 $ruledb->save_rule ($rule);
1026 $ruledb->rule_add_what_group ($rule, $spam5);
1027 $ruledb->rule_add_action ($rule, $mod_spam_subject);
1028 $ruledb->rule_add_action ($rule, $quarantine);
1030 ## Block Spam Level 10
1031 $rule = PMG
::RuleDB
::Rule-
>new ('Block Spam (Level 10)', 78, 0, 0);
1032 $ruledb->save_rule ($rule);
1034 $ruledb->rule_add_what_group ($rule, $spam10);
1035 $ruledb->rule_add_action ($rule, $block);
1037 ## Block Outgoing Spam
1038 $rule = PMG
::RuleDB
::Rule-
>new ('Block outgoing Spam', 70, 0, 1);
1039 $ruledb->save_rule ($rule);
1041 $ruledb->rule_add_what_group ($rule, $spam3);
1042 $ruledb->rule_add_action ($rule, $notify_admin);
1043 $ruledb->rule_add_action ($rule, $notify_sender);
1044 $ruledb->rule_add_action ($rule, $block);
1047 $rule = PMG
::RuleDB
::Rule-
>new ('Add Disclaimer', 60, 0, 1);
1048 $ruledb->save_rule ($rule);
1049 $ruledb->rule_add_action ($rule, $add_discl);
1051 # Block Multimedia Files
1052 $rule = PMG
::RuleDB
::Rule-
>new ('Block Multimedia Files', 87, 0, 2);
1053 $ruledb->save_rule ($rule);
1055 $ruledb->rule_add_what_group ($rule, $mm_content);
1056 $ruledb->rule_add_action ($rule, $remove);
1058 #$ruledb->rule_add_from_group ($rule, $anybody);
1059 #$ruledb->rule_add_from_group ($rule, $trusted);
1060 #$ruledb->rule_add_to_group ($rule, $anybody);
1061 #$ruledb->rule_add_what_group ($rule, $ct_filter);
1062 #$ruledb->rule_add_action ($rule, $add_discl);
1063 #$ruledb->rule_add_action ($rule, $remove);
1064 #$ruledb->rule_add_action ($rule, $bcc);
1065 #$ruledb->rule_add_action ($rule, $storeq);
1066 #$ruledb->rule_add_action ($rule, $accept);
1068 cond_create_std_actions
($ruledb);
1071 sub get_remote_time
{
1074 my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
1076 my $ctinfo = $sth->fetchrow_hashref();
1079 return $ctinfo ?
$ctinfo->{ctime
} : 0;
1083 my ($lcid, $database) = @_;
1085 die "got unexpected cid for new master" if !$lcid;
1090 $dbh = open_ruledb
($database);
1094 print STDERR
"update quarantine database\n";
1095 $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
1096 "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
1098 print STDERR
"update statistic database\n";
1099 $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
1100 "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
1102 print STDERR
"update greylist database\n";
1103 $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
1110 $dbh->rollback if $err;
1117 sub update_master_clusterinfo
{
1118 my ($clientcid) = @_;
1120 my $dbh = open_ruledb
();
1122 $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
1124 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'VirusInfo');
1126 foreach my $table (@mt) {
1127 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
1128 "EXTRACT(EPOCH FROM now())");
1132 sub update_client_clusterinfo
{
1133 my ($mastercid) = @_;
1135 my $dbh = open_ruledb
();
1137 $dbh->do ("DELETE FROM StatInfo"); # not needed at node
1139 $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
1141 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
1142 "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
1144 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
1145 "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
1147 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'VirusInfo');
1149 foreach my $table (@mt) {
1150 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
1151 "COALESCE (max (mtime), 0) FROM $table");
1158 my $ni = $cinfo->{master
};
1160 die "no master defined - unable to sync data from master\n" if !$ni;
1162 my $master_ip = $ni->{ip
};
1163 my $master_cid = $ni->{cid
};
1164 my $master_name = $ni->{name
};
1166 my $fn = "/tmp/masterdb$$.tar";
1169 my $dbname = $default_db_name;
1172 print STDERR
"copying master database from '${master_ip}'\n";
1174 open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
1177 ['/usr/bin/ssh', '-o', 'BatchMode=yes',
1178 '-o', "HostKeyAlias=${master_name}",
1179 $master_ip, 'pg_dump'],
1180 { output
=> '>&' . fileno($fh) },
1181 $dbname, '-F', 'c');
1187 print STDERR
"copying master database finished (got $size bytes)\n";
1189 print STDERR
"delete local database\n";
1191 postgres_admin_cmd
('dropdb', undef, $dbname , '--if-exists');
1193 print STDERR
"create new local database\n";
1195 postgres_admin_cmd
('createdb', undef, $dbname);
1197 print STDERR
"insert received data into local database\n";
1203 if ($line =~ m/restoring data for table \"(.+)\"/) {
1204 print STDERR
"restoring table $1\n";
1205 } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
1206 $mess = "creating indexes";
1207 print STDERR
"$mess\n";
1214 errmsg
=> "pg_restore failed"
1217 postgres_admin_cmd
('pg_restore', $opts, '-d', $dbname, '-v', $fn);
1219 print STDERR
"run analyze to speed up database queries\n";
1221 postgres_admin_cmd
('psql', { input
=> 'analyze;' }, $dbname);
1223 update_client_clusterinfo
($master_cid);
1233 sub cluster_sync_status
{
1240 foreach my $ni (values %{$cinfo->{ids
}}) {
1241 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1242 $minmtime->{$ni->{cid
}} = 0;
1246 $dbh = open_ruledb
();
1248 my $sth = $dbh->prepare(
1249 "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " .
1250 "WHERE name = 'lastsync' AND ivalue > 0 " .
1255 while (my $info = $sth->fetchrow_hashref()) {
1256 foreach my $ni (values %{$cinfo->{ids
}}) {
1257 next if $cinfo->{local}->{cid
} == $ni->{cid
}; # skip local CID
1258 if ($ni->{cid
} == $info->{cid
}) { # node exists
1259 $minmtime->{$ni->{cid
}} = $info->{minmtime
};
1268 $dbh->disconnect() if $dbh;
1271 syslog
('err', PMG
::Utils
::msgquote
($err));