usr/sbin/opendmarc-expire

   1 #!/usr/bin/perl
   2 #
   3 # Copyright (c) 2010-2012, 2014, 2015, The Trusted Domain Project.
   4 # All rights reserved.
   5 #
   6 # Script to age out OpenDMARC aggregate report data
   7
   8 ###
   9 ### Setup
  10 ###
  11
  12 use strict;
  13 use warnings;
  14
  15 use DBI;
  16 use File::Basename;
  17 use Getopt::Long;
  18 use IO::Handle;
  19 use POSIX;
  20
  21 require DBD::mysql;
  22
  23 # general
  24 my $progname      = basename($0);
  25 my $version       = "1.3.1";
  26 my $verbose       = 0;
  27 my $helponly      = 0;
  28 my $showversion   = 0;
  29 my $alltables     = 0;
  30
  31 my $minmsg;
  32 my $rowcount;
  33
  34 my $dbi_s;
  35 my $dbi_h;
  36 my $dbi_a;
  37
  38 # DB parameters
  39 my $def_dbhost    = "localhost";
  40 my $def_dbname    = "opendmarc";
  41 my $def_dbuser    = "opendmarc";
  42 my $def_dbpasswd  = "opendmarc";
  43 my $def_dbport    = "3306";
  44 my $dbhost;
  45 my $dbname;
  46 my $dbuser;
  47 my $dbpasswd;
  48 my $dbport;
  49
  50 my $dbscheme      = "mysql";
  51
  52 my $def_maxage    = 180;
  53
  54 my $rows;
  55 my $maxage;
  56
  57 ###
  58 ### NO user-serviceable parts beyond this point
  59 ###
  60
  61 sub usage
  62 {
  63         print STDERR "$progname: usage: $progname [options]\n";
  64         print STDERR "\t--alltables        expire rows from all tables\n";
  65         print STDERR "\t--dbhost=host      database host [$def_dbhost]\n";
  66         print STDERR "\t--dbname=name      database name [$def_dbname]\n";
  67         print STDERR "\t--dbpasswd=passwd  database password [$def_dbpasswd]\n";
  68         print STDERR "\t--dbport=port      database port [$def_dbport]\n";
  69         print STDERR "\t--dbuser=user      database user [$def_dbuser]\n";
  70         print STDERR "\t--expire=days      expiration time, in days [$def_maxage]\n";
  71         print STDERR "\t--help             print help and exit\n";
  72         print STDERR "\t--verbose          verbose output\n";
  73         print STDERR "\t--version          print version and exit\n";
  74 }
  75
  76 # parse command line arguments
  77 my $opt_retval = &Getopt::Long::GetOptions ('alltables!' => \$alltables,
  78                                             'dbhost=s' => \$dbhost,
  79                                             'dbname=s' => \$dbname,
  80                                             'dbpasswd=s' => \$dbpasswd,
  81                                             'dbport=s' => \$dbport,
  82                                             'dbuser=s' => \$dbuser,
  83                                             'expire=i' => \$maxage,
  84                                             'help!' => \$helponly,
  85                                             'verbose!' => \$verbose,
  86                                             'version!' => \$showversion,
  87                                            );
  88
  89 if (!$opt_retval || $helponly)
  90 {
  91         usage();
  92
  93         if ($helponly)
  94         {
  95                 exit(0);
  96         }
  97         else
  98         {
  99                 exit(1);
 100         }
 101 }
 102
 103 if ($showversion)
 104 {
 105         print STDOUT "$progname v$version\n";
 106         exit(0);
 107 }
 108
 109 # apply defaults
 110 if (!defined($dbhost))
 111 {
 112         if (defined($ENV{'OPENDMARC_DBHOST'}))
 113         {
 114                 $dbhost = $ENV{'OPENDMARC_DBHOST'};
 115         }
 116         else
 117         {
 118                 $dbhost = $def_dbhost;
 119         }
 120 }
 121
 122 if (!defined($dbname))
 123 {
 124         if (defined($ENV{'OPENDMARC_DB'}))
 125         {
 126                 $dbname = $ENV{'OPENDMARC_DB'};
 127         }
 128         else
 129         {
 130                 $dbname = $def_dbname;
 131         }
 132 }
 133
 134 if (!defined($dbpasswd))
 135 {
 136         if (defined($ENV{'OPENDMARC_PASSWORD'}))
 137         {
 138                 $dbpasswd = $ENV{'OPENDMARC_PASSWORD'};
 139         }
 140         else
 141         {
 142                 $dbpasswd = $def_dbpasswd;
 143         }
 144 }
 145
 146 if (!defined($dbport))
 147 {
 148         if (defined($ENV{'OPENDMARC_PORT'}))
 149         {
 150                 $dbport = $ENV{'OPENDMARC_PORT'};
 151         }
 152         else
 153         {
 154                 $dbport = $def_dbport;
 155         }
 156 }
 157
 158 if (!defined($dbuser))
 159 {
 160         if (defined($ENV{'OPENDMARC_USER'}))
 161         {
 162                 $dbuser = $ENV{'OPENDMARC_USER'};
 163         }
 164         else
 165         {
 166                 $dbuser = $def_dbuser;
 167         }
 168 }
 169
 170 if (!defined($maxage))
 171 {
 172         if (defined($ENV{'OPENDMARC_MAXAGE'}))
 173         {
 174                 $maxage = $ENV{'OPENDMARC_MAXAGE'};
 175         }
 176         else
 177         {
 178                 $maxage = $def_maxage;
 179         }
 180 }
 181
 182 # sanity check
 183 if ($maxage <= 0)
 184 {
 185         print STDERR "$progname: invalid expiration time\n";
 186         exit(1);
 187 }
 188
 189 #
 190 # Let's go!
 191 #
 192
 193 if ($verbose)
 194 {
 195         print STDERR "$progname: started at " . localtime() . "\n";
 196 }
 197
 198 my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
 199               ";host=" . $dbhost . ";port=" . $dbport;
 200
 201 $dbi_h = DBI->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError => 0 });
 202 if (!defined($dbi_h))
 203 {
 204         print STDERR "$progname: unable to connect to database: $DBI::errstr\n";
 205         exit(1);
 206 }
 207
 208 if ($verbose)
 209 {
 210         print STDERR "$progname: connected to database\n";
 211 }
 212
 213 #
 214 # Expire messages
 215 #
 216
 217 if ($verbose)
 218 {
 219         print STDERR "$progname: expiring messages older than $maxage day(s)\n";
 220 }
 221
 222 $dbi_s = $dbi_h->prepare("DELETE FROM messages WHERE date <= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL ? DAY)");
 223 $rows = $dbi_s->execute($maxage);
 224 if (!$rows)
 225 {
 226         print STDERR "$progname: DELETE failed: " . $dbi_h->errstr;
 227         $dbi_s->finish;
 228         $dbi_h->disconnect;
 229         exit(1);
 230 }
 231 elsif ($verbose)
 232 {
 233         if ($rows eq "0E0")
 234         {
 235                 print STDOUT "$progname: no rows deleted\n";
 236         }
 237         else
 238         {
 239                 print STDOUT "$progname: $rows row(s) deleted\n";
 240         }
 241 }
 242
 243 $dbi_s->finish;
 244
 245 #
 246 # Expire signatures
 247 #
 248
 249 $dbi_s = $dbi_h->prepare("SELECT MIN(id) FROM messages");
 250 if (!$dbi_s->execute)
 251 {
 252         print STDERR "$progname: SELECT failed: " . $dbi_h->errstr;
 253         $dbi_s->finish;
 254         $dbi_h->disconnect;
 255         exit(1);
 256 }
 257
 258 while ($dbi_a = $dbi_s->fetchrow_arrayref())
 259 {
 260         $minmsg = $dbi_a->[0];
 261 }
 262
 263 #
 264 # We might have emptied the messages table
 265 #
 266 $dbi_s->finish;
 267
 268 if (!defined($minmsg))
 269 {
 270         $dbi_s = $dbi_h->prepare("SELECT COUNT(id) FROM messages");
 271         if (!$dbi_s->execute)
 272         {
 273                 print STDERR "$progname: SELECT failed: " . $dbi_h->errstr;
 274                 $dbi_s->finish;
 275                 $dbi_h->disconnect;
 276                 exit(1);
 277         }
 278
 279         while ($dbi_a = $dbi_s->fetchrow_arrayref())
 280         {
 281                 $rowcount = $dbi_a->[0];
 282         }
 283
 284         $dbi_s->finish;
 285
 286         if (defined($rowcount) && $rowcount == 0)
 287         {
 288                 $dbi_s = $dbi_h->prepare("TRUNCATE TABLE signatures");
 289                 if ($dbi_s->execute)
 290                 {
 291                         print STDERR "$progname: TRUNCATE failed: " . $dbi_h->errstr;
 292                         $dbi_s->finish;
 293                         $dbi_h->disconnect;
 294                         exit(1);
 295                 }
 296
 297                 $dbi_s->finish;
 298         }
 299
 300         $dbi_h->disconnect;
 301         exit(1);
 302 }
 303 else
 304 {
 305         if ($verbose)
 306         {
 307                 print STDERR "$progname: expiring signatures on expired messages (id < $minmsg)\n";
 308         }
 309
 310         $dbi_s = $dbi_h->prepare("DELETE FROM signatures WHERE message < ?");
 311         $rows = $dbi_s->execute($minmsg);
 312         if (!$rows)
 313         {
 314                 print STDERR "$progname: DELETE failed: " . $dbi_h->errstr;
 315                 $dbi_s->finish;
 316                 $dbi_h->disconnect;
 317                 exit(1);
 318         }
 319         elsif ($verbose)
 320         {
 321                 if ($rows eq "0E0")
 322                 {
 323                         print STDOUT "$progname: no rows deleted\n";
 324                 }
 325                 else
 326                 {
 327                         print STDOUT "$progname: $rows row(s) deleted\n";
 328                 }
 329         }
 330
 331         $dbi_s->finish;
 332 }
 333
 334 #
 335 # Expire request data
 336 #
 337
 338 if ($verbose)
 339 {
 340         print STDERR "$progname: expiring request data older than $maxage days\n";
 341 }
 342
 343 $dbi_s = $dbi_h->prepare("DELETE FROM requests WHERE lastsent <= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL ? DAY) AND NOT lastsent = '1971-01-01 00:00:01'");
 344 $rows = $dbi_s->execute($maxage);
 345 if (!$rows)
 346 {
 347         print STDERR "$progname: DELETE failed: " . $dbi_h->errstr . "\n";
 348         $dbi_s->finish;
 349         $dbi_h->disconnect;
 350         exit(1);
 351 }
 352 elsif ($verbose)
 353 {
 354         if ($rows eq "0E0")
 355         {
 356                 print STDOUT "$progname: no rows deleted\n";
 357         }
 358         else
 359         {
 360                 print STDOUT "$progname: $rows row(s) deleted\n";
 361         }
 362 }
 363
 364 $dbi_s->finish;
 365
 366 if ($alltables)
 367 {
 368         if ($verbose)
 369         {
 370                 print STDERR "$progname: expiring unneeded domain data\n";
 371         }
 372
 373         $dbi_s = $dbi_h->prepare("DELETE FROM domains WHERE id NOT IN
 374                 (SELECT DISTINCT domain FROM requests) AND id NOT IN
 375                 (SELECT DISTINCT from_domain FROM messages) AND id NOT IN
 376                 (SELECT DISTINCT env_domain FROM messages) AND id NOT IN
 377                 (SELECT DISTINCT policy_domain FROM messages) AND id NOT IN
 378                 (SELECT DISTINCT domain FROM signatures)");
 379         $rows = $dbi_s->execute();
 380         if (!$rows)
 381         {
 382                 print STDERR "$progname: DELETE failed: " . $dbi_h->errstr . "\n";
 383                 $dbi_s->finish;
 384                 $dbi_h->disconnect;
 385                 exit(1);
 386         }
 387         elsif ($verbose)
 388         {
 389                 if ($rows eq "0E0")
 390                 {
 391                         print STDOUT "$progname: no rows deleted\n";
 392                 }
 393                 else
 394                 {
 395                         print STDOUT "$progname: $rows row(s) deleted\n";
 396                 }
 397         }
 398
 399         if ($verbose)
 400         {
 401                 print STDERR "$progname: expiring unneeded IP data\n";
 402         }
 403
 404         $dbi_s = $dbi_h->prepare("DELETE FROM ipaddr WHERE id NOT IN
 405                 (SELECT DISTINCT ip FROM messages)");
 406         $rows = $dbi_s->execute();
 407         if (!$rows)
 408         {
 409                 print STDERR "$progname: DELETE failed: " . $dbi_h->errstr . "\n";
 410                 $dbi_s->finish;
 411                 $dbi_h->disconnect;
 412                 exit(1);
 413         }
 414         elsif ($verbose)
 415         {
 416                 if ($rows eq "0E0")
 417                 {
 418                         print STDOUT "$progname: no rows deleted\n";
 419                 }
 420                 else
 421                 {
 422                         print STDOUT "$progname: $rows row(s) deleted\n";
 423                 }
 424         }
 425
 426         if ($verbose)
 427         {
 428                 print STDERR "$progname: expiring unneeded reporter data\n";
 429         }
 430
 431         $dbi_s = $dbi_h->prepare("DELETE FROM reporters WHERE id NOT IN
 432                 (SELECT DISTINCT reporter FROM messages)");
 433         $rows = $dbi_s->execute();
 434         if (!$rows)
 435         {
 436                 print STDERR "$progname: DELETE failed: " . $dbi_h->errstr . "\n";
 437                 $dbi_s->finish;
 438                 $dbi_h->disconnect;
 439                 exit(1);
 440         }
 441         elsif ($verbose)
 442         {
 443                 if ($rows eq "0E0")
 444                 {
 445                         print STDOUT "$progname: no rows deleted\n";
 446                 }
 447                 else
 448                 {
 449                         print STDOUT "$progname: $rows row(s) deleted\n";
 450                 }
 451         }
 452 }
 453
 454 #
 455 # All done!
 456 #
 457
 458 if ($verbose)
 459 {
 460         print STDERR "$progname: terminating at " . localtime() . "\n";
 461 }
 462
 463 $dbi_h->disconnect;
 464
 465 exit(0);