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

[Patrick Nelson]

Jesper K. Pedersen wrote:

>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.
>  
>
 I use PAM-PgSql (on http://sourceforge.net/projects/pam-pgsql/) to 
utilize my Postgresql system.  The only problem is that it isn't being 
actively developed but it does work with the my cyrus-imap 2.2.10 setup 
great.
---
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