]>
Commit | Line | Data |
---|---|---|
72d8bf49 DM |
1 | package PMG::RuleDB; |
2 | ||
3 | use strict; | |
4 | use warnings; | |
72d8bf49 DM |
5 | use DBI; |
6 | use HTML::Entities; | |
af748e8c | 7 | use Data::Dumper; |
4418cffc | 8 | use Encode qw(encode); |
72d8bf49 DM |
9 | |
10 | use PVE::SafeSyslog; | |
11 | ||
12 | use PMG::Utils; | |
13 | use PMG::DBTools; | |
14 | ||
15 | use PMG::RuleDB::Group; | |
16 | ||
17 | #use Proxmox::Statistic; | |
18 | use PMG::RuleDB::Object; | |
19 | use PMG::RuleDB::WhoRegex; | |
20 | use PMG::RuleDB::ReceiverRegex; | |
21 | use PMG::RuleDB::EMail; | |
22 | use PMG::RuleDB::Receiver; | |
23 | use PMG::RuleDB::IPAddress; | |
24 | use PMG::RuleDB::IPNet; | |
25 | use PMG::RuleDB::Domain; | |
26 | use PMG::RuleDB::ReceiverDomain; | |
10621236 | 27 | use PMG::RuleDB::LDAP; |
c712d3a2 | 28 | use PMG::RuleDB::LDAPUser; |
72d8bf49 DM |
29 | use PMG::RuleDB::TimeFrame; |
30 | use PMG::RuleDB::Spam; | |
31 | use PMG::RuleDB::ReportSpam; | |
32 | use PMG::RuleDB::Virus; | |
33 | use PMG::RuleDB::Accept; | |
34 | use PMG::RuleDB::Remove; | |
35 | use PMG::RuleDB::ModField; | |
36 | use PMG::RuleDB::MatchField; | |
37 | use PMG::RuleDB::MatchFilename; | |
5e809f47 | 38 | use PMG::RuleDB::MatchArchiveFilename; |
72d8bf49 DM |
39 | use PMG::RuleDB::Attach; |
40 | use PMG::RuleDB::Disclaimer; | |
41 | use PMG::RuleDB::BCC; | |
42 | use PMG::RuleDB::Quarantine; | |
43 | use PMG::RuleDB::Block; | |
44 | use PMG::RuleDB::Counter; | |
45 | use PMG::RuleDB::Notify; | |
46 | use PMG::RuleDB::Rule; | |
47 | use PMG::RuleDB::ContentTypeFilter; | |
c4741113 | 48 | use PMG::RuleDB::ArchiveFilter; |
72d8bf49 DM |
49 | |
50 | sub new { | |
51 | my ($type, $dbh) = @_; | |
9ef3f143 | 52 | |
72d8bf49 DM |
53 | $dbh = PMG::DBTools::open_ruledb("Proxmox_ruledb") if !defined ($dbh); |
54 | ||
55 | my $self = bless { dbh => $dbh }, $type; | |
56 | ||
57 | return $self; | |
58 | } | |
59 | ||
60 | sub close { | |
61 | my ($self) = @_; | |
62 | ||
63 | $self->{dbh}->disconnect(); | |
64 | } | |
65 | ||
9973fc66 | 66 | sub create_group_with_obj { |
bdf383f3 | 67 | my ($self, $obj, $name, $info) = @_; |
744483ce DM |
68 | |
69 | my $og; | |
70 | my $id; | |
71 | ||
72 | defined($obj) || die "proxmox: undefined object"; | |
73 | ||
4418cffc SI |
74 | $name = encode('UTF-8', $name // ''); |
75 | $info = encode('UTF-8', $info // ''); | |
bdf383f3 | 76 | |
744483ce DM |
77 | eval { |
78 | ||
79 | $self->{dbh}->begin_work; | |
80 | ||
bdf383f3 DM |
81 | $self->{dbh}->do("INSERT INTO Objectgroup (Name, Info, Class) " . |
82 | "VALUES (?, ?, ?)", undef, | |
83 | $name, $info, $obj->oclass()); | |
744483ce DM |
84 | |
85 | my $lid = PMG::Utils::lastid($self->{dbh}, 'objectgroup_id_seq'); | |
86 | ||
4a574ae2 | 87 | $og = PMG::RuleDB::Group->new($name, $info, $obj->oclass()); |
744483ce DM |
88 | $og->{id} = $lid; |
89 | ||
90 | $obj->{ogroup} = $lid; | |
91 | $id = $obj->save($self, 1); | |
92 | $obj->{id} = $id; # just to be sure | |
93 | ||
94 | $self->{dbh}->commit; | |
95 | }; | |
96 | if (my $err = $@) { | |
97 | $self->{dbh}->rollback; | |
98 | die $err; | |
99 | } | |
100 | return $og; | |
101 | } | |
102 | ||
72d8bf49 DM |
103 | sub load_groups { |
104 | my ($self, $rule) = @_; | |
105 | ||
9ef3f143 | 106 | defined($rule->{id}) || die "undefined rule id: ERROR"; |
72d8bf49 DM |
107 | |
108 | my $sth = $self->{dbh}->prepare( | |
109 | "SELECT RuleGroup.Grouptype, Objectgroup.ID, " . | |
9ef3f143 | 110 | "Objectgroup.Name, Objectgroup.Info " . |
72d8bf49 DM |
111 | "FROM Rulegroup, Objectgroup " . |
112 | "WHERE Rulegroup.Rule_ID = ? and " . | |
113 | "Rulegroup.Objectgroup_ID = Objectgroup.ID " . | |
114 | "ORDER BY RuleGroup.Grouptype"); | |
115 | ||
116 | my $groups = (); | |
117 | ||
118 | $sth->execute($rule->{id}); | |
119 | ||
4108629e | 120 | my ($from, $to, $when, $what, $action) = ([], [], [], [], []); |
72d8bf49 DM |
121 | |
122 | while (my $ref = $sth->fetchrow_hashref()) { | |
123 | my $og = PMG::RuleDB::Group->new($ref->{name}, $ref->{info}); | |
124 | $og->{id} = $ref->{id}; | |
125 | ||
126 | if ($ref->{'grouptype'} == 0) { #from | |
127 | push @$from, $og; | |
128 | } elsif ($ref->{'grouptype'} == 1) { # to | |
129 | push @$to, $og; | |
130 | } elsif ($ref->{'grouptype'} == 2) { # when | |
131 | push @$when, $og; | |
132 | } elsif ($ref->{'grouptype'} == 3) { # what | |
133 | push @$what, $og; | |
134 | } elsif ($ref->{'grouptype'} == 4) { # action | |
135 | my $objects = $self->load_group_objects($og->{id}); | |
136 | my $obj = @$objects[0]; | |
9ef3f143 | 137 | defined($obj) || die "undefined action object: ERROR"; |
72d8bf49 DM |
138 | $og->{action} = $obj; |
139 | push @$action, $og; | |
140 | } | |
141 | } | |
142 | ||
143 | $sth->finish(); | |
144 | ||
145 | return ($from, $to, $when, $what, $action); | |
146 | } | |
147 | ||
4108629e DM |
148 | sub load_groups_by_name { |
149 | my ($self, $rule) = @_; | |
150 | ||
151 | my ($from, $to, $when, $what, $action) = | |
152 | $self->load_groups($rule); | |
153 | ||
154 | return { | |
155 | from => $from, | |
156 | to => $to, | |
157 | when => $when, | |
158 | what => $what, | |
159 | action => $action, | |
160 | }; | |
161 | } | |
162 | ||
45d3ef6f DC |
163 | sub update_group_attributes { |
164 | my ($self, $og) = @_; | |
165 | ||
166 | my $attributes = [qw(and invert)]; | |
167 | ||
168 | for my $attribute ($attributes->@*) { | |
169 | # only save the values if they're set to 1 | |
170 | if ($og->{$attribute}) { | |
171 | $self->{dbh}->do( | |
172 | "INSERT INTO Objectgroup_Attributes (Objectgroup_ID, Name, Value) " . | |
173 | "VALUES (?, ?, ?) ". | |
174 | "ON CONFLICT (Objectgroup_ID, Name) DO UPDATE SET Value = ?", undef, | |
175 | $og->{id}, $attribute, $og->{$attribute}, $og->{$attribute}, | |
176 | ); | |
177 | } else { | |
178 | $self->{dbh}->do( | |
179 | "DELETE FROM Objectgroup_Attributes " . | |
180 | "WHERE Objectgroup_ID = ? AND Name = ?", undef, | |
181 | $og->{id}, $attribute, | |
182 | ); | |
183 | } | |
184 | } | |
185 | } | |
186 | ||
72d8bf49 DM |
187 | sub save_group { |
188 | my ($self, $og) = @_; | |
189 | ||
9ef3f143 DM |
190 | defined($og->{name}) || |
191 | die "undefined group attribute - name: ERROR"; | |
192 | defined($og->{info}) || | |
193 | die "undefined group attribute - info: ERROR"; | |
194 | defined($og->{class}) || | |
195 | die "undefined group attribute - class: ERROR"; | |
72d8bf49 DM |
196 | |
197 | if (defined($og->{id})) { | |
45d3ef6f DC |
198 | $self->{dbh}->begin_work; |
199 | ||
200 | eval { | |
201 | $self->{dbh}->do("UPDATE Objectgroup " . | |
202 | "SET Name = ?, Info = ? " . | |
203 | "WHERE ID = ?", undef, | |
204 | encode('UTF-8', $og->{name}), | |
205 | encode('UTF-8', $og->{info}), | |
206 | $og->{id}); | |
72d8bf49 | 207 | |
45d3ef6f | 208 | $self->update_group_attributes($og); |
9ef3f143 | 209 | |
45d3ef6f DC |
210 | $self->{dbh}->commit; |
211 | }; | |
72d8bf49 | 212 | |
45d3ef6f DC |
213 | if (my $err = $@) { |
214 | $self->{dbh}->rollback; | |
215 | syslog('err', $err); | |
216 | return undef; | |
217 | } | |
72d8bf49 | 218 | } else { |
45d3ef6f | 219 | $self->{dbh}->begin_work; |
72d8bf49 | 220 | |
45d3ef6f DC |
221 | eval { |
222 | my $sth = $self->{dbh}->prepare( | |
223 | "INSERT INTO Objectgroup (Name, Info, Class) " . | |
224 | "VALUES (?, ?, ?);"); | |
72d8bf49 | 225 | |
45d3ef6f DC |
226 | $sth->execute(encode('UTF-8', $og->name), encode('UTF-8', $og->info), $og->class); |
227 | ||
228 | $og->{id} = PMG::Utils::lastid($self->{dbh}, 'objectgroup_id_seq'); | |
229 | ||
230 | $self->update_group_attributes($og); | |
231 | ||
232 | $self->{dbh}->commit; | |
233 | }; | |
234 | ||
235 | if (my $err = $@) { | |
236 | $self->{dbh}->rollback; | |
237 | syslog('err', $err); | |
238 | return undef; | |
239 | } | |
72d8bf49 DM |
240 | } |
241 | ||
45d3ef6f | 242 | return $og->{id}; |
72d8bf49 DM |
243 | } |
244 | ||
72d8bf49 DM |
245 | sub delete_group { |
246 | my ($self, $groupid) = @_; | |
247 | ||
9ef3f143 | 248 | defined($groupid) || die "undefined group id: ERROR"; |
72d8bf49 DM |
249 | |
250 | eval { | |
251 | ||
252 | $self->{dbh}->begin_work; | |
253 | ||
254 | # test if group is used in rules | |
255 | $self->{dbh}->do("LOCK TABLE RuleGroup IN EXCLUSIVE MODE"); | |
256 | ||
257 | my $sth = $self->{dbh}->prepare( | |
258 | "SELECT Rule.Name as rulename, ObjectGroup.Name as groupname " . | |
259 | "FROM RuleGroup, Rule, ObjectGroup WHERE " . | |
260 | "ObjectGroup.ID = ? AND Objectgroup_ID = ObjectGroup.ID AND " . | |
261 | "Rule_ID = Rule.ID"); | |
262 | ||
263 | $sth->execute($groupid); | |
264 | ||
265 | if (my $ref = $sth->fetchrow_hashref()) { | |
4418cffc SI |
266 | my $groupname = PMG::Utils::try_decode_utf8($ref->{groupname}); |
267 | my $rulename = PMG::Utils::try_decode_utf8($ref->{rulename}); | |
268 | die "Group '$groupname' is used by rule '$rulename' - unable to delete\n"; | |
72d8bf49 DM |
269 | } |
270 | ||
271 | $sth->finish(); | |
272 | ||
9ef3f143 | 273 | $self->{dbh}->do("DELETE FROM ObjectGroup " . |
72d8bf49 DM |
274 | "WHERE ID = ?", undef, $groupid); |
275 | ||
9ef3f143 | 276 | $self->{dbh}->do("DELETE FROM RuleGroup " . |
72d8bf49 DM |
277 | "WHERE Objectgroup_ID = ?", undef, $groupid); |
278 | ||
279 | $sth = $self->{dbh}->prepare("SELECT * FROM Object " . | |
280 | "where Objectgroup_ID = ?"); | |
281 | $sth->execute($groupid); | |
9ef3f143 | 282 | |
72d8bf49 | 283 | while (my $ref = $sth->fetchrow_hashref()) { |
9ef3f143 | 284 | $self->{dbh}->do("DELETE FROM Attribut " . |
72d8bf49 DM |
285 | "WHERE Object_ID = ?", undef, $ref->{id}); |
286 | } | |
9ef3f143 | 287 | |
72d8bf49 DM |
288 | $sth->finish(); |
289 | ||
9ef3f143 | 290 | $self->{dbh}->do("DELETE FROM Object " . |
72d8bf49 | 291 | "WHERE Objectgroup_ID = ?", undef, $groupid); |
9ef3f143 | 292 | |
72d8bf49 DM |
293 | $self->{dbh}->commit; |
294 | }; | |
295 | if (my $err = $@) { | |
296 | $self->{dbh}->rollback; | |
9ef3f143 | 297 | die $err; |
72d8bf49 DM |
298 | } |
299 | ||
300 | return undef; | |
301 | } | |
302 | ||
45d3ef6f DC |
303 | sub load_group_attributes { |
304 | my ($self, $og) = @_; | |
305 | ||
306 | my $attribute_sth = $self->{dbh}->prepare("SELECT * FROM Objectgroup_Attributes WHERE Objectgroup_ID = ?"); | |
307 | $attribute_sth->execute($og->{id}); | |
308 | ||
309 | while (my $ref = $attribute_sth->fetchrow_hashref()) { | |
310 | $og->{and} = $ref->{value} if $ref->{name} eq 'and'; | |
311 | $og->{invert} = $ref->{value} if $ref->{name} eq 'invert'; | |
312 | } | |
313 | } | |
314 | ||
72d8bf49 DM |
315 | sub load_objectgroups { |
316 | my ($self, $class, $id) = @_; | |
9ef3f143 | 317 | |
72d8bf49 | 318 | my $sth; |
9ef3f143 DM |
319 | |
320 | defined($class) || die "undefined object class"; | |
321 | ||
45d3ef6f | 322 | $self->{dbh}->begin_work; |
9ef3f143 | 323 | |
72d8bf49 | 324 | my $arr_og = (); |
72d8bf49 | 325 | |
45d3ef6f DC |
326 | eval { |
327 | if (!(defined($id))) { | |
328 | $sth = $self->{dbh}->prepare( | |
329 | "SELECT * FROM Objectgroup where Class = ? ORDER BY name"); | |
330 | $sth->execute($class); | |
331 | ||
332 | } else { | |
333 | $sth = $self->{dbh}->prepare( | |
334 | "SELECT * FROM Objectgroup where Class like ? and id = ? " . | |
335 | "order by name"); | |
336 | $sth->execute($class,$id); | |
72d8bf49 | 337 | } |
9ef3f143 | 338 | |
45d3ef6f DC |
339 | while (my $ref = $sth->fetchrow_hashref()) { |
340 | my $og = PMG::RuleDB::Group->new($ref->{name}, $ref->{info}, | |
341 | $ref->{class}); | |
342 | $og->{id} = $ref->{id}; | |
343 | ||
344 | if ($class eq 'action') { | |
345 | my $objects = $self->load_group_objects($og->{id}); | |
346 | my $obj = @$objects[0]; | |
347 | defined($obj) || die "undefined action object: ERROR"; | |
348 | $og->{action} = $obj; | |
349 | } else { | |
350 | $self->load_group_attributes($og); | |
351 | } | |
352 | push @$arr_og, $og; | |
353 | } | |
354 | ||
355 | $sth->finish(); | |
356 | }; | |
357 | ||
358 | my $err = $@; | |
359 | ||
360 | $self->{dbh}->rollback; # finish transaction | |
361 | ||
362 | die $err if $err; | |
72d8bf49 DM |
363 | |
364 | return $arr_og; | |
365 | } | |
366 | ||
367 | sub get_object { | |
368 | my ($self, $otype) = @_; | |
369 | ||
8671ea1c SI |
370 | my $obj; |
371 | ||
372 | # FIXME: remove deprecated types and files with PMG 8.0 | |
373 | my $deprecated_types = { | |
374 | 4004 => "Attach", | |
375 | 4008 => "ReportSpam", | |
376 | 4999 => "Counter", | |
377 | }; | |
9ef3f143 | 378 | |
72d8bf49 DM |
379 | # WHO OBJECTS |
380 | if ($otype == PMG::RuleDB::Domain::otype()) { | |
381 | $obj = PMG::RuleDB::Domain->new(); | |
9ef3f143 | 382 | } |
72d8bf49 DM |
383 | elsif ($otype == PMG::RuleDB::ReceiverDomain::otype) { |
384 | $obj = PMG::RuleDB::ReceiverDomain->new(); | |
9ef3f143 | 385 | } |
72d8bf49 DM |
386 | elsif ($otype == PMG::RuleDB::WhoRegex::otype) { |
387 | $obj = PMG::RuleDB::WhoRegex->new(); | |
9ef3f143 | 388 | } |
72d8bf49 DM |
389 | elsif ($otype == PMG::RuleDB::ReceiverRegex::otype) { |
390 | $obj = PMG::RuleDB::ReceiverRegex->new(); | |
9ef3f143 | 391 | } |
72d8bf49 DM |
392 | elsif ($otype == PMG::RuleDB::EMail::otype) { |
393 | $obj = PMG::RuleDB::EMail->new(); | |
9ef3f143 | 394 | } |
72d8bf49 DM |
395 | elsif ($otype == PMG::RuleDB::Receiver::otype) { |
396 | $obj = PMG::RuleDB::Receiver->new(); | |
9ef3f143 | 397 | } |
72d8bf49 DM |
398 | elsif ($otype == PMG::RuleDB::IPAddress::otype) { |
399 | $obj = PMG::RuleDB::IPAddress->new(); | |
9ef3f143 | 400 | } |
72d8bf49 DM |
401 | elsif ($otype == PMG::RuleDB::IPNet::otype) { |
402 | $obj = PMG::RuleDB::IPNet->new(); | |
9ef3f143 | 403 | } |
10621236 DM |
404 | elsif ($otype == PMG::RuleDB::LDAP::otype) { |
405 | $obj = PMG::RuleDB::LDAP->new(); | |
406 | } | |
c712d3a2 DM |
407 | elsif ($otype == PMG::RuleDB::LDAPUser::otype) { |
408 | $obj = PMG::RuleDB::LDAPUser->new(); | |
409 | } | |
72d8bf49 DM |
410 | # WHEN OBJECTS |
411 | elsif ($otype == PMG::RuleDB::TimeFrame::otype) { | |
412 | $obj = PMG::RuleDB::TimeFrame->new(); | |
9ef3f143 | 413 | } |
72d8bf49 DM |
414 | # WHAT OBJECTS |
415 | elsif ($otype == PMG::RuleDB::Spam::otype) { | |
416 | $obj = PMG::RuleDB::Spam->new(); | |
417 | } | |
418 | elsif ($otype == PMG::RuleDB::Virus::otype) { | |
419 | $obj = PMG::RuleDB::Virus->new(); | |
420 | } | |
421 | elsif ($otype == PMG::RuleDB::MatchField::otype) { | |
422 | $obj = PMG::RuleDB::MatchField->new(); | |
423 | } | |
424 | elsif ($otype == PMG::RuleDB::MatchFilename::otype) { | |
425 | $obj = PMG::RuleDB::MatchFilename->new(); | |
426 | } | |
5e809f47 DC |
427 | elsif ($otype == PMG::RuleDB::MatchArchiveFilename::otype) { |
428 | $obj = PMG::RuleDB::MatchArchiveFilename->new(); | |
429 | } | |
72d8bf49 DM |
430 | elsif ($otype == PMG::RuleDB::ContentTypeFilter::otype) { |
431 | $obj = PMG::RuleDB::ContentTypeFilter->new(); | |
432 | } | |
c4741113 DM |
433 | elsif ($otype == PMG::RuleDB::ArchiveFilter::otype) { |
434 | $obj = PMG::RuleDB::ArchiveFilter->new(); | |
435 | } | |
72d8bf49 DM |
436 | # ACTION OBJECTS |
437 | elsif ($otype == PMG::RuleDB::ModField::otype) { | |
438 | $obj = PMG::RuleDB::ModField->new(); | |
439 | } | |
440 | elsif ($otype == PMG::RuleDB::Accept::otype()) { | |
441 | $obj = PMG::RuleDB::Accept->new(); | |
442 | } | |
443 | elsif ($otype == PMG::RuleDB::ReportSpam::otype()) { | |
444 | $obj = PMG::RuleDB::ReportSpam->new(); | |
445 | } | |
446 | elsif ($otype == PMG::RuleDB::Attach::otype) { | |
447 | $obj = PMG::RuleDB::Attach->new(); | |
448 | } | |
449 | elsif ($otype == PMG::RuleDB::Disclaimer::otype) { | |
450 | $obj = PMG::RuleDB::Disclaimer->new(); | |
451 | } | |
452 | elsif ($otype == PMG::RuleDB::BCC::otype) { | |
453 | $obj = PMG::RuleDB::BCC->new(); | |
454 | } | |
455 | elsif ($otype == PMG::RuleDB::Quarantine::otype) { | |
456 | $obj = PMG::RuleDB::Quarantine->new(); | |
457 | } | |
458 | elsif ($otype == PMG::RuleDB::Block::otype) { | |
459 | $obj = PMG::RuleDB::Block->new(); | |
460 | } | |
461 | elsif ($otype == PMG::RuleDB::Counter::otype) { | |
462 | $obj = PMG::RuleDB::Counter->new(); | |
463 | } | |
464 | elsif ($otype == PMG::RuleDB::Remove::otype) { | |
465 | $obj = PMG::RuleDB::Remove->new(); | |
466 | } | |
467 | elsif ($otype == PMG::RuleDB::Notify::otype) { | |
468 | $obj = PMG::RuleDB::Notify->new(); | |
469 | } | |
470 | else { | |
9ef3f143 | 471 | die "proxmox: unknown object type: ERROR"; |
72d8bf49 | 472 | } |
9ef3f143 | 473 | |
8671ea1c SI |
474 | if ( grep( $_ == $otype, keys %$deprecated_types)) { |
475 | syslog('warning', "proxmox: deprecated object of type %s found!", | |
476 | $deprecated_types->{$otype}); | |
477 | } | |
72d8bf49 DM |
478 | return $obj; |
479 | } | |
480 | ||
8671ea1c | 481 | # FIXME: remove with PMG 8.0 |
72d8bf49 DM |
482 | sub load_counters_data { |
483 | my ($self) = @_; | |
9ef3f143 | 484 | |
72d8bf49 DM |
485 | my $sth = $self->{dbh}->prepare( |
486 | "SELECT Object.id, Objectgroup.name, Object.Value, Objectgroup.info " . | |
9ef3f143 | 487 | "FROM Object, Objectgroup " . |
72d8bf49 DM |
488 | "WHERE objectgroup.id = object.objectgroup_id and ObjectType = ? " . |
489 | "order by Objectgroup.name, Value"); | |
490 | ||
491 | my @data; | |
492 | ||
493 | $sth->execute(PMG::RuleDB::Counter->otype()); | |
494 | ||
9ef3f143 | 495 | while (my $ref = $sth->fetchrow_hashref()) { |
72d8bf49 DM |
496 | my $tmp = [$ref->{id},$ref->{name},$ref->{value},$ref->{info}]; |
497 | push (@data, $tmp); | |
498 | } | |
9ef3f143 | 499 | |
72d8bf49 | 500 | $sth->finish(); |
9ef3f143 DM |
501 | |
502 | return @data; | |
72d8bf49 DM |
503 | } |
504 | ||
505 | sub load_object { | |
506 | my ($self, $objid) = @_; | |
9ef3f143 | 507 | |
72d8bf49 DM |
508 | my $value = ''; |
509 | ||
9ef3f143 | 510 | defined($objid) || die "undefined object id"; |
72d8bf49 DM |
511 | |
512 | my $sth = $self->{dbh}->prepare("SELECT * FROM Object where ID = ?"); | |
513 | $sth->execute($objid); | |
9ef3f143 | 514 | |
72d8bf49 DM |
515 | my $ref = $sth->fetchrow_hashref(); |
516 | ||
517 | $sth->finish(); | |
518 | ||
9ef3f143 DM |
519 | if (defined($ref->{'value'})) { |
520 | $value = $ref->{'value'}; | |
72d8bf49 DM |
521 | } |
522 | ||
9ef3f143 | 523 | if (!(defined($ref->{'objecttype'}) && |
72d8bf49 DM |
524 | defined($ref->{'objectgroup_id'}))) { |
525 | return undef; | |
526 | } | |
527 | ||
528 | my $ogroup = $ref->{'objectgroup_id'}; | |
9ef3f143 | 529 | |
72d8bf49 DM |
530 | my $otype = $ref->{'objecttype'}; |
531 | my $obj = $self->get_object($otype); | |
532 | ||
13a96624 | 533 | return $obj->load_attr($self, $objid, $ogroup, $value); |
72d8bf49 DM |
534 | } |
535 | ||
744483ce DM |
536 | sub load_object_full { |
537 | my ($self, $id, $gid, $exp_otype) = @_; | |
538 | ||
539 | my $obj = $self->load_object($id); | |
540 | die "object '$id' does not exists\n" if !defined($obj); | |
541 | ||
542 | my $otype = $obj->otype(); | |
543 | die "wrong object type ($otype != $exp_otype)\n" | |
544 | if defined($exp_otype) && $otype != $exp_otype; | |
545 | ||
546 | die "wrong object group ($obj->{ogroup} != $gid)\n" | |
547 | if $obj->{ogroup} != $gid; | |
548 | ||
549 | return $obj; | |
550 | } | |
551 | ||
72d8bf49 DM |
552 | sub load_group_by_name { |
553 | my ($self, $name) = @_; | |
554 | ||
4418cffc | 555 | $name = encode('UTF-8', $name); |
9ef3f143 | 556 | my $sth = $self->{dbh}->prepare("SELECT * FROM Objectgroup " . |
72d8bf49 DM |
557 | "WHERE name = ?"); |
558 | ||
559 | $sth->execute($name); | |
9ef3f143 | 560 | |
72d8bf49 DM |
561 | while (my $ref = $sth->fetchrow_hashref()) { |
562 | my $og = PMG::RuleDB::Group->new($ref->{name}, $ref->{info}, | |
563 | $ref->{class}); | |
564 | $og->{id} = $ref->{id}; | |
565 | ||
566 | $sth->finish(); | |
567 | ||
568 | if ($ref->{'class'} eq 'action') { | |
569 | my $objects = $self->load_group_objects($og->{id}); | |
570 | my $obj = @$objects[0]; | |
9ef3f143 | 571 | defined($obj) || die "undefined action object: ERROR"; |
72d8bf49 DM |
572 | $og->{action} = $obj; |
573 | } | |
9ef3f143 | 574 | |
72d8bf49 DM |
575 | return $og; |
576 | } | |
577 | ||
578 | $sth->finish(); | |
579 | ||
580 | return undef; | |
581 | } | |
582 | ||
583 | sub greylistexclusion_groupid { | |
584 | my ($self) = @_; | |
9ef3f143 | 585 | |
72d8bf49 | 586 | my $sth = $self->{dbh}->prepare( |
9ef3f143 DM |
587 | "select id from objectgroup where class='greylist' limit 1;"); |
588 | ||
72d8bf49 | 589 | $sth->execute(); |
9ef3f143 | 590 | |
72d8bf49 | 591 | my $ref = $sth->fetchrow_hashref(); |
9ef3f143 | 592 | |
72d8bf49 DM |
593 | return $ref->{id}; |
594 | } | |
595 | ||
596 | sub load_group_objects { | |
597 | my ($self, $ogid) = @_; | |
598 | ||
9ef3f143 | 599 | defined($ogid) || die "undefined group id: ERROR"; |
72d8bf49 | 600 | |
9ef3f143 DM |
601 | my $sth = $self->{dbh}->prepare( |
602 | "SELECT * FROM Object " . | |
72d8bf49 DM |
603 | "WHERE Objectgroup_ID = ? order by ObjectType,Value"); |
604 | ||
605 | my $objects = (); | |
606 | ||
607 | $sth->execute($ogid); | |
608 | ||
609 | while (my $ref = $sth->fetchrow_hashref()) { | |
610 | my $obj = $self->load_object($ref->{id}); | |
611 | push @$objects, $obj; | |
612 | } | |
613 | ||
614 | $sth->finish(); | |
615 | ||
616 | return $objects; | |
617 | } | |
618 | ||
619 | ||
620 | sub save_object { | |
621 | my ($self, $obj) = @_; | |
9ef3f143 | 622 | |
72d8bf49 DM |
623 | $obj->save($self); |
624 | ||
625 | return $obj->{id}; | |
626 | } | |
627 | ||
628 | sub group_add_object { | |
629 | my ($self, $group, $obj) = @_; | |
9ef3f143 | 630 | |
72d8bf49 | 631 | ($obj->oclass() eq $group->{class}) || |
9ef3f143 | 632 | die "wrong object class: ERROR"; |
72d8bf49 DM |
633 | |
634 | $obj->{ogroup} = $group->{id}; | |
9ef3f143 | 635 | |
72d8bf49 DM |
636 | $self->save_object($obj); |
637 | } | |
638 | ||
639 | sub delete_object { | |
640 | my ($self, $obj) = @_; | |
641 | ||
9ef3f143 | 642 | defined($obj->{id}) || die "undefined object id"; |
72d8bf49 DM |
643 | |
644 | eval { | |
645 | ||
646 | $self->{dbh}->begin_work; | |
647 | ||
9ef3f143 | 648 | $self->{dbh}->do("DELETE FROM Attribut " . |
72d8bf49 | 649 | "WHERE Object_ID = ?", undef, $obj->{id}); |
9ef3f143 DM |
650 | |
651 | $self->{dbh}->do("DELETE FROM Object " . | |
72d8bf49 DM |
652 | "WHERE ID = ?", |
653 | undef, $obj->{id}); | |
654 | ||
655 | $self->{dbh}->commit; | |
656 | }; | |
657 | if (my $err = $@) { | |
658 | $self->{dbh}->rollback; | |
659 | syslog('err', $err); | |
660 | return undef; | |
661 | } | |
662 | ||
663 | $obj->{id} = undef; | |
664 | ||
665 | return 1; | |
666 | } | |
667 | ||
66481ecf DC |
668 | sub update_rule_attributes { |
669 | my ($self, $rule) = @_; | |
670 | ||
671 | my $types = [qw(what when from to)]; | |
672 | my $attributes = [qw(and invert)]; | |
673 | ||
674 | for my $type ($types->@*) { | |
675 | for my $attribute ($attributes->@*) { | |
676 | my $prop = "$type-$attribute"; | |
677 | ||
678 | # only save the values if they're set to 1 | |
679 | if ($rule->{$prop}) { | |
680 | $self->{dbh}->do( | |
681 | "INSERT INTO Rule_Attributes (Rule_ID, Name, Value) " . | |
682 | "VALUES (?, ?, ?) ". | |
683 | "ON CONFLICT (Rule_ID, Name) DO UPDATE SET Value = ?", undef, | |
684 | $rule->{id}, $prop, $rule->{$prop}, $rule->{$prop}, | |
685 | ); | |
686 | } else { | |
687 | $self->{dbh}->do( | |
688 | "DELETE FROM Rule_Attributes " . | |
689 | "WHERE Rule_ID = ? AND Name = ?", undef, | |
690 | $rule->{id}, $prop, | |
691 | ); | |
692 | } | |
693 | } | |
694 | } | |
695 | } | |
696 | ||
72d8bf49 DM |
697 | sub save_rule { |
698 | my ($self, $rule) = @_; | |
699 | ||
9ef3f143 DM |
700 | defined($rule->{name}) || |
701 | die "undefined rule attribute - name: ERROR"; | |
702 | defined($rule->{priority}) || | |
703 | die "undefined rule attribute - priority: ERROR"; | |
704 | defined($rule->{active}) || | |
705 | die "undefined rule attribute - active: ERROR"; | |
706 | defined($rule->{direction}) || | |
707 | die "undefined rule attribute - direction: ERROR"; | |
72d8bf49 | 708 | |
4418cffc | 709 | my $rulename = encode('UTF-8', $rule->{name}); |
72d8bf49 | 710 | if (defined($rule->{id})) { |
66481ecf DC |
711 | $self->{dbh}->begin_work; |
712 | ||
713 | eval { | |
714 | $self->{dbh}->do( | |
715 | "UPDATE Rule " . | |
716 | "SET Name = ?, Priority = ?, Active = ?, Direction = ? " . | |
717 | "WHERE ID = ?", undef, | |
718 | $rulename, $rule->{priority}, $rule->{active}, | |
719 | $rule->{direction}, $rule->{id}); | |
72d8bf49 | 720 | |
66481ecf | 721 | $self->update_rule_attributes($rule); |
72d8bf49 | 722 | |
66481ecf DC |
723 | $self->{dbh}->commit; |
724 | }; | |
72d8bf49 | 725 | |
66481ecf DC |
726 | if (my $err = $@) { |
727 | $self->{dbh}->rollback; | |
728 | syslog('err', $err); | |
729 | return undef; | |
730 | } | |
72d8bf49 | 731 | } else { |
66481ecf | 732 | $self->{dbh}->begin_work; |
72d8bf49 | 733 | |
66481ecf DC |
734 | eval { |
735 | my $sth = $self->{dbh}->prepare( | |
736 | "INSERT INTO Rule (Name, Priority, Active, Direction) " . | |
737 | "VALUES (?, ?, ?, ?);"); | |
738 | ||
739 | $sth->execute($rulename, $rule->priority, $rule->active, | |
740 | $rule->direction); | |
741 | ||
742 | ||
743 | $rule->{id} = PMG::Utils::lastid($self->{dbh}, 'rule_id_seq'); | |
744 | ||
745 | $self->update_rule_attributes($rule); | |
746 | ||
747 | $self->{dbh}->commit; | |
748 | }; | |
72d8bf49 | 749 | |
66481ecf DC |
750 | if (my $err = $@) { |
751 | $self->{dbh}->rollback; | |
752 | syslog('err', $err); | |
753 | return undef; | |
754 | } | |
72d8bf49 DM |
755 | } |
756 | ||
66481ecf | 757 | return $rule->{id}; |
72d8bf49 DM |
758 | } |
759 | ||
760 | sub delete_rule { | |
761 | my ($self, $ruleid) = @_; | |
762 | ||
9ef3f143 | 763 | defined($ruleid) || die "undefined rule id: ERROR"; |
72d8bf49 DM |
764 | |
765 | eval { | |
766 | $self->{dbh}->begin_work; | |
767 | ||
9ef3f143 | 768 | $self->{dbh}->do("DELETE FROM Rule " . |
72d8bf49 | 769 | "WHERE ID = ?", undef, $ruleid); |
9ef3f143 | 770 | $self->{dbh}->do("DELETE FROM RuleGroup " . |
72d8bf49 | 771 | "WHERE Rule_ID = ?", undef, $ruleid); |
72d8bf49 DM |
772 | $self->{dbh}->commit; |
773 | }; | |
774 | if (my $err = $@) { | |
775 | $self->{dbh}->rollback; | |
776 | syslog('err', $err); | |
777 | return undef; | |
778 | } | |
779 | ||
780 | return 1; | |
781 | } | |
782 | ||
783 | sub delete_testrules { | |
784 | my ($self) = @_; | |
785 | ||
786 | eval { | |
787 | $self->{dbh}->begin_work; | |
788 | ||
789 | my $sth = $self->{dbh}->prepare("Select id FROM Rule " . | |
790 | "WHERE name = 'testrule'"); | |
791 | $sth->execute(); | |
792 | ||
793 | while(my $ref = $sth->fetchrow_hashref()) { | |
9ef3f143 | 794 | $self->{dbh}->do("DELETE FROM Rule " . |
72d8bf49 | 795 | "WHERE ID = ?", undef, $ref->{id}); |
9ef3f143 | 796 | $self->{dbh}->do("DELETE FROM RuleGroup " . |
72d8bf49 DM |
797 | "WHERE Rule_ID = ?", undef, $ref->{id}); |
798 | } | |
799 | $sth->finish(); | |
800 | ||
801 | $self->{dbh}->commit; | |
802 | }; | |
803 | if (my $err = $@) { | |
804 | $self->{dbh}->rollback; | |
9ef3f143 | 805 | die $err; |
72d8bf49 DM |
806 | } |
807 | ||
9ef3f143 | 808 | return 1; |
72d8bf49 DM |
809 | } |
810 | ||
0e022653 DM |
811 | my $grouptype_hash = { |
812 | from => 0, | |
813 | to => 1, | |
814 | when => 2, | |
815 | what => 3, | |
816 | action => 4, | |
817 | }; | |
818 | ||
72d8bf49 | 819 | sub rule_add_group { |
0e022653 DM |
820 | my ($self, $ruleid, $groupid, $gtype_str) = @_; |
821 | ||
822 | my $gtype = $grouptype_hash->{$gtype_str} // | |
823 | die "unknown group type '$gtype_str'\n"; | |
72d8bf49 | 824 | |
9ef3f143 DM |
825 | defined($ruleid) || die "undefined rule id: ERROR"; |
826 | defined($groupid) || die "undefined group id: ERROR"; | |
827 | defined($gtype) || die "undefined group type: ERROR"; | |
828 | ||
829 | $self->{dbh}->do("INSERT INTO RuleGroup " . | |
72d8bf49 | 830 | "(Objectgroup_ID, Rule_ID, Grouptype) " . |
9ef3f143 | 831 | "VALUES (?, ?, ?)", undef, |
72d8bf49 DM |
832 | $groupid, $ruleid, $gtype); |
833 | return 1; | |
834 | } | |
835 | ||
836 | sub rule_add_from_group { | |
837 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 838 | |
0e022653 | 839 | $self->rule_add_group($rule->{id}, $group->{id}, 'from'); |
72d8bf49 DM |
840 | } |
841 | ||
842 | sub rule_add_to_group { | |
843 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 844 | |
0e022653 | 845 | $self->rule_add_group($rule->{id}, $group->{id}, 'to'); |
72d8bf49 DM |
846 | } |
847 | ||
848 | sub rule_add_when_group { | |
849 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 850 | |
0e022653 | 851 | $self->rule_add_group($rule->{id}, $group->{id}, 'when'); |
72d8bf49 DM |
852 | } |
853 | ||
854 | sub rule_add_what_group { | |
855 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 856 | |
0e022653 | 857 | $self->rule_add_group($rule->{id}, $group->{id}, 'what'); |
72d8bf49 DM |
858 | } |
859 | ||
860 | sub rule_add_action { | |
861 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 862 | |
0e022653 | 863 | $self->rule_add_group($rule->{id}, $group->{id}, 'action'); |
72d8bf49 DM |
864 | } |
865 | ||
866 | sub rule_remove_group { | |
0e022653 DM |
867 | my ($self, $ruleid, $groupid, $gtype_str) = @_; |
868 | ||
869 | my $gtype = $grouptype_hash->{$gtype_str} // | |
870 | die "unknown group type '$gtype_str'\n"; | |
72d8bf49 | 871 | |
9ef3f143 DM |
872 | defined($ruleid) || die "undefined rule id: ERROR"; |
873 | defined($groupid) || die "undefined group id: ERROR"; | |
874 | defined($gtype) || die "undefined group type: ERROR"; | |
72d8bf49 | 875 | |
9ef3f143 | 876 | $self->{dbh}->do("DELETE FROM RuleGroup WHERE " . |
72d8bf49 DM |
877 | "Objectgroup_ID = ? and Rule_ID = ? and Grouptype = ?", |
878 | undef, $groupid, $ruleid, $gtype); | |
879 | return 1; | |
880 | } | |
881 | ||
66481ecf DC |
882 | sub load_rule_attributes { |
883 | my ($self, $rule) = @_; | |
884 | ||
885 | my $types = [qw(what when from to)]; | |
886 | my $attributes = [qw(and invert)]; | |
887 | ||
888 | my $attribute_sth = $self->{dbh}->prepare("SELECT * FROM Rule_Attributes WHERE Rule_ID = ?"); | |
889 | $attribute_sth->execute($rule->{id}); | |
890 | ||
891 | while (my $ref = $attribute_sth->fetchrow_hashref()) { | |
892 | if ($ref->{name} =~ m/^((?:what|when|from|to)-(?:and|invert))$/) { | |
893 | my $prop = $1; | |
894 | $rule->{$prop} = $ref->{value}; | |
895 | } | |
896 | } | |
897 | } | |
898 | ||
72d8bf49 DM |
899 | sub load_rule { |
900 | my ($self, $id) = @_; | |
9ef3f143 DM |
901 | |
902 | defined($id) || die "undefined id: ERROR"; | |
903 | ||
66481ecf | 904 | $self->{dbh}->begin_work; |
72d8bf49 | 905 | |
66481ecf | 906 | my $rule; |
72d8bf49 | 907 | |
66481ecf DC |
908 | eval { |
909 | my $sth = $self->{dbh}->prepare( | |
910 | "SELECT * FROM Rule where id = ? ORDER BY Priority DESC"); | |
72d8bf49 | 911 | |
66481ecf DC |
912 | $sth->execute($id); |
913 | ||
914 | my $ref = $sth->fetchrow_hashref(); | |
915 | die "rule '$id' does not exist\n" if !defined($ref); | |
9ef3f143 | 916 | |
66481ecf DC |
917 | $rule = PMG::RuleDB::Rule->new($ref->{name}, $ref->{priority}, |
918 | $ref->{active}, $ref->{direction}); | |
919 | $rule->{id} = $ref->{id}; | |
920 | ||
921 | $self->load_rule_attributes($rule); | |
922 | }; | |
923 | my $err = $@; | |
924 | ||
925 | $self->{dbh}->rollback; # finish transaction | |
926 | ||
927 | die $err if $err; | |
9ef3f143 DM |
928 | |
929 | return $rule; | |
72d8bf49 DM |
930 | } |
931 | ||
932 | sub load_rules { | |
933 | my ($self) = @_; | |
934 | ||
935 | my $sth = $self->{dbh}->prepare( | |
936 | "SELECT * FROM Rule ORDER BY Priority DESC"); | |
937 | ||
938 | my $rules = (); | |
939 | ||
940 | $sth->execute(); | |
9ef3f143 | 941 | |
72d8bf49 | 942 | while (my $ref = $sth->fetchrow_hashref()) { |
4418cffc SI |
943 | my $rulename = PMG::Utils::try_decode_utf8($ref->{name}); |
944 | my $rule = PMG::RuleDB::Rule->new($rulename, $ref->{priority}, | |
72d8bf49 DM |
945 | $ref->{active}, $ref->{direction}); |
946 | $rule->{id} = $ref->{id}; | |
947 | push @$rules, $rule; | |
948 | } | |
949 | ||
950 | $sth->finish(); | |
951 | ||
952 | return $rules; | |
953 | } | |
954 | ||
955 | ||
956 | ||
957 | 1; | |
958 | ||
959 | __END__ | |
960 | ||
961 | =head1 PMG::RuleDB | |
962 | ||
963 | The RuleDB Object manages the database connection and provides an interface to manipulate the database without SQL. A typical application first create a RuleDB object: | |
964 | ||
965 | use PMG::RuleDB; | |
966 | ||
967 | $ruledb = PMG::RuleDB->new(); | |
968 | ||
969 | =head2 Database Overview | |
970 | ||
971 | =head3 Rules | |
972 | ||
973 | Rules contains sets of Groups, grouped by classes (FROM, TO, WHEN, WHAT and ACTION). Each rule has an associated priority and and active/inactive marker. | |
974 | ||
975 | =head3 Groups | |
976 | ||
977 | A Group is a set of Objects. | |
978 | ||
979 | =head3 Objects | |
980 | ||
981 | Objects contains the filter data. | |
982 | ||
983 | =head3 Rule Semantics | |
984 | ||
985 | The classes have 'and' semantics. A rule matches if the checks in FROM, TO, WHEN and WHAT classes returns TRUE. | |
986 | ||
987 | Within a class the objects are or'ed together. | |
988 | ||
989 | =head2 Managing Rules | |
990 | ||
991 | =head3 $ruledb->load_rules() | |
992 | ||
993 | Returns an array of Rules containing all rules in the database. | |
994 | ||
995 | =head3 $ruledb->save_rule ($rule) | |
996 | ||
997 | One can use the following code to add a new rule to the database: | |
998 | ||
999 | my $rule = PMG::RuleDB::Rule->new ($name, $priority, $active); | |
1000 | $ruledb->save_rule ($rule); | |
1001 | ||
9ef3f143 | 1002 | You can also use save_rule() to commit changes back to the database. |
72d8bf49 DM |
1003 | |
1004 | =head3 $ruledb->delete_rule ($ruleid) | |
1005 | ||
1006 | Removes the rule from the database. | |
1007 | ||
1008 | =head3 $ruledb->rule_add_group ($rule, $og, $gtype) | |
1009 | ||
1010 | Add an object group to the rule. | |
1011 | ||
1012 | Possible values for $gtype are: | |
1013 | ||
0e022653 | 1014 | 'from' 'to', 'when', 'what', 'action' |
72d8bf49 DM |
1015 | |
1016 | =head3 $ruledb->rule_remove_group ($rule, $og, $gtype) | |
1017 | ||
1018 | Removes an object group from the rule. | |
1019 | ||
1020 | =head2 Managing Objects and Groups | |
1021 | ||
1022 | =head3 $ruledb->load_groups ($rule) | |
1023 | ||
1024 | Return all object groups belonging to a rule. Data is divided into separate arrays: | |
1025 | ||
9ef3f143 | 1026 | my ($from, $to, $when, $what, $action) = |
72d8bf49 DM |
1027 | $ruledb->load_groups($rule); |
1028 | ||
1029 | =head3 $ruledb->save_group ($og) | |
1030 | ||
1031 | This can be used to add or modify an Group. This code segemnt creates | |
1032 | a new object group: | |
1033 | ||
1034 | $og = PMG::RuleDB::Group->new ($name, $desc); | |
1035 | $ruledb->save_group ($og); | |
1036 | ||
1037 | ||
1038 | =head3 $ruledb->delete_group ($groupid) | |
1039 | ||
9ef3f143 DM |
1040 | Deletes the object group, all reference to the group and all objects |
1041 | belonging to this group from the Database. | |
72d8bf49 DM |
1042 | |
1043 | =head3 $ruledb->group_add_object ($og, $obj) | |
1044 | ||
1045 | Attach an object to an object group. | |
1046 | ||
1047 | =head3 $ruledb->save_object ($obj) | |
1048 | ||
9ef3f143 | 1049 | Save or update an object. This can be used to add new objects |
1359baef | 1050 | to the database (although group_add_object() is the preferred way): |
72d8bf49 DM |
1051 | |
1052 | $obj = PMG::RuleDB::EMail->new ('.*@mydomain.com'); | |
1053 | # we need to set the object group manually | |
1054 | $obj->ogroup ($group->id); | |
1055 | $ruledb->save_object ($obj); | |
9ef3f143 | 1056 | |
72d8bf49 DM |
1057 | |
1058 | =head3 $ruledb->delete_object ($obj) | |
1059 | ||
9ef3f143 | 1060 | Deletes the object, all references to the object and all object |
72d8bf49 | 1061 | attributes from the database. |