Skip to content

Instantly share code, notes, and snippets.

@nasirhafeez

nasirhafeez/freeradius-advanced-use-cases.md

Last active July 24, 2025 10:18
Download ZIP

Revisions

  1. nasirhafeez revised this gist Apr 23, 2025. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -30,7 +30,7 @@

    [Transforming Data Using Regular Expression Sub-Capture](#transforming-data-using-regular-expression-sub-capture)

    [Change of Authorization Example on Mikrotik](#coa-example-mikrotik)
    [Change of Authorization Example on Mikrotik](#change-of-authorization-example-on-mikrotik)

    # Introduction

  2. nasirhafeez revised this gist Apr 23, 2025. 1 changed file with 26 additions and 0 deletions.
    26 changes: 26 additions & 0 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -30,6 +30,8 @@

    [Transforming Data Using Regular Expression Sub-Capture](#transforming-data-using-regular-expression-sub-capture)

    [Change of Authorization Example on Mikrotik](#coa-example-mikrotik)

    # Introduction

    This document presents the configurations related to some advanced
    @@ -545,4 +547,28 @@ if (Cisco-AVPair =~ /^client-mac-address=(..)(..).(..)(..).(..)(..)$/){
    Cisco-AVPair := "%{Tmp-String-1}"
    }
    }
    ```

    # Change of Authorization Example on Mikrotik

    Suppose we want to increase the `Session-Timeout` of a user. The user will be identified by `User-Name` and `Framed-IP-Address`. Create file `coa` with these contents:

    ```
    User-Name := "D6:D8:C3:1E:BF:4F"
    Framed-IP-Address := 192.168.88.252
    NAS-IP-Address := 192.168.200.32
    Session-Timeout := 3600
    ```

    Execute `coa` command:

    ```
    root@Lenovo-i7:~# cat coa | radclient -x 192.168.200.32:3799 coa testing123
    Sent CoA-Request Id 224 from 0.0.0.0:57226 to 192.168.200.32:3799 length 57
    User-Name := "D6:D8:C3:1E:BF:4F"
    Framed-IP-Address := 192.168.88.253
    NAS-IP-Address := 192.168.200.32
    Session-Timeout := 3600
    Received CoA-ACK Id 224 from 192.168.200.32:3799 to 172.22.51.155:57226 length 38
    Message-Authenticator = 0x44697ff3ad17a89cb4ebe9df2684ee57
    ```
  3. nasirhafeez revised this gist Feb 25, 2024. 1 changed file with 4 additions and 2 deletions.
    6 changes: 4 additions & 2 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -243,7 +243,7 @@ CREATE TABLE IF NOT EXISTS `macs` (
    );
    ```

    The following code is added to authorize section of `/etc/freeradius/3.0/sites-enabled/default` for MAC addition and
    The following code is added to `authorize` section of `/etc/freeradius/3.0/sites-enabled/default` for MAC addition and
    authorization. When a username is initially created it has the MAC
    address set to `na`. Upon first connection the MAC address is saved in
    `macs` table, and subsequently it is checked.
    @@ -533,7 +533,9 @@ User-Password = "pass1"
    Cisco-AVPair = "client-mac-address=70a7.4130.6438"
    ```

    Suppose we wanted to transform it into this form: `Cisco-AVPair = "70:A7:41:30:64:38"`. Here is the code for doing it:
    Suppose we wanted to transform it into this form: `Cisco-AVPair = "70:A7:41:30:64:38"`.

    We can add the following code in the relevant configration section (such as `authorize` section of `/etc/freeradius/3.0/sites-enabled/default`):

    ```
    if (Cisco-AVPair =~ /^client-mac-address=(..)(..).(..)(..).(..)(..)$/){
  4. nasirhafeez revised this gist Feb 25, 2024. 1 changed file with 24 additions and 0 deletions.
    24 changes: 24 additions & 0 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -28,6 +28,8 @@

    [Accepting All Passwords For a Username](#accepting-all-passwords-for-a-username)

    [Transforming Data Using Regular Expression Sub-Capture](#transforming-data-using-regular-expression-sub-capture)

    # Introduction

    This document presents the configurations related to some advanced
    @@ -519,4 +521,26 @@ When using MySQL, add username with `Auth-Type:= Accept` in `radcheck` like this
    +----+----------+------------+----+----------------------+
    | 1 | testuser | Auth-Type | := | Accept |
    +----+----------+------------+----+----------------------+
    ```

    # Transforming Data Using Regular Expression Sub-Capture

    Let's say we have an attribute of this form `Cisco-AVPair = "client-mac-address=70a7.4130.6438"` as shown below:

    ```
    User-Name = "user1"
    User-Password = "pass1"
    Cisco-AVPair = "client-mac-address=70a7.4130.6438"
    ```

    Suppose we wanted to transform it into this form: `Cisco-AVPair = "70:A7:41:30:64:38"`. Here is the code for doing it:

    ```
    if (Cisco-AVPair =~ /^client-mac-address=(..)(..).(..)(..).(..)(..)$/){
    update request {
    Tmp-String-0 = "%{1}:%{2}:%{3}:%{4}:%{5}:%{6}"
    Tmp-String-1 = "%{toupper:%{Tmp-String-0}}"
    Cisco-AVPair := "%{Tmp-String-1}"
    }
    }
    ```
  5. nasirhafeez revised this gist Feb 6, 2024. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -241,7 +241,7 @@ CREATE TABLE IF NOT EXISTS `macs` (
    );
    ```

    The following code is added to authorize section for MAC addition and
    The following code is added to authorize section of `/etc/freeradius/3.0/sites-enabled/default` for MAC addition and
    authorization. When a username is initially created it has the MAC
    address set to `na`. Upon first connection the MAC address is saved in
    `macs` table, and subsequently it is checked.
  6. nasirhafeez revised this gist Nov 22, 2023. 1 changed file with 20 additions and 0 deletions.
    20 changes: 20 additions & 0 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -26,6 +26,8 @@

    [pfSense PPPoE Server MPD Bandwidth Rate Limiting](#pfsense-pppoe-server-mpd-bandwidth-rate-limiting)

    [Accepting All Passwords For a Username](#accepting-all-passwords-for-a-username)

    # Introduction

    This document presents the configurations related to some advanced
    @@ -499,4 +501,22 @@ $INCLUDE dictionary.mpd
    steve Cleartext-Password := "testing"
    mpd-limit = "in#1=all rate-limit 3000000",
    mpd-limit = "out#1=all rate-limit 3000000"
    ```

    # Accepting All Passwords For a Username

    When using users file, add the following on top of `/etc/freeradius/3.0/users`:

    ```
    DEFAULT Auth-Type := Accept
    ```

    When using MySQL, add username with `Auth-Type:= Accept` in `radcheck` like this:

    ```
    +----+----------+------------+----+----------------------+
    | id | username | attribute | op | value |
    +----+----------+------------+----+----------------------+
    | 1 | testuser | Auth-Type | := | Accept |
    +----+----------+------------+----+----------------------+
    ```
  7. nasirhafeez revised this gist Jun 8, 2022. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -63,7 +63,7 @@ FLUSH PRIVILEGES;
    exit
    ```

    *Import MySQL schema for database*
    *Import MySQL Schema*

    ```
    mysql radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql
    @@ -154,7 +154,7 @@ systemctl restart freeradius
    Open MySQL:

    ```
    mysql -u root -p
    mysql
    ```

    Add user:
  8. nasirhafeez revised this gist Jun 8, 2022. 1 changed file with 7 additions and 0 deletions.
    7 changes: 7 additions & 0 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -47,8 +47,15 @@ apt-get install freeradius freeradius-mysql freeradius-utils

    *Setup MySQL*

    Open MySQL:

    ```
    mysql
    ```

    Enter the following commands:

    ```
    create database radius;
    CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
    GRANT ALL PRIVILEGES ON radius.* TO '<username>'@'localhost';
  9. nasirhafeez revised this gist Jun 8, 2022. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -42,7 +42,7 @@ apt-get install -y mysql-server
    *Install FreeRADIUS*

    ```
    sudo apt-get install freeradius freeradius-mysql freeradius-utils
    apt-get install freeradius freeradius-mysql freeradius-utils
    ```

    *Setup MySQL*
    @@ -126,14 +126,14 @@ Save and exit.
    *Enable SQL Module*

    ```
    sudo ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/
    ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/
    ```

    *Set Permissions*

    ```
    sudo chgrp -h freerad /etc/freeradius/3.0/mods-available/sql
    sudo chown -R freerad:freerad /etc/freeradius/3.0/mods-enabled/sql
    chgrp -h freerad /etc/freeradius/3.0/mods-available/sql
    chown -R freerad:freerad /etc/freeradius/3.0/mods-enabled/sql
    ```

    *Restart FreeRADIUS Service*
  10. nasirhafeez revised this gist Jun 8, 2022. 1 changed file with 136 additions and 0 deletions.
    136 changes: 136 additions & 0 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -4,6 +4,8 @@

    [Introduction](#introduction)

    [Installation](#installation)

    [Expiration](#expiration)

    [Changing IP Pool for Expired Username](#changing-ip-pool-for-expired-username)
    @@ -29,6 +31,140 @@
    This document presents the configurations related to some advanced
    FreeRADIUS use cases.

    # Installation

    *Install MySQL*

    ```
    apt-get install -y mysql-server
    ```

    *Install FreeRADIUS*

    ```
    sudo apt-get install freeradius freeradius-mysql freeradius-utils
    ```

    *Setup MySQL*

    ```
    mysql
    create database radius;
    CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
    GRANT ALL PRIVILEGES ON radius.* TO '<username>'@'localhost';
    FLUSH PRIVILEGES;
    exit
    ```

    *Import MySQL schema for database*

    ```
    mysql radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql
    ```

    *Setup FreeRADIUS-MySQL Integration*

    Open SQL configuration file:

    ```
    nano /etc/freeradius/3.0/mods-available/sql
    ```

    Set the following options:

    ```
    dialect = "mysql"
    ```

    Comment:

    ```
    # driver = "rlm_sql_null"
    ```

    Uncomment:

    ```
    driver = "rlm_sql_${dialect}"
    ```

    Comment out `tls` section in `mysql` section:

    ```
    mysql {
    # If any of the files below are set, TLS encryption is enabled
    # tls {
    # ca_file = "/etc/ssl/certs/my_ca.crt"
    # ca_path = "/etc/ssl/certs/"
    # certificate_file = "/etc/ssl/certs/private/client.crt"
    # private_key_file = "/etc/ssl/certs/private/client.key"
    # cipher = "DHE-RSA-AES256-SHA:AES128-SHA"
    #
    # tls_required = yes
    # tls_check_cert = no
    # tls_check_cert_cn = no
    # }
    ```

    Uncomment and add login credentials created earlier:

    ```
    server = "localhost"
    port = 3306
    login = "<username>"
    password = "<password>"
    ```

    Uncomment:

    ```
    read_clients = yes
    ```

    Save and exit.

    *Enable SQL Module*

    ```
    sudo ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/
    ```

    *Set Permissions*

    ```
    sudo chgrp -h freerad /etc/freeradius/3.0/mods-available/sql
    sudo chown -R freerad:freerad /etc/freeradius/3.0/mods-enabled/sql
    ```

    *Restart FreeRADIUS Service*

    ```
    systemctl restart freeradius
    ```

    *Create Dummy User*

    Open MySQL:

    ```
    mysql -u root -p
    ```

    Add user:

    ```
    use radius;
    insert into radcheck (username,attribute,op,value) values("fredf", "Cleartext-Password", ":=", "wilma");
    ```

    *Authentication Test Using radtest*

    ```
    radtest fredf wilma localhost 0 testing123
    ```

    You should receive `Access-Accept` in response.

    # Expiration

    The Expiration attribute is entered in `radcheck` table as follows:
  11. nasirhafeez revised this gist Aug 5, 2021. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -305,6 +305,7 @@ Received Disconnect-ACK Id 28 from 192.168.100.35:3799 to 0.0.0.0:0 length 30
    # pfSense PPPoE Server MPD Bandwidth Rate Limiting

    [Reference](https://forum.netgate.com/topic/141034/rate-limit-on-radius-reply-attributes-for-pppoe-connections-not-working/2)

    [MPD RADIUS Documentation](http://mpd.sourceforge.net/doc5/mpd30.html)

    1. Create `dictionary.mpd` in `/usr/share/freeradius/`:
  12. nasirhafeez revised this gist Aug 5, 2021. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -305,6 +305,7 @@ Received Disconnect-ACK Id 28 from 192.168.100.35:3799 to 0.0.0.0:0 length 30
    # pfSense PPPoE Server MPD Bandwidth Rate Limiting

    [Reference](https://forum.netgate.com/topic/141034/rate-limit-on-radius-reply-attributes-for-pppoe-connections-not-working/2)
    [MPD RADIUS Documentation](http://mpd.sourceforge.net/doc5/mpd30.html)

    1. Create `dictionary.mpd` in `/usr/share/freeradius/`:

  13. nasirhafeez revised this gist Aug 5, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,7 +22,7 @@

    [User Disconnection from RADIUS](#user-disconnection-from-radius)

    [pfSense PPPoE Server MPD Bandwidth Rate Limiting](#pfsense-pppoe-server-mpd-bandwidth-rate-limit)
    [pfSense PPPoE Server MPD Bandwidth Rate Limiting](#pfsense-pppoe-server-mpd-bandwidth-rate-limiting)

    # Introduction

  14. nasirhafeez revised this gist Aug 5, 2021. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,7 +22,7 @@

    [User Disconnection from RADIUS](#user-disconnection-from-radius)

    [pfSense PPPoE Server (MPD) Bandwidth Rate Limiting](#pfsense-pppoe-server-mpd-bandwidth-rate-limit)
    [pfSense PPPoE Server MPD Bandwidth Rate Limiting](#pfsense-pppoe-server-mpd-bandwidth-rate-limit)

    # Introduction

    @@ -302,11 +302,11 @@ Received Disconnect-ACK Id 28 from 192.168.100.35:3799 to 0.0.0.0:0 length 30

    <img width="485" alt="image11" src="https://user-images.githubusercontent.com/38311694/83622636-af75a400-a5a9-11ea-859b-0362c7196d96.png">

    # pfSense PPPoE Server (MPD) Bandwidth Rate Limiting
    # pfSense PPPoE Server MPD Bandwidth Rate Limiting

    [Reference](https://forum.netgate.com/topic/141034/rate-limit-on-radius-reply-attributes-for-pppoe-connections-not-working/2)

    1. Create dictionary.mpd in the dictionary folder:
    1. Create `dictionary.mpd` in `/usr/share/freeradius/`:

    ```
    # dictionary.mpd
    @@ -342,7 +342,7 @@ ATTRIBUTE mpd-drop-user 154 integer
    END-VENDOR mpd
    ```

    2. Add link to dictionary.mpd in dictionary file:
    2. Add link to `dictionary.mpd` in `/usr/share/freeradius/dictionary`:

    ```
    $INCLUDE dictionary.mpd
  15. nasirhafeez revised this gist Aug 5, 2021. 1 changed file with 55 additions and 0 deletions.
    55 changes: 55 additions & 0 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,6 +22,8 @@

    [User Disconnection from RADIUS](#user-disconnection-from-radius)

    [pfSense PPPoE Server (MPD) Bandwidth Rate Limiting](#pfsense-pppoe-server-mpd-bandwidth-rate-limit)

    # Introduction

    This document presents the configurations related to some advanced
    @@ -300,3 +302,56 @@ Received Disconnect-ACK Id 28 from 192.168.100.35:3799 to 0.0.0.0:0 length 30

    <img width="485" alt="image11" src="https://user-images.githubusercontent.com/38311694/83622636-af75a400-a5a9-11ea-859b-0362c7196d96.png">

    # pfSense PPPoE Server (MPD) Bandwidth Rate Limiting

    [Reference](https://forum.netgate.com/topic/141034/rate-limit-on-radius-reply-attributes-for-pppoe-connections-not-working/2)

    1. Create dictionary.mpd in the dictionary folder:

    ```
    # dictionary.mpd
    VENDOR mpd 12341
    BEGIN-VENDOR mpd
    ATTRIBUTE mpd-rule 1 string
    ATTRIBUTE mpd-pipe 2 string
    ATTRIBUTE mpd-queue 3 string
    ATTRIBUTE mpd-table 4 string
    ATTRIBUTE mpd-table-static 5 string
    ATTRIBUTE mpd-filter 6 string
    ATTRIBUTE mpd-limit 7 string
    ATTRIBUTE mpd-input-octets 8 string
    ATTRIBUTE mpd-input-packets 9 string
    ATTRIBUTE mpd-output-octets 10 string
    ATTRIBUTE mpd-output-packets 11 string
    ATTRIBUTE mpd-link 12 string
    ATTRIBUTE mpd-bundle 13 string
    ATTRIBUTE mpd-iface 14 string
    ATTRIBUTE mpd-iface-index 15 integer
    ATTRIBUTE mpd-input-acct 16 string
    ATTRIBUTE mpd-output-acct 17 string
    ATTRIBUTE mpd-action 18 string
    ATTRIBUTE mpd-peer-ident 19 string
    ATTRIBUTE mpd-iface-name 20 string
    ATTRIBUTE mpd-iface-descr 21 string
    ATTRIBUTE mpd-iface-group 22 string
    ATTRIBUTE mpd-drop-user 154 integer
    END-VENDOR mpd
    ```

    2. Add link to dictionary.mpd in dictionary file:

    ```
    $INCLUDE dictionary.mpd
    ```

    3. Add rate limit to user using `mpd-limit` attribute in the following way:

    ```
    steve Cleartext-Password := "testing"
    mpd-limit = "in#1=all rate-limit 3000000",
    mpd-limit = "out#1=all rate-limit 3000000"
    ```
  16. nasirhafeez revised this gist Aug 3, 2021. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions freeradius-advanced-use-cases.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,5 @@
    # FreeRADIUS Advanced Use Cases

    # Contents

    [Introduction](#introduction)
  17. nasirhafeez renamed this gist Jun 3, 2020. 1 changed file with 0 additions and 0 deletions.
    0 file.md → freeradius-advanced-use-cases.md
    View file
    File renamed without changes.
  18. nasirhafeez revised this gist Jun 3, 2020. 1 changed file with 13 additions and 13 deletions.
    26 changes: 13 additions & 13 deletions file.md
    View file
    Original file line number Diff line number Diff line change
    @@ -44,15 +44,15 @@ Add the following code in authorize section of
    `/etc/freeradius/3.0/sites-enabled/default`:
    ```
    expiration{
    userlock = 1
    userlock = 1
    }
    if(userlock){
    # Let him connect with EXPIRED pool in reply
    ok
    update reply {
    Reply-Message := "Your account has expired, %{User-Name} / Reason: DATE LIMIT REACHED"
    Framed-Pool := "Expired"
    }
    # Let him connect with EXPIRED pool in reply
    ok
    update reply {
    Reply-Message := "Your account has expired, %{User-Name} / Reason: DATE LIMIT REACHED"
    Framed-Pool := "Expired"
    }
    }
    ```

    @@ -75,7 +75,7 @@ Enter the following code in `Post-Auth-Type REJECT` section of
    `/etc/freeradius/3.0/sites-enabled/default`:
    ```
    update reply {
    Reply-Message = 'Wrong Password'
    Reply-Message = 'Wrong Password'
    }
    ```
    <img width="491" alt="image6" src="https://user-images.githubusercontent.com/38311694/83622614-aab0f000-a5a9-11ea-89bd-76f60f2c7e17.png">
    @@ -85,11 +85,11 @@ Reply-Message = 'Wrong Password'
    A new table is created to store username to MAC address bindings:
    ```
    CREATE TABLE IF NOT EXISTS `macs` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    `callingstationid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'na',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    `callingstationid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'na',
    PRIMARY KEY (`id`)
    );
    ```

    The following code is added to authorize section for MAC addition and
  19. nasirhafeez revised this gist Jun 3, 2020. 1 changed file with 75 additions and 162 deletions.
    237 changes: 75 additions & 162 deletions file.md
    View file
    Original file line number Diff line number Diff line change
    @@ -75,9 +75,7 @@ Enter the following code in `Post-Auth-Type REJECT` section of
    `/etc/freeradius/3.0/sites-enabled/default`:
    ```
    update reply {
    Reply-Message = 'Wrong Password'
    }
    ```
    <img width="491" alt="image6" src="https://user-images.githubusercontent.com/38311694/83622614-aab0f000-a5a9-11ea-89bd-76f60f2c7e17.png">
    @@ -86,19 +84,12 @@ Reply-Message = 'Wrong Password'

    A new table is created to store username to MAC address bindings:
    ```
    CREATE TABLE IF NOT EXISTS \`macs\` (
    \`id\` int unsigned NOT NULL AUTO\_INCREMENT,
    \`username\` varchar(100) COLLATE utf8mb4\_unicode\_ci NOT NULL,
    \`callingstationid\` varchar(50) COLLATE utf8mb4\_unicode\_ci NOT NULL
    DEFAULT 'na',
    PRIMARY KEY (\`id\`)
    ) ENGINE=InnoDB AUTO\_INCREMENT=7 DEFAULT CHARSET=utf8mb4
    COLLATE=utf8mb4\_unicode\_ci;
    CREATE TABLE IF NOT EXISTS `macs` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    `callingstationid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'na',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ```

    The following code is added to authorize section for MAC addition and
    @@ -107,62 +98,38 @@ address set to `na`. Upon first connection the MAC address is saved in
    `macs` table, and subsequently it is checked.
    ```
    update control{
    #check if callingstationid does not exist in mac_limit Table
    Tmp-String-0 = "%{sql: SELECT callingstationid FROM macs WHERE username = '%{User-Name}'}"
    \#check if callingstationid does not exist in mac\_limit Table
    Tmp-String-0 = "%{sql: SELECT callingstationid FROM macs WHERE username
    = '%{User-Name}'}"
    \#check if mac is available or not in mac\_limit table
    Tmp-Integer-2 = "%{sql: SELECT count(\*) FROM macs WHERE username =
    '%{User-Name}' AND callingstationid='%{Calling-Station-Id}'}"
    #check if mac is available or not in mac_limit table
    Tmp-Integer-2 = "%{sql: SELECT count(*) FROM macs WHERE username = '%{User-Name}' AND callingstationid='%{Calling-Station-Id}'}"
    }
    if(\&control:Tmp-String-0 == "na"){
    "%{sql: UPDATE macs SET callingstationid = '%{Calling-Station-Id}' WHERE
    Username = '%{User-Name}'}"
    update reply {
    Reply-Message := "New mac Added"
    if(&control:Tmp-String-0 == "na"){
    "%{sql: UPDATE macs SET callingstationid = '%{Calling-Station-Id}' WHERE Username = '%{User-Name}'}"
    update reply {
    Reply-Message := "New mac Added"
    }
    }
    }
    elsif(\&control:Tmp-Integer-2 == 0){
    update reply {
    Reply-Message += "MAC auth Failed"
    }
    reject
    elsif(&control:Tmp-Integer-2 == 0){
    update reply {
    Reply-Message += "MAC auth Failed"
    }
    reject
    }
    ```
    # User Binding with NAS

    A new table is created to store huntgroups:
    ```
    CREATE TABLE radhuntgroup (
    id int(11) unsigned NOT NULL auto\_increment,
    groupname varchar(64) NOT NULL default '',
    nasipaddress varchar(15) NOT NULL default '',
    nasportid varchar(15) default NULL,
    PRIMARY KEY (id),
    KEY nasipaddress (nasipaddress)
    id int(11) unsigned NOT NULL auto_increment,
    groupname varchar(64) NOT NULL default '',
    nasipaddress varchar(15) NOT NULL default '',
    nasportid varchar(15) default NULL,
    PRIMARY KEY (id),
    KEY nasipaddress (nasipaddress)
    ) ;
    ```
    NAS IP addresses are assigned to various huntgroups:
    @@ -180,59 +147,34 @@ belonging to `hg1`. Similarly, users of usergroup `usergroup2` are only
    allowed to be connected to NASs belonging to `hg2`.
    ```
    update request{
    Huntgroup-Name := "%{sql:SELECT groupname FROM radhuntgroup WHERE
    nasipaddress='%{NAS-IP-Address}'}"
    Huntgroup-Name := "%{sql:SELECT groupname FROM radhuntgroup WHERE nasipaddress='%{NAS-IP-Address}'}"
    }
    \# bind hg1users usergroup to hg1 huntgroup
    # bind hg1users usergroup to hg1 huntgroup
    if (SQL-Group == "hg1users") {
    if (Huntgroup-Name == "hg1") {
    \#ok
    if (Huntgroup-Name == "hg1") {
    #ok
    }
    else {
    update reply {
    Reply-Message += "Error: User not allowed connection on this device"
    }
    reject
    }
    }
    else {
    update reply {
    Reply-Message += "Error: User not allowed connection on this device"
    }
    reject
    }
    }
    \# bind hg2users usergroup to hg2 huntgroup
    # bind hg2users usergroup to hg2 huntgroup
    elsif (SQL-Group == "hg2users") {
    if (Huntgroup-Name == "hg2") {
    \#ok
    }
    else {
    update reply {
    Reply-Message += "Error: User not allowed connection on this device"
    if (Huntgroup-Name == "hg2") {
    #ok
    }
    else {
    update reply {
    Reply-Message += "Error: User not allowed connection on this device"
    }
    reject
    }
    }
    reject
    }
    }
    ```
    [Reference](https://wiki.freeradius.org/guide/SQL-Huntgroup-HOWTO)

    @@ -242,32 +184,21 @@ Suppose we only want to allow requests to RADIUS of the following
    Service-Type:

    1. Framed

    2. Login

    And we want to reject all other requests. The following code should be
    added to authorize section:
    ```
    \#Only allow service types "Framed-User" or "Login-User"; reject all
    others
    if((\&request:Service-Type=="Framed-User") ||
    (\&request:Service-Type=="Login-User")){
    \#ok
    #Only allow service types "Framed-User" or "Login-User"; reject all others
    if((&request:Service-Type=="Framed-User") || (&request:Service-Type=="Login-User")){
    #ok
    }
    else{
    update reply {
    Reply-Message += "Wrong service type"
    }
    reject
    update reply {
    Reply-Message += "Wrong service type"
    }
    reject
    }
    ```
    # SQLCounter
    @@ -287,23 +218,17 @@ The file `mods-enabled/sqlcounter` contains several default counters. This
    is the configuration of daily counter:
    ```
    sqlcounter dailycounter {
    sql_module_instance = sql
    dialect = mysql
    sql\_module\_instance = sql
    dialect = mysql
    counter\_name = Daily-Session-Time
    check\_name = Max-Daily-Session
    counter_name = Daily-Session-Time
    check_name = Max-Daily-Session
    reply_name = Session-Timeout
    reply\_name = Session-Timeout
    key = User-Name
    reset = daily
    $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
    key = User-Name
    reset = daily
    $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
    }
    ```
    This counter checks for an internal attribute `Max-Daily-Session` and uses
    @@ -330,23 +255,18 @@ volume daily.

    In the file `mods-enabled/sqlcounter` create a new counter:
    ```
    sqlcounter total\_volume {
    sql\_module\_instance = sql
    dialect = sql
    counter\_name = Max-Total-Volume
    sqlcounter total_volume {
    check\_name = Max-Volume
    sql_module_instance = sql
    dialect = sql
    key = User-Name
    counter_name = Max-Total-Volume
    check_name = Max-Volume
    reset = daily
    query = "SELECT SUM(acctinputoctets) + SUM(acctoutputoctets) FROM
    radacct WHERE UserName='%{${key}}'"
    key = User-Name
    reset = daily
    query = "SELECT SUM(acctinputoctets) + SUM(acctoutputoctets) FROM radacct WHERE UserName='%{${key}}'"
    }
    ```
    Register the counter `total\_volume` in authorize section in
    @@ -368,18 +288,11 @@ performs some action on them (like disconnection, rate-limiting etc).
    To disconnect a user from RADIUS we use the user’s username and IP
    address and secret of NAS in radclient to disconnect that user:
    ```
    root@ubuntu:\~\# echo 'User-Name = test1' | radclient -x
    192.168.100.35:3799 disconnect ''1234''
    Sent Disconnect-Request Id 28 from 0.0.0.0:35685 to 192.168.100.35:3799
    length 27
    User-Name = "test1"
    Received Disconnect-ACK Id 28 from 192.168.100.35:3799 to 0.0.0.0:0
    length 30
    NAS-Identifier = "MikroTik"
    root@ubuntu:~# echo 'User-Name = test1' | radclient -x 192.168.100.35:3799 disconnect ''1234''
    Sent Disconnect-Request Id 28 from 0.0.0.0:35685 to 192.168.100.35:3799 length 27
    User-Name = "test1"
    Received Disconnect-ACK Id 28 from 192.168.100.35:3799 to 0.0.0.0:0 length 30
    NAS-Identifier = "MikroTik"
    ```
    ***Enabling incoming messages in Mikrotik NAS***

  20. nasirhafeez revised this gist Jun 3, 2020. 1 changed file with 66 additions and 75 deletions.
    141 changes: 66 additions & 75 deletions file.md
    View file
    Original file line number Diff line number Diff line change
    @@ -34,69 +34,58 @@ The Expiration attribute is entered in `radcheck` table as follows:
    This means that this username will expire on 25 Apr 2020 at 10:42:00.
    Session-Timeout is given to user based on this.

    Note: expiration keyword needs to be present in authorize section in
    /etc/freeradius/3.0/sites-enabled/default (it is present by default in
    Note: `expiration` keyword needs to be present in authorize section in
    `/etc/freeradius/3.0/sites-enabled/default` (it is present by default in
    FreeRADIUS 3.x).

    # Changing IP Pool for Expired Username

    Add the following code in authorize section of
    /etc/freeradius/3.0/sites-enabled/default:

    > expiration{
    >
    > userlock = 1
    >
    > }
    >
    > if(userlock){
    >
    > \# Let him connect with EXPIRED pool in reply
    >
    > ok
    >
    > update reply {
    >
    > Reply-Message := "Your account has expired, %{User-Name} / Reason:
    > DATE LIMIT REACHED"
    >
    > Framed-Pool := "Expired"
    >
    > }
    >
    > }
    `/etc/freeradius/3.0/sites-enabled/default`:
    ```
    expiration{
    userlock = 1
    }
    if(userlock){
    # Let him connect with EXPIRED pool in reply
    ok
    update reply {
    Reply-Message := "Your account has expired, %{User-Name} / Reason: DATE LIMIT REACHED"
    Framed-Pool := "Expired"
    }
    }
    ```

    Testing results:

    ![C:\\Users\\Nasir\\Downloads\\Account Expired.png](./media/image3.png)

    ![C:\\Users\\Nasir\\Downloads\\Expired Pool IP.png](./media/image4.png)
    <img width="689" alt="image3" src="https://user-images.githubusercontent.com/38311694/83622543-940a9900-a5a9-11ea-83f1-f84c64ff1c08.png">
    <img width="878" alt="image4" src="https://user-images.githubusercontent.com/38311694/83622579-9d940100-a5a9-11ea-960c-3489f6a6bd4a.png">

    # Expiration After Certain Connection Time

    Suppose a user has paid for a certain amount of time and their username
    needs to be expired after that much time online. We can use the
    Expire-After attribute for that:
    `Expire-After` attribute for that:

    ![](./media/image5.png)
    <img width="355" alt="image5" src="https://user-images.githubusercontent.com/38311694/83622591-a1c01e80-a5a9-11ea-8d45-d6f92ab0aa04.png">

    # Wrong Password Notification

    Enter the following code in Post-Auth-Type REJECT section of
    /etc/freeradius/3.0/sites-enabled/default:

    Enter the following code in `Post-Auth-Type REJECT` section of
    `/etc/freeradius/3.0/sites-enabled/default`:
    ```
    update reply {
    Reply-Message = 'Wrong Password'
    }

    ![](./media/image6.png)
    ```
    <img width="491" alt="image6" src="https://user-images.githubusercontent.com/38311694/83622614-aab0f000-a5a9-11ea-89bd-76f60f2c7e17.png">

    # User MAC Binding

    A new table is created to store username to MAC address bindings:

    ```
    CREATE TABLE IF NOT EXISTS \`macs\` (
    \`id\` int unsigned NOT NULL AUTO\_INCREMENT,
    @@ -110,12 +99,13 @@ PRIMARY KEY (\`id\`)
    ) ENGINE=InnoDB AUTO\_INCREMENT=7 DEFAULT CHARSET=utf8mb4
    COLLATE=utf8mb4\_unicode\_ci;
    ```

    The following code is added to authorize section for MAC addition and
    authorization. When a username is initially created it has the MAC
    address set to “na”. Upon first connection the MAC address is saved in
    macs table, and subsequently it is checked.

    address set to `na`. Upon first connection the MAC address is saved in
    `macs` table, and subsequently it is checked.
    ```
    update control{
    \#check if callingstationid does not exist in mac\_limit Table
    @@ -154,11 +144,11 @@ Reply-Message += "MAC auth Failed"
    reject
    }

    ```
    # User Binding with NAS

    A new table is created to store huntgroups:

    ```
    CREATE TABLE radhuntgroup (
    id int(11) unsigned NOT NULL auto\_increment,
    @@ -174,21 +164,21 @@ PRIMARY KEY (id),
    KEY nasipaddress (nasipaddress)
    ) ;

    ```
    NAS IP addresses are assigned to various huntgroups:

    ![](./media/image7.png)
    <img width="253" alt="image7" src="https://user-images.githubusercontent.com/38311694/83622621-ac7ab380-a5a9-11ea-8b48-bea96048c130.png">

    Users are assigned to usergroups:

    ![](./media/image8.png)
    <img width="245" alt="image8" src="https://user-images.githubusercontent.com/38311694/83622623-ad134a00-a5a9-11ea-8ceb-c0652cc403f1.png">

    The following code is added in authorize section after preprocess. This
    code adds the Huntgroup-Name attribute to RADIUS Access Request. Then,
    it allows users of usergroup hg1users to only be connected on NASs
    belonging to hg1. Similarly, users of usergroup usergroup2 are only
    allowed to be connected to NASs belonging to hg2.

    it allows users of usergroup `hg1users` to only be connected on NASs
    belonging to `hg1`. Similarly, users of usergroup `usergroup2` are only
    allowed to be connected to NASs belonging to `hg2`.
    ```
    update request{
    Huntgroup-Name := "%{sql:SELECT groupname FROM radhuntgroup WHERE
    @@ -243,7 +233,7 @@ reject
    }
    }

    ```
    [Reference](https://wiki.freeradius.org/guide/SQL-Huntgroup-HOWTO)

    # Restrict Service Type
    @@ -257,7 +247,7 @@ Service-Type:

    And we want to reject all other requests. The following code should be
    added to authorize section:

    ```
    \#Only allow service types "Framed-User" or "Login-User"; reject all
    others
    @@ -279,12 +269,12 @@ Reply-Message += "Wrong service type"
    reject
    }

    ```
    # SQLCounter

    ***Time Based Quota***

    We can use Session-Timeout attribute to limit session time of a user.
    We can use `Session-Timeout` attribute to limit session time of a user.
    For example, if we wanted to limit a user to only 30 minutes of network
    access daily we could set Session-Timeout to 1800 (1800 s = 30 min).
    This will ensure that the user automatically gets disconnected after 30
    @@ -293,9 +283,9 @@ again to get 30 more minutes. To solve this problem, FreeRADIUS has some
    pre-defined counters that can be used to assign time-based session
    limits (like daily, monthly, etc).

    The file mods-enabled/sqlcounter contains several default counters. This
    The file `mods-enabled/sqlcounter` contains several default counters. This
    is the configuration of daily counter:

    ```
    sqlcounter dailycounter {
    sql\_module\_instance = sql
    @@ -315,31 +305,31 @@ reset = daily
    $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
    }

    This counter checks for an internal attribute Max-Daily-Session and uses
    ```
    This counter checks for an internal attribute `Max-Daily-Session` and uses
    the session data in DB to calculate the remaining session time of the
    user for that day. It then adds that remaining Session-Timeout to RADIUS
    user for that day. It then adds that remaining `Session-Timeout` to RADIUS
    Access Accept packet.

    We need to add dailycounter to sites-available/default in authorize
    section. In DB add the Max-Daily-Session attribute to radcheck table:

    ![](./media/image9.png)
    We need to add `dailycounter` to `sites-available/default` in authorize
    section. In DB add the `Max-Daily-Session` attribute to radcheck table:

    Enable the sqlcounter module:
    <img width="354" alt="image9" src="https://user-images.githubusercontent.com/38311694/83622628-adabe080-a5a9-11ea-88bf-4af48a89f213.png">

    Enable the `sqlcounter` module:
    ```
    cd /etc/freeradius/3.0/mods-enabled

    ln -s ../mods-available/sqlcounter
    ```

    ***Volume Based Quota***

    We can use sqlcounter module to query accounting data in DB and impose
    We can use `sqlcounter` module to query accounting data in DB and impose
    volume-based limits. Suppose we want to allow a user to have 25 MB of
    volume daily.

    In the file mods-enabled/sqlcounter create a new counter:

    In the file `mods-enabled/sqlcounter` create a new counter:
    ```
    sqlcounter total\_volume {
    sql\_module\_instance = sql
    @@ -358,12 +348,12 @@ query = "SELECT SUM(acctinputoctets) + SUM(acctoutputoctets) FROM
    radacct WHERE UserName='%{${key}}'"
    }
    ```
    Register the counter `total\_volume` in authorize section in
    `sites-available/default`. Add the `Max-Volume` attribute for that user in
    bytes in `radcheck` table:

    Register the counter total\_volume in authorize section in
    sites-available/default. Add the Max-Volume attribute for that user in
    bytes in radcheck table:

    ![](./media/image10.png)
    <img width="351" alt="image10" src="https://user-images.githubusercontent.com/38311694/83622631-aedd0d80-a5a9-11ea-8325-206ba5c069b3.png">

    Once the user has exceeded their volume limit they will not be
    authorized on their next connection attempt. Please note that users who
    @@ -377,7 +367,7 @@ performs some action on them (like disconnection, rate-limiting etc).

    To disconnect a user from RADIUS we use the user’s username and IP
    address and secret of NAS in radclient to disconnect that user:

    ```
    root@ubuntu:\~\# echo 'User-Name = test1' | radclient -x
    192.168.100.35:3799 disconnect ''1234''
    @@ -390,7 +380,8 @@ Received Disconnect-ACK Id 28 from 192.168.100.35:3799 to 0.0.0.0:0
    length 30
    NAS-Identifier = "MikroTik"

    ```
    ***Enabling incoming messages in Mikrotik NAS***

    ![](./media/image11.png)
    <img width="485" alt="image11" src="https://user-images.githubusercontent.com/38311694/83622636-af75a400-a5a9-11ea-859b-0362c7196d96.png">

  21. nasirhafeez revised this gist Jun 3, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion file.md
    View file
    Original file line number Diff line number Diff line change
    @@ -29,7 +29,7 @@ FreeRADIUS use cases.

    The Expiration attribute is entered in `radcheck` table as follows:


    <img width="611" alt="image2" src="https://user-images.githubusercontent.com/38311694/83622383-5443b180-a5a9-11ea-985f-d7e0c2ac6e99.png">

    This means that this username will expire on 25 Apr 2020 at 10:42:00.
    Session-Timeout is given to user based on this.
  22. nasirhafeez revised this gist Jun 3, 2020. 1 changed file with 1 addition and 3 deletions.
    4 changes: 1 addition & 3 deletions file.md
    View file
    Original file line number Diff line number Diff line change
    @@ -27,11 +27,9 @@ FreeRADIUS use cases.

    # Expiration

    ##
    The Expiration attribute is entered in `radcheck` table as follows:

    The Expiration attribute is entered in radcheck table as follows:

    ![](./media/image2.png)

    This means that this username will expire on 25 Apr 2020 at 10:42:00.
    Session-Timeout is given to user based on this.
  23. nasirhafeez revised this gist Jun 3, 2020. 1 changed file with 10 additions and 21 deletions.
    31 changes: 10 additions & 21 deletions file.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,35 +1,24 @@
    ![](./media/image1.png)

    | Version | Author | Date | Description |
    | ------- | ------------ | ----------- | ---------------------------------------------- |
    | 1.0 | Nasir Hafeez | 25 Apr 2020 | Initial Version |
    | 1.1 | Nasir Hafeez | 29 Apr 2020 | Added section “Restrict Service Type |
    | 1.2 | Nasir Hafeez | 30 Apr 2020 | Added section “SQLCounter” |
    | 1.3 | Nasir Hafeez | 6 May 2020 | Added section “User Disconnection from RADIUS” |

    # Contents

    [Introduction 4](#introduction)
    [Introduction](#introduction)

    [Expiration 4](#expiration)
    [Expiration](#expiration)

    [Changing IP Pool for Expired Username
    4](#changing-ip-pool-for-expired-username)
    [Changing IP Pool for Expired Username](#changing-ip-pool-for-expired-username)

    [Expiration After Certain Connection Time
    5](#expiration-after-certain-connection-time)
    [Expiration After Certain Connection Time](#expiration-after-certain-connection-time)

    [Wrong Password Notification 6](#wrong-password-notification)
    [Wrong Password Notification](#wrong-password-notification)

    [User MAC Binding 6](#user-mac-binding)
    [User MAC Binding](#user-mac-binding)

    [User Binding with NAS 8](#user-binding-with-nas)
    [User Binding with NAS](#user-binding-with-nas)

    [Restrict Service Type 10](#restrict-service-type)
    [Restrict Service Type](#restrict-service-type)

    [SQLCounter 11](#sqlcounter)
    [SQLCounter](#sqlcounter)

    [User Disconnection from RADIUS 14](#user-disconnection-from-radius)
    [User Disconnection from RADIUS](#user-disconnection-from-radius)

    # Introduction

  24. nasirhafeez created this gist Jun 3, 2020.
    409 changes: 409 additions & 0 deletions file.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,409 @@
    ![](./media/image1.png)

    | Version | Author | Date | Description |
    | ------- | ------------ | ----------- | ---------------------------------------------- |
    | 1.0 | Nasir Hafeez | 25 Apr 2020 | Initial Version |
    | 1.1 | Nasir Hafeez | 29 Apr 2020 | Added section “Restrict Service Type |
    | 1.2 | Nasir Hafeez | 30 Apr 2020 | Added section “SQLCounter” |
    | 1.3 | Nasir Hafeez | 6 May 2020 | Added section “User Disconnection from RADIUS” |

    # Contents

    [Introduction 4](#introduction)

    [Expiration 4](#expiration)

    [Changing IP Pool for Expired Username
    4](#changing-ip-pool-for-expired-username)

    [Expiration After Certain Connection Time
    5](#expiration-after-certain-connection-time)

    [Wrong Password Notification 6](#wrong-password-notification)

    [User MAC Binding 6](#user-mac-binding)

    [User Binding with NAS 8](#user-binding-with-nas)

    [Restrict Service Type 10](#restrict-service-type)

    [SQLCounter 11](#sqlcounter)

    [User Disconnection from RADIUS 14](#user-disconnection-from-radius)

    # Introduction

    This document presents the configurations related to some advanced
    FreeRADIUS use cases.

    # Expiration

    ##

    The Expiration attribute is entered in radcheck table as follows:

    ![](./media/image2.png)

    This means that this username will expire on 25 Apr 2020 at 10:42:00.
    Session-Timeout is given to user based on this.

    Note: expiration keyword needs to be present in authorize section in
    /etc/freeradius/3.0/sites-enabled/default (it is present by default in
    FreeRADIUS 3.x).

    # Changing IP Pool for Expired Username

    Add the following code in authorize section of
    /etc/freeradius/3.0/sites-enabled/default:

    > expiration{
    >
    > userlock = 1
    >
    > }
    >
    > if(userlock){
    >
    > \# Let him connect with EXPIRED pool in reply
    >
    > ok
    >
    > update reply {
    >
    > Reply-Message := "Your account has expired, %{User-Name} / Reason:
    > DATE LIMIT REACHED"
    >
    > Framed-Pool := "Expired"
    >
    > }
    >
    > }
    Testing results:

    ![C:\\Users\\Nasir\\Downloads\\Account Expired.png](./media/image3.png)

    ![C:\\Users\\Nasir\\Downloads\\Expired Pool IP.png](./media/image4.png)

    # Expiration After Certain Connection Time

    Suppose a user has paid for a certain amount of time and their username
    needs to be expired after that much time online. We can use the
    Expire-After attribute for that:

    ![](./media/image5.png)

    # Wrong Password Notification

    Enter the following code in Post-Auth-Type REJECT section of
    /etc/freeradius/3.0/sites-enabled/default:

    update reply {

    Reply-Message = 'Wrong Password'

    }

    ![](./media/image6.png)

    # User MAC Binding

    A new table is created to store username to MAC address bindings:

    CREATE TABLE IF NOT EXISTS \`macs\` (

    \`id\` int unsigned NOT NULL AUTO\_INCREMENT,

    \`username\` varchar(100) COLLATE utf8mb4\_unicode\_ci NOT NULL,

    \`callingstationid\` varchar(50) COLLATE utf8mb4\_unicode\_ci NOT NULL
    DEFAULT 'na',

    PRIMARY KEY (\`id\`)

    ) ENGINE=InnoDB AUTO\_INCREMENT=7 DEFAULT CHARSET=utf8mb4
    COLLATE=utf8mb4\_unicode\_ci;

    The following code is added to authorize section for MAC addition and
    authorization. When a username is initially created it has the MAC
    address set to “na”. Upon first connection the MAC address is saved in
    macs table, and subsequently it is checked.

    update control{

    \#check if callingstationid does not exist in mac\_limit Table

    Tmp-String-0 = "%{sql: SELECT callingstationid FROM macs WHERE username
    = '%{User-Name}'}"

    \#check if mac is available or not in mac\_limit table

    Tmp-Integer-2 = "%{sql: SELECT count(\*) FROM macs WHERE username =
    '%{User-Name}' AND callingstationid='%{Calling-Station-Id}'}"

    }

    if(\&control:Tmp-String-0 == "na"){

    "%{sql: UPDATE macs SET callingstationid = '%{Calling-Station-Id}' WHERE
    Username = '%{User-Name}'}"

    update reply {

    Reply-Message := "New mac Added"

    }

    }

    elsif(\&control:Tmp-Integer-2 == 0){

    update reply {

    Reply-Message += "MAC auth Failed"

    }

    reject

    }

    # User Binding with NAS

    A new table is created to store huntgroups:

    CREATE TABLE radhuntgroup (

    id int(11) unsigned NOT NULL auto\_increment,

    groupname varchar(64) NOT NULL default '',

    nasipaddress varchar(15) NOT NULL default '',

    nasportid varchar(15) default NULL,

    PRIMARY KEY (id),

    KEY nasipaddress (nasipaddress)

    ) ;

    NAS IP addresses are assigned to various huntgroups:

    ![](./media/image7.png)

    Users are assigned to usergroups:

    ![](./media/image8.png)

    The following code is added in authorize section after preprocess. This
    code adds the Huntgroup-Name attribute to RADIUS Access Request. Then,
    it allows users of usergroup hg1users to only be connected on NASs
    belonging to hg1. Similarly, users of usergroup usergroup2 are only
    allowed to be connected to NASs belonging to hg2.

    update request{

    Huntgroup-Name := "%{sql:SELECT groupname FROM radhuntgroup WHERE
    nasipaddress='%{NAS-IP-Address}'}"

    }

    \# bind hg1users usergroup to hg1 huntgroup

    if (SQL-Group == "hg1users") {

    if (Huntgroup-Name == "hg1") {

    \#ok

    }

    else {

    update reply {

    Reply-Message += "Error: User not allowed connection on this device"

    }

    reject

    }

    }

    \# bind hg2users usergroup to hg2 huntgroup

    elsif (SQL-Group == "hg2users") {

    if (Huntgroup-Name == "hg2") {

    \#ok

    }

    else {

    update reply {

    Reply-Message += "Error: User not allowed connection on this device"

    }

    reject

    }

    }

    [Reference](https://wiki.freeradius.org/guide/SQL-Huntgroup-HOWTO)

    # Restrict Service Type

    Suppose we only want to allow requests to RADIUS of the following
    Service-Type:

    1. Framed

    2. Login

    And we want to reject all other requests. The following code should be
    added to authorize section:

    \#Only allow service types "Framed-User" or "Login-User"; reject all
    others

    if((\&request:Service-Type=="Framed-User") ||
    (\&request:Service-Type=="Login-User")){

    \#ok

    }

    else{

    update reply {

    Reply-Message += "Wrong service type"

    }

    reject

    }

    # SQLCounter

    ***Time Based Quota***

    We can use Session-Timeout attribute to limit session time of a user.
    For example, if we wanted to limit a user to only 30 minutes of network
    access daily we could set Session-Timeout to 1800 (1800 s = 30 min).
    This will ensure that the user automatically gets disconnected after 30
    min. But the problem with this approach is that the user can connect
    again to get 30 more minutes. To solve this problem, FreeRADIUS has some
    pre-defined counters that can be used to assign time-based session
    limits (like daily, monthly, etc).

    The file mods-enabled/sqlcounter contains several default counters. This
    is the configuration of daily counter:

    sqlcounter dailycounter {

    sql\_module\_instance = sql

    dialect = mysql

    counter\_name = Daily-Session-Time

    check\_name = Max-Daily-Session

    reply\_name = Session-Timeout

    key = User-Name

    reset = daily

    $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf

    }

    This counter checks for an internal attribute Max-Daily-Session and uses
    the session data in DB to calculate the remaining session time of the
    user for that day. It then adds that remaining Session-Timeout to RADIUS
    Access Accept packet.

    We need to add dailycounter to sites-available/default in authorize
    section. In DB add the Max-Daily-Session attribute to radcheck table:

    ![](./media/image9.png)

    Enable the sqlcounter module:

    cd /etc/freeradius/3.0/mods-enabled

    ln -s ../mods-available/sqlcounter

    ***Volume Based Quota***

    We can use sqlcounter module to query accounting data in DB and impose
    volume-based limits. Suppose we want to allow a user to have 25 MB of
    volume daily.

    In the file mods-enabled/sqlcounter create a new counter:

    sqlcounter total\_volume {

    sql\_module\_instance = sql

    dialect = sql

    counter\_name = Max-Total-Volume

    check\_name = Max-Volume

    key = User-Name

    reset = daily

    query = "SELECT SUM(acctinputoctets) + SUM(acctoutputoctets) FROM
    radacct WHERE UserName='%{${key}}'"

    }

    Register the counter total\_volume in authorize section in
    sites-available/default. Add the Max-Volume attribute for that user in
    bytes in radcheck table:

    ![](./media/image10.png)

    Once the user has exceeded their volume limit they will not be
    authorized on their next connection attempt. Please note that users who
    are already connected will not be affected by this restriction. To
    perform any action on such already-connected sessions that have gone
    over their volume limit some script can be written that periodically
    queries the DB and if it finds some user has used up their data limit it
    performs some action on them (like disconnection, rate-limiting etc).

    # User Disconnection from RADIUS

    To disconnect a user from RADIUS we use the user’s username and IP
    address and secret of NAS in radclient to disconnect that user:

    root@ubuntu:\~\# echo 'User-Name = test1' | radclient -x
    192.168.100.35:3799 disconnect ''1234''

    Sent Disconnect-Request Id 28 from 0.0.0.0:35685 to 192.168.100.35:3799
    length 27

    User-Name = "test1"

    Received Disconnect-ACK Id 28 from 192.168.100.35:3799 to 0.0.0.0:0
    length 30

    NAS-Identifier = "MikroTik"

    ***Enabling incoming messages in Mikrotik NAS***

    ![](./media/image11.png)