Quantcast

Freeradius3 + SQL -> radusergroup check is not matched

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Freeradius3 + SQL -> radusergroup check is not matched

Martin Bednar
Hello,

I'm struggling with this issue for couple days so any help would be appreciated

I'm running 3.0.13 version with mariadb as backend

# rpm -aq | grep -i radius
freeradius-config-3.0.13-19.1.x86_64
freeradius-3.0.13-19.1.x86_64
freeradius-utils-3.0.13-19.1.x86_64
freeradius-mysql-3.0.13-19.1.x86_64

basic authentication is working fine, however I have problem with
groupcheck. What I'm trying to achieve is to limit user per SSID. I
have created 2 profiles with appropriate reply actions

MariaDB [radius]> select * from radgroupcheck;
+----+---------------------------+-------------------+----+----------------------+
| id | groupname                 | attribute         | op | value
          |
+----+---------------------------+-------------------+----+----------------------+
|  1 | Reject-Profile            | Auth-Type         | := | Reject
          |
|  6 | SSID_EMPL-Test     | Aruba-Essid-Name  | == | EMPL-Test            |

MariaDB [radius]> select * from radgroupreply;
+----+---------------------------+---------------+----+--------+
| id | groupname                 | attribute     | op | value  |
+----+---------------------------+---------------+----+--------+
|  1 | Reject-Profile            | Auth-Type     | := | Reject |
|  6 | SSID_EMPL-Test            | Reply-Message | =  | Vitaj  |


and assigned them to the user


MariaDB [radius]> select * from radusergroup;
+-----------------------+---------------------------+----------+
| username              | groupname                 | priority |
+-----------------------+---------------------------+----------+
| miro                  | SSID_EMPL-Test            |        1 |
| miro                  | Reject-Profile            |        2 |
+-----------------------+---------------------------+----------+

Expectation is that Radius will check whether user is connecting to
the SSID "EMPL-Test" if so it will be accepted otherwise it will go to
Reject profile and request will be rejected.

Problem is that user is always rejected even when is connecting to the
correct SSID.

(6) Received Access-Request Id 9 from 10.200.99.38:56327 to
10.1.20.5:1812 length 258
(6)   User-Name = "miro"
(6)   NAS-IP-Address = 10.200.99.38
(6)   NAS-Port = 0
(6)   NAS-Identifier = "10.200.99.38"
(6)   NAS-Port-Type = Wireless-802.11
(6)   Calling-Station-Id = "606720cbfb52"
(6)   Called-Station-Id = "84d47ec68cda"
(6)   Service-Type = Login-User
(6)   Framed-MTU = 1100
(6)   EAP-Message =
0x0207002b190017030100205b4dc9fa8209387ae85ddd94e674431e428c4baacb2f58cc7c14d4d310106e6d
(6)   State = 0x97bd745292ba6df3cc8378af9230d359
(6)   Aruba-Essid-Name = "EMPL-Test"
(6)   Aruba-Location-Id = "84:d4:7e:c6:8c:da"
(6)   Aruba-AP-Group = "instant-C6:8C:DA"
(6)   Aruba-Device-Type = "Win 7"

We can see in the output that Aruba-Essid-Name = "EMPL-Test"

but in the SQL part there is no match on that profile, instead of that
it is going to next one Reject-profile and rejecting the request:

(7) sql: EXPAND %{User-Name}
(7) sql:    --> miro
(7) sql: SQL-User-Name set to 'miro'
rlm_sql (sql): Reserved connection (1)
(7) sql: EXPAND SELECT id, username, attribute, value, op FROM
radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
(7) sql:    --> SELECT id, username, attribute, value, op FROM
radcheck WHERE username = 'miro' ORDER BY id
(7) sql: Executing select query: SELECT id, username, attribute,
value, op FROM radcheck WHERE username = 'miro' ORDER BY id
(7) sql: User found in radcheck table
(7) sql: Conditional check items matched, merging assignment check items
(7) sql:   Cleartext-Password := "miro"
(7) sql:   Expiration := "May 31 2017 00:00:00 CEST"
(7) sql: EXPAND SELECT id, username, attribute, value, op FROM
radreply WHERE username = '%{SQL-User-Name}' ORDER BY id
(7) sql:    --> SELECT id, username, attribute, value, op FROM
radreply WHERE username = 'miro' ORDER BY id
(7) sql: Executing select query: SELECT id, username, attribute,
value, op FROM radreply WHERE username = 'miro' ORDER BY id
(7) sql: EXPAND SELECT groupname FROM radusergroup WHERE username =
'%{SQL-User-Name}' ORDER BY priority
(7) sql:    --> SELECT groupname FROM radusergroup WHERE username =
'miro' ORDER BY priority
(7) sql: Executing select query: SELECT groupname FROM radusergroup
WHERE username = 'miro' ORDER BY priority
(7) sql: User found in the group table
(7) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id
(7) sql:    --> SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = 'SSID_EMPL-Test' ORDER BY id
(7) sql: Executing select query: SELECT id, groupname, attribute,
Value, op FROM radgroupcheck WHERE groupname = 'SSID_EMPL-Test' ORDER
BY id
(7) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id
(7) sql:    --> SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = 'Reject-Profile' ORDER BY id
(7) sql: Executing select query: SELECT id, groupname, attribute,
Value, op FROM radgroupcheck WHERE groupname = 'Reject-Profile' ORDER
BY id
(7) sql: Group "Reject-Profile": Conditional check items matched
(7) sql: Group "Reject-Profile": Merging assignment check items
(7) sql:   Auth-Type := Reject
(7) sql: EXPAND SELECT id, groupname, attribute, value, op FROM
radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id
(7) sql:    --> SELECT id, groupname, attribute, value, op FROM
radgroupreply WHERE groupname = 'Reject-Profile' ORDER BY id
(7) sql: Executing select query: SELECT id, groupname, attribute,
value, op FROM radgroupreply WHERE groupname = 'Reject-Profile' ORDER
BY id
(7) sql: Group "Reject-Profile": Merging reply items
(7) sql:   Auth-Type := Reject
rlm_sql (sql): Released connection (1)


What I don't really understand is that if I do the test with radclient
I got Accept:

# echo "User-Name=miro,User-Password=miro,Aruba-Essid-Name=EMPL-Test"
| radclient localhost:1812 auth test123
Sent Access-Request Id 148 from 0.0.0.0:35642 to 127.0.0.1:1812 length 61
Received Access-Accept Id 148 from 127.0.0.1:1812 to 0.0.0.0:0 length 33

#radiusd -X

(9) Received Access-Request Id 225 from 127.0.0.1:41535 to
127.0.0.1:1812 length 61
(9)   User-Name = "miro"
(9)   User-Password = "miro"
(9)   Aruba-Essid-Name = "EMPL-Test"
...
(9) sql: EXPAND SELECT id, username, attribute, value, op FROM
radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
(9) sql:    --> SELECT id, username, attribute, value, op FROM
radcheck WHERE username = 'miro' ORDER BY id
(9) sql: Executing select query: SELECT id, username, attribute,
value, op FROM radcheck WHERE username = 'miro' ORDER BY id
(9) sql: User found in radcheck table
(9) sql: Conditional check items matched, merging assignment check items
(9) sql:   Cleartext-Password := "miro"
(9) sql:   Expiration := "May 31 2017 00:00:00 CEST"
(9) sql: EXPAND SELECT id, username, attribute, value, op FROM
radreply WHERE username = '%{SQL-User-Name}' ORDER BY id
(9) sql:    --> SELECT id, username, attribute, value, op FROM
radreply WHERE username = 'miro' ORDER BY id
(9) sql: Executing select query: SELECT id, username, attribute,
value, op FROM radreply WHERE username = 'miro' ORDER BY id
(9) sql: EXPAND SELECT groupname FROM radusergroup WHERE username =
'%{SQL-User-Name}' ORDER BY priority
(9) sql:    --> SELECT groupname FROM radusergroup WHERE username =
'miro' ORDER BY priority
(9) sql: Executing select query: SELECT groupname FROM radusergroup
WHERE username = 'miro' ORDER BY priority
(9) sql: User found in the group table
(9) sql: EXPAND SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id
(9) sql:    --> SELECT id, groupname, attribute, Value, op FROM
radgroupcheck WHERE groupname = 'SSID_EMPL-Test' ORDER BY id
(9) sql: Executing select query: SELECT id, groupname, attribute,
Value, op FROM radgroupcheck WHERE groupname = 'SSID_EMPL-Test' ORDER
BY id
(9) sql: Group "SSID_EMPL-Test": Conditional check items matched
(9) sql: Group "SSID_EMPL-Test": Merging assignment check items
(9) sql: EXPAND SELECT id, groupname, attribute, value, op FROM
radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id
(9) sql:    --> SELECT id, groupname, attribute, value, op FROM
radgroupreply WHERE groupname = 'SSID_EMPL-Test' ORDER BY id
(9) sql: Executing select query: SELECT id, groupname, attribute,
value, op FROM radgroupreply WHERE groupname = 'SSID_EMPL-Test' ORDER
BY id
(9) sql: Group "SSID_EMPL-Test": Merging reply items
(9) sql:   Reply-Message = "Vitaj"


It seems that in both cases is Aruba-Essid-Name = "EMPL-Test" the same
so I don't understand the difference in behaviour. I tried also
different attribute but with the same result. I changed AP type also
the same result.

Any idea what could be the problem ?

Thank you,

--
Martin
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Freeradius3 + SQL -> radusergroup check is not matched

Alan DeKok-2
On May 15, 2017, at 6:18 AM, Martin Bednar <[hidden email]> wrote:

> basic authentication is working fine, however I have problem with
> groupcheck. What I'm trying to achieve is to limit user per SSID. I
> have created 2 profiles with appropriate reply actions
>
> MariaDB [radius]> select * from radgroupcheck;
> +----+---------------------------+-------------------+----+----------------------+
> | id | groupname                 | attribute         | op | value
>          |
> +----+---------------------------+-------------------+----+----------------------+
> |  1 | Reject-Profile            | Auth-Type         | := | Reject
>          |
> |  6 | SSID_EMPL-Test     | Aruba-Essid-Name  | == | EMPL-Test            |

  Which says that anyone in the SSID_EMPL-Test is rejected if they use the EMPL-Test  SSID.

  That's probably not what you want.

> Expectation is that Radius will check whether user is connecting to
> the SSID "EMPL-Test" if so it will be accepted otherwise it will go to
> Reject profile and request will be rejected.

  Please read the Wiki for how the SQL module works.  This is documented.

http://wiki.freeradius.org/modules/Rlm_sql

> What I don't really understand is that if I do the test with radclient
> I got Accept:

  The reason is buried in the debug output, and isn't immediately obvious.

> Any idea what could be the problem ?

  Write down what you want the server to do.  Read the SQL module documentation.  Then, configure it to do what you want...

  The simples approach TBH is to just create a custom table, which has columns User-Name and Aruba-Essid-Name.  Populate it with the information on who is allowed to use what SSID.

  Then, do a select:

        if "%{sql:SELECT username from SSID_TABLE where username = %{User-Name} and SSID = %{Aruba-Essid-Name}" == "") {
                reject
        }

  i.e. look up the user in the table by name and SSID.  If there's no entry, reject the user.

  The default SQL schema in FreeRADIUS is designed (and documented) to do one thing.  If you want to do something else, it's often best to create a custom table.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Loading...