use warnings;
use POSIX ":sys_wait_h";
-use POSIX ':signal_h';
+use POSIX qw(:signal_h getuid);
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);
};
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 $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 {
my ($dbname) = @_;
- PVE::Tools::run_command(['dropdb', '-U', 'postgres', $dbname]);
+ postgres_admin_cmd('dropdb', undef, $dbname);
}
sub database_list {
$database_list->{$name} = { owner => $owner };
};
- my $cmd = ['psql', '-U', 'postgres', '--list', '--quiet', '--tuples-only'];
-
- PVE::Tools::run_command($cmd, outfunc => $parser);
+ postgres_admin_cmd('psql', { outfunc => $parser }, '--list', '--quiet', '--tuples-only');
return $database_list;
}
-my $dbfunction_maxint = <<__EOD;
- CREATE OR REPLACE FUNCTION maxint (INTEGER, INTEGER) RETURNS INTEGER AS
- 'BEGIN IF \$1 > \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql;
-__EOD
-
-my $dbfunction_minint = <<__EOD;
- CREATE OR REPLACE FUNCTION minint (INTEGER, INTEGER) RETURNS INTEGER AS
- 'BEGIN IF \$1 < \$2 THEN RETURN \$1; ELSE RETURN \$2; END IF; END;' LANGUAGE plpgsql;
-__EOD
-
-# merge function to avoid update/insert race condition
-# see: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
-my $dbfunction_merge_greylist = <<__EOD;
- CREATE OR REPLACE FUNCTION merge_greylist (in_ipnet VARCHAR, in_host INTEGER, in_sender VARCHAR,
- in_receiver VARCHAR, in_instance VARCHAR,
- in_rctime INTEGER, in_extime INTEGER, in_delay INTEGER,
- in_blocked INTEGER, in_passed INTEGER, in_mtime INTEGER,
- in_cid INTEGER) RETURNS INTEGER AS
- 'BEGIN
- LOOP
- UPDATE CGreylist SET Host = CASE WHEN MTime >= in_mtime THEN Host ELSE in_host END,
- CID = maxint (CID, in_cid), RCTime = minint (rctime, in_rctime),
- ExTime = maxint (extime, in_extime),
- Delay = maxint (delay, in_delay),
- Blocked = maxint (blocked, in_blocked),
- Passed = maxint (passed, in_passed)
- WHERE IPNet = in_ipnet AND Sender = in_sender AND Receiver = in_receiver;
-
- IF found THEN
- RETURN 0;
- END IF;
-
- BEGIN
- INSERT INTO CGREYLIST (IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, MTime, CID)
- VALUES (in_ipnet, in_host, in_sender, in_receiver, in_instance, in_rctime, in_extime,
- in_delay, in_blocked, in_passed, in_mtime, in_cid);
- RETURN 1;
- EXCEPTION WHEN unique_violation THEN
- -- do nothing - continue loop
- END;
- END LOOP;
- END;' LANGUAGE plpgsql;
-__EOD
-
my $cgreylist_ctablecmd = <<__EOD;
CREATE TABLE CGreylist
(IPNet VARCHAR(16) NOT NULL,
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
my $cmd = "SELECT tablename FROM pg_tables " .
"WHERE tablename = lower ('$name')";
- my $sth = $dbh->prepare ($cmd);
+ my $sth = $dbh->prepare($cmd);
$sth->execute();
};
if (my $err = $@) {
$dbh->rollback;
- croak $err;
+ 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);
+}
+
+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) = @_;
- $dbname = "Proxmox_ruledb" if !$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)
- my $cmd = [ 'createdb', '-U', 'postgres', '-E', 'sql_ascii',
- '-T', 'template0', '--lc-collate=C', '--lc-ctype=C', $dbname ];
- PVE::Tools::run_command($cmd);
+ $createdb->($dbname);
my $dbh = open_ruledb($dbname);
- #$dbh->do ($dbloaddrivers_sql);
- #$dbh->do ($dbfunction_update_modtime);
-
- $dbh->do ($dbfunction_minint);
-
- $dbh->do ($dbfunction_maxint);
-
- $dbh->do ($dbfunction_merge_greylist);
+ # 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
$clusterinfo_ctablecmd;
+ $local_stat_ctablecmd;
+
$daily_stat_ctablecmd;
$domain_stat_ctablecmd;
my $obj = PMG::RuleDB::Quarantine->new ();
my $txt = decode_entities(PMG::RuleDB::Quarantine->otype_text);
my $quarantine = $ruledb->create_group_with_obj
- ($obj, $txt, PMG::RuleDB::Quarantine->oinfo);
+ ($obj, $txt, 'Move to quarantine.');
}
};
}
}
-sub upgrade_mailstore_db {
- my ($dbh) = @_;
-
- eval {
- $dbh->begin_work;
-
- my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('MailStore')";
-
- my $sth = $dbh->prepare($cmd);
- $sth->execute();
- my $ref = $sth->fetchrow_hashref();
- $sth->finish();
-
- if ($ref) { # table exists
-
- $cmd = "INSERT INTO CMailStore " .
- "(CID, RID, ID, Time, QType, Bytes, Spamlevel, Info, Header, Sender, File) " .
- "SELECT 0, ID, ID, Time, QType, Bytes, Spamlevel, Info, Header, Sender, File FROM MailStore";
-
- $dbh->do($cmd);
-
- $cmd = "INSERT INTO CMSReceivers " .
- "(CMailStore_CID, CMailStore_RID, PMail, Receiver, TicketID, Status, MTime) " .
- "SELECT 0, MailStore_ID, PMail, Receiver, TicketID, Status, 0 FROM MSReceivers";
-
- $dbh->do($cmd);
-
- $dbh->do("SELECT setval ('cmailstore_id_seq', nextval ('mailstore_id_seq'))");
-
- $dbh->do("DROP TABLE MailStore");
- $dbh->do("DROP TABLE MSReceivers");
- }
-
- $dbh->commit;
- };
- if (my $err = $@) {
- $dbh->rollback;
- die $err;
- }
-}
-
-sub upgrade_dailystat_db {
- my ($dbh) = @_;
-
- eval { # make sure we have MTime
- $dbh->do("ALTER TABLE DailyStat ADD COLUMN MTime INTEGER;" .
- "UPDATE DailyStat SET MTime = EXTRACT (EPOCH FROM now());");
- };
-
- eval { # make sure we have correct constraints for MTime
- $dbh->do ("ALTER TABLE DailyStat ALTER COLUMN MTime SET NOT NULL;");
- };
-
- eval { # make sure we have RBLCount
- $dbh->do ("ALTER TABLE DailyStat ADD COLUMN RBLCount INTEGER;" .
- "UPDATE DailyStat SET RBLCount = 0;");
- };
-
- eval { # make sure we have correct constraints for RBLCount
- $dbh->do ("ALTER TABLE DailyStat ALTER COLUMN RBLCount SET DEFAULT 0;" .
- "ALTER TABLE DailyStat ALTER COLUMN RBLCount SET NOT NULL;");
- };
-
- eval {
- $dbh->begin_work;
-
- my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('DailyStat_MTime_Index')";
-
- my $sth = $dbh->prepare($cmd);
- $sth->execute();
- my $ref = $sth->fetchrow_hashref();
- $sth->finish();
-
- if (!$ref) { # index does not exist
- $dbh->do ("CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime)");
- }
-
- $dbh->commit;
- };
- if (my $err = $@) {
- $dbh->rollback;
- die $err;
- }
-}
-
-sub upgrade_domainstat_db {
- my ($dbh) = @_;
-
- eval { # make sure we have MTime
- $dbh->do("ALTER TABLE DomainStat ADD COLUMN MTime INTEGER;" .
- "UPDATE DomainStat SET MTime = EXTRACT (EPOCH FROM now());" .
- "ALTER TABLE DomainStat ALTER COLUMN MTime SET NOT NULL;");
- };
-
- eval {
- $dbh->begin_work;
-
- my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('DomainStat_MTime_Index')";
-
- my $sth = $dbh->prepare($cmd);
- $sth->execute();
- my $ref = $sth->fetchrow_hashref();
- $sth->finish();
-
- if (!$ref) { # index does not exist
- $dbh->do ("CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime)");
- }
-
- $dbh->commit;
- };
- if (my $err = $@) {
- $dbh->rollback;
- die $@;
- }
-}
-
-sub upgrade_statistic_db {
- my ($dbh) = @_;
-
- eval {
- $dbh->begin_work;
-
- my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('Statistic')";
-
- my $sth = $dbh->prepare($cmd);
- $sth->execute();
- my $ref = $sth->fetchrow_hashref();
- $sth->finish();
-
- if ($ref) { # old table exists
-
- my $timezone = tz_local_offset();;
-
- $dbh->do("INSERT INTO VirusInfo (Time, Name, Count, MTime) " .
- "SELECT ((time + $timezone) / 86400) * 86400 as day, virusinfo, " .
- "count (virusinfo), max (Time) FROM Statistic " .
- "WHERE virusinfo IS NOT NULL GROUP BY day, virusinfo");
-
- my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime (time());
- my $end = timelocal(0, 0, 0, $mday, $mon, $year);
- my $start = $end - 3600*24*7; # / days
-
- $cmd = "INSERT INTO CStatistic " .
- "(CID, RID, ID, Time, Bytes, Direction, Spamlevel, VirusInfo, PTime, Sender) " .
- "SELECT 0, ID, ID, Time, Bytes, Direction, Spamlevel, VirusInfo, PTime, Sender FROM Statistic " .
- "WHERE time >= $start";
-
- $dbh->do($cmd);
-
- $dbh->do("SELECT setval ('cstatistic_id_seq', nextval ('statistic_id_seq'))");
-
- $dbh->do("INSERT INTO StatInfo (name, ivalue) VALUES ('virusinfo_index', " .
- "nextval ('statistic_id_seq'))");
-
- $cmd = "INSERT INTO CReceivers (CStatistic_CID, CStatistic_RID, Receiver, Blocked) " .
- "SELECT 0, Mail_ID, Receiver, Blocked FROM Receivers " .
- "WHERE EXISTS (SELECT * FROM CStatistic WHERE CID = 0 AND RID = Mail_ID)";
-
- $dbh->do($cmd);
-
- $dbh->do("DROP TABLE Statistic");
- $dbh->do("DROP TABLE Receivers");
- }
-
- $dbh->commit;
- };
- if (my $err = $@) {
- $dbh->rollback;
- die $err;
- }
-}
-
-sub upgrade_greylist_db {
- my ($dbh) = @_;
-
- eval {
- $dbh->begin_work;
-
- my $cmd = "SELECT tablename FROM pg_tables WHERE tablename = lower ('Greylist')";
-
- my $sth = $dbh->prepare($cmd);
- $sth->execute();
- my $ref = $sth->fetchrow_hashref();
- $sth->finish();
-
- if ($ref) { # table exists
-
- $cmd = "INSERT INTO CGreylist " .
- "(IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, MTime, CID) " .
- "SELECT IPNet, Host, Sender, Receiver, Instance, RCTime, ExTime, Delay, Blocked, Passed, RCTime, 0 FROM Greylist";
-
- $dbh->do($cmd);
-
- $dbh->do("DROP TABLE Greylist");
- }
-
- $dbh->commit;
- };
- if (my $err = $@) {
- $dbh->rollback;
- die $err;
- }
-}
-
-sub upgrade_userprefs_db {
- my ($dbh) = @_;
-
- eval {
- $dbh->do("ALTER TABLE UserPrefs ADD COLUMN MTime INTEGER;" .
- "UPDATE UserPrefs SET MTime = EXTRACT (EPOCH FROM now());" .
- "ALTER TABLE UserPrefs ALTER COLUMN MTime SET NOT NULL;");
- };
-
-
- eval {
- $dbh->begin_work;
-
- my $cmd = "SELECT indexname FROM pg_indexes WHERE indexname = lower ('UserPrefs_MTime_Index')";
-
- my $sth = $dbh->prepare($cmd);
- $sth->execute();
- my $ref = $sth->fetchrow_hashref();
- $sth->finish();
-
- if (!$ref) { # index does not exist
- $dbh->do("CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime)");
- }
-
- $dbh->commit;
- };
- if ($@) {
- $dbh->rollback;
- die $@;
- }
-}
-
sub upgradedb {
my ($ruledb) = @_;
my $dbh = $ruledb->{dbh};
- $dbh->do($dbfunction_minint);
-
- $dbh->do($dbfunction_maxint);
-
- $dbh->do($dbfunction_merge_greylist);
-
# make sure we do not use slow sequential scans when upgraing
# database (before analyze can gather statistics)
$dbh->do("set enable_seqscan = false");
- cond_create_dbtable($dbh, 'DailyStat', $daily_stat_ctablecmd);
- cond_create_dbtable($dbh, 'DomainStat', $domain_stat_ctablecmd);
- cond_create_dbtable($dbh, 'StatInfo', $statinfo_ctablecmd);
- cond_create_dbtable($dbh, 'CMailStore', $cmailstore_ctablecmd);
- cond_create_dbtable($dbh, 'UserPrefs', $userprefs_ctablecmd);
- cond_create_dbtable($dbh, 'CGreylist', $cgreylist_ctablecmd);
- cond_create_dbtable($dbh, 'CStatistic', $cstatistic_ctablecmd);
- cond_create_dbtable($dbh, 'ClusterInfo', $clusterinfo_ctablecmd);
- cond_create_dbtable($dbh, 'VirusInfo', $virusinfo_stat_ctablecmd);
-
- cond_create_std_actions($ruledb);
+ 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,
+ };
- upgrade_mailstore_db($dbh);
+ foreach my $table (keys %$tables) {
+ cond_create_dbtable($dbh, $table, $tables->{$table});
+ }
- upgrade_statistic_db($dbh);
+ cond_create_std_actions($ruledb);
- upgrade_userprefs_db($dbh);
+ # 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");
+ }
- upgrade_greylist_db($dbh);
+ eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); };
+ # ignore errors here
- upgrade_dailystat_db($dbh);
- upgrade_domainstat_db($dbh);
+ # 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 {
"AND value = 'content-type:application/x-java-vm';");
};
- eval {
- $dbh->do ("ANALYZE");
- };
+ foreach my $table (keys %$tables) {
+ eval { $dbh->do("ANALYZE $table"); };
+ warn $@ if $@;
+ }
+
+ reload_ruledb();
}
sub init_ruledb {
$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)');
}
# 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 {
+ 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";
+
+ $createdb->($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