[2025-jun-17] The SlackDocs mailing lists at https://lists.alienbase.nl/mailman/listinfo have been retired. No one has been using these lists for years and it's time to say goodbye. The list archives remain available at https://scalzi.slackware.nl/mailman/listinfo/slackdocs

[2025-jun-17] The SlackDocs Wiki has moved to a new server, in order to make it more performant.

Welcome to the Slackware Documentation Project

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
howtos:network_services:postfix_dovecot_mysql:database [2015/02/15 08:49 (UTC)] – astrogeekhowtos:network_services:postfix_dovecot_mysql:database [2015/06/11 14:42 (UTC)] (current) – [Creating Mail Database Tables] tim
Line 1: Line 1:
-===== Virtual Mail Server Database ===== +====== Virtual Mail Server Database ====== 
-<note important>This article is under construction and not complete! Please return later...</note>+ 
 +This page is supplemental to main article: [[howtos:network_services:postfix_dovecot_mysql|Creating a Virtual Mail Server with Postfix, Dovecot and MySQL]]
  
 The database is used to provide configuration and authentication for your virtual mail domains and mail boxes. Email content is not stored in the database in this configuration. The database is used to provide configuration and authentication for your virtual mail domains and mail boxes. Email content is not stored in the database in this configuration.
Line 6: Line 7:
 If you have a full Slackware installation with MySQL already working then you may skip to the next section. If you have a full Slackware installation with MySQL already working then you may skip to the next section.
  
-If you need to install MySQL (MariaDB) then grab the package from your repo of choice, verify it, then install it with: +If you need to install MySQL (MariaDB) then grab the package and sigfile from a repo, verify it, then install it: 
-<code>installpkg path/to/mariadb-xxxxx.txz</code>+<code> 
 +# For 32 bit package # 
 +wget http://slackware.osuosl.org/slackware-14.1/patches/packages/mariadb-5.5.40-i486-2_slack14.1.txz 
 +wget http://slackware.osuosl.org/slackware-14.1/patches/packages/mariadb-5.5.40-i486-2_slack14.1.txz.asc 
 +# For 64 bit package # 
 +wget http://slackware.osuosl.org/slackware64-14.1/patches/packages/mariadb-5.5.40-x86_64-2_slack14.1.txz 
 +wget http://slackware.osuosl.org/slackware64-14.1/patches/packages/mariadb-5.5.40-x86_64-2_slack14.1.txz.asc 
 +gpg --verify mariadb-5.5.40...txz.asc {path to your downloaded package sigfile} 
 +installpkg mariadb-5.5.40...txz 
 +</code>
 <note>If installing to a Linode.com VPS you may also need to install the libaio package for your Slackware version.</note> <note>If installing to a Linode.com VPS you may also need to install the libaio package for your Slackware version.</note>
 You will then need to initialize the database server and start it. Follow the instructions found in the top comments of the start script, /etc/rc.d/rc.mysqld. Here is a short summary... You will then need to initialize the database server and start it. Follow the instructions found in the top comments of the start script, /etc/rc.d/rc.mysqld. Here is a short summary...
 <code> <code>
-mysql_install_db --user=mysql //Initialize database//+mysql_install_db --user=mysql
 ... ...
-chmod +x /etc/rc.d/rc.mysqld //Start the server//+chmod +x /etc/rc.d/rc.mysqld
 /etc/rc.d/rc.mysqld start /etc/rc.d/rc.mysqld start
 ... ...
-mysql_secure_installation //Secure the installation and set root mysql user password//+mysql_secure_installation
 </code> </code>
  
Line 33: Line 43:
 </code> </code>
  
-Remember the mailuser password for postfix and dovecot setup later.+Remember the mailuser password for when we configure postfix and dovecot later.
  
 ==== Creating Mail Database Tables ==== ==== Creating Mail Database Tables ====
Line 39: Line 49:
  
 This gives you total flexibility to integrate your mail system into any arbitrary operating environment. It also means that there is no default database structure, so we //must// provide it ourselves! This gives you total flexibility to integrate your mail system into any arbitrary operating environment. It also means that there is no default database structure, so we //must// provide it ourselves!
 +
 +<note tip>We will create only a minimal database schema, sufficient to get the system working.
 +If you need to serve multiple domains or many virtual users, you will probably want to extend it by adding other columns or tables which meet your specific needs.</note>
  
 Figure 2 below, shows the schema of our virtual mail database. Figure 2 below, shows the schema of our virtual mail database.
Line 46: Line 59:
   * virtual_users - Contains an entry for each virtual mail box served   * virtual_users - Contains an entry for each virtual mail box served
   * virtual_aliases - Maps aliases (source) to actual mail boxes (destination)   * virtual_aliases - Maps aliases (source) to actual mail boxes (destination)
 +to create these tables, type
 +<code>mysql --user="mailuser" -p -e "CREATE TABLE virtual_domains( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY ( id ) );" mailserver
 +
 +mysql --user="mailuser" -p -e "CREATE TABLE virtual_users( id INT NOT NULL AUTO_INCREMENT, domain_id INT NOT NULL , password VARCHAR(106) NOT NULL, email VARCHAR(100) NOT NULL, PRIMARY KEY ( id ) ,FOREIGN KEY (domain_id) REFERENCES virtual_domains(id));" mailserver
  
 +mysql --user="mailuser" -p -e "CREATE TABLE virtual_aliases( id INT NOT NULL AUTO_INCREMENT, domain_id INT NOT NULL, source VARCHAR(100) NOT NULL, destination VARCHAR(100) NOT NULL, PRIMARY KEY ( id ),FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) );" mailserver
 +</code> each time supplying the password you gave for the mysql mailuser user.
 ==== Adding Domain Entries ==== ==== Adding Domain Entries ====
-To add entries to the virtual_domains table use the following query: +To add entries to the virtual_domains table using the mysql client: 
- +<code> 
-<code>mysql> INSERT INTO virtual_domains SET id=NULL, name='my-domain.com'</code>+mysql -p mailserver 
 +mysql> INSERT INTO virtual_domains SET id=NULL, name='my-domain.com'; 
 +quit 
 +</code>
  
-This table must include entries for each domain served by your mail server.+This table must include entries for each domain served by your virtual mail server.
  
 You should add at least the following rows: You should add at least the following rows:
Line 61: Line 83:
   * The localhost.domain variant - localhost.my-domain.com   * The localhost.domain variant - localhost.my-domain.com
  
-To see the current rows in this table...+To see the current rows in this table using the mysql client:
  
-<code>mysql> SELECT * FROM virtual_domains+<code>mysql> SELECT * FROM virtual_domains;
 +----+-------------------------+ +----+-------------------------+
 | id | name                    | | id | name                    |
Line 74: Line 96:
 </code> </code>
  
-Note the automatically generated id value for my-domain.com, it will be used to enter user mail boxes and mail box aliases.+Note the automatically generated id value for the domain itself, //my-domain.com//, it will be used when we enter user mail boxes and mail box aliases.
  
 ==== Adding User Mail Box Entries ==== ==== Adding User Mail Box Entries ====
 To add entries for each mail box, you will need the domain id from the previous step for the corresponding domain name. To add entries for each mail box, you will need the domain id from the previous step for the corresponding domain name.
  
-We do not store the user passwords as plain text in the database, instead we will generate an SHA512 hash of the passwords.+We do not store the user passwords as plain text in the database, instead we will generate an SHA512 hash of the passwords, used by dovecot for authentication.
  
-To insert a virtual user mailbox use the following query... +To insert a virtual user mailbox from the mysql client, substitute the domain id, actual password and email into the following query: 
-<code>INSERT INTO virtual_users SET+<code>mysql> INSERT INTO virtual_users SET
 id=NULL, id=NULL,
-domain_id=1, //From previous step//+domain_id=1,
 password=ENCRYPT('user-password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), password=ENCRYPT('user-password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))),
 email='me@my-domain.com'; email='me@my-domain.com';
 </code> </code>
  
-To see currently assigned mail boxes for a domain (with password suppressed), use the following query...+To see currently assigned mail boxes for a domain (with password column suppressed):
 <code>mysql> SELECT U.id, domain_id, email FROM virtual_domains D <code>mysql> SELECT U.id, domain_id, email FROM virtual_domains D
 JOIN virtual_users U ON(U.domain_id=D.id) WHERE name='my-domain.com'; JOIN virtual_users U ON(U.domain_id=D.id) WHERE name='my-domain.com';
Line 99: Line 121:
 |  3 |         1 | us@my-domain.com  | |  3 |         1 | us@my-domain.com  |
 +----+-----------+-------------------+ +----+-----------+-------------------+
 +</code>
 +
 +To reset the password for an email user, substitute the new password and email values into this query:
 +<code>mysql> UPDATE virtual_users SET
 +password=ENCRYPT('new-password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))
 +WHERE email='me@my-domain.com';
 </code> </code>
  
 ==== Adding Mail Box Alias Entries ==== ==== Adding Mail Box Alias Entries ====
-To add entries for each mail box alias, you will again need the domain id from the first step corresponding to the domain name of the alias.+To add entries for each mail box alias, you will again need the domain id corresponding to the domain name of the alias.
  
-To insert a virtual mailbox alias use the following query... +To insert a virtual mailbox alias using the mysql client: 
-<code>INSERT INTO virtual_aliases SET+<code>mysql> INSERT INTO virtual_aliases SET
 id=NULL, id=NULL,
 domain_id=1, domain_id=1,
Line 113: Line 141:
 In the example given, mail sent to alias@my-domain.com will be accepted and delivered to you@my-domain.com. In the example given, mail sent to alias@my-domain.com will be accepted and delivered to you@my-domain.com.
  
-To see current aliases in the database, use the following query...+To see current aliases in the database:
 <code>mysql> SELECT A.id, domain_id, source, destination FROM virtual_domains D  <code>mysql> SELECT A.id, domain_id, source, destination FROM virtual_domains D 
 JOIN virtual_aliases A ON(A.domain_id=D.id) WHERE name='my-domain.com'; JOIN virtual_aliases A ON(A.domain_id=D.id) WHERE name='my-domain.com';
Line 123: Line 151:
 </code> </code>
  
 +Go ahead and enter a few virtual email users, and maybe an alias or two, so that you will have working accounts to test with after we set up postfix and dovecot.
  
 +For troubleshooting, use the queries shown above to verify the domains, email users and aliases in the database at any time, and to reset virtual user passwords.
 [[howtos:network_services:postfix_dovecot_mysql|Return to main article page]] [[howtos:network_services:postfix_dovecot_mysql|Return to main article page]]
 +====== Sources ======
 +  * Originally written by [[wiki:user:astrogeek | astrogeek]]
 {{tag>howtos email postfix dovecot mysql}} {{tag>howtos email postfix dovecot mysql}}

Trace: • security

QR Code
QR Code howtos:network_services:postfix_dovecot_mysql:database (generated for current page)