SQL Authentication¶

Pre-requisites¶

All examples use MySQL but it should be possible to use PostgresQL or similar database.

MySQL Server¶

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.

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

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.

Note

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.

saslauthd¶

saslauthd needs to be configured to use PAM. In the output of ps ax | grep saslauthd it’ll probably look like saslauthd -a pam Optionally -r (realm) parameter with saslauthd if you plan to use username@domain style logins instead of just username

pam_mysql¶

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.

Database structure¶

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.

CREATE DATABASE db_mail;
GRANT SELECT ON db_mail.* TO 'db_mail_user'@'localhost'
    IDENTIFIED BY 'db_mail_password';

USE db_mail;

CREATE TABLE mail_users (
    username VARCHAR(200) NOT NULL,
    password VARCHAR(40) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    INDEX username_idx(username)
);

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

INSERT INTO mail_users SET username='jane@example.org',
    password=ENCRYPT('janepass','$1$1234abcd'), active=1;

Warning

You should use better passwords and salts in production!

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.

PAM configuration for use with saslauthd¶

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.

Assuming the files already contain the following

#%PAM-1.0
auth       required     pam_nologin.so
auth       include      system-auth
account    include      system-auth
session    include      system-auth

amend them to read

#%PAM-1.0
auth       required     pam_nologin.so
auth       sufficient   pam_mysql.so config_file=/etc/mail-pam-mysql.conf
auth       include      system-auth
account    sufficient   pam_mysql.so config_file=/etc/mail-pam-mysql.conf
account    include      system-auth
password   required     pam_deny.so
session    include      system-auth

The configuration file specified also needs to be created. Using the database connection details established earlier, the configuration file should contain:

verbose = 0;
users.host = localhost;
users.database = db_mail;
users.db_user = db_mail_user;
users.db_passwd = db_mail_password;
users.password_crypt = 1;
users.md5 = true;

users.table = mail_users;
users.where_clause = active = 1;
users.user_column = username;
users.password_column = password;

Warning

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 chmod 600 /etc/mail-pam-mysql.conf

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. If you are using the MySQL PASSWORD() function, change password_crypt to 1

Testing the SASL configuration¶

If everything is correct, you should be able to run the following commands:

$ testsaslauthd -u jane -r example.org -p janepass -s imap
0: OK "Success."

$ testsaslauthd -u jane -r example.org -p janepas3 -s imap
0: NO "authentication failed"

$ testsaslauthd -u jane -r example.org -p janepass -s pop
0: NO "authentication failed"

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.

auth       sufficient   pam_mysql.so config_file=/etc/mail-pam-mysql.conf
account    sufficient   pam_mysql.so config_file=/etc/mail-pam-mysql.conf

If everything is correct, you should be able to run the following and get an OK response.

$ testsaslauthd -u jane -r example.org -p janepass -s pop
0: OK "Success."

Create the test mailboxes within Cyrus¶

cyradm> cm user/jane@example.org

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.

Testing everything together¶

This step assumes Cyrus is already configured and listening on localhost port 143 (IMAP). Change the openssl command as required if it’s not.

$ openssl s_client -connect localhost:143 -starttls imap
...
. OK Completed
0 LOGIN jane@example.org janepass
0 OK [CAPABILITY ...] User logged in ...
0 LOGOUT
* BYE LOGOUT received
0 OK Completed

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.

Back to Features

Cyrus IMAP