[lms] radius_sql.conf z lms/sample
Marcin S.
redark w tlen.pl
Pią, 31 Lip 2009, 19:20:30 CEST
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!
Więcej informacji o liście lms