[2018-01-09]

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

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.

Homepage -> http://www.grs-service.ch/pub/grs_mminstallation.html

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

9 thoughts on “Managing users, aliases and domains

  • 2018-01-24 at 16:28
    Permalink

    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…

    Reply
    • 2018-01-27 at 20:39
      Permalink

      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.

      Reply
  • 2018-02-26 at 15:30
    Permalink

    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

    Reply
    • 2018-02-26 at 15:46
      Permalink

      Would that work?
      select CONCAT(‘{MD5-CRYPT}$1$’, Md5(‘new password’));

      Reply
  • 2018-04-21 at 22:11
    Permalink

    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

    Reply
    • 2018-04-25 at 11:59
      Permalink

      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.

      Reply
  • 2018-09-28 at 18:35
    Permalink

    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?

    Reply
    • 2018-10-01 at 16:50
      Permalink

      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/

      Reply
  • 2018-10-11 at 06:43
    Permalink

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

    Reply

Leave a Reply

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