]> git.proxmox.com Git - pmg-api.git/blame - src/PMG/DBTools.pm
move postgres_admin_command into PMG::Utils
[pmg-api.git] / src / PMG / DBTools.pm
CommitLineData
a355f100
DM
1package PMG::DBTools;
2
3use strict;
4use warnings;
5
6use POSIX ":sys_wait_h";
a513899e 7use POSIX qw(:signal_h getuid);
a355f100 8use DBI;
8fb6f404 9use Time::Local;
a355f100 10
f3464b71 11use PVE::SafeSyslog;
a355f100
DM
12use PVE::Tools;
13
f3464b71 14use PMG::Utils;
0a580593 15use PMG::RuleDB;
1af3c560 16use PMG::MailQueue;
f9967a49 17use PMG::Config;
6529020a 18use PMG::Utils qw(postgres_admin_cmd);
0a580593 19
987ee804
DM
20our $default_db_name = "Proxmox_ruledb";
21
2e049252
DM
22our $cgreylist_merge_sql =
23 'INSERT INTO CGREYLIST (IPNet,Host,Sender,Receiver,Instance,RCTime,' .
24 'ExTime,Delay,Blocked,Passed,MTime,CID) ' .
25 'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ' .
26 'ON CONFLICT (IPNet,Sender,Receiver) DO UPDATE SET ' .
0c6e9b56
DM
27 'Host = CASE WHEN CGREYLIST.MTime >= excluded.MTime THEN CGREYLIST.Host ELSE excluded.Host END,' .
28 'CID = GREATEST(CGREYLIST.CID, excluded.CID), RCTime = LEAST(CGREYLIST.RCTime, excluded.RCTime),' .
29 'ExTime = GREATEST(CGREYLIST.ExTime, excluded.ExTime),' .
30 'Delay = GREATEST(CGREYLIST.Delay, excluded.Delay),' .
31 'Blocked = GREATEST(CGREYLIST.Blocked, excluded.Blocked),' .
32 'Passed = GREATEST(CGREYLIST.Passed, excluded.Passed)';
2e049252 33
a355f100
DM
34sub open_ruledb {
35 my ($database, $host, $port) = @_;
36
5475e159 37 $port //= 5432;
a355f100 38
5475e159 39 $database //= $default_db_name;
a355f100
DM
40
41 if ($host) {
42
83e9f427 43 # Note: pmgtunnel uses UDP sockets inside directory '/run/pmgtunnel',
155b0da9 44 # and the cluster 'cid' as port number. You can connect to the
83e9f427 45 # socket with: host => /run/pmgtunnel, port => $cid
155b0da9 46
a355f100
DM
47 my $dsn = "dbi:Pg:dbname=$database;host=$host;port=$port;";
48
49 my $timeout = 5;
50 # only low level alarm interface works for DBI->connect
51 my $mask = POSIX::SigSet->new(SIGALRM);
52 my $action = POSIX::SigAction->new(sub { die "connect timeout\n" }, $mask);
53 my $oldaction = POSIX::SigAction->new();
54 sigaction(SIGALRM, $action, $oldaction);
55
56 my $rdb;
57
58 eval {
59 alarm($timeout);
5475e159 60 $rdb = DBI->connect($dsn, 'root', undef,
a355f100
DM
61 { PrintError => 0, RaiseError => 1 });
62 alarm(0);
63 };
64 alarm(0);
65 sigaction(SIGALRM, $oldaction); # restore original handler
f1bf78ff 66
a355f100
DM
67 die $@ if $@;
68
69 return $rdb;
70 } else {
155b0da9 71 my $dsn = "DBI:Pg:dbname=$database;host=/var/run/postgresql;port=$port";
a355f100 72
5475e159 73 my $dbh = DBI->connect($dsn, $> == 0 ? 'root' : 'www-data', undef,
a355f100
DM
74 { PrintError => 0, RaiseError => 1 });
75
76 return $dbh;
77 }
78}
79
80sub delete_ruledb {
81 my ($dbname) = @_;
82
97cab76a 83 postgres_admin_cmd('dropdb', undef, $dbname);
a355f100
DM
84}
85
f1bf78ff
DM
86sub database_list {
87
88 my $database_list = {};
89
90 my $parser = sub {
91 my $line = shift;
92
93 my ($name, $owner) = map { PVE::Tools::trim($_) } split(/\|/, $line);
94 return if !$name || !$owner;
95
96 $database_list->{$name} = { owner => $owner };
97 };
98
97cab76a 99 postgres_admin_cmd('psql', { outfunc => $parser }, '--list', '--quiet', '--tuples-only');
f1bf78ff
DM
100
101 return $database_list;
102}
103
f1bf78ff
DM
104my $cgreylist_ctablecmd = <<__EOD;
105 CREATE TABLE CGreylist
106 (IPNet VARCHAR(16) NOT NULL,
107 Host INTEGER NOT NULL,
108 Sender VARCHAR(255) NOT NULL,
109 Receiver VARCHAR(255) NOT NULL,
110 Instance VARCHAR(255),
111 RCTime INTEGER NOT NULL,
112 ExTime INTEGER NOT NULL,
113 Delay INTEGER NOT NULL DEFAULT 0,
114 Blocked INTEGER NOT NULL,
115 Passed INTEGER NOT NULL,
116 CID INTEGER NOT NULL,
117 MTime INTEGER NOT NULL,
118 PRIMARY KEY (IPNet, Sender, Receiver));
119
120 CREATE INDEX CGreylist_Instance_Sender_Index ON CGreylist (Instance, Sender);
121
122 CREATE INDEX CGreylist_ExTime_Index ON CGreylist (ExTime);
123
124 CREATE INDEX CGreylist_MTime_Index ON CGreylist (MTime);
125__EOD
126
127my $clusterinfo_ctablecmd = <<__EOD;
128 CREATE TABLE ClusterInfo
129 (CID INTEGER NOT NULL,
130 Name VARCHAR NOT NULL,
131 IValue INTEGER,
132 SValue VARCHAR,
133 PRIMARY KEY (CID, Name))
134__EOD
135
5e1408fd
DM
136my $local_stat_ctablecmd = <<__EOD;
137 CREATE TABLE LocalStat
ac28e80e 138 (Time INTEGER NOT NULL,
5e1408fd 139 RBLCount INTEGER DEFAULT 0 NOT NULL,
70aafbae 140 PregreetCount INTEGER DEFAULT 0 NOT NULL,
5e1408fd
DM
141 CID INTEGER NOT NULL,
142 MTime INTEGER NOT NULL,
143 PRIMARY KEY (Time, CID));
144
145 CREATE INDEX LocalStat_MTime_Index ON LocalStat (MTime);
146__EOD
147
148
f1bf78ff
DM
149my $daily_stat_ctablecmd = <<__EOD;
150 CREATE TABLE DailyStat
151 (Time INTEGER NOT NULL UNIQUE,
152 CountIn INTEGER NOT NULL,
153 CountOut INTEGER NOT NULL,
154 BytesIn REAL NOT NULL,
155 BytesOut REAL NOT NULL,
156 VirusIn INTEGER NOT NULL,
157 VirusOut INTEGER NOT NULL,
158 SpamIn INTEGER NOT NULL,
159 SpamOut INTEGER NOT NULL,
160 BouncesIn INTEGER NOT NULL,
161 BouncesOut INTEGER NOT NULL,
162 GreylistCount INTEGER NOT NULL,
163 SPFCount INTEGER NOT NULL,
164 PTimeSum REAL NOT NULL,
165 MTime INTEGER NOT NULL,
166 RBLCount INTEGER DEFAULT 0 NOT NULL,
167 PRIMARY KEY (Time));
168
169 CREATE INDEX DailyStat_MTime_Index ON DailyStat (MTime);
170
171__EOD
172
173my $domain_stat_ctablecmd = <<__EOD;
174 CREATE TABLE DomainStat
175 (Time INTEGER NOT NULL,
176 Domain VARCHAR(255) NOT NULL,
177 CountIn INTEGER NOT NULL,
178 CountOut INTEGER NOT NULL,
179 BytesIn REAL NOT NULL,
180 BytesOut REAL NOT NULL,
181 VirusIn INTEGER NOT NULL,
182 VirusOut INTEGER NOT NULL,
183 SpamIn INTEGER NOT NULL,
184 SpamOut INTEGER NOT NULL,
185 BouncesIn INTEGER NOT NULL,
186 BouncesOut INTEGER NOT NULL,
187 PTimeSum REAL NOT NULL,
188 MTime INTEGER NOT NULL,
189 PRIMARY KEY (Time, Domain));
190
191 CREATE INDEX DomainStat_MTime_Index ON DomainStat (MTime);
192__EOD
193
194my $statinfo_ctablecmd = <<__EOD;
195 CREATE TABLE StatInfo
196 (Name VARCHAR(255) NOT NULL UNIQUE,
197 IValue INTEGER,
198 SValue VARCHAR(255),
199 PRIMARY KEY (Name))
200__EOD
201
202my $virusinfo_stat_ctablecmd = <<__EOD;
203 CREATE TABLE VirusInfo
204 (Time INTEGER NOT NULL,
205 Name VARCHAR NOT NULL,
206 Count INTEGER NOT NULL,
207 MTime INTEGER NOT NULL,
208 PRIMARY KEY (Time, Name));
209
210 CREATE INDEX VirusInfo_MTime_Index ON VirusInfo (MTime);
211
212__EOD
213
ef5c77eb 214# mail storage table
f1bf78ff
DM
215# QTypes
216# V - Virus quarantine
217# S - Spam quarantine
218# D - Delayed Mails - not implemented
219# A - Held for Audit - not implemented
220# Status
221# N - new
222# D - deleted
223
224my $cmailstore_ctablecmd = <<__EOD;
225 CREATE TABLE CMailStore
226 (CID INTEGER DEFAULT 0 NOT NULL,
227 RID INTEGER NOT NULL,
228 ID SERIAL UNIQUE,
229 Time INTEGER NOT NULL,
230 QType "char" NOT NULL,
231 Bytes INTEGER NOT NULL,
232 Spamlevel INTEGER NOT NULL,
233 Info VARCHAR NULL,
234 Sender VARCHAR(255) NOT NULL,
235 Header VARCHAR NOT NULL,
236 File VARCHAR(255) NOT NULL,
237 PRIMARY KEY (CID, RID));
238 CREATE INDEX CMailStore_Time_Index ON CMailStore (Time);
239
240 CREATE TABLE CMSReceivers
241 (CMailStore_CID INTEGER NOT NULL,
242 CMailStore_RID INTEGER NOT NULL,
243 PMail VARCHAR(255) NOT NULL,
244 Receiver VARCHAR(255),
f2eb5f90 245 TicketID INTEGER NOT NULL,
f1bf78ff
DM
246 Status "char" NOT NULL,
247 MTime INTEGER NOT NULL);
248
249 CREATE INDEX CMailStore_ID_Index ON CMSReceivers (CMailStore_CID, CMailStore_RID);
250
251 CREATE INDEX CMSReceivers_MTime_Index ON CMSReceivers (MTime);
252
253__EOD
254
255my $cstatistic_ctablecmd = <<__EOD;
256 CREATE TABLE CStatistic
257 (CID INTEGER DEFAULT 0 NOT NULL,
258 RID INTEGER NOT NULL,
259 ID SERIAL UNIQUE,
260 Time INTEGER NOT NULL,
261 Bytes INTEGER NOT NULL,
262 Direction Boolean NOT NULL,
263 Spamlevel INTEGER NOT NULL,
264 VirusInfo VARCHAR(255) NULL,
265 PTime INTEGER NOT NULL,
266 Sender VARCHAR(255) NOT NULL,
267 PRIMARY KEY (CID, RID));
268
269 CREATE INDEX CStatistic_Time_Index ON CStatistic (Time);
270
271 CREATE TABLE CReceivers
272 (CStatistic_CID INTEGER NOT NULL,
273 CStatistic_RID INTEGER NOT NULL,
274 Receiver VARCHAR(255) NOT NULL,
275 Blocked Boolean NOT NULL);
276
277 CREATE INDEX CStatistic_ID_Index ON CReceivers (CStatistic_CID, CStatistic_RID);
278__EOD
279
280# user preferences (black an whitelists, ...)
281# Name: perference name ('BL' -> blacklist, 'WL' -> whitelist)
282# Data: arbitrary data
283my $userprefs_ctablecmd = <<__EOD;
284 CREATE TABLE UserPrefs
285 (PMail VARCHAR,
286 Name VARCHAR(255),
287 Data VARCHAR,
288 MTime INTEGER NOT NULL,
289 PRIMARY KEY (PMail, Name));
290
291 CREATE INDEX UserPrefs_MTime_Index ON UserPrefs (MTime);
292
293__EOD
758c7b6b 294
0a580593
DM
295sub cond_create_dbtable {
296 my ($dbh, $name, $ctablecmd) = @_;
297
298 eval {
299 $dbh->begin_work;
300
301 my $cmd = "SELECT tablename FROM pg_tables " .
302 "WHERE tablename = lower ('$name')";
303
5e1408fd 304 my $sth = $dbh->prepare($cmd);
758c7b6b 305
0a580593
DM
306 $sth->execute();
307
308 if (!(my $ref = $sth->fetchrow_hashref())) {
309 $dbh->do ($ctablecmd);
310 }
758c7b6b 311
0a580593
DM
312 $sth->finish();
313
314 $dbh->commit;
315 };
316 if (my $err = $@) {
317 $dbh->rollback;
9ef3f143 318 die $err;
0a580593
DM
319 }
320}
f1bf78ff 321
f2eb5f90
DM
322sub database_column_exists {
323 my ($dbh, $table, $column) = @_;
324
325 my $sth = $dbh->prepare(
326 "SELECT column_name FROM information_schema.columns " .
327 "WHERE table_name = ? and column_name = ?");
328 $sth->execute(lc($table), lc($column));
329 my $res = $sth->fetchrow_hashref();
330 return defined($res);
331}
332
2cc31eaa
DC
333my $createdb = sub {
334 my ($dbname) = @_;
335 postgres_admin_cmd(
336 'createdb',
337 undef,
338 '-E', 'sql_ascii',
339 '-T', 'template0',
340 '--lc-collate=C',
341 '--lc-ctype=C',
342 $dbname,
343 );
344};
345
f1bf78ff
DM
346sub create_ruledb {
347 my ($dbname) = @_;
348
987ee804 349 $dbname = $default_db_name if !$dbname;
f1bf78ff 350
97cab76a
DM
351 my $silent_opts = { outfunc => sub {}, errfunc => sub {} };
352 # make sure we have user 'root'
353 eval { postgres_admin_cmd('createuser', $silent_opts, '-D', 'root'); };
5475e159
DM
354 # also create 'www-data' (and give it read-only access below)
355 eval { postgres_admin_cmd('createuser', $silent_opts, '-I', '-D', 'www-data'); };
97cab76a 356
f1bf78ff
DM
357 # use sql_ascii to avoid any character set conversions, and be compatible with
358 # older postgres versions (update from 8.1 must be possible)
f1bf78ff 359
2cc31eaa 360 $createdb->($dbname);
f1bf78ff
DM
361
362 my $dbh = open_ruledb($dbname);
363
5475e159
DM
364 # make sure 'www-data' can read all tables
365 $dbh->do("ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO \"www-data\"");
366
f1bf78ff
DM
367 $dbh->do (
368<<EOD
369 CREATE TABLE Attribut
370 (Object_ID INTEGER NOT NULL,
371 Name VARCHAR(20) NOT NULL,
372 Value BYTEA NULL,
373 PRIMARY KEY (Object_ID, Name));
374
375 CREATE INDEX Attribut_Object_ID_Index ON Attribut(Object_ID);
376
377 CREATE TABLE Object
378 (ID SERIAL UNIQUE,
379 ObjectType INTEGER NOT NULL,
380 Objectgroup_ID INTEGER NOT NULL,
381 Value BYTEA NULL,
382 PRIMARY KEY (ID));
383
384 CREATE TABLE Objectgroup
385 (ID SERIAL UNIQUE,
386 Name VARCHAR(255) NOT NULL,
387 Info VARCHAR(255) NULL,
388 Class VARCHAR(10) NOT NULL,
389 PRIMARY KEY (ID));
390
391 CREATE TABLE Rule
392 (ID SERIAL UNIQUE,
393 Name VARCHAR(255) NULL,
394 Priority INTEGER NOT NULL,
395 Active INTEGER NOT NULL DEFAULT 0,
396 Direction INTEGER NOT NULL DEFAULT 2,
397 Count INTEGER NOT NULL DEFAULT 0,
398 PRIMARY KEY (ID));
399
400 CREATE TABLE RuleGroup
401 (Objectgroup_ID INTEGER NOT NULL,
402 Rule_ID INTEGER NOT NULL,
403 Grouptype INTEGER NOT NULL,
404 PRIMARY KEY (Objectgroup_ID, Rule_ID, Grouptype));
405
406 $cgreylist_ctablecmd;
407
408 $clusterinfo_ctablecmd;
409
5e1408fd
DM
410 $local_stat_ctablecmd;
411
f1bf78ff
DM
412 $daily_stat_ctablecmd;
413
414 $domain_stat_ctablecmd;
415
416 $statinfo_ctablecmd;
417
418 $cmailstore_ctablecmd;
419
420 $cstatistic_ctablecmd;
421
422 $userprefs_ctablecmd;
423
424 $virusinfo_stat_ctablecmd;
425EOD
426 );
427
428 return $dbh;
429}
430
0a580593
DM
431sub cond_create_action_quarantine {
432 my ($ruledb) = @_;
433
434 my $dbh = $ruledb->{dbh};
435
436 eval {
437 my $sth = $dbh->prepare(
438 "SELECT * FROM Objectgroup, Object " .
439 "WHERE Object.ObjectType = ? AND Objectgroup.Class = ? " .
440 "AND Object.objectgroup_id = Objectgroup.id");
441
442 my $otype = PMG::RuleDB::Quarantine::otype();
443 if ($sth->execute($otype, 'action') <= 0) {
444 my $obj = PMG::RuleDB::Quarantine->new ();
445 my $txt = decode_entities(PMG::RuleDB::Quarantine->otype_text);
446 my $quarantine = $ruledb->create_group_with_obj
9578dcd7 447 ($obj, $txt, 'Move to quarantine.');
0a580593
DM
448 }
449 };
450}
451
452sub cond_create_std_actions {
453 my ($ruledb) = @_;
454
455 cond_create_action_quarantine($ruledb);
456
457 #cond_create_action_report_spam($ruledb);
458}
459
460
461sub upgradedb {
462 my ($ruledb) = @_;
463
464 my $dbh = $ruledb->{dbh};
465
758c7b6b 466 # make sure we do not use slow sequential scans when upgraing
0a580593
DM
467 # database (before analyze can gather statistics)
468 $dbh->do("set enable_seqscan = false");
469
97cab76a 470 my $tables = {
5e1408fd
DM
471 'LocalStat', $local_stat_ctablecmd,
472 'DailyStat', $daily_stat_ctablecmd,
97cab76a
DM
473 'DomainStat', $domain_stat_ctablecmd,
474 'StatInfo', $statinfo_ctablecmd,
475 'CMailStore', $cmailstore_ctablecmd,
476 'UserPrefs', $userprefs_ctablecmd,
477 'CGreylist', $cgreylist_ctablecmd,
478 'CStatistic', $cstatistic_ctablecmd,
479 'ClusterInfo', $clusterinfo_ctablecmd,
480 'VirusInfo', $virusinfo_stat_ctablecmd,
481 };
482
483 foreach my $table (keys %$tables) {
7f2a3858 484 cond_create_dbtable($dbh, $table, $tables->{$table});
97cab76a 485 }
0a580593 486
e7c865af 487 cond_create_std_actions($ruledb);
0a580593 488
dc295201 489 # upgrade tables here if necessary
7d980415 490 if (!database_column_exists($dbh, 'LocalStat', 'PregreetCount')) {
e17403ff
DM
491 $dbh->do("ALTER TABLE LocalStat ADD COLUMN " .
492 "PregreetCount INTEGER DEFAULT 0 NOT NULL");
7d980415 493 }
70aafbae 494
ac28e80e
DM
495 eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); };
496 # ignore errors here
497
498
f2eb5f90
DM
499 # add missing TicketID to CMSReceivers
500 if (!database_column_exists($dbh, 'CMSReceivers', 'TicketID')) {
501 eval {
502 $dbh->begin_work;
503 $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq");
504 $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " .
505 "TicketID INTEGER NOT NULL " .
506 "DEFAULT nextval('cmsreceivers_ticketid_seq')");
507 $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " .
508 "TicketID DROP DEFAULT");
509 $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq");
510 $dbh->commit;
511 };
512 if (my $err = $@) {
513 $dbh->rollback;
514 die $err;
515 }
516 }
517
0a580593
DM
518 # update obsolete content type names
519 eval {
e7c865af
DM
520 $dbh->do("UPDATE Object " .
521 "SET value = 'content-type:application/java-vm' ".
522 "WHERE objecttype = 3003 " .
523 "AND value = 'content-type:application/x-java-vm';");
0a580593
DM
524 };
525
97cab76a
DM
526 foreach my $table (keys %$tables) {
527 eval { $dbh->do("ANALYZE $table"); };
528 warn $@ if $@;
529 }
79bc9b56
DM
530
531 reload_ruledb();
0a580593
DM
532}
533
534sub init_ruledb {
535 my ($ruledb, $reset, $testmode) = @_;
536
537 my $dbh = $ruledb->{dbh};
538
539 if (!$reset) {
540 # Greylist Objectgroup
541 my $greylistgroup = PMG::RuleDB::Group->new
542 ("GreyExclusion", "-", "greylist");
543 $ruledb->save_group ($greylistgroup);
544
545 } else {
546 # we do not touch greylist objects
547 my $glids = "SELECT object.ID FROM Object, Objectgroup WHERE " .
548 "objectgroup_id = objectgroup.id and class = 'greylist'";
549
550 $dbh->do ("DELETE FROM Rule; " .
551 "DELETE FROM RuleGroup; " .
552 "DELETE FROM Attribut WHERE Object_ID NOT IN ($glids); " .
553 "DELETE FROM Object WHERE ID NOT IN ($glids); " .
554 "DELETE FROM Objectgroup WHERE class != 'greylist';");
555 }
556
557 # WHO Objects
558
559 # Blacklist
560 my $obj = PMG::RuleDB::EMail->new ('nomail@fromthisdomain.com');
561 my $blacklist = $ruledb->create_group_with_obj(
562 $obj, 'Blacklist', 'Global blacklist');
758c7b6b 563
0a580593
DM
564 # Whitelist
565 $obj = PMG::RuleDB::EMail->new('mail@fromthisdomain.com');
566 my $whitelist = $ruledb->create_group_with_obj(
567 $obj, 'Whitelist', 'Global whitelist');
568
569 # WHEN Objects
570
571 # Working hours
a29b9649 572 $obj = PMG::RuleDB::TimeFrame->new(8*60, 16*60);
0a580593
DM
573 my $working_hours =$ruledb->create_group_with_obj($obj, 'Office Hours' ,
574 'Usual office hours');
575
576 # WHAT Objects
577
0a580593 578 # Images
a29b9649
DM
579 $obj = PMG::RuleDB::ContentTypeFilter->new('image/.*');
580 my $img_content = $ruledb->create_group_with_obj(
581 $obj, 'Images', 'All kinds of graphic files');
582
0a580593 583 # Multimedia
a29b9649
DM
584 $obj = PMG::RuleDB::ContentTypeFilter->new('audio/.*');
585 my $mm_content = $ruledb->create_group_with_obj(
586 $obj, 'Multimedia', 'Audio and Video');
0a580593 587
a29b9649
DM
588 $obj = PMG::RuleDB::ContentTypeFilter->new('video/.*');
589 $ruledb->group_add_object($mm_content, $obj);
0a580593
DM
590
591 # Office Files
a29b9649
DM
592 $obj = PMG::RuleDB::ContentTypeFilter->new('application/vnd\.ms-excel');
593 my $office_content = $ruledb->create_group_with_obj(
594 $obj, 'Office Files', 'Common Office Files');
758c7b6b 595
a29b9649
DM
596 $obj = PMG::RuleDB::ContentTypeFilter->new(
597 'application/vnd\.ms-powerpoint');
758c7b6b 598
a29b9649 599 $ruledb->group_add_object($office_content, $obj);
758c7b6b 600
a29b9649 601 $obj = PMG::RuleDB::ContentTypeFilter->new('application/msword');
0a580593 602 $ruledb->group_add_object ($office_content, $obj);
758c7b6b 603
a29b9649
DM
604 $obj = PMG::RuleDB::ContentTypeFilter->new(
605 'application/vnd\.openxmlformats-officedocument\..*');
606 $ruledb->group_add_object($office_content, $obj);
758c7b6b 607
a29b9649
DM
608 $obj = PMG::RuleDB::ContentTypeFilter->new(
609 'application/vnd\.oasis\.opendocument\..*');
610 $ruledb->group_add_object($office_content, $obj);
0a580593 611
a29b9649
DM
612 $obj = PMG::RuleDB::ContentTypeFilter->new(
613 'application/vnd\.stardivision\..*');
614 $ruledb->group_add_object($office_content, $obj);
758c7b6b 615
a29b9649
DM
616 $obj = PMG::RuleDB::ContentTypeFilter->new(
617 'application/vnd\.sun\.xml\..*');
618 $ruledb->group_add_object($office_content, $obj);
758c7b6b 619
0a580593 620 # Dangerous Content
a29b9649
DM
621 $obj = PMG::RuleDB::ContentTypeFilter->new(
622 'application/x-ms-dos-executable');
623 my $exe_content = $ruledb->create_group_with_obj(
624 $obj, 'Dangerous Content', 'executable files and partial messages');
758c7b6b 625
a29b9649
DM
626 $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-java');
627 $ruledb->group_add_object($exe_content, $obj);
628 $obj = PMG::RuleDB::ContentTypeFilter->new('application/javascript');
629 $ruledb->group_add_object($exe_content, $obj);
630 $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-executable');
631 $ruledb->group_add_object($exe_content, $obj);
79ef2452
DM
632 $obj = PMG::RuleDB::ContentTypeFilter->new('application/x-ms-dos-executable');
633 $ruledb->group_add_object($exe_content, $obj);
a29b9649
DM
634 $obj = PMG::RuleDB::ContentTypeFilter->new('message/partial');
635 $ruledb->group_add_object($exe_content, $obj);
636 $obj = PMG::RuleDB::MatchFilename->new('.*\.(vbs|pif|lnk|shs|shb)');
637 $ruledb->group_add_object($exe_content, $obj);
be6c5e4a 638 $obj = PMG::RuleDB::MatchFilename->new('.*\.\{.+\}');
a29b9649 639 $ruledb->group_add_object($exe_content, $obj);
0a580593
DM
640
641 # Virus
758c7b6b
DM
642 $obj = PMG::RuleDB::Virus->new();
643 my $virus = $ruledb->create_group_with_obj(
644 $obj, 'Virus', 'Matches virus infected mail');
645
0a580593
DM
646 # WHAT Objects
647
648 # Spam
758c7b6b
DM
649 $obj = PMG::RuleDB::Spam->new(3);
650 my $spam3 = $ruledb->create_group_with_obj(
651 $obj, 'Spam (Level 3)', 'Matches possible spam mail');
e7c865af 652
758c7b6b
DM
653 $obj = PMG::RuleDB::Spam->new(5);
654 my $spam5 = $ruledb->create_group_with_obj(
655 $obj, 'Spam (Level 5)', 'Matches possible spam mail');
e7c865af 656
758c7b6b
DM
657 $obj = PMG::RuleDB::Spam->new(10);
658 my $spam10 = $ruledb->create_group_with_obj(
659 $obj, 'Spam (Level 10)', 'Matches possible spam mail');
0a580593
DM
660
661 # ACTIONS
662
758c7b6b
DM
663 # Mark Spam
664 $obj = PMG::RuleDB::ModField->new('X-SPAM-LEVEL', '__SPAM_INFO__');
665 my $mod_spam_level = $ruledb->create_group_with_obj(
e7c865af 666 $obj, 'Modify Spam Level',
758c7b6b 667 'Mark mail as spam by adding a header tag.');
0a580593
DM
668
669 # Mark Spam
758c7b6b
DM
670 $obj = PMG::RuleDB::ModField->new('subject', 'SPAM: __SUBJECT__');
671 my $mod_spam_subject = $ruledb->create_group_with_obj(
e7c865af 672 $obj, 'Modify Spam Subject',
758c7b6b 673 'Mark mail as spam by modifying the subject.');
e7c865af 674
0a580593 675 # Remove matching attachments
758c7b6b
DM
676 $obj = PMG::RuleDB::Remove->new(0);
677 my $remove = $ruledb->create_group_with_obj(
678 $obj, 'Remove attachments', 'Remove matching attachments');
e7c865af 679
0a580593 680 # Remove all attachments
758c7b6b
DM
681 $obj = PMG::RuleDB::Remove->new(1);
682 my $remove_all = $ruledb->create_group_with_obj(
683 $obj, 'Remove all attachments', 'Remove all attachments');
0a580593
DM
684
685 # Accept
758c7b6b
DM
686 $obj = PMG::RuleDB::Accept->new();
687 my $accept = $ruledb->create_group_with_obj(
688 $obj, 'Accept', 'Accept mail for Delivery');
0a580593
DM
689
690 # Block
758c7b6b
DM
691 $obj = PMG::RuleDB::Block->new ();
692 my $block = $ruledb->create_group_with_obj($obj, 'Block', 'Block mail');
0a580593
DM
693
694 # Quarantine
758c7b6b
DM
695 $obj = PMG::RuleDB::Quarantine->new();
696 my $quarantine = $ruledb->create_group_with_obj(
697 $obj, 'Quarantine', 'Move mail to quarantine');
0a580593
DM
698
699 # Notify Admin
758c7b6b
DM
700 $obj = PMG::RuleDB::Notify->new('__ADMIN__');
701 my $notify_admin = $ruledb->create_group_with_obj(
702 $obj, 'Notify Admin', 'Send notification');
0a580593
DM
703
704 # Notify Sender
758c7b6b
DM
705 $obj = PMG::RuleDB::Notify->new('__SENDER__');
706 my $notify_sender = $ruledb->create_group_with_obj(
707 $obj, 'Notify Sender', 'Send notification');
0a580593
DM
708
709 # Add Disclaimer
758c7b6b
DM
710 $obj = PMG::RuleDB::Disclaimer->new ();
711 my $add_discl = $ruledb->create_group_with_obj(
712 $obj, 'Disclaimer', 'Add Disclaimer');
0a580593
DM
713
714 # Attach original mail
715 #$obj = Proxmox::RuleDB::Attach->new ();
758c7b6b 716 #my $attach_orig = $ruledb->create_group_with_obj ($obj, 'Attach Original Mail',
0a580593
DM
717 # 'Attach Original Mail');
718
719 ####################### RULES ##################################
720
721 ## Block Dangerous Files
a29b9649 722 my $rule = PMG::RuleDB::Rule->new ('Block Dangerous Files', 93, 1, 0);
0a580593
DM
723 $ruledb->save_rule ($rule);
724
725 $ruledb->rule_add_what_group ($rule, $exe_content);
726 $ruledb->rule_add_action ($rule, $remove);
727
728 ## Block Viruses
a29b9649 729 $rule = PMG::RuleDB::Rule->new ('Block Viruses', 96, 1, 0);
0a580593
DM
730 $ruledb->save_rule ($rule);
731
732 $ruledb->rule_add_what_group ($rule, $virus);
733 $ruledb->rule_add_action ($rule, $notify_admin);
758c7b6b 734
0a580593
DM
735 if ($testmode) {
736 $ruledb->rule_add_action ($rule, $block);
737 } else {
738 $ruledb->rule_add_action ($rule, $quarantine);
739 }
740
741 ## Virus Alert
a29b9649 742 $rule = PMG::RuleDB::Rule->new ('Virus Alert', 96, 1, 1);
0a580593
DM
743 $ruledb->save_rule ($rule);
744
745 $ruledb->rule_add_what_group ($rule, $virus);
746 $ruledb->rule_add_action ($rule, $notify_sender);
747 $ruledb->rule_add_action ($rule, $notify_admin);
748 $ruledb->rule_add_action ($rule, $block);
758c7b6b 749
0a580593 750 ## Blacklist
a29b9649 751 $rule = PMG::RuleDB::Rule->new ('Blacklist', 98, 1, 0);
0a580593
DM
752 $ruledb->save_rule ($rule);
753
754 $ruledb->rule_add_from_group ($rule, $blacklist);
755 $ruledb->rule_add_action ($rule, $block);
756
757 ## Modify header
758 if (!$testmode) {
a29b9649 759 $rule = PMG::RuleDB::Rule->new ('Modify Header', 90, 1, 0);
0a580593
DM
760 $ruledb->save_rule ($rule);
761 $ruledb->rule_add_action ($rule, $mod_spam_level);
762 }
763
764 ## Whitelist
a29b9649 765 $rule = PMG::RuleDB::Rule->new ('Whitelist', 85, 1, 0);
0a580593
DM
766 $ruledb->save_rule ($rule);
767
768 $ruledb->rule_add_from_group ($rule, $whitelist);
769 $ruledb->rule_add_action ($rule, $accept);
770
771 if ($testmode) {
a29b9649 772 $rule = PMG::RuleDB::Rule->new ('Mark Spam', 80, 1, 0);
0a580593
DM
773 $ruledb->save_rule ($rule);
774
775 $ruledb->rule_add_what_group ($rule, $spam10);
776 $ruledb->rule_add_action ($rule, $mod_spam_level);
777 $ruledb->rule_add_action ($rule, $mod_spam_subject);
778 } else {
779 # Quarantine/Mark Spam (Level 3)
a29b9649 780 $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 3)', 80, 1, 0);
0a580593
DM
781 $ruledb->save_rule ($rule);
782
783 $ruledb->rule_add_what_group ($rule, $spam3);
784 $ruledb->rule_add_action ($rule, $mod_spam_subject);
785 $ruledb->rule_add_action ($rule, $quarantine);
786 #$ruledb->rule_add_action ($rule, $count_spam);
787 }
788
789 # Quarantine/Mark Spam (Level 5)
1a3a9999 790 $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0);
0a580593
DM
791 $ruledb->save_rule ($rule);
792
793 $ruledb->rule_add_what_group ($rule, $spam5);
794 $ruledb->rule_add_action ($rule, $mod_spam_subject);
795 $ruledb->rule_add_action ($rule, $quarantine);
796
797 ## Block Spam Level 10
1a3a9999 798 $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 82, 0, 0);
0a580593
DM
799 $ruledb->save_rule ($rule);
800
801 $ruledb->rule_add_what_group ($rule, $spam10);
802 $ruledb->rule_add_action ($rule, $block);
803
804 ## Block Outgoing Spam
a29b9649 805 $rule = PMG::RuleDB::Rule->new ('Block outgoing Spam', 70, 0, 1);
0a580593
DM
806 $ruledb->save_rule ($rule);
807
808 $ruledb->rule_add_what_group ($rule, $spam3);
809 $ruledb->rule_add_action ($rule, $notify_admin);
810 $ruledb->rule_add_action ($rule, $notify_sender);
811 $ruledb->rule_add_action ($rule, $block);
812
813 ## Add disclaimer
a29b9649 814 $rule = PMG::RuleDB::Rule->new ('Add Disclaimer', 60, 0, 1);
0a580593
DM
815 $ruledb->save_rule ($rule);
816 $ruledb->rule_add_action ($rule, $add_discl);
817
818 # Block Multimedia Files
a29b9649 819 $rule = PMG::RuleDB::Rule->new ('Block Multimedia Files', 87, 0, 2);
0a580593
DM
820 $ruledb->save_rule ($rule);
821
822 $ruledb->rule_add_what_group ($rule, $mm_content);
823 $ruledb->rule_add_action ($rule, $remove);
824
825 #$ruledb->rule_add_from_group ($rule, $anybody);
826 #$ruledb->rule_add_from_group ($rule, $trusted);
827 #$ruledb->rule_add_to_group ($rule, $anybody);
828 #$ruledb->rule_add_what_group ($rule, $ct_filter);
829 #$ruledb->rule_add_action ($rule, $add_discl);
830 #$ruledb->rule_add_action ($rule, $remove);
831 #$ruledb->rule_add_action ($rule, $bcc);
832 #$ruledb->rule_add_action ($rule, $storeq);
833 #$ruledb->rule_add_action ($rule, $accept);
834
835 cond_create_std_actions ($ruledb);
8c002247
DM
836
837 reload_ruledb();
0a580593
DM
838}
839
8f4e102b
DM
840sub get_remote_time {
841 my ($rdb) = @_;
842
843 my $sth = $rdb->prepare("SELECT EXTRACT (EPOCH FROM TIMESTAMP (0) WITH TIME ZONE 'now') as ctime;");
844 $sth->execute();
845 my $ctinfo = $sth->fetchrow_hashref();
846 $sth->finish ();
847
848 return $ctinfo ? $ctinfo->{ctime} : 0;
849}
850
cfdf6608
DM
851sub init_masterdb {
852 my ($lcid, $database) = @_;
853
854 die "got unexpected cid for new master" if !$lcid;
855
856 my $dbh;
857
858 eval {
859 $dbh = open_ruledb($database);
860
861 $dbh->begin_work;
862
863 print STDERR "update quarantine database\n";
864 $dbh->do ("UPDATE CMailStore SET CID = $lcid WHERE CID = 0;" .
865 "UPDATE CMSReceivers SET CMailStore_CID = $lcid WHERE CMailStore_CID = 0;");
866
867 print STDERR "update statistic database\n";
868 $dbh->do ("UPDATE CStatistic SET CID = $lcid WHERE CID = 0;" .
869 "UPDATE CReceivers SET CStatistic_CID = $lcid WHERE CStatistic_CID = 0;");
870
871 print STDERR "update greylist database\n";
872 $dbh->do ("UPDATE CGreylist SET CID = $lcid WHERE CID = 0;");
873
5e1408fd
DM
874 print STDERR "update localstat database\n";
875 $dbh->do ("UPDATE LocalStat SET CID = $lcid WHERE CID = 0;");
876
cfdf6608
DM
877 $dbh->commit;
878 };
879 my $err = $@;
880
881 if ($dbh) {
882 $dbh->rollback if $err;
883 $dbh->disconnect();
884 }
885
886 die $err if $err;
887}
888
8fb6f404
DM
889sub purge_statistic_database {
890 my ($dbh, $statlifetime) = @_;
891
892 return if $statlifetime <= 0;
893
894 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
895 my $end = timelocal(0, 0, 0, $mday, $mon, $year);
896 my $start = $end - $statlifetime*86400;
897
898 # delete statistics older than $start
899
900 my $rows = 0;
901
902 eval {
903 $dbh->begin_work;
904
905 my $sth = $dbh->prepare("DELETE FROM CStatistic WHERE time < $start");
906 $sth->execute;
907 $rows = $sth->rows;
908 $sth->finish;
909
910 if ($rows > 0) {
911 $sth = $dbh->prepare(
912 "DELETE FROM CReceivers WHERE NOT EXISTS " .
913 "(SELECT * FROM CStatistic WHERE CID = CStatistic_CID AND RID = CStatistic_RID)");
914
915 $sth->execute;
916 }
917 $dbh->commit;
918 };
919 if (my $err = $@) {
920 $dbh->rollback;
921 die $err;
922 }
923
924 return $rows;
925}
926
1af3c560
DM
927sub purge_quarantine_database {
928 my ($dbh, $qtype, $lifetime) = @_;
929
930 my $spooldir = $PMG::MailQueue::spooldir;
931
932 my (undef, undef, undef, $mday, $mon, $year) = localtime(time());
933 my $end = timelocal(0, 0, 0, $mday, $mon, $year);
934 my $start = $end - $lifetime*86400;
935
936 my $sth = $dbh->prepare(
937 "SELECT file FROM CMailStore WHERE time < $start AND QType = '$qtype'");
938
939 $sth->execute();
940
b8b8c31b
DM
941 my $count = 0;
942
1af3c560
DM
943 while (my $ref = $sth->fetchrow_hashref()) {
944 my $filename = "$spooldir/$ref->{file}";
b8b8c31b 945 $count++ if unlink($filename);
1af3c560
DM
946 }
947
948 $sth->finish();
949
950 $dbh->do(
951 "DELETE FROM CMailStore WHERE time < $start AND QType = '$qtype';" .
952 "DELETE FROM CMSReceivers WHERE NOT EXISTS " .
953 "(SELECT * FROM CMailStore WHERE CID = CMailStore_CID AND RID = CMailStore_RID)");
b8b8c31b
DM
954
955 return $count;
1af3c560
DM
956}
957
8af3f74c
DM
958sub get_quarantine_count {
959 my ($dbh, $qtype) = @_;
960
961 # Note;: We try to estimate used disk space - each mail
962 # is stored in an extra file ...
963
964 my $bs = 4096;
965
966 my $sth = $dbh->prepare(
967 "SELECT count(ID) as count, sum (ceil((Bytes+$bs-1)/$bs)*$bs) / (1024*1024) as mbytes, " .
968 "avg(Bytes) as avgbytes, avg(Spamlevel) as avgspam " .
969 "FROM CMailStore WHERE QType = ?");
970
971 $sth->execute($qtype);
972
973 my $ref = $sth->fetchrow_hashref();
974
975 $sth->finish;
976
13a3f36c
DM
977 foreach my $k (qw(count mbytes avgbytes avgspam)) {
978 $ref->{$k} //= 0;
979 }
980
8af3f74c
DM
981 return $ref;
982}
983
da686811
DM
984sub copy_table {
985 my ($ldb, $rdb, $table) = @_;
986
987 $table = lc($table);
988
989 my $sth = $ldb->column_info(undef, undef, $table, undef);
990 my $attrs = $sth->fetchall_arrayref({});
991
992 my @col_arr;
993 foreach my $ref (@$attrs) {
994 push @col_arr, $ref->{COLUMN_NAME};
995 }
996
997 $sth->finish();
998
999 my $cols = join(', ', @col_arr);
1000 $cols || die "unable to fetch column definitions of table '$table' : ERROR";
1001
1002 $rdb->do("COPY $table ($cols) TO STDOUT");
1003
1004 my $data = '';
1005
1006 eval {
1007 $ldb->do("COPY $table ($cols) FROM stdin");
1008
1009 while ($rdb->pg_getcopydata($data) >= 0) {
1010 $ldb->pg_putcopydata($data);
1011 }
1012
1013 $ldb->pg_putcopyend();
1014 };
1015 if (my $err = $@) {
1016 $ldb->pg_putcopyend();
1017 die $err;
1018 }
1019}
1020
6cf5b4aa
DM
1021sub copy_selected_data {
1022 my ($dbh, $select_sth, $table, $attrs, $callback) = @_;
1023
1024 my $count = 0;
1025
1026 my $insert_sth = $dbh->prepare(
c2e5905e 1027 "INSERT INTO ${table}(" . join(',', @$attrs) . ') ' .
6cf5b4aa
DM
1028 'VALUES (' . join(',', ('?') x scalar(@$attrs)) . ')');
1029
1030 while (my $ref = $select_sth->fetchrow_hashref()) {
1031 $callback->($ref) if $callback;
1032 $count++;
1033 $insert_sth->execute(map { $ref->{$_} } @$attrs);
1034 }
1035
1036 return $count;
1037}
1038
987ee804
DM
1039sub update_master_clusterinfo {
1040 my ($clientcid) = @_;
1041
1042 my $dbh = open_ruledb();
1043
1044 $dbh->do("DELETE FROM ClusterInfo WHERE CID = $clientcid");
1045
5e1408fd 1046 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
987ee804
DM
1047
1048 foreach my $table (@mt) {
1049 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $clientcid, 'lastmt_$table', " .
1050 "EXTRACT(EPOCH FROM now())");
1051 }
1052}
1053
1054sub update_client_clusterinfo {
1055 my ($mastercid) = @_;
1056
1057 my $dbh = open_ruledb();
1058
1059 $dbh->do ("DELETE FROM StatInfo"); # not needed at node
1060
1061 $dbh->do ("DELETE FROM ClusterInfo WHERE CID = $mastercid");
1062
1063 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CMailStore', " .
1064 "COALESCE (max (rid), -1) FROM CMailStore WHERE cid = $mastercid");
1065
1066 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastid_CStatistic', " .
1067 "COALESCE (max (rid), -1) FROM CStatistic WHERE cid = $mastercid");
1068
5e1408fd 1069 my @mt = ('CMSReceivers', 'CGreylist', 'UserPrefs', 'DomainStat', 'DailyStat', 'LocalStat', 'VirusInfo');
987ee804
DM
1070
1071 foreach my $table (@mt) {
1072 $dbh->do ("INSERT INTO ClusterInfo (cid, name, ivalue) select $mastercid, 'lastmt_$table', " .
1073 "COALESCE (max (mtime), 0) FROM $table");
1074 }
1075}
1076
7b313034
DM
1077sub create_clusterinfo_default {
1078 my ($dbh, $rcid, $name, $ivalue, $svalue) = @_;
1079
1080 my $sth = $dbh->prepare("SELECT * FROM ClusterInfo WHERE CID = ? AND Name = ?");
1081 $sth->execute($rcid, $name);
1082 if (!$sth->fetchrow_hashref()) {
1083 $dbh->do("INSERT INTO ClusterInfo (CID, Name, IValue, SValue) " .
1084 "VALUES (?, ?, ?, ?)", undef,
1085 $rcid, $name, $ivalue, $svalue);
1086 }
1087 $sth->finish();
1088}
1089
1090sub read_int_clusterinfo {
1091 my ($dbh, $rcid, $name) = @_;
1092
1093 my $sth = $dbh->prepare(
1094 "SELECT ivalue as value FROM ClusterInfo " .
1095 "WHERE cid = ? AND NAME = ?");
1096 $sth->execute($rcid, $name);
1097 my $cinfo = $sth->fetchrow_hashref();
1098 $sth->finish();
1099
1100 return $cinfo->{value};
1101}
1102
1103sub write_maxint_clusterinfo {
1104 my ($dbh, $rcid, $name, $value) = @_;
1105
2e049252 1106 $dbh->do("UPDATE ClusterInfo SET ivalue = GREATEST(ivalue, ?) " .
7b313034
DM
1107 "WHERE cid = ? AND name = ?", undef,
1108 $value, $rcid, $name);
1109}
1110
987ee804
DM
1111sub init_nodedb {
1112 my ($cinfo) = @_;
1113
1114 my $ni = $cinfo->{master};
1115
1116 die "no master defined - unable to sync data from master\n" if !$ni;
1117
1118 my $master_ip = $ni->{ip};
1119 my $master_cid = $ni->{cid};
22c16ff6 1120 my $master_name = $ni->{name};
987ee804
DM
1121
1122 my $fn = "/tmp/masterdb$$.tar";
1123 unlink $fn;
1124
1125 my $dbname = $default_db_name;
1126
1127 eval {
1128 print STDERR "copying master database from '${master_ip}'\n";
1129
97cab76a
DM
1130 open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
1131
b8c0c87a
DM
1132 my $cmd = ['/usr/bin/ssh', '-o', 'BatchMode=yes',
1133 '-o', "HostKeyAlias=${master_name}", $master_ip,
1134 'pg_dump', $dbname, '-F', 'c' ];
1135
1136 PVE::Tools::run_command($cmd, output => '>&' . fileno($fh));
22c16ff6 1137
97cab76a 1138 close($fh);
987ee804
DM
1139
1140 my $size = -s $fn;
1141
1142 print STDERR "copying master database finished (got $size bytes)\n";
1143
1144 print STDERR "delete local database\n";
1145
97cab76a 1146 postgres_admin_cmd('dropdb', undef, $dbname , '--if-exists');
987ee804
DM
1147
1148 print STDERR "create new local database\n";
1149
2cc31eaa 1150 $createdb->($dbname);
987ee804
DM
1151
1152 print STDERR "insert received data into local database\n";
1153
1154 my $mess;
1155 my $parser = sub {
1156 my $line = shift;
1157
1158 if ($line =~ m/restoring data for table \"(.+)\"/) {
1159 print STDERR "restoring table $1\n";
1160 } elsif (!$mess && ($line =~ m/creating (INDEX|CONSTRAINT)/)) {
1161 $mess = "creating indexes";
1162 print STDERR "$mess\n";
1163 }
1164 };
1165
97cab76a
DM
1166 my $opts = {
1167 outfunc => $parser,
1168 errfunc => $parser,
1169 errmsg => "pg_restore failed"
1170 };
1171
1172 postgres_admin_cmd('pg_restore', $opts, '-d', $dbname, '-v', $fn);
987ee804
DM
1173
1174 print STDERR "run analyze to speed up database queries\n";
1175
97cab76a 1176 postgres_admin_cmd('psql', { input => 'analyze;' }, $dbname);
987ee804
DM
1177
1178 update_client_clusterinfo($master_cid);
1179 };
1180
1181 my $err = $@;
1182
1183 unlink $fn;
1184
1185 die $err if $err;
1186}
1187
f3464b71
DM
1188sub cluster_sync_status {
1189 my ($cinfo) = @_;
1190
1191 my $dbh;
1192
1193 my $minmtime;
1194
1195 foreach my $ni (values %{$cinfo->{ids}}) {
1196 next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID
1197 $minmtime->{$ni->{cid}} = 0;
1198 }
1199
1200 eval {
1201 $dbh = open_ruledb();
1202
1203 my $sth = $dbh->prepare(
1204 "SELECT cid, MIN (ivalue) as minmtime FROM ClusterInfo " .
1205 "WHERE name = 'lastsync' AND ivalue > 0 " .
1206 "GROUP BY cid");
1207
1208 $sth->execute();
1209
1210 while (my $info = $sth->fetchrow_hashref()) {
1211 foreach my $ni (values %{$cinfo->{ids}}) {
1212 next if $cinfo->{local}->{cid} == $ni->{cid}; # skip local CID
1213 if ($ni->{cid} == $info->{cid}) { # node exists
1214 $minmtime->{$ni->{cid}} = $info->{minmtime};
1215 }
1216 }
1217 }
1218
2ab22e49 1219 $sth->finish();
f3464b71
DM
1220 };
1221 my $err = $@;
1222
1223 $dbh->disconnect() if $dbh;
1224
b902c0b8 1225 syslog('err', $err) if $err;
f3464b71
DM
1226
1227 return $minmtime;
1228}
1229
2ab22e49 1230sub load_mail_data {
666b5e8f 1231 my ($dbh, $cid, $rid, $ticketid) = @_;
2ab22e49
DM
1232
1233 my $sth = $dbh->prepare(
1234 "SELECT * FROM CMailStore, CMSReceivers WHERE " .
666b5e8f 1235 "CID = ? AND RID = ? AND TicketID = ? AND " .
2ab22e49 1236 "CID = CMailStore_CID AND RID = CMailStore_RID");
666b5e8f 1237 $sth->execute($cid, $rid, $ticketid);
2ab22e49
DM
1238
1239 my $res = $sth->fetchrow_hashref();
1240
1241 $sth->finish();
1242
666b5e8f 1243 die "no such mail (C${cid}R${rid}T${ticketid})\n" if !defined($res);
2ab22e49
DM
1244
1245 return $res;
1246}
1247
cbef3ff8 1248sub reload_ruledb {
f9967a49
DM
1249 my ($ruledb) = @_;
1250
1251 # Note: we pass $ruledb when modifying SMTP whitelist
1252 if (defined($ruledb)) {
1253 eval {
1254 my $rulecache = PMG::RuleCache->new($ruledb);
1255 PMG::Config::rewrite_postfix_whitelist($rulecache);
1256 };
1257 if (my $err = $@) {
1258 warn "problems updating SMTP whitelist - $err";
1259 }
1260 }
1261
83e9f427 1262 my $pid_file = '/run/pmg-smtp-filter.pid';
cbef3ff8
DM
1263 my $pid = PVE::Tools::file_read_firstline($pid_file);
1264
1265 return 0 if !$pid;
1266
14e192ba
DM
1267 return 0 if $pid !~ m/^(\d+)$/;
1268 $pid = $1; # untaint
1269
cbef3ff8
DM
1270 return kill (10, $pid); # send SIGUSR1
1271}
1272
a355f100 12731;