FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s>
Disclaimer:
This solution was designed for some specific situation described below. Surely there might be better ways, but sometimes dueto my laziness & dumbness, I select whichever gives required results. You may fine tune it, or modify it as required. I always recommends to read the FR mailing list for any query.
It’s a weird world we live in.
EVERY HUMAN BEING IS BORN FREE BUT YET EVERYWHERE HE IS IN CHAINS … ~
Syed Jahanzaib ~
Scenario:
OP have single Mikrotik CCR as NAS (pppoe server) and Freeradius as billing system. VLAN(s) are configured on Cisco switch for each reseller/dealer area with proper Trunking configured from SW to CCR. OP have few reseller/franchise managers, like Dealer-A, Dealer-B. They can create there own users in freeradius using customized fronted designed in php. All dealers can view/add/edit there own users only.
Sometimes it happens that Dealer-A creates User-ID and provide it to a user/friend who is sitting in Dealer-B network, therefore from Billing perspective its a Loss for Dealer-B.
Requirement:
To prevent users roaming. Every user must be allowed to connect from there respective dealer network Only. [That is ‘allowed-vlan]. User’s MAC address validation is also in place but we know that mac can be cloned easily.
Solution:
We have a user table which contains a column vlanid. We will add a SQL IF statement which will check the the connecting user NAS-Port-Id & match it with users allowed vlanid in the user’s table.
Users Table Example:
mysql> describe users; +-------------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+-------------------+-----------------------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | username | varchar(128) | NO | UNI | NULL | | | password | varchar(32) | NO | | NULL | | | firstname | text | NO | | NULL | | | lastname | text | NO | | NULL | | | email | text | NO | | NULL | | | mobile | text | NO | | NULL | | | cnic | text | NO | | NULL | | | srvname | text | NO | | NULL | | | srvid | int(3) | NO | | NULL | | | expiration | date | YES | | NULL | | | mac | varchar(30) | NO | | NULL | | | bwpkg | varchar(256) | NO | | NULL | | | pool | varchar(128) | YES | | other | | | is_enabled | int(1) | NO | | NULL | | | is_days_expired | int(1) | NO | | NULL | | | is_qt_expired | int(1) | NO | | NULL | | | is_uptime_expired | int(1) | NO | | NULL | | | qt_total | varchar(32) | NO | | NULL | | | qt_used | varchar(20) | NO | | NULL | | | uptime_limit | varchar(20) | NO | | NULL | | | uptime_used | varchar(32) | NO | | NULL | | | owner | text | NO | | NULL | | | vlanid | varchar(32) | NO | | NULL | | | createdon | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------------------+--------------+------+-----+-------------------+-----------------------------+ mysql> select * from users; +----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+------------+-------------------+-------------+----------------------+------------+-----------------+---------------+-------------------+----------+---------+--------------+-------------+--------+-------------+---------------------+ | id | username | password | firstname | lastname | email | mobile | cnic | srvname | srvid | expiration | mac | bwpkg | pool | is_enabled | is_days_expired | is_qt_expired | is_uptime_expired | qt_total | qt_used | uptime_limit | uptime_used | owner | vlanid | createdon | +----+----------+----------+----------------+---------------+---------------------+-------------+-------------------+---------+-------+------------+-------------------+-------------+----------------------+------------+-----------------+---------------+-------------------+----------+---------+--------------+-------------+--------+-------------+---------------------+ | 1 | zaib | zaib | OK | jahanzaib | aacableAThotmail.com | 03333021909 | 1234567890-1-1 | | 0 | 2018-01-04 | 00:0C:29:B9:D8:A0 | 1024k/1024k | public-pool | 1 | 0 | 0 | 0 | 300 | 38980 | | | manager1 | ether1-LAN | 2018-01-17 16:45:05 |
Now we will add the SQL IF statement that will actually check every incoming Authentication request for matching vlan with users table.
Edit Default Sites-Enabled file,
nano /etc/freeradius/sites-enabled/default
& paste following in `Authorize` Section
if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
update reply {
Reply-Message = 'Error: You are not allowed to connect form this VLAN !'
}
update control {
Auth-Type := "Reject"
}
}
Save & Exit.
Now reload Freeradius in Debug Mode (by freeradius -X) & monitor the debugging.
If user will connect from another vlan which does not matches the vlanid in user’s table he will get denied with the message.
Note: For testing purposes . I have added dummy entry in user’s vlanid.
rad_recv: Access-Request packet from host 101.11.11.253 port 34175, id=11, length=119
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 14
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "00:0C:29:B9:D8:A0"
Called-Station-Id = "testppp"
NAS-Port-Id = "ether1-LAN" # < select vlanid from users where username = 'zaib'
rlm_sql (sql): Reserving sql socket id: 29
sql_xlat finished
rlm_sql (sql): Released sql socket id: 29
expand: %{sql: select vlanid from users where username = '%{User-Name}'} -> ether1-LAN-DUMMY
expand: %{NAS-Port-Id} -> ether1-LAN
? Evaluating ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") -> TRUE
++? if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") -> TRUE
++if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
+++update reply {
+++} # update reply = noop
+++update control {
+++} # update control = noop
++} # if ("%{sql: select vlanid from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") = noop
Sending delayed reject for request 0
Sending Access-Reject of id 11 to 101.11.11.253 port 34175
# Meaningful Reply Message by zaib
Reply-Message = "Error: You are not allowed to connect form this VLAN !"
Waking up in 4.9 seconds.
Cleaning up request 0 ID 11 with timestamp +31
Ready to process requests.
& if the users request matches , he will be granted access (off course after all other checks):)
2# Limit login by Service Type like PPTP only or PPPoE only
Just to share logic, may be incorrect but In Mikrotik I see following
- PPPoE > NAS-Port-Type = Ethernet
- PPTP > NAS-Port-Type = Virtual
mysql> describe users; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | username | varchar(64) | NO | | NULL | | | srvtype | varchar(16) | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) </div> <div>
mysql> select * from users; # this type will allow pppoe only +----+----------+----------+ | id | username | srvtype | +----+----------+----------+ | 1 | zaib | Ethernet | +----+----------+----------+ 1 row in set (0.00 sec) </div> <div>
if ("%{sql: select from users where username = '%{User-Name}'}" != "%{NAS-Port-Id}") {
update reply {
Reply-Message = 'Error: You are not allowed to connect form this VLAN !'
}
update control {
Auth-Type := "Reject"
}
}
(9) } # if ("%{sql: select srvtype from users where username = '%{User-Name}'}" != "%{NAS-Port-Type}") = noop
(9) Reply-Message = "Error: You are not allowed to connect using this service type !"
(9) Found Auth-Type = Reject
(9) Auth-Type = Reject, rejecting user
(9) Failed to authenticate the user
Syed Jahanzaib - Personal Blog to Share Knowledge ! 