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