Bash Scripts for cbpolicyd

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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

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

where "query string" is an sql statement you have tested on sql interactive cli [code] sqlite3 /opt/zimbra/data/cbpolicyd/db/cbpolicyd.sqlitedb [/code]

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') ;


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 Cambiami.001"
Jump to: navigation, search