sasl-canonuser-plugin via sql

[Dan White]

On 13/05/10 08:49 +0200, Lars Duesing wrote:
>Hi Dan, Hi List,
>
>I've done the whole canonuser into the auxprop-plugin.
>This time it is against cyrus-sasl-2.1.24rc1 plain. No other dependencies.
>
>Would you please have another look at it?
>
>Thanks a lot,
>
>Lars

Lars,

I was able to get it to work - notes are below.

One minor issue is that you might want to still document sql_select and
mark it as deprecated, and then update the 'Notes' and 'Examples' (in
options.html) for the sql plugin to reflect the changes, or maybe just
stick with 'sql_select' for auxprop retrieval.

Please submit your patch to the Cyrus Bugzilla:

https://bugzilla.andrew.cmu.edu/

** Notes **

#> sqlite3 cyrus.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table users (user TEXT, userPassword TEXT);
sqlite> create table canon (login TEXT, user TEXT);
sqlite> insert into users values ('dwhite_04927', 'mysecret');
sqlite> insert into users values ('dwhite_12345', 'mysecret');
sqlite> insert into users values ('dwhite', 'mysecret');
sqlite> insert into canon values ('dwhite_04927', 'dwhite');
sqlite> insert into canon values ('dwhite_12345', 'dwhite');
sqlite> select * from users;
dwhite_04927|mysecret
dwhite_12345|mysecret
dwhite|mysecret
sqlite> select * from canon;
dwhite_04927|dwhite
dwhite_12345|dwhite
sqlite> .quit
#> chown cyrus:mail cyrus.db
#> chmod 640 cyrus.db

#> grep 'sasl\|plaintext' /etc/imapd.conf 
sasl_pwcheck_method: auxprop
sasl_auxprop_plugin: sql
sasl_canon_user_plugin: sql
sasl_sql_engine: sqlite3
sasl_sql_database: /usr/lib/sasl2/cyrus.db
sasl_sql_auxprop_select: select userPassword from users where user='%u'
sasl_sql_canon_select: select user from canon where login='%u'
allowplaintext: yes
sasl_minimum_layer: 0
sasl_log_level: 7

#> imtest -m LOGIN -a dwhite_04927 -w mysecret localhost

Some snippets from /var/log/auth.log:

May 13 14:30:55 zek imap[4258]: sql_canon_plugin using sqlite3 engine with select: select user from canon where login='%u'
May 13 14:30:55 zek imap[4258]: sql_auxprop_plugin using sqlite3 engine with select: select userPassword from users where user='%u'
May 13 14:30:55 zek imap[4258]: sql_canon_server
May 13 14:30:55 zek imap[4258]: sql_canon_server Parse the username dwhite_04927
May 13 14:30:55 zek imap[4258]: sql plugin try and connect to a host
May 13 14:30:55 zek imap[4258]: sql plugin trying to open db '/usr/lib/sasl2/cyrus.db' on host ''
May 13 14:30:55 zek imap[4258]: sql_canon plugin create statement from dwhite_04927 zek
May 13 14:30:55 zek imap[4258]: sql_canon plugin doing query select user from canon where login='dwhite_04927';
May 13 14:30:55 zek imap[4258]: sql_canon plugin create statement from dwhite zek
May 13 14:30:55 zek imap[4258]: sql_canon plugin doing query select user from canon where login='dwhite';
May 13 14:30:55 zek imap[4258]: sql plugin: no result found
May 13 14:30:55 zek imap[4258]: sql_canon_server
May 13 14:30:55 zek imap[4258]: sql_canon_server Parse the username dwhite
May 13 14:30:55 zek imap[4258]: sql plugin try and connect to a host
May 13 14:30:55 zek imap[4258]: sql plugin trying to open db '/usr/lib/sasl2/cyrus.db' on host ''
May 13 14:30:55 zek imap[4258]: sql_canon plugin create statement from dwhite zek
May 13 14:30:55 zek imap[4258]: sql_canon plugin doing query select user from canon where login='dwhite';
May 13 14:30:55 zek imap[4258]: sql plugin: no result found
May 13 14:30:55 zek imap[4258]: sql_auxprop plugin Parse the username dwhite
May 13 14:30:55 zek imap[4258]: sql plugin try and connect to a host
May 13 14:30:55 zek imap[4258]: sql plugin trying to open db '/usr/lib/sasl2/cyrus.db' on host ''
May 13 14:30:55 zek imap[4258]: begin transaction
May 13 14:30:55 zek imap[4258]: sql_auxprop plugin create statement from userPassword dwhite zek
May 13 14:30:55 zek imap[4258]: sql_auxprop plugin doing query select userPassword from users where user='dwhite';
May 13 14:30:55 zek imap[4258]: sql_auxprop plugin create statement from cmusaslsecretPLAIN dwhite zek
May 13 14:30:55 zek imap[4258]: sql_auxprop plugin doing query select userPassword from users where user='dwhite';
May 13 14:30:55 zek imap[4258]: commit transaction
May 13 14:30:55 zek imap[4258]: sql_auxprop plugin Parse the username dwhite
May 13 14:30:55 zek imap[4258]: sql plugin try and connect to a host
May 13 14:30:55 zek imap[4258]: sql plugin trying to open db '/usr/lib/sasl2/cyrus.db' on host ''

#> tail -20 /var/log/syslog (from an earlier attempt)

May 13 14:17:55 zek master[2467]: process 2470 exited, status 0
May 13 14:18:00 zek master[2474]: about to exec /usr/bin/imapd
May 13 14:18:00 zek imap[2474]: executed
May 13 14:18:00 zek imap[2474]: accepted connection
May 13 14:18:00 zek imap[2474]: IOERROR: opening /var/lib/imap/user_deny.db: No such file or directory
May 13 14:18:00 zek imap[2474]: login: zek.olp.net [127.0.0.1] dwhite plaintext User logged in
May 13 14:18:00 zek imap[2474]: IOERROR: opening /var/lib/imap/user_deny.db: No such file or directory
May 13 14:18:02 zek imap[2474]: IOERROR: opening /var/lib/imap/user_deny.db: No such file or directory

which indicates that dwhite_04927 was canonicalized to dwhite.

-- 
Dan White