Debian Codegraph @

doc/html/_sources/imap/concepts/features/authentication-sql.rst.txt

raw
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`