Maybe you already know what you have to do to create mail domains and mail users. After all I tried to explain the database schema on the page that dealt with preparing the database. But if that wasn’t clear enough let me explain what you need to do to manage your mail accounts.
Web interfaces
If you don’t like using SQL queries to manage your mail server you may like to install a web-based management software. Several developers contributed web interfaces for the previous Wheezy tutorial and they will probably still work but only create weak password hashes. With a little experience in web development you should be able to develop your own management system. I have documented a few common SQL queries further down.
These web interfaces were specifically made for this Jessie guide:
ISPmail Admin
Homepage: http://ima.jungclaussen.com/
Demo: http://ima.jungclaussen.com/demo/
GRSoft Virtual Mail Manager
Peter Gutwein has updated his PHP-based web interface to create strong password hashes as recommended in this Jessie guide. It supports english, german, spanish, french, italian, russian and swedish.
(Used to be available from http://www.grs-service.ch/pub/grs_mminstallation.html but is not available any more.)
Here are screenshots of the working application:
Managing the database directly
Common tasks / PHPMyAdmin
This table explains what changes are required in the database for your everyday tasks. You can click your way through PHPMyAdmin following the instructions in this table:
| Create a mail domain | Insert a new row into the virtual_domains table and set the “name” to the name of the new domain. |
| Delete a mail domain | Delete the row from the virtual_domains table that has the right “name”. All aliases and users will automatically be deleted, too. However the mailboxes will stay on disk at /var/vmail/… and you need to delete them manually. |
| Create a mail user | Find out the “id” of the right domain from the virtual_domains table. The insert a new row into the virtual_users table. Set the domain_id to the value you just looked up in the virtual_domains table. Set the “email” field to the complete email address of the new user. Create a new password in a shell using the “dovecot pw -s SHA256-CRYPT” command and insert the result into the “password” field. |
| Change the password of a user | Find the row in the virtual_users table by looking for the right “email” field. Create a new password in a shell using the “dovecot pw -s SHA256-CRYPT” command and insert the result into the “password” field. |
| Delete a mail user | Find the row in the virtual_users table by looking for the right “email” field and delete it. The mailbox will stay on disk at /var/vmail/… and you need to delete it manually |
| Create a mail forwarding | You can forward emails from one (source) email to other addresses (destinations) – even outside of your mail server. Find out the “id” of the right domain (the part after the “@” of the source email address) from the virtual_domains table. Create a new row in the virtual_aliases table for each destination (if you have multiple destination addresses). Set the “source” field to the complete source email address. And set the “destination” field to the respective complete destination email address. |
| Delete a mail forwardding | Find all rows in the virtual_aliases table by looking for the right “source” email address. Remove all rows that you lead to “destination” addresses you don’t want to forward email to. |
SQL queries
| Create a mail domain | INSERT INTO virtual_domains (name) VALUES (“example.org”); |
| Delete a mail domain | DELETE FROM virtual_domains where name=’example.org’; |
| Create a mail user | INSERT INTO virtual_users (domain_id, email, password) VALUES ( (SELECT id FROM virtual_domains WHERE name=’example.org’), ‘john@example.org’,CONCAT(‘{SHA256-CRYPT}’, ENCRYPT (‘new password’, CONCAT(‘$5$’, SUBSTRING(SHA(RAND()), -16))))); |
| Change the password of a user | UPDATE virtual_users SET password=CONCAT(‘{SHA256-CRYPT}’, ENCRYPT (‘new password’, CONCAT(‘$5$’, SUBSTRING(SHA(RAND()), -16)))) WHERE email=’email@address’; |
| Delete a mail user | DELETE FROM virtual_users WHERE email=’john@example.org’; |
| Create a mail forwarding | INSERT INTO virtual_aliases (domain_id, source, destination) VALUES ( (SELECT id FROM virtual_domains WHERE name=’example.org’), ‘melissa@example.org’, ‘juila@example.net’); |
| Delete a mail forwarding | DELETE FROM virtual_aliases WHERE source=’melissa@example.org’; |
Thanks for the great updated guide for Jessie! 🙂
I successfully deployed it, working great. Except… web interfaces. I use ISPmail Admin now, but I can only login as admin user. When I try to login as a normal user, it doesn’t work. I think because it doesn’t understand the updates password scheme in the database (with {PLAIN-MD5} or {SHA256-CRYPT} in front of the password).
Any idea how to solve this? Or is there another interface which understands the new scheme? Thanks!
Hi Hans. Thanks for the feedback. My ancient ISPmailAdmin is so old… I’m not even sure it can be installed any more because it is based on a deprecated web framework. However I am already working on a new web interface called ISPadmin that is half done. It will handle the SHA-256 hashes and also be able to assign administrators for the domains so that you can delegate user handling. The weather is rainy outside anyway so perhaps I’ll get some more lines of code done this weekend.
That’s the best news of the day Christoph! 🙂 Thanks in advance and keep up the good work! I’ll do another rain dance tomorrow 😉 😉
You can download the GRSMailmanager for Jessie on http://www.grs-service.ch/pub/grs_mminstallation.html .
SHA256 and the older md5 passwords are supported.
Regars
Peter
p.s. I added a multilanguage-package to the download-page including translations in spanish, French, italian, russian and swedish. All those Translation have been done by former users of the VMM.
Peter! Wow! This is very welcome! Thank you very much, it works great (except it doesn’t run on PHP7, I had to go back to 5.6). 🙂
Yes – the mysql – Extension was deleted in php 7! But anyway you could use php 7 after this Workaround: https://ckon.wordpress.com/2015/08/06/put-mysql-functions-back-into-php-7/ .
Did not try it, but as some other coders told this should work.
Regards Peter
I placed a tested php7 – Version into the download area now :-).
Hello! It’s possible to use the roundcube plugin “password” to permit the user to change the password?
With the default config file i had no luck…
Many thanks in advance
Already fixed, was just a typo 😛
Thx!
Mal ganz dumm gefragt: wie sieht denn nun eine Zeile in der Tabelle virtual_users aus für john@example.org mit dem Passwort start123 ? Muss es so sein:
1 1 john@example.org {SHA256-CRYPT}$5$EvfedKzPsSlPCj2T$.yQ7um2G0wQ83wR8TlQSfboy1yR8FjOdJcxNT5H3pv1
oder so:
1 1 john@example.org $5$EvfedKzPsSlPCj2T$.yQ7um2G0wQ83wR8TlQSfboy1yR8FjOdJcxNT5H3pv1
oder so:
1 1 john@example.org EvfedKzPsSlPCj2T$.yQ7um2G0wQ83wR8TlQSfboy1yR8FjOdJcxNT5H3pv1
Ist ja toll das SHA256 eingesetzt wird, aber das Einfügen von Hand ist umständlich und funktioniert bei mir auch nicht wie angegeben (SQL beklagt Syntaxfehler). Mit dem GRSoft Manager kann ich zwar Mailadressen anlegen, aber über Roundcube oder Thunderbird kann man sich dann anschließend trotzdem nicht anmelden. Die Passwörter die GRSoft Manager erzeugt, gebinnen auch immer mit $5$rounds=5000$0dPp… ich weiss ja nicht ob das so sein soll, aber die von dovecot pw generierten beginnen anders, warum auch immer…
Der Zusatz {SHA256-CRYPT} bei den Passwörtern braucht es nicht und wenn du deine Passwörter über den Mailmanager anlegst bzw. editierst, funktioniert das auch mit Roundcube und Thunderbird bzw. anderen Mailclients.
Für die anderen Leser:
1.) Die Fehler in der SQL-Syntax passieren, wenn man den Code aus der Website in die Konsole kopiert, dann werden scheinbar die falschen Anführungsstriche eingefügt. Richtig ist der einfache Anführungsstrich, der auf einem deutschen Tastaturlayout auf der Rautetaste # liegt
2.) Aus einem anderen Tutorial geht hervor, dass wohl die 2. Version des Passworthashes richtig ist, man sollte also nur {SHA256-CRYPT} am Anfang weglassen, dann sollte es gehen.
Hi,
May be i have miss it, but are there mail quota implemented, so that the diferent mailbox sizes can be defined?
Thanks in advance
In Wheezy to get quota support in postfix you had to download and apply a patch and rebuild the deb files, think that still applies to the postfix that comes with Jessie…. I initially setup for quotas but keepign postfix both up to date and patched for quota support became painful, and I realized I wasn’t even really using quotas (mail for me and my family…) so I re-did my server w/o the quota support. If you want to add those quotas, check the “perfect server” docs on howtoforge.com – they have a mail set up *very* similar to this one for wheezy and I’m sure the process is pretty much the same (extra column in db, changed queries to match, apply patch to postfix)
Hello! I installed GRS Virtual Mail Manager and it works so far but when switching the language to German the special characters are not displayed correctly.
Thx!
That depends on the Defaultcharset of your server …
If you add “AddDefaultCharset ISO-8859-1” (Apache-Syntax) to your Server, it will be displayed correct.
Alternative:
add ‘header(‘Content-Type: text/html; charset=ISO-8859-1′);’ to mgr_main.php that it looks like:
<?php
header('Content-Type: text/html; charset=ISO-8859-1');
…
…
…
Thanks, that’s a reeeally awesome guide!
But there is a mistake in SQL queries Create a mail user.
You would hash the password 2 times, because you’re using dovecot and MySQL for it.
So please remove this sentence “Generate a password hash using dovecot pw -s SHA256-CRYPT first.”
Fixed it – thanks.
I am working on a Rails app to manage domains and users on my ISPmail server.
Not quite ready for prime time, but I intend to make use of this shortly.
I have not tested against an existing database yet. You are warned.
See https://github.com/ringe/ispmail-on-rails
Everything appears to have worked right up to the point of creating virtual users. The SQL command above gives me the error.
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘@notmydomain.net’,CONCAT(‘{SHA256-CRYPT}’, ENCRYPT (‘notmypassword’ at line 2
I am using MySQL workbench to run the command against the server. MySQL is version 5.5.44-0+deb8u1
Me too… For me it was a cut&paste issue with the quotes…
I was using this character ‘ instead of ‘ and that caused the error message…
Hope it helps…
Raffaele
Yeah the blog software strikes again, this time fucking up the quotes instead of turning two dashes into an emdash. God how i hate blog software
GRSoft Virtual Mail Manager – oddly I’ve got all the other stuff in this guide yet I am stuck at installing this? Any pointers on how / where it goes and how I get this to install?
Hey Runar, nice work I was thinking to write something similar 🙂 but now you already started. I might jump in a contribute in the near future, I just forked. Thanks!
Please check out https://github.com/ringe/ispmail-on-rails and let me know if it works for you.
I also added ansible tasks to install this on ispmail-jessie: https://github.com/ringe/ansible-ispmail-jessie
I am putting this in production myself, and I would really appreciate some feedback. 🙂
Hey Runar, nice work I was thinking to write something similar 🙂 but now you already started. I might jump in a contribute in the near future, I just forked. Thanks!
I have an issue where I added a second virtual domain (using GRSoft) and the emails being sent from that domain are failing spamassassin HK_RANDOM _ENVFROM an HK_RANDOM_FROM on the recipient’s end. It doesn’t look like postfix is setting the headers properly. It works fine on the other domain that I went through setup with. Thoughts?
Since I have not found any pointers to Mailbox size restrictions aka quotas! Are you planning to add a section on Quotas as in older Tutorials? Last one I read was the one on debian Lenny, so it has been a while? Is there anything new in jessie?
Hi Christoph, thanks for the great guide! There’s another well maintained management tool, which I’ve been using for years:
https://sourceforge.net/projects/postfixadmin/
It works more or less out of the box with the setup described in your guides.
Are you sure? Last time I checked the database schema was so different that it didn’t work. Can you give directions for its configuration?
Sure, here you go:
mysql-virtual-mailbox-domains.cf
query = SELECT 1 FROM domain WHERE domain=’%s’
mysql-virtual-mailbox-maps.cf
query = SELECT 1 FROM mailbox WHERE username=’%s’
mysql-virtual-alias-maps.cf
query = SELECT goto FROM alias WHERE address=’%s’
mysql-email2email.cf
SELECT username FROM mailbox WHERE username=’%s’
dovecot-sql.conf.ext
user_query = SELECT ‘/var/vmail/%d/%n’ AS home, ‘maildir:/var/vmail/%d/%n/maildir’ AS mail, 5000 AS uid, 5000 AS gid, CONCAT(‘*:bytes=’, IF(mailbox.quota = 0, domain.maxquota*1024000, mailbox.quota)) as quota_rule FROM mailbox, domain WHERE username = ‘%u’ AND mailbox.active = ‘1’ AND domain.domain = ‘%d’ AND domain.active = ‘1’
password_query = SELECT username as user, password, ‘/var/vmail/%d/%n’ AS userdb_home, ‘/var/vmail/%d/%n/maildir’ AS userdb_mail FROM mailbox WHERE username = ‘%u’ AND active = ‘1’
/etc/dovecot/dovecot-dict-sql.conf.ext
connect = host=localhost dbname=mailaccounts user=dovecot password=yourpasswordhere
map {
pattern = priv/quota/storage
table = quota2
username_field = username
value_field = bytes
}
map {
pattern = priv/quota/messages
table = quota2
username_field = username
value_field = messages
}
add to /etc/dovecot/conf.d/90-quota.conf
plugin {
# Using SQL Tables to store current quota size
quota = dict:domain:%d:proxy::sqlquota
quota2 = dict:User Quota::proxy::sqlquota
# Allow 10% more for Trash Folder
#quota_rule2 = Trash:storage=+10%%
}
dict {
sqlquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
That’s it. Hopefully, I didn’t forget anything. As you can see, I’m also using quota with dovecot, which is directly suported by postfixadmin, hence the additional configs for dovecot. Please be careful with manual quota recalculation as dovecot could possibly still have this bug: http://dovecot.org/list/dovecot/2013-February/088440.html
For strong password encryption with postfixadmin/dovecot/roundcube see https://kaworu.ch/blog/2016/04/20/strong-crypt-scheme-with-dovecot-postfixadmin-and-roundcube/
Additionally I’ve setup lucene/solr for fulltest indexing with dovecot, but that is another story…
Many thanks, Christoph, for the guide which I’ve worked through and set up an – almost – fully functional email server. Thanks also to Anton for posting the configuration for postfixadmin which I have also used. Rather than using the version available from sourceforge I installed the stable version from Debian’s main repository and can confirm that everything seems to work fine with this configuration although there’s one remaining glitch which I’m trying to remedy now which is that incoming mail is being rejected:
When sending an email from a Gmail account, the email is returned with: “The error that the other server returned was: “550 5.1.1 : Recipient address rejected: User unknown in virtual mailbox table.” But that, I think, is nothing to do with postfixadmin.
Typo in mysql-email2email.cf, forgrot “query =”
Just to add to my previous comment about postfixadmin: Having re-checked all the configuration files, email is successfully sent using a postfixadmin accounte and now isn’t rejected by the server. I subsequently set up roundcube as described in the guide but the login fails. With the icedove, when setting up an account the configuration, apparently, works flawlessly finding both incoming imap.example.com,STARTTLS and outgoing smtp.example.com, STARTTLS but after checking the password returns “User or password invalid” and “Configuration could not be verified — is the username or password wrong? Lastly, I set up Roundcube exactly as detailed in the guide but am unable to login either; so something is awry somewhere. Oh well, back to delving into the logs and configuration files again. 🙂
Finally, having corrected several errors in the files, everything works fine and I’m able to login, send and receive emails with Roundcube — and Icedove — as set out in this guide.
@Anton. I tried Postfixadmin before correcting the issues mentioned in my last post and it looks very good but I’ve decided not to use it for the time being as I’m not sure how to configure /etc/fstab for quota yet.
Now I’m looking at the GRSoft Virtual Mail Manager as an alternative but I’m not too sure how to run the install.php from a browser so I’ll just manage the mailserver database directly till I’ve figured it out either option.
Mail quota is handled by dovecot alone. It has nothing to do with disk/fs quota. For detailed information see: http://wiki2.dovecot.org/Quota/Configuration
If you don’t need quota you can disable quota support in postfixadmin, too.
Thanks Anton for the reply. I was laboring under a misapprehension about quota which I was not familiar with at all.
I’d just like to observe that, because the configuration you posted is not wrapped in code blocks, if cut and pasted as such, gives rise to SQL syntax errors on account of the normal quotation marks for code being substituted by unicode quotes used for blogging purposes but, having corrected that, everything seems to work fine except for one — I hope final — error after trying to login using Roundcube, namely:
dovecot: auth-worker(27941): Warning: mysql: Query failed, retrying: Table ‘mailserver.mailbox’ doesn’t exist
dovecot: auth-worker(27941): Error: sql(user@example.net,127.0.0.1): Password query failed: Table ‘mailserver.mailbox’ doesn’t exist
dovecot: imap-login: Disconnected (auth failed, 1 attempts in 2 secs): user=, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured, session=
Thanks for a great tutorial!
How are aliases handled in this scenario?:
I have set up two full accounts: a@domain.com and b@domain.com
I create, in addition to those above, 1 alias that forwards a@domain.com to b@domain.com. Basically I want to have a copy of the email sent to a in b’s mailbox.
In my tests, it looks like as if an incoming email is then only forwarded to b@domain.com and does not show up in the a@domain.com inbox? Is that correct?
Please look at Example 2 at this page.
Hi, I looked at example 2 at the page. This is exactly the scenario. However, it did not work in my case. I got this warning in the log files:
… “status=deferred (mail system configuration error) ”
… “postfix/pipe[12345]: warning: pipe flag `D’ requires dovecot_destination_recipient_limit = 1 ”
So I added “dovecot_destination_recipient_limit = 1” to Postfix’ main.cf. Afterwards it works! (Must admit I am running Cent OS 7).
I’ve asked a lot of questions and you’re guide is amazing. I’ve got a mail server up and running and everything seems to be happy. just wanted to say thank you very much for the amazing guide and for your help!
Hello !!!!!!
I installed GRsoft but I can not log in … in mysql the tables are all created, the user master mail that I created “adminroot@example.com”, I can enter the roundcube webmail sent and I receive email, but I can not get GRsoft mail…..please help!!!!!!!
Christoph Haas, I installed ISPmailAdmin 0.9.4. I noticed that login form’s password parameter is sanitize. This makes it imposible to login using passwords with special characters because they are converted to html entities. handleParam funcion treats password parameter specially but only if it is defined as /^pwd_/. In login form password is defined as sloginpass, so it is sanitize.
Could you check it?
Thanks for this useful guide
Your second sentence is a bit wrong: After all I tried to explained the database schema it on the page that dealt with preparing the database.
“explained” should be “explain” and you should drop the “it”.
Again, I LOVE your tutorial.
Oops. Thanks. Seems I need someone to proofread my writings. 🙂