use PMG::Utils;
use PMG::RuleDB;
use PMG::MailQueue;
+use PMG::Config;
our $default_db_name = "Proxmox_ruledb";
PRIMARY KEY (CID, Name))
__EOD
+my $local_stat_ctablecmd = <<__EOD;
+ CREATE TABLE LocalStat
+ (Time INTEGER NOT NULL,
+ 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,
__EOD
-# mail storage stable
+# mail storage table
# QTypes
# V - Virus quarantine
# S - Spam quarantine
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);
my $cmd = "SELECT tablename FROM pg_tables " .
"WHERE tablename = lower ('$name')";
- my $sth = $dbh->prepare ($cmd);
+ my $sth = $dbh->prepare($cmd);
$sth->execute();
}
}
+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);
+}
+
+my $createdb = sub {
+ my ($dbname) = @_;
+ postgres_admin_cmd(
+ 'createdb',
+ undef,
+ '-E', 'sql_ascii',
+ '-T', 'template0',
+ '--lc-collate=C',
+ '--lc-ctype=C',
+ $dbname,
+ );
+};
+
sub create_ruledb {
my ($dbname) = @_;
# 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);
+ $createdb->($dbname);
my $dbh = open_ruledb($dbname);
$clusterinfo_ctablecmd;
+ $local_stat_ctablecmd;
+
$daily_stat_ctablecmd;
$domain_stat_ctablecmd;
$dbh->do("set enable_seqscan = false");
my $tables = {
- 'DailyStat'=> $daily_stat_ctablecmd,
+ 'LocalStat', $local_stat_ctablecmd,
+ 'DailyStat', $daily_stat_ctablecmd,
'DomainStat', $domain_stat_ctablecmd,
'StatInfo', $statinfo_ctablecmd,
'CMailStore', $cmailstore_ctablecmd,
};
foreach my $table (keys %$tables) {
- cond_create_dbtable($dbh, $table, $tables->{$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");
+ }
+
+ eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); };
+ # ignore errors here
+
+
+ # 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 {
eval { $dbh->do("ANALYZE $table"); };
warn $@ if $@;
}
+
+ reload_ruledb();
}
sub init_ruledb {
}
# Quarantine/Mark Spam (Level 5)
- $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 79, 0, 0);
+ $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, $quarantine);
## Block Spam Level 10
- $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 78, 0, 0);
+ $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, $accept);
cond_create_std_actions ($ruledb);
+
+ reload_ruledb();
}
sub get_remote_time {
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 = $@;
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) = @_;
$dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
- my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'VirusInfo');
+ 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', " .
$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', 'VirusInfo');
+ 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', " .
open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
- postgres_admin_cmd(
- ['/usr/bin/ssh', '-o', 'BatchMode=yes',
- '-o', "HostKeyAlias=${master_name}",
- $master_ip, 'pg_dump'],
- { output => '>&' . fileno($fh) },
- $dbname, '-F', 'c');
+ 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);
print STDERR "create new local database\n";
- postgres_admin_cmd('createdb', undef, $dbname);
+ $createdb->($dbname);
print STDERR "insert received data into local database\n";
}
sub load_mail_data {
- my ($dbh, $cid, $rid) = @_;
+ my ($dbh, $cid, $rid, $ticketid) = @_;
my $sth = $dbh->prepare(
"SELECT * FROM CMailStore, CMSReceivers WHERE " .
- "CID = $cid and RID = $rid AND " .
+ "CID = ? AND RID = ? AND TicketID = ? AND " .
"CID = CMailStore_CID AND RID = CMailStore_RID");
- $sth->execute ();
+ $sth->execute($cid, $rid, $ticketid);
my $res = $sth->fetchrow_hashref();
$sth->finish();
- die "no such mail (C${cid}R${rid})\n" if !defined($res);
+ 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
}
projects / pmg-api.git / blobdiff