| 1 | package Mail::DMARC::Report::Store::SQL; | ||
| 2 | our $VERSION = '2.20260827'; | ||
| 3 | use strict; | ||
| 4 | use warnings; | ||
| 5 | use feature 'signatures'; | ||
| 6 | |||
| 7 | use Carp; | ||
| 8 | use Data::Dumper; | ||
| 9 | use DBIx::Simple; | ||
| 10 | use File::ShareDir; | ||
| 11 | |||
| 12 | use Mail::DMARC::Report::Store::SQL::Grammars::MySQL; | ||
| 13 | use Mail::DMARC::Report::Store::SQL::Grammars::SQLite; | ||
| 14 | use Mail::DMARC::Report::Store::SQL::Grammars::PostgreSQL; | ||
| 15 | |||
| 16 | use parent 'Mail::DMARC::Base'; | ||
| 17 | use Mail::DMARC::Report::Aggregate; | ||
| 18 | use Mail::DMARC::Report::Aggregate::Record; | ||
| 19 | use Mail::DMARC::Policy; | ||
| 20 | |||
| 21 | my @AGG_FIELDS = qw/ messages aligned_both aligned_dkim aligned_spf aligned_none | ||
| 22 | disp_none disp_quarantine disp_reject /; | ||
| 23 | |||
| 24 | my %FORWARDING_REASON | ||
| 25 | = map { $_ => 1 } qw/ forwarded mailing_list trusted_forwarder /; | ||
| 26 | |||
| 27 | # A source is called aligned while its failures stay under this share of its | ||
| 28 | # own volume. Requiring exactly zero would label almost every real source as | ||
| 29 | # failing: retries, odd forwarders and low volume abuse of your domain put a | ||
| 30 | # few failures behind nearly everyone, and a bucket that catches everything | ||
| 31 | # sorts nothing. | ||
| 32 | my $FAILING_TOLERANCE = 0.02; | ||
| 33 | |||
| 34 | # Forwarding rarely accounts for every failure at a source that also has other | ||
| 35 | # problems, so the reasons only have to explain the bulk of them. | ||
| 36 | my $FORWARDING_SHARE = 0.5; | ||
| 37 | |||
| 38 | # ARC exists to carry authentication across a forwarding hop, so a receiver | ||
| 39 | # that overrode its policy on a passing ARC chain is telling you the mail was | ||
| 40 | # legitimately forwarded. The aggregate format has no field for that, so | ||
| 41 | # receivers report it as local_policy with the result in free text. | ||
| 42 | my $ARC_PASS = qr/\barc = pass\b/xi; | ||
| 43 | |||
| 44 | sub _explains_forwarding($reason) { | ||
| 45 | return 1 if $FORWARDING_REASON{ $reason->{type} // '' }; | ||
| 46 | return 1 | ||
| 47 | if 'local_policy' eq ( $reason->{type} // '' ) | ||
| 48 | && defined $reason->{comment} | ||
| 49 | && $reason->{comment} =~ $ARC_PASS; | ||
| 50 | return 0; | ||
| 51 | } | ||
| 52 | |||
| 53 | sub save_aggregate( $self, $agg ) { | ||
| 54 | |||
| 55 | $self->db_connect(); | ||
| 56 | |||
| 57 | croak "policy_published must be a Mail::DMARC::Policy object" | ||
| 58 | if 'Mail::DMARC::Policy' ne ref $agg->policy_published; | ||
| 59 | |||
| 60 | #warn Dumper($meta); ## no critic (Carp) | ||
| 61 | foreach my $f (qw/ org_name email begin end /) { | ||
| 62 | croak "meta field $f required" if !$agg->metadata->$f; | ||
| 63 | } | ||
| 64 | |||
| 65 | my $rid = $self->get_report_id($agg) | ||
| 66 | or croak "failed to create report!"; | ||
| 67 | |||
| 68 | # on 6/8/2013, Microsoft spat out a bunch of reports with zero records. | ||
| 69 | if ( !$agg->record ) { | ||
| 70 | warn "\ta report with ZERO records! Illegal.\n"; ## no critic (Carp) | ||
| 71 | return $rid; | ||
| 72 | } | ||
| 73 | |||
| 74 | foreach my $rec ( @{ $agg->record } ) { | ||
| 75 | $self->insert_agg_record( $rid, $rec ); | ||
| 76 | } | ||
| 77 | |||
| 78 | return $rid; | ||
| 79 | } | ||
| 80 | |||
| 81 | sub retrieve( $self, @args ) { | ||
| 82 | my %args = @args; | ||
| 83 | |||
| 84 | my $query = $self->grammar->select_report_query; | ||
| 85 | my @params; | ||
| 86 | |||
| 87 | if ( $args{rid} ) { | ||
| 88 | $query .= $self->grammar->and_arg('r.id'); | ||
| 89 | push @params, $args{rid}; | ||
| 90 | } | ||
| 91 | if ( $args{begin} ) { | ||
| 92 | $query .= $self->grammar->and_arg( 'r.begin', '>=' ); | ||
| 93 | push @params, $args{begin}; | ||
| 94 | } | ||
| 95 | if ( $args{end} ) { | ||
| 96 | $query .= $self->grammar->and_arg( 'r.end', '<=' ); | ||
| 97 | push @params, $args{end}; | ||
| 98 | } | ||
| 99 | if ( $args{author} ) { | ||
| 100 | my ( $op, $val ) = $self->_negate_arg( $args{author} ); | ||
| 101 | $query .= $self->grammar->and_arg( 'a.org_name', $op ); | ||
| 102 | push @params, $val; | ||
| 103 | } | ||
| 104 | if ( $args{from_domain} ) { | ||
| 105 | my ( $op, $val ) = $self->_negate_arg( $args{from_domain} ); | ||
| 106 | $query .= $self->grammar->and_arg( 'fd.domain', $op ); | ||
| 107 | push @params, $val; | ||
| 108 | } | ||
| 109 | |||
| 110 | my $sort_by = lc( $args{sort_by} || 'rid' ); | ||
| 111 | $sort_by = 'rid' if $sort_by eq 'id'; | ||
| 112 | |||
| 113 | my %allowed_sort = map { $_ => 1 } qw( rid author from_domain begin end ); | ||
| 114 | $sort_by = 'rid' if !$allowed_sort{$sort_by}; | ||
| 115 | |||
| 116 | my $sort_order = uc( $args{sort_order} || 'DESC' ); | ||
| 117 | $sort_order = 'DESC' if $sort_order ne 'ASC' && $sort_order ne 'DESC'; | ||
| 118 | |||
| 119 | $query .= $self->grammar->order_by( $sort_by, $sort_order ); | ||
| 120 | |||
| 121 | if ( defined $args{limit} ) { | ||
| 122 | croak "limit must be a positive integer" | ||
| 123 | if $args{limit} !~ /^\d+$/ || $args{limit} < 1; | ||
| 124 | $query .= $self->grammar->limit( $args{limit} ); | ||
| 125 | } | ||
| 126 | |||
| 127 | my $reports = $self->query( $query, \@params ); | ||
| 128 | |||
| 129 | foreach (@$reports) { | ||
| 130 | $_->{begin} = join( " ", split( /T/, $self->epoch_to_iso( $_->{begin} ) ) ); | ||
| 131 | $_->{end} = join( " ", split( /T/, $self->epoch_to_iso( $_->{end} ) ) ); | ||
| 132 | } | ||
| 133 | return $reports; | ||
| 134 | } | ||
| 135 | |||
| 136 | sub _negate_arg( $self, $val ) { | ||
| 137 | if ( substr( $val, 0, 1 ) eq '!' ) { | ||
| 138 | return ( '!=', substr( $val, 1 ) ); | ||
| 139 | } | ||
| 140 | return ( '=', $val ); | ||
| 141 | } | ||
| 142 | |||
| 143 | sub next_todo($self) { | ||
| 144 | |||
| 145 | if ( !exists $self->{_todo_list} ) { | ||
| 146 | $self->{_todo_list} | ||
| 147 | = $self->query( $self->grammar->select_todo_query, [ $self->time ] ); | ||
| 148 | return if !$self->{_todo_list}; | ||
| 149 | } | ||
| 150 | |||
| 151 | my $next_todo = shift @{ $self->{_todo_list} }; | ||
| 152 | if ( !$next_todo ) { | ||
| 153 | delete $self->{_todo_list}; | ||
| 154 | return; | ||
| 155 | } | ||
| 156 | |||
| 157 | my $agg = Mail::DMARC::Report::Aggregate->new(); | ||
| 158 | $self->populate_agg_metadata( \$agg, \$next_todo ); | ||
| 159 | |||
| 160 | my $pp = $self->get_report_policy_published( $next_todo->{rid} ); | ||
| 161 | $pp->{domain} = $next_todo->{from_domain}; | ||
| 162 | $agg->policy_published( Mail::DMARC::Policy->new(%$pp) ); | ||
| 163 | |||
| 164 | $self->populate_agg_records( \$agg, $next_todo->{rid} ); | ||
| 165 | return $agg; | ||
| 166 | } | ||
| 167 | |||
| 168 | sub retrieve_todo( $self, @args ) { | ||
| 169 | |||
| 170 | # this method extracts the data from the SQL tables and populates a | ||
| 171 | # list of Aggregate report objects with them. | ||
| 172 | my $reports | ||
| 173 | = $self->query( $self->grammar->select_todo_query, [ $self->time ] ); | ||
| 174 | my @reports_todo; | ||
| 175 | return \@reports_todo if !@$reports; | ||
| 176 | |||
| 177 | foreach my $report ( @{$reports} ) { | ||
| 178 | |||
| 179 | my $agg = Mail::DMARC::Report::Aggregate->new(); | ||
| 180 | $self->populate_agg_metadata( \$agg, \$report ); | ||
| 181 | |||
| 182 | my $pp = $self->get_report_policy_published( $report->{rid} ); | ||
| 183 | $pp->{domain} = $report->{from_domain}; | ||
| 184 | $agg->policy_published( Mail::DMARC::Policy->new(%$pp) ); | ||
| 185 | |||
| 186 | $self->populate_agg_records( \$agg, $report->{rid} ); | ||
| 187 | push @reports_todo, $agg; | ||
| 188 | } | ||
| 189 | return \@reports_todo; | ||
| 190 | } | ||
| 191 | |||
| 192 | sub delete_report( $self, $report_id = undef ) { | ||
| 193 | $report_id or croak "missing report ID"; | ||
| 194 | print "deleting report $report_id\n" if $self->verbose; | ||
| 195 | |||
| 196 | # deletes with FK don't cascade in SQLite? Clean each table manually | ||
| 197 | my $rows = $self->query( $self->grammar->report_record_id, [$report_id] ); | ||
| 198 | my @row_ids = map { $_->{id} } @$rows; | ||
| 199 | |||
| 200 | if (@row_ids) { | ||
| 201 | foreach my $table ( | ||
| 202 | qw/ report_record_spf report_record_dkim report_record_reason /) | ||
| 203 | { | ||
| 204 | print "deleting $table rows " . join( ',', @row_ids ) . "\n" | ||
| 205 | if $self->verbose; | ||
| 206 | eval { | ||
| 207 | $self->query( $self->grammar->delete_from_where_record_in($table), | ||
| 208 | \@row_ids ); | ||
| 209 | }; | ||
| 210 | |||
| 211 | # warn $@ if $@; | ||
| 212 | } | ||
| 213 | } | ||
| 214 | foreach my $table (qw/ report_policy_published report_record report_error /) { | ||
| 215 | print "deleting $table rows for report $report_id\n" if $self->verbose; | ||
| 216 | eval { | ||
| 217 | $self->query( $self->grammar->delete_from_where_report($table), | ||
| 218 | [$report_id] ); | ||
| 219 | }; | ||
| 220 | |||
| 221 | # warn $@ if $@; | ||
| 222 | } | ||
| 223 | |||
| 224 | # In MySQL, where FK constraints DO cascade, this is the only query needed | ||
| 225 | $self->query( $self->grammar->delete_report, [$report_id] ); | ||
| 226 | return 1; | ||
| 227 | } | ||
| 228 | |||
| 229 | sub get_domain_id( $self, $domain ) { | ||
| 230 | croak "missing domain calling " . ( caller(0) )[3] if !$domain; | ||
| 231 | my $r = $self->query( $self->grammar->select_domain_id, [$domain] ); | ||
| 232 | if ( $r && @$r ) { | ||
| 233 | return $r->[0]{id}; | ||
| 234 | } | ||
| 235 | return $self->query( $self->grammar->insert_domain, [$domain] ); | ||
| 236 | } | ||
| 237 | |||
| 238 | sub get_author_id( $self, $meta ) { | ||
| 239 | croak "missing author name" if !$meta->org_name; | ||
| 240 | my $r = $self->query( $self->grammar->select_author_id, [ $meta->org_name ] ); | ||
| 241 | if ( $r && @$r ) { | ||
| 242 | return $r->[0]{id}; | ||
| 243 | } | ||
| 244 | carp "missing email" if !$meta->email; | ||
| 245 | return $self->query( $self->grammar->insert_author, | ||
| 246 | [ $meta->org_name, $meta->email, $meta->extra_contact_info ] ); | ||
| 247 | } | ||
| 248 | |||
| 249 | sub get_report_id( $self, $aggr ) { | ||
| 250 | |||
| 251 | my $meta = $aggr->metadata; | ||
| 252 | my $pol = $aggr->policy_published; | ||
| 253 | |||
| 254 | # check if report exists | ||
| 255 | my $author_id = $self->get_author_id($meta) or croak; | ||
| 256 | my $from_dom_id = $self->get_domain_id( $pol->domain ) or croak; | ||
| 257 | |||
| 258 | my $ids; | ||
| 259 | if ( $meta->report_id ) { | ||
| 260 | |||
| 261 | # reports arriving via the wire will have an author ID & report ID | ||
| 262 | $ids = $self->query( $self->grammar->select_report_id, | ||
| 263 | [ $meta->report_id, $author_id ] ); | ||
| 264 | } | ||
| 265 | else { | ||
| 266 | # Reports submitted by our local MTA will not have a report ID | ||
| 267 | # They aggregate on the From domain, where the DMARC policy was discovered | ||
| 268 | $ids = $self->query( | ||
| 269 | $self->grammar->select_id_with_end, | ||
| 270 | [ $from_dom_id, $self->time, $author_id ] | ||
| 271 | ); | ||
| 272 | } | ||
| 273 | |||
| 274 | if (@$ids) { # report already exists | ||
| 275 | return $self->{report_id} = $ids->[0]{id}; | ||
| 276 | } | ||
| 277 | |||
| 278 | my $rid = $self->{report_id} = $self->query( $self->grammar->insert_report, | ||
| 279 | [ $from_dom_id, $meta->begin, $meta->end, $author_id, $meta->uuid ] ) | ||
| 280 | or return; | ||
| 281 | |||
| 282 | $self->insert_policy_published( $rid, $pol ); | ||
| 283 | return $rid; | ||
| 284 | } | ||
| 285 | |||
| 286 | sub get_report( $self, @args ) { | ||
| 287 | croak "invalid parameters" if @args % 2; | ||
| 288 | my %args = @args; | ||
| 289 | |||
| 290 | my @known = qw/ r.id a.org_name fd.domain r.begin r.end /; | ||
| 291 | my %known = map { $_ => 1 } @known; | ||
| 292 | |||
| 293 | my $where = ''; | ||
| 294 | my @where_params; | ||
| 295 | |||
| 296 | # Optional reporting window, so a caller can scope the list the same way | ||
| 297 | # the aggregate views are scoped. Compared against begin, matching how | ||
| 298 | # those views attribute a report to a day. | ||
| 299 | for my $pair ( [ since => 'r.begin', '>=' ], [ until => 'r.begin', '<=' ] ) { | ||
| 300 | my ( $param, $column, $op ) = @$pair; | ||
| 301 | next if !defined $args{$param}; | ||
| 302 | next if $args{$param} !~ /\A-?[0-9]+\z/x; | ||
| 303 | $where .= $self->grammar->and_arg( $column, $op ); | ||
| 304 | push @where_params, $args{$param}; | ||
| 305 | } | ||
| 306 | |||
| 307 | my ( $origin, $origin_params ) | ||
| 308 | = $self->_origin_filter( $args{reports} // 'all' ); | ||
| 309 | $where .= $origin; | ||
| 310 | push @where_params, @$origin_params; | ||
| 311 | |||
| 312 | # Per-column LIKE searches | ||
| 313 | for my $pair ( [ search_domain => 'fd.domain' ], | ||
| 314 | [ search_author => 'a.org_name' ] ) | ||
| 315 | { | ||
| 316 | my ( $param, $col_name ) = @$pair; | ||
| 317 | next unless $args{$param}; | ||
| 318 | my $safe = $args{$param}; | ||
| 319 | $safe =~ s/([%_!])/!$1/g; # escape LIKE metacharacters using ! | ||
| 320 | $where .= " AND $col_name LIKE ? ESCAPE '!'"; | ||
| 321 | push @where_params, '%' . $safe . '%'; | ||
| 322 | } | ||
| 323 | |||
| 324 | foreach my $known (@known) { | ||
| 325 | next if !defined $args{$known}; | ||
| 326 | $where .= $self->grammar->and_arg($known); | ||
| 327 | push @where_params, $args{$known}; | ||
| 328 | } | ||
| 329 | |||
| 330 | my $total_recs = $self->dbix->query( $self->grammar->count_reports )->list; | ||
| 331 | my $filtered_recs = $total_recs; | ||
| 332 | if ($where) { | ||
| 333 | $filtered_recs | ||
| 334 | = $self->dbix->query( | ||
| 335 | $self->grammar->count_filtered_report_query . $where, | ||
| 336 | @where_params )->list; | ||
| 337 | } | ||
| 338 | |||
| 339 | my $order = ''; | ||
| 340 | if ( $args{sort_col} && $known{ $args{sort_col} } ) { | ||
| 341 | if ( $args{sort_dir} ) { | ||
| 342 | $order = $self->grammar->order_by( $args{sort_col}, | ||
| 343 | $args{sort_dir} eq 'desc' ? ' DESC' : ' ASC' ); | ||
| 344 | } | ||
| 345 | } | ||
| 346 | |||
| 347 | my $query = $self->grammar->select_report_query . $where . $order; | ||
| 348 | my @params = @where_params; | ||
| 349 | if ( $args{length} ) { | ||
| 350 | my $start = $args{start} || 0; | ||
| 351 | $start = 0 if $start < 0; | ||
| 352 | $query .= $self->grammar->limit_args(2); | ||
| 353 | push @params, $start, $args{length}; | ||
| 354 | } | ||
| 355 | |||
| 356 | # warn "query: $query\n" . join(", ", @params) . "\n"; | ||
| 357 | my $reports = $self->query( $query, \@params ); | ||
| 358 | foreach (@$reports) { | ||
| 359 | $_->{begin} = $self->epoch_to_iso( $_->{begin} ); | ||
| 360 | $_->{end} = $self->epoch_to_iso( $_->{end} ); | ||
| 361 | } | ||
| 362 | |||
| 363 | $self->attach_report_summaries($reports) if $args{summary}; | ||
| 364 | |||
| 365 | return { | ||
| 366 | recordsTotal => $total_recs, | ||
| 367 | recordsFiltered => $filtered_recs, | ||
| 368 | data => $reports, | ||
| 369 | }; | ||
| 370 | } | ||
| 371 | |||
| 372 | # What each report contains, so a viewer has less reason to expand every row. | ||
| 373 | # Opt in from get_report: the callers that only want the report envelope should | ||
| 374 | # not pay for a second query. | ||
| 375 | sub attach_report_summaries( $self, $reports ) { | ||
| 376 | my @rids = map { $_->{rid} } @$reports; | ||
| 377 | return if !@rids; | ||
| 378 | |||
| 379 | my $rows = $self->query( $self->grammar->select_report_summary_query, \@rids ); | ||
| 380 | |||
| 381 | my %by_rid; | ||
| 382 | foreach my $row (@$rows) { | ||
| 383 | my $summary = $by_rid{ $row->{rid} } ||= { | ||
| 384 | ( map { $_ => 0 } @AGG_FIELDS ), | ||
| 385 | header_from => {}, | ||
| 386 | envelope_to => {}, | ||
| 387 | }; | ||
| 388 | $summary->{$_} += $row->{$_} || 0 foreach @AGG_FIELDS; | ||
| 389 | |||
| 390 | # A report names one domain per record; the set is usually a single | ||
| 391 | # entry, occasionally a handful. | ||
| 392 | foreach my $field (qw/ header_from envelope_to /) { | ||
| 393 | next if !defined $row->{$field} || '' eq $row->{$field}; | ||
| 394 | $summary->{$field}{ $row->{$field} } = 1; | ||
| 395 | } | ||
| 396 | } | ||
| 397 | |||
| 398 | foreach my $report (@$reports) { | ||
| 399 | my $found = $by_rid{ $report->{rid} }; | ||
| 400 | if ( !$found ) { | ||
| 401 | $report->{summary} | ||
| 402 | = { ( map { $_ => 0 } @AGG_FIELDS ), | ||
| 403 | header_from => [], envelope_to => [] }; | ||
| 404 | next; | ||
| 405 | } | ||
| 406 | my %summary = map { $_ => $found->{$_} + 0 } @AGG_FIELDS; | ||
| 407 | $summary{$_} = [ sort keys %{ $found->{$_} } ] | ||
| 408 | foreach qw/ header_from envelope_to /; | ||
| 409 | $report->{summary} = \%summary; | ||
| 410 | } | ||
| 411 | return; | ||
| 412 | } | ||
| 413 | |||
| 414 | sub get_report_policy_published( $self, $rid ) { | ||
| 415 | my $pp = $self->query( $self->grammar->select_report_policy_published, [$rid] ) | ||
| 416 | ->[0]; | ||
| 417 | $pp->{p} ||= 'none'; | ||
| 418 | $pp = Mail::DMARC::Policy->new( v => 'DMARC1', %$pp ); | ||
| 419 | return $pp; | ||
| 420 | } | ||
| 421 | |||
| 422 | sub get_rr( $self, @args ) { | ||
| 423 | croak "invalid parameters" if @args % 2; | ||
| 424 | my %args = @args; | ||
| 425 | |||
| 426 | # warn Dumper(\%args); | ||
| 427 | croak "missing report ID (rid)!" if !defined $args{rid}; | ||
| 428 | |||
| 429 | my $rows = $self->query( $self->grammar->select_rr_query, [ $args{rid} ] ); | ||
| 430 | foreach (@$rows) { | ||
| 431 | $_->{source_ip} = $self->any_inet_ntop( $_->{source_ip} ) | ||
| 432 | if $self->grammar->language ne 'postgresql'; | ||
| 433 | $_->{reasons} | ||
| 434 | = $self->query( $self->grammar->select_report_reason, [ $_->{id} ] ); | ||
| 435 | } | ||
| 436 | return { data => $rows, }; | ||
| 437 | } | ||
| 438 | |||
| 439 | sub populate_agg_metadata( $self, $agg_ref, $report_ref ) { | ||
| 440 | |||
| 441 | $$agg_ref->metadata->report_id( $$report_ref->{rid} ); | ||
| 442 | |||
| 443 | foreach my $f (qw/ org_name email extra_contact_info /) { | ||
| 444 | $$agg_ref->metadata->$f( $self->config->{organization}{$f} ); | ||
| 445 | } | ||
| 446 | foreach my $f (qw/ begin end /) { | ||
| 447 | $$agg_ref->metadata->$f( $$report_ref->{$f} ); | ||
| 448 | } | ||
| 449 | |||
| 450 | my $errors = $self->query( $self->grammar->select_report_error, | ||
| 451 | [ $$report_ref->{rid} ] ); | ||
| 452 | foreach (@$errors) { | ||
| 453 | $$agg_ref->metadata->error( $_->{error} ); | ||
| 454 | } | ||
| 455 | return 1; | ||
| 456 | } | ||
| 457 | |||
| 458 | sub populate_agg_records( $self, $agg_ref, $rid ) { | ||
| 459 | |||
| 460 | my $recs = $self->query( $self->grammar->select_rr_query, [$rid] ); | ||
| 461 | |||
| 462 | # aggregate the connections per IP-Disposition-DKIM-SPF uniqueness | ||
| 463 | my ( %ips, %uniq, %pe, %auth, %ident, %reasons, %other ); | ||
| 464 | foreach my $rec (@$recs) { | ||
| 465 | my $ip = $rec->{source_ip}; | ||
| 466 | $ip = $self->any_inet_ntop( $rec->{source_ip} ) | ||
| 467 | if $self->grammar->language ne 'postgresql'; | ||
| 468 | my $key = join( '-', $ip, @$rec{qw/ disposition dkim spf /} ); # hash slice | ||
| 469 | $uniq{$key}++; | ||
| 470 | $ips{$key} = $rec->{source_ip}; | ||
| 471 | $ident{$key}{header_from} ||= $rec->{header_from}; | ||
| 472 | $ident{$key}{envelope_from} ||= $rec->{envelope_from}; | ||
| 473 | $ident{$key}{envelope_to} ||= $rec->{envelope_to}; | ||
| 474 | |||
| 475 | $pe{$key}{disposition} ||= $rec->{disposition}; | ||
| 476 | $pe{$key}{dkim} ||= $rec->{dkim}; | ||
| 477 | $pe{$key}{spf} ||= $rec->{spf}; | ||
| 478 | |||
| 479 | $auth{$key}{spf} ||= $self->get_row_spf( $rec->{id} ); | ||
| 480 | $auth{$key}{dkim} ||= $self->get_row_dkim( $rec->{id} ); | ||
| 481 | |||
| 482 | my $reasons = $self->get_row_reason( $rec->{id} ); | ||
| 483 | foreach my $reason (@$reasons) { | ||
| 484 | my $type = $reason->{type} or next; | ||
| 485 | $reasons{$key}{$type} = $reason->{comment}; # flatten reasons | ||
| 486 | } | ||
| 487 | } | ||
| 488 | |||
| 489 | foreach my $u ( keys %uniq ) { | ||
| 490 | my $record = Mail::DMARC::Report::Aggregate::Record->new( | ||
| 491 | identifiers => $ident{$u}, | ||
| 492 | auth_results => $auth{$u}, | ||
| 493 | row => { | ||
| 494 | source_ip => $self->grammar->language eq 'postgresql' ? $ips{$u} | ||
| 495 | : $self->any_inet_ntop( $ips{$u} ), | ||
| 496 | count => $uniq{$u}, | ||
| 497 | policy_evaluated => { | ||
| 498 | %{ $pe{$u} }, | ||
| 499 | $reasons{$u} | ||
| 500 | ? ( reason => [ | ||
| 501 | map { { type => $_, comment => $reasons{$u}{$_} } } | ||
| 502 | sort keys %{ $reasons{$u} } | ||
| 503 | ] | ||
| 504 | ) | ||
| 505 | : (), | ||
| 506 | }, | ||
| 507 | } | ||
| 508 | ); | ||
| 509 | $$agg_ref->record($record); | ||
| 510 | } | ||
| 511 | return $$agg_ref->record; | ||
| 512 | } | ||
| 513 | |||
| 514 | sub row_exists( $self, $rid, $rec ) { | ||
| 515 | |||
| 516 | if ( !defined $rec->{row}{count} ) { | ||
| 517 | print "new record\n" if $self->verbose; | ||
| 518 | return; | ||
| 519 | } | ||
| 520 | |||
| 521 | my $rows = $self->query( $self->grammar->select_report_record, | ||
| 522 | [ $rid, $rec->{row}{source_ip}, $rec->{row}{count}, ] ); | ||
| 523 | |||
| 524 | return 1 if @$rows; | ||
| 525 | return; | ||
| 526 | } | ||
| 527 | |||
| 528 | sub insert_agg_record( $self, $report_id, $rec ) { | ||
| 529 | |||
| 530 | return 1 if $self->row_exists( $report_id, $rec ); | ||
| 531 | |||
| 532 | my $row_id = $self->insert_rr( $report_id, $rec ) | ||
| 533 | or croak "failed to insert report row"; | ||
| 534 | |||
| 535 | my $reasons = $rec->row->policy_evaluated->reason; | ||
| 536 | if ($reasons) { | ||
| 537 | foreach my $reason (@$reasons) { | ||
| 538 | next if !$reason || !$reason->{type}; | ||
| 539 | $self->insert_rr_reason( $row_id, $reason->{type}, $reason->{comment} ); | ||
| 540 | } | ||
| 541 | } | ||
| 542 | |||
| 543 | my $spf_ref = $rec->auth_results->spf; | ||
| 544 | if ($spf_ref) { | ||
| 545 | foreach my $spf (@$spf_ref) { | ||
| 546 | $self->insert_rr_spf( $row_id, $spf ); | ||
| 547 | } | ||
| 548 | } | ||
| 549 | |||
| 550 | my $dkim = $rec->auth_results->dkim; | ||
| 551 | if ($dkim) { | ||
| 552 | foreach my $sig (@$dkim) { | ||
| 553 | next if !$sig || !$sig->{domain}; | ||
| 554 | $self->insert_rr_dkim( $row_id, $sig ); | ||
| 555 | } | ||
| 556 | } | ||
| 557 | return 1; | ||
| 558 | } | ||
| 559 | |||
| 560 | sub insert_error( $self, $rid, $error ) { | ||
| 561 | |||
| 562 | # wait >5m before trying to deliver this report again | ||
| 563 | $self->query( $self->grammar->insert_error(0), | ||
| 564 | [ $self->time + ( 5 * 60 ), $rid ] ); | ||
| 565 | |||
| 566 | return $self->query( $self->grammar->insert_error(1), [ $rid, $error ] ); | ||
| 567 | } | ||
| 568 | |||
| 569 | sub insert_rr_reason( $self, $row_id, $type, $comment ) { | ||
| 570 | return $self->query( $self->grammar->insert_rr_reason, | ||
| 571 | [ $row_id, $type, ( $comment || '' ) ] ); | ||
| 572 | } | ||
| 573 | |||
| 574 | sub insert_rr_dkim( $self, $row_id, $dkim ) { | ||
| 575 | my ( @fields, @values ); | ||
| 576 | foreach (qw/ domain selector result human_result /) { | ||
| 577 | next if !defined $dkim->{$_}; | ||
| 578 | if ( 'domain' eq $_ ) { | ||
| 579 | push @fields, 'domain_id'; | ||
| 580 | push @values, $self->get_domain_id( $dkim->{domain} ); | ||
| 581 | next; | ||
| 582 | } | ||
| 583 | push @fields, $_; | ||
| 584 | push @values, $dkim->{$_}; | ||
| 585 | } | ||
| 586 | my $query = $self->grammar->insert_rr_dkim( \@fields ); | ||
| 587 | $self->query( $query, [ $row_id, @values ] ); | ||
| 588 | return 1; | ||
| 589 | } | ||
| 590 | |||
| 591 | sub insert_rr_spf( $self, $row_id, $spf ) { | ||
| 592 | my ( @fields, @values ); | ||
| 593 | for (qw/ domain scope result /) { | ||
| 594 | next if !defined $spf->{$_}; | ||
| 595 | if ( 'domain' eq $_ ) { | ||
| 596 | push @fields, 'domain_id'; | ||
| 597 | push @values, $self->get_domain_id( $spf->{domain} ); | ||
| 598 | next; | ||
| 599 | } | ||
| 600 | push @fields, $_; | ||
| 601 | push @values, $spf->{$_}; | ||
| 602 | } | ||
| 603 | my $query = $self->grammar->insert_rr_spf( \@fields ); | ||
| 604 | $self->query( $query, [ $row_id, @values ] ); | ||
| 605 | return 1; | ||
| 606 | } | ||
| 607 | |||
| 608 | sub insert_rr( $self, $report_id, $rec ) { | ||
| 609 | $report_id or croak "report ID required?!"; | ||
| 610 | my $query = $self->grammar->insert_rr; | ||
| 611 | |||
| 612 | my $ip = $rec->row->source_ip; | ||
| 613 | $ip = $self->any_inet_pton($ip) if $self->grammar->language ne 'postgresql'; | ||
| 614 | my @args = ( $report_id, $ip, $rec->{row}{count}, ); | ||
| 615 | foreach my $f (qw/ header_from envelope_to envelope_from /) { | ||
| 616 | push @args, | ||
| 617 | $rec->identifiers->$f | ||
| 618 | ? $self->get_domain_id( $rec->identifiers->$f ) | ||
| 619 | : undef; | ||
| 620 | } | ||
| 621 | push @args, map { $rec->row->policy_evaluated->$_ } qw/ disposition dkim spf /; | ||
| 622 | my $rr_id = $self->query( $query, \@args ) or croak; | ||
| 623 | return $self->{report_row_id} = $rr_id; | ||
| 624 | } | ||
| 625 | |||
| 626 | sub insert_policy_published( $self, $id, $pub ) { | ||
| 627 | my $query = $self->grammar->insert_policy_published; | ||
| 628 | $self->query( $query, [ $id, @$pub{qw/ adkim aspf p sp pct rua /} ] ); | ||
| 629 | return 1; | ||
| 630 | } | ||
| 631 | |||
| 632 | sub db_connect($self) { | ||
| 633 | |||
| 634 | my $dsn = $self->config->{report_store}{dsn} or croak; | ||
| 635 | my $user = $self->config->{report_store}{user}; | ||
| 636 | my $pass = $self->config->{report_store}{pass}; | ||
| 637 | |||
| 638 | # cacheing | ||
| 639 | if ( $self->{grammar} && $self->{dbix} ) { | ||
| 640 | my $cached_grammar_type = $self->{grammar}->dsn; | ||
| 641 | if ( $dsn =~ /$cached_grammar_type/ ) { | ||
| 642 | return $self->{dbix}; # caching | ||
| 643 | } | ||
| 644 | } | ||
| 645 | |||
| 646 | my $needs_tables; | ||
| 647 | |||
| 648 | $self->{grammar} = undef; | ||
| 649 | my %opts; | ||
| 650 | |||
| 651 | if ( $dsn =~ /sqlite/i ) { | ||
| 652 | my ($db) = ( split /=/, $dsn )[-1]; | ||
| 653 | if ( !$db || $db eq ':memory:' || !-e $db ) { | ||
| 654 | my $schema = 'mail_dmarc_schema.sqlite'; | ||
| 655 | $needs_tables = $self->get_db_schema($schema) | ||
| 656 | or croak | ||
| 657 | "can't locate DB $db AND can't find $schema! Create $db manually.\n"; | ||
| 658 | } | ||
| 659 | $self->{grammar} = Mail::DMARC::Report::Store::SQL::Grammars::SQLite->new(); | ||
| 660 | } | ||
| 661 | elsif ( $dsn =~ /mysql/i ) { | ||
| 662 | $opts{'mysql_enable_utf8mb4'} = 1; | ||
| 663 | $self->{grammar} = Mail::DMARC::Report::Store::SQL::Grammars::MySQL->new(); | ||
| 664 | } | ||
| 665 | elsif ( $dsn =~ /pg/i ) { | ||
| 666 | $self->{grammar} | ||
| 667 | = Mail::DMARC::Report::Store::SQL::Grammars::PostgreSQL->new(); | ||
| 668 | } | ||
| 669 | else { | ||
| 670 | croak "can't determine database type, so unable to load grammar.\n"; | ||
| 671 | } | ||
| 672 | |||
| 673 | $self->{dbix} = DBIx::Simple->connect( $dsn, $user, $pass, \%opts ) | ||
| 674 | or return $self->error( DBIx::Simple->error ); | ||
| 675 | |||
| 676 | if ($needs_tables) { | ||
| 677 | $self->apply_db_schema($needs_tables); | ||
| 678 | } | ||
| 679 | |||
| 680 | return $self->{dbix}; | ||
| 681 | } | ||
| 682 | |||
| 683 | sub db_check_err( $self, $err ) { | ||
| 684 | ## no critic (PackageVars) | ||
| 685 | return if !defined $DBI::errstr; | ||
| 686 | return if !$DBI::errstr; | ||
| 687 | return if $DBI::errstr eq 'DBI error: '; | ||
| 688 | croak $err . $DBI::errstr; | ||
| 689 | } | ||
| 690 | |||
| 691 | sub dbix($self) { | ||
| 692 | return $self->{dbix} if $self->{dbix}; | ||
| 693 | return $self->db_connect(); | ||
| 694 | } | ||
| 695 | |||
| 696 | sub apply_db_schema( $self, $file ) { | ||
| 697 | my $setup = $self->slurp($file); | ||
| 698 | foreach ( split /;/, $setup ) { | ||
| 699 | |||
| 700 | # warn "$_\n"; | ||
| 701 | $self->dbix->query($_); | ||
| 702 | } | ||
| 703 | return; | ||
| 704 | } | ||
| 705 | |||
| 706 | sub get_db_schema( $self, $file ) { | ||
| 707 | return "share/$file" if -f "share/$file"; # when testing | ||
| 708 | return File::ShareDir::dist_file( 'Mail-DMARC', $file ); # when installed | ||
| 709 | } | ||
| 710 | |||
| 711 | sub query( $self, $query, $params = undef, @extra ) { | ||
| 712 | |||
| 713 | my @c = caller; | ||
| 714 | my $err = sprintf( "query called by %s, %s\n", $c[0], $c[2] ) . "\t$query\n\t"; | ||
| 715 | |||
| 716 | my @params; | ||
| 717 | if ( defined $params ) { | ||
| 718 | @params = ref $params eq 'ARRAY' ? @$params : $params; | ||
| 719 | no warnings; ## no critic (NoWarnings) | ||
| 720 | $err .= join( ', ', @params ); | ||
| 721 | } | ||
| 722 | |||
| 723 | croak "too many arguments to exec_query!" if @extra; | ||
| 724 | |||
| 725 | my $dbix = $self->db_connect() or croak DBIx::Simple->error; | ||
| 726 | |||
| 727 | return $self->query_insert( $query, $err, @params ) if $query =~ /^INSERT/ix; | ||
| 728 | return $self->query_replace( $query, $err, @params ) | ||
| 729 | if $query =~ /^(?:REPLACE|UPDATE)/ix; | ||
| 730 | return $self->query_delete( $query, $err, @params ) | ||
| 731 | if $query =~ /^(?:DELETE|TRUNCATE)/ix; | ||
| 732 | return $self->query_any( $query, $err, @params ); | ||
| 733 | } | ||
| 734 | |||
| 735 | sub query_any( $self, $query, $err, @params ) { | ||
| 736 | |||
| 737 | # warn "query: $query\n" . join(", ", @params) . "\n"; | ||
| 738 | my $r; | ||
| 739 | eval { $r = $self->dbix->query( $query, @params )->hashes; } or print ''; | ||
| 740 | $self->db_check_err($err); | ||
| 741 | die "something went wrong with: $err\n" if !$r; ## no critic (Carp) | ||
| 742 | return $r; | ||
| 743 | } | ||
| 744 | |||
| 745 | sub query_insert( $self, $query, $err, @params ) { | ||
| 746 | eval { $self->dbix->query( $query, @params ) } or do { | ||
| 747 | warn DBIx::Simple->error . "\n"; | ||
| 748 | croak $err; | ||
| 749 | }; | ||
| 750 | $self->db_check_err($err); | ||
| 751 | |||
| 752 | # If the table has no autoincrement field, last_insert_id is zero | ||
| 753 | my ( undef, undef, $table ) = split /\s+/, $query; | ||
| 754 | ($table) = split( /\(/, $table ) if $table =~ /\(/; | ||
| 755 | $table =~ s/^"|"$//g; | ||
| 756 | croak "unable to determine table in query: $query" if !$table; | ||
| 757 | return $self->dbix->last_insert_id( undef, undef, $table, undef ); | ||
| 758 | } | ||
| 759 | |||
| 760 | sub query_replace( $self, $query, $err, @params ) { | ||
| 761 | $self->dbix->query( $query, @params ) or croak $err; | ||
| 762 | $self->db_check_err($err); | ||
| 763 | return 1; # sorry, no indication of success | ||
| 764 | } | ||
| 765 | |||
| 766 | sub query_delete( $self, $query, $err, @params ) { | ||
| 767 | my $affected = $self->dbix->query( $query, @params )->rows or croak $err; | ||
| 768 | $self->db_check_err($err); | ||
| 769 | return $affected; | ||
| 770 | } | ||
| 771 | |||
| 772 | sub get_row_spf( $self, $rowid ) { | ||
| 773 | return $self->query( $self->grammar->select_row_spf, [$rowid] ); | ||
| 774 | } | ||
| 775 | |||
| 776 | sub get_row_dkim( $self, $rowid ) { | ||
| 777 | return $self->query( $self->grammar->select_row_dkim, [$rowid] ); | ||
| 778 | } | ||
| 779 | |||
| 780 | sub get_row_reason( $self, $rowid ) { | ||
| 781 | return $self->query( $self->grammar->select_row_reason, [$rowid] ); | ||
| 782 | } | ||
| 783 | |||
| 784 | # A store doubles as the queue for reports this installation is preparing to | ||
| 785 | # send, and those are about other people's domains as seen by us. Reports about | ||
| 786 | # our domains as seen by others are a different population, and blending the | ||
| 787 | # two makes the headline pass rate answer neither question. They are told apart | ||
| 788 | # by who authored the report, which is the only signal the schema carries; an | ||
| 789 | # installation that has changed its org_name will read its older outgoing | ||
| 790 | # reports as received. | ||
| 791 | sub _origin_filter( $self, $kind ) { | ||
| 792 | return ( '', [] ) if 'all' eq ( $kind // '' ); | ||
| 793 | |||
| 794 | my $org = $self->config->{organization}{org_name}; | ||
| 795 | return ( '', [] ) if !$org; | ||
| 796 | |||
| 797 | my $op = 'outgoing' eq ( $kind // '' ) ? '=' : '<>'; | ||
| 798 | return ( $self->grammar->and_arg( 'a.org_name', $op ), [$org] ); | ||
| 799 | } | ||
| 800 | |||
| 801 | # Reports are attributed to the day their window begins, matching the day | ||
| 802 | # bucketing in the grammar, so a window filter compares against begin alone. | ||
| 803 | sub _agg_where( $self, $args ) { | ||
| 804 | my ( $where, @params ) = (''); | ||
| 805 | |||
| 806 | my @filters = ( | ||
| 807 | [ since => 'r.begin', '>=' ], | ||
| 808 | [ until => 'r.begin', '<=' ], | ||
| 809 | [ from_domain => 'fd.domain', '=' ], | ||
| 810 | [ author => 'a.org_name', '=' ], | ||
| 811 | ); | ||
| 812 | |||
| 813 | foreach my $filter (@filters) { | ||
| 814 | my ( $arg, $column, $op ) = @$filter; | ||
| 815 | next if !defined $args->{$arg}; | ||
| 816 | $where .= $self->grammar->and_arg( $column, $op ); | ||
| 817 | push @params, $args->{$arg}; | ||
| 818 | } | ||
| 819 | |||
| 820 | if ( defined $args->{source_ip} ) { | ||
| 821 | $where .= $self->grammar->and_arg('rr.source_ip'); | ||
| 822 | push @params, | ||
| 823 | $self->grammar->language eq 'postgresql' | ||
| 824 | ? $args->{source_ip} | ||
| 825 | : $self->any_inet_pton( $args->{source_ip} ); | ||
| 826 | } | ||
| 827 | |||
| 828 | $where .= $self->grammar->and_failing if $args->{failing_only}; | ||
| 829 | |||
| 830 | my ( $origin, $origin_params ) | ||
| 831 | = $self->_origin_filter( $args->{reports} // 'received' ); | ||
| 832 | $where .= $origin; | ||
| 833 | push @params, @$origin_params; | ||
| 834 | |||
| 835 | return ( $where, \@params ); | ||
| 836 | } | ||
| 837 | |||
| 838 | # DBI hands back aggregates as strings; the JSON views need numbers. | ||
| 839 | sub _agg_numify($rows) { | ||
| 840 | foreach my $row (@$rows) { | ||
| 841 | foreach my $field ( @AGG_FIELDS, | ||
| 842 | qw/ day reporters first_seen last_seen reports / ) | ||
| 843 | { | ||
| 844 | $row->{$field} += 0 if defined $row->{$field}; | ||
| 845 | } | ||
| 846 | } | ||
| 847 | return $rows; | ||
| 848 | } | ||
| 849 | |||
| 850 | sub _agg_ips_to_text( $self, $rows ) { | ||
| 851 | return $rows if $self->grammar->language eq 'postgresql'; | ||
| 852 | foreach my $row (@$rows) { | ||
| 853 | $row->{source_ip} = $self->any_inet_ntop( $row->{source_ip} ) | ||
| 854 | if $row->{source_ip}; | ||
| 855 | } | ||
| 856 | return $rows; | ||
| 857 | } | ||
| 858 | |||
| 859 | sub get_timeseries( $self, @args ) { | ||
| 860 | croak "invalid parameters" if @args % 2; | ||
| 861 | my %args = @args; | ||
| 862 | |||
| 863 | my ( $where, $params ) = $self->_agg_where( \%args ); | ||
| 864 | my $rows | ||
| 865 | = $self->query( $self->grammar->select_timeseries_query($where), $params ); | ||
| 866 | |||
| 867 | return { data => _agg_numify($rows) }; | ||
| 868 | } | ||
| 869 | |||
| 870 | # Totals are summed from the daily buckets rather than queried separately, so | ||
| 871 | # the tiles and the chart can never disagree. | ||
| 872 | sub _sum_timeseries( $self, %args ) { | ||
| 873 | my $days = $self->get_timeseries(%args)->{data}; | ||
| 874 | |||
| 875 | my %totals = map { $_ => 0 } @AGG_FIELDS; | ||
| 876 | foreach my $day (@$days) { | ||
| 877 | $totals{$_} += $day->{$_} || 0 for @AGG_FIELDS; | ||
| 878 | } | ||
| 879 | $totals{dmarc_pass} = $totals{messages} - $totals{aligned_none}; | ||
| 880 | $totals{days} = scalar @$days; | ||
| 881 | |||
| 882 | return \%totals; | ||
| 883 | } | ||
| 884 | |||
| 885 | sub get_summary( $self, @args ) { | ||
| 886 | croak "invalid parameters" if @args % 2; | ||
| 887 | my %args = @args; | ||
| 888 | |||
| 889 | my %summary = ( current => $self->_sum_timeseries(%args) ); | ||
| 890 | |||
| 891 | # An equal-length window immediately before this one, for the deltas. | ||
| 892 | if ( defined $args{since} && defined $args{until} ) { | ||
| 893 | my $span = $args{until} - $args{since}; | ||
| 894 | $summary{previous} = $self->_sum_timeseries( %args, | ||
| 895 | since => $args{since} - $span - 1, | ||
| 896 | until => $args{since} - 1, | ||
| 897 | ); | ||
| 898 | } | ||
| 899 | |||
| 900 | return \%summary; | ||
| 901 | } | ||
| 902 | |||
| 903 | sub get_sources( $self, @args ) { | ||
| 904 | croak "invalid parameters" if @args % 2; | ||
| 905 | my %args = @args; | ||
| 906 | |||
| 907 | my ( $where, $params ) = $self->_agg_where( \%args ); | ||
| 908 | my $sources | ||
| 909 | = $self->query( $self->grammar->select_sources_query($where), $params ); | ||
| 910 | my ( $fwhere, $fparams ) | ||
| 911 | = $self->_agg_where( { %args, failing_only => 1 } ); | ||
| 912 | my $reasons = $self->query( | ||
| 913 | $self->grammar->select_source_reasons_query($fwhere), $fparams ); | ||
| 914 | |||
| 915 | $self->_agg_ips_to_text($sources); | ||
| 916 | $self->_agg_ips_to_text($reasons); | ||
| 917 | _agg_numify($sources); | ||
| 918 | |||
| 919 | my %by_ip; | ||
| 920 | foreach my $reason (@$reasons) { | ||
| 921 | push @{ $by_ip{ $reason->{source_ip} } }, | ||
| 922 | { | ||
| 923 | type => $reason->{type}, | ||
| 924 | comment => $reason->{comment}, | ||
| 925 | messages => ( $reason->{messages} || 0 ) + 0, | ||
| 926 | }; | ||
| 927 | } | ||
| 928 | |||
| 929 | foreach my $source (@$sources) { | ||
| 930 | $source->{reasons} = $by_ip{ $source->{source_ip} } || []; | ||
| 931 | $source->{bucket} = _classify_source($source); | ||
| 932 | } | ||
| 933 | |||
| 934 | my $sort = $args{sort_col} && $args{sort_col} eq 'messages' | ||
| 935 | ? sub { $b->{messages} <=> $a->{messages} } | ||
| 936 | : sub { $b->{aligned_none} <=> $a->{aligned_none} | ||
| 937 | || $b->{messages} <=> $a->{messages} }; | ||
| 938 | my @sorted = sort $sort @$sources; | ||
| 939 | |||
| 940 | my $total = scalar @sorted; | ||
| 941 | |||
| 942 | # Summed before paging: a caller showing each source's share of the window | ||
| 943 | # cannot derive the denominator from one page. | ||
| 944 | my $messages = 0; | ||
| 945 | $messages += $_->{messages} || 0 foreach @sorted; | ||
| 946 | |||
| 947 | if ( $args{length} ) { | ||
| 948 | my $start = $args{start} || 0; | ||
| 949 | $start = 0 if $start < 0; | ||
| 950 | @sorted = splice @sorted, $start, $args{length}; | ||
| 951 | } | ||
| 952 | |||
| 953 | return { | ||
| 954 | recordsTotal => $total, | ||
| 955 | messagesTotal => $messages, | ||
| 956 | data => \@sorted, | ||
| 957 | }; | ||
| 958 | } | ||
| 959 | |||
| 960 | # Ranks a source by whether it needs attention. Telling a misconfigured sender | ||
| 961 | # apart from an unauthenticated one needs the auth detail tables, which the | ||
| 962 | # list query does not join; get_source_detail makes that distinction. | ||
| 963 | sub _classify_source($source) { | ||
| 964 | my $failing = $source->{aligned_none} || 0; | ||
| 965 | my $total = $source->{messages} || 0; | ||
| 966 | |||
| 967 | return 'aligned' if !$failing; | ||
| 968 | return 'aligned' if $total && ( $failing / $total ) < $FAILING_TOLERANCE; | ||
| 969 | |||
| 970 | # The largest single reason, not their sum: one record may carry several, | ||
| 971 | # and each row already counts that record's whole volume. | ||
| 972 | my $forwarded = 0; | ||
| 973 | foreach my $reason ( @{ $source->{reasons} } ) { | ||
| 974 | next if !_explains_forwarding($reason); | ||
| 975 | $forwarded = $reason->{messages} if $reason->{messages} > $forwarded; | ||
| 976 | } | ||
| 977 | return 'forwarded' if $forwarded >= $failing * $FORWARDING_SHARE; | ||
| 978 | |||
| 979 | return 'failing'; | ||
| 980 | } | ||
| 981 | |||
| 982 | sub get_source_detail( $self, @args ) { | ||
| 983 | croak "invalid parameters" if @args % 2; | ||
| 984 | my %args = @args; | ||
| 985 | croak "missing source_ip" if !defined $args{source_ip}; | ||
| 986 | |||
| 987 | my ( $where, $params ) = $self->_agg_where( \%args ); | ||
| 988 | |||
| 989 | my $records | ||
| 990 | = $self->query( $self->grammar->select_source_detail_query($where), $params ); | ||
| 991 | my $dkim | ||
| 992 | = $self->query( $self->grammar->select_source_dkim_query($where), $params ); | ||
| 993 | my $spf | ||
| 994 | = $self->query( $self->grammar->select_source_spf_query($where), $params ); | ||
| 995 | |||
| 996 | _agg_numify($_) for $records, $dkim, $spf; | ||
| 997 | |||
| 998 | my $failing = 0; | ||
| 999 | $failing += $_->{messages} | ||
| 1000 | foreach grep { 'pass' ne ( $_->{dkim} // '' ) && 'pass' ne ( $_->{spf} // '' ) } | ||
| 1001 | @$records; | ||
| 1002 | |||
| 1003 | my ( $fwhere, $fparams ) | ||
| 1004 | = $self->_agg_where( { %args, failing_only => 1 } ); | ||
| 1005 | my $failing_auth | ||
| 1006 | = @{ $self->query( $self->grammar->select_source_dkim_query($fwhere), | ||
| 1007 | $fparams ) } | ||
| 1008 | || @{ $self->query( $self->grammar->select_source_spf_query($fwhere), | ||
| 1009 | $fparams ) }; | ||
| 1010 | |||
| 1011 | my $bucket | ||
| 1012 | = !$failing ? 'aligned' | ||
| 1013 | : $failing_auth ? 'broken' | ||
| 1014 | : 'unauthenticated'; | ||
| 1015 | |||
| 1016 | return { | ||
| 1017 | source_ip => $args{source_ip}, | ||
| 1018 | bucket => $bucket, | ||
| 1019 | records => $records, | ||
| 1020 | dkim => $dkim, | ||
| 1021 | spf => $spf, | ||
| 1022 | }; | ||
| 1023 | } | ||
| 1024 | |||
| 1025 | sub get_report_domains( $self, @args ) { | ||
| 1026 | croak "invalid parameters" if @args % 2; | ||
| 1027 | my %args = @args; | ||
| 1028 | |||
| 1029 | my ( $where, $params ) | ||
| 1030 | = $self->_origin_filter( $args{reports} // 'received' ); | ||
| 1031 | my $rows | ||
| 1032 | = $self->query( $self->grammar->select_report_domains_query($where), | ||
| 1033 | $params ); | ||
| 1034 | |||
| 1035 | return { data => _agg_numify($rows) }; | ||
| 1036 | } | ||
| 1037 | |||
| 1038 | sub grammar($self) { | ||
| 1039 | $self->db_connect(); | ||
| 1040 | return $self->{grammar}; | ||
| 1041 | } | ||
| 1042 | |||
| 1043 | 1; | ||
| 1044 | |||
| 1045 | __END__ | ||
| 1046 | |||
| 1047 | =pod | ||
| 1048 | |||
| 1049 | =head1 NAME | ||
| 1050 | |||
| 1051 | Mail::DMARC::Report::Store::SQL - store and retrieve reports from a SQL RDBMS | ||
| 1052 | |||
| 1053 | =head1 VERSION | ||
| 1054 | |||
| 1055 | version 2.20260827 | ||
| 1056 | |||
| 1057 | =head1 DESCRIPTION | ||
| 1058 | |||
| 1059 | Uses ANSI SQL syntax, keeping the SQL as portable as possible. | ||
| 1060 | |||
| 1061 | DB engine specific features are to be avoided. | ||
| 1062 | |||
| 1063 | =head1 SYPNOSIS | ||
| 1064 | |||
| 1065 | Store and retrieve DMARC reports from SQL data store. | ||
| 1066 | |||
| 1067 | Tested with SQLite, MySQL and PostgreSQL. | ||
| 1068 | |||
| 1069 | =head1 AUTHORS | ||
| 1070 | |||
| 1071 | =over 4 | ||
| 1072 | |||
| 1073 | =item * | ||
| 1074 | |||
| 1075 | Matt Simerson <msimerson@cpan.org> | ||
| 1076 | |||
| 1077 | =item * | ||
| 1078 | |||
| 1079 | Davide Migliavacca <shari@cpan.org> | ||
| 1080 | |||
| 1081 | =item * | ||
| 1082 | |||
| 1083 | Marc Bradshaw <marc@marcbradshaw.net> | ||
| 1084 | |||
| 1085 | =back | ||
| 1086 | |||
| 1087 | =head1 COPYRIGHT AND LICENSE | ||
| 1088 | |||
| 1089 | This software is copyright (c) 2026 by Matt Simerson. | ||
| 1090 | |||
| 1091 | This is free software; you can redistribute it and/or modify it under | ||
| 1092 | the same terms as the Perl 5 programming language system itself. | ||
| 1093 | |||
| 1094 | =cut |