Managing users, aliases and domains

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/

ima_screenshot

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:

grsoft-vmm-screenshot1grsoft-vmm-screenshot2grsoft-vmm-screenshot3grsoft-vmm-screenshot4grsoft-vmm-screenshot5grsoft-vmm-screenshot6grsoft-vmm-screenshot7grsoft-vmm-screenshot8grsoft-vmm-screenshot9

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’;

47 thoughts on “Managing users, aliases and domains”

  1. Hans van Eijsden

    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!

    1. Christoph Haas

      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.

      1. Hans van Eijsden

        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 😉 😉

    1. Peter Gutwein

      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.

    2. Hans van Eijsden

      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). 🙂

  2. 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

  3. 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…

    1. Peter Gutwein

      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.

  4. 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.

  5. 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

    1. 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)

  6. 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!

    1. Peter Gutwein

      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.

      1. 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');


  7. 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.”

  8. 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

    1. 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

      1. 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

  9. 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?

    1. 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!

    1. 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!

  10. Steve Graves

    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?

  11. 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?

    1. Christoph Haas

      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?

      1. 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…

        1. 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.

  12. 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. 🙂

  13. 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.

  14. 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=

  15. 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?

      1. 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).

  16. 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!

  17. 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!!!!!!!

  18. 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

  19. Tom Marcoen

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top