File Coverage

blib/lib/Mail/MtPolicyd/Plugin/SqlList.pm
Criterion Covered Total %
statement 30 32 93.7
branch 9 12 75.0
condition 1 3 33.3
subroutine 6 6 100.0
pod 1 1 100.0
total 47 54 87.0


line stmt bran cond sub pod time code
1             package Mail::MtPolicyd::Plugin::SqlList;
2              
3 2     2   1770 use Moose;
  2         4  
  2         13  
4 2     2   8661 use namespace::autoclean;
  2         2  
  2         17  
5              
6             our $VERSION = '2.01'; # VERSION
7             # ABSTRACT: mtpolicyd plugin for accessing a SQL white/black/access list
8              
9             extends 'Mail::MtPolicyd::Plugin';
10             with 'Mail::MtPolicyd::Plugin::Role::Scoring';
11             with 'Mail::MtPolicyd::Plugin::Role::UserConfig' => {
12             'uc_attributes' => [ 'enabled' ],
13             };
14              
15 2     2   194 use Mail::MtPolicyd::Plugin::Result;
  2         4  
  2         591  
16              
17              
18             has 'enabled' => ( is => 'rw', isa => 'Str', default => 'on' );
19              
20             has 'sql_query' => (
21             is => 'rw', isa => 'Str',
22             default => 'SELECT client_ip FROM whitelist WHERE client_ip=INET_ATON(?)',
23             );
24              
25             has 'score' => ( is => 'rw', isa => 'Maybe[Num]' );
26             has 'match_action' => ( is => 'rw', isa => 'Maybe[Str]' );
27             has 'not_match_action' => ( is => 'rw', isa => 'Maybe[Str]' );
28              
29             with 'Mail::MtPolicyd::Role::Connection' => {
30             name => 'db',
31             type => 'Sql',
32             };
33             with 'Mail::MtPolicyd::Plugin::Role::SqlUtils';
34              
35             sub _query_db {
36 4     4   3 my ( $self, $ip ) = @_;
37 4         101 return $self->execute_sql($self->sql_query, $ip)->fetchrow_array;
38             }
39              
40             sub run {
41 5     5 1 721 my ( $self, $r ) = @_;
42 5         179 my $ip = $r->attr('client_address');
43 5         132 my $session = $r->session;
44 5         5 my $config;
45              
46 5 100       16 if( $self->get_uc( $session, 'enabled') eq 'off' ) {
47 1         5 return;
48             }
49              
50 4 50       6 if( ! defined $ip) {
51 0         0 $self->log($r, 'no attribute \'client_address\' in request');
52 0         0 return;
53             }
54              
55             my $value = $r->do_cached( $self->name.'-result',
56 4     4   91 sub { $self->_query_db($ip) } );
  4         17  
57 4 100       44 if( $value ) {
58 2         61 $self->log($r, 'client_address '.$ip.' matched SqlList '.$self->name);
59 2 50 33     67 if( defined $self->score
60             && ! $r->is_already_done($self->name.'-score') ) {
61 2         43 $self->add_score($r, $self->name , $self->score);
62             }
63 2 50       53 if( defined $self->match_action ) {
64 2         51 return Mail::MtPolicyd::Plugin::Result->new(
65             action => $self->match_action,
66             abort => 1,
67             );
68             }
69             } else {
70 2         60 $self->log($r, 'client_address '.$ip.' did not match SqlList '.$self->name);
71 2 100       69 if( defined $self->not_match_action ) {
72 1         26 return Mail::MtPolicyd::Plugin::Result->new(
73             action => $self->not_match_action,
74             abort => 1,
75             );
76             }
77             }
78              
79 1         5 return;
80             }
81              
82              
83             __PACKAGE__->meta->make_immutable;
84              
85             1;
86              
87             __END__
88              
89             =pod
90              
91             =encoding UTF-8
92              
93             =head1 NAME
94              
95             Mail::MtPolicyd::Plugin::SqlList - mtpolicyd plugin for accessing a SQL white/black/access list
96              
97             =head1 VERSION
98              
99             version 2.01
100              
101             =head1 SYNOPSIS
102              
103             <Plugin whitelist>
104             module="SqlList"
105             sql_query="SELECT client_ip FROM whitelist WHERE client_ip=?"
106             match_action=dunno
107             </Plugin>
108              
109             <Plugin blacklist>
110             module="SqlList"
111             sql_query="SELECT client_ip FROM blacklist WHERE client_ip=?"
112             match_action="reject you are blacklisted!"
113             </Plugin>
114              
115             =head1 DESCRIPTION
116              
117             Plugin checks the client_address against a SQL table.
118              
119             Depending on wether a supplied SQL query matched actions can be taken.
120              
121             =head2 PARAMETERS
122              
123             The module takes the following parameters:
124              
125             =over
126              
127             =item (uc_)enabled (default: "on")
128              
129             Could be set to 'off' to deactivate check. Could be used to activate/deactivate check per user.
130              
131             =item sql_query (default: "SELECT client_ip FROM whitelist WHERE client_ip=INET_ATON(?)")
132              
133             Prepared SQL statement to use for checking an IP address.
134              
135             ? will be replaced by the IP address.
136              
137             The module will match if the statement returns one or more rows.
138              
139             =back
140              
141             By default the plugin will do nothing. One of the following actions should be specified:
142              
143             =over
144              
145             =item match_action (default: empty)
146              
147             If given this action will be returned to the MTA if the SQL query matched.
148              
149             =item not_match_action (default: empty)
150              
151             If given this action will be returned to the MTA if the SQL query DID NOT matched.
152              
153             =item score (default: empty)
154              
155             If given this score will be applied to the session.
156              
157             =back
158              
159             =head1 EXAMPLE WITH A MYSQL TABLE
160              
161             You may use the following table for storing ipv4 addresses in MySQL:
162              
163             CREATE TABLE `whitelist` (
164             `id` int(11) NOT NULL AUTO_INCREMENT,
165             `client_ip` INT UNSIGNED NOT NULL,
166             PRIMARY KEY (`id`),
167             UNIQUE KEY `client_ip` (`client_ip`)
168             ) ENGINE=MyISAM DEFAULT CHARSET=latin1
169              
170             INSERT INTO whitelist VALUES(NULL, INET_ATON('127.0.0.1'));
171              
172             And use it as a whitelist in mtpolicyd:
173              
174             <VirtualHost 12345>
175             name="reputation"
176             <Plugin whitelist>
177             module="SqlList"
178             sql_query="SELECT client_ip FROM whitelist WHERE client_ip=INET_ATON(?)"
179             match_action="dunno"
180             </Plugin>
181             <Plugin trigger-greylisting>
182             ...
183             </VirtualHost>
184              
185             =head1 AUTHOR
186              
187             Markus Benning <ich@markusbenning.de>
188              
189             =head1 COPYRIGHT AND LICENSE
190              
191             This software is Copyright (c) 2014 by Markus Benning <ich@markusbenning.de>.
192              
193             This is free software, licensed under:
194              
195             The GNU General Public License, Version 2, June 1991
196              
197             =cut