W dniu 10.05.2012 23:19, lists@kaszebe.eu napisał(a):

Jest sobie freeradius + baza LMS mysql oraz koncentrator pppoe na MT ROS 5.15 Dodany wpis "$INCLUDE /usr/share/freeradius/dictionary.mikrotik" w /usr/share/freeradius/dictionary.mikrotik Plik dictionary.mikrotik ściągnięty od mikrotika.

Fragment radius-sql.conf wygląda mniej więcej tak:

authorize_check_query = \ "SELECT n.id AS id , upper(m.mac) AS UserName, 'Cleartext-Password' AS Attribute, passwd AS Value, ':=' AS op \ FROM nodes n \ JOIN macs m ON (m.nodeid = n.id) \ WHERE \ upper(m.mac) = '%{User-Name}' AND n.access=1 ORDER BY n.id"

authorize_reply_query = \ "SELECT n.id AS id, upper(m.mac) AS UserName, 'Framed-IP-Address' Attribute, inet_ntoa(n.ipaddr) AS Value, ':=' AS op \ FROM nodes n \ JOIN macs m ON (m.nodeid = n.id) \ WHERE \ upper(m.mac) = '%{User-Name}' \ UNION \ SELECT n.id id, upper(m.mac) UserName, 'Mikrotik-Rate-Limit' Attribute, '1024k/1024k' Value, "==" op \ FROM nodes n \ JOIN macs m ON (m.nodeid = n.id) \ WHERE \ upper(m.mac) = '%{User-Name}'"

Połączenie nie jest możliwe. Gdy usunę w authorize_reply_query drugie zapytanie wszystko działa poprawnie, urządzenie loguje się po pppoe (lecz nie tworzy się oczywiście kolejka w Simple Queue). Samo zapytanie zwraca wartości: +----+-------------------+---------------------+--------------+----+ | id | UserName | Attribute | Value | op | +----+-------------------+---------------------+--------------+----+ | 31 | 00:02:72:7D:00:80 | Framed-IP-Address | 10.0.0.20 | := | | 31 | 00:02:72:7D:00:80 | Mikrotik-Rate-Limit | 1024k/1024k | == | +----+-------------------+---------------------+--------------+----+

Zauważyłem że cokolwiek bym nie dodał w authorize_reply_query jako drugi atrybut zawsze w logu ten sam błąd "rlm_sql_mysql: MYSQL check_error: 1064 received".

Fragment logu:

[sql_dhcp_ip_assign] expand: %{User-Name} -> 00:02:72:7D:00:80 [sql_dhcp_ip_assign] sql_set_user escaped user --> '00:02:72:7D:00:80' rlm_sql (sql_dhcp_ip_assign): Reserving sql socket id: 9 [sql_dhcp_ip_assign] expand: SELECT n.id AS id , upper(m.mac) AS UserName, 'Cleartext-Password' AS Attribute, passwd AS Value, ':=' AS op FROM nodes n JOIN macs m ON (m.nodeid = n.id) WHERE upper(m.mac) = '%{User-Name}' AND n.access=1 ORDER BY n.id -> SELECT n.id AS id , upper(m.mac) AS UserName, 'Cleartext-Password' AS Attribute, passwd AS Value, ':=' AS op FROM nodes n JOIN macs m ON (m.nodeid = n.id) WHERE upper(m.mac) = '00:02:72:7D:00:80' AND n.access=1 ORDER BY n.id rlm_sql_mysql: query: SELECT n.id AS id , upper(m.mac) AS UserName, 'Cleartext-Password' AS Attribute, passwd AS Value, ':=' AS op FROM nodes n JOIN macs m ON (m.nodeid = n.id) WHERE upper(m.mac) = '00:02:72:7D:00:80' AND n.access=1 ORDER BY n.id [sql_dhcp_ip_assign] User found in radcheck table [sql_dhcp_ip_assign] expand: SELECT n.id AS id, upper(m.mac) AS UserName, 'Framed-IP-Address' Attribute, inet_ntoa(n.ipaddr) AS Value, ':=' AS op FROM nodes n JOIN macs m ON (m.nodeid = n.id) WHERE upper(m.mac) = '%{User-Name}' UNION SELECT n.id id, upper(m.mac) UserName, 'Mikrotik-Rate-Limit' Attribute, '1024k/1024k' Value, -> SELECT n.id AS id, upper(m.mac) AS UserName, 'Framed-IP-Address' Attribute, inet_ntoa(n.ipaddr) AS Value, ':=' AS op FROM nodes n JOIN macs m ON (m.nodeid = n.id) WHERE upper(m.mac) = '00:02:72:7D:00:80' UNION SELECT n.id id, upper(m.mac) UserName, 'Mikrotik-Rate-Limit' Attribute, '1024k/1024k' Value, rlm_sql_mysql: query: SELECT n.id AS id, upper(m.mac) AS UserName, 'Framed-IP-Address' Attribute, inet_ntoa(n.ipaddr) AS Value, ':=' AS op FROM nodes n JOIN macs m ON (m.nodeid = n.id) WHERE upper(m.mac) = '00:02:72:7D:00:80' UNION SELECT n.id id, upper(m.mac) UserName, 'Mikrotik-Rate-Limit' Attribute, '1024k/1024k' Value, rlm_sql_mysql: MYSQL check_error: 1064 received rlm_sql_getvpdata: database query error [sql_dhcp_ip_assign] SQL query error; rejecting user rlm_sql (sql_dhcp_ip_assign): Released sql socket id: 9 ++[sql_dhcp_ip_assign] returns fail Using Post-Auth-Type Reject # Executing group from file /etc/freeradius/sites-enabled/default +- entering group REJECT {...} [attr_filter.access_reject] expand: %{User-Name} -> 00:02:72:7D:00:80 attr_filter: Matched entry DEFAULT at line 11

Co robię źle ?

Spróbuj "upakować" treść zapytania. Najwyraźniej maksymalne dozwolone to 1064 znaki. Możesz się pozbyć tej masy spacji.

-- ITFan