FreeRadius MySQL

unixnerd777

I was running freeradius3 version 0.15.7_21 and wanted to install Avahi, but it's repo has evidently been removed, so I had to upgrade pfSense itself to 2.5.1 from 2.4.x - this upgraded freeradius. Now I'm stuck with nothing able to connect to wifi, I can't downgrade pfSense, I can't downgrade freeradius, and it appears like I'm supposed to either ditch freeradius and go around and re-flash a ton of embeded devices to use a different ssid or manually create config entries for every device in my database as it doesn't appear to be pulling from the DB to assign VLANs. I have freeradius running against MySQL; I can connect to MySQL just fine, no errors in any logs, but when I run a test I get Access-Reject for some entries in the DB, others return the fallback public VLAN.

My question to try to get this to work again: how can I either downgrade pfSense to what I had before so I can get a functioning version of freeradius, or what version of freeradius does 0.15.7_21 include so I can run it outside of pfSense so this doesn't happen again. Also, is there any way for me to know what breaking changes I'll get anytime I do upgrade so this won't happen again with any packages I have?

viktor_g

@unixnerd777 Could you provide more info?
Is this related to https://redmine.pfsense.org/issues/11980 ?

Try to run freeradius in debug mode:

# killall radiusd
# radiusd -X

and show the output during authentication attempt

Gertjan

@unixnerd777

I'm using

using a MySQL database server somewhere on my LAN.

Works very well.

Do what @viktor_g proposed : if there are any errors, they will get logged in 'red'.

unixnerd777

Thanks for your patience, the original post was posted when this issue got between me and sleep. I copied my database to this file and deconfigured sql to see if that'd fix it and it didn't, this is what it returns for that:
/usr/local/etc/raddb/authorized_macs:

device-mac-addr-here Cleartext-Password := "device-mac-addr-here"
	
	Tunnel-Type = VLAN,
	Tunnel-Medium-Type = IEEE-802,
	Tunnel-Private-Group-ID = "107"

radiusd -X:

(42) Received Access-Request Id 65 from ap.ip.here:59784 to pfsense.ip.here:1812 length 73
(42)   User-Name = "device-mac-addr-here"
(42)   User-Password = "device-mac-addr-here"
(42) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default
(42)   authorize {
(42)     [preprocess] = ok
(42)     policy pfs_rewrite_calling_station_id {
(42)       if (&Calling-Station-Id && (&Calling-Station-Id =~ /([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})/i)) {
(42)       if (&Calling-Station-Id && (&Calling-Station-Id =~ /([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})/i))  -> FALSE
(42)       else {
(42)         [noop] = noop
(42)       } # else = noop
(42)     } # policy pfs_rewrite_calling_station_id = noop
(42) authorized_macs: EXPAND %{Calling-Station-ID}
(42) authorized_macs:    -->
(42)     [authorized_macs] = noop
(42)     if (ok) {
(42)     if (ok)  -> FALSE
(42)     [chap] = noop
(42)     [mschap] = noop
(42)     [digest] = noop
(42) suffix: Checking for suffix after "@"
(42) suffix: No '@' in User-Name = "device-mac-addr-here", skipping NULL due to config.
(42)     [suffix] = noop
(42) ntdomain: Checking for prefix before "\"
(42) ntdomain: No '\' in User-Name = "device-mac-addr-here", skipping NULL due to config.
(42)     [ntdomain] = noop
(42) eap: No EAP-Message, not doing EAP
(42)     [eap] = noop
(42)     [files] = noop
(42)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept")) {
(42)     EXPAND %{%{Control:Auth-Type}:-No-Accept}
(42)        --> No-Accept
(42)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept"))  -> TRUE
(42)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept"))  {
(42)       if (true) {
(42)       if (true)  -> TRUE
(42)       if (true)  {
(42)         if (notfound || noop) {
(42)         if (notfound || noop)  -> FALSE
(42)       } # if (true)  = ok
(42)     } # if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept"))  = ok
rlm_counter: Entering module authorize code
rlm_counter: Could not find Check item value pair
(42)     [daily] = noop
rlm_counter: Entering module authorize code
rlm_counter: Could not find Check item value pair
(42)     [weekly] = noop
rlm_counter: Entering module authorize code
rlm_counter: Could not find Check item value pair
(42)     [monthly] = noop
rlm_counter: Entering module authorize code
rlm_counter: Could not find Check item value pair
(42)     [forever] = noop
(42)     if (&request:Calling-Station-Id == &control:Calling-Station-Id) {
(42)     ERROR: Failed retrieving values required to evaluate condition
(42)     [expiration] = noop
(42)     [logintime] = noop
(42) pap: WARNING: No "known good" password found for the user.  Not setting Auth-Type
(42) pap: WARNING: Authentication will fail unless a "known good" password is available
(42)     [pap] = noop
(42)   } # authorize = ok
(42) ERROR: No Auth-Type found: rejecting the user via Post-Auth-Type = Reject
(42) Failed to authenticate the user
(42) Using Post-Auth-Type Reject
(42) # Executing group from file /usr/local/etc/raddb/sites-enabled/default
(42)   Post-Auth-Type REJECT {
(42) attr_filter.access_reject: EXPAND %{User-Name}
(42) attr_filter.access_reject:    --> device-mac-addr-here
(42) attr_filter.access_reject: Matched entry DEFAULT at line 11
(42)     [attr_filter.access_reject] = updated
(42)     [eap] = noop
(42)     policy remove_reply_message_if_eap {
(42)       if (&reply:EAP-Message && &reply:Reply-Message) {
(42)       if (&reply:EAP-Message && &reply:Reply-Message)  -> FALSE
(42)       else {
(42)         [noop] = noop
(42)       } # else = noop
(42)     } # policy remove_reply_message_if_eap = noop
(42)   } # Post-Auth-Type REJECT = updated
(42) Login incorrect (Failed retrieving values required to evaluate condition): [device-mac-addr-here/device-mac-addr-here] (from client uap port 0)
(42) Delaying response for 1.000000 seconds
Waking up in 0.3 seconds.
Waking up in 0.6 seconds.
(42) Sending delayed response
(42) Sent Access-Reject Id 65 from pfsense.ip.here:1812 to ap.ip.here:59784 length 20

If I then configure it to run against sql again:
/usr/local/etc/raddb/authorized_macs:

DEFAULT Cleartext-Password := "%{User-Name}"

	Tunnel-Type = VLAN,
	Tunnel-Medium-Type = IEEE-802,
	Tunnel-Private-Group-ID = "878"

radiusd -X:

(0) Received Access-Request Id 66 from ap.ip.here:60015 to pfsense.ip.here:1812 length 73
(0)   User-Name = "device-mac-addr-here"
(0)   User-Password = "device-mac-addr-here"
(0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default
(0)   authorize {
(0)     [preprocess] = ok
(0)     policy pfs_rewrite_calling_station_id {
(0)       if (&Calling-Station-Id && (&Calling-Station-Id =~ /([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})/i)) {
(0)       if (&Calling-Station-Id && (&Calling-Station-Id =~ /([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})/i))  -> FALSE
(0)       else {
(0)         [noop] = noop
(0)       } # else = noop
(0)     } # policy pfs_rewrite_calling_station_id = noop
(0) authorized_macs: EXPAND %{Calling-Station-ID}
(0) authorized_macs:    -->
(0) authorized_macs: EXPAND %{User-Name}
(0) authorized_macs:    --> device-mac-addr-here
(0) authorized_macs: users: Matched entry DEFAULT at line 25
(0)     [authorized_macs] = ok
(0)     if (ok) {
(0)     if (ok)  -> TRUE
(0)     if (ok)  {
(0)       update control {
(0)         Auth-Type := Accept
(0)       } # update control = noop
(0)     } # if (ok)  = noop
(0)     [chap] = noop
(0)     [mschap] = noop
(0)     [digest] = noop
(0) suffix: Checking for suffix after "@"
(0) suffix: No '@' in User-Name = "device-mac-addr-here", skipping NULL due to config.
(0)     [suffix] = noop
(0) ntdomain: Checking for prefix before "\"
(0) ntdomain: No '\' in User-Name = "device-mac-addr-here", skipping NULL due to config.
(0)     [ntdomain] = noop
(0) eap: No EAP-Message, not doing EAP
(0)     [eap] = noop
(0)     [files] = noop
(0)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept")) {
(0)     EXPAND %{%{Control:Auth-Type}:-No-Accept}
(0)        --> Accept
(0)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept"))  -> FALSE
(0) dailycounter: WARNING: Couldn't find check attribute, control:Max-Daily-Session, doing nothing...
(0)     [dailycounter] = noop
(0) monthlycounter: WARNING: Couldn't find check attribute, control:Max-Monthly-Session, doing nothing...
(0)     [monthlycounter] = noop
(0) noresetcounter: WARNING: Couldn't find check attribute, control:Max-All-Session, doing nothing...
(0)     [noresetcounter] = noop
(0) expire_on_login: WARNING: Couldn't find check attribute, control:Expire-After, doing nothing...
(0)     [expire_on_login] = noop
(0)     if (&request:Calling-Station-Id == &control:Calling-Station-Id) {
(0)     ERROR: Failed retrieving values required to evaluate condition
(0)     [expiration] = noop
(0)     [logintime] = noop
(0) pap: WARNING: Auth-Type already set.  Not setting to PAP
(0)     [pap] = noop
(0)   } # authorize = ok
(0) Found Auth-Type = Accept
(0) Auth-Type = Accept, accepting the user
(0) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default
(0)   post-auth {
(0)     update {
(0)       No attributes updated for RHS &session-state:
(0)     } # update = noop
(0)     redundant sql {
(0) sql1: EXPAND .query
(0) sql1:    --> .query
(0) sql1: Using query template 'query'
rlm_sql (sql1): Reserved connection (1)
(0) sql1: EXPAND %{User-Name}
(0) sql1:    --> device-mac-addr-here
(0) sql1: SQL-User-Name set to 'device-mac-addr-here'
(0) sql1: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S.%M')
(0) sql1:    --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'device-mac-addr-here', 'device-mac-addr-here', 'Access-Accept', '2021-06-07 10:27:27.571066')
(0) sql1: EXPAND /var/log/sqltrace.sql
(0) sql1:    --> /var/log/sqltrace.sql
(0) sql1: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'device-mac-addr-here', 'device-mac-addr-here', 'Access-Accept', '2021-06-07 10:27:27.571066')
(0) sql1: SQL query returned: success
(0) sql1: 1 record(s) updated
rlm_sql (sql1): Released connection (1)
rlm_sql (sql1): Closing connection (2), from 3 unused connections
rlm_sql_mysql: Socket destructor called, closing socket
(0)       [sql1] = ok
(0)     } # redundant sql = ok
(0)     [exec] = noop
(0)     policy remove_reply_message_if_eap {
(0)       if (&reply:EAP-Message && &reply:Reply-Message) {
(0)       if (&reply:EAP-Message && &reply:Reply-Message)  -> FALSE
(0)       else {
(0)         [noop] = noop
(0)       } # else = noop
(0)     } # policy remove_reply_message_if_eap = noop
(0)   } # post-auth = ok
(0) Login OK: [device-mac-addr-here/device-mac-addr-here] (from client uap port 0)
(0) Sent Access-Accept Id 66 from pfsense.ip.here:1812 to ap.ip.here:60015 length 0
(0)   Tunnel-Type = VLAN
(0)   Tunnel-Medium-Type = IEEE-802
(0)   Tunnel-Private-Group-Id = "878"
(0) Finished request

In this instance it puts my device on the honeypot/jail VLAN (878), but it shouldn't as this device exists in the DB.
If I remove that default line in the macs file, it seems to work fine:

(0) Received Access-Request Id 68 from ap.ip.here:54899 to pfsense.ip.here:1812 length 73
(0)   User-Name = "device-mac-addr-here"
(0)   User-Password = "device-mac-addr-here"
(0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default
(0)   authorize {
(0)     [preprocess] = ok
(0)     policy pfs_rewrite_calling_station_id {
(0)       if (&Calling-Station-Id && (&Calling-Station-Id =~ /([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})/i)) {
(0)       if (&Calling-Station-Id && (&Calling-Station-Id =~ /([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})[^0-9a-f]?([0-9a-f]{2})/i))  -> FALSE
(0)       else {
(0)         [noop] = noop
(0)       } # else = noop
(0)     } # policy pfs_rewrite_calling_station_id = noop
(0) authorized_macs: EXPAND %{Calling-Station-ID}
(0) authorized_macs:    -->
(0)     [authorized_macs] = noop
(0)     if (ok) {
(0)     if (ok)  -> FALSE
(0)     [chap] = noop
(0)     [mschap] = noop
(0)     [digest] = noop
(0) suffix: Checking for suffix after "@"
(0) suffix: No '@' in User-Name = "device-mac-addr-here", skipping NULL due to config.
(0)     [suffix] = noop
(0) ntdomain: Checking for prefix before "\"
(0) ntdomain: No '\' in User-Name = "device-mac-addr-here", skipping NULL due to config.
(0)     [ntdomain] = noop
(0) eap: No EAP-Message, not doing EAP
(0)     [eap] = noop
(0)     [files] = noop
(0)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept")) {
(0)     EXPAND %{%{Control:Auth-Type}:-No-Accept}
(0)        --> No-Accept
(0)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept"))  -> TRUE
(0)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept"))  {
(0)       redundant sql {
(0) sql1: EXPAND %{User-Name}
(0) sql1:    --> device-mac-addr-here
(0) sql1: SQL-User-Name set to 'device-mac-addr-here'
rlm_sql (sql1): Reserved connection (1)
(0) sql1: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
(0) sql1:    --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'device-mac-addr-here' ORDER BY id
(0) sql1: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'device-mac-addr-here' ORDER BY id
(0) sql1: User found in radcheck table
(0) sql1: Conditional check items matched, merging assignment check items
(0) sql1:   Cleartext-Password := "device-mac-addr-here"
(0) sql1: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id
(0) sql1:    --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'device-mac-addr-here' ORDER BY id
(0) sql1: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'device-mac-addr-here' ORDER BY id
(0) sql1: User found in radreply table, merging reply items
(0) sql1:   Tunnel-Type := VLAN
(0) sql1:   Tunnel-Medium-Type := IEEE-802
(0) sql1:   Tunnel-Private-Group-Id := ""
rlm_sql (sql1): Reserved connection (2)
rlm_sql (sql1): Released connection (2)
(0) sql1: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority
(0) sql1:    --> SELECT groupname FROM radusergroup WHERE username = 'device-mac-addr-here' ORDER BY priority
(0) sql1: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'device-mac-addr-here' ORDER BY priority
(0) sql1: User not found in any groups
rlm_sql (sql1): Released connection (1)
(0)         [sql1] = ok
(0)       } # redundant sql = ok
(0)       if (notfound || noop) {
(0)       if (notfound || noop)  -> FALSE
(0)     } # if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept"))  = ok
(0) dailycounter: WARNING: Couldn't find check attribute, control:Max-Daily-Session, doing nothing...
(0)     [dailycounter] = noop
(0) monthlycounter: WARNING: Couldn't find check attribute, control:Max-Monthly-Session, doing nothing...
(0)     [monthlycounter] = noop
(0) noresetcounter: WARNING: Couldn't find check attribute, control:Max-All-Session, doing nothing...
(0)     [noresetcounter] = noop
(0) expire_on_login: WARNING: Couldn't find check attribute, control:Expire-After, doing nothing...
(0)     [expire_on_login] = noop
(0)     if (&request:Calling-Station-Id == &control:Calling-Station-Id) {
(0)     ERROR: Failed retrieving values required to evaluate condition
(0)     [expiration] = noop
(0)     [logintime] = noop
(0)     [pap] = updated
(0)   } # authorize = updated
(0) Found Auth-Type = PAP
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/default
(0)   Auth-Type PAP {
(0) pap: Login attempt with password
(0) pap: Comparing with "known good" Cleartext-Password
(0) pap: User authenticated successfully
(0)     [pap] = ok
(0)   } # Auth-Type PAP = ok
(0) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default
(0)   post-auth {
(0)     update {
(0)       No attributes updated for RHS &session-state:
(0)     } # update = noop
(0)     redundant sql {
(0) sql1: EXPAND .query
(0) sql1:    --> .query
(0) sql1: Using query template 'query'
rlm_sql (sql1): Reserved connection (3)
(0) sql1: EXPAND %{User-Name}
(0) sql1:    --> device-mac-addr-here
(0) sql1: SQL-User-Name set to 'device-mac-addr-here'
(0) sql1: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S.%M')
(0) sql1:    --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'device-mac-addr-here', 'device-mac-addr-here', 'Access-Accept', '2021-06-07 10:38:04.476495')
(0) sql1: EXPAND /var/log/sqltrace.sql
(0) sql1:    --> /var/log/sqltrace.sql
(0) sql1: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'device-mac-addr-here', 'device-mac-addr-here', 'Access-Accept', '2021-06-07 10:38:04.476495')
(0) sql1: SQL query returned: success
(0) sql1: 1 record(s) updated
rlm_sql (sql1): Released connection (3)
(0)       [sql1] = ok
(0)     } # redundant sql = ok
(0)     [exec] = noop
(0)     policy remove_reply_message_if_eap {
(0)       if (&reply:EAP-Message && &reply:Reply-Message) {
(0)       if (&reply:EAP-Message && &reply:Reply-Message)  -> FALSE
(0)       else {
(0)         [noop] = noop
(0)       } # else = noop
(0)     } # policy remove_reply_message_if_eap = noop
(0)   } # post-auth = ok
(0) Login OK: [device-mac-addr-here/device-mac-addr-here] (from client uap port 0)
(0) Sent Access-Accept Id 68 from pfsense.ip.here:1812 to ap.ip.here:54899 length 0
(0)   Tunnel-Type = VLAN
(0)   Tunnel-Medium-Type = IEEE-802
(0)   Tunnel-Private-Group-Id = ""
(0) Finished request

Currently my untagged VLAN on my APs is my core LAN as it was setup first, then when I separated the networks, I made a honeypot/jail VLAN for new devices to land in until they get added in the DB. I guess with this update I can't use "default"? Does that mean I should configure untagged to be the jail/honeypot and move my core LAN to a VLAN ID?
I'm guessing in this new version, it's deciding to read the macs file over what it gets from the DB, in the past if a device exists in both, it'd default to the DB response and I could do that default vlan fallback, now it seems to ignore the DB when it finds anything in the macs file that could match.

unixnerd777

Some background: I have 6 networks (guest, tenant1, tenant2, tenant3, iot, and me) I want on WiFi, but my APs can only do 4 and I'd rather not have a ton of SSIDs... Curranty/in the past a device joins the network, they land on a "public" network with a captive portal. They pick their network, it adds them to the DB, kicks them off WiFi, their device re-joins and is then on the right VLAN. They can then connect a device that doesn't support WPA2-Enterprise or a captive portal and "claim" the device from another device they joined (I.E. "claim" an XBox from their laptop). This allows me to have 1 SSID and devices land on their correct VLAN based on their DB record.

I'm guessing it'd probably be better to move freeradius to my cluster as I can then have it wait for the DB to start (or auto-restart until the DB is ready). Currently when the power goes out, pfSense boots before the cluster, so I have to manually start freeradius once the cluster finishes booting.

Gertjan

@unixnerd777 said in FreeRadius MySQL:

so I have to manually start freeradius....

Hummm.
Complicated setup. Automatized everything.
I would have added a UPS right from the start.

unixnerd777

@gertjan Yea, it's one of the first things on my list when I can afford anything.