# Courier authentication troubles

## expat_iain

Have a test setup that uses exim, mysql and courier-imap. The user table in MySQL is common to exim and courier and contains the following fields:

```
mysql> describe users;

+---------+--------------+------+-----+---------+-------+

| Field   | Type         | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id      | int(11)      |      | PRI | 0       |       |

| email   | varchar(50)  |      | UNI |         |       |

| crypt   | varchar(128) |      |     |         |       |

| clear   | varchar(128) |      |     |         |       |

| name    | varchar(128) |      |     |         |       |

| uid     | int(2)       |      |     | 8       |       |

| gid     | int(2)       |      |     | 12      |       |

| home    | varchar(255) |      |     |         |       |

| maildir | varchar(255) |      |     |         |       |

| quota   | varchar(255) |      |     |         |       |

| active  | set('Y','N') |      |     | Y       |       |

+---------+--------------+------+-----+---------+-------+

11 rows in set (0.00 sec)

```

Exim delivers mail to the related directories in maildir format, all good so far. When trying to connect via IMAP the connection fails as follows:

```
09:51:36[iain@neteng-iain]$ telnet 10.10.10.64 143

Trying 10.10.10.64...

Connected to 10.10.10.64.

Escape character is '^]'.

* OK [CAPABILITY IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA AUTH=CRAM-MD5 IDLE SMAP1 KEYWORDS ACL ACL2=UNION STARTTLS] Courier-IMAP ready. Copyright 1998-2005 Double Precision, Inc.  See COPYING for distribution information.

A LOGIN iain@example.com xxxxxxxx

* BYE Temporary problem, please try again later

Connection closed by foreign host.

```

In /var/log/syslog is listed the following error:

```
Mar  3 09:59:34 localhost imapd: LOGIN FAILED, user=iain@example.com, ip=[10.10.10.183]

Mar  3 09:59:34 localhost imapd: authentication error: Input/output error

```

Checking the mysql.log file shows a query:

```
SELECT email,crypt,clear,name,uid,gid,home,maildir,quota FROM users WHERE email='iain@example.com' AND active='Y'

```

...and if I run the query manually (logging in as the mailadmin user) I get the expected result back.

My /etc/courier/authlib/authmysqlrc file contains:

```
MYSQL_SERVER            localhost

MYSQL_USERNAME          mailadmin

MYSQL_PASSWORD          xxxxxxxx

MYSQL_SOCKET            /var/run/mysqld/mysqld.sock

MYSQL_PORT              3306

MYSQL_DATABASE          mailsql

MYSQL_USER_TABLE        users

MYSQL_CLEAR_PWFIELD     clear

MYSQL_UID_FIELD         uid

MYSQL_GID_FIELD         gid

MYSQL_LOGIN_FIELD       email

MYSQL_HOME_FIELD        home

MYSQL_NAME_FIELD        name

MYSQL_MAILDIR_FIELD     maildir

MYSQL_QUOTA_FIELD       quota

MYSQL_SELECT_CLAUSE     SELECT email,crypt,clear,name,uid,gid,home,maildir,quota FROM users WHERE email='$(local_part)@$(domain)' AND active='Y';

```

For the sake of clarity, the /etc/courier-imap/imapd file contains:

```
ADDRESS=0.0.0.0

PORT=143

MAXDAEMONS=40

MAXPERIP=4

PIDFILE=/var/run/imapd.pid

TCPDOPTS="-nodnslookup -noidentlookup"

IMAP_CAPABILITY="IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA AUTH=CRAM-MD5 IDLE"

IMAP_KEYWORDS=1

SMAP_CAPABILITY=SMAP1

IMAP_CAPABILITY_ORIG="IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA AUTH=CRAM-MD5 AUTH=CRAM-SHA1 IDLE"

IMAP_PROXY=0

IMAP_PROXY_FOREIGN=0

IMAP_IDLE_TIMEOUT=60

IMAP_CAPABILITY_TLS="$IMAP_CAPABILITY AUTH=PLAIN"

IMAP_CAPABILITY_TLS_ORIG="$IMAP_CAPABILITY_ORIG AUTH=PLAIN"

IMAP_DISABLETHREADSORT=0

IMAP_CHECK_ALL_FOLDERS=0

IMAP_OBSOLETE_CLIENT=0

IMAP_UMASK=022

IMAP_ULIMITD=65536

IMAP_USELOCKS=1

IMAP_SHAREDINDEXFILE=/etc/courier-imap/shared/index

IMAP_ENHANCEDIDLE=0

IMAP_TRASHFOLDERNAME=Trash

IMAP_EMPTYTRASH=Trash:30,Sent:30

IMAP_MOVE_EXPUNGE_TO_TRASH=0

SENDMAIL=/usr/sbin/sendmail

HEADERFROM=X-IMAP-Sender

IMAPDSTART=NO

MAILDIRPATH=mail

MAILDIR=mail

PRERUN=

```

...and the /etc/courier/authlib/authdaemorc file contains:

```
authmodulelist="authmysql authpam"

authmodulelistorig="authcustom authcram authuserdb authldap authmysql authpam"

daemons=5

authdaemonvar=/var/lib/courier/authdaemon

DEBUG_LOGIN=2

DEFAULTOPTIONS=""

```

As far as I can see, this is all should be working fine. But I cannot get a detialed reason as to why I cannot get the mail. Oh, the mail directory is owned by mail:mail all the way down and mails are delivered under uid/gid 8/12.

Any ideas??

Iain.

----------

## acasto

I just had the same problem. Try moving, or I just symlinked the /etc/courier/authlib/authdaemonrc file to /etc/courier/authdaemonrc. Try this: 

```
# cd /etc/courier/

# ln -s authlib/authdaemonrc authdaemonrc
```

I don't know why this happens, maybe something changed.

- Adam

----------

## expat_iain

Linked, restarted courier-authlib...same problem.  :Sad: 

Regs.

Iain.

----------

## j-m

Did you compile with USE="fam"? If so, is fam running?

----------

## expat_iain

 *j-m wrote:*   

> Did you compile with USE="fam"? If so, is fam running?

 

No.

```
mail2 root # emerge courier-imap -pv

These are the packages that I would merge, in order:

Calculating dependencies ...done!

[ebuild   R   ] net-mail/courier-imap-4.0.1  +berkdb -debug -fam +gdbm -ipv6 +nls (-selinux) 0 kB
```

My USE flags are as follows:

```
mail2 root # grep USE /etc/make.conf

USE="mysql ldap imap ssl apache2 php mmx sse -gtk -gtk2 -gnome -gnome2 -kde -qt -ipv6 -X"
```

If that's of any help.

Is there a method to increase the verbosity of the imapd messages?? Really need to find out *why* it fails. Had looked at Cyrus as a solution, but have Courier working great on another machine and prefer to work with maildirs.

Regs.

Iain.

----------

## j-m

```

MYSQL_SELECT_CLAUSE     SELECT email,crypt,clear,name,uid,gid,home,maildir,quota FROM users WHERE

```

I don´t think this is correct. You are missing a definition there:

```

MYSQL_CRYPT_PWFIELD     crypt

```

----------

## expat_iain

 *j-m wrote:*   

> 
> 
> ```
> 
> MYSQL_SELECT_CLAUSE     SELECT email,crypt,clear,name,uid,gid,home,maildir,quota FROM users WHERE
> ...

 

From /etc/courier/authlib/authmysqlrc:

```
##NAME: MYSQL_CRYPT_PWFIELD:0

#

# Either MYSQL_CRYPT_PWFIELD or MYSQL_CLEAR_PWFIELD must be defined.  Both

# are OK too. crypted passwords go into MYSQL_CRYPT_PWFIELD, cleartext

# passwords go into MYSQL_CLEAR_PWFIELD.  Cleartext passwords allow

# CRAM-MD5 authentication to be implemented.

# MYSQL_CRYPT_PWFIELD   crypt

```

But will try it and see...

Iain,

----------

## j-m

 *expat_iain wrote:*   

> 
> 
> From /etc/courier/authlib/authmysqlrc:
> 
> ```
> ...

 

Sure, but you have to define it. You have a line missing there. It cannot be commented out if you are doing the crypt field select from MySQL...

----------

## expat_iain

Took out the comment for the crypt field, restarted courier-authlib and tried again. Same failure. FWIW, the failure is instant. Then commented out the crypt field and modified the SQL clause to:

```
MYSQL_SELECT_CLAUSE     SELECT email,clear,name,uid,gid,home,maildir,quota FROM mailsql.users WHERE email='$(local_part)@$(domain)' AND active='Y';
```

this time there was a failure once more, but there was about a four second timeout before the failure message came back. Log files comtain same error info as above.

Next up, tried commenting out clear, uncommenting crypt and modifying SQL clause to relfect...same symptoms with four second timeout.

Ring any bells??

Regs.

Iain.

----------

## j-m

Eh, does the user have the privileges to use that MySQL database at all? Also, I don´t really understand what are you trying to achieve with this one, it is not defined anywhere... 

```

email='$(local_part)@$(domain)'

```

 :Question: 

----------

## expat_iain

The user defined is 'mailadmin'. I can login using that account, run the select query manually and get the results back as follows:

```
mail2 root # mysql -u mailadmin -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 36 to server version: 4.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT email,clear,name,uid,gid,home,maildir,quota FROM mailsql.users WHERE email='iain@example.com' AND active='Y';

+--------------------+----------+------+-----+-----+---------------------------+-------------------------------------+-------+

| email              | clear    | name | uid | gid | home                      | maildir                             | quota |

+--------------------+----------+------+-----+-----+---------------------------+-------------------------------------+-------+

| iain@example.com | xxxxxxxx | Iain |   8 |  12 | /home/mail/example.com/iain | /home/mail/example.com/iain/mail/ |       |

+--------------------+----------+------+-----+-----+---------------------------+-------------------------------------+-------+

1 row in set (0.00 sec)

mysql> exit

Bye

```

The part of the query

```
email='$(local_part)@$(domain)'

```

Does a query on the full email address as shown in the SQL log:

```
050304 13:52:29      36 Query       SELECT email,clear,name,uid,gid,home,maildir,quota FROM mailsql.users WHERE email='iain@example.com' AND active='Y'

```

Last edited by expat_iain on Mon Mar 07, 2005 1:36 pm; edited 2 times in total

----------

## j-m

OK, last try. Disable authpam and restart. If that does not work, I give up...

----------

## expat_iain

Surely if I disable authpam, I'll not be authenticating via MySQL??

Regs.

Iain.

----------

## j-m

 *expat_iain wrote:*   

> Surely if I disable authpam, I'll not be authenticating via MySQL??
> 
> Regs.
> 
> Iain.

 

You are using PAM for this? Perhaps you should have said pretty much earlier...

If you are using PAM, you need something like this in /etc/pam.d/imap (pop)

```

auth     optional       pam_mysql.so host=localhost db=mailsql user=mailadmin \

    passwd=xxxxxx table=users usercolumn=email passwdcolumn=clear crypt=0

account  required       pam_mysql.so host=localhost db=mailsql user=mailadmin \

    passwd=xxxxxx table=users usercolumn=email passwdcolumn=clear crypt=0

```

Crypt does not work with CRAM-MD5. You also need to comment out the whole MYSQL_SELECT_CLAUSE quoted above in authmysqlrc.

----------

## expat_iain

Been down with flu past few days.

Okay, in my /etc/pam.d/imap file I have the following:

```
auth     optional       pam_mysql.so host=localhost db=mailsql user=mailadmin passwd=$password table=users usercolumn=email passwdcolumn=clear crypt=0

account  required       pam_mysql.so host=localhost db=mailsql user=mailadmin passwd=$password table=users usercolumn=email passwdcolumn=clear crypt=0
```

The '$password' is literal. I have this same setup working on another machine, and know that the SQL query is executing as in /var/log/mysql/mysql.log I see the query listed:

```
050307 14:26:29      45 Connect     mailadmin@localhost on

                     45 Init DB     mailsql

                     45 Query       SELECT email, "", clear, name, uid, gid, home, maildir, quota, "" FROM mailsql.users WHERE email="iain@example.com" AND active="Y"
```

The problem would appear to be somewhere in the courier handling...??

Iain.

----------

## Pingu1979

Glad to see someone else is having this problem... but I'm getting exactly the same error with just the authmysql module, I don't use PAM at all.

----------

## expat_iain

Enable logging in MySQL and check the query. Then pass the query shown into a MySQL session logging in as the specified user.

Still getting problems, post your related configs.

Regs.

Iain.

----------

## Pingu1979

The SQL query works if I put it in manually but not when it's done by courier-authlib/courier-imap which is exactly what was happening to you I believe.

Anyway, here are my related configs and the layout of the MySQL table.

authdaemonrc:

```
authmodulelist="authmysql"

daemons=5

authdaemonvar=/var/lib/courier/authdaemon

DEBUG_LOGIN=2
```

authmysqlrc:

```
MYSQL_SERVER            192.168.0.3

MYSQL_USERNAME          mail

MYSQL_PASSWORD          ********

MYSQL_OPT               0

MYSQL_DATABASE          mail

MYSQL_USER_TABLE        users

MYSQL_CRYPT_PWFIELD     crypt

MYSQL_CLEAR_PWFIELD     clear

MYSQL_UID_FIELD         uid

MYSQL_GID_FIELD         gid

MYSQL_LOGIN_FIELD       email

MYSQL_HOME_FIELD        homedir

MYSQL_NAME_FIELD        fullname

MYSQL_MAILDIR_FIELD     maildir

MYSQL_SELECT_CLAUSE SELECT email, \

            '', clear, '', '', \

            maildir, '', '', '' ,'' FROM \

            users WHERE email=CONCAT('$(local_part)', '@', '$(domain)')
```

```
mysql> describe users;

+---------------+---------------------+------+-----+----------------+-------+

| Field         | Type                | Null | Key | Default        | Extra |

+---------------+---------------------+------+-----+----------------+-------+

| email         | varchar(255)        |      | PRI |                |       |

| crypt         | varchar(32)         |      |     |                |       |

| clear         | varchar(255)        |      |     |                |       |

| uid           | tinyint(3) unsigned |      |     | 8              |       |

| gid           | tinyint(3) unsigned |      |     | 12             |       |

| homedir       | varchar(255)        |      |     |                |       |

| maildir       | varchar(255)        |      |     |                |       |

| fullname      | varchar(255)        |      |     |                |       |

+---------------+---------------------+------+-----+----------------+-------+

8 rows in set (0.00 sec)
```

----------

