FreeRadius MySQL
-
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?
-
@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
-
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'.
-
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. -
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.
-
@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. -
@gertjan Yea, it's one of the first things on my list when I can afford anything.