Get a list of domains with mail enabled.

[Frater]

I have an SMTP-proxy running on several Plesk-servers to protect them against spam (ASSP)
This proxy behaves better if it knows the domains for which it should accept mail.

For this purpose I wrote a script that checks the database each 15 minutes and generates a temporary file.
If that temporary file is different than the one in use by assp it will update the file.

This script works alright, but because I only know how I can extract all domains from the database it is also filled with domains that don't have mail enabled.
It's not a big deal really as it just accepts some extra domains which get rejected by postfix/qmail anyhow.
But still it looks better if only the mail enabled domains are shown.

I would like to replace this command:
mysql --skip-column-names -uadmin -p`cat /etc/psa/.psa.shadow` psa -e "SELECT domains.name FROM domains ORDER BY domains.name ASC; " | sort -u

with a command that only gives me the domains that are "mail-enabled"

 

[IgorG]

You can use following SQL query

select concat(mail.mail_name,"@",domains.name) as address from mail,domains,accounts where mail.dom_id=domains.id and mail.account_id=accounts.id order by address;

You will see list of all mailboxes for all domains.

Or just list of all domains with mailboxes:

select domains.name from mail,domains,accounts where mail.dom_id=domains.id and mail.account_id=accounts.id;

 

[Frater]

Hi Igor,

Thanks for reacting.
I tested both commands.

The first one gives me a list with "NULL" as output.

The 2nd one could do the job, but it would mean that a domain that has no mailboxes but a "catchall and redirect" would not be included. My proxy would not accept mail for such a domain. I don't have such domains, but don't know if I or someone else will configure one like that in the future.

Should the first command (after correction) work for such a domain? Because of lack of experience with mysql I have difficulty reading these statements.

If so, I would appreciate such a command.

 

[IgorG]

First query should work.

select concat(mail.mail_name,"@",domains.name) as address from mail,domains,accounts where mail.dom_id=domains.id and mail.account_id=accounts.id order by address;

List of all mailboxes should be in output.

 

[Frater]

Hi Igor,

I made a mistake with the double-quotes..

It turns out this command does work for domains without active mailboxes: "select domains.name from mail,domains,accounts where mail.dom_id=domains.id and mail.account_id=accounts.id;".

So, thanks!

I was afraid it wouldn't include those domains that forward all the mail to another address.
I'm not using that really and haven't configured such a domain for a long time.
I forgot that I would still need to configure a mailbox, but then this mailbox needs to be disabled so no mail is being kept on the server.

I just tested it on a domain and the mysql command now shows it.

BTW... here's the script

# ln -s /usr/local/sbin/assp_localdomains /etc/cron.15min/assp_localdomains
# cat /usr/local/sbin/assp_localdomains

#!/bin/sh

LOCALDOMAINS=/opt/ASSP/files/localdomains.txt
POSTFIXDIR=/etc/postfix
RELAYFILE=${POSTFIXDIR}/relay_domains
TRANSPORTFILE=${POSTFIXDIR}/transport
HEADER="# WARNING do not EDIT!!! \n# This file will be overwritten by $0 (`readlink -f $0`)"

FGROUP=`stat -c%G ${LOCALDOMAINS}`
FUSER=`stat -c%U ${LOCALDOMAINS}`

TMP=`mktemp`
chown ${FUSER}.${FGROUP} ${TMP}

echo -e "${HEADER}\n" >${TMP}
echo -e "################################################\n# Domains coming from Plesk\n################################################"  >>${TMP}

mysql  --skip-column-names -uadmin -p`cat /etc/psa/.psa.shadow` psa -e "select domains.name from mail,domains,accounts where mail.dom_id=domains.id and mail.account_id=accounts.id;" | sort -u >>${TMP}

echo -e "\n################################################" >>${TMP}
if [ -e "${RELAYFILE}" ] ; then
  echo -e "# Domains coming from ${RELAYFILE}\n################################################"  >>${TMP}
  awk '{print $1}' "${RELAYFILE}"  >>${TMP}
else
  echo -e "\n# ${RELAYFILE} does NOT exist!!\n################################################"  >>${TMP}
fi

if ! diff ${TMP} ${LOCALDOMAINS} >/dev/null 2>&1 ; then
  cp -p ${TMP}  ${LOCALDOMAINS}
  killall -HUP assp.pl
fi

rm -f ${TMP}