Mysql, Radreply attributes are not being merged.

Alan Buxey alan.buxey at gmail.com
Thu Apr 27 20:39:04 CEST 2017


had a look - side by side diffs look pretty similar (barring a few SQL
socket calls and the radreply (!) - so, do you have something else
lurking in the config (e.g. in your sql dialup.conf/radgroup/radreply
queries....or in that check_lease code ?

alan

On 27 April 2017 at 18:56, Waqas Toor <waqasnasirtoor at gmail.com> wrote:
> Any help? I am using the latest version.
>
>
> ---------- Forwarded message ----------
> From: "Waqas Toor" <waqasnasirtoor at gmail.com>
> Date: Apr 27, 2017 11:12 AM
> Subject: Mysql, Radreply attributes are not being merged.
> To: "FreeRadius users mailing list" <freeradius-users at lists.freeradius.org>
> Cc:
>
> 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
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


More information about the Freeradius-Users mailing list