Код: Выделить всё
MySQL has certain limitations that means it can hand out the same IP address to 2 different users.
# We suggest using an SQL DB with proper transaction support, such as PostgreSQL, or using MySQL with InnoDB.Почему нельзя, можно. Если решить две проблемыsolomon писал(а):поиск по форуму запрос freeradius - были прочитаны все посты пр фрирадиус - к своему сожалению узнал что нельзя настроить выдачу адресов из пула,хранимых в БД mysql, при помощи фрирадиуса, все клоняются к тому чтобы адреса выдавались NAS'ом - млин моего директора это не устроит - Хотя на штатном все прекрасно работает и статические и динамические адреса из пула БД УТМ.... Смысл всего проделанного заключался в том, чтобы постепенно пеейти на Freeraius познакомиться с ним поближе - а уже потом настрроить для подключения физ лиц OPTION 82....
Код: Выделить всё
check_query
mysql> SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='vlad' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0;
+-------------+-------+--------------------+-------+----+
| ip_group_id | uname | Cleartext-Password | upass | := |
+-------------+-------+--------------------+-------+----+
| 5 | vlad | Cleartext-Password | 123 | := |
+-------------+-------+--------------------+-------+----+
1 row in set (0.01 sec)
mysql> SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='test' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0;
+-------------+-------+--------------------+-------+----+
| ip_group_id | uname | Cleartext-Password | upass | := |
+-------------+-------+--------------------+-------+----+
| 8 | test | Cleartext-Password | test | := |
+-------------+-------+--------------------+-------+----+
reply_query
mysql> SELECT '','test','Framed-IP-Address',inet_ntoa( ip_groups.ip & 0xffffffff ) AS ip,'=' FROM UTM5.ip_groups WHERE uname ='test' AND is_deleted = '0' AND inet_ntoa( ip_groups.ip & 0xffffffff ) LIKE '95.215.%' UNION SELECT '', 'test', 'Framed-Pool', 'dynamic', '=' FROM ip_groups WHERE uname ='test' AND inet_ntoa( ip_groups.ip & 0xffffffff ) NOT LIKE '95.215.%' UNION SELECT '', 'test', 'Cisco', radius_data.value, '=' FROM radius_data WHERE radius_data.owner_id=(SELECT sl.service_id FROM ip_groups ig, iptraffic_service_links isl, service_links sl WHERE ig.uname='test' AND ig.is_deleted=0 AND ig.ip_group_id=isl.ip_group_id AND isl.is_deleted=0 AND isl.id=sl.id and sl.is_deleted=0) AND radius_data.vendor=9;
+--+------+-------------------+-----------------------------------------------------------------------------------------------------------+---+
| | test | Framed-IP-Address | ip | = |
+--+------+-------------------+-----------------------------------------------------------------------------------------------------------+---+
| | test | Framed-Pool | dynamic | = |
| | test | Cisco | lcp:interface-config#1=rate-limit input 2048000 256000 256000 conform-action transmit exceed-action drop | = |
| | test | Cisco | lcp:interface-config#1=rate-limit output 2048000 256000 256000 conform-action transmit exceed-action drop | = |
+--+------+-------------------+-----------------------------------------------------------------------------------------------------------+---+
3 rows in set (0.00 sec)
mysql> SELECT '','vlad','Framed-IP-Address',inet_ntoa( ip_groups.ip & 0xffffffff ) AS ip,'=' FROM UTM5.ip_groups WHERE uname ='vlad' AND is_deleted = '0' AND inet_ntoa( ip_groups.ip & 0xffffffff ) LIKE '95.215.%' UNION SELECT '', 'vlad', 'Framed-Pool', 'dynamic', '=' FROM ip_groups WHERE uname ='vlad' AND inet_ntoa( ip_groups.ip & 0xffffffff ) NOT LIKE '95.215.%' UNION SELECT '', 'vlad', 'Cisco', radius_data.value, '=' FROM radius_data WHERE radius_data.owner_id=(SELECT sl.service_id FROM ip_groups ig, iptraffic_service_links isl, service_links sl WHERE ig.uname='vlad' AND ig.is_deleted=0 AND ig.ip_group_id=isl.ip_group_id AND isl.is_deleted=0 AND isl.id=sl.id and sl.is_deleted=0) AND radius_data.vendor=9;
+--+------+-------------------+-----------------------------------------------------------------------------------------------------------+---+
| | vlad | Framed-IP-Address | ip | = |
+--+------+-------------------+-----------------------------------------------------------------------------------------------------------+---+
| | vlad | Framed-IP-Address | 95.215.x.y | = |
| | vlad | Cisco | lcp:interface-config#1=rate-limit input 2048000 256000 256000 conform-action transmit exceed-action drop | = |
| | vlad | Cisco | lcp:interface-config#1=rate-limit output 2048000 256000 256000 conform-action transmit exceed-action drop | = |
+--+------+-------------------+-----------------------------------------------------------------------------------------------------------+---+Код: Выделить всё
Ready to process requests.
rad_recv: Access-Request packet from host 172.16.2.40 port 1645, id=249, length=138
Framed-Protocol = PPP
User-Name = "vlad"
MS-CHAP-Challenge = 0x0334854e37fa469b4b240a0649fc1c2c
MS-CHAP2-Response = 0x01bf291e9de9fc2eefc12ba79e5d70d4518db702000000296a3a9966150f55903c2013d68325aaeeddd6660f5c271688d30e
NAS-Port-Type = Virtual
NAS-Port = 246
Service-Type = Framed-User
NAS-IP-Address = 172.16.2.40
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
[mschap] Found MS-CHAP attributes. Setting 'Auth-Type = mschap'
++[mschap] returns ok
[sql] expand: %{User-Name} -> vlad
[sql] sql_set_user escaped user --> 'vlad'
rlm_sql (sql): Reserving sql socket id: 4
[sql] expand: SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='%{SQL-User-Name}' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0 -> SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='vlad' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0
[sql] User found in radcheck table
rlm_sql (sql): Released sql socket id: 4
++[sql] returns ok
++[logintime] returns noop
Found Auth-Type = MSCHAP
+- entering group MS-CHAP {...}
[mschap] Told to do MS-CHAPv2 for vlad with NT-Password
[mschap] adding MS-CHAPv2 MPPE keys
++[mschap] returns ok
Login OK: [vlad] (from client cisco3660 port 246)
+- entering group post-auth {...}
[sqlippool] No Pool-Name defined.
[sqlippool] expand: No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> No Pool-Name defined (did cli port 246 user vlad)
No Pool-Name defined (did cli port 246 user vlad)
++[sqlippool] returns noop
Sending Access-Accept of id 249 to 172.16.2.40 port 1645
MS-CHAP2-Success = 0x01533d36334630393242363641454530394138413339303445324643424443304639364435364432433533
MS-MPPE-Recv-Key = 0xa1b318a963701a9292852f213d5f603f
MS-MPPE-Send-Key = 0x81e03a29e90e028c343477adc212a32f
MS-MPPE-Encryption-Policy = 0x00000001
MS-MPPE-Encryption-Types = 0x00000006
Finished request 0.
Going to the next request
Waking up in 4.9 seconds.
rad_recv: Access-Request packet from host 172.16.2.40 port 1645, id=250, length=87
User-Name = "pools-router_3660"
User-Password = "cisco"
Framed-Protocol = PPP
NAS-Port-Type = Virtual
NAS-Port = 246
Service-Type = Outbound-User
NAS-IP-Address = 172.16.2.40
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
[sql] expand: %{User-Name} -> pools-router_3660
[sql] sql_set_user escaped user --> 'pools-router_3660'
rlm_sql (sql): Reserving sql socket id: 3
[sql] expand: SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='%{SQL-User-Name}' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0 -> SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='pools-router_3660' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0
rlm_sql (sql): Released sql socket id: 3
[sql] User pools-router_3660 not found
++[sql] returns notfound
++[logintime] returns noop
No authenticate method (Auth-Type) configuration found for the request: Rejecting the user
Failed to authenticate the user.
Login incorrect: [pools-router_3660/cisco] (from client cisco3660 port 246)
Using Post-Auth-Type Reject
+- entering group REJECT {...}
[attr_filter.access_reject] expand: %{User-Name} -> pools-router_3660
attr_filter: Matched entry DEFAULT at line 11
++[attr_filter.access_reject] returns updated
Delaying reject of request 1 for 1 seconds
Going to the next request
Waking up in 0.9 seconds.
Sending delayed reject for request 1
Sending Access-Reject of id 250 to 172.16.2.40 port 1645
Waking up in 3.8 seconds.
Cleaning up request 0 ID 249 with timestamp +6
Waking up in 1.1 seconds.
Cleaning up request 1 ID 250 with timestamp +6
Ready to process requests.Код: Выделить всё
Listening on authentication address * port 1812
Listening on accounting address * port 1813
Listening on command file /opt/freeradius/var/run/radiusd/radiusd.sock
Listening on proxy address * port 1814
Ready to process requests.
rad_recv: Access-Request packet from host 172.16.2.40 port 1645, id=20, length=155
Framed-Protocol = PPP
User-Name = "test"
MS-CHAP-Challenge = 0x11d76e5d232ceb456b4000e49a7cd012
MS-CHAP2-Response = 0x01bfe04402c6aba7e684c09765022d8abe17b70200000029fa206445f1d2c866322499a46920d1bb123ebdc2953b58e7f47b
NAS-Port-Type = Virtual
NAS-Port = 271
NAS-Port-Id = "Uniq-Sess-ID271"
Service-Type = Framed-User
NAS-IP-Address = 172.16.2.40
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
[mschap] Found MS-CHAP attributes. Setting 'Auth-Type = mschap'
++[mschap] returns ok
[sql] expand: %{User-Name} -> test
[sql] sql_set_user escaped user --> 'test'
rlm_sql (sql): Reserving sql socket id: 4
[sql] expand: SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='%{SQL-User-Name}' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0 -> SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='test' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0
[sql] User found in radcheck table
[sql] WARNING: Unknown variable '%'': See 'doc/variables.txt'
[sql] WARNING: Unknown variable '%'': See 'doc/variables.txt'
[sql] expand: SELECT '', '%{SQL-User-Name}', 'Framed-IP-Address', inet_ntoa(4294967295 & ip) AS ip,':=' FROM UTM5.ip_groups WHERE uname ='%{SQL-User-Name}' AND is_deleted = '0' AND inet_ntoa(4294967295 & ip) LIKE '95.215.%' UNION SELECT '', '%{SQL-User-Name}', 'Framed-Pool', 'test', ':=' FROM ip_groups WHERE uname ='%{SQL-User-Name}' AND inet_ntoa(4294967295 & ip) NOT LIKE '95.215.%' UNION SELECT '', '%{SQL-User-Name}', 'Cisco-AVPair', radius_data.value, ':=' FROM radius_data WHERE radius_data.owner_id=(SELECT sl.service_id FROM ip_groups ig, iptraffic_service_links isl, service_links sl WHERE ig.uname='%{SQL-User-Name}' AND ig.is_deleted=0 AND ig.ip_group_id=isl.ip_group_id AND isl.is_deleted=0 AND isl.id=sl.id and sl.is_deleted=0) AND radius_data.vendor=9 -> SELECT '', 'test', 'Framed-IP-Address', inet_ntoa(4294967295 & ip) AS ip,':=' FROM UTM5.ip_groups WHERE uname ='test' AND is_deleted = '0' AND inet_ntoa(4294967295 & ip) L
rlm_sql (sql): Released sql socket id: 4
++[sql] returns ok
++[expiration] returns noop
++[logintime] returns noop
Found Auth-Type = MSCHAP
+- entering group MS-CHAP {...}
[mschap] Told to do MS-CHAPv2 for test with NT-Password
[mschap] adding MS-CHAPv2 MPPE keys
++[mschap] returns ok
Login OK: [test] (from client cisco-3660 port 271)
+- entering group post-auth {...}
++[exec] returns noop
Sending Access-Accept of id 20 to 172.16.2.40 port 1645
Framed-Pool := "test"
Cisco-AVPair := "lcp:interface-config#1=rate-limit input 1024000 128000 128000 conform-action transmit exceed-action drop"
MS-CHAP2-Success = 0x01533d38444533433630364543313541453946334342313134363046323442394345443941373546373043
MS-MPPE-Recv-Key = 0x2ea2e01fefb1275c6354aaea3ee61c35
MS-MPPE-Send-Key = 0x42f44e52f5efd34a5d3c3584cdb994f6
MS-MPPE-Encryption-Policy = 0x00000001
MS-MPPE-Encryption-Types = 0x00000006
Finished request 0.
Going to the next request
Waking up in 4.9 seconds.
rad_recv: Access-Request packet from host 172.16.2.40 port 1645, id=21, length=104
User-Name = "pools-router_3660"
User-Password = "cisco"
Framed-Protocol = PPP
NAS-Port-Type = Virtual
NAS-Port = 271
NAS-Port-Id = "Uniq-Sess-ID271"
Service-Type = Outbound-User
NAS-IP-Address = 172.16.2.40
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
[sql] expand: %{User-Name} -> pools-router_3660
[sql] sql_set_user escaped user --> 'pools-router_3660'
rlm_sql (sql): Reserving sql socket id: 3
[sql] expand: SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='%{SQL-User-Name}' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0 -> SELECT ig.ip_group_id, ig.uname, 'Cleartext-Password', ig.upass, ':=' FROM iptraffic_service_links il, ip_groups ig, service_links sl, accounts a WHERE ig.uname='pools-router_3660' AND ig.ip_group_id=il.ip_group_id AND sl.account_id=a.id AND a.is_deleted=0 AND a.balance>0 and a.int_status=1 and sl.id=il.id and ig.is_deleted=0 and il.is_deleted=0 and sl.is_deleted=0
rlm_sql (sql): Released sql socket id: 3
[sql] User pools-router_3660 not found
++[sql] returns notfound
++[expiration] returns noop
++[logintime] returns noop
No authenticate method (Auth-Type) configuration found for the request: Rejecting the user
Failed to authenticate the user.
Login incorrect: [pools-router_3660/cisco] (from client cisco-3660 port 271)
Using Post-Auth-Type Reject
+- entering group REJECT {...}
[attr_filter.access_reject] expand: %{User-Name} -> pools-router_3660
attr_filter: Matched entry DEFAULT at line 11
++[attr_filter.access_reject] returns updated
Delaying reject of request 1 for 1 seconds
Going to the next request
Waking up in 0.9 seconds.
Sending delayed reject for request 1
Sending Access-Reject of id 21 to 172.16.2.40 port 1645
Waking up in 3.8 seconds.
Cleaning up request 0 ID 20 with timestamp +7
Waking up in 1.1 seconds.
