A script to sync mailman members from a MySQL database to a mailman server over SSH.

* Still needs config xml loading

#!/usr/bin/python
import os
import os.path
import re
import sys
import datetime
import tempfile
import MySQLdb
from xml.dom import minidom
 
#
# Default config settings, can be overwritten
#  in the xml config section
#
# Setting False will force to stop running ssh commands
SSH_ENABLE=True
# Log Level (0=Quiet, 6 = Everything)
LOG_LEVEL=5
# Name to use in logs
LOG_NAME="mailmansync"
# The date format to use in logs
LOG_DATEFORMAT="%Y-%m-%d %H:%M:%S"
# --- - - - - - - - - - ---- #
 
 
# File to read mailman list stuff from
MAILLIST_FILE="maillists.xml"
 
# Log levels available
LOG_OFF=0
LOG_FATAL=1
LOG_ERROR=2
LOG_WARN=3
LOG_INFO=4
LOG_DEBUG=5
LOG_ALL=6
 
# Global args (Set from main)
args = None
 
def main(argv):
    # No argparse on paris, set the useful defaults
    global args
    args = Args()
    setattr(args, "xmlfile", MAILLIST_FILE)
    setattr(args, "sshenable", SSH_ENABLE)
    setattr(args, "logname", LOG_NAME)
    setattr(args, "loglvl", LOG_LEVEL)
    setattr(args, "logdateformat", LOG_DATEFORMAT)
 
 
 
    if not os.path.exists(args.xmlfile):
        log(LOG_FATAL, "Cant find mailist xml file:" + args.xmlfile)
        return
 
    if args.sshenable is False:
            log(LOG_WARN, "Running with ssh commands disabled")
 
    command_syncall()
 
    return
 
def command_syncall():
    # Load the mail sync definitions
    maillist = loadMailListXML()
    log(LOG_DEBUG, "Loaded " + str(len(maillist)) + " lists for processing")
 
    for list in maillist:
        log(LOG_INFO, "Processing list " + list.name + " Db: " + list.dbconnstr)
 
        msg = "Datasource SQL: "
        if len(list.sql.split("\n"))>1:
            msg += "\n\t"
        log(LOG_DEBUG, msg + list.sql)
 
        # Run the sql on the db to get the email addresses
        pdb = Pdb(list.dbconn)
        pdb._connect()
        rows = pdb.query(list.sql)
        pdb._close()
 
        log(LOG_DEBUG, "Databased returned " + str(len(rows)) + " records")
        # Remove all but the 'email' column, remove empties, and add newline
        if list.emailfield != "email":
            log(LOG_DEBUG, "Using emailfield: " + list.emailfield)
        emails = map(lambda x: x[list.emailfield], rows)
        emails = filter(lambda x: x != "", emails)
        emails = map(lambda x: x + '\n', emails)
        log(LOG_INFO, list.name + ": " + str(len(emails)) + " members")
 
        # Output all emails to file, copy to remote host
        tmpFile = tempfile.NamedTemporaryFile()
        tmpRemoteFilepath = list.sshconn.tmpdir + "/" + os.path.basename(tmpFile.name)
        tmpFile.writelines(emails)
        tmpFile.flush()
        list.sshconn.scp(tmpFile.name, list.sshconn.tmpdir)
 
        # Run the 'sync_members' command
        list.sshconn.syncmembers(list.name, tmpRemoteFilepath)
 
        # Delete temp files
        tmpFile.close()
        #list.sshconn.rmfile(tmpRemoteFilepath)
 
def log(lvl, msg):
    if args.loglvl >= lvl:
        if msg == "":
            return
        if msg == "\r\n":
            print ""
            return
 
        if args.loglvl >= LOG_DEBUG:
            msg = "[" + loglvlstr(lvl) + "] " + msg
 
        msg = args.logname + " " + msg
        msg = datetime.datetime.now().strftime(args.logdateformat) + " " + msg
 
        print msg
 
def loglvlstr(lvl):
    return {
        1 : "FATAL",
        2 : "ERROR",
        3 : "WARN ",
        4 : "INFO ",
        5 : "DEBUG",
        6 : "ALL  ",
    }[lvl]
 
class Args(dict):
    pass
 
class MailList:
    def __init__(self, name):
        self.name = name
        self.connstr = ""
        self.email = ""
        self.sql = ""
        self.dbconn = None
        self.sshconn = None
 
class DbConnection:
    def __init__(self, name):
        self.name = name
        self.type = ""
        self.host = ""
        self.db = ""
        self.user = ""
        self.passwd = ""
 
class SshConnection:
    def __init__(self, name):
        self.name = name
        self.host = ""
        self.user = ""
        self.sshkey = ""
        self.cmdssh = ""
        self.cmdscp = ""
        self.cmdsyncmembers = ""
        self.tmpdir = ""
 
    def rmfile(self, remotefile):
        self.sshcmd("rm -f " + remotefile)
 
    def runcmd(self, runcmd):
        # Warn if ssh cmds disabled
        if args.sshenable is False:
            log(LOG_WARN, "**SSH CMDS DISABLED***")
        log(LOG_DEBUG, runcmd)
        # Don't put to tty for less then info
        if (args.loglvl<LOG_INFO):
            runcmd += " > /dev/null"
        # Run the command if enabled
        output = None
        if args.sshenable is True:
            output = os.popen(runcmd).read()
            output = lineclip(clipstr = output, new_delim = "\n\t")
            log(LOG_DEBUG, output)
        return output
 
    def scp(self, src, destfolder):
        cmd = self.cmdscp + " -i " + self.sshkey + " "
        cmd += src + " "
        cmd += self.user + "@" + self.host + ":"
        cmd += destfolder + "/"
        return self.runcmd(cmd)
 
    def sshcmd(self, runcmd):
        cmd = self.cmdssh  + " -i " + self.sshkey + " "
        cmd += self.user + "@" + self.host + " "
        cmd += runcmd
        return self.runcmd(cmd)
 
    def syncmembers(self, listname, file):   
        cmd = self.cmdsyncmembers + " -w=no -g=no -a=no "
        cmd += " -f " + file + " "
        cmd += listname
 
        log(LOG_DEBUG, "Running sync_members:")
        lines = self.sshcmd(cmd).split("\n")
        log(LOG_DEBUG, "Finished sync_members.")
 
        # Count the added/removed members
        added = [l for l in lines if l.strip().startswith("Added")]
        removed = [l for l in lines if l.strip().startswith("Removed")]        
        log(LOG_INFO, "Members Added: " + str(len(added)) + " Removed: " + str(len(removed)))
 
class Pdb:
    def __init__(self, dbconn):
        self.dbconn = dbconn
 
    def _connect(self):
        self.db = MySQLdb.connect(host=self.dbconn.host, 
                                    user=self.dbconn.user,
                                    passwd=self.dbconn.passwd,
                                    db=self.dbconn.db)
 
    def _close(self):
        self.db.close()
 
    def query(self, query):
        cursor=self.db.cursor()
        cursor.execute(query)
        fields = map(lambda x:x[0], cursor.description)
        rows = [dict(zip(fields,row))   for row in cursor.fetchall()]
        cursor.close()
        return rows
 
 
def loadMailListXML():
    xmldoc = minidom.parse(args.xmlfile)
 
 
    #
    # Load the ssh connections
    #
    sshconnlist = []
    root = xmldoc.getElementsByTagName("sshconnections")
    for t in root:
        j = t.getElementsByTagName("sshconnection")
        for i in j:
            name = i.getAttribute("name")
            sshconn = SshConnection(name)
            sshconn.host = i.getElementsByTagName('host')[0].childNodes[0].data
            sshconn.user = i.getElementsByTagName('user')[0].childNodes[0].data
            sshconn.sshkey = i.getElementsByTagName('sshkey')[0].childNodes[0].data
            sshconn.tmpdir = i.getElementsByTagName('tmpdir')[0].childNodes[0].data
            sshconn.cmdssh = i.getElementsByTagName('ssh')[0].childNodes[0].data
            sshconn.cmdscp = i.getElementsByTagName('scp')[0].childNodes[0].data
            sshconn.cmdsyncmembers = i.getElementsByTagName('syncmembers')[0].childNodes[0].data
            sshconnlist.append(sshconn)
 
    #
    # Load the database connections
    #
    dbconnlist = []
    root = xmldoc.getElementsByTagName("dbconnections")
    for t in root:
        j = t.getElementsByTagName("dbconnection")
        for i in j:
            name = i.getAttribute("name")
            dbconn = DbConnection(name)
            dbconn.type = i.getElementsByTagName('type')[0].childNodes[0].data
            dbconn.host = i.getElementsByTagName('host')[0].childNodes[0].data
            dbconn.db = i.getElementsByTagName('db')[0].childNodes[0].data
            dbconn.user = i.getElementsByTagName('user')[0].childNodes[0].data
            dbconn.passwd = i.getElementsByTagName('pass')[0].childNodes[0].data
            dbconnlist.append(dbconn)
 
    #
    # Load all the mailling lists, match connstr to
    #  an actual connection class
    #
    maillist = []
    root = xmldoc.getElementsByTagName("maillists")
    for t in root:
        j = t.getElementsByTagName("list")
        for i in j:
            name = i.getAttribute("name")
            list = MailList(name)          
            list.sshconnstr = i.getAttribute("sshconn")
            list.dbconnstr = i.getAttribute("dbconn")
            list.emailfield = "email"
            list.email = i.getElementsByTagName('email')[0].childNodes[0].data           
            list.sql = lineclip(clipstr = i.getElementsByTagName('sql')[0].childNodes[0].data, new_delim = "\n\t")
            if i.getElementsByTagName('sql')[0].hasAttribute("emailfield"):
                list.emailfield = i.getElementsByTagName('sql')[0].getAttribute("emailfield")
            list.sshconn = [x for x in sshconnlist if x.name == list.sshconnstr][0]
            list.dbconn = [x for x in dbconnlist if x.name == list.dbconnstr][0]
 
            if list.dbconn is not None and list.sshconn is not None:
                maillist.append(list)
            else:
                print "Bad ssh or db connection string for " + list.name
 
    return maillist
 
def lineclip(clipstr, split_delim = "\n", new_delim = "\n"):
    # break lines into array
    lines = clipstr.split(split_delim)
    # strip the line down
    lines = [line.strip() for line in lines]
    # remove empty lines
    lines = filter(lambda x: x != "", lines)
    # join back into a string
    line = new_delim.join(lines)
 
    return line
 
 
if __name__ == "__main__":
   main(sys.argv[1:]) 

<mailsync>
    <config>
        <sshenable>True</sshenable>
        <loglevel>5</loglevel>
        <logname>mailmansync</logname>
        <logdateformat>%Y-%m-%d %H:%M:%S</logdateformat>
    </config>
    <sshconnections>
        <sshconnection name="mailman">
            <host>mailman.newioit.com.au</host>
            <user>mailman</user>
            <sshkey>sshkey</sshkey>
            <tmpdir>/tmp</tmpdir>
            <scp>/usr/bin/scp</scp>
            <ssh>/usr/bin/ssh</ssh>
            <syncmembers>/usr/lib/mailman/bin/sync_members</syncmembers>
        </sshconnection>
    </sshconnections>
    <dbconnections>
        <dbconnection name="newioit_staff">
            <type>mysql</type>
            <host>idm.newioit.com.au</host>
            <db>staff</db>
            <user>staff</user>
            <pass>mypass</pass>
        </dbconnection>
    </dbconnections>
    <maillists>
        <list name="newioit.test" dbconn="newioit_staff" sshconn="mailman">
            <email>mail.test@newioit.com.au</email>
            <sql>
                select 'test@newioit.com.au' as email
            </sql>
        </list>
        <list name="newioit.techy" dbconn="newioit_staff" sshconn="mailman">
            <email>mail.techy@newioit.com.au</email>
            <sql emailfield="usyd_email">
                SELECT usyd_email, unikey FROM tblStaff 
                inner join `relGroupStaff` on tblStaff.id=`relGroupStaff`.`staffFK`
                inner join `tblGroup` on tblGroup.id=`relGroupStaff`.`groupFK`
                WHERE tblGroup.name = 'techy'
            </sql>
        </list>
    </maillists>
</mailsync>