| 1 |
================== |
| 2 |
SQL Authentication |
| 3 |
================== |
| 4 |
|
| 5 |
Pre-requisites |
| 6 |
============== |
| 7 |
|
| 8 |
All examples use MySQL but it should be possible to use PostgresQL or similar database. |
| 9 |
|
| 10 |
MySQL Server |
| 11 |
------------ |
| 12 |
This document assumes that MySQL is running on ``localhost`` and that you can connect as a user who has the required privileges to add users and databases. |
| 13 |
|
| 14 |
We will create a database ``db_mail`` with the ``mail_users`` table and grant SELECT privileges to the user ``db_mail_user`` with password ``db_mail_password`` |
| 15 |
|
| 16 |
The database connection details are completely changeable and you can even use a remote MySQL server, but remember to change them throughout the instructions below. |
| 17 |
|
| 18 |
.. note:: |
| 19 |
The database table doesn't need to be writable by the Cyrus server. If your master database is held on a different server, you could use MySQL replication to replicate just the mail_users table to the Cyrus server and make the table read-only on the replica. |
| 20 |
|
| 21 |
saslauthd |
| 22 |
--------- |
| 23 |
saslauthd needs to be configured to use PAM. In the output of :command:`ps ax | grep saslauthd` it'll probably look like :command:`saslauthd -a pam` |
| 24 |
Optionally ``-r`` (realm) parameter with saslauthd if you plan to use username@domain style logins instead of just username |
| 25 |
|
| 26 |
pam_mysql |
| 27 |
--------- |
| 28 |
pam_mysql (0.7) from http://pam-mysql.sourceforge.net/ - it's quite likely that other MySQL PAM modules would work, but this one definitely works and using a different version will require different configuration to that described below. |
| 29 |
|
| 30 |
Database structure |
| 31 |
================== |
| 32 |
|
| 33 |
At the very minimum, the mail_users table requires username and password columns. It is also recommended to include a column to indicate if the account is permitted to login or not since this will make disabling an account much easier than altering the password field. |
| 34 |
|
| 35 |
.. parsed-literal:: |
| 36 |
CREATE DATABASE db_mail; |
| 37 |
GRANT SELECT ON db_mail.* TO 'db_mail_user'@'localhost' |
| 38 |
IDENTIFIED BY 'db_mail_password'; |
| 39 |
|
| 40 |
USE db_mail; |
| 41 |
|
| 42 |
CREATE TABLE mail_users ( |
| 43 |
username VARCHAR(200) NOT NULL, |
| 44 |
password VARCHAR(40) NOT NULL, |
| 45 |
active TINYINT(1) NOT NULL DEFAULT 1, |
| 46 |
INDEX username_idx(username) |
| 47 |
); |
| 48 |
|
| 49 |
Next insert an example record for testing purposes. The username must match the mailbox created in Cyrus and the login is the full email address. If you don't need/want domain based virtual users, you can also just insert a username without the @example.org |
| 50 |
|
| 51 |
.. parsed-literal:: |
| 52 |
|
| 53 |
INSERT INTO mail_users SET username='[email protected]', |
| 54 |
password=ENCRYPT('janepass','$1$1234abcd'), active=1; |
| 55 |
|
| 56 |
.. warning:: |
| 57 |
You should use better passwords and salts in production! |
| 58 |
|
| 59 |
On modern versions of Linux, the salts used for the password encryption should be $1$ followed by 8 random alphanumeric characters. Other operating systems may have different requirements for the salt as used by the system crypt(3) function and described in the relevant man page. |
| 60 |
|
| 61 |
PAM configuration for use with saslauthd |
| 62 |
======================================== |
| 63 |
|
| 64 |
Assuming both POP3 and IMAP services are being offered, you will need to update /etc/pam.d/pop and /etc/pam.d/imap to allow MySQL to be used. |
| 65 |
|
| 66 |
Assuming the files already contain the following |
| 67 |
|
| 68 |
.. parsed-literal:: |
| 69 |
|
| 70 |
#%PAM-1.0 |
| 71 |
auth required pam_nologin.so |
| 72 |
auth include system-auth |
| 73 |
account include system-auth |
| 74 |
session include system-auth |
| 75 |
|
| 76 |
amend them to read |
| 77 |
|
| 78 |
.. parsed-literal:: |
| 79 |
|
| 80 |
#%PAM-1.0 |
| 81 |
auth required pam_nologin.so |
| 82 |
auth sufficient pam_mysql.so config_file=/etc/mail-pam-mysql.conf |
| 83 |
auth include system-auth |
| 84 |
account sufficient pam_mysql.so config_file=/etc/mail-pam-mysql.conf |
| 85 |
account include system-auth |
| 86 |
password required pam_deny.so |
| 87 |
session include system-auth |
| 88 |
|
| 89 |
The configuration file specified also needs to be created. Using the database connection details established earlier, the configuration file should contain: |
| 90 |
|
| 91 |
.. parsed-literal:: |
| 92 |
|
| 93 |
verbose = 0; |
| 94 |
users.host = localhost; |
| 95 |
users.database = db_mail; |
| 96 |
users.db_user = db_mail_user; |
| 97 |
users.db_passwd = db_mail_password; |
| 98 |
users.password_crypt = 1; |
| 99 |
users.md5 = true; |
| 100 |
|
| 101 |
users.table = mail_users; |
| 102 |
users.where_clause = active = 1; |
| 103 |
users.user_column = username; |
| 104 |
users.password_column = password; |
| 105 |
|
| 106 |
.. warning:: |
| 107 |
Because this file contains the database password, you should ensure it is properly protected. Change the ownership to root:root (if it's not already), and :command:`chmod 600 /etc/mail-pam-mysql.conf` |
| 108 |
|
| 109 |
Changing verbose to 1 results in a large amount of debugging output in the logs, including the SQL being run. This can be useful if it's not working as expected. |
| 110 |
If you are using the MySQL PASSWORD() function, change password_crypt to 1 |
| 111 |
|
| 112 |
|
| 113 |
Testing the SASL configuration |
| 114 |
============================== |
| 115 |
|
| 116 |
If everything is correct, you should be able to run the following commands: |
| 117 |
|
| 118 |
.. parsed-literal:: |
| 119 |
|
| 120 |
$ :command:`testsaslauthd -u jane -r example.org -p janepass -s imap` |
| 121 |
0: OK "Success." |
| 122 |
|
| 123 |
$ :command:`testsaslauthd -u jane -r example.org -p janepas3 -s imap` |
| 124 |
0: NO "authentication failed" |
| 125 |
|
| 126 |
$ :command:`testsaslauthd -u jane -r example.org -p janepass -s pop` |
| 127 |
0: NO "authentication failed" |
| 128 |
|
| 129 |
This fails because we haven't setup the PAM config file for the POP service, update /etc/pam.d/pop by adding the following two 'sufficient' lines above the appropriate 'required' lines. |
| 130 |
|
| 131 |
.. parsed-literal:: |
| 132 |
auth sufficient pam_mysql.so config_file=/etc/mail-pam-mysql.conf |
| 133 |
account sufficient pam_mysql.so config_file=/etc/mail-pam-mysql.conf |
| 134 |
|
| 135 |
If everything is correct, you should be able to run the following and get an OK response. |
| 136 |
|
| 137 |
.. parsed-literal:: |
| 138 |
$ :command:`testsaslauthd -u jane -r example.org -p janepass -s pop` |
| 139 |
0: OK "Success." |
| 140 |
|
| 141 |
Create the test mailboxes within Cyrus |
| 142 |
====================================== |
| 143 |
|
| 144 |
.. parsed-literal:: |
| 145 |
cyradm> cm user/[email protected] |
| 146 |
|
| 147 |
If you get an error when creating the mailbox, check that you have ``virtdomains: userid`` and ``unixhierarchysep: on`` in /etc/imapd.conf as the syntax for the mailbox name will be different. |
| 148 |
|
| 149 |
Testing everything together |
| 150 |
=========================== |
| 151 |
|
| 152 |
This step assumes Cyrus is already configured and listening on localhost port 143 (IMAP). Change the openssl command as required if it's not. |
| 153 |
|
| 154 |
.. parsed-literal:: |
| 155 |
|
| 156 |
$ :command:`openssl s_client -connect localhost:143 -starttls imap` |
| 157 |
... |
| 158 |
. OK Completed |
| 159 |
0 LOGIN [email protected] janepass |
| 160 |
0 OK [CAPABILITY ...] User logged in ... |
| 161 |
0 LOGOUT |
| 162 |
* BYE LOGOUT received |
| 163 |
0 OK Completed |
| 164 |
|
| 165 |
If you don't get an OK response to the LOGIN command, something isn't working properly and there may be useful log messages in either maillog, messages or secure log files. |
| 166 |
|
| 167 |
|
| 168 |
Back to :ref:`imap-features` |