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