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 in the section 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 an earlier version of this guide and they will probably still work because the database schema has not changed. 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 have been provided to you by other diligent hands:
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.
Homepage -> http://www.grs-service.ch/pub/grs_mminstallation.html
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’; |
Builded a brand new System from Scratch and all is working like a charm: Thank you so much!
Only one thing to mention: The SQL queries above don’t work so far on my server – neither on phpMyAdmin, nor directly on console. “Create a mail domain” i had to change VALUES (“example.org”) to VALUES (‘example.org’) for example. “Create a mail user” ended in a syntax mistake – { and } seems not to work, and another hint was found in the error-message near CONCAT ..
Perhaps my mistake, but anything else worked from beginning…
The text in the table “SQL queries” of the above HTML page contains single quotation marks at the moment ( ‘curved quotes’; in HTML: ‘ and ’). These have to be replaced with apostrophes (‘straight’, ‘typewriter single quote’; in HTML: '). I did this manually after copying and pasting the text of the query in order to avoid the errors.
Hi,
I have the database of the previous version, which has the passwords in md5 as a source to authorize users in a freeradius system.
I have not been able to make freeradius work correctly with the SHA256-CRYPT scheme.
https://freeradius.org/radiusd/man/rlm_pap.txt
https://wiki.dovecot.org/Authentication/PasswordSchemes
I was able to do it with the scheme SSHA256.
How do I write the SQL query in roundcube, when working with this scheme?
Can someone guide me? I was trying all day.
Regards
Would that work?
select CONCAT(‘{MD5-CRYPT}$1$’, Md5(‘new password’));
I’m surprised that Postfixadmin is not mentioned. It’s a great web interface, for managing domains, users, allowing user connections, providing strong password encryption, vacation, quota, and more.
It’s available on Debian, but you may want to get the latest version on http://postfixadmin.sourceforge.net
Postfixadmin was invented about the same time I published the first version of this guide. And they went in a different direction. Their database schema is completely different. So installing it won’t get you anywhere.
I have been thoroughly enjoying your guide as it is both easy to follow and explains a lot of the meaning behind your choices and instructions. However I am stuck now at the install of ISPmail Admin. Looking at the documentation on the ISPmail Admin website I can see that step 2 is already accomplished in your guide. I have downloaded the tar and uncompressed it on my mail server, however I am confused as to step 4 as I am unsure what the “appropriate directory” would be?
I have figured out a way for it to function properly, although I am sure there is a more “correct” and or “organized” manner to do so. I simply made a folder inside of the /var/lib/roundcube/ tree and unzipped the web interface there. Now I can access the manager via https://webmail.example.com/folder/
Put the web interface in /var/www/html/
In /etc/apache2/sites-available/webmail.example.org-https.conf add
Alias /mailadmin “/var/www/html/mailadmin”
systemctl reload apache2
Very good article, but I`d like suggest to make version with Postfixadmin sql scheme because Postfixadmin has many options howto manage domains.
E.g. I`m going to make new post server and need use fetchmail from primary mx server. Fetching is greatly managed by Postfixadmin.
Now I have to tried use Postfixadmin, or fetchmail table to fetching emails from remote server and combine it with setup from this article.
Another problem will be migrate all users settings from old server to new server (different sql scheme, different password crypt…etc).
Hey
Thanks for the great tutorial! I enjoyed the whole tutorial. Some problems here and there but in the end, everything went perfectly.
It would be great to add the limit for user storage. I wanna add a user with only 500mb storage.
I checked a little about dovecot quota and I’ll figure out but I think to add this to the tutorial would be good as well.
Thanks,
Dan
@Christoph
When you compare your Database Schema and Postfixadmin’s, are there any advantages disadvantages one over another? Is one better than the other?
The reason I am asking, I might incorporate Postfixadmin’s Database Schema into your guide. This will allow me to use the Postfixadmin Web Interface. Plus it will be easier to enable Mail Quotas too, I think…
Thanks…
Postfixadmin uses a pretty different schema. I did not deliberately choose something different. It’s just that I tried to keep the schema the same to make upgrades and migrations easy. It’s worth a try.
I use ISPmail Admin, and I wanted to use an really secure hash like BCRYPT and not one that is insecure like MD5, SHA1, SHA256, SHA512.
https://www.acunetix.com/blog/web-security-zone/insecure-default-password-hashing-cms/
So I replaced a part of the code and added something in /srv/hps/dbadmin/pub/mat/inc/IspMailAdminApp.inc.php on line 468
$opts = [ ‘cost’ => 13, ];
$sPwdHash = ‘{BLF-CRYPT}’.password_hash($sPwdPlain, PASSWORD_BCRYPT, $opts);
Hi,
Could you tell me how to create a password {SHA-256} in PHP code ?
Regards
This is the solution to my question above:
$salt=’$5$’.mt_rand(1000000000000000,9999999999999999);
$password = ‘{SHA256-CRYPT}’.crypt($new_password, $salt);