Mysql, Radreply attributes are not being merged.

Waqas Toor waqasnasirtoor at gmail.com
Thu Apr 27 10:12:48 CEST 2017


Hello,
I have added attributes in radreply table against the user, but they are
not being merged in the Access-Accept Packet.
Same i do with radtest utility they are being added. I am unable to figure
out what is missing.

actual Access-Request from NAS
==========================================================
(0) Received Access-Request Id 92 from 10.0.0.11:1645 to 10.12.32.61:1812
length 179
(0)   User-Name = "10.48.136.46"
(0)   Framed-IP-Address = 10.48.136.46
(0)   Cisco-Account-Info = "S10.48.136.46"
(0)   User-Password = "cisco"
(0)   Calling-Station-Id = "0/0/0/103:00-00-00-00-00-00"
(0)   NAS-Port-Type = Virtual
(0)   NAS-Port = 0
(0)   NAS-Port-Id = "0/0/0/103"
(0)   Service-Type = Outbound-User
(0)   NAS-IP-Address = 10.0.0.11
(0)   NAS-Identifier = "IDC0100ISG02.nms.itcc.com.sa"
(0)   Event-Timestamp = "Apr 27 2017 02:05:39 EDT"
(0) # Executing section authorize from file
/usr/local/etc/raddb/sites-enabled/default
(0)   authorize {
(0)     policy filter_username {
(0)       if (&User-Name) {
(0)       if (&User-Name)  -> TRUE
(0)       if (&User-Name)  {
(0)         if (&User-Name =~ / /) {
(0)         if (&User-Name =~ / /)  -> FALSE
(0)         if (&User-Name =~ /@[^@]*@/ ) {
(0)         if (&User-Name =~ /@[^@]*@/ )  -> FALSE
(0)         if (&User-Name =~ /\.\./ ) {
(0)         if (&User-Name =~ /\.\./ )  -> FALSE
(0)         if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))  {
(0)         if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))   ->
FALSE
(0)         if (&User-Name =~ /\.$/)  {
(0)         if (&User-Name =~ /\.$/)   -> FALSE
(0)         if (&User-Name =~ /@\./)  {
(0)         if (&User-Name =~ /@\./)   -> FALSE
(0)       } # if (&User-Name)  = notfound
(0)     } # policy filter_username = notfound
(0)     [preprocess] = ok
(0)     [chap] = noop
(0)     [mschap] = noop
(0)     [digest] = noop
(0) lease_check: Executing: /usr/local/bin/leasequery %{User-Name}:
(0) lease_check: EXPAND %{User-Name}
(0) lease_check:    --> 10.48.136.46
(0) lease_check: Program returned code (0) and output 'User-Name :=
1/0A:D824BD511480'
(0) lease_check: Program executed successfully
(0)     [lease_check] = ok
(0) sql: EXPAND %{User-Name}
(0) sql:    --> 1/0A:D824BD511480
(0) sql: SQL-User-Name set to '1/0A:D824BD511480'
rlm_sql (sql): Closing connection (0): Hit idle_timeout, was idle for 264
seconds
rlm_sql_mysql: Socket destructor called, closing socket
rlm_sql (sql): Closing connection (1): Hit idle_timeout, was idle for 264
seconds
rlm_sql_mysql: Socket destructor called, closing socket
rlm_sql (sql): Closing connection (2): Hit idle_timeout, was idle for 264
seconds
rlm_sql (sql): You probably need to lower "min"
rlm_sql_mysql: Socket destructor called, closing socket
rlm_sql (sql): Closing connection (3): Hit idle_timeout, was idle for 264
seconds
rlm_sql (sql): You probably need to lower "min"
rlm_sql_mysql: Socket destructor called, closing socket
rlm_sql (sql): Closing connection (4): Hit idle_timeout, was idle for 264
seconds
rlm_sql (sql): You probably need to lower "min"
rlm_sql_mysql: Socket destructor called, closing socket
rlm_sql (sql): 0 of 0 connections in use.  You  may need to increase "spare"
rlm_sql (sql): Opening additional connection (5), 1 of 32 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 5.5.52-MariaDB, protocol version 10
rlm_sql (sql): Reserved connection (5)
(0) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck
WHERE username = '%{SQL-User-Name}' ORDER BY id
(0) sql:    --> SELECT id, username, attribute, value, op FROM radcheck
WHERE username = '1/0A:D824BD511480' ORDER BY id
(0) sql: Executing select query: SELECT id, username, attribute, value, op
FROM radcheck WHERE username = '1/0A:D824BD511480' ORDER BY id
(0) sql: User found in radcheck table
(0) sql: Conditional check items matched, merging assignment check items
(0) sql:   Cleartext-Password := "cisco"
(0) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply
WHERE username = '%{SQL-User-Name}' ORDER BY id
(0) sql:    --> SELECT id, username, attribute, value, op FROM radreply
WHERE username = '1/0A:D824BD511480' ORDER BY id
(0) sql: Executing select query: SELECT id, username, attribute, value, op
FROM radreply WHERE username = '1/0A:D824BD511480' ORDER BY id
(0) sql: EXPAND SELECT groupname FROM radusergroup WHERE username =
'%{SQL-User-Name}' ORDER BY priority
(0) sql:    --> SELECT groupname FROM radusergroup WHERE username =
'1/0A:D824BD511480' ORDER BY priority
(0) sql: Executing select query: SELECT groupname FROM radusergroup WHERE
username = '1/0A:D824BD511480' ORDER BY priority
(0) sql: User not found in any groups
rlm_sql (sql): Released connection (5)
Need 2 more connections to reach min connections (3)
rlm_sql (sql): Opening additional connection (6), 1 of 31 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 5.5.52-MariaDB, protocol version 10
(0)     [sql] = ok
(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
(0)     } # update = noop
(0) sql: EXPAND .query
(0) sql:    --> .query
(0) sql: Using query template 'query'
rlm_sql (sql): Reserved connection (5)
(0) sql: EXPAND %{User-Name}
(0) sql:    --> 1/0A:D824BD511480
(0) sql: SQL-User-Name set to '1/0A:D824BD511480'
(0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate)
VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}', '%S')
(0) sql:    --> INSERT INTO radpostauth (username, pass, reply, authdate)
VALUES ( '1/0A:D824BD511480', 'cisco', 'Access-Accept', '2017-04-27
02:05:39.425995')
(0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply,
authdate) VALUES ( '1/0A:D824BD511480', 'cisco', 'Access-Accept',
'2017-04-27 02:05:39.425995')
(0) sql: SQL query returned: success
(0) sql: 1 record(s) updated
rlm_sql (sql): Released connection (5)
(0)     [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) Sent Access-Accept Id 92 from 10.12.32.61:1812 to 10.0.0.11:1645 length
0
(0) Finished request
Waking up in 4.9 seconds.
(0) Cleaning up request packet ID 92 with timestamp +264
Ready to process requests
===================================================================



radtest snippet
==============================================================
Received Access-Request Id 120 from 127.0.0.1:39450 to 127.0.0.1:1812
length 82
(2)   User-Name = "10.48.136.46"
(2)   User-Password = "cisco"
(2)   NAS-IP-Address = 10.12.32.61
(2)   NAS-Port = 0
(2)   Message-Authenticator = 0xbd6028760e9fe9fcd41666ca91f382af
(2) # Executing section authorize from file
/usr/local/etc/raddb/sites-enabled/default
(2)   authorize {
(2)     policy filter_username {
(2)       if (&User-Name) {
(2)       if (&User-Name)  -> TRUE
(2)       if (&User-Name)  {
(2)         if (&User-Name =~ / /) {
(2)         if (&User-Name =~ / /)  -> FALSE
(2)         if (&User-Name =~ /@[^@]*@/ ) {
(2)         if (&User-Name =~ /@[^@]*@/ )  -> FALSE
(2)         if (&User-Name =~ /\.\./ ) {
(2)         if (&User-Name =~ /\.\./ )  -> FALSE
(2)         if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))  {
(2)         if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))   ->
FALSE
(2)         if (&User-Name =~ /\.$/)  {
(2)         if (&User-Name =~ /\.$/)   -> FALSE
(2)         if (&User-Name =~ /@\./)  {
(2)         if (&User-Name =~ /@\./)   -> FALSE
(2)       } # if (&User-Name)  = notfound
(2)     } # policy filter_username = notfound
(2)     [preprocess] = ok
(2)     [chap] = noop
(2)     [mschap] = noop
(2)     [digest] = noop
(2) lease_check: Executing: /usr/local/bin/leasequery %{User-Name}:
(2) lease_check: EXPAND %{User-Name}
(2) lease_check:    --> 10.48.136.46
(2) lease_check: Program returned code (0) and output 'User-Name :=
1/0A:D824BD511480'
(2) lease_check: Program executed successfully
(2)     [lease_check] = ok
(2) sql: EXPAND %{User-Name}
(2) sql:    --> 1/0A:D824BD511480
(2) sql: SQL-User-Name set to '1/0A:D824BD511480'
rlm_sql (sql): Closing connection (8): Hit idle_timeout, was idle for 95
seconds
rlm_sql (sql): You probably need to lower "min"
rlm_sql_mysql: Socket destructor called, closing socket
rlm_sql (sql): Closing connection (7): Hit idle_timeout, was idle for 95
seconds
rlm_sql (sql): You probably need to lower "min"
rlm_sql_mysql: Socket destructor called, closing socket
rlm_sql (sql): 0 of 0 connections in use.  You  may need to increase "spare"
rlm_sql (sql): Opening additional connection (9), 1 of 32 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 5.5.52-MariaDB, protocol version 10
rlm_sql (sql): Reserved connection (9)
(2) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck
WHERE username = '%{SQL-User-Name}' ORDER BY id
(2) sql:    --> SELECT id, username, attribute, value, op FROM radcheck
WHERE username = '1/0A:D824BD511480' ORDER BY id
(2) sql: Executing select query: SELECT id, username, attribute, value, op
FROM radcheck WHERE username = '1/0A:D824BD511480' ORDER BY id
(2) sql: User found in radcheck table
(2) sql: Conditional check items matched, merging assignment check items
(2) sql:   Cleartext-Password := "cisco"
(2) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply
WHERE username = '%{SQL-User-Name}' ORDER BY id
(2) sql:    --> SELECT id, username, attribute, value, op FROM radreply
WHERE username = '1/0A:D824BD511480' ORDER BY id
(2) sql: Executing select query: SELECT id, username, attribute, value, op
FROM radreply WHERE username = '1/0A:D824BD511480' ORDER BY id
(2) sql: User found in radreply table, merging reply items
(2) sql:   Idle-Timeout := 600
(2) sql:   Session-Timeout := 3600
(2) sql:   Cisco-Account-Info := "ABB_business_basic_0_100_50"
(2) sql:   Cisco-AVPair := "accounting-list=ACCNT_LIST2"
(2) sql:   User-Name := "1/0A:D824BD511480"
(2) sql: EXPAND SELECT groupname FROM radusergroup WHERE username =
'%{SQL-User-Name}' ORDER BY priority
(2) sql:    --> SELECT groupname FROM radusergroup WHERE username =
'1/0A:D824BD511480' ORDER BY priority
(2) sql: Executing select query: SELECT groupname FROM radusergroup WHERE
username = '1/0A:D824BD511480' ORDER BY priority
(2) sql: User not found in any groups
rlm_sql (sql): Released connection (9)
Need 2 more connections to reach min connections (3)
rlm_sql (sql): Opening additional connection (10), 1 of 31 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 5.5.52-MariaDB, protocol version 10
(2)     [sql] = ok
(2)     [expiration] = noop
(2)     [logintime] = noop
(2)     [pap] = updated
(2)   } # authorize = updated
(2) Found Auth-Type = PAP
(2) # Executing group from file /usr/local/etc/raddb/sites-enabled/default
(2)   Auth-Type PAP {
(2) pap: Login attempt with password
(2) pap: Comparing with "known good" Cleartext-Password
(2) pap: User authenticated successfully
(2)     [pap] = ok
(2)   } # Auth-Type PAP = ok
(2) # Executing section post-auth from file
/usr/local/etc/raddb/sites-enabled/default
(2)   post-auth {
(2)     update {
(2)       No attributes updated
(2)     } # update = noop
(2) sql: EXPAND .query
(2) sql:    --> .query
(2) sql: Using query template 'query'
rlm_sql (sql): Reserved connection (9)
(2) sql: EXPAND %{User-Name}
(2) sql:    --> 1/0A:D824BD511480
(2) sql: SQL-User-Name set to '1/0A:D824BD511480'
(2) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate)
VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}', '%S')
(2) sql:    --> INSERT INTO radpostauth (username, pass, reply, authdate)
VALUES ( '1/0A:D824BD511480', 'cisco', 'Access-Accept', '2017-04-27
03:24:06.492079')
(2) sql: Executing query: INSERT INTO radpostauth (username, pass, reply,
authdate) VALUES ( '1/0A:D824BD511480', 'cisco', 'Access-Accept',
'2017-04-27 03:24:06.492079')
(2) sql: SQL query returned: success
(2) sql: 1 record(s) updated
rlm_sql (sql): Released connection (9)
(2)     [sql] = ok
(2)     [exec] = noop
(2)     policy remove_reply_message_if_eap {
(2)       if (&reply:EAP-Message && &reply:Reply-Message) {
(2)       if (&reply:EAP-Message && &reply:Reply-Message)  -> FALSE
(2)       else {
(2)         [noop] = noop
(2)       } # else = noop
(2)     } # policy remove_reply_message_if_eap = noop
(2)   } # post-auth = ok
(2) Sent Access-Accept Id 120 from 127.0.0.1:1812 to 127.0.0.1:39450 length
0
(2)   Idle-Timeout = 600
(2)   Session-Timeout = 3600
(2)   Cisco-Account-Info = "ABB_business_basic_0_100_50"
(2)   Cisco-AVPair = "accounting-list=ACCNT_LIST2"
(2)   User-Name = "1/0A:D824BD511480"
(2) Finished request
Waking up in 4.9 seconds.
(2) Cleaning up request packet ID 120 with timestamp +4971
Ready to process request
==========================================================




Waqas Toor


More information about the Freeradius-Users mailing list