Freeradius 3.0.12 doesn't send any data through mysql

by naven   Last Updated October 19, 2019 16:00 PM

After upgrading from v 2.x, freeradius stopped working for me. I have done multiple tshooting steps which led me to believe that the application is not sending any data through MySQL, even though it states to do so.

So what I've discovered is that freeradius should send query to DB each time a user tries to authenticate. Several queries in fact.

To verify if there is any traffic going to the MySQL server I did tcpdmp:

tcpdump -i eth0 -n | grep 192.168.32.13 | grep -v ARP

After restarting the service and attempting to authenticate using radius there was nothing logged by tcpdump. It works if I do it manually (so neither the connection nor credentials are a problem).

Freeradius keeps saying that the user is not found in any groups:

(0) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'naven' ORDER BY priority
(0) sql: User not found in any groups

But the query directly in the DB is yielding a result:

MariaDB [radius]> SELECT groupname FROM radusergroup WHERE username = 'naven' ORDER BY priority;
+-----------+
| groupname |
+-----------+
| admin     |
+-----------+
1 row in set (0.00 sec)

Also the below INSERT query is never saved to a database.

(0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S')
(0) sql:    --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'naven', 'XXXXXXXXXXXXXX', 'Access-Reject', '2019-10-19 16:44:03')
(0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'naven', 'XXXXXXXXXXXXXX', 'Access-Reject', '2019-10-19 16:44:03')

mysql -u radius -pEpyGju6EogSFua4u -h 192.168.32.13
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16773
Server version: 10.1.41-MariaDB-0+deb9u1 Debian 9.9

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT groupname FROM radusergroup WHERE username = 'naven' ORDER BY priority;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> use radius;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [radius]> SELECT groupname FROM radusergroup WHERE username = 'naven' ORDER BY priority;
+-----------+
| groupname |
+-----------+
| admin     |
+-----------+
1 row in set (0.00 sec)

MariaDB [radius]> select * from radpostauth;
+----+----------+--------------------------------------+---------------+---------------------+
| id | username | pass                                 | reply         | authdate            |
+----+----------+--------------------------------------+---------------+---------------------+
|  1 | naven    | XXXXXXXXXXXXXX | Access-Reject | 2019-10-19 15:59:45 |
+----+----------+--------------------------------------+---------------+---------------------+
1 row in set (0.00 sec)

I have added the radpostauth entry manually. I copied the query from some previous log to make sure it works. Also using above method to connect to MySQL confirmed my tcpdump working correctly- I could see legitimate MySQL traffic between the servers.

Throughout the post I only redacted some hostnames and 'pass' from radpostauth.

Server fault is limiting my message to 30k lines so I can't post whole freeradius -X log.

freeradius -X
FreeRADIUS Version 3.0.12
Copyright (C) 1999-2016 The FreeRADIUS server project and contributors
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE
You may redistribute copies of FreeRADIUS under the terms of the
GNU General Public License
For more information about these matters, see the file named COPYRIGHT
Starting - reading configuration files ...
including dictionary file /usr/share/freeradius/dictionary
including dictionary file /usr/share/freeradius/dictionary.dhcp
including dictionary file /usr/share/freeradius/dictionary.vqp
including dictionary file /etc/freeradius/3.0/dictionary
including configuration file /etc/freeradius/3.0/radiusd.conf
including configuration file /etc/freeradius/3.0/proxy.conf
including configuration file /etc/freeradius/3.0/clients.conf
including files in directory /etc/freeradius/3.0/mods-enabled/
including configuration file /etc/freeradius/3.0/mods-enabled/radutmp
including configuration file /etc/freeradius/3.0/mods-enabled/linelog
including configuration file /etc/freeradius/3.0/mods-enabled/unix
including configuration file /etc/freeradius/3.0/mods-enabled/expiration
including configuration file /etc/freeradius/3.0/mods-enabled/cache_eap
including configuration file /etc/freeradius/3.0/mods-enabled/dynamic_clients
including configuration file /etc/freeradius/3.0/mods-enabled/mschap
including configuration file /etc/freeradius/3.0/mods-enabled/detail.log
including configuration file /etc/freeradius/3.0/mods-enabled/sradutmp
including configuration file /etc/freeradius/3.0/mods-enabled/detail
including configuration file /etc/freeradius/3.0/mods-enabled/files
including configuration file /etc/freeradius/3.0/mods-enabled/pap
including configuration file /etc/freeradius/3.0/mods-enabled/attr_filter
including configuration file /etc/freeradius/3.0/mods-enabled/echo
including configuration file /etc/freeradius/3.0/mods-enabled/digest
including configuration file /etc/freeradius/3.0/mods-enabled/logintime
including configuration file /etc/freeradius/3.0/mods-enabled/ntlm_auth
including configuration file /etc/freeradius/3.0/mods-enabled/soh
including configuration file /etc/freeradius/3.0/mods-enabled/eap
including configuration file /etc/freeradius/3.0/mods-enabled/preprocess
including configuration file /etc/freeradius/3.0/mods-enabled/replicate
including configuration file /etc/freeradius/3.0/mods-enabled/sql
including configuration file /etc/freeradius/3.0/mods-config/sql/main/mysql/queries.conf
including configuration file /etc/freeradius/3.0/mods-enabled/unpack
including configuration file /etc/freeradius/3.0/mods-enabled/always
including configuration file /etc/freeradius/3.0/mods-enabled/exec
including configuration file /etc/freeradius/3.0/mods-enabled/utf8
including configuration file /etc/freeradius/3.0/mods-enabled/expr
including configuration file /etc/freeradius/3.0/mods-enabled/realm
including configuration file /etc/freeradius/3.0/mods-enabled/chap
including configuration file /etc/freeradius/3.0/mods-enabled/passwd
including files in directory /etc/freeradius/3.0/policy.d/
including configuration file /etc/freeradius/3.0/policy.d/moonshot-targeted-ids
including configuration file /etc/freeradius/3.0/policy.d/debug
including configuration file /etc/freeradius/3.0/policy.d/accounting
including configuration file /etc/freeradius/3.0/policy.d/cui
including configuration file /etc/freeradius/3.0/policy.d/filter
including configuration file /etc/freeradius/3.0/policy.d/dhcp
including configuration file /etc/freeradius/3.0/policy.d/eap
including configuration file /etc/freeradius/3.0/policy.d/abfab-tr
including configuration file /etc/freeradius/3.0/policy.d/control
including configuration file /etc/freeradius/3.0/policy.d/canonicalization
including configuration file /etc/freeradius/3.0/policy.d/operator-name
including files in directory /etc/freeradius/3.0/sites-enabled/
including configuration file /etc/freeradius/3.0/sites-enabled/inner-tunnel
including configuration file /etc/freeradius/3.0/sites-enabled/default
main {
 security {
    user = "freerad"
    group = "freerad"
    allow_core_dumps = no
 }
    name = "freeradius"
    prefix = "/usr"
    localstatedir = "/var"
    logdir = "/var/log/freeradius"
    run_dir = "/var/run/freeradius"
}
main {
    name = "freeradius"
    prefix = "/usr"
    localstatedir = "/var"
    sbindir = "/usr/sbin"
    logdir = "/var/log/freeradius"
    run_dir = "/var/run/freeradius"
    libdir = "/usr/lib/freeradius"
    radacctdir = "/var/log/freeradius/radacct"
    hostname_lookups = no
    max_request_time = 30
    cleanup_delay = 5
    max_requests = 16384
    pidfile = "/var/run/freeradius/freeradius.pid"
    checkrad = "/usr/sbin/checkrad"
    debug_level = 0
    proxy_requests = yes
 log {
    stripped_names = no
    auth = no
    auth_badpass = no
    auth_goodpass = no
    colourise = yes
    msg_denied = "You are already logged in - access denied"
 }
 resources {
 }
 security {
    max_attributes = 200
    reject_delay = 1.000000
    status_server = yes
 }
}
radiusd: #### Loading Realms and Home Servers ####
 proxy server {
    retry_delay = 5
    retry_count = 3
    default_fallback = no
    dead_time = 120
    wake_all_if_all_dead = no
 }
 home_server localhost {
    ipaddr = 127.0.0.1
    port = 1812
    type = "auth"
    secret = <<< secret >>>
    response_window = 20.000000
    response_timeouts = 1
    max_outstanding = 65536
    zombie_period = 40
    status_check = "status-server"
    ping_interval = 30
    check_interval = 30
    check_timeout = 4
    num_answers_to_alive = 3
    revive_interval = 120
  limit {
    max_connections = 16
    max_requests = 0
    lifetime = 0
    idle_timeout = 0
  }
  coa {
    irt = 2
    mrt = 16
    mrc = 5
    mrd = 30
  }
 }
 home_server_pool my_auth_failover {
    type = fail-over
    home_server = localhost
 }
 realm example.com {
    auth_pool = my_auth_failover
 }
 realm LOCAL {
 }
radiusd: #### Loading Clients ####
 client localhost {
    ipaddr = 127.0.0.1
    require_message_authenticator = no
    secret = <<< secret >>>
    nas_type = "other"
    proto = "*"
  limit {
    max_connections = 16
    lifetime = 0
    idle_timeout = 30
  }
 }
 client localhost_ipv6 {
    ipv6addr = ::1
    require_message_authenticator = no
    secret = <<< secret >>>
  limit {
    max_connections = 16
    lifetime = 0
    idle_timeout = 30
  }
 }
 client 192.168.32.0/23 {
    ipaddr = 192.168.32.0
    netmask = 23
    require_message_authenticator = no
    secret = <<< secret >>>
  limit {
    max_connections = 16
    lifetime = 0
    idle_timeout = 30
  }
 }
 client 192.168.35.0/24 {
    ipaddr = 192.168.35.0
    netmask = 24
    require_message_authenticator = no
    secret = <<< secret >>>
  limit {
    max_connections = 16
    lifetime = 0
    idle_timeout = 30
  }
 }
Debugger not attached
 # Creating Auth-Type = mschap
 # Creating Auth-Type = eap
 # Creating Auth-Type = PAP
 # Creating Auth-Type = CHAP
 # Creating Auth-Type = MS-CHAP
 # Creating Auth-Type = digest
radiusd: #### Instantiating modules ####
 modules {
  # Loaded module rlm_radutmp
  # Loading module "radutmp" from file /etc/freeradius/3.0/mods-enabled/radutmp
  radutmp {
    filename = "/var/log/freeradius/radutmp"
    username = "%{User-Name}"
    case_sensitive = yes
    check_with_nas = yes
    permissions = 384
    caller_id = yes
  }
  # Loaded module rlm_linelog
  # Loading module "linelog" from file /etc/freeradius/3.0/mods-enabled/linelog
  linelog {
    filename = "/var/log/freeradius/linelog"
    escape_filenames = no
    syslog_severity = "info"
    permissions = 384
    format = "This is a log message for %{User-Name}"
    reference = "messages.%{%{reply:Packet-Type}:-default}"
  }

Can you please help me get this to work?

P.S. Can you advise on how should I send the rest of the logs? The logs is quite extensive and comments are capped at few hundred characters.



Related Questions


Updated June 09, 2015 06:00 AM

Updated November 06, 2017 17:00 PM

Updated August 17, 2018 20:00 PM

Updated July 29, 2018 13:00 PM