As mentioned in my previous post, I now have my own mail server running, built from scratch with Arch Linux, Postfix, Dovecot, etc. It has been in operation for a couple of weeks now, and I’m super happy with it. I Should have done this years ago…
Anyway, this being a DIY project and Linux, it does mean that you need to configure many things yourself, and it helps (a lot!) if you have some degree of scripting skills to automate stuff. There is no need to re-invent the wheel over and over again, but I do find giving a project such as this some personal flavour with my own scripting skills, is very rewarding indeed.
Bye bye, user
As you might know, a DIY mail server on Linux is constructed from a handful of components, each one with a specific task or purpose. To give a brief idea, in my setup Postfix acts as the MTA (Message Transfer Agent), receiving and sending mail over SMTP. Dovecot is responsible for actually storing the mail for the virtual mail users, and serving mail clients over IMAP and POP. One of these clients is RoundCube, in my view one of the best web mail clients around (but lacking a working CalDAV calendar, as mentioned in my previous post). Settings are stored in MySQL databases (MariaDB actually). Mail domain, mailbox and alias management is centralized with PostfixAdmin. Etc etc…
Whenever an user is deleted from PostfixAdmin, it is removed from the Postfix database and thus no longer part of the mail system. However, since the actual mails are stored and served by Dovecot, these are NOT deleted. Same goes for RoundCube for instance: the deleted user is NOT removed from the RoundCube database. Wouldn’t it be nice if these stale files and settings can be automatically removed? I wrote a little Python script that does just that.
This script will remove the folders of deleted users, and purges them from the RoundCube database. Note that this script is written reflecting my own setup, you might want to review it first to see if it needs adapting to your mail server setup. You might be using a different mailbox folder, or other tables in the RoundCube database also need pruning? Maybe you want to keep some data for some reason?
I normally don’t script much in Python, so this was a good exercise for me. It might not be the best Python script ever written, but I’m content with it 😉
#!/usr/bin/python
# import
import glob, logging, MySQLdb, shutil
# initialize
LOGFILE = "/var/log/postfix/purgedeleted.log"
MAILPATH = "/var/vmail"
# main
COUNTER = 0
logging.basicConfig(filename=LOGFILE, format='%(asctime)s %(levelname)s: %(message)s', level=logging.INFO)
# get domains from postfix
postfix_db = MySQLdb.connect("localhost", "postfix_user", "postfix_password", "postfix_db")
cursor = postfix_db.cursor()
SQL = "SELECT domain FROM domain WHERE transport = '%s'" % ('virtual')
try:
cursor.execute(SQL)
DOMAINS = cursor.fetchall()
except:
logging.critical("Exited with error code 1: unable to read from postfix database.")
exit(1)
# get mailboxes per domain from postfix
for DOMAIN in DOMAINS:
SQL = "SELECT local_part FROM mailbox WHERE domain = '%s'" % (DOMAIN)
try:
cursor.execute(SQL)
POSTFIX = cursor.fetchall()
POSTFIXUSERS = []
for USER in POSTFIX:
POSTFIXUSERS.append(USER[0])
except:
logging.critical("Exited with error code 2: unable to read from postfix database.")
exit(2)
# prepare roundcube database
roundcube_db = MySQLdb.connect("localhost", "roundcube_user", "roundcube_password", "roundcube_db")
cursor.close()
cursor = roundcube_db.cursor()
# check all vmail folders
FOLDERS = glob.glob(MAILPATH + "/" + DOMAIN[0] + "/*")
for PATH in FOLDERS:
USER = PATH.split('/')[-1]
if not (USER in POSTFIXUSERS):
shutil.rmtree(PATH)
logging.info('Successfully removed %s.', PATH)
COUNTER += 1
# remove corresponding userid records from roundcube database
SQL = "SELECT user_id FROM users WHERE username = '%s'" % (USER + "@" + DOMAIN[0])
try:
cursor.execute(SQL)
FOUND = cursor.rowcount
except:
logging.error("Error code 3: unable to read from roundcube database.")
exit(3)
if (FOUND != 0):
USERID = cursor.fetchone()[0]
SQL = "DELETE FROM auth_tokens WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM cache WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM cache_index WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM cache_messages WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM cache_thread WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM carddav_contacts WHERE abook_id IN (SELECT id FROM carddav_addressbooks WHERE user_id = '%s')" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM carddav_group_user WHERE group_id IN (SELECT id FROM carddav_groups WHERE abook_id IN (SELECT id FROM carddav_addressbooks WHERE user_id = '%s'))" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM carddav_groups WHERE abook_id IN (SELECT id FROM carddav_addressbooks WHERE user_id = '%s')" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM carddav_addressbooks WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM contacts WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM contactgroupmembers WHERE contactgroup_id IN (SELECT contactgroup_id FROM contactgroups WHERE user_id = '%s')" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM contactgroups WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM dictionary WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM identities WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM searches WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM userlogins WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
SQL = "DELETE FROM users WHERE user_id = '%s'" % (USERID)
cursor.execute(SQL)
try:
roundcube_db.commit()
logging.info('Successfully removed %s from roundcube.', USER + "@" + DOMAIN[0])
except:
roundcube_db.rollback()
logging.error('Error code 4: unable to delete %s from roundcube.', USER + "@" + DOMAIN[0])
# close roundcube database
roundcube_db.close()
# close postfix database
postfix_db.close()
# end
logging.info('Purge finished, %d mail users removed.', COUNTER)
exit(0)Make sure the log file can be written in the location specified at the beginning of the script.
Save the script as purgedeleted.py (or any name you like) and make it executable (“sudo chmod +x purgedeleted.py”). Finally create an entry in your root’s crontab. Executing the script once a day, preferably during quiet hours, would be more than sufficient.
Marco