Extract "all valid emails"?`

[janjoh]

Hello!

I am looking at integrating PLESK within a corporate enviroment. I would need to be able to extract a list of _Every_ mail adress that the system would accept in order to send to their external antispam solution.

I have found "normal" mail accounts by doing something like this

select accounts.id, concat(mail.mail_name,'@',domains.name), accounts.password from mail left join domains on domains.id = mail.dom_id left join accounts on mail.account_id = accounts.id;


But, what other sources do i need to analyse? Aliases i have found, but i cannot figure it out. mail_aliases has a mn_id, that i cannot link to a domain...

Also, mailing lists.. where can i extract that?

Am i missing any other types of mail adresses?

 

[shall]

determining valid email addresses

I am looking at integrating PLESK within a corporate enviroment. I would need to be able to extract a list of _Every_ mail adress that the system would accept in order to send to their external antispam solution.

As long as you don't have to have the other information (like passwords) from your original SQL query, this should do the trick for you:

SELECT CONCAT(mail.mail_name,'@',domains.name) 
FROM mail LEFT JOIN domains ON domains.id = mail.dom_id;

SELECT CONCAT(mail.mail_name,'@',domain_aliases.name) 
FROM mail LEFT JOIN domain_aliases ON domain_aliases.dom_id = mail.dom_id
WHERE domain_aliases.mail='true';

SELECT CONCAT(maillists.name,'@',domains.name) 
FROM maillists LEFT JOIN domains ON domains.id = maillists.dom_id;

SELECT CONCAT(maillists.name,'@',domain_aliases.name) 
FROM maillists LEFT JOIN domain_aliases ON domain_aliases.dom_id = maillists.dom_id
WHERE domain_aliases.mail='true';

SELECT CONCAT(mail_aliases.alias,'@',domains.name) 
FROM mail_aliases 
LEFT JOIN mail ON mail.id = mail_aliases.mn_id
LEFT JOIN domains ON domains.id = mail.dom_id;

SELECT CONCAT(mail_aliases.alias,'@',domain_aliases.name) 
FROM mail_aliases
LEFT JOIN mail ON mail.id = mail_aliases.mn_id
LEFT JOIN domain_aliases ON domain_aliases.dom_id = mail.dom_id
WHERE domain_aliases.mail='true';

These statements build the email addresses for the 'normal' addresses, normal addresses on domain aliases, mailing lists, mailing lists on domain aliases, and mail aliases on primary domain, and mail aliases on aliased domains.

WARNING!: This does NOT take into account those domains that may have configured a catch-all address. Most third-party spam filters forbid the use of catch-alls, though, so it's assumed that you're not using any catch-all addresses. To determine if you are using catchalls, and on what accounts, you can run this SQL:

SELECT domains.name
FROM domains LEFT JOIN domainservices ON domains.id = domainservices.dom_id
WHERE domainservices.id IN (
SELECT parameters.id
FROM parameters 
WHERE parameters.parameter='nonexist_mail'
AND parameters.value='catch'
)
ORDER BY domains.name;

 

[janjoh]

Wow.. thanks!

A couple of statements failed though...

mysql> SELECT CONCAT(mail.mail_name,'@',domain_aliases.name)
-> FROM mail LEFT JOIN domain_aliases ON domain_aliases.dom_id = mail.dom_id WHERE domain_aliases.mail='true';
ERROR 1146 (42S02): Table 'psa.domain_aliases' doesn't exist

mysql> SELECT CONCAT(maillists.name,'@',domains.name)
-> FROM maillists LEFT JOIN domains ON domains.id = maillists.dom_id;
ERROR 1146 (42S02): Table 'psa.maillists' doesn't exist
mysql>


mysql> SELECT CONCAT(maillists.name,'@',domain_aliases.name)
-> FROM maillists LEFT JOIN domain_aliases ON domain_aliases.dom_id = maillists.dom_id WHERE domain_aliases.mail='true';
ERROR 1146 (42S02): Table 'psa.maillists' doesn't exist
mysql>

mysql> SELECT CONCAT(mail_aliases.alias,'@',domain_aliases.name)
-> FROM mail_aliases
-> LEFT JOIN mail ON mail.id = mail_aliases.mn_id LEFT JOIN domain_aliases ON domain_aliases.dom_id = mail.dom_id WHERE domain_aliases.mail='true';
ERROR 1146 (42S02): Table 'psa.domain_aliases' doesn't exist
mysql>


Any idea why i do not seem to thave those aliases?

And no, catch-alls would be a violation of policy.

 

[shall]

Wow.. thanks!

A couple of statements failed though...
...
Any idea why i do not seem to thave those aliases?

Looks like the *nix version has a couple tables named differently than the Win server where I use this. Here's a modified version that ought to work:

SELECT CONCAT(mail.mail_name,'@',domains.name) 
FROM mail LEFT JOIN domains ON domains.id = mail.dom_id;

SELECT CONCAT(mail.mail_name,'@',domainaliases.name) 
FROM mail LEFT JOIN domainaliases ON domainaliases.dom_id = mail.dom_id
WHERE domainaliases.mail='true';

SELECT CONCAT(MailLists.name,'@',domains.name) 
FROM MailLists LEFT JOIN domains ON domains.id = MailLists.dom_id;

SELECT CONCAT(MailLists.name,'@',domainaliases.name) 
FROM MailLists LEFT JOIN domainaliases ON domainaliases.dom_id = MailLists.dom_id
WHERE domainaliases.mail='true';

SELECT CONCAT(mail_aliases.alias,'@',domains.name) 
FROM mail_aliases 
LEFT JOIN mail ON mail.id = mail_aliases.mn_id
LEFT JOIN domains ON domains.id = mail.dom_id;

SELECT CONCAT(mail_aliases.alias,'@',domainaliases.name) 
FROM mail_aliases
LEFT JOIN mail ON mail.id = mail_aliases.mn_id
LEFT JOIN domainaliases ON domainaliases.dom_id = mail.dom_id
WHERE domainaliases.mail='true';

windows: domain_aliases, maillists
nix: domainaliases, MailLists

 

[janjoh]

That worked well enough!

I am most grateful!

 

[faris]

This is extremely useful info. Thank you.

It would be nice if this could be added to the Knowledgebase.

Faris.