14 use PMG
::RuleDB
::Group
;
16 #use Proxmox::Statistic;
17 use PMG
::RuleDB
::Object
;
18 use PMG
::RuleDB
::WhoRegex
;
19 use PMG
::RuleDB
::ReceiverRegex
;
20 use PMG
::RuleDB
::EMail
;
21 use PMG
::RuleDB
::Receiver
;
22 use PMG
::RuleDB
::IPAddress
;
23 use PMG
::RuleDB
::IPNet
;
24 use PMG
::RuleDB
::Domain
;
25 use PMG
::RuleDB
::ReceiverDomain
;
26 use PMG
::RuleDB
::LDAP
;
27 use PMG
::RuleDB
::LDAPUser
;
28 use PMG
::RuleDB
::TimeFrame
;
29 use PMG
::RuleDB
::Spam
;
30 use PMG
::RuleDB
::ReportSpam
;
31 use PMG
::RuleDB
::Virus
;
32 use PMG
::RuleDB
::Accept
;
33 use PMG
::RuleDB
::Remove
;
34 use PMG
::RuleDB
::ModField
;
35 use PMG
::RuleDB
::MatchField
;
36 use PMG
::RuleDB
::MatchFilename
;
37 use PMG
::RuleDB
::Attach
;
38 use PMG
::RuleDB
::Disclaimer
;
40 use PMG
::RuleDB
::Quarantine
;
41 use PMG
::RuleDB
::Block
;
42 use PMG
::RuleDB
::Counter
;
43 use PMG
::RuleDB
::Notify
;
44 use PMG
::RuleDB
::Rule
;
45 use PMG
::RuleDB
::ContentTypeFilter
;
46 use PMG
::RuleDB
::ArchiveFilter
;
49 my ($type, $dbh) = @_;
51 $dbh = PMG
::DBTools
::open_ruledb
("Proxmox_ruledb") if !defined ($dbh);
53 my $self = bless { dbh
=> $dbh }, $type;
61 $self->{dbh
}->disconnect();
64 sub create_group_with_obj
{
65 my ($self, $obj, $name, $info) = @_;
70 defined($obj) || die "proxmox: undefined object";
77 $self->{dbh
}->begin_work;
79 $self->{dbh
}->do("INSERT INTO Objectgroup (Name, Info, Class) " .
80 "VALUES (?, ?, ?)", undef,
81 $name, $info, $obj->oclass());
83 my $lid = PMG
::Utils
::lastid
($self->{dbh
}, 'objectgroup_id_seq');
85 $og = PMG
::RuleDB
::Group-
>new($name, $info, $obj->oclass());
88 $obj->{ogroup
} = $lid;
89 $id = $obj->save($self, 1);
90 $obj->{id
} = $id; # just to be sure
95 $self->{dbh
}->rollback;
102 my ($self, $rule) = @_;
104 defined($rule->{id
}) || die "undefined rule id: ERROR";
106 my $sth = $self->{dbh
}->prepare(
107 "SELECT RuleGroup.Grouptype, Objectgroup.ID, " .
108 "Objectgroup.Name, Objectgroup.Info " .
109 "FROM Rulegroup, Objectgroup " .
110 "WHERE Rulegroup.Rule_ID = ? and " .
111 "Rulegroup.Objectgroup_ID = Objectgroup.ID " .
112 "ORDER BY RuleGroup.Grouptype");
116 $sth->execute($rule->{id
});
118 my ($from, $to, $when, $what, $action) = ([], [], [], [], []);
120 while (my $ref = $sth->fetchrow_hashref()) {
121 my $og = PMG
::RuleDB
::Group-
>new($ref->{name
}, $ref->{info
});
122 $og->{id
} = $ref->{id
};
124 if ($ref->{'grouptype'} == 0) { #from
126 } elsif ($ref->{'grouptype'} == 1) { # to
128 } elsif ($ref->{'grouptype'} == 2) { # when
130 } elsif ($ref->{'grouptype'} == 3) { # what
132 } elsif ($ref->{'grouptype'} == 4) { # action
133 my $objects = $self->load_group_objects($og->{id
});
134 my $obj = @$objects[0];
135 defined($obj) || die "undefined action object: ERROR";
136 $og->{action
} = $obj;
143 return ($from, $to, $when, $what, $action);
146 sub load_groups_by_name
{
147 my ($self, $rule) = @_;
149 my ($from, $to, $when, $what, $action) =
150 $self->load_groups($rule);
162 my ($self, $og) = @_;
164 defined($og->{name
}) ||
165 die "undefined group attribute - name: ERROR";
166 defined($og->{info
}) ||
167 die "undefined group attribute - info: ERROR";
168 defined($og->{class}) ||
169 die "undefined group attribute - class: ERROR";
171 if (defined($og->{id
})) {
173 $self->{dbh
}->do("UPDATE Objectgroup " .
174 "SET Name = ?, Info = ? " .
175 "WHERE ID = ?", undef,
176 $og->{name
}, $og->{info
}, $og->{id
});
181 my $sth = $self->{dbh
}->prepare(
182 "INSERT INTO Objectgroup (Name, Info, Class) " .
183 "VALUES (?, ?, ?);");
185 $sth->execute($og->name, $og->info, $og->class);
187 return $og->{id
} = PMG
::Utils
::lastid
($self->{dbh
}, 'objectgroup_id_seq');
194 my ($self, $groupid) = @_;
196 defined($groupid) || die "undefined group id: ERROR";
200 $self->{dbh
}->begin_work;
202 # test if group is used in rules
203 $self->{dbh
}->do("LOCK TABLE RuleGroup IN EXCLUSIVE MODE");
205 my $sth = $self->{dbh
}->prepare(
206 "SELECT Rule.Name as rulename, ObjectGroup.Name as groupname " .
207 "FROM RuleGroup, Rule, ObjectGroup WHERE " .
208 "ObjectGroup.ID = ? AND Objectgroup_ID = ObjectGroup.ID AND " .
209 "Rule_ID = Rule.ID");
211 $sth->execute($groupid);
213 if (my $ref = $sth->fetchrow_hashref()) {
214 die "Group '$ref->{groupname}' is used by rule '$ref->{rulename}' - unable to delete\n";
219 $self->{dbh
}->do("DELETE FROM ObjectGroup " .
220 "WHERE ID = ?", undef, $groupid);
222 $self->{dbh
}->do("DELETE FROM RuleGroup " .
223 "WHERE Objectgroup_ID = ?", undef, $groupid);
225 $sth = $self->{dbh
}->prepare("SELECT * FROM Object " .
226 "where Objectgroup_ID = ?");
227 $sth->execute($groupid);
229 while (my $ref = $sth->fetchrow_hashref()) {
230 $self->{dbh
}->do("DELETE FROM Attribut " .
231 "WHERE Object_ID = ?", undef, $ref->{id
});
236 $self->{dbh
}->do("DELETE FROM Object " .
237 "WHERE Objectgroup_ID = ?", undef, $groupid);
239 $self->{dbh
}->commit;
242 $self->{dbh
}->rollback;
249 sub load_objectgroups
{
250 my ($self, $class, $id) = @_;
254 defined($class) || die "undefined object class";
256 if (!(defined($id))) {
257 $sth = $self->{dbh
}->prepare(
258 "SELECT * FROM Objectgroup where Class = ? ORDER BY name");
259 $sth->execute($class);
262 $sth = $self->{dbh
}->prepare(
263 "SELECT * FROM Objectgroup where Class like ? and id = ? " .
265 $sth->execute($class,$id);
269 while (my $ref = $sth->fetchrow_hashref()) {
270 my $og = PMG
::RuleDB
::Group-
>new($ref->{name
}, $ref->{info
},
272 $og->{id
} = $ref->{id
};
274 if ($class eq 'action') {
275 my $objects = $self->load_group_objects($og->{id
});
276 my $obj = @$objects[0];
277 defined($obj) || die "undefined action object: ERROR";
278 $og->{action
} = $obj;
289 my ($self, $otype) = @_;
294 if ($otype == PMG
::RuleDB
::Domain
::otype
()) {
295 $obj = PMG
::RuleDB
::Domain-
>new();
297 elsif ($otype == PMG
::RuleDB
::ReceiverDomain
::otype
) {
298 $obj = PMG
::RuleDB
::ReceiverDomain-
>new();
300 elsif ($otype == PMG
::RuleDB
::WhoRegex
::otype
) {
301 $obj = PMG
::RuleDB
::WhoRegex-
>new();
303 elsif ($otype == PMG
::RuleDB
::ReceiverRegex
::otype
) {
304 $obj = PMG
::RuleDB
::ReceiverRegex-
>new();
306 elsif ($otype == PMG
::RuleDB
::EMail
::otype
) {
307 $obj = PMG
::RuleDB
::EMail-
>new();
309 elsif ($otype == PMG
::RuleDB
::Receiver
::otype
) {
310 $obj = PMG
::RuleDB
::Receiver-
>new();
312 elsif ($otype == PMG
::RuleDB
::IPAddress
::otype
) {
313 $obj = PMG
::RuleDB
::IPAddress-
>new();
315 elsif ($otype == PMG
::RuleDB
::IPNet
::otype
) {
316 $obj = PMG
::RuleDB
::IPNet-
>new();
318 elsif ($otype == PMG
::RuleDB
::LDAP
::otype
) {
319 $obj = PMG
::RuleDB
::LDAP-
>new();
321 elsif ($otype == PMG
::RuleDB
::LDAPUser
::otype
) {
322 $obj = PMG
::RuleDB
::LDAPUser-
>new();
325 elsif ($otype == PMG
::RuleDB
::TimeFrame
::otype
) {
326 $obj = PMG
::RuleDB
::TimeFrame-
>new();
329 elsif ($otype == PMG
::RuleDB
::Spam
::otype
) {
330 $obj = PMG
::RuleDB
::Spam-
>new();
332 elsif ($otype == PMG
::RuleDB
::Virus
::otype
) {
333 $obj = PMG
::RuleDB
::Virus-
>new();
335 elsif ($otype == PMG
::RuleDB
::MatchField
::otype
) {
336 $obj = PMG
::RuleDB
::MatchField-
>new();
338 elsif ($otype == PMG
::RuleDB
::MatchFilename
::otype
) {
339 $obj = PMG
::RuleDB
::MatchFilename-
>new();
341 elsif ($otype == PMG
::RuleDB
::ContentTypeFilter
::otype
) {
342 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new();
344 elsif ($otype == PMG
::RuleDB
::ArchiveFilter
::otype
) {
345 $obj = PMG
::RuleDB
::ArchiveFilter-
>new();
348 elsif ($otype == PMG
::RuleDB
::ModField
::otype
) {
349 $obj = PMG
::RuleDB
::ModField-
>new();
351 elsif ($otype == PMG
::RuleDB
::Accept
::otype
()) {
352 $obj = PMG
::RuleDB
::Accept-
>new();
354 elsif ($otype == PMG
::RuleDB
::ReportSpam
::otype
()) {
355 $obj = PMG
::RuleDB
::ReportSpam-
>new();
357 elsif ($otype == PMG
::RuleDB
::Attach
::otype
) {
358 $obj = PMG
::RuleDB
::Attach-
>new();
360 elsif ($otype == PMG
::RuleDB
::Disclaimer
::otype
) {
361 $obj = PMG
::RuleDB
::Disclaimer-
>new();
363 elsif ($otype == PMG
::RuleDB
::BCC
::otype
) {
364 $obj = PMG
::RuleDB
::BCC-
>new();
366 elsif ($otype == PMG
::RuleDB
::Quarantine
::otype
) {
367 $obj = PMG
::RuleDB
::Quarantine-
>new();
369 elsif ($otype == PMG
::RuleDB
::Block
::otype
) {
370 $obj = PMG
::RuleDB
::Block-
>new();
372 elsif ($otype == PMG
::RuleDB
::Counter
::otype
) {
373 $obj = PMG
::RuleDB
::Counter-
>new();
375 elsif ($otype == PMG
::RuleDB
::Remove
::otype
) {
376 $obj = PMG
::RuleDB
::Remove-
>new();
378 elsif ($otype == PMG
::RuleDB
::Notify
::otype
) {
379 $obj = PMG
::RuleDB
::Notify-
>new();
382 die "proxmox: unknown object type: ERROR";
388 sub load_counters_data
{
391 my $sth = $self->{dbh
}->prepare(
392 "SELECT Object.id, Objectgroup.name, Object.Value, Objectgroup.info " .
393 "FROM Object, Objectgroup " .
394 "WHERE objectgroup.id = object.objectgroup_id and ObjectType = ? " .
395 "order by Objectgroup.name, Value");
399 $sth->execute(PMG
::RuleDB
::Counter-
>otype());
401 while (my $ref = $sth->fetchrow_hashref()) {
402 my $tmp = [$ref->{id
},$ref->{name
},$ref->{value
},$ref->{info
}];
412 my ($self, $objid) = @_;
416 defined($objid) || die "undefined object id";
418 my $sth = $self->{dbh
}->prepare("SELECT * FROM Object where ID = ?");
419 $sth->execute($objid);
421 my $ref = $sth->fetchrow_hashref();
425 if (defined($ref->{'value'})) {
426 $value = $ref->{'value'};
429 if (!(defined($ref->{'objecttype'}) &&
430 defined($ref->{'objectgroup_id'}))) {
434 my $ogroup = $ref->{'objectgroup_id'};
436 my $otype = $ref->{'objecttype'};
437 my $obj = $self->get_object($otype);
439 $obj->load_attr($self, $objid, $ogroup, $value);
442 sub load_object_full
{
443 my ($self, $id, $gid, $exp_otype) = @_;
445 my $obj = $self->load_object($id);
446 die "object '$id' does not exists\n" if !defined($obj);
448 my $otype = $obj->otype();
449 die "wrong object type ($otype != $exp_otype)\n"
450 if defined($exp_otype) && $otype != $exp_otype;
452 die "wrong object group ($obj->{ogroup} != $gid)\n"
453 if $obj->{ogroup
} != $gid;
458 sub load_group_by_name
{
459 my ($self, $name) = @_;
461 my $sth = $self->{dbh
}->prepare("SELECT * FROM Objectgroup " .
464 $sth->execute($name);
466 while (my $ref = $sth->fetchrow_hashref()) {
467 my $og = PMG
::RuleDB
::Group-
>new($ref->{name
}, $ref->{info
},
469 $og->{id
} = $ref->{id
};
473 if ($ref->{'class'} eq 'action') {
474 my $objects = $self->load_group_objects($og->{id
});
475 my $obj = @$objects[0];
476 defined($obj) || die "undefined action object: ERROR";
477 $og->{action
} = $obj;
488 sub greylistexclusion_groupid
{
491 my $sth = $self->{dbh
}->prepare(
492 "select id from objectgroup where class='greylist' limit 1;");
496 my $ref = $sth->fetchrow_hashref();
501 sub load_group_objects
{
502 my ($self, $ogid) = @_;
504 defined($ogid) || die "undefined group id: ERROR";
506 my $sth = $self->{dbh
}->prepare(
507 "SELECT * FROM Object " .
508 "WHERE Objectgroup_ID = ? order by ObjectType,Value");
512 $sth->execute($ogid);
514 while (my $ref = $sth->fetchrow_hashref()) {
515 my $obj = $self->load_object($ref->{id
});
516 push @$objects, $obj;
526 my ($self, $obj) = @_;
533 sub group_add_object
{
534 my ($self, $group, $obj) = @_;
536 ($obj->oclass() eq $group->{class}) ||
537 die "wrong object class: ERROR";
539 $obj->{ogroup
} = $group->{id
};
541 $self->save_object($obj);
545 my ($self, $obj) = @_;
547 defined($obj->{id
}) || die "undefined object id";
551 $self->{dbh
}->begin_work;
553 $self->{dbh
}->do("DELETE FROM Attribut " .
554 "WHERE Object_ID = ?", undef, $obj->{id
});
556 $self->{dbh
}->do("DELETE FROM Object " .
560 $self->{dbh
}->commit;
563 $self->{dbh
}->rollback;
574 my ($self, $rule) = @_;
576 defined($rule->{name
}) ||
577 die "undefined rule attribute - name: ERROR";
578 defined($rule->{priority
}) ||
579 die "undefined rule attribute - priority: ERROR";
580 defined($rule->{active
}) ||
581 die "undefined rule attribute - active: ERROR";
582 defined($rule->{direction
}) ||
583 die "undefined rule attribute - direction: ERROR";
585 if (defined($rule->{id
})) {
589 "SET Name = ?, Priority = ?, Active = ?, Direction = ? " .
590 "WHERE ID = ?", undef,
591 $rule->{name
}, $rule->{priority
}, $rule->{active
},
592 $rule->{direction
}, $rule->{id
});
597 my $sth = $self->{dbh
}->prepare(
598 "INSERT INTO Rule (Name, Priority, Active, Direction) " .
599 "VALUES (?, ?, ?, ?);");
601 $sth->execute($rule->name, $rule->priority, $rule->active,
604 return $rule->{id
} = PMG
::Utils
::lastid
($self->{dbh
}, 'rule_id_seq');
611 my ($self, $ruleid) = @_;
613 defined($ruleid) || die "undefined rule id: ERROR";
616 $self->{dbh
}->begin_work;
618 $self->{dbh
}->do("DELETE FROM Rule " .
619 "WHERE ID = ?", undef, $ruleid);
620 $self->{dbh
}->do("DELETE FROM RuleGroup " .
621 "WHERE Rule_ID = ?", undef, $ruleid);
623 $self->{dbh
}->commit;
626 $self->{dbh
}->rollback;
634 sub delete_testrules
{
638 $self->{dbh
}->begin_work;
640 my $sth = $self->{dbh
}->prepare("Select id FROM Rule " .
641 "WHERE name = 'testrule'");
644 while(my $ref = $sth->fetchrow_hashref()) {
645 $self->{dbh
}->do("DELETE FROM Rule " .
646 "WHERE ID = ?", undef, $ref->{id
});
647 $self->{dbh
}->do("DELETE FROM RuleGroup " .
648 "WHERE Rule_ID = ?", undef, $ref->{id
});
652 $self->{dbh
}->commit;
655 $self->{dbh
}->rollback;
662 my $grouptype_hash = {
671 my ($self, $ruleid, $groupid, $gtype_str) = @_;
673 my $gtype = $grouptype_hash->{$gtype_str} //
674 die "unknown group type '$gtype_str'\n";
676 defined($ruleid) || die "undefined rule id: ERROR";
677 defined($groupid) || die "undefined group id: ERROR";
678 defined($gtype) || die "undefined group type: ERROR";
680 $self->{dbh
}->do("INSERT INTO RuleGroup " .
681 "(Objectgroup_ID, Rule_ID, Grouptype) " .
682 "VALUES (?, ?, ?)", undef,
683 $groupid, $ruleid, $gtype);
687 sub rule_add_from_group
{
688 my ($self, $rule, $group) = @_;
690 $self->rule_add_group($rule->{id
}, $group->{id
}, 'from');
693 sub rule_add_to_group
{
694 my ($self, $rule, $group) = @_;
696 $self->rule_add_group($rule->{id
}, $group->{id
}, 'to');
699 sub rule_add_when_group
{
700 my ($self, $rule, $group) = @_;
702 $self->rule_add_group($rule->{id
}, $group->{id
}, 'when');
705 sub rule_add_what_group
{
706 my ($self, $rule, $group) = @_;
708 $self->rule_add_group($rule->{id
}, $group->{id
}, 'what');
711 sub rule_add_action
{
712 my ($self, $rule, $group) = @_;
714 $self->rule_add_group($rule->{id
}, $group->{id
}, 'action');
717 sub rule_remove_group
{
718 my ($self, $ruleid, $groupid, $gtype_str) = @_;
720 my $gtype = $grouptype_hash->{$gtype_str} //
721 die "unknown group type '$gtype_str'\n";
723 defined($ruleid) || die "undefined rule id: ERROR";
724 defined($groupid) || die "undefined group id: ERROR";
725 defined($gtype) || die "undefined group type: ERROR";
727 $self->{dbh
}->do("DELETE FROM RuleGroup WHERE " .
728 "Objectgroup_ID = ? and Rule_ID = ? and Grouptype = ?",
729 undef, $groupid, $ruleid, $gtype);
734 my ($self, $id) = @_;
736 defined($id) || die "undefined id: ERROR";
738 my $sth = $self->{dbh
}->prepare(
739 "SELECT * FROM Rule where id = ? ORDER BY Priority DESC");
745 my $ref = $sth->fetchrow_hashref();
746 die "rule '$id' does not exist\n" if !defined($ref);
748 my $rule = PMG
::RuleDB
::Rule-
>new($ref->{name
}, $ref->{priority
},
749 $ref->{active
}, $ref->{direction
});
750 $rule->{id
} = $ref->{id
};
758 my $sth = $self->{dbh
}->prepare(
759 "SELECT * FROM Rule ORDER BY Priority DESC");
765 while (my $ref = $sth->fetchrow_hashref()) {
766 my $rule = PMG
::RuleDB
::Rule-
>new($ref->{name
}, $ref->{priority
},
767 $ref->{active
}, $ref->{direction
});
768 $rule->{id
} = $ref->{id
};
785 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:
789 $ruledb = PMG::RuleDB->new();
791 =head2 Database Overview
795 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.
799 A Group is a set of Objects.
803 Objects contains the filter data.
805 =head3 Rule Semantics
807 The classes have 'and' semantics. A rule matches if the checks in FROM, TO, WHEN and WHAT classes returns TRUE.
809 Within a class the objects are or'ed together.
811 =head2 Managing Rules
813 =head3 $ruledb->load_rules()
815 Returns an array of Rules containing all rules in the database.
817 =head3 $ruledb->save_rule ($rule)
819 One can use the following code to add a new rule to the database:
821 my $rule = PMG::RuleDB::Rule->new ($name, $priority, $active);
822 $ruledb->save_rule ($rule);
824 You can also use save_rule() to commit changes back to the database.
826 =head3 $ruledb->delete_rule ($ruleid)
828 Removes the rule from the database.
830 =head3 $ruledb->rule_add_group ($rule, $og, $gtype)
832 Add an object group to the rule.
834 Possible values for $gtype are:
836 'from' 'to', 'when', 'what', 'action'
838 =head3 $ruledb->rule_remove_group ($rule, $og, $gtype)
840 Removes an object group from the rule.
842 =head2 Managing Objects and Groups
844 =head3 $ruledb->load_groups ($rule)
846 Return all object groups belonging to a rule. Data is divided into separate arrays:
848 my ($from, $to, $when, $what, $action) =
849 $ruledb->load_groups($rule);
851 =head3 $ruledb->save_group ($og)
853 This can be used to add or modify an Group. This code segemnt creates
856 $og = PMG::RuleDB::Group->new ($name, $desc);
857 $ruledb->save_group ($og);
860 =head3 $ruledb->delete_group ($groupid)
862 Deletes the object group, all reference to the group and all objects
863 belonging to this group from the Database.
865 =head3 $ruledb->group_add_object ($og, $obj)
867 Attach an object to an object group.
869 =head3 $ruledb->save_object ($obj)
871 Save or update an object. This can be used to add new objects
872 to the database (although group_add_object() is the prefered way):
874 $obj = PMG::RuleDB::EMail->new ('.*@mydomain.com');
875 # we need to set the object group manually
876 $obj->ogroup ($group->id);
877 $ruledb->save_object ($obj);
880 =head3 $ruledb->delete_object ($obj)
882 Deletes the object, all references to the object and all object
883 attributes from the database.