use POSIX ":sys_wait_h";
use POSIX ':signal_h';
use DBI;
+use Time::Local;
+use PVE::SafeSyslog;
use PVE::Tools;
+use PMG::Utils;
+use PMG::RuleDB;
+use PMG::MailQueue;
+use PMG::Config;
+
+our $default_db_name = "Proxmox_ruledb";
+
+our $cgreylist_merge_sql =
+ 'INSERT INTO CGREYLIST (IPNet,Host,Sender,Receiver,Instance,RCTime,' .
+ 'ExTime,Delay,Blocked,Passed,MTime,CID) ' .
+ 'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ' .
+ 'ON CONFLICT (IPNet,Sender,Receiver) DO UPDATE SET ' .
+ 'Host = CASE WHEN CGREYLIST.MTime >= excluded.MTime THEN CGREYLIST.Host ELSE excluded.Host END,' .
+ 'CID = GREATEST(CGREYLIST.CID, excluded.CID), RCTime = LEAST(CGREYLIST.RCTime, excluded.RCTime),' .
+ 'ExTime = GREATEST(CGREYLIST.ExTime, excluded.ExTime),' .
+ 'Delay = GREATEST(CGREYLIST.Delay, excluded.Delay),' .
+ 'Blocked = GREATEST(CGREYLIST.Blocked, excluded.Blocked),' .
+ 'Passed = GREATEST(CGREYLIST.Passed, excluded.Passed)';
+
sub open_ruledb {
my ($database, $host, $port) = @_;
- $port = 5432 if !$port;
+ $port //= 5432;
- $database = "Proxmox_ruledb" if !$database;
+ $database //= $default_db_name;
if ($host) {
+ # Note: pmgtunnel uses UDP sockets inside directory '/var/run/pmgtunnel',
+ # and the cluster 'cid' as port number. You can connect to the
+ # socket with: host => /var/run/pmgtunnel, port => $cid
+
my $dsn = "dbi:Pg:dbname=$database;host=$host;port=$port;";
my $timeout = 5;
eval {
alarm($timeout);
- $rdb = DBI->connect($dsn, "postgres", undef,
+ $rdb = DBI->connect($dsn, 'root', undef,
{ PrintError => 0, RaiseError => 1 });
alarm(0);
};
alarm(0);
sigaction(SIGALRM, $oldaction); # restore original handler
-
+
die $@ if $@;
return $rdb;
} else {
- my $dsn = "DBI:Pg:dbname=$database";
+ my $dsn = "DBI:Pg:dbname=$database;host=/var/run/postgresql;port=$port";
- my $dbh = DBI->connect($dsn, "postgres", undef,
+ my $dbh = DBI->connect($dsn, $> == 0 ? 'root' : 'www-data', undef,
{ PrintError => 0, RaiseError => 1 });
return $dbh;
}
}
+sub postgres_admin_cmd {
+ my ($cmd, $options, @params) = @_;
+
+ $cmd = ref($cmd) ? $cmd : [ $cmd ];
+ my $uid = getpwnam('postgres') || die "getpwnam postgres failed\n";
+
+ local $> = $uid;
+ $! && die "setuid postgres ($uid) failed - $!\n";
+
+ PVE::Tools::run_command([@$cmd, '-U', 'postgres', @params], %$options);
+}
+
sub delete_ruledb {
my ($dbname) = @_;
- PVE::Tools::run_command(['dropdb', '-U', 'postgres', $dbname]);
+ postgres_admin_cmd('dropdb', undef, $dbname);
+}
+
+sub database_list {
+
+ my $database_list = {};
+
+ my $parser = sub {
+ my $line = shift;
+
+ my ($name, $owner) = map { PVE::Tools::trim($_) } split(/\|/, $line);
+ return if !$name || !$owner;
+
+ $database_list->{$name} = { owner => $owner };
+ };
+
+ postgres_admin_cmd('psql', { outfunc => $parser }, '--list', '--quiet', '--tuples-only');
+
+ return $database_list;
+}
+
+my $cgreylist_ctablecmd = <<__EOD;
+ CREATE TABLE CGreylist
+ (IPNet VARCHAR(16) NOT NULL,
+ Host INTEGER NOT NULL,
+ Sender VARCHAR(255) NOT NULL,
+ Receiver VARCHAR(255) NOT NULL,
+ Instance VARCHAR(255),
+ RCTime INTEGER NOT NULL,
+ ExTime INTEGER NOT NULL,
+ Delay INTEGER NOT NULL DEFAULT 0,
+ Blocked INTEGER NOT NULL,
+ Passed INTEGER NOT NULL,
+ CID INTEGER NOT NULL,
+ MTime INTEGER NOT NULL,
+ PRIMARY KEY (IPNet, Sender, Receiver));
+
+ CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender);
+
+ CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime);
+
+ CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime);
+__EOD
+
+my $clusterinfo_ctablecmd = <<__EOD;
+ CREATE TABLE ClusterInfo
+ (CID INTEGER NOT NULL,
+ Name VARCHAR NOT NULL,
+ IValue INTEGER,
+ SValue VARCHAR,
+ PRIMARY KEY (CID, Name))
+__EOD
+
+my $local_stat_ctablecmd = <<__EOD;
+ CREATE TABLE LocalStat
+ (Time INTEGER NOT NULL UNIQUE,
+ RBLCount INTEGER DEFAULT 0 NOT NULL,
+ PregreetCount INTEGER DEFAULT 0 NOT NULL,
+ CID INTEGER NOT NULL,
+ MTime INTEGER NOT NULL,
+ PRIMARY KEY (Time, CID));
+
+ CREATE INDEX LocalStat_MTime_Index ON LocalStat (MTime);
+__EOD
+
+
+my $daily_stat_ctablecmd = <<__EOD;
+ CREATE TABLE DailyStat
+ (Time INTEGER NOT NULL UNIQUE,
+ CountIn INTEGER NOT NULL,
+ CountOut INTEGER NOT NULL,
+ BytesIn REAL NOT NULL,
+ BytesOut REAL NOT NULL,
+ VirusIn INTEGER NOT NULL,
+ VirusOut INTEGER NOT NULL,
+ SpamIn INTEGER NOT NULL,
+ SpamOut INTEGER NOT NULL,
+ BouncesIn INTEGER NOT NULL,
+ BouncesOut INTEGER NOT NULL,
+ GreylistCount INTEGER NOT NULL,
+ SPFCount INTEGER NOT NULL,
+ PTimeSum REAL NOT NULL,
+ MTime INTEGER NOT NULL,
+ RBLCount INTEGER DEFAULT 0 NOT NULL,
+ PRIMARY KEY (Time));
+
+ CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
+
+__EOD
+
+my $domain_stat_ctablecmd = <<__EOD;
+ CREATE TABLE DomainStat
+ (Time INTEGER NOT NULL,
+ Domain VARCHAR(255) NOT NULL,
+ CountIn INTEGER NOT NULL,
+ CountOut INTEGER NOT NULL,
+ BytesIn REAL NOT NULL,
+ BytesOut REAL NOT NULL,
+ VirusIn INTEGER NOT NULL,
+ VirusOut INTEGER NOT NULL,
+ SpamIn INTEGER NOT NULL,
+ SpamOut INTEGER NOT NULL,
+ BouncesIn INTEGER NOT NULL,
+ BouncesOut INTEGER NOT NULL,
+ PTimeSum REAL NOT NULL,
+ MTime INTEGER NOT NULL,
+ PRIMARY KEY (Time, Domain));
+
+ CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
+__EOD
+
+my $statinfo_ctablecmd = <<__EOD;
+ CREATE TABLE StatInfo
+ (Name VARCHAR(255) NOT NULL UNIQUE,
+ IValue INTEGER,
+ SValue VARCHAR(255),
+ PRIMARY KEY (Name))
+__EOD
+
+my $virusinfo_stat_ctablecmd = <<__EOD;
+ CREATE TABLE VirusInfo
+ (Time INTEGER NOT NULL,
+ Name VARCHAR NOT NULL,
+ Count INTEGER NOT NULL,
+ MTime INTEGER NOT NULL,
+ PRIMARY KEY (Time, Name));
+
+ CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
+
+__EOD
+
+# mail storage table
+# QTypes
+# V - Virus quarantine
+# S - Spam quarantine
+# D - Delayed Mails - not implemented
+# A - Held for Audit - not implemented
+# Status
+# N - new
+# D - deleted
+
+my $cmailstore_ctablecmd = <<__EOD;
+ CREATE TABLE CMailStore
+ (CID INTEGER DEFAULT 0 NOT NULL,
+ RID INTEGER NOT NULL,
+ ID SERIAL UNIQUE,
+ Time INTEGER NOT NULL,
+ QType "char" NOT NULL,
+ Bytes INTEGER NOT NULL,
+ Spamlevel INTEGER NOT NULL,
+ Info VARCHAR NULL,
+ Sender VARCHAR(255) NOT NULL,
+ Header VARCHAR NOT NULL,
+ File VARCHAR(255) NOT NULL,
+ PRIMARY KEY (CID, RID));
+ CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
+
+ CREATE TABLE CMSReceivers
+ (CMailStore_CID INTEGER NOT NULL,
+ CMailStore_RID INTEGER NOT NULL,
+ PMail VARCHAR(255) NOT NULL,
+ Receiver VARCHAR(255),
+ TicketID INTEGER NOT NULL,
+ Status "char" NOT NULL,
+ MTime INTEGER NOT NULL);
+
+ CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
+
+ CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
+
+__EOD
+
+my $cstatistic_ctablecmd = <<__EOD;
+ CREATE TABLE CStatistic
+ (CID INTEGER DEFAULT 0 NOT NULL,
+ RID INTEGER NOT NULL,
+ ID SERIAL UNIQUE,
+ Time INTEGER NOT NULL,
+ Bytes INTEGER NOT NULL,
+ Direction Boolean NOT NULL,
+ Spamlevel INTEGER NOT NULL,
+ VirusInfo VARCHAR(255) NULL,
+ PTime INTEGER NOT NULL,
+ Sender VARCHAR(255) NOT NULL,
+ PRIMARY KEY (CID, RID));
+
+ CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
+
+ CREATE TABLE CReceivers
+ (CStatistic_CID INTEGER NOT NULL,
+ CStatistic_RID INTEGER NOT NULL,
+ Receiver VARCHAR(255) NOT NULL,
+ Blocked Boolean NOT NULL);
+
+ CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
+__EOD
+
+# user preferences (black an whitelists, ...)
+# Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
+# Data: arbitrary data
+my $userprefs_ctablecmd = <<__EOD;
+ CREATE TABLE UserPrefs
+ (PMail VARCHAR,
+ Name VARCHAR(255),
+ Data VARCHAR,
+ MTime INTEGER NOT NULL,
+ PRIMARY KEY (PMail, Name));
+
+ CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
+
+__EOD
+
+sub cond_create_dbtable {
+ my ($dbh, $name, $ctablecmd) = @_;
+
+ eval {
+ $dbh->begin_work;
+
+ my $cmd = "SELECT tablename FROM pg_tables " .
+ "WHERE tablename = lower ('$name')";
+
+ my $sth = $dbh->prepare($cmd);
+
+ $sth->execute();
+
+ if (!(my $ref = $sth->fetchrow_hashref())) {
+ $dbh->do ($ctablecmd);
+ }
+
+ $sth->finish();
+
+ $dbh->commit;
+ };
+ if (my $err = $@) {
+ $dbh->rollback;
+ die $err;
+ }
+}
+
+sub database_column_exists {
+ my ($dbh, $table, $column) = @_;
+
+ my $sth = $dbh->prepare(
+ "SELECT column_name FROM information_schema.columns " .
+ "WHERE table_name = ? and column_name = ?");
+ $sth->execute(lc($table), lc($column));
+ my $res = $sth->fetchrow_hashref();
+ return defined($res);
+}
+
+sub create_ruledb {
+ my ($dbname) = @_;
+
+ $dbname = $default_db_name if !$dbname;
+
+ my $silent_opts = { outfunc => sub {}, errfunc => sub {} };
+ # make sure we have user 'root'
+ eval { postgres_admin_cmd('createuser', $silent_opts, '-D', 'root'); };
+ # also create 'www-data' (and give it read-only access below)
+ eval { postgres_admin_cmd('createuser', $silent_opts, '-I', '-D', 'www-data'); };
+
+ # use sql_ascii to avoid any character set conversions, and be compatible with
+ # older postgres versions (update from 8.1 must be possible)
+
+ postgres_admin_cmd('createdb', undef, '-E', 'sql_ascii', '-T', 'template0',
+ '--lc-collate=C', '--lc-ctype=C', $dbname);
+
+ my $dbh = open_ruledb($dbname);
+
+ # make sure 'www-data' can read all tables
+ $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\"");
+
+ $dbh->do (
+<<EOD
+ CREATE TABLE Attribut
+ (Object_ID INTEGER NOT NULL,
+ Name VARCHAR(20) NOT NULL,
+ Value BYTEA NULL,
+ PRIMARY KEY (Object_ID, Name));
+
+ CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
+
+ CREATE TABLE Object
+ (ID SERIAL UNIQUE,
+ ObjectType INTEGER NOT NULL,
+ Objectgroup_ID INTEGER NOT NULL,
+ Value BYTEA NULL,
+ PRIMARY KEY (ID));
+
+ CREATE TABLE Objectgroup
+ (ID SERIAL UNIQUE,
+ Name VARCHAR(255) NOT NULL,
+ Info VARCHAR(255) NULL,
+ Class VARCHAR(10) NOT NULL,
+ PRIMARY KEY (ID));
+
+ CREATE TABLE Rule
+ (ID SERIAL UNIQUE,
+ Name VARCHAR(255) NULL,
+ Priority INTEGER NOT NULL,
+ Active INTEGER NOT NULL DEFAULT 0,
+ Direction INTEGER NOT NULL DEFAULT 2,
+ Count INTEGER NOT NULL DEFAULT 0,
+ PRIMARY KEY (ID));
+
+ CREATE TABLE RuleGroup
+ (Objectgroup_ID INTEGER NOT NULL,
+ Rule_ID INTEGER NOT NULL,
+ Grouptype INTEGER NOT NULL,
+ PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
+
+ $cgreylist_ctablecmd;
+
+ $clusterinfo_ctablecmd;
+
+ $local_stat_ctablecmd;
+
+ $daily_stat_ctablecmd;
+
+ $domain_stat_ctablecmd;
+
+ $statinfo_ctablecmd;
+
+ $cmailstore_ctablecmd;
+
+ $cstatistic_ctablecmd;
+
+ $userprefs_ctablecmd;
+
+ $virusinfo_stat_ctablecmd;
+EOD
+ );
+
+ return $dbh;
+}
+
+sub cond_create_action_quarantine {
+ my ($ruledb) = @_;
+
+ my $dbh = $ruledb->{dbh};
+
+ eval {
+ my $sth = $dbh->prepare(
+ "SELECT * FROM Objectgroup, Object " .
+ "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " .
+ "AND Object.objectgroup_id = Objectgroup.id");
+
+ my $otype = PMG::RuleDB::Quarantine::otype();
+ if ($sth->execute($otype, 'action') <= 0) {
+ my $obj = PMG::RuleDB::Quarantine->new ();
+ my $txt = decode_entities(PMG::RuleDB::Quarantine->otype_text);
+ my $quarantine = $ruledb->create_group_with_obj
+ ($obj, $txt, 'Move to quarantine.');
+ }
+ };
+}
+
+sub cond_create_std_actions {
+ my ($ruledb) = @_;
+
+ cond_create_action_quarantine($ruledb);
+
+ #cond_create_action_report_spam($ruledb);
+}
+
+
+sub upgradedb {
+ my ($ruledb) = @_;
+
+ my $dbh = $ruledb->{dbh};
+
+ # make sure we do not use slow sequential scans when upgraing
+ # database (before analyze can gather statistics)
+ $dbh->do("set enable_seqscan = false");
+
+ my $tables = {
+ 'LocalStat', $local_stat_ctablecmd,
+ 'DailyStat', $daily_stat_ctablecmd,
+ 'DomainStat', $domain_stat_ctablecmd,
+ 'StatInfo', $statinfo_ctablecmd,
+ 'CMailStore', $cmailstore_ctablecmd,
+ 'UserPrefs', $userprefs_ctablecmd,
+ 'CGreylist', $cgreylist_ctablecmd,
+ 'CStatistic', $cstatistic_ctablecmd,
+ 'ClusterInfo', $clusterinfo_ctablecmd,
+ 'VirusInfo', $virusinfo_stat_ctablecmd,
+ };
+
+ foreach my $table (keys %$tables) {
+ cond_create_dbtable($dbh, $table, $tables->{$table});
+ }
+
+ cond_create_std_actions($ruledb);
+
+ # upgrade tables here if necessary
+ if (!database_column_exists($dbh, 'LocalStat', 'PregreetCount')) {
+ $dbh->do("ALTER TABLE LocalStat ADD COLUMN " .
+ "PregreetCount INTEGER DEFAULT 0 NOT NULL");
+ }
+
+ # add missing TicketID to CMSReceivers
+ if (!database_column_exists($dbh, 'CMSReceivers', 'TicketID')) {
+ eval {
+ $dbh->begin_work;
+ $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq");
+ $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " .
+ "TicketID INTEGER NOT NULL " .
+ "DEFAULT nextval('cmsreceivers_ticketid_seq')");
+ $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " .
+ "TicketID DROP DEFAULT");
+ $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq");
+ $dbh->commit;
+ };
+ if (my $err = $@) {
+ $dbh->rollback;
+ die $err;
+ }
+ }
+
+ # update obsolete content type names
+ eval {
+ $dbh->do("UPDATE Object " .
+ "SET value = 'content-type:application/java-vm' ".
+ "WHERE objecttype = 3003 " .
+ "AND value = 'content-type:application/x-java-vm';");
+ };
+
+ foreach my $table (keys %$tables) {
+ eval { $dbh->do("ANALYZE $table"); };
+ warn $@ if $@;
+ }
+
+ reload_ruledb();
+}
+
+sub init_ruledb {
+ my ($ruledb, $reset, $testmode) = @_;
+
+ my $dbh = $ruledb->{dbh};
+
+ if (!$reset) {
+ # Greylist Objectgroup
+ my $greylistgroup = PMG::RuleDB::Group->new
+ ("GreyExclusion", "-", "greylist");
+ $ruledb->save_group ($greylistgroup);
+
+ } else {
+ # we do not touch greylist objects
+ my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
+ "objectgroup_id = objectgroup.id and class = 'greylist'";
+
+ $dbh->do ("DELETE FROM Rule; " .
+ "DELETE FROM RuleGroup; " .
+ "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
+ "DELETE FROM Object WHERE ID NOT IN ($glids); " .
+ "DELETE FROM Objectgroup WHERE class != 'greylist';");
+ }
+
+ # WHO Objects
+
+ # Blacklist
+ my $obj = PMG::RuleDB::EMail->new ('nomail@fromthisdomain.com');
+ my $blacklist = $ruledb->create_group_with_obj(
+ $obj, 'Blacklist', 'Global blacklist');
+
+ # Whitelist
+ $obj = PMG::RuleDB::EMail->new('mail@fromthisdomain.com');
+ my $whitelist = $ruledb->create_group_with_obj(
+ $obj, 'Whitelist', 'Global whitelist');
+
+ # WHEN Objects
+
+ # Working hours
+ $obj = PMG::RuleDB::TimeFrame->new(8*60, 16*60);
+ my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
+ 'Usual office hours');
+
+ # WHAT Objects
+
+ # Images
+ $obj = PMG::RuleDB::ContentTypeFilter->new('image/.*');
+ my $img_content = $ruledb->create_group_with_obj(
+ $obj, 'Images', 'All kinds of graphic files');
+
+ # Multimedia
+ $obj = PMG::RuleDB::ContentTypeFilter->new('audio/.*');
+ my $mm_content = $ruledb->create_group_with_obj(
+ $obj, 'Multimedia', 'Audio and Video');
+
+ $obj = PMG::RuleDB::ContentTypeFilter->new('video/.*');
+ $ruledb->group_add_object($mm_content, $obj);
+
+ # Office Files
+ $obj = PMG::RuleDB::ContentTypeFilter->new('application/vnd\.ms-excel');
+ my $office_content = $ruledb->create_group_with_obj(
+ $obj, 'Office Files', 'Common Office Files');
+
+ $obj = PMG::RuleDB::ContentTypeFilter->new(
+ 'application/vnd\.ms-powerpoint');
+
+ $ruledb->group_add_object($office_content, $obj);
+
+ $obj = PMG::RuleDB::ContentTypeFilter->new('application/msword');
+ $ruledb->group_add_object ($office_content, $obj);
+
+ $obj = PMG::RuleDB::ContentTypeFilter->new(
+ 'application/vnd\.openxmlformats-officedocument\..*');
+ $ruledb->group_add_object($office_content, $obj);
+
+ $obj = PMG::RuleDB::ContentTypeFilter->new(
+ 'application/vnd\.oasis\.opendocument\..*');
+ $ruledb->group_add_object($office_content, $obj);
+
+ $obj = PMG::RuleDB::ContentTypeFilter->new(
+ 'application/vnd\.stardivision\..*');
+ $ruledb->group_add_object($office_content, $obj);
+
+ $obj = PMG::RuleDB::ContentTypeFilter->new(
+ 'application/vnd\.sun\.xml\..*');
+ $ruledb->group_add_object($office_content, $obj);
+
+ # Dangerous Content
+ $obj = PMG::RuleDB::ContentTypeFilter->new(
+ 'application/x-ms-dos-executable');
+ my $exe_content = $ruledb->create_group_with_obj(
+ $obj, 'Dangerous Content', 'executable files and partial messages');
+
+ $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-java');
+ $ruledb->group_add_object($exe_content, $obj);
+ $obj = PMG::RuleDB::ContentTypeFilter->new('application/javascript');
+ $ruledb->group_add_object($exe_content, $obj);
+ $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-executable');
+ $ruledb->group_add_object($exe_content, $obj);
+ $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-ms-dos-executable');
+ $ruledb->group_add_object($exe_content, $obj);
+ $obj = PMG::RuleDB::ContentTypeFilter->new('message/partial');
+ $ruledb->group_add_object($exe_content, $obj);
+ $obj = PMG::RuleDB::MatchFilename->new('.*\.(vbs|pif|lnk|shs|shb)');
+ $ruledb->group_add_object($exe_content, $obj);
+ $obj = PMG::RuleDB::MatchFilename->new('.*\.\{.+\}');
+ $ruledb->group_add_object($exe_content, $obj);
+
+ # Virus
+ $obj = PMG::RuleDB::Virus->new();
+ my $virus = $ruledb->create_group_with_obj(
+ $obj, 'Virus', 'Matches virus infected mail');
+
+ # WHAT Objects
+
+ # Spam
+ $obj = PMG::RuleDB::Spam->new(3);
+ my $spam3 = $ruledb->create_group_with_obj(
+ $obj, 'Spam (Level 3)', 'Matches possible spam mail');
+
+ $obj = PMG::RuleDB::Spam->new(5);
+ my $spam5 = $ruledb->create_group_with_obj(
+ $obj, 'Spam (Level 5)', 'Matches possible spam mail');
+
+ $obj = PMG::RuleDB::Spam->new(10);
+ my $spam10 = $ruledb->create_group_with_obj(
+ $obj, 'Spam (Level 10)', 'Matches possible spam mail');
+
+ # ACTIONS
+
+ # Mark Spam
+ $obj = PMG::RuleDB::ModField->new('X-SPAM-LEVEL', '__SPAM_INFO__');
+ my $mod_spam_level = $ruledb->create_group_with_obj(
+ $obj, 'Modify Spam Level',
+ 'Mark mail as spam by adding a header tag.');
+
+ # Mark Spam
+ $obj = PMG::RuleDB::ModField->new('subject', 'SPAM: __SUBJECT__');
+ my $mod_spam_subject = $ruledb->create_group_with_obj(
+ $obj, 'Modify Spam Subject',
+ 'Mark mail as spam by modifying the subject.');
+
+ # Remove matching attachments
+ $obj = PMG::RuleDB::Remove->new(0);
+ my $remove = $ruledb->create_group_with_obj(
+ $obj, 'Remove attachments', 'Remove matching attachments');
+
+ # Remove all attachments
+ $obj = PMG::RuleDB::Remove->new(1);
+ my $remove_all = $ruledb->create_group_with_obj(
+ $obj, 'Remove all attachments', 'Remove all attachments');
+
+ # Accept
+ $obj = PMG::RuleDB::Accept->new();
+ my $accept = $ruledb->create_group_with_obj(
+ $obj, 'Accept', 'Accept mail for Delivery');
+
+ # Block
+ $obj = PMG::RuleDB::Block->new ();
+ my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
+
+ # Quarantine
+ $obj = PMG::RuleDB::Quarantine->new();
+ my $quarantine = $ruledb->create_group_with_obj(
+ $obj, 'Quarantine', 'Move mail to quarantine');
+
+ # Notify Admin
+ $obj = PMG::RuleDB::Notify->new('__ADMIN__');
+ my $notify_admin = $ruledb->create_group_with_obj(
+ $obj, 'Notify Admin', 'Send notification');
+
+ # Notify Sender
+ $obj = PMG::RuleDB::Notify->new('__SENDER__');
+ my $notify_sender = $ruledb->create_group_with_obj(
+ $obj, 'Notify Sender', 'Send notification');
+
+ # Add Disclaimer
+ $obj = PMG::RuleDB::Disclaimer->new ();
+ my $add_discl = $ruledb->create_group_with_obj(
+ $obj, 'Disclaimer', 'Add Disclaimer');
+
+ # Attach original mail
+ #$obj = Proxmox::RuleDB::Attach->new ();
+ #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
+ # 'Attach Original Mail');
+
+ ####################### RULES ##################################
+
+ ## Block Dangerous Files
+ my $rule = PMG::RuleDB::Rule->new ('Block Dangerous Files', 93, 1, 0);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_what_group ($rule, $exe_content);
+ $ruledb->rule_add_action ($rule, $remove);
+
+ ## Block Viruses
+ $rule = PMG::RuleDB::Rule->new ('Block Viruses', 96, 1, 0);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_what_group ($rule, $virus);
+ $ruledb->rule_add_action ($rule, $notify_admin);
+
+ if ($testmode) {
+ $ruledb->rule_add_action ($rule, $block);
+ } else {
+ $ruledb->rule_add_action ($rule, $quarantine);
+ }
+
+ ## Virus Alert
+ $rule = PMG::RuleDB::Rule->new ('Virus Alert', 96, 1, 1);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_what_group ($rule, $virus);
+ $ruledb->rule_add_action ($rule, $notify_sender);
+ $ruledb->rule_add_action ($rule, $notify_admin);
+ $ruledb->rule_add_action ($rule, $block);
+
+ ## Blacklist
+ $rule = PMG::RuleDB::Rule->new ('Blacklist', 98, 1, 0);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_from_group ($rule, $blacklist);
+ $ruledb->rule_add_action ($rule, $block);
+
+ ## Modify header
+ if (!$testmode) {
+ $rule = PMG::RuleDB::Rule->new ('Modify Header', 90, 1, 0);
+ $ruledb->save_rule ($rule);
+ $ruledb->rule_add_action ($rule, $mod_spam_level);
+ }
+
+ ## Whitelist
+ $rule = PMG::RuleDB::Rule->new ('Whitelist', 85, 1, 0);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_from_group ($rule, $whitelist);
+ $ruledb->rule_add_action ($rule, $accept);
+
+ if ($testmode) {
+ $rule = PMG::RuleDB::Rule->new ('Mark Spam', 80, 1, 0);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_what_group ($rule, $spam10);
+ $ruledb->rule_add_action ($rule, $mod_spam_level);
+ $ruledb->rule_add_action ($rule, $mod_spam_subject);
+ } else {
+ # Quarantine/Mark Spam (Level 3)
+ $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_what_group ($rule, $spam3);
+ $ruledb->rule_add_action ($rule, $mod_spam_subject);
+ $ruledb->rule_add_action ($rule, $quarantine);
+ #$ruledb->rule_add_action ($rule, $count_spam);
+ }
+
+ # Quarantine/Mark Spam (Level 5)
+ $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_what_group ($rule, $spam5);
+ $ruledb->rule_add_action ($rule, $mod_spam_subject);
+ $ruledb->rule_add_action ($rule, $quarantine);
+
+ ## Block Spam Level 10
+ $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 82, 0, 0);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_what_group ($rule, $spam10);
+ $ruledb->rule_add_action ($rule, $block);
+
+ ## Block Outgoing Spam
+ $rule = PMG::RuleDB::Rule->new ('Block outgoing Spam', 70, 0, 1);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_what_group ($rule, $spam3);
+ $ruledb->rule_add_action ($rule, $notify_admin);
+ $ruledb->rule_add_action ($rule, $notify_sender);
+ $ruledb->rule_add_action ($rule, $block);
+
+ ## Add disclaimer
+ $rule = PMG::RuleDB::Rule->new ('Add Disclaimer', 60, 0, 1);
+ $ruledb->save_rule ($rule);
+ $ruledb->rule_add_action ($rule, $add_discl);
+
+ # Block Multimedia Files
+ $rule = PMG::RuleDB::Rule->new ('Block Multimedia Files', 87, 0, 2);
+ $ruledb->save_rule ($rule);
+
+ $ruledb->rule_add_what_group ($rule, $mm_content);
+ $ruledb->rule_add_action ($rule, $remove);
+
+ #$ruledb->rule_add_from_group ($rule, $anybody);
+ #$ruledb->rule_add_from_group ($rule, $trusted);
+ #$ruledb->rule_add_to_group ($rule, $anybody);
+ #$ruledb->rule_add_what_group ($rule, $ct_filter);
+ #$ruledb->rule_add_action ($rule, $add_discl);
+ #$ruledb->rule_add_action ($rule, $remove);
+ #$ruledb->rule_add_action ($rule, $bcc);
+ #$ruledb->rule_add_action ($rule, $storeq);
+ #$ruledb->rule_add_action ($rule, $accept);
+
+ cond_create_std_actions ($ruledb);
+
+ reload_ruledb();
+}
+
+sub get_remote_time {
+ my ($rdb) = @_;
+
+ my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
+ $sth->execute();
+ my $ctinfo = $sth->fetchrow_hashref();
+ $sth->finish ();
+
+ return $ctinfo ? $ctinfo->{ctime} : 0;
+}
+
+sub init_masterdb {
+ my ($lcid, $database) = @_;
+
+ die "got unexpected cid for new master" if !$lcid;
+
+ my $dbh;
+
+ eval {
+ $dbh = open_ruledb($database);
+
+ $dbh->begin_work;
+
+ print STDERR "update quarantine database\n";
+ $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
+ "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
+
+ print STDERR "update statistic database\n";
+ $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
+ "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
+
+ print STDERR "update greylist database\n";
+ $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
+
+ print STDERR "update localstat database\n";
+ $dbh->do ("UPDATE LocalStat SET CID = $lcid WHERE CID = 0;");
+
+ $dbh->commit;
+ };
+ my $err = $@;
+
+ if ($dbh) {
+ $dbh->rollback if $err;
+ $dbh->disconnect();
+ }
+
+ die $err if $err;
+}
+
+sub purge_statistic_database {
+ my ($dbh, $statlifetime) = @_;
+
+ return if $statlifetime <= 0;
+
+ my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
+ my $end = timelocal(0, 0, 0, $mday, $mon, $year);
+ my $start = $end - $statlifetime*86400;
+
+ # delete statistics older than $start
+
+ my $rows = 0;
+
+ eval {
+ $dbh->begin_work;
+
+ my $sth = $dbh->prepare("DELETE FROM CStatistic WHERE time < $start");
+ $sth->execute;
+ $rows = $sth->rows;
+ $sth->finish;
+
+ if ($rows > 0) {
+ $sth = $dbh->prepare(
+ "DELETE FROM CReceivers WHERE NOT EXISTS " .
+ "(SELECT * FROM CStatistic WHERE CID = CStatistic_CID AND RID = CStatistic_RID)");
+
+ $sth->execute;
+ }
+ $dbh->commit;
+ };
+ if (my $err = $@) {
+ $dbh->rollback;
+ die $err;
+ }
+
+ return $rows;
+}
+
+sub purge_quarantine_database {
+ my ($dbh, $qtype, $lifetime) = @_;
+
+ my $spooldir = $PMG::MailQueue::spooldir;
+
+ my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
+ my $end = timelocal(0, 0, 0, $mday, $mon, $year);
+ my $start = $end - $lifetime*86400;
+
+ my $sth = $dbh->prepare(
+ "SELECT file FROM CMailStore WHERE time < $start AND QType = '$qtype'");
+
+ $sth->execute();
+
+ my $count = 0;
+
+ while (my $ref = $sth->fetchrow_hashref()) {
+ my $filename = "$spooldir/$ref->{file}";
+ $count++ if unlink($filename);
+ }
+
+ $sth->finish();
+
+ $dbh->do(
+ "DELETE FROM CMailStore WHERE time < $start AND QType = '$qtype';" .
+ "DELETE FROM CMSReceivers WHERE NOT EXISTS " .
+ "(SELECT * FROM CMailStore WHERE CID = CMailStore_CID AND RID = CMailStore_RID)");
+
+ return $count;
+}
+
+sub get_quarantine_count {
+ my ($dbh, $qtype) = @_;
+
+ # Note;: We try to estimate used disk space - each mail
+ # is stored in an extra file ...
+
+ my $bs = 4096;
+
+ my $sth = $dbh->prepare(
+ "SELECT count(ID) as count, sum (ceil((Bytes+$bs-1)/$bs)*$bs) / (1024*1024) as mbytes, " .
+ "avg(Bytes) as avgbytes, avg(Spamlevel) as avgspam " .
+ "FROM CMailStore WHERE QType = ?");
+
+ $sth->execute($qtype);
+
+ my $ref = $sth->fetchrow_hashref();
+
+ $sth->finish;
+
+ foreach my $k (qw(count mbytes avgbytes avgspam)) {
+ $ref->{$k} //= 0;
+ }
+
+ return $ref;
+}
+
+sub copy_table {
+ my ($ldb, $rdb, $table) = @_;
+
+ $table = lc($table);
+
+ my $sth = $ldb->column_info(undef, undef, $table, undef);
+ my $attrs = $sth->fetchall_arrayref({});
+
+ my @col_arr;
+ foreach my $ref (@$attrs) {
+ push @col_arr, $ref->{COLUMN_NAME};
+ }
+
+ $sth->finish();
+
+ my $cols = join(', ', @col_arr);
+ $cols || die "unable to fetch column definitions of table '$table' : ERROR";
+
+ $rdb->do("COPY $table ($cols) TO STDOUT");
+
+ my $data = '';
+
+ eval {
+ $ldb->do("COPY $table ($cols) FROM stdin");
+
+ while ($rdb->pg_getcopydata($data) >= 0) {
+ $ldb->pg_putcopydata($data);
+ }
+
+ $ldb->pg_putcopyend();
+ };
+ if (my $err = $@) {
+ $ldb->pg_putcopyend();
+ die $err;
+ }
+}
+
+sub copy_selected_data {
+ my ($dbh, $select_sth, $table, $attrs, $callback) = @_;
+
+ my $count = 0;
+
+ my $insert_sth = $dbh->prepare(
+ "INSERT INTO ${table}(" . join(',', @$attrs) . ') ' .
+ 'VALUES (' . join(',', ('?') x scalar(@$attrs)) . ')');
+
+ while (my $ref = $select_sth->fetchrow_hashref()) {
+ $callback->($ref) if $callback;
+ $count++;
+ $insert_sth->execute(map { $ref->{$_} } @$attrs);
+ }
+
+ return $count;
+}
+
+sub update_master_clusterinfo {
+ my ($clientcid) = @_;
+
+ my $dbh = open_ruledb();
+
+ $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
+
+ my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
+
+ foreach my $table (@mt) {
+ $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
+ "EXTRACT(EPOCH FROM now())");
+ }
+}
+
+sub update_client_clusterinfo {
+ my ($mastercid) = @_;
+
+ my $dbh = open_ruledb();
+
+ $dbh->do ("DELETE FROM StatInfo"); # not needed at node
+
+ $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
+
+ $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
+ "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
+
+ $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
+ "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
+
+ my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
+
+ foreach my $table (@mt) {
+ $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
+ "COALESCE (max (mtime), 0) FROM $table");
+ }
+}
+
+sub create_clusterinfo_default {
+ my ($dbh, $rcid, $name, $ivalue, $svalue) = @_;
+
+ my $sth = $dbh->prepare("SELECT * FROM ClusterInfo WHERE CID = ? AND Name = ?");
+ $sth->execute($rcid, $name);
+ if (!$sth->fetchrow_hashref()) {
+ $dbh->do("INSERT INTO ClusterInfo (CID, Name, IValue, SValue) " .
+ "VALUES (?, ?, ?, ?)", undef,
+ $rcid, $name, $ivalue, $svalue);
+ }
+ $sth->finish();
+}
+
+sub read_int_clusterinfo {
+ my ($dbh, $rcid, $name) = @_;
+
+ my $sth = $dbh->prepare(
+ "SELECT ivalue as value FROM ClusterInfo " .
+ "WHERE cid = ? AND NAME = ?");
+ $sth->execute($rcid, $name);
+ my $cinfo = $sth->fetchrow_hashref();
+ $sth->finish();
+
+ return $cinfo->{value};
+}
+
+sub write_maxint_clusterinfo {
+ my ($dbh, $rcid, $name, $value) = @_;
+
+ $dbh->do("UPDATE ClusterInfo SET ivalue = GREATEST(ivalue, ?) " .
+ "WHERE cid = ? AND name = ?", undef,
+ $value, $rcid, $name);
+}
+
+sub init_nodedb {
+ my ($cinfo) = @_;
+
+ my $ni = $cinfo->{master};
+
+ die "no master defined - unable to sync data from master\n" if !$ni;
+
+ my $master_ip = $ni->{ip};
+ my $master_cid = $ni->{cid};
+ my $master_name = $ni->{name};
+
+ my $fn = "/tmp/masterdb$$.tar";
+ unlink $fn;
+
+ my $dbname = $default_db_name;
+
+ eval {
+ print STDERR "copying master database from '${master_ip}'\n";
+
+ open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
+
+ my $cmd = ['/usr/bin/ssh', '-o', 'BatchMode=yes',
+ '-o', "HostKeyAlias=${master_name}", $master_ip,
+ 'pg_dump', $dbname, '-F', 'c' ];
+
+ PVE::Tools::run_command($cmd, output => '>&' . fileno($fh));
+
+ close($fh);
+
+ my $size = -s $fn;
+
+ print STDERR "copying master database finished (got $size bytes)\n";
+
+ print STDERR "delete local database\n";
+
+ postgres_admin_cmd('dropdb', undef, $dbname , '--if-exists');
+
+ print STDERR "create new local database\n";
+
+ postgres_admin_cmd('createdb', undef, $dbname);
+
+ print STDERR "insert received data into local database\n";
+
+ my $mess;
+ my $parser = sub {
+ my $line = shift;
+
+ if ($line =~ m/restoring data for table \"(.+)\"/) {
+ print STDERR "restoring table $1\n";
+ } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
+ $mess = "creating indexes";
+ print STDERR "$mess\n";
+ }
+ };
+
+ my $opts = {
+ outfunc => $parser,
+ errfunc => $parser,
+ errmsg => "pg_restore failed"
+ };
+
+ postgres_admin_cmd('pg_restore', $opts, '-d', $dbname, '-v', $fn);
+
+ print STDERR "run analyze to speed up database queries\n";
+
+ postgres_admin_cmd('psql', { input => 'analyze;' }, $dbname);
+
+ update_client_clusterinfo($master_cid);
+ };
+
+ my $err = $@;
+
+ unlink $fn;
+
+ die $err if $err;
+}
+
+sub cluster_sync_status {
+ my ($cinfo) = @_;
+
+ my $dbh;
+
+ my $minmtime;
+
+ foreach my $ni (values %{$cinfo->{ids}}) {
+ next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID
+ $minmtime->{$ni->{cid}} = 0;
+ }
+
+ eval {
+ $dbh = open_ruledb();
+
+ my $sth = $dbh->prepare(
+ "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " .
+ "WHERE name = 'lastsync' AND ivalue > 0 " .
+ "GROUP BY cid");
+
+ $sth->execute();
+
+ while (my $info = $sth->fetchrow_hashref()) {
+ foreach my $ni (values %{$cinfo->{ids}}) {
+ next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID
+ if ($ni->{cid} == $info->{cid}) { # node exists
+ $minmtime->{$ni->{cid}} = $info->{minmtime};
+ }
+ }
+ }
+
+ $sth->finish();
+ };
+ my $err = $@;
+
+ $dbh->disconnect() if $dbh;
+
+ syslog('err', $err) if $err;
+
+ return $minmtime;
+}
+
+sub load_mail_data {
+ my ($dbh, $cid, $rid, $ticketid) = @_;
+
+ my $sth = $dbh->prepare(
+ "SELECT * FROM CMailStore, CMSReceivers WHERE " .
+ "CID = ? AND RID = ? AND TicketID = ? AND " .
+ "CID = CMailStore_CID AND RID = CMailStore_RID");
+ $sth->execute($cid, $rid, $ticketid);
+
+ my $res = $sth->fetchrow_hashref();
+
+ $sth->finish();
+
+ die "no such mail (C${cid}R${rid}T${ticketid})\n" if !defined($res);
+
+ return $res;
+}
+
+sub reload_ruledb {
+ my ($ruledb) = @_;
+
+ # Note: we pass $ruledb when modifying SMTP whitelist
+ if (defined($ruledb)) {
+ eval {
+ my $rulecache = PMG::RuleCache->new($ruledb);
+ PMG::Config::rewrite_postfix_whitelist($rulecache);
+ };
+ if (my $err = $@) {
+ warn "problems updating SMTP whitelist - $err";
+ }
+ }
+
+ my $pid_file = '/var/run/pmg-smtp-filter.pid';
+ my $pid = PVE::Tools::file_read_firstline($pid_file);
+
+ return 0 if !$pid;
+
+ return 0 if $pid !~ m/^(\d+)$/;
+ $pid = $1; # untaint
+
+ return kill (10, $pid); # send SIGUSR1
}
1;
projects / pmg-api.git / blobdiff