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