associating mac addresses with usernames

[Stephen Vigus]

Ah, managed to get it working. Changed things to look like this:

if("%{sql:SELECT radusergroup.username FROM authorized_macs LEFT JOIN
radusergroup ON ( authorized_macs.username = radusergroup.username )
WHERE authorized_macs.macaddr = '%{User-Name}'}"){
        update control {
      Auth-Type := Accept
        }
        }
        else {
          reject
        }
}

I see in the radacct table it shows that the mac authenticated instead
of the username, but thats fine. I'm sure I'll figure it out.

Cheers
Stephen


On Sun, Apr 10, 2011 at 11:55 AM, Stephen Vigus <svigus at gmail.com> wrote:
> Hi Alan
>
> I think I'm probably doing things wrong. I realized the query I'm
> using is meant to count and allow anything thats not zero... not what
> I'm after tho.
> Just to double check, any sql queries I want to use are supposed to go
> inside "sites-available/default" under "authorize {" ?
>
> Below are the debug log if you still want to have a look at it:
>
> rad_recv: Access-Request packet from host 192.168.56.254 port 34095,
> id=10, length=192
>        NAS-Port-Type = Ethernet
>        Calling-Station-Id = "08:00:27:7C:51:CF"
>        Called-Station-Id = "hotspot1"
>        NAS-Port-Id = "ether2"
>        User-Name = "08:00:27:7C:51:CF"
>        NAS-Port = 2150629381
>        Acct-Session-Id = "80300005"
>        Framed-IP-Address = 192.168.56.102
>        Mikrotik-Host-IP = 192.168.56.102
>        User-Password = ""
>        Service-Type = Login-User
>        WISPr-Logoff-URL = "http://192.168.56.254/logout"
>        NAS-Identifier = "MikroTik"
>        NAS-IP-Address = 192.168.56.254
> +- entering group authorize {...}
> ++[preprocess] returns ok
> ++[chap] returns noop
> ++[mschap] returns noop
> [suffix] No '@' in User-Name = "08:00:27:7C:51:CF", looking up realm NULL
> [suffix] No such realm "NULL"
> ++[suffix] returns noop
> [sql]   expand: %{User-Name} -> 08:00:27:7C:51:CF
> [sql] sql_set_user escaped user --> '08:00:27:7C:51:CF'
> rlm_sql (sql): Reserving sql socket id: 4
> [sql]   expand: SELECT id, username, attribute, value, op
> FROM radcheck           WHERE username = '%{SQL-User-Name}'
> ORDER BY id -> SELECT id, username, attribute, value, op
> FROM radcheck           WHERE username = '08:00:27:7C:51:CF'
> ORDER BY id
> [sql]   expand: SELECT groupname           FROM radusergroup
> WHERE username = '%{SQL-User-Name}'           ORDER BY priority ->
> SELECT groupname           FROM radusergroup           WHERE username
> = '08:00:27:7C:51:CF'           ORDER BY priority
> rlm_sql (sql): Released sql socket id: 4
> [sql] User 08:00:27:7C:51:CF not found
> ++[sql] returns notfound
> ++[expiration] returns noop
> ++[logintime] returns noop
> [pap] WARNING! No "known good" password found for the user.
> Authentication may fail because of this.
> ++[pap] returns noop
> ++? if ("%{sql:SELECT COUNT(radusergroup.username) FROM
> authorized_macs LEFT JOIN radusergroup ON ( authorized_macs.username =
> radusergroup.username ) WHERE authorized_macs.macaddr =
> ''%{User-Name}'}" > 0)
> Badly formatted variable: %{sql:SELECT COUNT(radusergroup.username)
> FROM authorized_macs LEFT JOIN radusergroup ON (
> authorized_macs.username = radusergroup.username ) WHERE
> authorized_macs.macaddr = ''%{User-Name}'}
> ? Evaluating ("%{sql:SELECT COUNT(radusergroup.username) FROM
> authorized_macs LEFT JOIN radusergroup ON ( authorized_macs.username =
> radusergroup.username ) WHERE authorized_macs.macaddr =
> ''%{User-Name}'}" > 0) -> FALSE
> ++? if ("%{sql:SELECT COUNT(radusergroup.username) FROM
> authorized_macs LEFT JOIN radusergroup ON ( authorized_macs.username =
> radusergroup.username ) WHERE authorized_macs.macaddr =
> ''%{User-Name}'}" > 0) -> FALSE
> ++- entering else else {...}
> +++[reject] returns reject
> ++- else else returns reject
> Using Post-Auth-Type Reject
> +- entering group REJECT {...}
> [attr_filter.access_reject]     expand: %{User-Name} -> 08:00:27:7C:51:CF
>  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.
> Sending delayed reject for request 0
> Sending Access-Reject of id 10 to 192.168.56.254 port 34095
> Waking up in 4.9 seconds.
> Cleaning up request 0 ID 10 with timestamp +33
>
>
>
> Appreciate the help.
> Stephen
>
>
> On Sun, Apr 10, 2011 at 10:33 AM, Alan DeKok <aland at deployingradius.com> wrote:
>> Stephen Vigus wrote:
>>> In mysql this query would display the username associated to the mac
>>> (eg, user1 at realm1), although it seems freeradius does not like this.
>>
>>  <sigh>  Post the debug log.
>>
>>> Can anyone point me in the right direction so freeradius would think
>>> its "user1 at realm1" authenticating when it receives the mac address?
>>
>>  Read the documentation for how to solve problems.
>>
>>  Alan DeKok.
>> -
>> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>>
>