Problems getting Cyrus Imapd to with with SASL (PostgreSQL engine)

[Jesper K. Pedersen]

I have been wanting to upgrade our small mail server to use a
PostgreSQL database to authenticate users.

As the server needed a full overhaul I have installed a test server with
Slackware 10.1
Downloaded Cyrus SASL 2.1.20, Cyrus IMAPD 2.2.12

I already have my postgres server running without a problem.

I also have SASL compiled for supporting the PostgreSQL database via the
auxprop, and finally also have compiled Imapd and have it all running.

I have created a couple of test users in the sql database with cleartext
passwords.

I am not able to successfully run the imtest:
An example run:
Command: imtest -s -a mailman at solnet localhost
Result:
verify error:num=18:self signed certificate
TLS connection established: TLSv1 with cipher AES256-SHA (256/256 bits)
S: * OK vega Cyrus IMAP4 v2.2.12 server ready
C: C01 CAPABILITY
S: * CAPABILITY IMAP4 IMAP4rev1 ACL QUOTA LITERAL+ MAILBOX-REFERRALS
NAMESPACE UIDPLUS ID NO_ATOMIC_RENAME UNSELECT CHILDREN MULTIAPPEND
BINARY SORT THREAD=ORDEREDSUBJECT THREAD=REFERENCES ANNOTATEMORE IDLE
AUTH=PLAIN SASL-IR S: C01 OK Completed
Please enter your password: 
C: A01 AUTHENTICATE PLAIN AG1haWxtYW5Ac29sbmV0AGFkbWluMjAwNQ==
S: A01 NO authentication failure
Authentication failed. generic failure
Security strength factor: 256
C: Q01 LOGOUT
Connection closed.


My imapd log says the following (please note I made my own self signing
SSL certificate so I could test using TLS/SSL):
------------------------
Apr 11 21:32:59 vega imaps[5666]: starttls: TLSv1 with cipher AES256-SHA
	(256/256 bits new) no authentication 
Apr 11 21:33:02 vega imaps[5666]: Password verification failed
Apr 11 21:33:02 vega imaps[5666]: badlogin: localhost [127.0.0.1] PLAIN
	[SASL(-13): authentication failure: Password verification
	failed]
------------------------


Postgres database log:
------------------------
LOG:  connection received: host=127.0.0.1 port=32870
LOG:  connection authorized: user=/*removed*/ database=emaildb
LOG:  statement: BEGIN;
LOG:  statement: select clearpw from users where email =
	'mailman at solnet'; 
LOG:  statement: select clearpw from users where email =
	'mailman at solnet'; 
LOG:  statement: COMMIT;
------------------------

Doing the SQL query manually using pgsql command line interface to
PostgreSQL gives me the expected password in the field "clearpw".


(cyrus imapd) imapd.conf:
------------------------
configdirectory: /var/imap
partition-default: /var/spool/imap

admins: mailman at solnet mailman

virtdomains: yes
defaultdomain: solnet

sasl_pwcheck_method: auxprop
sasl_mech_list: plain
sasl_auxprop_plugin: sql
sasl_sql_engine: pgsql
sasl_sql_hostnames: localhost
sasl_sql_user: /*removed*/
sasl_sql_passwd: /*removed*/
sasl_sql_database: emaildb
sasl_sql_select: select clearpw from users where email = '%u@%r'
sasl_sql_verbose: yes

tls_key_file: /var/imap/certs/cyrus-global.pem
tls_ca_file: /var/imap/certs/cyrus-global.pem
tls_cert_file: /var/imap/certs/cyrus-global.pem

sendmail: /usr/sbin/sendmail

lmtp_downcase_rcpt: yes
------------------------


cyrus.conf:
------------------------
START {
  recover       cmd="ctl_cyrusdb -r"

  # this is only necessary if using idled for IMAP IDLE
  #  idled              cmd="idled"
}

# UNIX sockets start with a slash and are put into /var/imap/socket
SERVICES {
  # add or remove based on preferences
  imap          cmd="imapd" listen="imap" prefork=0
  imaps         cmd="imapd -s" listen="imaps" prefork=0
  pop3          cmd="pop3d" listen="pop3" prefork=0
  #  pop3s              cmd="pop3d -s" listen="pop3s" prefork=0
  sieve         cmd="timsieved" listen="sieve" prefork=0

  smmapd        cmd="smmapd" listen="/var/imap/socket/smmapd" prefork=1

  # these are only necessary if receiving/exporting usenet via NNTP
  #  nntp               cmd="nntpd" listen="nntp" prefork=0
  #  nntps              cmd="nntpd -s" listen="nntps" prefork=0

  # at least one LMTP is required for delivery
  #  lmtp               cmd="lmtpd" listen="lmtp" prefork=0
  lmtpunix      cmd="lmtpd" listen="/var/imap/socket/lmtp" prefork=0

  # this is only necessary if using notifications
  #  notify     cmd="notifyd" listen="/var/imap/socket/notify"
proto="udp" prefork=1}

EVENTS {
  squatter      cmd="squatter" period=300
  # this is required

  checkpoint    cmd="ctl_cyrusdb -c" period=30
  delprune      cmd="cyr_expire -E 3" at=0400
  tlsprune      cmd="tls_prune" at=0400
}
------------------------


/usr/lib/sasl2/imapd.conf :
------------------------
pwcheck_method: auxprop
mech_list: plain
auxprop_plugin: sql
sql_engine: pgsql
sql_hostnames: localhost
sql_user: /*removed*/
sql_passwd: /*removed*/
sql_database: emaildb
sql_select: select clearpw from users where email = '%u@%r'
sql_verbose: yes
------------------------


Anyone with any ideas of what I am doing wrong, or how I could debug
this further?
Any hints are greatly appreciated.


Best regards,
Jesper K. Pedersen
--
Carpe Aptenodytes!
---
Cyrus Home Page: http://asg.web.cmu.edu/cyrus
Cyrus Wiki/FAQ: http://cyruswiki.andrew.cmu.edu
List Archives/Info: http://asg.web.cmu.edu/cyrus/mailing-list.html