On my server, I have several domains and I have corresponding email addresses for a few of them, for example: ses5909@mydomain.com, ses5909@anotherdomain.com, etc. I have come to find that in Plesk if I have a domain that has an email user named ses5909 and I need to make the same username on another domain, they are not allowed to share the same domain. So if I give ses5909@mydomain.com the password: supersneakypw, I cannot give that password to ses5909@anotherdomain, or anyone else that has the same username. This is pretty lame if you ask me. The reason for this is that mail is not handled on the domain level, but rather the username level. That is a whole other issue though.
Well today I needed to get into my mail and I wasn’t at the computer that I run my mail client on so I went to my webmail account. I couldn’t remember the password for the life of me. I tried a few that I commonly use, but no luck. So, I needed to try to find my password. I could have reset it, but I’ve resorted to this in the past and I am just tired of doing that. I needed to find a way to retrieve my password.
So, I logged into mysql as the root admin. I looked through the databases and noticed there was a psa db which I am assuming stands for Plesk Server Administration. I immediately looked for email and finally saw mail. I saw all of the usernames and a quick join with the domains table showed me which column belonged to which domain.
SELECT mail.mail_name, domains.name from mail LEFT OUTER JOIN domains ON domains.id = mail.dom_id;
But, now I needed to find the passwords for these. After some more hunting, I found the accounts table where passwords are stored in plain text. If only I didn’t have 200 to look through. So a query was in order:
SELECT accounts.id, mail.mail_name, accounts.password, domains.name FROM domains LEFT JOIN mail ON domains.id = mail.dom_id LEFT JOIN accounts ON mail.account_id = accounts.id
Problem solved 
February 8th, 2007 at 7:58 am
Did I read that right? Passwords are stored in plain text? Ouch…
February 8th, 2007 at 8:07 am
Yep. There is a lot of issue with that as I’m sure you can imagine. Having all of the passwords for your server stored in plain text.. but.. it doesn’t stop me from using it
February 8th, 2007 at 9:16 am
Yes, it is strange but convenient for PHP scripts too. At least it’s stored in a database that requires access and not in individual files!
February 28th, 2007 at 4:12 pm
passwords are not stored in plaintext but as blob. blobs will be converted to plain text by choice on export
February 28th, 2007 at 4:28 pm
But its not rocket-science to pull text out of a blob. I would have assumed before looking in there that they used md5.
February 29th, 2008 at 12:47 pm
Plesk should allow you to use the same password for a username that is the same on another domain. This issue is major pain for any admin who likes to use a default account for all domains they are hosing, for example Webmaster or something like that.
I share your pain,
Crack Google
Offering Free Search Engine Optimization Support.
March 6th, 2008 at 10:50 am
One other thing. If someone has breached your server to the point where they are are able to view your shadow file, or your plesk file where the passwords are kept (cleartext or not), then you have bigger problems and email should be the least of your concern.
It seems more like a reason to pass judgement, which is far easier to do than to produce something. You can’t expect to do anything in this world that is great or even above standard, without being criticized.
On the other hand, it does beg one to ask, if they have that “hole”, what other “holes” may exist? So from that perspective, I appreciate everyone’s efforts in keeping a sharp eye out for the community!
Crack Google
Offering Free Search Engine Optimization Support
April 11th, 2008 at 4:55 am
I found this qry more helpful when i migrated.
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;
November 23rd, 2008 at 6:43 am
cool. I transfer my websites to new server and try to this way get all mail list today. Save my many time!
November 30th, 2008 at 4:01 am
Worked fine. Thanks a lot.
April 12th, 2009 at 8:02 am
Where do I execute this query?
If I go to Server > Database Servers > MySql, I can only see my own databases.
How do I get at this psa database? Thanks.
February 17th, 2010 at 5:55 am
One way to do this is:
ssh into the server then:
> mysql -u username -p -D psa
then
>password: password
then
mysql>SELECT accounts.id, mail.mail_name, accounts.password, domains.name FROM domains LEFT JOIN mail ON domains.id = mail.dom_id LEFT JOIN accounts ON mail.account_id = accounts.id;
If you only want password for one domain then just add:
WHERE domains.name = ‘domainname.com’;
Hope that helps someone.
Paul
June 14th, 2010 at 8:02 pm
In Plesk 9 try the mail_auth_view script
In Ubuntu located at
/usr/local/psa/admin/bin/mail_auth_view
October 13th, 2010 at 11:07 pm
This is a slightly improved query:
select concat(m.mail_name, ‘@’,d.name) as Email, convert(a.password, char) as Password, m.mail_name as User, d.name as Domain
from domains d
join mail m on m.dom_id = d.id
join accounts a ON a.id = m.account_id
order by d.name, m.mail_name
You can add a where as needed, e.g.
where d.name = ‘mydomain.com.au’
August 2nd, 2012 at 11:23 am
Hello,
Plesk is causing my big problems(re-writing DNS files with bad data), so I decided to shut it down.
Now, I have to create a new email account. So I did this:
1. insert into accounts (type,password) values(“plain”,”temp123″); -> which gave the last id = 245
2. insert into mail (userId,mail_name,perm_id,postbox,account_id,spamfilter,dom_id) values(“245″,”newuser”,”0″,”true”,”245″,”false”,”2″); -> the last “2″ is my domain id ;
3. created “Maildir” folder structure in /var/qmail/mailnames//newuser/
4. edited : /var/qmail/users/assign and inserted the line : =2-newuser:popuser:110:31:/var/qmail/mailnames//newuser:::
But webmail it is not working, Horde giving me “Login failed because your username or password was entered incorrectly”.
Can you help me please and tell me what did I miss or did wrong?
Thank you very much!!
September 5th, 2012 at 11:07 pm
yep, it’s real clear way for doing that, still if u could offering for further thought will be totally great!