Automatically Updating Backup MX User Tables

Posted on by dc84gth.

Automatically Updating Backup MX User Tables

If you are following along with the guide found here, you've probably figured out that its kind of a pain in the ass to go back and update the allowed_emails hash every time you add a new email.

If so you're in luck, with a few python scripts and a cronjob we can automate the process, This tutorial was designed around a iRedMail installation and database layout, it can be easily adapted to another all in one solution though. Before we can update our information we need to find it, in the case of iRedMail we can find out information in the tables:

  • alias
  • domain

Quickly using some google-fu i was able to find a python script to pull down the contents of a MySQL table: pull_emails.py

# pull_emails.py - Fetch and display the emails a iRedMail server services 
# Credit: https://scriptingmysql.wordpress.com/2011/09/09/retrieving-data-from-mysql-via-python/ 
# Pull's current emails serviced by a iRedMail server. 
import MySQLdb 
import sys 

db_master = "mail.yourserver.com"  
db_user = "db_user"  
db_passwd = "your password"  
db_database = "vmail" # Default database for iRedMail  

connection = MySQLdb.connect (host = db_master, user = db_user, passwd = db_passwd, db = db_database)  
cursor = connection.cursor ()  
cursor.execute ("SELECT address FROM `alias`;")  
data = cursor.fetchall ()  
for row in data :  
        print row[0] + " OK"  
cursor.close ()  
connection.close ()  
sys.exit()  

And then we do the same thing for domains: pull_domains.py

# pull_domains.py - Fetch and display the domains a iRedMail server services 
# Credit: https://scriptingmysql.wordpress.com/2011/09/09/retrieving-data-from-mysql-via-python/
# Pull's current domains serviced by a iRedMail server. 
import MySQLdb 
import sys 

db_master = "mail.yourserver.com"  
db_user = "db_user"  
db_passwd = "your password"  
db_database = "vmail" # Default database for iRedMail  

connection = MySQLdb.connect (host = db_master, user = db_user, passwd = db_passwd, db = db_database)  
cursor = connection.cursor ()  
cursor.execute ("SELECT domain FROM `domain`;")  
data = cursor.fetchall ()  
for row in data :  
        print row[0] + " OK"  
cursor.close ()  
connection.close ()  
sys.exit()  

We can achieve a proper transport map file by adapting one line in pull_domains.py to something like this: make_transport_map.py

for row in data :
        print row[0] + " smtp:[mail.yourdomain.com]:587"

Please remember that unless postfix is configured to use SSL port 587 will not work for you, submit using port 25 instead

Now with the scripts in order, we can create another script to redirect the output of the script to our files allowed_emails, allowed_domains and transport_map after that we can go ahead and postmap the files so that postfix can read it: update_postfix_hashes.sh

POSTFIX_DIR="/etc/postfix/"
python $POSTFIX_DIR/pull_emails.py > $POSTFIX_DIR/allowed_emails
python $POSTFIX_DIR/pull_domains.py > $POSTFIX_DIR/allowed_domains
python $POSTFIX_DIR/make_transport_map.py > $POSTFIX_DIR/transport_map
# Create db files.
postmap $POSTFIX_DIR/allowed_domains $POSTFIX_DIR/allowed_emails $POSTFIX_DIR/transport_map
# Restart postfix
/etc/init.d/postfix restart

after all that, all thats left to do is make a new cronjob

sudo crontab -e

0 3 * * * bash /etc/postfix/update_postfix_hashes.sh

Comments

Recently added