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 earlier versions of this guide and they will probably still work. The only change in the database schema is the quota field in the virtual_users table. I assume that the tools will add support for it. However you should be able to develop your own management system or integrate the mail server into your own system easily. 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 BLF-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 BLF-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’,'{BLF-CRYPT}$2y$05$.We…’; |
| Change the password of a user | UPDATE virtual_users SET password='{BLF-CRYPT}$2y$05$.We…’ 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’; |
http://ima.jungclaussen.com/ this GUI does not support bcrypt
in the config file there’s :
/**
** Pasword hashes
** Enable only *one* of the following
**/
// define(‘IMA_CFG_USE_SHA256_HASHES’, true);
// define(‘IMA_CFG_USE_MD5_HASHES’, true);
/**
** access control: uncomment the type you want to use.
For https://www.ima.jungclaussen.com/ to accept dovecot blowfish password, you may change one method in the file IspMailAdminApp.inc.php like this :
protected function makePwd_DbHash($sPwdPlain)
{
return ‘{BLF-CRYPT}’.password_hash($sPwdPlain, PASSWORD_BCRYPT);
}
Thankyou for this Gompali, I am hoping that this ispmailadmin GUI works with the Buster guide, except for viewing/changing quotas.
The GUI is ok and makes the coffee 😉 thanks to its author.
I’m working not on a GUI but on an API that may help manipulate the database with http requests with Symfony framework. I’ll link the source code when i’ll have time to write a doc.
When I replace the original function MakePwd_DbHash with the BLF-CRYPT version from this comment, I get in the apache2 log file:
PHP Parse error: syntax error, unexpected ‘{‘ , expecting ‘;’ in ispmailadmin/inc/IspMailAdminApp.inc.php on line 474
where line 474 is the new return statement. Triple checking both my cut/paste and removal of the original function doesn’t help. Suggestions please!
had the same issue was from having hidden characters when copy and pasting, just write it manually and it should work.
Thanks Alison! ISPMailAdmin works perfectly now!
I spoke to soon. If I use ISPMailAdmin to change a password without adapting to BLF-CRYPT, it correctly updates the password using {SHA256-CRYPT}. But if I make the {BLF-CRYPT} change, it fails entering a blank password.
Hello, I am following the new ispmail guide, the thing is that instead of trying with ispmail for the creation of users and aliases, I tried the new thing that the guide brings, I did it grsoft, the thing is that it is already installed but I created it users never recognize my password, I try to log in to the roundcube with the new users that I create, but nothing, I also try it with mutt from console and nothing, and I know it’s not the server itself because I did some tests with ispmail and with him if they log in.
i install this Alternative PHP7 – Version (latest version).
If anybody is interested, I have modified Ole’s ISPmail Admin 0.9.6 to manage quotas:
https://gitlab.com/ToKe79/ispmailadmin/-/commits/buster
Simply uncomment `define(‘IMA_CFG_QUOTAS’, true);` and you should be good to go. I have also added a switch for the BCRYPT passwords (uncomment `define(‘IMA_CFG_USE_BCRYPT_HASHES’, true);`), default is SHA256 for backwards compatibility (I removed the two obsolete defines).
Excellent, works like a charm. Thanks a lot!
ISPmail Admin, new version 0.9.7; Thursday, November 5th, 2020
– Added choice (config.inc.php) of password hashing method: BCRYPT
ISPmail Admin, new version 0.9.7; Thursday, November 5th, 2020
– Added support of quotas (config.inc.php)
Does someone have already tested PostfixAdmin?
(http://postfixadmin.sourceforge.net/)
Unfortunately the data schemas differ completely. Both projects emerged roughly at the same time and we never synced. 🙁
Yes, I have it working. AddOns like the vacation plugin take some extra work, but IIRC just to get it working, you only have to adjust the SQL queries.
I’ve been trying Tomas’ ‘Buster’ version of IspmailAdmin, with the options of quotas and bcrypt as per the latest guide. Most actions I’ve tried work but:
1) creating new accounts doesn’t. You get an error message which indicates a mismatch in columns.
I’ve managed to fix the issue *just for DB with quotas defined* by replacing the section starting at line 461 in EmailAccounts.inc with one that only works, but only with quotas:
“INSERT INTO virtual_users (domain_id, password, email, quota)
VALUES ( “.strval($iIdDomain).”,
‘”.$this->App->DB->realEscapeString($sPwdHash).”
‘”.”,
‘”.$this->App->DB->realEscapeString($sAccount.’@’.$sDomain).”
‘”.”,
‘”.strval($iQuota).”
‘”.”
)
”
but I’ve not been able to work out how to add back the ‘with and without quota’ switching (I’n not familiar with php syntax.
2) I find that I can login as ‘mailadmin’ if defining the logon password to be the same as the database one but I can’t define a different user with the admin rights in config.inc.php although as this is defined separately I’d assume it ought to work.
3) Also login to IspmailAdmin as a non-admin user that I’ve created doesn’t seem to work. And changing a users password doesn’t to work properly: if you change it then IMAPing in fails, whether with the old or new password. Putting it back to the old password then restores IMAP functionality.
For (2) and (3) I suspect some confusion is happening between passwords and their bcrypt hashes.
Following up on my login/imap problem with ISPMailAdmin:
Users created by ISPMailAdmin *are* in MariaDB virtual user database, but unlike those created directly in MariaDB or ones created by Adminer they don’t seem to be in the virtual mailbox table –
If I try to send an email to them (e.g using swaks) I get a rejection:
550 5.1.1 : Recipient address rejected: User unknown in virtual mailbox table.
In adminer they appear greyed out and don’t show s as mailable.
Any suggestions?
Hi jfoss. A Buster-only version would work for me. I attempted to access your version of IspMailAdmin but the link doesn’t work (not even a ping response).
Hi Marjorie,
I have fixed small typos in the code when creating users with quotas. It should work now (I did not commit the fix and realized this after I moved my mail server and cloned the repository; luckily I had the fix on other machine).
Cheers,
Tomas
The link “section that dealt with preparing the database” is to the stretch version of the page
Would any of the web interfaces featured here work with PostgreSQL rather than MySQL? I’ve gone through their documentation and they both seem to rely on the latter
I reinstalled my email server just few weeks ago with that guide, but used postgresSQL instead of MySQL. Worked pretty well. Roundcube is supporting postgresSQL natively
If anyone is interested, after this guide I have coded a small django app to manage virtual domains, users (quota and password are handled) and aliases. It uses bootsrap4 and is relatively good looking. It also checks for DKIM, DMARC, adn SPF records of virtual domains. It is available here : https://github.com/nanoy42/dinomail
Thanks a lot for your work. Just trying it out. Changed a few things from PostgreSQL to MariaDB. But I struggle with the migrations. Is the dinomail database supposed to be seperate from the mailserver database?
Thanks to Tomas and Gompali for fixing thing I had no time to (until now).
ISPmail Admin, new version 0.9.7; Thursday, November 5th, 2020
– Added support of quotas (config.inc.php)
– Added choice (config.inc.php) of password hashing method: BCRYPT
– Added database connection via socket
https://ima.jungclaussen.com
Hi,
Thanks for your work and this hudge tuto.
Why not use Postfix Admin as web interfaces in order to managing users, aliases and domains ?
Is that possible to complete your tuto by installing the best web interface (I don’t know if Postfix Admin is the best one) for managing users etc. ?
Thank you 🙂
The web interface used must be compatible with how ISPMail is laid out. Postifix Admin would likely demand many significant changes be made to the tutorial and those changes would make it incompatible with existing installations.
Postfix Admin is an entirely separate project from both Postfix and ISPMail and as such, it may be a moving target.
Hi Christoph,
Thank you very much for this great tutorial.
I found a small but nasty bug in one of the SQL queries (“Create a mail user”)… obviously only one additional parenthesis is missing. Correct entry without SLQ error is therefore:
INSERT INTO virtual_users (domain_id, email, password) VALUES ((SELECT id FROM virtual_domains WHERE name=’example.org’, ‘john@example.org’,'{BLF-CRYPT}$2y$05$.We…’);
Please fix it for the rest of the world 😉
Best from Cologne
Wolfram
Translated with http://www.DeepL.com/Translator (free version)
>INSERT INTO virtual_users (domain_id, email, password) VALUES ((SELECT id FROM virtual_domains >WHERE name=’example.org’, ‘john@example.org’,'{BLF-CRYPT}$2y$05$.We…’);
The text is correct. You omitted a “)” after name=’example.org’ (and possibly put an additional one before the SELECT keyword).
Not getting an error is different from being correct. The query is supposed to return the domain_id only. The e-mail address and password are strings and should not be included in the query. The VALUES do not need to be enclosed in parentheses.
MariaDB [mailserver]> insert into virtual_users (domain_id, email, password) values (select id from virtual_domains where name=’mydomain.tld’), ‘n@mydomain.tld’, ‘{BLF-CRYPT}$2y…’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘select id from virtual_domains where name=’mydomain.tld’), ‘n@mydomain.tld’, ‘{BL…’ at line 1
I’m quite certain that you need nested parens:
MariaDB [mailserver]> insert into virtual_users (domain_id, email, password) values ((select id from virtual_domains where name=’mydomain.tld), ‘n@mydomain.tld’, ‘{BLF-CRYPT}$2y…’);
Query OK, 1 row affected (0.023 sec)
Moin Wolfgang. Please give me a hint what went wrong. Yes, you are using a subquery to determine the domain_id of the domain. That is important if you already have other domains in your database. However if you are starting from scratch then the database is supposed to be empty at that point.
(Bitte gib mir einen Tipp, wo etwas schief gegangen ist. Ja, du benutzt eine Subquery, um die domain_id der Domain zu bekommen. Das ist wichtig, falls du schon andere Domains in der Datenbank hast. An dieser Stelle im Tutorial sollte die Datenbank aber noch leer sein, deshalb ist domain_id=1 eigentlich ungefährlich.)
The VALUES have to be set into brackets ( ).