1. Please take a little time for this simple survey! Thank you for participating!
    Dismiss Notice
  2. Dear Pleskians, please read this carefully! New attachments and other rules Thank you!
    Dismiss Notice
  3. Dear Pleskians, I really hope that you will share your opinion in this Special topic for chatter about Plesk in the Clouds. Thank you!
    Dismiss Notice

Get a list of domains with mail enabled.

Discussion in 'Plesk 10.x for Linux Issues, Fixes, How-To' started by Frater, Oct 23, 2012.

  1. Frater

    Frater Regular Pleskian

    18
     
    Joined:
    Oct 17, 2011
    Messages:
    173
    Likes Received:
    3
    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"
     
  2. IgorG

    IgorG Forums Analyst Staff Member

    49
    24%
    Joined:
    Oct 27, 2009
    Messages:
    24,572
    Likes Received:
    1,243
    Location:
    Novosibirsk, Russia
    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;
     
  3. Frater

    Frater Regular Pleskian

    18
     
    Joined:
    Oct 17, 2011
    Messages:
    173
    Likes Received:
    3
    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.
     
    Last edited: Oct 23, 2012
  4. IgorG

    IgorG Forums Analyst Staff Member

    49
    24%
    Joined:
    Oct 27, 2009
    Messages:
    24,572
    Likes Received:
    1,243
    Location:
    Novosibirsk, Russia
    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.
     
  5. Frater

    Frater Regular Pleskian

    18
     
    Joined:
    Oct 17, 2011
    Messages:
    173
    Likes Received:
    3
    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
    Code:
    #!/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}
    
     
    Last edited: Oct 24, 2012
Loading...