[Solved] Authentication over freeradius database
Added by dreadbit over 7 years ago
Hello, I'm trying to make auth.backend mysql to authenticate over freeradius database.
That's what I have:
auth.backend.mysql.users_table = "radcheck".
auth.backend.mysql.col_user = " attribute = 'Cleartext-Password' and (expire is NULL or expire > now() ) and op = ':=' and username "
auth.backend.mysql.col_pass = "value"
auth.backend.mysql.col_realm = " username !"
, that generates the requests, which, as I suspect, look like SELECT value FROM radcheck WHERE attribute = 'Cleartext-Password' and (expire is NULL or expire > now() ) and op = ':=' and username ='thatsme' AND username !='web12';
(expire field is my specific addition to freeradius table) and - I suspect this should work, but, as you guess, it does not, saying (mod_auth.c.500) password doesn't match for / username: thatsme, IP: 192.168.66.61
(but not mysql error)
The question is simple: how to debug that?
Some notes: I do not have anything like realm in my mysql table, so I do the auth.backend.mysql.col_realm = " username !" trick.
That's what my table looks like
+----+----------+--------------------+----+-------+--------+ | id | username | attribute | op | value | expire | +----+----------+--------------------+----+-------+--------+ | 1 | thatsme | Cleartext-Password | := | bebebe| NULL | +----+----------+--------------------+----+-------+--------+
Any ideas?
Replies (4)
RE: Authenticaton over freeradius database - Added by gstrauss over 7 years ago
The question is simple: how to debug that?
Answer: debug the same way you might debug just about anything else: verify things piecemeal. Step 1: ignore lighttpd. Manually issue the SQL command from a MySQL shell to the database and see if it gives you back what you think it should give you back. Do you get back exactly 1 row, with the clear-text password (in your case) in the first column of the result? That's what you appear to be expecting.
However, mod_authn_mysql is expecting either a crypted password or the md5sum of the password. Why is that, you ask? Well, first, it is generally considered a good practice to avoid storing clear-text passwords in a database. Second, mod_authn_mysql was based, in part, on the older patches available at https://svnweb.freebsd.org/ports/tags/RELEASE_10_3_0/www/lighttpd/files/extra-patch-mysqlauth?revision=411747&view=markup (which I do not recommend using).
RE: Authenticaton over freeradius database - Added by dreadbit over 7 years ago
gstrauss wrote:
The question is simple: how to debug that?
Answer: debug the same way you might debug just about anything else: verify things piecemeal. Step 1: ignore lighttpd.
That was the question - I want to see sql query somewehere in debug logs.
Do you get back exactly 1 row, with the clear-text password (in your case) in the first column of the result? That's what you appear to be expecting.
However, mod_authn_mysql is expecting either a crypted password or the md5sum of the password.
And that's the answer! It helped! (By changing auth.backend.mysql.col_pass = "md5(value)")
Why is that, you ask?
No-no-no, I do not! I clearly understand that keepeing unencrypted password is evil, but that's unclear for mysql auth module docs (or maybe I'm blind). And nothing made me expect is is not clear text - just because lighhtpd uses storing clean passwords in file as one of legal mechanisms.
However, the existing FreeRadius installation behaves this way.
I'd be even happier if the method of generating queries be a little more flexible, but, well, that is all I want for now, thanks!
RE: [Solved] Authentication over freeradius database - Added by gstrauss over 7 years ago
Glad you have a solution, and thanks for the update.
FYI: FreeRADIUS can be configured to use encrypted passwords from MySQL, but as you noted, such is not always immediately available if integrating with an existing installation.
Regarding mod_authn_mysql documentation: yes, it should be better documented that an encrypted password is expected from the database. Thanks for the feedback.
RE: [Solved] Authentication over freeradius database - Added by dreadbit over 7 years ago
But speaking generally, there is not guarantee that the password is stored in plain text, in md5 or even encrypted by some sane method which database is willing to share. So it seems logical that lighttpd creates SQL request (via sprintf()-alike syntax or like that) with password included, and does not even attempt to fetch anything meaningful from database, just checking if there is a row in reply for a successful auth.
So if anyone is going to redesign something in this corner of code in some future, please, keep that in mind.