Using encode/decode with SASL and Mysql

• Previous message: Estevam Henrique Carvalho: "RES: Kerberos x pam"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

We've decided to keep our SASL clear-text passwords in a Mysql
database, but to use the Mysql encode() and decode() functions to
store and retrieve them. The main benefit of this decision is that
clear-text passwords are not visible to anyone accidentially viewing
the table. They also do not appear in the dumps and on the backup
tapes.

This is still not very secure, but it's adequate, given the benefits
of maintaining clear-text passwords. The encryption key must be coded
in the SQL statements that reside in imapd.conf. It's also sent over
the network along with each SQL query.

My first attempt was with these statements in imapd.conf:

 sasl_sql_select: select decode(%p,'secret') from sasl where username = '%u' and realm = '%r'
 sasl_sql_insert: insert into sasl (username, realm, %p) values ('%u', '%r', encode('%v','secret'))
 sasl_sql_update: update sasl set %p = encode('%v','secret') where username = '%u' and realm = '%r'

This mostly worked, except that `sasl_auto_transition' caused this error:

 Mar 17 10:44:30 setup01 imapd[2086]: [ID 702911 auth.error] sql query failed: You have an error in your SQL syntax near '*,'secret') from sasl where username = 'mills' and realm = 'testing.umanitoba.' at line 1

This happened because the SQL auxprop sets the property (%p) to "*",
which is not valid in the decode() function. It's attempting to
determine if the user exists in the table. The only solution I could
find was to avoid %p this way:

 sasl_sql_select: select decode(userPassword,'secret') from sasl where username = '%u' and realm = '%r'

Is there a better way?

-- 
-Gary Mills-    -Unix Support-    -U of M Academic Computing and Networking-

• Previous message: Estevam Henrique Carvalho: "RES: Kerberos x pam"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]