View source for Bash Scripts for cbpolicyd

==Introduction==

You can do many things using policies, but not all.

Here you can find some bash examples, a bare starting point to implement new functionalities.

==How to query sqlite==



Basically, you can

 echo "query string;"  | sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb


where "query string" is an sql statement you have tested on sql interactive cli

 sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb

==Trapping abused sasl users==

I was interested in trap sasl senders, when sasl credentials are abused and you have many connections per second each coming from a different ip.

The table to use is session_tracking.

To search for sasl username and orininating ip, you can isse:


 echo "select distinct  SASLUsername, ClientAddress from session_tracking;" | sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb | more


If you want restrict it to last 10 minutes:


 echo "select distinct  SASLUsername, ClientAddress from session_tracking where ( (strftime('%s','now') - UnixTimestamp) < 600) | sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb | more



You have to filter out all connections coming from mtas that can send to your server w/out authentication:


 select distinct  SASLUsername, ClientAddress from session_tracking where ( (strftime('%s','now') - UnixTimestamp) < 3600) and (ClientAddress != '1.2.3.4') and (ClientAddress != '4.3.2.1') ;

==The script==

This is the complete bash script example:

 #! /bin/bash
 
 rm -f /tmp/temp
 
 echo "select distinct  SASLUsername, ClientAddress 
 from session_tracking 
 where ( (strftime('%s','now') - UnixTimestamp) < 600) 
 and (ClientAddress != 'a.b.c.d') and (ClientAddress != '1.2.3.4') ;" | sqlite3 /opt/zimbra/data/cbpolicyd /db/cbpolicyd.sqlitedb | cut -f 1 -d '|' \
            | sort | uniq -c | awk '{ if( $1 > 5 ) print $2 " has sent from: " $1 " uniq ip " }'  > /tmp/temp
 
 
 [ -s /tmp/temp ] && cat /tmp/temp  |  mail -s "alarm: botnet attack botnet in act" alarm@yourdomain
 
 [ -s /tmp/temp ] && user=`cat /tmp/temp |  awk '{ print $1 }'` && su - zimbra -c "zmprov sp $user changeme"



{{Article Footer|Zimbra Collaboration Suite 8.4|11/15/2013}}

[[Category:Policyd]]
[[Category:Anti-spam]]