Accounting in MySQL - Password
Hi, This may be a simple question but I was not able to find a setting for this. Basically, I'm using freeradius with LDAP for my user base and MySQL for accounting. I've loaded schema on MySQL, configured freeradius and my two tables get populated properly (radacct and radpostauth). My Clients are using TTLS/PAP and PEAP/MSCHAP protocols. When I look in radpostauth, the "pass" field is populated in clear text when TTLS/PAP protocol is used. Is there a way to disable this or at least encrypt the password? Password logging is only a problem when we use MySQL. Freeradius Log files do not contain passwords. Thanks.
Hi,
This may be a simple question but I was not able to find a setting for this. Basically, I'm using freeradius with LDAP for my user base and MySQL for accounting. I've loaded schema on MySQL, configured freeradius and my two tables get populated properly (radacct and radpostauth).
the default config has this in the dialup.conf file (the configuration that mysql module uses) postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')" edit that appropriately - eg change it to postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ 'PASSWORD', \ '%{reply:Packet-Type}', '%S')" its what I did years ago before putting the service into production....we moved to postgresql in the end for production - so made same change for the postgresql config. alan
Thanks Alan & Alan. That change seemed to work. I did some testing today with the accounting on sql. Please could you explain this so that I can understand the logging better? My clients use TTLS/PAP and PEAP/MSCHAP. +----+-----------------------+----------+---------------+---------------------+ | id | username | pass | reply | authdate | +----+-----------------------+----------+---------------+---------------------+ | 15 | basil | _hidden_ | Access-Reject | 2014-01-13 10:57:20 | | 18 | basil | _hidden_ | Access-Accept | 2014-01-13 11:13:58 | | 19 | anonymous | _hidden_ | Access-Accept | 2014-01-13 11:13:58 | | 20 | basil | _hidden_ | Access-Accept | 2014-01-13 11:15:11 | | 21 | basil | _hidden_ | Access-Accept | 2014-01-13 11:15:11 | | 25 | anonymous | _hidden_ | Access-Reject | 2014-01-13 11:22:52 | | 27 | basil@moo.com | _hidden_ | Access-Reject | 2014-01-13 11:51:03 | | 28 | basil | _hidden_ | Access-Accept | 2014-01-13 11:55:28 | | 29 | basil@moo.com| _hidden_ | Access-Accept | 2014-01-13 11:55:28 | +----+-----------------------+----------+---------------+---------------------+ 15 - PEAP/MSCHAP (Invalid credentials) 18/19 - TTLS/PAP (Valid Credentials with privacy on) 20/21 - TTLS/PAP (Valid Credentials with privacy off) 25 - TTLS/PAP (Invalid credentials with privacy on) 27 - TTLS/PAP (Invalid credentials with privacy on and basil@moo.com as anonymous user) 28/29 - TTLS/PAP (Valid credentials with privacy on and basil@moo.com as anonymous user and basil as actual user) Will "accept" always result in two entries? Is there anything I can do to stop clients from using anonymous or changing anonymous id to anything else like basil@moo.com in the test above? Is there anything I can do to log the actual user that was rejected as in the case of (25)? Thanks. On 10 January 2014 19:42, <A.L.M.Buxey@lboro.ac.uk> wrote:
Hi,
This may be a simple question but I was not able to find a setting for this. Basically, I'm using freeradius with LDAP for my user base and MySQL for accounting. I've loaded schema on MySQL, configured freeradius and my two tables get populated properly (radacct and radpostauth).
the default config has this in the dialup.conf file (the configuration that mysql module uses)
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')"
edit that appropriately - eg change it to
postauth_query = "INSERT INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ 'PASSWORD', \ '%{reply:Packet-Type}', '%S')"
its what I did years ago before putting the service into production....we moved to postgresql in the end for production - so made same change for the postgresql config.
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi,
Will "accept" always result in two entries? Is there anything I can do to stop clients from using anonymous or changing anonymous id to anything else like basil@moo.com in the test above? Is there anything I can do to log the actual user that was rejected as in the case of (25)?
depends where your logging - and yes, you'll get 2 entries if you are logging in inner and outer - the server is just doing what its told. if you want to log the user of the reject then you need to configure logging for the inner post-auth reject section. alan
P K wrote:
Thanks Alan & Alan. That change seemed to work. I did some testing today with the accounting on sql. Please could you explain this so that I can understand the logging better?
It also helps to read the configuration, the debug output, and to understand what you've done.
15 - PEAP/MSCHAP (Invalid credentials) 18/19 - TTLS/PAP (Valid Credentials with privacy on) 20/21 - TTLS/PAP (Valid Credentials with privacy off) 25 - TTLS/PAP (Invalid credentials with privacy on) 27 - TTLS/PAP (Invalid credentials with privacy on and basil@moo.com as anonymous user) 28/29 - TTLS/PAP (Valid credentials with privacy on and basil@moo.com as anonymous user and basil as actual user)
Will "accept" always result in two entries?
Yes, because that's what you told it to do. You're using EAP-TTLS, which has the "outer" session, and "inner" one. You've configured the server to log *both* sessions.
Is there anything I can do to stop clients from using anonymous or changing anonymous id to anything else like basil@moo.com in the test above?
No, because "anonymous" is the identity they're using in the outer session.
Is there anything I can do to log the actual user that was rejected as in the case of (25)?
Yes, configure "sql" in "Post-Auth-Type Reject" in sites-enabled/inner-tunnel. You may have to run 2.2.3 for this. Again, all of these questions are answered by reading the debug output and the configuration *you* created. Alan DeKok.
Thanks Alan. I understand now. I've created my own postauth table and updated the sql query. My query is like this: postauth_query = "INSERT INTO newradpostauth \ (username, usernameouter, usernameinner, reply, authdate, calling_station_id, called_station_id, nasipaddress) \ VALUES ( \ '%{User-Name}', \ '%{outer.request:User-Name}', \ '%{User-Name}', \ '%{reply:Packet-Type}', \ '%S', \ '%{Calling-Station-Id}', \ '%{config:name}', \ '%{NAS-IP-Address}' \ )" This works ok with a few niggles. outer.request:User-Name works when this query is being executed from inner-tunnel but not in outer obviously. So I want to put an if condition. I'm fine with the syntax but i'm struggling to determine what to put a condition on. What variable should I look at to determine if I'm in inner or outer tunnel? I was thinking virtual-server .. not sure. Also I want to record protocol TTLS/PAP or PEAP/MSCHAP that has been in action. What variable should I use for this? Is there a variable that would indicate the module that succeeded e.g ldap, mysql etc. so that I could record it? Auth-Type?? Thanks. On 13 January 2014 13:25, Alan DeKok <aland@deployingradius.com> wrote:
P K wrote:
Thanks Alan & Alan. That change seemed to work. I did some testing today with the accounting on sql. Please could you explain this so that I can understand the logging better?
It also helps to read the configuration, the debug output, and to understand what you've done.
15 - PEAP/MSCHAP (Invalid credentials) 18/19 - TTLS/PAP (Valid Credentials with privacy on) 20/21 - TTLS/PAP (Valid Credentials with privacy off) 25 - TTLS/PAP (Invalid credentials with privacy on) 27 - TTLS/PAP (Invalid credentials with privacy on and basil@moo.com as anonymous user) 28/29 - TTLS/PAP (Valid credentials with privacy on and basil@moo.com as anonymous user and basil as actual user)
Will "accept" always result in two entries?
Yes, because that's what you told it to do. You're using EAP-TTLS, which has the "outer" session, and "inner" one. You've configured the server to log *both* sessions.
Is there anything I can do to stop clients from using anonymous or changing anonymous id to anything else like basil@moo.com in the test above?
No, because "anonymous" is the identity they're using in the outer session.
Is there anything I can do to log the actual user that was rejected as in the case of (25)?
Yes, configure "sql" in "Post-Auth-Type Reject" in sites-enabled/inner-tunnel. You may have to run 2.2.3 for this.
Again, all of these questions are answered by reading the debug output and the configuration *you* created.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Not to worry Alan. Found all the variables in /usr/share/freeradius/dictionary.freeradius.internal. %{control:Auth-Type} - returns whatever the auth type was set to in the end. i.e. ldap in inner tunnel. In outer, sets to EAP. %{EAP-Type} - returns EAP-TTLS for example %Virtual-Server} - returns inner-tunnel inside On 14 January 2014 13:24, P K <getpkme@gmail.com> wrote:
Thanks Alan. I understand now. I've created my own postauth table and updated the sql query. My query is like this:
postauth_query = "INSERT INTO newradpostauth \ (username, usernameouter, usernameinner, reply, authdate, calling_station_id, called_station_id, nasipaddress) \ VALUES ( \ '%{User-Name}', \ '%{outer.request:User-Name}', \ '%{User-Name}', \ '%{reply:Packet-Type}', \ '%S', \ '%{Calling-Station-Id}', \ '%{config:name}', \ '%{NAS-IP-Address}' \ )"
This works ok with a few niggles.
outer.request:User-Name works when this query is being executed from inner-tunnel but not in outer obviously. So I want to put an if condition. I'm fine with the syntax but i'm struggling to determine what to put a condition on. What variable should I look at to determine if I'm in inner or outer tunnel? I was thinking virtual-server .. not sure.
Also I want to record protocol TTLS/PAP or PEAP/MSCHAP that has been in action. What variable should I use for this?
Is there a variable that would indicate the module that succeeded e.g ldap, mysql etc. so that I could record it? Auth-Type??
Thanks.
On 13 January 2014 13:25, Alan DeKok <aland@deployingradius.com> wrote:
P K wrote:
Thanks Alan & Alan. That change seemed to work. I did some testing today with the accounting on sql. Please could you explain this so that I can understand the logging better?
It also helps to read the configuration, the debug output, and to understand what you've done.
15 - PEAP/MSCHAP (Invalid credentials) 18/19 - TTLS/PAP (Valid Credentials with privacy on) 20/21 - TTLS/PAP (Valid Credentials with privacy off) 25 - TTLS/PAP (Invalid credentials with privacy on) 27 - TTLS/PAP (Invalid credentials with privacy on and basil@moo.com as anonymous user) 28/29 - TTLS/PAP (Valid credentials with privacy on and basil@moo.com as anonymous user and basil as actual user)
Will "accept" always result in two entries?
Yes, because that's what you told it to do. You're using EAP-TTLS, which has the "outer" session, and "inner" one. You've configured the server to log *both* sessions.
Is there anything I can do to stop clients from using anonymous or changing anonymous id to anything else like basil@moo.com in the test above?
No, because "anonymous" is the identity they're using in the outer session.
Is there anything I can do to log the actual user that was rejected as in the case of (25)?
Yes, configure "sql" in "Post-Auth-Type Reject" in sites-enabled/inner-tunnel. You may have to run 2.2.3 for this.
Again, all of these questions are answered by reading the debug output and the configuration *you* created.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
P K wrote:
Thanks Alan. I understand now. I've created my own postauth table and updated the sql query. My query is like this:
That should work.
outer.request:User-Name works when this query is being executed from inner-tunnel but not in outer obviously. So I want to put an if condition. I'm fine with the syntax but i'm struggling to determine what to put a condition on. What variable should I look at to determine if I'm in inner or outer tunnel? I was thinking virtual-server .. not sure.
You put a condition on the expansion. See "man unlang" %{%{outer.request:User-Name}:-NONE}
Also I want to record protocol TTLS/PAP or PEAP/MSCHAP that has been in action. What variable should I use for this?
outer.request:EAP-Type.
Is there a variable that would indicate the module that succeeded e.g ldap, mysql etc. so that I could record it? Auth-Type??
No. Because the EAP module is doing authentication. Alan DeKok.
Thanks Alan. Got it working. I've scoured the list and found your reply here in 2010 ( http://lists.freeradius.org/pipermail/freeradius-users/2010-July/047686.html). I'm having the same problem - has this been fixed? post-auth is not being executed in inner-tunnel when the user is *rejected*. As a result no reject entry is being written to the table from inner tunnel.Is there a way to make it execute? I'm running 2.1.10+dfsg-3ubuntu0.12.04.1. #inner tunnel ..snip.. post-auth { ..snip.. sql Post-Auth-Type REJECT { # log failed authentications in SQL, too. sql attr_filter.access_reject } update outer.reply { User-Name = "%{request:User-Name}" } } Thanks for all your help. On 14 January 2014 16:51, Alan DeKok <aland@deployingradius.com> wrote:
P K wrote:
Thanks Alan. I understand now. I've created my own postauth table and updated the sql query. My query is like this:
That should work.
outer.request:User-Name works when this query is being executed from inner-tunnel but not in outer obviously. So I want to put an if condition. I'm fine with the syntax but i'm struggling to determine what to put a condition on. What variable should I look at to determine if I'm in inner or outer tunnel? I was thinking virtual-server .. not sure.
You put a condition on the expansion. See "man unlang"
%{%{outer.request:User-Name}:-NONE}
Also I want to record protocol TTLS/PAP or PEAP/MSCHAP that has been in action. What variable should I use for this?
outer.request:EAP-Type.
Is there a variable that would indicate the module that succeeded e.g ldap, mysql etc. so that I could record it? Auth-Type??
No. Because the EAP module is doing authentication.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 15/01/14 09:31, P K wrote:
Thanks Alan. Got it working.
I've scoured the list and found your reply here in 2010 (http://lists.freeradius.org/pipermail/freeradius-users/2010-July/047686.html). I'm having the same problem - has this been fixed? post-auth is not being executed in inner-tunnel when the user is *rejected*. As a result no reject entry is being written to the table from inner tunnel.Is there a way to make it execute? I'm running 2.1.10+dfsg-3ubuntu0.12.04.1.
Upgrade. That's fixed in 2.2.3 (and a couple of earlier versions, but you don't want to run them)
participants (4)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
P K -
Phil Mayers