X-Git-Url: https://git.proxmox.com/?a=blobdiff_plain;f=PMG%2FDBTools.pm;h=464b0136644470fde12394a532a9efd30a417904;hb=c2e3529ab6319171fcaa25be6b2935cbf3bb7e86;hp=e24623c9ad7823240a3c58cc0d3f86ad2f1a6ac5;hpb=157a946ba501a66773e5905b7d9de36cfa76b6b9;p=pmg-api.git diff --git a/PMG/DBTools.pm b/PMG/DBTools.pm index e24623c..464b013 100644 --- a/PMG/DBTools.pm +++ b/PMG/DBTools.pm @@ -4,7 +4,7 @@ use strict; use warnings; use POSIX ":sys_wait_h"; -use POSIX ':signal_h'; +use POSIX qw(:signal_h getuid); use DBI; use Time::Local; @@ -14,6 +14,7 @@ use PVE::Tools; use PMG::Utils; use PMG::RuleDB; use PMG::MailQueue; +use PMG::Config; our $default_db_name = "Proxmox_ruledb"; @@ -79,12 +80,17 @@ 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"; + my $save_uid = POSIX::getuid(); + my $pg_uid = getpwnam('postgres') || die "getpwnam postgres failed\n"; + + PVE::Tools::setresuid(-1, $pg_uid, -1) || + die "setresuid postgres ($pg_uid) failed - $!\n"; PVE::Tools::run_command([@$cmd, '-U', 'postgres', @params], %$options); + + PVE::Tools::setresuid(-1, $save_uid, -1) || + die "setresuid back failed - $!\n"; } sub delete_ruledb { @@ -143,6 +149,19 @@ my $clusterinfo_ctablecmd = <<__EOD; 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, @@ -208,7 +227,7 @@ my $virusinfo_stat_ctablecmd = <<__EOD; __EOD -# mail storage stable +# mail storage table # QTypes # V - Virus quarantine # S - Spam quarantine @@ -239,6 +258,7 @@ my $cmailstore_ctablecmd = <<__EOD; 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); @@ -297,7 +317,7 @@ sub cond_create_dbtable { my $cmd = "SELECT tablename FROM pg_tables " . "WHERE tablename = lower ('$name')"; - my $sth = $dbh->prepare ($cmd); + my $sth = $dbh->prepare($cmd); $sth->execute(); @@ -315,6 +335,30 @@ sub cond_create_dbtable { } } +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) = @_; @@ -329,8 +373,7 @@ sub create_ruledb { # 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); @@ -380,6 +423,8 @@ sub create_ruledb { $clusterinfo_ctablecmd; + $local_stat_ctablecmd; + $daily_stat_ctablecmd; $domain_stat_ctablecmd; @@ -439,7 +484,8 @@ sub upgradedb { $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, @@ -451,12 +497,39 @@ sub upgradedb { }; 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 { @@ -470,6 +543,8 @@ sub upgradedb { eval { $dbh->do("ANALYZE $table"); }; warn $@ if $@; } + + reload_ruledb(); } sub init_ruledb { @@ -728,7 +803,7 @@ 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); @@ -736,7 +811,7 @@ sub init_ruledb { $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); @@ -774,6 +849,8 @@ sub init_ruledb { #$ruledb->rule_add_action ($rule, $accept); cond_create_std_actions ($ruledb); + + reload_ruledb(); } sub get_remote_time { @@ -810,6 +887,9 @@ sub init_masterdb { 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 = $@; @@ -891,6 +971,32 @@ sub purge_quarantine_database { 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) = @_; @@ -953,7 +1059,7 @@ sub update_master_clusterinfo { $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', " . @@ -976,7 +1082,7 @@ sub update_client_clusterinfo { $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', " . @@ -1039,12 +1145,11 @@ sub init_nodedb { 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); @@ -1058,7 +1163,7 @@ sub init_nodedb { print STDERR "create new local database\n"; - postgres_admin_cmd('createdb', undef, $dbname); + $createdb->($dbname); print STDERR "insert received data into local database\n"; @@ -1139,92 +1244,46 @@ sub cluster_sync_status { } 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 add_to_blackwhite { - my ($dbh, $username, $listname, $addrs, $delete) = @_; - - my $name = $listname eq 'BL' ? 'BL' : 'WL'; - my $oname = $listname eq 'BL' ? 'WL' : 'BL'; - my $qu = $dbh->quote ($username); - - my $sth = $dbh->prepare( - "SELECT * FROM UserPrefs WHERE pmail = $qu AND (Name = 'BL' OR Name = 'WL')"); - $sth->execute(); - - my $list = { 'WL' => {}, 'BL' => {} }; +sub reload_ruledb { + my ($ruledb) = @_; - while (my $ref = $sth->fetchrow_hashref()) { - my $data = $ref->{data}; - $data =~ s/[,;]/ /g; - my @alist = split('\s+', $data); - - my $tmp = {}; - foreach my $a (@alist) { - if ($a =~ m/^[[:ascii:]]+$/) { - $tmp->{$a} = 1; - } + # 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"; } - - $list->{$ref->{name}} = $tmp; } - $sth->finish; - - if ($addrs) { - - foreach my $v (@$addrs) { - die "email address '$v' is too long (> 512 characters)\n" - if length($v) > 512; + my $pid_file = '/var/run/pmg-smtp-filter.pid'; + my $pid = PVE::Tools::file_read_firstline($pid_file); - if ($delete) { - delete($list->{$name}->{$v}); - } else { - if ($v =~ m/[[:^ascii:]]/) { - die "email address '$v' contains invalid characters\n"; - } - $list->{$name}->{$v} = 1; - delete ($list->{$oname}->{$v}); - } - } - - my $wlist = $dbh->quote(join (',', keys %{$list->{WL}}) || ''); - my $blist = $dbh->quote(join (',', keys %{$list->{BL}}) || ''); - - if (!$delete) { - my $maxlen = 200000; - die "whitelist size exceeds limit (> $maxlen bytes)\n" - if length($wlist) > $maxlen; - die "blacklist size exceeds limit (> $maxlen bytes)\n" - if length($blist) > $maxlen; - } - - $dbh->do( - "DELETE FROM UserPrefs WHERE pmail = $qu AND (Name = 'WL' OR Name = 'BL');" . - "INSERT INTO UserPrefs (PMail, Name, Data, MTime) " . - "VALUES ($qu, 'BL', $blist, EXTRACT (EPOCH FROM now()));" . - "INSERT INTO UserPrefs (PMail, Name, Data, MTime) " . - "VALUES ($qu, 'WL', $wlist, EXTRACT (EPOCH FROM now()));"); - } + return 0 if !$pid; - my $values = [ keys %{$list->{$name}} ]; + return 0 if $pid !~ m/^(\d+)$/; + $pid = $1; # untaint - return $values; + return kill (10, $pid); # send SIGUSR1 } 1;