FreeRADIUS is a high performance RADIUS application that accepts a large number of network devices as RADIUS Client including MikroTik Router. FreeRADIUS MySQL module helps to get user authentication and authorization information from database server and to store user accounting information in database server. Configuring MySQL user group and profile properly, freeRADIUS user restriction can easily be maintained. How to configure user group to apply user limitation was discussed in my previous article. In this article, I will discuss how to configure freeRADIUS user profile to apply user limitation with more efficiently.

FreeRADIUS User Profile Configuration

A user profile is nothing but a user who has no entry in radcheck and radreply table but is a member of one or more groups to hold reply attributes. Assigning group to a profile is done with radusergroup table. User-Profile internal AVP is used to assign a profile to a user with radcheck table. So, a complete profile configuration is done by the following steps.

• Creating user limitations with group management
• Assigning group to a profile holder user
• Assigning created profile to users

In this article, we will create the following three profiles for MikroTik PPPoE users according to the above steps.

Creating User Limitation with Group Management

In freeRADIUS, group is used to categorize user check and reply attributes that actually apply user limitations. The radgroupcheck table contains check AVPs and the radgroupreply table contains reply AVPs. As we will create three user profiles, we have to create three groups also. Our proposed groups name and its check and reply attributes are summarized with the following tables.

The radgroupcheck table contains group check AVPs. So, we have to insert group check AVP Framed-Protocol in radgroupcheck table. The following steps will show how to insert check AVP in the radgroupcheck table.

• Login to your freeRADIUS Server (I have installed freeRADIUS server on CentOS 7 with MariaDB Database Server) with root user.
• Now login to your Database Server and select your RADIUS database (radius). You should replace your database username and password in the following command.

  [root@freeradius ~]# mysql -uroot -pPasskey85 radius

  Reading table information for completion of table and column names

  You can turn off this feature to get a quicker startup with -A

  Welcome to the MariaDB monitor.  Commands end with ; or \g.

  Your MariaDB connection id is 243

  Server version: 5.5.60-MariaDB MariaDB Server

  Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

  Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

  MariaDB [radius]>

• Issue the following command to insert Framed-Protocol check AVP for 512k group. As we are creating profile for MikroTik PPPoE user, the value of Framed-Protocol attribute should be PPP.
  MariaDB [radius]> insert into radgroupcheck (groupname,attribute,op,value) values (“512k”,”Framed-Protocol”,”==”,”PPP”);
• Now issue the following command to insert Framed-Protocol AVP for 1M group.
  MariaDB [radius]> insert into radgroupcheck (groupname,attribute,op,value) values (“1M”,”Framed-Protocol”,”==”,”PPP”);
• Similarly issue the following command to insert Framed-Protocol for 2M group.
  MariaDB [radius]> insert into radgroupcheck (groupname,attribute,op,value) values (“2M”,”Framed-Protocol”,”==”,”PPP”);
• To show entry in the radgroupreply table, issue the following command.

  MariaDB [radius]> select * from radgroupcheck;

  +—-+———–+—————–+—-+——-+

  | id | groupname | attribute       | op | value |

  +—-+———–+—————–+—-+——-+

  |  1 | 512k      | Framed-Protocol | == | PPP   |

  |  2 | 1M        | Framed-Protocol | == | PPP   |

  |  3 | 2M        | Framed-Protocol | == | PPP   |

  +—-+———–+—————–+—-+——-+

These entries ensure that group reply only applicable for the PPP request. After inserting group checking, we will now insert group reply AVP in the radgroupreply table. The following steps will show how to insert reply AVPs in the radgroupreply table.

• Issue the following command to set IP Pool for 512k group user.
  MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“512k”,”Framed-Pool”,”=”,”512k_pool”);
• Similarly, issue the following command to set IP Pool for 1M group user.
  MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“1M”,”Framed-Pool”,”=”,”1M_pool”);
• Again, issue the following command to set IP Pool for 2M group user.
  MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“2M”,”Framed-Pool”,”=”,”2M_pool”);
• Issue the following command to apply bandwidth limit for 512k group.
  MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“512k”,”Mikrotik-Rate-Limit”,”=”,”512k/512k 1M/1M 512k/512k 40/40″);

  Here, Mikrotik-Rate-Limit AVP indicates that 512k group user will get 512k upload and 512k download speed, 1M burst upload and 1M burst download, burst threshold upload 512k and download 512k and burst time is 40s for both upload and download.

• Similarly, issue the following command to apply bandwidth limit for 1M group user.
  MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“1M”,”Mikrotik-Rate-Limit”,”=”,”1M/1M 2M/2M 1M/1M 40/40″);
• Again, issue the following command to apply bandwidth limit for 1M group user.
  MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“2M”,”Mikrotik-Rate-Limit”,”=”,”2M/2M 4M/4M 2M/2M 40/40″);
• To show radgroupreply entries, issue the following command.

  MariaDB [radius]> select * from radgroupreply;

  +—-+———–+———————+—-+———————————+

  | id | groupname | attribute           | op | value                           |

  +—-+———–+———————+—-+———————————+

  | 12 | 512k      | Framed-Pool         | =  | 512k_pool                       |

  | 13 | 1M        | Framed-Pool         | =  | 1M_pool                         |

  | 14 | 2M        | Framed-Pool         | =  | 2M_pool                         |

  | 15 | 512k      | Mikrotik-Rate-Limit | =  | 512k/512k 1M/1M 512k/512k 40/40 |

  | 16 | 1M        | Mikrotik-Rate-Limit | =  | 1M/1M 2M/2M 1M/1M 40/40         |

  | 17 | 2M        | Mikrotik-Rate-Limit | =  | 2M/2M 4M/4M 2M/2M 40/40         |

  +—-+———–+———————+—-+———————————+

Group reply attributes are inserted successfully. Now we will assign our desired user profile to group.

Assigning Group to a Profile Holder User

After creating groups, it is time to assign group to user. As discussed early, freeRADIUS profile is a user but it has no entry in radcheck and radreply table. So, our proposed three profiles (512k_Profile, 1M_Profile and 2M_Profile) are logical users and we will assign these users to group according to the following table.

The radusergroup table contains user to group mapping. So, we need to insert entry in radusergroup table to map our profile and group. The following steps will show how to map profile and group in radusergroup table.

• Issue the following command to map 512k_Profile to 512k group.
  MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“512k_Profile”,”512k”,10);
• Similarly, issue the following command to map 1M_Profile to 1M group.
  MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“1M_Profile”,”1M”,10);
• Again, issue the following command to map 2M_Profile to 2M group.
  MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“2M_Profile”,”2M”,10);
• To show radusergroup table entry, issue the following command.

  MariaDB [radius]> select * from radusergroup;

  +————–+———–+———-+

  | username     | groupname | priority |

  +————–+———–+———-+

  | 512k_Profile | 512k      |       10 |

  | 1M_Profile   | 1M        |       10 |

  | 2M_Profile   | 2M        |       10 |

  +————–+———–+———-+

Profile to group mapping has been completed. Now we will create user and assign profile to user.

Assigning Created Profile to Users

After creating user profile, we can create as many users as we want and assign their profile with User-Profile control attribute for applying user limitation. The radcheck table contains user check attribute. So, to create users, we have to insert username and password as well as other user check attribute in radcheck table. In this article, we will create three users (bob, alice and tom) and assign their profile with radcheck table. The following steps will show how to insert user check attribute in radcheck table.

• To create bob user whose password will be passme, issue the following command.
  MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“bob”,”Cleartext-Password”,”:=”,”passme”);
• Similarly, to create alice user, issue the following command.
  MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“alice”,”Cleartext-Password”,”:=”,”passme”);
• Again, to create tom user, issue the following command.
  MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“tom”,”Cleartext-Password”,”:=”,”passme”);
• Now to assign 512k_Profile to bob user, issue the following command.
  MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“bob”,”User-Profile”,”:=”,”512k_Profile”);
• Similarly, to assign 1M_Profile to alice user, issue the following command.
  MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“alice”,”User-Profile”,”:=”,”1M_Profile”);
• Again, to assign 2M_Profile to tom user, issue to following command.
  MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“tom”,”User-Profile”,”:=”,”2M_Profile”);
• To show radcheck entry, issue the following command.

  MariaDB [radius]> select * from radcheck;

  +—-+———-+——————–+—-+————–+

  | id | username | attribute          | op | value        |

  +—-+———-+——————–+—-+————–+

  | 17 | bob      | Cleartext-Password | := | passme       |

  | 18 | alice    | Cleartext-Password | := | passme       |

  | 19 | tom      | Cleartext-Password | := | passme       |

  | 20 | bob      | User-Profile       | := | 512k_Profile |

  | 21 | alice    | User-Profile       | := | 1M_Profile   |

  | 22 | tom      | User-Profile       | := | 2M_Profile   |

  +—-+———-+——————–+—-+————–+

• To logout from database, issue the quit command.

We have successfully created three RADIUS users and assigned their profile. Now we will check these users login and reply attributes with radtest program.

FreeRADIUS User Profile Testing with radtest Program

We will now test our user profile configuration with radtest program. So, issue the following command to login with bob user and check his reply attribute.

If everything is OK, the radtest program will show the abobe output. That means, bob user will now be able to login to freeRADIUS client devices with his password and after successful login he will get an IP from 512k_pool and his bandwidth will be according to Mikrotik-Rate-Limit AVP.

Similarly, you can test alice and tom user with radtest program and can check user reply attributes. In the next article, we will test freeRADIUS user profile from a MikroTik Router where MikroTik PPPoE Service will be accessible with these users.

If you face any confusion to follow above steps properly, watch the below video about MikroTik with FreeRADIUS User Profile Configuration. I hope it will reduce your any confusion.

How to configure freeRADIUS user profile with MySQL database has been discussed in this article. I hope you will now be able to configure freeRADIUS user profile according to your organization requirements. However, if you face any confusion, feel free to discuss in comment or contact with me from Contact page. In the next article, I will show how to configure MikroTik PPPoE Service with freeRADIUS Server and authenticate and authorize PPPoE users from these profile users.