Differences

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

--- howtos:network_services:postfix_dovecot_mysql:database [2015/02/15 08:49 (UTC)]
astrogeek
+++ howtos:network_services:postfix_dovecot_mysql:database [2015/06/11 14:42 (UTC)] (current)
tim [Creating Mail Database Tables]
@@ 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.
@@ 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 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>
 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>
-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
 ...
-mysql_secure_installation //Secure the installation and set root mysql user password//
+mysql_secure_installation
 </code>
@@ Line 33: / Line 43: @@
 </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 ====
@@ 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!
+<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.
@@ Line 46: / Line 59: @@
   * virtual_users - Contains an entry for each virtual mail box served
   * 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 ====
-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:
@@ Line 61: / Line 83: @@
   * 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                    |
@@ Line 74: / Line 96: @@
 </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 ====
 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,
-domain_id=1, //From previous step//
+domain_id=1,
 password=ENCRYPT('user-password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))),
 email='me@my-domain.com';
 </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
 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  |
 +----+-----------+-------------------+
+</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>
 ==== 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,
 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.
-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
 JOIN virtual_aliases A ON(A.domain_id=D.id) WHERE name='my-domain.com';
@@ Line 123: / Line 151: @@
 </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]]
+====== Sources ======
+  * Originally written by [[wiki:user:astrogeek | astrogeek]]
 {{tag>howtos email postfix dovecot mysql}}

Trace:

Differences

Navigation

Search

Toolbox

In Other Languages

SlackDocs

Slackware Links

QR Code