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