PHPNuxBill with FreeRADIUS

To install PHPNuxBill in an Ubuntu server click here

Install FreeRADIUS

apt-get install freeradius freeradius-mysql freeradius-utils

Enable the SQL Module

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

Configure the SQL Module

Edit /etc/freeradius/3.0/mods-available/sql — find and set these values (uncomment if commented):

sql {
    driver = "rlm_sql_mysql"
    dialect = "mysql"

    # Connection info
    server = "localhost"
    port = 3306
    login = "phpnuxbill"
    password = "phpnuxbill"

    # Database name
    radius_db = "phpnuxbill"

    # Read NAS clients from the database nas table
    read_clients = yes
    client_table = "nas"

    # Uncomment to enable
    pool {
        start = 5
        min = 4
        max = 10
        spare = 3
        uses = 0
        lifetime = 0
        idle_timeout = 60
    }
}

Also find and add/uncomment the TLS block to prevent SSL errors:

mysql {
    tls {
        #ca_file = "/etc/ssl/certs/ca.crt"
        #tls_required = no
    }
    warnings = auto
}

Compete /etc/freeradius/3.0/mods-available/sql:

sql {
        dialect = "mysql"

        driver = "rlm_sql_${dialect}"

        mysql {
                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
                }
                warnings = auto
        }

        server = "localhost"
        port = 3306
        login = "phpnuxbill"
        password = "phpnuxbill"

        radius_db = "phpnuxbill"

        acct_table1 = "radacct"
        acct_table2 = "radacct"

        postauth_table = "radpostauth"

        authcheck_table = "radcheck"
        groupcheck_table = "radgroupcheck"

        authreply_table = "radreply"
        groupreply_table = "radgroupreply"

        usergroup_table = "radusergroup"

        read_groups = yes

        read_profiles = yes

        delete_stale_sessions = yes

        #logfile = ${logdir}/sqllog.sql

        #query_timeout = 5

        pool {
                start = ${thread[pool].start_servers}
                min = ${thread[pool].min_spare_servers}
                max = ${thread[pool].max_servers}
                spare = ${thread[pool].max_spare_servers}
                uses = 0
                retry_delay = 30
                lifetime = 0
                idle_timeout = 60
        }

        read_clients = yes

        client_table = "nas"

        group_attribute = "SQL-Group"

        $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf
}

Enable sqlcounter Module

ln -s /etc/freeradius/3.0/mods-available/sqlcounter /etc/freeradius/3.0/mods-enabled/sqlcounter

Edit /etc/freeradius/3.0/mods-available/sqlcounter and add these three counters:

sqlcounter accessperiod {
    sql_module_instance = sql
    dialect = ${modules.sql.dialect}
    counter_name = Max-Access-Period-Time
    check_name = Access-Period
    key = User-Name
    reset = never
    $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

sqlcounter quotalimit {
    sql_module_instance = sql
    dialect = ${modules.sql.dialect}
    counter_name = Max-Volume
    check_name = Max-Data
    reply_name = Mikrotik-Total-Limit
    key = User-Name
    reset = never
    $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

sqlcounter uptimelimit {
    counter_name = 'Max-All-Session-Time'
    check_name = 'Max-All-Session'
    sql_module_instance = sql
    key = 'User-Name'
    reset = never
    query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{${key}}'"
}

Complete /etc/freeradius/3.0/mods-available/sqlcounter file:

sqlcounter dailycounter {
        sql_module_instance = sql
        dialect = ${modules.sql.dialect}
        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
}

sqlcounter weeklycounter {
        sql_module_instance = sql
        dialect = ${modules.sql.dialect}
        counter_name = Weekly-Session-Time
        check_name = Max-Weekly-Session
        reply_name = Session-Timeout
        key = User-Name
        reset = weekly
        $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

sqlcounter monthlycounter {
        sql_module_instance = sql
        dialect = ${modules.sql.dialect}
        counter_name = Monthly-Session-Time
        check_name = Max-Monthly-Session
        reply_name = Session-Timeout
        key = User-Name
        reset = monthly
        $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

sqlcounter noresetcounter {
        sql_module_instance = sql
        dialect = ${modules.sql.dialect}
        counter_name = Max-All-Session-Time
        check_name = Max-All-Session
        key = User-Name
        reset = never
        $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

sqlcounter expire_on_login {
        sql_module_instance = sql
        dialect = ${modules.sql.dialect}
        counter_name = Expire-After-Initial-Login
        check_name = Expire-After
        key = User-Name
        reset = never
        $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

sqlcounter accessperiod {
        sql_module_instance = sql
        dialect = ${modules.sql.dialect}
        counter_name = Max-Access-Period-Time
        check_name = Access-Period
        key = User-Name
        reset = never
        $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

sqlcounter quotalimit {
        sql_module_instance = sql
        dialect = ${modules.sql.dialect}
        counter_name = Max-Volume
        check_name = Max-Data
        reply_name = Mikrotik-Total-Limit
        key = User-Name
        reset = never
        $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf
}

sqlcounter uptimelimit {
        sql_module_instance = sql
        dialect = ${modules.sql.dialect}
        counter_name = Max-All-Session-Time
        check_name = Max-All-Session
        key = User-Name
        reset = never
        query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{${key}}'"
}

Create Counter Query Files

Create /etc/freeradius/3.0/mods-config/sql/counter/mysql/accessperiod.conf:

query = "\
SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(AcctStartTime) \
FROM radacct \
WHERE UserName='%{${key}}' \
ORDER BY AcctStartTime LIMIT 1"

Create /etc/freeradius/3.0/mods-config/sql/counter/mysql/quotalimit.conf:

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

Configure sites-enabled/default

Edit /etc/freeradius/3.0/sites-enabled/default. Find each section and add/uncomment sql — remove the files directive from each:

authorize {
    filter_username
    preprocess
    sql                      # <-- add this, remove "files"
    expiration
    quotalimit
    accessperiod
    uptimelimit

    if (User-Name) {
        if ("%{sql:UPDATE radacct SET AcctStopTime=ADDDATE(AcctStartTime,INTERVAL AcctSessionTime SECOND), AcctTerminateCause='Clear-Stale Session' WHERE UserName='%{User-Name}' AND CallingStationId='%{Calling-Station-Id}' AND AcctStopTime IS NULL}") {
        }
    }

    expiration {
        userlock = 1
    }
    if (userlock) {
        ok
        update reply {
            Reply-Message := "Your account has expired"
            Framed-Pool := "Expired"
        }
    }
}

authenticate {
    Auth-Type PAP {
        pap
    }
    Auth-Type CHAP {
        chap
    }
    Auth-Type MS-CHAP {
        mschap
    }
}

accounting {
    sql                      # <-- add this, remove "files"
    exec
}

session {
    sql                      # <-- add this
}

post-auth {
    sql                      # <-- add this
    exec
}

Complete /etc/freeradius/3.0/sites-enabled/default file:

server default {

listen {
        type = auth
        ipaddr = *
        port = 0
        limit {
              max_connections = 16
              lifetime = 0
              idle_timeout = 30
        }
}

listen {
        ipaddr = *
        port = 0
        type = acct
        limit {
        }
}

listen {
        type = auth
        ipv6addr = ::
        port = 0
        limit {
              max_connections = 16
              lifetime = 0
              idle_timeout = 30
        }
}

listen {
        ipv6addr = ::
        port = 0
        type = acct
        limit {
        }
}

authorize {
        filter_username
        preprocess
        chap
        mschap
        digest
        suffix
        eap {
                ok = return
        }

        #
        #  Look in SQL database for this user.
        #
        sql

        expiration
        logintime
        pap

        #
        #  PHPNuxBill counter checks
        #
        quotalimit
        accessperiod
        uptimelimit

        #
        #  Clear any stale sessions (e.g. after router reboot)
        #
        if (User-Name) {
                if ("%{sql:UPDATE radacct SET AcctStopTime=ADDDATE(AcctStartTime,INTERVAL AcctSessionTime SECOND), AcctTerminateCause='Clear-Stale Session' WHERE UserName='%{User-Name}' AND CallingStationId='%{Calling-Station-Id}' AND AcctStopTime IS NULL}") {
                }
        }

        #
        #  If account has expired, send back a rejection message
        #
        expiration {
                userlock = 1
        }
        if (userlock) {
                ok
                update reply {
                        Reply-Message := "Your account has expired"
                        Framed-Pool := "Expired"
                }
        }

        Autz-Type New-TLS-Connection {
                ok
        }
}

authenticate {
        Auth-Type PAP {
                pap
        }
        Auth-Type CHAP {
                chap
        }
        Auth-Type MS-CHAP {
                mschap
        }
        mschap
        digest
        eap
}

preacct {
        preprocess
        acct_unique
        suffix
        files
}

accounting {
        detail
        unix

        #
        #  Log accounting data to SQL (writes to radacct table)
        #
        sql

        exec
        attr_filter.accounting_response
}

session {
        #
        #  Use SQL for simultaneous-use checking
        #
        sql
}

post-auth {
        if (session-state:User-Name && reply:User-Name && request:User-Name && (reply:User-Name == request:User-Name)) {
                update reply {
                        &User-Name !* ANY
                }
        }
        update {
                &reply: += &session-state:
        }

        #
        #  Write authentication log to SQL (writes to radpostauth table)
        #  and return reply attributes from radreply table to MikroTik
        #  (e.g. Mikrotik-Rate-Limit, Session-Timeout, Expiration)
        #
        sql

        exec
        remove_reply_message_if_eap

        Post-Auth-Type REJECT {
                sql
                attr_filter.access_reject
                eap
                remove_reply_message_if_eap
        }

        Post-Auth-Type Challenge {
        }

        Post-Auth-Type Client-Lost {
        }

        if (EAP-Key-Name && &reply:EAP-Session-Id) {
                update reply {
                        &EAP-Key-Name := &reply:EAP-Session-Id
                }
        }
}

pre-proxy {
}

post-proxy {
        eap
}

}

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *


Ask a question
Ask a question