Simultaneous-Use and mysql
I am using a MySQL backend and want to prevent more than one logon from a user at a time (Simultaneous-Use := 1). Because I've read enough of the Internet to realize Alan D gets mad at too much or too little detail, I'll try to shoot somewhere in the middle. If you need more info please let me know. If you need less info I'll just apologize now. I've done testing (see -X output below), and am able to authenticate a second test modem, even though there is clearly a NULL acctstoptime for the user. What am I missing? System config: End user: ADSL modem, PPPoE encapsulation NAS: Cisco ASR (1002) FreeRADIUS: Version 3.0.16 (Ubuntu 18.04) Database: MySQL, initialized using included schema (mods-config/sql/main/mysql/) SQL Table radgroupcheck: groupname attribute op value DSL Port-Limit := 1 DSL Simultaneous-Use := 1 (All users are assigned to the 'DSL' group/profile) SQL Table radgroupreply: groupname attribute op value DSL Service-Type := Framed-User DSL Port-Limit := 1 DSL Framed-Protocol := PPP queries.conf: simul_count_query = "\ SELECT COUNT(*) \ FROM ${acct_table1} \ WHERE username = '%{SQL-User-Name}' \ AND acctstoptime IS NULL" simul_verify_query = "\ SELECT \ radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \ callingstationid, framedprotocol \ FROM ${acct_table1} \ WHERE username = '%{SQL-User-Name}' \ AND acctstoptime IS NULL" sites-enabled/default: authorize { filter_username sql preprocess chap mschap digest suffix eap { ok = return } sql expiration logintime pap } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } mschap digest eap } preacct { preprocess acct_unique suffix } accounting { detail unix sql if (noop) { ok } exec attr_filter.accounting_response } sites-enabled/inner-tunnel: authorize { filter_username chap mschap suffix update control { &Proxy-To-Realm := LOCAL } eap { ok = return } files sql expiration logintime pap } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } mschap eap } session { sql } Pertinent (I think) portions of freeradius -X. This is where the 2nd instance of 'siptest' is allowed online: Ready to process requests (1) Received Access-Request Id 240 from <Cisco IP>:1645 to <FreeRADIUS IP>:1812 length 130 (1) Framed-Protocol = PPP (1) User-Name = "siptest" (1) CHAP-Password = 0xaklhjasdfw23iuaselkfhaslu4hfli838we322 (1) NAS-Port-Type = PPPoEoVLAN (1) NAS-Port = 51269975 (1) NAS-Port-Id = "0/0/3/855" (1) Cisco-AVPair = "client-mac-address=e418.6bac.216d" (1) Service-Type = Framed-User (1) NAS-IP-Address = <Cisco IP> (1) # Executing section authorize from file /etc/freeradius/3.0/sites-enabled/default (1) authorize { (1) policy filter_username { (1) if (&User-Name) { (1) if (&User-Name) -> TRUE (1) if (&User-Name) { (1) if (&User-Name =~ / /) { (1) if (&User-Name =~ / /) -> FALSE (1) if (&User-Name =~ /@[^@]*@/ ) { (1) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (1) if (&User-Name =~ /\.\./ ) { (1) if (&User-Name =~ /\.\./ ) -> FALSE (1) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (1) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (1) if (&User-Name =~ /\.$/) { (1) if (&User-Name =~ /\.$/) -> FALSE (1) if (&User-Name =~ /@\./) { (1) if (&User-Name =~ /@\./) -> FALSE (1) } # if (&User-Name) = notfound (1) } # policy filter_username = notfound (1) [preprocess] = ok (1) chap: &control:Auth-Type := CHAP (1) [chap] = ok (1) [mschap] = noop (1) [digest] = noop (1) suffix: Checking for suffix after "@" (1) suffix: No '@' in User-Name = "siptest", looking up realm NULL (1) suffix: No such realm "NULL" (1) [suffix] = noop (1) eap: No EAP-Message, not doing EAP (1) [eap] = noop (1) sql: EXPAND %{User-Name} (1) sql: --> siptest (1) sql: SQL-User-Name set to 'siptest' rlm_sql (sql): Reserved connection (2) (1) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'siptest' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'siptest' ORDER BY id (1) sql: User found in radcheck table (1) sql: Conditional check items matched, merging assignment check items (1) sql: Cleartext-Password := "testpass" (1) sql: Port-Limit := 1 (1) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'siptest' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'siptest' ORDER BY id (1) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (1) sql: --> SELECT groupname FROM radusergroup WHERE username = 'siptest' ORDER BY priority (1) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'siptest' ORDER BY priority (1) sql: User found in the group table (1) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id (1) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'DSL' ORDER BY id (1) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'DSL' ORDER BY id (1) sql: Group "DSL": Conditional check items matched (1) sql: Group "DSL": Merging assignment check items (1) sql: Port-Limit := 1 (1) sql: Simultaneous-Use := 1 (1) sql: EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id (1) sql: --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'DSL' ORDER BY id (1) sql: Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'DSL' ORDER BY id (1) sql: Group "DSL": Merging reply items (1) sql: Service-Type := Framed-User (1) sql: Port-Limit := 1 (1) sql: Framed-Protocol := PPP rlm_sql (sql): Released connection (2) Need 3 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (7), 1 of 25 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on <MySQL IP> via TCP/IP, server version 5.7.26-29-57, protocol version 10 (1) [sql] = ok (1) [expiration] = noop (1) [logintime] = noop (1) pap: WARNING: Auth-Type already set. Not setting to PAP (1) [pap] = noop (1) } # authorize = ok (1) Found Auth-Type = CHAP (1) # Executing group from file /etc/freeradius/3.0/sites-enabled/default (1) Auth-Type CHAP { (1) chap: Comparing with "known good" Cleartext-Password (1) chap: CHAP user "siptest" authenticated successfully (1) [chap] = ok (1) } # Auth-Type CHAP = ok (1) # Executing section session from file /etc/freeradius/3.0/sites-enabled/default (1) session { (1) sql: EXPAND %{User-Name} (1) sql: --> siptest (1) sql: SQL-User-Name set to 'siptest' (1) sql: EXPAND SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL (1) sql: --> SELECT COUNT(*) FROM radacct WHERE username = 'siptest' AND acctstoptime IS NULL rlm_sql (sql): Reserved connection (3) (1) sql: Executing select query: SELECT COUNT(*) FROM radacct WHERE username = 'siptest' AND acctstoptime IS NULL (1) sql: EXPAND SELECT radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, callingstationid, framedprotocol FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL (1) sql: --> SELECT radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, callingstationid, framedprotocol FROM radacct WHERE username = 'siptest' AND acctstoptime IS NULL (1) sql: Executing select query: SELECT radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, callingstationid, framedprotocol FROM radacct WHERE username = 'siptest' AND acctstoptime IS NULL checkrad: Neither SNMP_Session module or /usr/bin/snmpget found! checkrad: /usr/bin/snmpwalk not found! (1) sql: Running Accounting section for automatically created accounting 'stop' (1) sql: Framed-Protocol = PPP (1) sql: User-Name = "siptest" (1) sql: CHAP-Password = 0xaklhjasdfw23iuaselkfhaslu4hfli838we322 (1) sql: NAS-Port-Type = PPPoEoVLAN (1) sql: NAS-Port = 51269975 (1) sql: NAS-Port-Id = "0/0/3/855" (1) sql: Cisco-AVPair = "client-mac-address=e418.6bac.216d" (1) sql: Service-Type = Framed-User (1) sql: NAS-IP-Address = <Cisco IP> (1) sql: Event-Timestamp = "Aug 2 2019 13:14:02 CDT" (1) sql: CHAP-Challenge = 0x0acd0360260336e9ba1208eef4997e31 (1) sql: SQL-User-Name := "siptest" (1) # Executing section preacct from file /etc/freeradius/3.0/sites-enabled/default
On Aug 2, 2019, at 5:42 PM, Ben McTee <eastex.benmctee@gmail.com> wrote:
I am using a MySQL backend and want to prevent more than one logon from a user at a time (Simultaneous-Use := 1). Because I've read enough of the Internet to realize Alan D gets mad at too much or too little detail,
I get cranky when people don't read the documentation. After spending much time writing it, a good percentage of people go "nah, we're not going to to read that." Despite that, I still try to help people. And for some reason, many people get mad when I point out that their questions are answered in the documentation, and that should probably have read it.
I've done testing (see -X output below), and am able to authenticate a second test modem, even though there is clearly a NULL acctstoptime for the user. What am I missing?
System config: End user: ADSL modem, PPPoE encapsulation NAS: Cisco ASR (1002) FreeRADIUS: Version 3.0.16 (Ubuntu 18.04) Database: MySQL, initialized using included schema (mods-config/sql/main/mysql/)
None of that is needed. The documentation says what we need.
SQL Table radgroupcheck: groupname attribute op value DSL Port-Limit := 1 DSL Simultaneous-Use := 1
That should work.
queries.conf:
Posting the default configuration files to the list isn't helpful. When you join the list, you get an email pointing you to a Wiki page which says what we need. That page explicitly says *don't* post the configuration files.
sites-enabled/default:
We don't need to see that, either. The documentation and at least weekly (if not daily) messages on this list say what to post.
Pertinent (I think) portions of freeradius -X. This is where the 2nd instance of 'siptest' is allowed online:
Ready to process requests ... (1) sql: Executing select query: SELECT radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, callingstationid, framedprotocol FROM radacct WHERE username = 'siptest' AND acctstoptime IS NULL
All that sets Simultaneous-Use, which is fine.
checkrad: Neither SNMP_Session module or /usr/bin/snmpget found! checkrad: /usr/bin/snmpwalk not found!
Messages like that seem relevant.
(1) sql: Running Accounting section for automatically created accounting 'stop'
And it's trying to delete the previous session. If you're going to run "checkrad", make sure that "snmpget" is installed, as the message above suggests. Otherwise, edit the client { ... } configuration, and set "nas_type = other". (Note that you *didn't* post the full debug output which would have showed what you set for "nas_type", but whatever,) Setting "nas_type = other" means the server just believes whatever is in the accounting database, and doesn't run checkrad. That will likely fix the problem. Alan DeKok.
I get cranky when people don't read the documentation. After spending much time writing it, a good percentage of people go "nah, we're not going to to read that."
I understand your frustration. I tried my best to configure it on my own for many hours, combing through the documentation and list archives. Your documentation and dedication to this community is wonderful.
Despite that, I still try to help people. And for some reason, many people get mad when I point out that their questions are answered in the documentation, and that should probably have read it.
I (and many others I'm sure) appreciate it. I actually enjoy reading your colorful responses.
queries.conf:
Posting the default configuration files to the list isn't helpful.
This was posted because I've found inconsistencies in list archives where the authorize_reply_query section was commented out. I realize there are countless configuration scenarious, but during this time I've wished for a "beginning to end freeradius, mysql, simultaneous-use blocking" type tutorial. If people go Googling, they run across these threads, which (besides the Wiki and config comments) are how we troubleshoot similar scenarios. Would it not be prudent to clarify for future readers?
If you're going to run "checkrad", make sure that "snmpget" is installed, as the message above suggests.
Ah, SNMP, did not realize it was for simultaneous use checking. I thought SNMP would have only been used to disconnect users, and I'm using packet of disconnect so never looked into it further.
Otherwise, edit the client { ... } configuration, and set "nas_type = other".
Changing the nas_type from cisco to other (in the nas table) allowed the limit to work as you explained.
Setting "nas_type = other" means the server just believes whatever is in the accounting database, and doesn't run checkrad. That will likely fix the problem.
Is checkrad more efficient/preferred or should I have a reason not to trust the accounting database? For example, if the stop packet isn't received for whatever reason, does it periodically compare accounting database to actual situation and correct if a user appears online but isn't? I can see this situation causing a user to not be allowed online if something goes wrong with accounting. I just don't want to worry if that's not a likely scenario. --- The logging does not honor my msg_denied setting in radiusd.conf. The detail_YYYYMMDD logs show the output below. Can I expect my logs to not include the simultaneous use message when nas_type is set to other? Mon Aug 5 07:45:09 2019 Acct-Session-Id = "0/0/3/855_000E8E37" Cisco-AVPair = "ppp-disconnect-cause=User failed CHAP authentication" User-Name = "siptest" Acct-Authentic = RADIUS Cisco-AVPair = "connect-progress=Auth Failed" Cisco-AVPair = "nas-tx-speed=1000000000" Cisco-AVPair = "nas-rx-speed=1000000000" Acct-Session-Time = 0 Acct-Input-Octets = 0 Acct-Output-Octets = 0 Acct-Input-Packets = 0 Acct-Output-Packets = 0 Acct-Terminate-Cause = User-Error Cisco-AVPair = "disc-cause-ext=PPP CHAP Fail" Acct-Status-Type = Stop NAS-Port-Type = PPPoEoVLAN NAS-Port = 51285559 NAS-Port-Id = "0/0/3/855" Cisco-AVPair = "client-mac-address=e418.6bac.216d" Service-Type = Framed-User NAS-IP-Address = <Cisco ASR IP> Acct-Delay-Time = 0 Event-Timestamp = "Aug 5 2019 07:45:09 CDT" Tmp-String-9 = "ai:" Acct-Unique-Session-Id = "15d7264ee300eaa11e96e1b218f44ed9" Timestamp = 1565009109 Ben M.
On Aug 5, 2019, at 10:01 AM, Ben McTee <eastex.benmctee@gmail.com> wrote:
I understand your frustration. I tried my best to configure it on my own for many hours, combing through the documentation and list archives. Your documentation and dedication to this community is wonderful.
Thanks.
I (and many others I'm sure) appreciate it. I actually enjoy reading your colorful responses.
I'm glad someone enjoys them.
This was posted because I've found inconsistencies in list archives where the authorize_reply_query section was commented out. I realize there are countless configuration scenarious, but during this time I've wished for a "beginning to end freeradius, mysql, simultaneous-use blocking" type tutorial. If people go Googling, they run across these threads, which (besides the Wiki and config comments) are how we troubleshoot similar scenarios. Would it not be prudent to clarify for future readers?
It would be prudent for people to contribute documentation. It *is* an open source project. The Wiki *is* editable. The reality is that the basic documentation for the server isn't bad. The configuration is documented, simple scenarios are documented. There's a lot of stuff in the Wiki. But... Doing anything unusual or complex requires understanding just about everything. Because RADIUS is complex. So it's easy for experts to write the docs, but they're busy doing other things. And then the people who *need* the docs generally don't contribute. Which means that they don't get better. What I'm doing for v4 is just biting the bullet and *paying* people to write documentation.
Changing the nas_type from cisco to other (in the nas table) allowed the limit to work as you explained.
Good.
Is checkrad more efficient/preferred or should I have a reason not to trust the accounting database? For example, if the stop packet isn't received for whatever reason, does it periodically compare accounting database to actual situation and correct if a user appears online but isn't?
The checkrad script compares the accounting database to what the NAS things. But *only* when a second login attempt is made. The reality is that checkrad is used a lot less than 10 years ago. Most NAS vendors have fixed their equipment to actually send packets. (!). Which means that the accounting database is almost always correct.
I can see this situation causing a user to not be allowed online if something goes wrong with accounting. I just don't want to worry if that's not a likely scenario.
Yes. That's the tradeoff. What happens most of the time is that the user logs in with the same NAS IP / port / whatever. So the server can tell this new session *exactly matches* an old open session. Which means that the old session can be safely closed, and a new session opened.
The logging does not honor my msg_denied setting in radiusd.conf. The detail_YYYYMMDD logs show the output below.
Those are accounting logs. msg_denied applies to authentication packets.
Can I expect my logs to not include the simultaneous use message when nas_type is set to other?
They should. Alan DeKok.
participants (2)
-
Alan DeKok -
Ben McTee