Hi, I have a LAN with several switches ( mostly hp procurve and a-series ) and a freeradius server which is performing mac authentication. In case of successfully authentication, the freeradius send back to the switch the VLAN to set on the switch port and this works fine.

My problem is: I would like to propagate to the switches all VLANs because there are other things (i.e.: stp protocol configuration) that became easier to manage, but I want that on a given switch some VLANs are not available.

Unluckily, it seems that it is not possible to configure this behaviour on the switch. If the unwanted VLAN is propagated from the uplink to the switch and the radius tell that the VLAN should be that, the switch sets up the unwanted VLAN on the port.

So I performed a try on my: FreeRADIUS Version 2.2.5, for host x86_64-pc-linux-gnu (debian 8). Since it uses a local mysql database, I tried to modify:

/etc/freeradius/sql/mysql/dialup.conf

by changing authorize_check_query from:

authorize_check_query = "SELECT id, username, attribute, value, op \ FROM ${authcheck_table} \ WHERE username = '%{SQL-User-Name}' \ ORDER BY id"

to

# authorize_check_query = "SELECT radcheck.id, radcheck.username, \ # radcheck.attribute, radcheck.value, radcheck.op, \ # radusergroup.groupname,radgroupreply.value, ptrnas, nasname \ # FROM ${authcheck_table} LEFT JOIN radusergroup ON radcheck.username=radusergroup.username \ # LEFT JOIN radgroupreply on radusergroup.groupname=radgroupreply.groupname \ # LEFT JOIN nasvlan on radgroupreply.value=vlan \ # LEFT JOIN nas on nasvlan.ptrnas=nas.id \ # WHERE radcheck.username='%{SQL-User-Name}' \ # AND radgroupreply.attribute='Tunnel-Private-Group-ID' \ # AND nasname = '%{NAS-IP-Address}' ORDER BY radcheck.id"

that now is commented out because the server deads for "unterminated string", perhaps because the query is too big to fit inside authorize_check_query. In the fields I had to put the table name to fix the fieldnames ambiguities. After radcheck.op, there are some other fields and I don't know if this could cause other kind of problems. This query uses only the nasvlan additional table and works fine when given at the interactive mysql command prompt:

root@radiuslan4:~/import# more test.sql SELECT radcheck.id, radcheck.username, radcheck.attribute, radcheck.value, radcheck.op, radusergroup.groupname, radgroupreply.value, ptrnas, nasname \ FROM radcheck LEFT JOIN radusergroup ON radcheck.username=radusergroup.username \ LEFT JOIN radgroupreply on radusergroup.groupname=radgroupreply.groupname \ LEFT JOIN nasvlan on radgroupreply.value=vlan \ LEFT JOIN nas on nasvlan.ptrnas=nas.id \ WHERE radcheck.username='00d059cf752c' AND radgroupreply.attribute='Tunnel-Private-Group-ID' \ AND nasname='172.17.1.87';

root@radiuslan4:~/import# mysql -u root -p radius < ./test.sql Enter password: id username attribute value op groupname value ptrnas nasname 11021 00d059cf752c Cleartext-Password 00d059cf752c := mac-addr-008 8 71 172.17.1.87

My questions are:

Is there in freeradius an easier way to get the desired result? or, should be possible in a next release to allow a larger authorize_check_query? Just by shortening the above query:

authorize_check_query = "SELECT radcheck.id, radcheck.username, radcheck.attribute, radcheck.value, radcheck.op, \ radusergroup.groupname,radgroupreply.value, ptrnas, nasname FROM ${authcheck_table} LEFT JOIN radusergroup ON radcheck.username=radusergroup.username LEFT JOIN radgroupreply on radusergroup.groupname=radgroupreply.groupname \ LEFT JOIN nasvlan on radgroupreply.value=vlan LEFT JOIN nas on nasvlan.ptrnas=nas.id \ WHERE radcheck.username='%{SQL-User-Name}' AND radgroupreply.attribute='Tunnel-Private-Group-ID' \ AND nasname = '%{NAS-IP-Address}' ORDER BY radcheck.id" there are less backslashes breaking the string to the next line, now I got the freeradius working. However, I have a shorter query by using the table aliases that works at the mysql command prompt: root@radiuslan4:~/import# more ./test2.sql SELECT r.id, r.username, r.attribute, r.value, r.op, rg.groupname, ry.value, ptrnas, nasname \ FROM radcheck r LEFT JOIN radusergroup rg ON r.username=rg.username \ LEFT JOIN radgroupreply ry on rg.groupname=ry.groupname \ LEFT JOIN nasvlan on ry.value=vlan \ LEFT JOIN nas on nasvlan.ptrnas=nas.id \ WHERE r.username='00d059cf752c' AND ry.attribute='Tunnel-Private-Group-ID' \ AND nasname='172.17.1.87'; root@radiuslan4:~/import# mysql -u root -p radius < ./test2.sql Enter password: id username attribute value op groupname value ptrnas nasname 11021 00d059cf752c Cleartext-Password 00d059cf752c := mac-addr-011 11 71 172.17.1.87 but when it is in the file dialup.conf: # authorize_check_query = "SELECT r.id, r.username, r.attribute, r.value, r.op, rg.groupname, ry.value, ptrnas, nasname \ FROM radcheck r LEFT JOIN radusergroup rg ON r.username=rg.username \ LEFT JOIN radgroupreply ry on rg.groupname=ry.groupname \ LEFT JOIN nasvlan on ry.value=vlan \ LEFT JOIN nas on nasvlan.ptrnas=nas.id \ WHERE r.username='%{NAS-IP-Address}' AND ry.attribute='Tunnel-Private-Group-ID' \ AND nasname='%{NAS-IP-Address}' ORDER BY radcheck.id " # the freeradius deads: /usr/sbin/freeradius -X ... including configuration file /etc/freeradius/sql/mysql/dialup.conf /etc/freeradius/sql/mysql/dialup.conf[130]: Expecting section start brace '{' after "FROM radcheck" Errors reading or parsing /etc/freeradius/radiusd.conf It seems that from the freeradius parser point of view, the above working sql is not good. Why?