[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.
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
howtos:network_services:postfix_dovecot_mysql:database [2015/02/15 07:38 (UTC)] – astrogeek | howtos: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 page is supplemental to main article: [[howtos: |
+ | 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. | ||
+ | |||
+ | 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 and sigfile from a repo, verify it, then install it: | ||
+ | < | ||
+ | # For 32 bit package # | ||
+ | wget http:// | ||
+ | wget http:// | ||
+ | # For 64 bit package # | ||
+ | wget http:// | ||
+ | wget http:// | ||
+ | gpg --verify mariadb-5.5.40...txz.asc {path to your downloaded package sigfile} | ||
+ | installpkg mariadb-5.5.40...txz | ||
+ | </ | ||
+ | < | ||
+ | You will then need to initialize the database server and start it. Follow the instructions found in the top comments of the start script, / | ||
+ | < | ||
+ | mysql_install_db --user=mysql | ||
+ | ... | ||
+ | chmod +x / | ||
+ | / | ||
+ | ... | ||
+ | mysql_secure_installation | ||
+ | </ | ||
+ | |||
+ | ==== Creating Mail DB User and Database ==== | ||
+ | Your database server should now be running and have a root mysql user password set. | ||
+ | |||
+ | Next we need to create a database, and a user to access it on behalf of our mail server. | ||
+ | |||
+ | We will use the highly original names // | ||
+ | |||
+ | You may easily create both as follows, as root (provide the root mysql user password when prompted): | ||
+ | |||
+ | < | ||
+ | mysql -p -e " | ||
+ | mysql -p -e "GRANT ALL ON mailserver.* TO mailuser@localhost IDENTIFIED BY ' | ||
+ | </ | ||
+ | |||
+ | Remember the mailuser password for when we configure postfix and dovecot later. | ||
+ | |||
+ | ==== Creating Mail Database Tables ==== | ||
+ | Neither Postfix nor Dovecot place any restrictions on the data structures you should use to support virtual mail functions. Their only requirements are that you must supply a set of queries that return specific fields as needed. | ||
+ | |||
+ | 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.</ | ||
+ | |||
+ | Figure 2 below, shows the schema of our virtual mail database. | ||
+ | {{ howtos: | ||
+ | |||
+ | * virtual_domains - Contains an entry for each domain name served by virtual email | ||
+ | * 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 | ||
+ | < | ||
+ | |||
+ | mysql --user=" | ||
+ | |||
+ | mysql --user=" | ||
+ | </ | ||
+ | ==== Adding Domain Entries ==== | ||
+ | To add entries to the virtual_domains table using the mysql client: | ||
+ | < | ||
+ | mysql -p mailserver | ||
+ | mysql> INSERT INTO virtual_domains SET id=NULL, name=' | ||
+ | quit | ||
+ | </ | ||
+ | |||
+ | This table must include entries for each domain served by your virtual mail server. | ||
+ | |||
+ | You should add at least the following rows: | ||
+ | |||
+ | * The domain name - my-domain.com | ||
+ | * The hostname - darkstar | ||
+ | * The FQDN of the host - darkstar.my-domain.com | ||
+ | * The localhost.domain variant - localhost.my-domain.com | ||
+ | |||
+ | To see the current rows in this table using the mysql client: | ||
+ | |||
+ | < | ||
+ | +----+-------------------------+ | ||
+ | | id | name | | ||
+ | +----+-------------------------+ | ||
+ | | 1 | my-domain.com | ||
+ | | 2 | darkstar | ||
+ | | 3 | darkstar.my-domain.com | ||
+ | | 4 | localhost.my-domain.com | | ||
+ | +----+-------------------------+ | ||
+ | </ | ||
+ | |||
+ | Note the automatically generated id value for the domain itself, // | ||
+ | |||
+ | ==== 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, used by dovecot for authentication. | ||
+ | |||
+ | To insert a virtual user mailbox from the mysql client, substitute the domain id, actual password and email into the following query: | ||
+ | < | ||
+ | id=NULL, | ||
+ | domain_id=1, | ||
+ | password=ENCRYPT(' | ||
+ | email=' | ||
+ | </ | ||
+ | |||
+ | To see currently assigned mail boxes for a domain (with password column suppressed): | ||
+ | < | ||
+ | JOIN virtual_users U ON(U.domain_id=D.id) WHERE name=' | ||
+ | +----+-----------+-------------------+ | ||
+ | | id | domain_id | email | | ||
+ | +----+-----------+-------------------+ | ||
+ | | 1 | 1 | me@my-domain.com | ||
+ | | 2 | 1 | you@my-domain.com | | ||
+ | | 3 | 1 | us@my-domain.com | ||
+ | +----+-----------+-------------------+ | ||
+ | </ | ||
+ | |||
+ | To reset the password for an email user, substitute the new password and email values into this query: | ||
+ | < | ||
+ | password=ENCRYPT(' | ||
+ | WHERE email=' | ||
+ | </ | ||
+ | |||
+ | ==== Adding Mail Box Alias Entries ==== | ||
+ | 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 using the mysql client: | ||
+ | < | ||
+ | id=NULL, | ||
+ | domain_id=1, | ||
+ | source=' | ||
+ | destination=' | ||
+ | </ | ||
+ | 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: | ||
+ | < | ||
+ | JOIN virtual_aliases A ON(A.domain_id=D.id) WHERE name=' | ||
+ | +----+-----------+---------------------+-------------------+ | ||
+ | | id | domain_id | source | ||
+ | +----+-----------+---------------------+-------------------+ | ||
+ | | 1 | 1 | alias@my-domain.com | you@my-domain.com | | ||
+ | +----+-----------+---------------------+-------------------+ | ||
+ | </ | ||
+ | |||
+ | 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, | ||
[[howtos: | [[howtos: | ||
- | {{tag> | + | ====== Sources ====== |
+ | * Originally written by [[wiki: | ||
+ | {{tag> |