Editing Infrastructure Machines (section)

==== process flows from bwdb2 ====
On traffic database server (bwdb):

<pre>crontab -e
#import sql from bwdb2
10,25,40,55 * * * * /usr/home/flowbin/processsql.pl</pre>

Add access to mysql: 
<pre>mysql -u root -p 
grant all on traffic.* to bwdb2@localhost identified by 's1lver4d';
</pre> 

<pre>cat > /usr/home/flowbin/processsql.pl

#!/usr/bin/perl

#use strict;
#$debug=1;
#$dry=1;

my $sqldir = "/usr/home/bwdb2/pending";
my $mysql = '/usr/local/bin/mysql';
my @err;
unless ($dry) {
    if (-e "$sqldir/.lock") {
        open(FILE, "$sqldir/.lock");
        my $pid = <FILE>;
        chomp($pid);
        close(FILE);
        if (kill(0, $pid)) {
            #another process is using the queue, bail out
            exit(0);
        }
        else {
            #dead lock file, remove it
            `rm $sqldir/.lock`;
        }
    }
    open(FILE, "> $sqldir/.lock");
    print FILE "$$\n";
    close(FILE);
}

opendir(DIR, $sqldir);
my @files = readdir(DIR);
closedir(DIR);

foreach my $file (sort @files) {
   next unless $file =~ /done$/;
   my $r = `bzcat $sqldir/$file | $mysql -u bwdb2 -ps1lver4d traffic`;
   unless ($?==0) {
      push @err, "bzcat $sqldir/$file | $mysql -u bwdb2 -pxxxxx traffic ($r)";
   }
   else {
      `rm $sqldir/$file`;
   }
}

`rm $sqldir/.lock` unless $dry;

if (@err) {
   email_support('bwdb: processsql.pl error',join "\n", @err);
}

sub email_support {
    my $subj=shift;
    my $body=shift;
    use Mail::Sendmail;

    # prepare message
    my %mail = (
        To      => 'dave@johncompanies.com',
        From    => 'support@johncompanies.com',
        Subject => $subj,
        Message => $body,
        smtp    => 'mail.johncompanies.com',
    );
    sendmail(%mail) || warn "Error: $Mail::Sendmail::error";
}

sub debug {
    my $message = shift;
    if ($debug) {
        print "$message\n";
    }
}
</pre>

 chmod 0700 /usr/home/flowbin/processsql.pl

Make sure bwdb is reachable from the outside only to bwdb2:

On nat, add to <tt>/etc/ipnat.rules</tt>
<pre># bwdb
bimap fxp0 10.1.4.203/32 -> 69.55.233.199/32</pre>

Reload:
 ipnat -C -F -f /etc/ipnat.rules

Setup firewall rule on firewall:
 ipfw add 00094 allow ip from 66.181.18.5 to 69.55.233.199 22
 ipfw add 00094 deny ip from any to 69.55.233.199

Setup firewall on bwdb to restrict access now that it's nat'd:
<pre>
cat >> /usr/local/etc/rc.d/boot.sh
ipfw add 1 allow tcp from any to any established
ipfw add 2 allow ip from 10.1.4.0/24,66.181.18.5,69.55.233.195 to me 22
ipfw add 3 allow ip from 10.1.4.5 to me 3306
ipfw add 4 allow ip from 69.55.225.225 53 to me 
ipfw add 5 allow ip from 69.55.230.2 25 to me 
ipfw add 6 allow ip from me to me 4444
ipfw add 7 allow icmp from any to me
ipfw add 8 allow udp from 10.1.4.203 to 10.1.4.203 dst-port 4444
ipfw add 9 allow udp from 10.1.4.5 to me 161
ipfw add 100 deny ip from any to me
</pre> 

 chmod 0700 /usr/local/etc/rc.d/boot.sh

From bwdb2, add ssh key:
 cat /root/.ssh/id_dsa.pub | ssh 69.55.233.199 'cat - >> /root/.ssh/authorized_keys' 

Confirm no password access:
 ssh 69.55.233.199 hostname