AW: sasl-canonuser-plugin via sql

[Lars Duesing]

Thank you Dan,

I documented all deprecated options, added a documentation part for
sql_canon added for clarification sql_auxprop_insert and sql_auxprop_update,
put in the correction I mentioned in the mail about ldap-plugin (stringsize
not checked before memcpy in [ldapdb,sql]canon_client )

The full patch is now under bugzilla:
https://bugzilla.andrew.cmu.edu/show_bug.cgi?id=3219

Thanks again, and I hope I did not code too weird ... :)

Lars

-----Ursprüngliche Nachricht-----
Von: Dan White [mailto:dwhite at olp.net] 
Gesendet: Donnerstag, 13. Mai 2010 17:11
An: Lars Duesing
Cc: cyrus-sasl at lists.andrew.cmu.edu
Betreff: Re: sasl-canonuser-plugin via sql

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); create table 
sqlite> canon (login TEXT, user TEXT); insert into users values 
sqlite> ('dwhite_04927', 'mysecret'); insert into users values 
sqlite> ('dwhite_12345', 'mysecret'); insert into users values 
sqlite> ('dwhite', 'mysecret'); insert into canon values 
sqlite> ('dwhite_04927', 'dwhite'); insert into canon values 
sqlite> ('dwhite_12345', 'dwhite'); 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