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
::MatchArchiveFilename
;
38 use PMG
::RuleDB
::Attach
;
39 use PMG
::RuleDB
::Disclaimer
;
41 use PMG
::RuleDB
::Quarantine
;
42 use PMG
::RuleDB
::Block
;
43 use PMG
::RuleDB
::Counter
;
44 use PMG
::RuleDB
::Notify
;
45 use PMG
::RuleDB
::Rule
;
46 use PMG
::RuleDB
::ContentTypeFilter
;
47 use PMG
::RuleDB
::ArchiveFilter
;
50 my ($type, $dbh) = @_;
52 $dbh = PMG
::DBTools
::open_ruledb
("Proxmox_ruledb") if !defined ($dbh);
54 my $self = bless { dbh
=> $dbh }, $type;
62 $self->{dbh
}->disconnect();
65 sub create_group_with_obj
{
66 my ($self, $obj, $name, $info) = @_;
71 defined($obj) || die "proxmox: undefined object";
78 $self->{dbh
}->begin_work;
80 $self->{dbh
}->do("INSERT INTO Objectgroup (Name, Info, Class) " .
81 "VALUES (?, ?, ?)", undef,
82 $name, $info, $obj->oclass());
84 my $lid = PMG
::Utils
::lastid
($self->{dbh
}, 'objectgroup_id_seq');
86 $og = PMG
::RuleDB
::Group-
>new($name, $info, $obj->oclass());
89 $obj->{ogroup
} = $lid;
90 $id = $obj->save($self, 1);
91 $obj->{id
} = $id; # just to be sure
96 $self->{dbh
}->rollback;
103 my ($self, $rule) = @_;
105 defined($rule->{id
}) || die "undefined rule id: ERROR";
107 my $sth = $self->{dbh
}->prepare(
108 "SELECT RuleGroup.Grouptype, Objectgroup.ID, " .
109 "Objectgroup.Name, Objectgroup.Info " .
110 "FROM Rulegroup, Objectgroup " .
111 "WHERE Rulegroup.Rule_ID = ? and " .
112 "Rulegroup.Objectgroup_ID = Objectgroup.ID " .
113 "ORDER BY RuleGroup.Grouptype");
117 $sth->execute($rule->{id
});
119 my ($from, $to, $when, $what, $action) = ([], [], [], [], []);
121 while (my $ref = $sth->fetchrow_hashref()) {
122 my $og = PMG
::RuleDB
::Group-
>new($ref->{name
}, $ref->{info
});
123 $og->{id
} = $ref->{id
};
125 if ($ref->{'grouptype'} == 0) { #from
127 } elsif ($ref->{'grouptype'} == 1) { # to
129 } elsif ($ref->{'grouptype'} == 2) { # when
131 } elsif ($ref->{'grouptype'} == 3) { # what
133 } elsif ($ref->{'grouptype'} == 4) { # action
134 my $objects = $self->load_group_objects($og->{id
});
135 my $obj = @$objects[0];
136 defined($obj) || die "undefined action object: ERROR";
137 $og->{action
} = $obj;
144 return ($from, $to, $when, $what, $action);
147 sub load_groups_by_name
{
148 my ($self, $rule) = @_;
150 my ($from, $to, $when, $what, $action) =
151 $self->load_groups($rule);
163 my ($self, $og) = @_;
165 defined($og->{name
}) ||
166 die "undefined group attribute - name: ERROR";
167 defined($og->{info
}) ||
168 die "undefined group attribute - info: ERROR";
169 defined($og->{class}) ||
170 die "undefined group attribute - class: ERROR";
172 if (defined($og->{id
})) {
174 $self->{dbh
}->do("UPDATE Objectgroup " .
175 "SET Name = ?, Info = ? " .
176 "WHERE ID = ?", undef,
177 $og->{name
}, $og->{info
}, $og->{id
});
182 my $sth = $self->{dbh
}->prepare(
183 "INSERT INTO Objectgroup (Name, Info, Class) " .
184 "VALUES (?, ?, ?);");
186 $sth->execute($og->name, $og->info, $og->class);
188 return $og->{id
} = PMG
::Utils
::lastid
($self->{dbh
}, 'objectgroup_id_seq');
195 my ($self, $groupid) = @_;
197 defined($groupid) || die "undefined group id: ERROR";
201 $self->{dbh
}->begin_work;
203 # test if group is used in rules
204 $self->{dbh
}->do("LOCK TABLE RuleGroup IN EXCLUSIVE MODE");
206 my $sth = $self->{dbh
}->prepare(
207 "SELECT Rule.Name as rulename, ObjectGroup.Name as groupname " .
208 "FROM RuleGroup, Rule, ObjectGroup WHERE " .
209 "ObjectGroup.ID = ? AND Objectgroup_ID = ObjectGroup.ID AND " .
210 "Rule_ID = Rule.ID");
212 $sth->execute($groupid);
214 if (my $ref = $sth->fetchrow_hashref()) {
215 die "Group '$ref->{groupname}' is used by rule '$ref->{rulename}' - unable to delete\n";
220 $self->{dbh
}->do("DELETE FROM ObjectGroup " .
221 "WHERE ID = ?", undef, $groupid);
223 $self->{dbh
}->do("DELETE FROM RuleGroup " .
224 "WHERE Objectgroup_ID = ?", undef, $groupid);
226 $sth = $self->{dbh
}->prepare("SELECT * FROM Object " .
227 "where Objectgroup_ID = ?");
228 $sth->execute($groupid);
230 while (my $ref = $sth->fetchrow_hashref()) {
231 $self->{dbh
}->do("DELETE FROM Attribut " .
232 "WHERE Object_ID = ?", undef, $ref->{id
});
237 $self->{dbh
}->do("DELETE FROM Object " .
238 "WHERE Objectgroup_ID = ?", undef, $groupid);
240 $self->{dbh
}->commit;
243 $self->{dbh
}->rollback;
250 sub load_objectgroups
{
251 my ($self, $class, $id) = @_;
255 defined($class) || die "undefined object class";
257 if (!(defined($id))) {
258 $sth = $self->{dbh
}->prepare(
259 "SELECT * FROM Objectgroup where Class = ? ORDER BY name");
260 $sth->execute($class);
263 $sth = $self->{dbh
}->prepare(
264 "SELECT * FROM Objectgroup where Class like ? and id = ? " .
266 $sth->execute($class,$id);
270 while (my $ref = $sth->fetchrow_hashref()) {
271 my $og = PMG
::RuleDB
::Group-
>new($ref->{name
}, $ref->{info
},
273 $og->{id
} = $ref->{id
};
275 if ($class eq 'action') {
276 my $objects = $self->load_group_objects($og->{id
});
277 my $obj = @$objects[0];
278 defined($obj) || die "undefined action object: ERROR";
279 $og->{action
} = $obj;
290 my ($self, $otype) = @_;
295 if ($otype == PMG
::RuleDB
::Domain
::otype
()) {
296 $obj = PMG
::RuleDB
::Domain-
>new();
298 elsif ($otype == PMG
::RuleDB
::ReceiverDomain
::otype
) {
299 $obj = PMG
::RuleDB
::ReceiverDomain-
>new();
301 elsif ($otype == PMG
::RuleDB
::WhoRegex
::otype
) {
302 $obj = PMG
::RuleDB
::WhoRegex-
>new();
304 elsif ($otype == PMG
::RuleDB
::ReceiverRegex
::otype
) {
305 $obj = PMG
::RuleDB
::ReceiverRegex-
>new();
307 elsif ($otype == PMG
::RuleDB
::EMail
::otype
) {
308 $obj = PMG
::RuleDB
::EMail-
>new();
310 elsif ($otype == PMG
::RuleDB
::Receiver
::otype
) {
311 $obj = PMG
::RuleDB
::Receiver-
>new();
313 elsif ($otype == PMG
::RuleDB
::IPAddress
::otype
) {
314 $obj = PMG
::RuleDB
::IPAddress-
>new();
316 elsif ($otype == PMG
::RuleDB
::IPNet
::otype
) {
317 $obj = PMG
::RuleDB
::IPNet-
>new();
319 elsif ($otype == PMG
::RuleDB
::LDAP
::otype
) {
320 $obj = PMG
::RuleDB
::LDAP-
>new();
322 elsif ($otype == PMG
::RuleDB
::LDAPUser
::otype
) {
323 $obj = PMG
::RuleDB
::LDAPUser-
>new();
326 elsif ($otype == PMG
::RuleDB
::TimeFrame
::otype
) {
327 $obj = PMG
::RuleDB
::TimeFrame-
>new();
330 elsif ($otype == PMG
::RuleDB
::Spam
::otype
) {
331 $obj = PMG
::RuleDB
::Spam-
>new();
333 elsif ($otype == PMG
::RuleDB
::Virus
::otype
) {
334 $obj = PMG
::RuleDB
::Virus-
>new();
336 elsif ($otype == PMG
::RuleDB
::MatchField
::otype
) {
337 $obj = PMG
::RuleDB
::MatchField-
>new();
339 elsif ($otype == PMG
::RuleDB
::MatchFilename
::otype
) {
340 $obj = PMG
::RuleDB
::MatchFilename-
>new();
342 elsif ($otype == PMG
::RuleDB
::MatchArchiveFilename
::otype
) {
343 $obj = PMG
::RuleDB
::MatchArchiveFilename-
>new();
345 elsif ($otype == PMG
::RuleDB
::ContentTypeFilter
::otype
) {
346 $obj = PMG
::RuleDB
::ContentTypeFilter-
>new();
348 elsif ($otype == PMG
::RuleDB
::ArchiveFilter
::otype
) {
349 $obj = PMG
::RuleDB
::ArchiveFilter-
>new();
352 elsif ($otype == PMG
::RuleDB
::ModField
::otype
) {
353 $obj = PMG
::RuleDB
::ModField-
>new();
355 elsif ($otype == PMG
::RuleDB
::Accept
::otype
()) {
356 $obj = PMG
::RuleDB
::Accept-
>new();
358 elsif ($otype == PMG
::RuleDB
::ReportSpam
::otype
()) {
359 $obj = PMG
::RuleDB
::ReportSpam-
>new();
361 elsif ($otype == PMG
::RuleDB
::Attach
::otype
) {
362 $obj = PMG
::RuleDB
::Attach-
>new();
364 elsif ($otype == PMG
::RuleDB
::Disclaimer
::otype
) {
365 $obj = PMG
::RuleDB
::Disclaimer-
>new();
367 elsif ($otype == PMG
::RuleDB
::BCC
::otype
) {
368 $obj = PMG
::RuleDB
::BCC-
>new();
370 elsif ($otype == PMG
::RuleDB
::Quarantine
::otype
) {
371 $obj = PMG
::RuleDB
::Quarantine-
>new();
373 elsif ($otype == PMG
::RuleDB
::Block
::otype
) {
374 $obj = PMG
::RuleDB
::Block-
>new();
376 elsif ($otype == PMG
::RuleDB
::Counter
::otype
) {
377 $obj = PMG
::RuleDB
::Counter-
>new();
379 elsif ($otype == PMG
::RuleDB
::Remove
::otype
) {
380 $obj = PMG
::RuleDB
::Remove-
>new();
382 elsif ($otype == PMG
::RuleDB
::Notify
::otype
) {
383 $obj = PMG
::RuleDB
::Notify-
>new();
386 die "proxmox: unknown object type: ERROR";
392 sub load_counters_data
{
395 my $sth = $self->{dbh
}->prepare(
396 "SELECT Object.id, Objectgroup.name, Object.Value, Objectgroup.info " .
397 "FROM Object, Objectgroup " .
398 "WHERE objectgroup.id = object.objectgroup_id and ObjectType = ? " .
399 "order by Objectgroup.name, Value");
403 $sth->execute(PMG
::RuleDB
::Counter-
>otype());
405 while (my $ref = $sth->fetchrow_hashref()) {
406 my $tmp = [$ref->{id
},$ref->{name
},$ref->{value
},$ref->{info
}];
416 my ($self, $objid) = @_;
420 defined($objid) || die "undefined object id";
422 my $sth = $self->{dbh
}->prepare("SELECT * FROM Object where ID = ?");
423 $sth->execute($objid);
425 my $ref = $sth->fetchrow_hashref();
429 if (defined($ref->{'value'})) {
430 $value = $ref->{'value'};
433 if (!(defined($ref->{'objecttype'}) &&
434 defined($ref->{'objectgroup_id'}))) {
438 my $ogroup = $ref->{'objectgroup_id'};
440 my $otype = $ref->{'objecttype'};
441 my $obj = $self->get_object($otype);
443 return $obj->load_attr($self, $objid, $ogroup, $value);
446 sub load_object_full
{
447 my ($self, $id, $gid, $exp_otype) = @_;
449 my $obj = $self->load_object($id);
450 die "object '$id' does not exists\n" if !defined($obj);
452 my $otype = $obj->otype();
453 die "wrong object type ($otype != $exp_otype)\n"
454 if defined($exp_otype) && $otype != $exp_otype;
456 die "wrong object group ($obj->{ogroup} != $gid)\n"
457 if $obj->{ogroup
} != $gid;
462 sub load_group_by_name
{
463 my ($self, $name) = @_;
465 my $sth = $self->{dbh
}->prepare("SELECT * FROM Objectgroup " .
468 $sth->execute($name);
470 while (my $ref = $sth->fetchrow_hashref()) {
471 my $og = PMG
::RuleDB
::Group-
>new($ref->{name
}, $ref->{info
},
473 $og->{id
} = $ref->{id
};
477 if ($ref->{'class'} eq 'action') {
478 my $objects = $self->load_group_objects($og->{id
});
479 my $obj = @$objects[0];
480 defined($obj) || die "undefined action object: ERROR";
481 $og->{action
} = $obj;
492 sub greylistexclusion_groupid
{
495 my $sth = $self->{dbh
}->prepare(
496 "select id from objectgroup where class='greylist' limit 1;");
500 my $ref = $sth->fetchrow_hashref();
505 sub load_group_objects
{
506 my ($self, $ogid) = @_;
508 defined($ogid) || die "undefined group id: ERROR";
510 my $sth = $self->{dbh
}->prepare(
511 "SELECT * FROM Object " .
512 "WHERE Objectgroup_ID = ? order by ObjectType,Value");
516 $sth->execute($ogid);
518 while (my $ref = $sth->fetchrow_hashref()) {
519 my $obj = $self->load_object($ref->{id
});
520 push @$objects, $obj;
530 my ($self, $obj) = @_;
537 sub group_add_object
{
538 my ($self, $group, $obj) = @_;
540 ($obj->oclass() eq $group->{class}) ||
541 die "wrong object class: ERROR";
543 $obj->{ogroup
} = $group->{id
};
545 $self->save_object($obj);
549 my ($self, $obj) = @_;
551 defined($obj->{id
}) || die "undefined object id";
555 $self->{dbh
}->begin_work;
557 $self->{dbh
}->do("DELETE FROM Attribut " .
558 "WHERE Object_ID = ?", undef, $obj->{id
});
560 $self->{dbh
}->do("DELETE FROM Object " .
564 $self->{dbh
}->commit;
567 $self->{dbh
}->rollback;
578 my ($self, $rule) = @_;
580 defined($rule->{name
}) ||
581 die "undefined rule attribute - name: ERROR";
582 defined($rule->{priority
}) ||
583 die "undefined rule attribute - priority: ERROR";
584 defined($rule->{active
}) ||
585 die "undefined rule attribute - active: ERROR";
586 defined($rule->{direction
}) ||
587 die "undefined rule attribute - direction: ERROR";
589 if (defined($rule->{id
})) {
593 "SET Name = ?, Priority = ?, Active = ?, Direction = ? " .
594 "WHERE ID = ?", undef,
595 $rule->{name
}, $rule->{priority
}, $rule->{active
},
596 $rule->{direction
}, $rule->{id
});
601 my $sth = $self->{dbh
}->prepare(
602 "INSERT INTO Rule (Name, Priority, Active, Direction) " .
603 "VALUES (?, ?, ?, ?);");
605 $sth->execute($rule->name, $rule->priority, $rule->active,
608 return $rule->{id
} = PMG
::Utils
::lastid
($self->{dbh
}, 'rule_id_seq');
615 my ($self, $ruleid) = @_;
617 defined($ruleid) || die "undefined rule id: ERROR";
620 $self->{dbh
}->begin_work;
622 $self->{dbh
}->do("DELETE FROM Rule " .
623 "WHERE ID = ?", undef, $ruleid);
624 $self->{dbh
}->do("DELETE FROM RuleGroup " .
625 "WHERE Rule_ID = ?", undef, $ruleid);
627 $self->{dbh
}->commit;
630 $self->{dbh
}->rollback;
638 sub delete_testrules
{
642 $self->{dbh
}->begin_work;
644 my $sth = $self->{dbh
}->prepare("Select id FROM Rule " .
645 "WHERE name = 'testrule'");
648 while(my $ref = $sth->fetchrow_hashref()) {
649 $self->{dbh
}->do("DELETE FROM Rule " .
650 "WHERE ID = ?", undef, $ref->{id
});
651 $self->{dbh
}->do("DELETE FROM RuleGroup " .
652 "WHERE Rule_ID = ?", undef, $ref->{id
});
656 $self->{dbh
}->commit;
659 $self->{dbh
}->rollback;
666 my $grouptype_hash = {
675 my ($self, $ruleid, $groupid, $gtype_str) = @_;
677 my $gtype = $grouptype_hash->{$gtype_str} //
678 die "unknown group type '$gtype_str'\n";
680 defined($ruleid) || die "undefined rule id: ERROR";
681 defined($groupid) || die "undefined group id: ERROR";
682 defined($gtype) || die "undefined group type: ERROR";
684 $self->{dbh
}->do("INSERT INTO RuleGroup " .
685 "(Objectgroup_ID, Rule_ID, Grouptype) " .
686 "VALUES (?, ?, ?)", undef,
687 $groupid, $ruleid, $gtype);
691 sub rule_add_from_group
{
692 my ($self, $rule, $group) = @_;
694 $self->rule_add_group($rule->{id
}, $group->{id
}, 'from');
697 sub rule_add_to_group
{
698 my ($self, $rule, $group) = @_;
700 $self->rule_add_group($rule->{id
}, $group->{id
}, 'to');
703 sub rule_add_when_group
{
704 my ($self, $rule, $group) = @_;
706 $self->rule_add_group($rule->{id
}, $group->{id
}, 'when');
709 sub rule_add_what_group
{
710 my ($self, $rule, $group) = @_;
712 $self->rule_add_group($rule->{id
}, $group->{id
}, 'what');
715 sub rule_add_action
{
716 my ($self, $rule, $group) = @_;
718 $self->rule_add_group($rule->{id
}, $group->{id
}, 'action');
721 sub rule_remove_group
{
722 my ($self, $ruleid, $groupid, $gtype_str) = @_;
724 my $gtype = $grouptype_hash->{$gtype_str} //
725 die "unknown group type '$gtype_str'\n";
727 defined($ruleid) || die "undefined rule id: ERROR";
728 defined($groupid) || die "undefined group id: ERROR";
729 defined($gtype) || die "undefined group type: ERROR";
731 $self->{dbh
}->do("DELETE FROM RuleGroup WHERE " .
732 "Objectgroup_ID = ? and Rule_ID = ? and Grouptype = ?",
733 undef, $groupid, $ruleid, $gtype);
738 my ($self, $id) = @_;
740 defined($id) || die "undefined id: ERROR";
742 my $sth = $self->{dbh
}->prepare(
743 "SELECT * FROM Rule where id = ? ORDER BY Priority DESC");
749 my $ref = $sth->fetchrow_hashref();
750 die "rule '$id' does not exist\n" if !defined($ref);
752 my $rule = PMG
::RuleDB
::Rule-
>new($ref->{name
}, $ref->{priority
},
753 $ref->{active
}, $ref->{direction
});
754 $rule->{id
} = $ref->{id
};
762 my $sth = $self->{dbh
}->prepare(
763 "SELECT * FROM Rule ORDER BY Priority DESC");
769 while (my $ref = $sth->fetchrow_hashref()) {
770 my $rule = PMG
::RuleDB
::Rule-
>new($ref->{name
}, $ref->{priority
},
771 $ref->{active
}, $ref->{direction
});
772 $rule->{id
} = $ref->{id
};
789 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:
793 $ruledb = PMG::RuleDB->new();
795 =head2 Database Overview
799 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.
803 A Group is a set of Objects.
807 Objects contains the filter data.
809 =head3 Rule Semantics
811 The classes have 'and' semantics. A rule matches if the checks in FROM, TO, WHEN and WHAT classes returns TRUE.
813 Within a class the objects are or'ed together.
815 =head2 Managing Rules
817 =head3 $ruledb->load_rules()
819 Returns an array of Rules containing all rules in the database.
821 =head3 $ruledb->save_rule ($rule)
823 One can use the following code to add a new rule to the database:
825 my $rule = PMG::RuleDB::Rule->new ($name, $priority, $active);
826 $ruledb->save_rule ($rule);
828 You can also use save_rule() to commit changes back to the database.
830 =head3 $ruledb->delete_rule ($ruleid)
832 Removes the rule from the database.
834 =head3 $ruledb->rule_add_group ($rule, $og, $gtype)
836 Add an object group to the rule.
838 Possible values for $gtype are:
840 'from' 'to', 'when', 'what', 'action'
842 =head3 $ruledb->rule_remove_group ($rule, $og, $gtype)
844 Removes an object group from the rule.
846 =head2 Managing Objects and Groups
848 =head3 $ruledb->load_groups ($rule)
850 Return all object groups belonging to a rule. Data is divided into separate arrays:
852 my ($from, $to, $when, $what, $action) =
853 $ruledb->load_groups($rule);
855 =head3 $ruledb->save_group ($og)
857 This can be used to add or modify an Group. This code segemnt creates
860 $og = PMG::RuleDB::Group->new ($name, $desc);
861 $ruledb->save_group ($og);
864 =head3 $ruledb->delete_group ($groupid)
866 Deletes the object group, all reference to the group and all objects
867 belonging to this group from the Database.
869 =head3 $ruledb->group_add_object ($og, $obj)
871 Attach an object to an object group.
873 =head3 $ruledb->save_object ($obj)
875 Save or update an object. This can be used to add new objects
876 to the database (although group_add_object() is the preferred way):
878 $obj = PMG::RuleDB::EMail->new ('.*@mydomain.com');
879 # we need to set the object group manually
880 $obj->ogroup ($group->id);
881 $ruledb->save_object ($obj);
884 =head3 $ruledb->delete_object ($obj)
886 Deletes the object, all references to the object and all object
887 attributes from the database.