Run time variable in sql
rob-ml at mailbox.org
rob-ml at mailbox.org
Mon Mar 26 10:32:06 CEST 2018
Hey list,
after problems in an older version I changed to the newest one (3.0.16).
Now the attributes are processed but not delivered correctly.
I want to process %{Actual-Data-Rate-Downstream} and set it as attribute Huawei-Output-Average-Rate.
+-----------+----------------------------+----+------------------------------------------+
| groupname | attribute | op | value |
+-----------+----------------------------+----+------------------------------------------+
| 50m | Huawei-Input-Average-Rate | = | `%{request:Actual-Data-Rate-Upstream}00` |
| 50m | Huawei-Output-Average-Rate | = | `%{request:Actual-Data-Rate-Downstream}00` |
+-----------+----------------------------+----+------------------------------------------+
Debug looks OK:
Expand of attribute:
(96) sql: EXPAND `%{request:Actual-Data-Rate-Upstream}00`
(96) sql: --> `4199900`
But it is allready 0:
Result to the client/bras:
(96) Huawei-Input-Average-Rate = 0
it sure is just a simpler one, but I can not find it, like a quote error or anything
Best wishes
Rob
Sample Debug:
(96) Received Access-Request Id 22 from <brasip>:1812 to radiusip:1812 length 362
(96) User-Name = "user123"
(96) User-Password = "userpasschap"
(96) NAS-Port = 3166215
(96) NAS-IP-Address = <brasip>
(96) Service-Type = Framed-User
(96) Framed-Protocol = PPP
(96) Calling-Station-Id = "<cpemac>"
(96) NAS-Identifier = "bras3"
(96) NAS-Port-Type = Ethernet
(96) NAS-Port-Id = "10.126.41.171/0.0.0.0 eth 2/7"
(96) Acct-Session-Id = "bras30030342352307b37b9d003a40"
(96) Connect-Info = "1000000000"
(96) Huawei-Startup-Stamp = 1516351868
(96) Huawei-IPHost-Addr = "255.255.255.255 <cpemac>"
(96) Huawei-Connect-ID = 14912
(96) Huawei-Version = "Huawei NE20E"
(96) Huawei-Product-ID = "NE20E"
(96) Huawei-Domain-Name = "example.com"
(96) Huawei-User-Mac = "<cpemac>"
(96) ADSL-Agent-Circuit-Id = 0x31302e3132362e34safasghasdasfasd2e302e302065746820322f37
(96) ADSL-Agent-Remote-Id = 0x4445552asdasfasdasd441472e414d525436
(96) Actual-Data-Rate-Upstream = 41999
(96) Actual-Data-Rate-Downstream = 109066
(96) Access-Loop-Encapsulation = 0x010000
(96) # Executing section authorize from file /usr/local/freeradius3/etc/raddb/sites-enabled/default
(96) authorize {
(96) policy filter_username {
(96) if (&User-Name) {
(96) if (&User-Name) -> TRUE
(96) if (&User-Name) {
(96) if (&User-Name =~ / /) {
(96) if (&User-Name =~ / /) -> FALSE
(96) if (&User-Name =~ /@[^@]*@/ ) {
(96) if (&User-Name =~ /@[^@]*@/ ) -> FALSE
(96) if (&User-Name =~ /\.\./ ) {
(96) if (&User-Name =~ /\.\./ ) -> FALSE
(96) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) {
(96) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE
(96) if (&User-Name =~ /\.$/) {
(96) if (&User-Name =~ /\.$/) -> FALSE
(96) if (&User-Name =~ /@\./) {
(96) if (&User-Name =~ /@\./) -> FALSE
(96) } # if (&User-Name) = notfound
(96) } # policy filter_username = notfound
(96) [preprocess] = ok
(96) [chap] = noop
(96) suffix: Checking for suffix after "@"
(96) suffix: Looking up realm "example.com" for User-Name = "user123"
(96) suffix: No such realm "example.com"
(96) [suffix] = noop
(96) sql: EXPAND %{User-Name}
(96) sql: --> user123
(96) sql: SQL-User-Name set to 'user123'
rlm_sql (sql): Reserved connection (113)
(96) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
(96) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'user123' ORDER BY id
(96) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'user123' ORDER BY id
(96) sql: User found in radcheck table
(96) sql: Conditional check items matched, merging assignment check items
(96) sql: Cleartext-Password := "userpasschap"
(96) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id
(96) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'user123' ORDER BY id
(96) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'user123' ORDER BY id
(96) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority
(96) sql: --> SELECT groupname FROM radusergroup WHERE username = 'user123' ORDER BY priority
(96) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'user123' ORDER BY priority
(96) sql: User found in the group table
(96) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id
(96) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'dyn' ORDER BY id
(96) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'dyn' ORDER BY id
(96) sql: Group "dyn": Conditional check items matched
(96) sql: Group "dyn": Merging assignment check items
(96) sql: Pool-Name := "DIALPOOL-DYN-1"
(96) sql: EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id
(96) sql: --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'dyn' ORDER BY id
(96) sql: Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'dyn' ORDER BY id
(96) sql: Group "dyn": Merging reply items
(96) sql: MS-Primary-DNS-Server = <primarydns>
(96) sql: MS-Secondary-DNS-Server = <secondarydns>
(96) sql: Session-Timeout := 86400
(96) sql: Fall-Through = Yes
(96) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id
(96) sql: --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '50m' ORDER BY id
(96) sql: Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '50m' ORDER BY id
(96) sql: Group "50m": Conditional check items matched
(96) sql: Group "50m": Merging assignment check items
(96) sql: EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id
(96) sql: --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '50m' ORDER BY id
(96) sql: Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '50m' ORDER BY id
(96) sql: Group "50m": Merging reply items
(96) sql: Huawei-Input-Average-Rate = "`%{request:Actual-Data-Rate-Upstream}00`"
(96) sql: EXPAND `%{request:Actual-Data-Rate-Upstream}00`
(96) sql: --> `4199900`
rlm_sql (sql): Released connection (113)
Need 1 more connections to reach min connections (3)
rlm_sql (sql): Opening additional connection (114), 1 of 30 pending slots used
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 10.1.26-MariaDB-0+deb9u1, protocol version 10
(96) [sql] = ok
(96) [expiration] = noop
(96) [logintime] = noop
(96) [pap] = updated
(96) } # authorize = updated
(96) Found Auth-Type = PAP
(96) # Executing group from file /usr/local/freeradius3/etc/raddb/sites-enabled/default
(96) Auth-Type PAP {
(96) pap: Login attempt with password
(96) pap: Comparing with "known good" Cleartext-Password
(96) pap: User authenticated successfully
(96) [pap] = ok
(96) } # Auth-Type PAP = ok
(96) # Executing section post-auth from file /usr/local/freeradius3/etc/raddb/sites-enabled/default
(96) post-auth {
(96) update {
(96) No attributes updated
(96) } # update = noop
rlm_sql (sql): Reserved connection (112)
(96) sql: EXPAND .query
(96) sql: --> .query
(96) sql: Using query template 'query'
rlm_sql (sql): Reserved connection (113)
(96) sql: EXPAND %{User-Name}
(96) sql: --> user123
(96) sql: SQL-User-Name set to 'user123'
(96) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S')
(96) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'user123', 'userpass', 'Access-Accept', '2018-03-23 15:25:35')
(96) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'user123', 'userpass', 'Access-Accept', '2018-03-23 15:25:35')
(96) sql: SQL query returned: success
(96) sql: 1 record(s) updated
rlm_sql (sql): Released connection (113)
(96) [sql] = ok
(96) [exec] = noop
(96) policy remove_reply_message_if_eap {
(96) if (&reply:EAP-Message && &reply:Reply-Message) {
(96) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE
(96) else {
(96) [noop] = noop
(96) } # else = noop
(96) } # policy remove_reply_message_if_eap = noop
(96) } # post-auth = ok
(96) Sent Access-Accept Id 22 from <radiusip>:1812 to <brasip>:1812 length 0
(96) MS-Primary-DNS-Server = <primarydns>
(96) MS-Secondary-DNS-Server = <secondarydns>
(96) Session-Timeout = 86400
(96) Huawei-Input-Average-Rate = 0
(96) Framed-IP-Address = <usercpeip>
(96) Finished request
Waking up in 4.9 seconds.
More information about the Freeradius-Users
mailing list