question about mac auth, vlans and mysql queries.

[Nazzareno Taborgna]

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