Witam!!
Przekopiowałem plik jak w temacie jako plik sql.conf w katalogu konfiguracyjnym radiusa, zmieniłem nazwę sql sql_pppoe na samo sql i po odpaleniu radius -X i próbie logowania po PPPOE do MT dostaje to:
---------------------------------------- radius -X including.... (...) Module: Linked to module rlm_sql Module: Instantiating sql sql { driver = "rlm_sql_mysql" server = "localhost" port = "" login = "lms" password = "..." radius_db = "lms" read_groups = yes sqltrace = no sqltracefile = "/usr/local/radius/var/log/radius/sqltrace.sql" readclients = yes deletestalesessions = yes num_sql_socks = 15 lifetime = 0 max_queries = 0 sql_user_name = "%{User-Name}" default_user_profile = "" nas_query = "SELECT nodes.id, inet_ntoa(ipaddr) as nasname, shortname, (SELECT nastypes.name FROM nastypes WHERE nastypes.id=netdevices.nastype) AS type, secret FROM nodes, netdevices Where nas=1 AND nodes.netdev=netdevices.id" authorize_check_query = "SELECT id, lower(name) as UserName , 'User-Password' as Attribute , passwd as Value, ':=' as op FROM nodes WHERE name = '%{User-Name}' UNION SELECT id, lower(name) as UserName , 'Simultaneous-Use' as Attribute, '1' as Value, ':=' as op FROM nodes WHERE name = '%{User-Name}' UNION SELECT 0 AS id, '%{User-Name}' AS UserName, 'Max-Octets' AS Attribute, CONCAT(ROUND(COALESCE(x.dlimit, y.dlimit))) AS Value, ':=' AS op FROM (SELECT n.id, MIN(n.name) AS name, SUM(t.dlimit/o.cnt) AS dlimit FROM nodeassignments na JOIN assignments a ON (na.assignmentid = a.id) JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (na.nodeid = n.id) JOIN (SELECT assignmentid, COUNT(*) AS cnt FROM nodeassignments GROUP BY assignmentid) o ON (o.assignmentid = na.assignmentid) WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) AND (a.dateto > unix_timestamp() OR a.dateto = 0) AND a.suspended = 0 AND n.name = '%{User-Name}' GROUP BY n.id ) x RIGHT JOIN (SELECT SUM(t.dlimit)/o.cnt AS dlimit FROM assignments a JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (a.customerid = n.ownerid) JOIN (SELECT COUNT(*) AS cnt, ownerid FROM nodes WHERE NOT EXISTS(SELECT 1 FROM nodeassignments, assignments a WHERE assignmentid = a.id AND nodeid = nodes.id AND a.suspended = 0 AND (a.dateto > unix_timestamp() OR a.dateto = 0)) GROUP BY ownerid) o ON (o.ownerid = n.ownerid) WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) AND (a.dateto > unix_timestamp() OR a.dateto = 0) AND a.suspended =?0 AND t.dlimit != '0' AND NOT EXISTS (SELECT 1 FROM nodeassignments WHERE assignmentid = a.id)?AND n.name = '%{User-Name}' GROUP BY n.id ) y ON (1=1);" authorize_reply_query = "SELECT id, lower(name) as UserName , 'Framed-IP-Address' as Attribute, inet_ntoa(ipaddr) as Value, '==' as op FROM nodes WHERE name = '%{User-Name}' UNION SELECT 0 AS id, '%{User-Name}' AS UserName, 'Mikrotik-Rate-Limit' AS Attribute, CONCAT(ROUND(COALESCE(x.upceil, y.upceil, z.upceil)),'k','/', ROUND(COALESCE(x.downceil, y.downceil, z.downceil)),'k') AS Value, '==' AS op FROM (SELECT n.id, MIN(n.name) AS name, SUM(t.downceil/o.cnt) AS downceil, SUM(t.upceil/o.cnt) AS upceil FROM nodeassignments na JOIN assignments a ON (na.assignmentid = a.id) JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (na.nodeid = n.id) JOIN (SELECT assignmentid, COUNT(*) AS cnt FROM nodeassignments GROUP BY assignmentid) o ON (o.assignmentid = na.assignmentid) WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) AND (a.dateto > unix_timestamp() OR a.dateto = 0) AND a.suspended = 0 AND n.name = '%{User-Name}' GROUP BY n.id ) x RIGHT JOIN (SELECT SUM(t.downceil)/o.cnt AS downceil, SUM(t.upceil)/o.cnt AS upceil FROM assignments a JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (a.customerid = n.ownerid) JOIN (SELECT COUNT(*) AS cnt, ownerid FROM nodes WHERE NOT EXISTS(SELECT 1 FROM nodeassignments, assignments a WHERE assignmentid = a.id AND nodeid = nodes.id AND a.suspended = 0 AND (a.dateto > unix_timestamp() OR a.dateto = 0)) GROUP BY ownerid) o ON (o.ownerid = n.ownerid) WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) AND (a.dateto > unix_timestamp() OR a.dateto = 0) AND a.suspended =?0 AND NOT EXISTS (SELECT 1 FROM nodeassignments WHERE assignmentid = a.id)?AND n.name = '%{User-Name}' GROUP BY n.id ) y ON (1=1) RIGHT JOIN (SELECT n.id, n.name, 64 AS downceil, 64 AS upceil FROM nodes n WHERE n.name = '%{User-Name}' ) z ON (1=1) UNION SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id;" authorize_group_check_query = "" authorize_group_reply_query = "" accounting_onoff_query = "" accounting_update_query = "" accounting_update_query_alt = "" accounting_start_query = "" accounting_start_query_alt = "" accounting_stop_query = "" accounting_stop_query_alt = "" connect_failure_retry_delay = 60 simul_count_query = "" simul_verify_query = "" postauth_query = "" safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" } -------------------------------------------------------------
przy próbie logowania pppoe dostaje to:
------------------------------------------------------------- rad_recv: Access-Request packet from host 192.168.0.2 port 1025, id=138, length=187 Service-Type = Framed-User Framed-Protocol = PPP NAS-Port = 120 NAS-Port-Type = Ethernet User-Name = "mobilek" Calling-Station-Id = "AA:AA:AA:AA:AA:AA" Called-Station-Id = "service1" NAS-Port-Id = "bridge1" MS-CHAP-Challenge = 0xcd737fd1d6f73f2cedf0de9db2772a8a MS-CHAP2-Response = 0x0100ca5764acebb82a306b55e08d3d8d3f4100000000000000005d08f1b178a355ea9b53271ba47a25479edf3047c3d79530 NAS-Identifier = "router" NAS-IP-Address = 192.168.0.2 +- entering group authorize {...} ++[preprocess] returns ok [auth_log] expand: /usr/local/radius/var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d -> /usr/local/radius/var/log/radius/radacct/192.168.0.2/auth-detail-20090731 [auth_log] /usr/local/radius/var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d expands to /usr/local/radius/var/log/radius/radacct/192.168.0.2/auth-detail-20090731 [auth_log] expand: %t -> Fri Jul 31 18:11:58 2009 ++[auth_log] returns ok [mschap] Found MS-CHAP attributes. Setting 'Auth-Type = mschap' ++[mschap] returns ok [sql] expand: %{User-Name} -> mobilek [sql] sql_set_user escaped user --> 'mobilek' rlm_sql (sql): Reserving sql socket id: 13 [sql] expand: SELECT id, lower(name) as UserName , 'User-Password' as Attribute , passwd as Value, ':=' as op FROM nodes WHERE name = '%{User-Name}' UNION SELECT id, lower(name) as UserName , 'Simultaneous-Use' as Attribute, '1' as Value, ':=' as op FROM nodes WHERE name = '%{User-Name}' UNION SELECT 0 AS id, '%{User-Name}' AS UserName, 'Max-Octets' AS Attribute, CONCAT(ROUND(COALESCE(x.dlimit, y.dlimit))) AS Value, ':=' AS op FROM (SELECT n.id, MIN(n.name) AS name, SUM(t.dlimit/o.cnt) AS dlimit FROM nodeassignments na JOIN assignments a ON (na.assignmentid = a.id) JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (na.nodeid = n.id) JOIN (SELECT assignmentid, COUNT(*) AS cnt FROM nodeassignments GROUP BY assignmentid) o ON (o.assignmentid = na.assignmentid) WHERE (a.datefrom <= un rlm_sql_mysql: MYSQL check_error: 1300 received rlm_sql_getvpdata: database query error [sql] SQL query error; rejecting user rlm_sql (sql): Released sql socket id: 13 ++[sql] returns fail Using Post-Auth-Type Reject +- entering group REJECT {...} [attr_filter.access_reject] expand: %{User-Name} -> mobilek attr_filter: Matched entry DEFAULT at line 11 ++[attr_filter.access_reject] returns updated Delaying reject of request 0 for 1 seconds Going to the next request Waking up in 0.9 seconds. rad_recv: Access-Request packet from host 192.168.0.2 port 1025, id=138, length=187 Waiting to send Access-Reject to client 192.168.0.2 port 1025 - ID: 138 Waking up in 0.6 seconds. rad_recv: Access-Request packet from host 192.168.0.2 port 1025, id=138, length=187 Waiting to send Access-Reject to client 192.168.0.2 port 1025 - ID: 138 Waking up in 0.3 seconds. Sending delayed reject for request 0 Sending Access-Reject of id 138 to 192.168.0.2 port 1025 Waking up in 4.9 seconds. Cleaning up request 0 ID 138 with timestamp +400
jednak kiedy w pliku z zapytaniami(sql.conf/sql_radius.conf) w sekcji skrócę i zostawie zapytania takie: sql { ... authorize_check_query = "SELECT id, lower(name) as UserName, 'User-Password' as Attribute , passwd as Value, ':=' as op FROM nodes WHERE name = '%{User-Name}';" authorize_reply_query = "SELECT id, lower(name) as UserName , 'Framed-IP-Address' as Attribute, inet_ntoa(ipaddr) as Value, '==' as op FROM nodes WHERE name = '%{User-Name}';" ... }
to user normalnie sie loguje dostaje IP i wszystko cyka wie ktoś może w którym miejscu jest nie tak w zapytaniach czy moze cos w konfigu coś trzeba poprawić?
POzdrawiam Marcin S.
!DSPAM:4a7327e4250044073210324!