Simultaneous-Use and mysql

2 Aug 2019

      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

Simultaneous-Use and mysql

Ben McTee