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 ?
--
ITFan
