usr/sbin/opendmarc-import

   1 #!/usr/bin/perl
   2 #
   3 # Copyright (c) 2012, 2014, The Trusted Domain Project.  All rights reserved.
   4 #
   5 # Script to import per-message DMARC data.
   6
   7 ###
   8 ### Setup
   9 ###
  10
  11 use strict;
  12 use warnings;
  13
  14 use Switch;
  15
  16 use DBI;
  17 use File::Basename;
  18 use Fcntl qw(:flock);
  19 use Getopt::Long;
  20 use POSIX;
  21
  22 require DBD::mysql;
  23
  24 # general
  25 my $progname      = basename($0);
  26 my $version       = "1.3.1";
  27 my $verbose       = 0;
  28 my $helponly      = 0;
  29 my $showversion   = 0;
  30
  31 # DB parameters
  32 my $def_dbhost    = "localhost";
  33 my $def_dbname    = "opendmarc";
  34 my $def_dbuser    = "opendmarc";
  35 my $def_dbpasswd  = "opendmarc";
  36 my $def_dbport    = "3306";
  37 my $def_interval  = "86400";
  38 my $dbhost;
  39 my $dbname;
  40 my $dbuser;
  41 my $dbpasswd;
  42 my $dbport;
  43
  44 my $dbscheme     = "mysql";
  45
  46 my $dbi_a;
  47 my $dbi_h;
  48 my $dbi_s;
  49 my $dbi_t;
  50
  51 my $lineno;
  52 my $key;
  53 my $value;
  54
  55 my $action;
  56 my $adkim;
  57 my $align_dkim;
  58 my $align_spf;
  59 my $aspf;
  60 my $dd;
  61 my $dkim_align;
  62 my @dkim_data;
  63 my $dkim_domain;
  64 my @dkim_entry;
  65 my $dkim_result;
  66 my $envdomain;
  67 my $fdomain;
  68 my $ipaddr;
  69 my $jobid;
  70 my $p;
  71 my $pct;
  72 my $pdomain;
  73 my $policy;
  74 my $received;
  75 my $reporter;
  76 my $repuri;
  77 my $sigcount = 0;
  78 my $sp;
  79 my $spf;
  80 my @rua;
  81
  82 ###
  83 ### NO user-serviceable parts beyond this point
  84 ###
  85
  86 sub get_value
  87 {
  88         my $table;
  89         my $column;
  90         my $id;
  91         my $out;
  92
  93         ($table, $column, $id) = @_;
  94
  95         $dbi_t = $dbi_h->prepare("SELECT $column FROM $table WHERE id = ?");
  96         if (!$dbi_t->execute($id))
  97         {
  98                 print STDERR "$progname: failed to $column value for ID $id: " . $dbi_h->errstr . "\n";
  99                 return undef;
 100         }
 101
 102         while ($dbi_a = $dbi_t->fetchrow_arrayref())
 103         {
 104                 if (defined($dbi_a->[0]))
 105                 {
 106                         $out = $dbi_a->[0];
 107                 }
 108         }
 109
 110         return $out;
 111 }
 112
 113 sub get_table_id
 114 {
 115         my $name;
 116         my $table;
 117         my $column;
 118         my $out;
 119
 120         ($name, $table, $column) = @_;
 121
 122         if (!defined($name) || !defined($table))
 123         {
 124                 return undef;
 125         }
 126
 127         if (!defined($column))
 128         {
 129                 $column = "name";
 130         }
 131
 132         $dbi_t = $dbi_h->prepare("SELECT id FROM $table WHERE $column = ?");
 133         if (!$dbi_t->execute($name))
 134         {
 135                 print STDERR "$progname: failed to retrieve table ID: " . $dbi_h->errstr . "\n";
 136                 return undef;
 137         }
 138
 139         undef $out;
 140         while ($dbi_a = $dbi_t->fetchrow_arrayref())
 141         {
 142                 if (defined($dbi_a->[0]))
 143                 {
 144                         $out = $dbi_a->[0];
 145                 }
 146         }
 147
 148         $dbi_t->finish;
 149
 150         if (!defined($out))
 151         {
 152                 $dbi_t = $dbi_h->prepare("INSERT INTO $table ($column) VALUES(?)");
 153                 if (!$dbi_t->execute($name))
 154                 {
 155                         print STDERR "$progname: failed to create table ID: " . $dbi_h->errstr . "\n";
 156                         return undef;
 157                 }
 158
 159                 $dbi_t = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
 160                 if (!$dbi_t->execute())
 161                 {
 162                         print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
 163                         return undef;
 164                 }
 165
 166                 while ($dbi_a = $dbi_t->fetchrow_arrayref())
 167                 {
 168                         if (defined($dbi_a->[0]))
 169                         {
 170                                 $out = $dbi_a->[0];
 171                         }
 172                 }
 173
 174                 $dbi_t->finish;
 175
 176                 if (!defined($out))
 177                 {
 178                         print STDERR "$progname: failed to retrieve created table ID: " . $dbi_h->errstr . "\n";
 179                         return undef;
 180                 }
 181         }
 182
 183         return $out;
 184 }
 185
 186 sub update_db
 187 {
 188         my $rep_id;
 189         my $from_id;
 190         my $envfrom_id;
 191         my $pdomain_id;
 192         my $ipaddr_id;
 193         my $msg_id;
 194         my $sdomain_id;
 195         my $request_id;
 196
 197         if ($verbose)
 198         {
 199                 print STDERR "$progname: updating at line $lineno\n";
 200         }
 201
 202         $rep_id = get_table_id($reporter, "reporters");
 203         $from_id = get_table_id($fdomain, "domains");
 204         $envfrom_id = get_table_id($envdomain, "domains");
 205         $pdomain_id = get_table_id($pdomain, "domains");
 206         $ipaddr_id = get_table_id($ipaddr, "ipaddr", "addr");
 207         $request_id = get_table_id($from_id, "requests", "domain");
 208
 209         if (!defined($rep_id) ||
 210             !defined($from_id) ||
 211             !defined($envfrom_id) ||
 212             !defined($pdomain_id) ||
 213             !defined($ipaddr_id) ||
 214             !defined($request_id))
 215         {
 216                 return;
 217         }
 218
 219         $dbi_s = $dbi_h->prepare("INSERT INTO messages (date, jobid, reporter, policy, disp, ip, env_domain, from_domain, spf, align_spf, align_dkim, sigcount) VALUES(FROM_UNIXTIME(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
 220         if (!$dbi_s->execute($received, $jobid, $rep_id, $policy, $action, $ipaddr_id, $envfrom_id, $from_id, $spf, $align_spf, $align_dkim, $sigcount))
 221         {
 222                 print STDERR "$progname: failed to insert message: " . $dbi_h->errstr . "\n";
 223                 return;
 224         }
 225
 226         $dbi_s->finish;
 227
 228         undef $msg_id;
 229         $dbi_s = $dbi_h->prepare("SELECT LAST_INSERT_ID()");
 230         if (!$dbi_s->execute())
 231         {
 232                 print STDERR "$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
 233                 return;
 234         }
 235
 236         while ($dbi_a = $dbi_s->fetchrow_arrayref())
 237         {
 238                 if (defined($dbi_a->[0]))
 239                 {
 240                         $msg_id = $dbi_a->[0];
 241                 }
 242         }
 243
 244         $dbi_s->finish;
 245
 246         if (!defined($msg_id))
 247         {
 248                 print STDERR "$progname: failed to retrieve message ID: " . $dbi_h->errstr . "\n";
 249                 return;
 250         }
 251
 252         $dbi_s = $dbi_h->prepare("INSERT INTO signatures (message, domain, pass, error) VALUES(?, ?, ?, ?)");
 253         foreach $dd (0 .. $#dkim_data)
 254         {
 255                 my $sdomain;
 256                 my $pass;
 257                 my $error;
 258
 259                 $sdomain = $dkim_data[$dd][0];
 260                 $pass = $dkim_data[$dd][1];
 261                 $error = $dkim_data[$dd][2];
 262
 263                 $sdomain_id = get_table_id($sdomain, "domains");
 264                 if (!defined($sdomain_id))
 265                 {
 266                         next;
 267                 }
 268
 269                 if (!$dbi_s->execute($msg_id, $sdomain_id, $pass, $error))
 270                 {
 271                         print STDERR "$progname: failed to insert DKIM data: " . $dbi_h->errstr . "\n";
 272                         $dbi_s->finish;
 273                         return;
 274                 }
 275         }
 276         $dbi_s->finish;
 277
 278         if (get_value("requests", "locked", $request_id) != 1)
 279         {
 280                 if (scalar @rua > 0)
 281                 {
 282                         $repuri = join(",", @rua);
 283                         $dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = ? WHERE id = ?");
 284
 285                         if (!$dbi_s->execute($repuri, $request_id))
 286                         {
 287                                 print STDERR "$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
 288                                 $dbi_s->finish;
 289                                 return;
 290                         }
 291
 292                         $dbi_s->finish;
 293                 }
 294                 else
 295                 {
 296                         $dbi_s = $dbi_h->prepare("UPDATE requests SET repuri = NULL WHERE id = ?");
 297
 298                         if (!$dbi_s->execute($request_id))
 299                         {
 300                                 print STDERR "$progname: failed to update reporting URI for $fdomain: " . $dbi_h->errstr . "\n";
 301                                 $dbi_s->finish;
 302                                 return;
 303                         }
 304
 305                         $dbi_s->finish;
 306                 }
 307
 308                 $dbi_s = $dbi_h->prepare("UPDATE requests SET adkim = ?, aspf = ?, policy = ?, spolicy = ?, pct = ? WHERE id = ?");
 309
 310                 if (!$dbi_s->execute($adkim, $aspf, $p, $sp, $pct, $request_id))
 311                 {
 312                         print STDERR "$progname: failed to update policy data for $fdomain: " . $dbi_h->errstr . "\n";
 313                         $dbi_s->finish;
 314                         return;
 315                 }
 316         }
 317
 318         $dbi_s->finish;
 319 }
 320
 321 sub usage
 322 {
 323         print STDERR "$progname: usage: $progname [options]\n";
 324         print STDERR "\t--dbhost=host      database host [$def_dbhost]\n";
 325         print STDERR "\t--dbname=name      database name [$def_dbname]\n";
 326         print STDERR "\t--dbpasswd=passwd  database password [$def_dbpasswd]\n";
 327         print STDERR "\t--dbport=port      database port [$def_dbport]\n";
 328         print STDERR "\t--dbuser=user      database user [$def_dbuser]\n";
 329         print STDERR "\t--help             print help and exit\n";
 330         print STDERR "\t--verbose          verbose output\n";
 331         print STDERR "\t--version          print version and exit\n";
 332 }
 333
 334 # parse command line arguments
 335 my $opt_retval = &Getopt::Long::GetOptions ('dbhost=s' => \$dbhost,
 336                                             'dbname=s' => \$dbname,
 337                                             'dbpasswd=s' => \$dbpasswd,
 338                                             'dbport=s' => \$dbport,
 339                                             'dbuser=s' => \$dbuser,
 340                                             'help!' => \$helponly,
 341                                             'verbose!' => \$verbose,
 342                                             'version!' => \$showversion,
 343                                            );
 344
 345 if (!$opt_retval || $helponly)
 346 {
 347         usage();
 348
 349         if ($helponly)
 350         {
 351                 exit(0);
 352         }
 353         else
 354         {
 355                 exit(1);
 356         }
 357 }
 358
 359 if ($showversion)
 360 {
 361         print STDOUT "$progname v$version\n";
 362         exit(0);
 363 }
 364
 365 # apply defaults
 366 if (!defined($dbhost))
 367 {
 368         if (defined($ENV{'OPENDMARC_DBHOST'}))
 369         {
 370                 $dbhost = $ENV{'OPENDMARC_DBHOST'};
 371         }
 372         else
 373         {
 374                 $dbhost = $def_dbhost;
 375         }
 376 }
 377
 378 if (!defined($dbname))
 379 {
 380         if (defined($ENV{'OPENDMARC_DB'}))
 381         {
 382                 $dbname = $ENV{'OPENDMARC_DB'};
 383         }
 384         else
 385         {
 386                 $dbname = $def_dbname;
 387         }
 388 }
 389
 390 if (!defined($dbpasswd))
 391 {
 392         if (defined($ENV{'OPENDMARC_PASSWORD'}))
 393         {
 394                 $dbpasswd = $ENV{'OPENDMARC_PASSWORD'};
 395         }
 396         else
 397         {
 398                 $dbpasswd = $def_dbpasswd;
 399         }
 400 }
 401
 402 if (!defined($dbport))
 403 {
 404         if (defined($ENV{'OPENDMARC_PORT'}))
 405         {
 406                 $dbport = $ENV{'OPENDMARC_PORT'};
 407         }
 408         else
 409         {
 410                 $dbport = $def_dbport;
 411         }
 412 }
 413
 414 if (!defined($dbuser))
 415 {
 416         if (defined($ENV{'OPENDMARC_USER'}))
 417         {
 418                 $dbuser = $ENV{'OPENDMARC_USER'};
 419         }
 420         else
 421         {
 422                 $dbuser = $def_dbuser;
 423         }
 424 }
 425
 426 if ($verbose)
 427 {
 428         print STDERR "$progname: started at " . localtime() . "\n";
 429 }
 430
 431 my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
 432               ";host=" . $dbhost . ";port=" . $dbport;
 433
 434 $dbi_h = DBI->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError => 0 });
 435 if (!defined($dbi_h))
 436 {
 437         print STDERR "$progname: unable to connect to database: $DBI::errstr\n";
 438         exit(1);
 439 }
 440
 441 if ($verbose)
 442 {
 443         print STDERR "$progname: connected to database\n";
 444 }
 445
 446 #
 447 # Read history file from stdin.
 448 #
 449
 450 $lineno = 0;
 451 if (!flock(STDIN, LOCK_SH))
 452 {
 453         print STDERR "$progname: warning: unable to establish read lock\n";
 454 }
 455
 456 while (<STDIN>)
 457 {
 458         $lineno++;
 459
 460         chomp;
 461         ($key, $value, $dkim_result) = split;
 462
 463         switch ($key)
 464         {
 465           case "action"         {
 466                                         $action = $value;
 467                                 }
 468
 469           case "adkim"          {
 470                                         $adkim = $value;
 471                                 }
 472
 473           case "align_dkim"     {
 474                                         $align_dkim = $value;
 475                                 }
 476
 477           case "align_spf"      {
 478                                         $align_spf = $value;
 479                                 }
 480
 481           case "aspf"           {
 482                                         $aspf = $value;
 483                                 }
 484
 485           case "dkim"           {
 486                                         undef @dkim_entry;
 487                                         push(@dkim_entry, $value);
 488                                         push(@dkim_entry, $dkim_result);
 489                                         if ($dkim_result eq "4" ||
 490                                             $dkim_result eq "5")
 491                                         {
 492                                                 push(@dkim_entry, 1);
 493                                         }
 494                                         else
 495                                         {
 496                                                 push(@dkim_entry, 0);
 497                                         }
 498                                         push(@dkim_data, [ @dkim_entry ]);
 499
 500                                         $sigcount++;
 501                                 }
 502
 503           case "from"           {
 504                                         $fdomain = $value;
 505                                 }
 506
 507           case "job"            {
 508                                         if (defined($jobid))
 509                                         {
 510                                                 update_db();
 511
 512                                                 undef $action;
 513                                                 undef $adkim;
 514                                                 undef $align_dkim;
 515                                                 undef $align_spf;
 516                                                 undef $aspf;
 517                                                 undef @dkim_data;
 518                                                 undef $envdomain;
 519                                                 undef $fdomain;
 520                                                 undef $ipaddr;
 521                                                 undef $jobid;
 522                                                 undef $p;
 523                                                 undef $pct;
 524                                                 undef $pdomain;
 525                                                 undef $policy;
 526                                                 undef $received;
 527                                                 undef $reporter;
 528                                                 undef @rua;
 529                                                 $sigcount = 0;
 530                                                 undef $sp;
 531                                                 undef $spf;
 532                                         }
 533
 534                                         $jobid = $value;
 535                                 }
 536
 537           case "ipaddr"         {
 538                                         $ipaddr = $value;
 539                                 }
 540
 541           case "mfrom"          {
 542                                         $envdomain = $value;
 543                                 }
 544
 545           case "p"              {
 546                                         $p = $value;
 547                                 }
 548
 549           case "pct"            {
 550                                         $pct = $value;
 551                                 }
 552
 553           case "pdomain"        {
 554                                         $pdomain = $value;
 555                                 }
 556
 557           case "policy"         {
 558                                         $policy = $value;
 559                                 }
 560
 561           case "received"       {
 562                                         $received = $value;
 563                                 }
 564
 565           case "reporter"       {
 566                                         $reporter = $value;
 567                                 }
 568
 569           case "rua"            {
 570                                         if ($value ne "-")
 571                                         {
 572                                                 push(@rua, $value);
 573                                         }
 574                                 }
 575
 576           case "sp"             {
 577                                         $sp = $value;
 578                                 }
 579
 580           case "spf"            {
 581                                         $spf = $value;
 582                                 }
 583
 584           else                  {
 585                                         print STDERR "$progname: unknown key '$key' at line $lineno\n";
 586                                 }
 587         }
 588 }
 589
 590 if (defined($jobid))
 591 {
 592         update_db();
 593 }
 594
 595 #
 596 # all done!
 597 #
 598
 599 if ($verbose)
 600 {
 601         print STDERR "$progname: terminating at " . localtime() . "\n";
 602 }
 603
 604 $dbi_h->disconnect;
 605
 606 exit(0);