Trzepak.pl


Nowy temat  Odpowiedz w temacie
Autor Wiadomość
Post: śr, 04 lip 2018 19:07:52 
Odpowiedz z cytatem
Offline
Żółtodziób
Żółtodziób

Rejestracja: sob, 27 sie 2016 15:17:18
Posty: 11
Witam, czy ma ktoś poprawna konfiguracje u siebie LMS + freeradius w wersji 3.0 który ładnie gada z MT ?

Mam zrobiona starsza wersje freeradiusa i dziala bez zarzutu, ale nowa konfiguracja nie chce mi zadzialac

Jezeli ma ktos mozliwosc podzielic sie swoim doswiadczeniem z mila checia zaplace za to.


Na górę
Post: czw, 05 lip 2018 0:32:48 
Odpowiedz z cytatem
Offline
Fanatyczny prenumerator
Fanatyczny prenumerator
Awatar użytkownika

Rejestracja: sob, 05 cze 2010 17:21:37
Posty: 481
Pokaż debugowanie radiusa w trakcie logowania, chcesz używać radiusa do pppoe, DHCP?


Na górę
Post: ndz, 08 lip 2018 13:23:29 
Odpowiedz z cytatem
Offline
Żółtodziób
Żółtodziób

Rejestracja: sob, 27 sie 2016 15:17:18
Posty: 11
Dzięki za zainteresowanie moim problemem, to może wrzucę po kolei moje pliki, odrazu proszę o przeanalizowanie ich czy są zgodne z funkcjonalnością... a zarazem pewnie pomogą komuś w przyszłości.

pierw mój plik sql w: /etc/freeradius/3.0/mods-enabled

Kod:
sql sql_nodesessions {
        driver = "rlm_sql_mysql"
        server = "localhost"
        login = "lms"
        password = "HASLO"
        radius_db = "lms"
        delete_stale_sessions = yes
        sqltrace = no
        sqltracefile = ${logdir}/sqltrace.sql
        num_sql_socks = 15
        connect_failure_retry_delay = 60
        sql_user_name = "%{User-Name}"

        accounting_stop_query = " \
                INSERT INTO nodesessions ( nodeid, ipaddr, mac, start, stop, download, upload) \
                SELECT  nodeid, inet_aton('%{Framed-IP-Address}'), mac, unix_timestamp(DATE_SUB('%S', INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND)), \
                        unix_timestamp('%S'), '%{Acct-Output-Octets}', '%{Acct-Input-Octets}' from macs where mac = upper('%{Calling-Station-Id}')"
}

sql sql_acc {
        driver = "rlm_sql_mysql"
        server = "localhost"
        login = "lms"
        password = "HASLO"
        radius_db = "lms"
        acct_table1 = "radacct"
        acct_table2 = "radacct"
        nas_table = "nas"
        delete_stale_sessions = yes
        sqltrace = no
        sqltracefile = ${logdir}/sqltrace.sql
        num_sql_socks = 15
        connect_failure_retry_delay = 60
        #safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
        sql_user_name = "%{User-Name}"

        accounting_onoff_query = " \
                UPDATE ${acct_table1} \
                SET     acctstoptime            = '%S', \
                        acctsessiontime         = unix_timestamp('%S') - unix_timestamp(AcctStartTime), \
                        acctterminatecause      = '%{Acct-Terminate-Cause}', \
                        acctstopdelay           = '%{Acct-Delay-Time}' \
                WHERE   acctsessionTime         = 0 \
                AND     acctstoptime            = 0 \
                AND     nasipaddress            = '%{NAS-IP-Address}' AND AcctStartTime <= '%S'"

        accounting_update_query = " \
                UPDATE ${acct_table1} \
                SET     framedipaddress         = '%{Framed-IP-Address}', \
                        acctsessiontime         = '%{Acct-Session-Time}', \
                        acctinputoctets         = '%{Acct-Input-Octets}', \
                        acctoutputoctets        = '%{Acct-Output-Octets}' \
                WHERE   acctsessionid           = '%{Acct-Session-Id}' \
                AND     username                = '%{SQL-User-Name}' \
                AND     nasipaddress            = '%{NAS-IP-Address}'"

        accounting_update_query_alt = " \
                INSERT into ${acct_table1} \
                        (acctsessionid,         acctuniqueid,           username, \
                        realm,                  nasipaddress,           nasportid, \
                        nasporttype,            acctstarttime,          acctsessiontime, \
                        acctauthentic,          connectinfo_start,      acctinputoctets, \
                        acctoutputoctets,       calledstationid,        callingstationid, \
                        servicetype,            framedprotocol,         framedipaddress, \
                        acctstartdelay) \
                VALUES \
                        ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', \
                        '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
                        '%{NAS-Port-Type}', DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND), '%{Acct-Session-Time}', \
                        '%{Acct-Authentic}', '', '%{Acct-Input-Octets}', \
                        '%{Acct-Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station-Id}', \
                        '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
                        '0')"

        accounting_start_query = " \
                INSERT into ${acct_table1} \
                        (acctsessionid,         acctuniqueid,           username, \
                        realm,                  nasipaddress,           nasportid, \
                        nasporttype,            acctstarttime,          acctstoptime, \
                        acctsessiontime,        acctauthentic,          connectinfo_start, \
                        connectinfo_stop,       acctinputoctets,        acctoutputoctets, \
                        calledstationid,        callingstationid,       acctterminatecause, \
                        servicetype,            framedprotocol,         framedipaddress, \
                        acctstartdelay,         acctstopdelay) \
                VALUES \
                        ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', \
                        '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
                        '%{NAS-Port-Type}', '%S', '0', \
                        '0', '%{Acct-Authentic}', '%{Connect-Info}', \
                        '', '0', '0', \
                        '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
                        '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
                        '%{Acct-Delay-Time}', '0')"

        accounting_start_query_alt  = " \
                UPDATE ${acct_table1} SET \
                        acctstarttime           = '%S', \
                        acctstartdelay          = '%{Acct-Delay-Time}', \
                        connectinfo_start       = '%{Connect-Info}' \
                WHERE   acctsessionid           = '%{Acct-Session-Id}' \
                AND     username                = '%{SQL-User-Name}' \
                AND     nasipaddress            = '%{NAS-IP-Address}'"

        accounting_stop_query = " \
                UPDATE ${acct_table2} SET \
                        acctstoptime            = '%S', \
                        acctsessiontime         = '%{Acct-Session-Time}', \
                        acctinputoctets         = '%{%{Acct-Input-Octets}:-0}', \
                        acctoutputoctets        = '%{%{Acct-Output-Octets}:-0}', \
                        acctterminatecause      = '%{Acct-Terminate-Cause}', \
                        acctstopdelay           = '%{%{Acct-Delay-Time}:-0}', \
                        connectinfo_stop        = '%{Connect-Info}' \
                WHERE acctsessionid             = '%{Acct-Session-Id}' \
                AND username                    = '%{SQL-User-Name}' \
                AND nasipaddress                = '%{NAS-IP-Address}';"

        accounting_stop_query_alt = \
                "INSERT into ${acct_table2} \
                        (acctsessionid,         acctuniqueid,           username, \
                        realm,                  nasipaddress,           nasportid, \
                        nasportype,             acctstarttime,          acctstoptime, \
                        acctsessiontime,        acctauthentic,          connectinfo_start, \
                        connectinfo_stop,       acctinputoctets,        acctoutputoctets, \
                        calledstationid,        callingstationid,       acctterminatecause, \
                        servicetype,            framedprotocol,         framedipaddress, \
                        acctstartdelay,         acctstopdelay) \
                VALUES \
                        ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', \
                        '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
                        '%{NAS-Port-Type}', DATE_SUB('%S', INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND), '%S', \
                        '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \
                        '%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', \
                        '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', \
                        '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
                        '0', '%{Acct-Delay-Time}')"

        # Uncomment simul_count_query to enable simultaneous use checking
        simul_verify_query = " \
                SELECT  radacctid,              acctsessionid,          username, \
                        nasipaddress,           nasportid,              framedipaddress, \
                        callingstationid,       framedprotocol \
                FROM    ${acct_table1} \
                WHERE   UserName                = '%{SQL-User-Name}' \
                AND     AcctStopTime            = 0"

}

sql sql_pppoe {
        driver = "rlm_sql_mysql"
        server = "localhost"
        login = "lms"
        password = "HASLO"
        radius_db = "lms"
        delete_stale_sessions = yes
        sqltrace = no
        sqltracefile = ${logdir}/sqltrace.sql
        num_sql_socks = 15
        connect_failure_retry_delay = 60
        sql_user_name = "%{User-Name}"
        sql_set_password = ""
        authreply_table = "radreply"
        nas_table = "nas"
        read_clients = no

   authorize_check_query = "SELECT \
            id, lower(name) as UserName , 'Cleartext-Password' as Attribute , passwd as Value, ':=' as op \
        FROM nodes \
        WHERE name = '%{User-Name}'\
        UNION \
        SELECT id, lower(name) as UserName , 'Simultaneous-Use' as Attribute, '1' as Value, ':=' as op \
        FROM nodes \
        WHERE name = '%{User-Name}'\
        UNION \
        SELECT 0 AS id, '%{User-Name}' AS UserName, 'Max-Octets' AS Attribute, \
            CONCAT(ROUND(COALESCE(x.dlimit, y.dlimit))) AS Value, ':=' AS op \
        FROM (\
            SELECT n.id, MIN(n.name) AS name, SUM(t.dlimit/o.cnt) AS dlimit \
            FROM nodeassignments na \
            JOIN assignments a ON (na.assignmentid = a.id) \
            JOIN tariffs t ON (a.tariffid = t.id)\
            JOIN nodes n ON (na.nodeid = n.id)\
            JOIN (\
                SELECT assignmentid, COUNT(*) AS cnt\
                FROM nodeassignments \
                GROUP BY assignmentid \
            ) o ON (o.assignmentid = na.assignmentid)\
            WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) \
                AND (a.dateto > unix_timestamp() OR a.dateto = 0) \
                AND a.suspended = 0 AND n.name = '%{User-Name}'\
            GROUP BY n.id\
        ) x\
        RIGHT JOIN ( \
            SELECT SUM(t.dlimit)/o.cnt AS dlimit\
            FROM assignments a\
            JOIN tariffs t ON (a.tariffid = t.id)\
            JOIN nodes n ON (a.customerid = n.ownerid)\
            JOIN ( \
                SELECT COUNT(*) AS cnt, ownerid \
                FROM nodes\
                WHERE NOT EXISTS (\
            SELECT 1 FROM nodeassignments, assignments a \
            WHERE assignmentid = a.id AND nodeid = nodes.id \
                        AND a.suspended = 0 AND (a.dateto > unix_timestamp() OR a.dateto = 0))\
                GROUP BY ownerid\
            ) o ON (o.ownerid = n.ownerid)\
            WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) \
                AND (a.dateto > unix_timestamp() OR a.dateto = 0) \
                AND a.suspended = 0 AND t.dlimit != '0'\
                AND NOT EXISTS (\
                    SELECT 1 FROM nodeassignments \
                    WHERE assignmentid = a.id)\
                AND n.name = '%{User-Name}'\
            GROUP BY n.id\
        ) y ON (1=1);"

    authorize_reply_query = "SELECT \
            id, lower(name) as UserName , 'Framed-IP-Address' as Attribute, inet_ntoa(ipaddr) as Value, '==' as op \
        FROM nodes \
        WHERE name = '%{User-Name}'\
        UNION\
       SELECT 0 AS id, '%{User-Name}' AS UserName, 'Mikrotik-Rate-Limit' AS Attribute,\
             CONCAT(ROUND(COALESCE(x.upceil, z.upceil)),'k','/', ROUND(COALESCE(x.downceil, z.downceil)),'k') AS Value, '==' AS op\
        FROM (\
            SELECT n.id, MIN(n.name) AS name, SUM(t.downceil/o.cnt) AS downceil, SUM(t.upceil/o.cnt) AS upceil\
            FROM nodeassignments na\
            JOIN assignments a ON (na.assignmentid = a.id)\
            JOIN tariffs t ON (a.tariffid = t.id)\
            JOIN nodes n ON (na.nodeid = n.id)\
            JOIN (\
                SELECT assignmentid, COUNT(*) AS cnt\
                FROM nodeassignments \
                GROUP BY assignmentid\
             ) o ON (o.assignmentid = na.assignmentid)\
            WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) \
                AND (a.dateto > unix_timestamp() OR a.dateto = 0) \
                AND a.suspended = 0 AND n.name = '%{User-Name}'\
            GROUP BY n.id\
        ) x\
        RIGHT JOIN (\
            SELECT n.id, n.name, 64 AS downceil, 64 AS upceil\
            FROM nodes n WHERE n.name = '%{User-Name}'\
        ) z ON (1=1);"
# z radreply
#        UNION\
#        SELECT id, UserName, Attribute, Value, op \
#        FROM ${authreply_table} \
#        WHERE Username = '%{SQL-User-Name}' \
#        ORDER BY id;"
}

sql sql_mac {
        driver = "rlm_sql_mysql"
        server = "localhost"
        login = "lms"
        password = "HASLO"
        radius_db = "lms"
        delete_stale_sessions = yes
        sqltrace = no
        sqltracefile = ${logdir}/sqltrace.sql
        num_sql_socks = 10
        connect_failure_retry_delay = 60
        sql_user_name = "%{User-Name}"
        sql_set_password = ""

#       authorize_check_query = "
#               SELECT nodes.id, upper(macs.mac) as UserName , 'Cleartext-Password' as Attribute, 'maccheck' as Value, ':=' as op \
#               FROM macs left join nodes on macs.nodeid=nodes.id \
#               WHERE nodes.access=1 and macs.mac='%{SQL-User-Name}' order by id"

        authorize_check_query = " \
                SELECT id, upper(mac) as UserName , 'User-Password' as Attribute, '' as Value, '==' as op \
                FROM nodes \
                WHERE upper(mac) = '%{SQL-User-Name}' and access=1 ORDER by id"
}

sql sql_last_online_mac {
        driver = "rlm_sql_mysql"
        server = "localhost"
        login = "lms"
        password = "HASLO"
        radius_db = "lms"
        postauth_table = "nodes"
        delete_stale_sessions = yes
        sqltrace = no
        sqltracefile = /etc/freeradius/sqltrace.sql
        num_sql_socks = 15
        connect_failure_retry_delay = 60
        sql_user_name = "%{User-Name}"
        sql_set_password = ""

        postauth_query = " \
                UPDATE nodes \
                SET     lastonline      = unix_timestamp() \
                WHERE   id              = (select nodeid from macs where mac='%{User-Name}')"
}

sql sql_last_online {
        driver = "rlm_sql_mysql"
        server = "localhost"
        login = "lms"
        password = "HASLO"
        radius_db = "lms"
        postauth_table = "nodes"
        delete_stale_sessions = yes
        sqltrace = no
        sqltracefile = /etc/freeradius/sqltrace.sql
        num_sql_socks = 15
        connect_failure_retry_delay = 60
        sql_user_name = "%{User-Name}"
        sql_set_password = ""

        postauth_query = " \
                UPDATE nodes INNER JOIN macs ON nodeid=nodes.id \
                SET     lastonline      = unix_timestamp() \
                WHERE   mac             = '%{Calling-Station-Id}' \
                OR      mac             = '%{User-Name}' \
                AND     nodeid          = nodes.id;"

        accounting_onoff_query =  " \
                UPDATE nodes INNER JOIN macs ON nodeid=nodes.id \
                SET     lastonline      = unix_timestamp() \
                WHERE   mac             = '%{Calling-Station-Id}' \
                OR      mac             = '%{User-Name}' \
                AND     nodeid=nodes.id;"

        accounting_update_query = "UPDATE nodes INNER JOIN macs ON nodeid=nodes.id \
                SET     lastonline      = unix_timestamp() \
                WHERE   mac             = '%{Calling-Station-Id}' \
                OR      mac             = '%{User-Name}' \
                AND     nodeid=nodes.id;"
}


Następnie mamy clients.conf
Kod:
client localhost {
        ipaddr                          = 127.0.0.1
#       ipv4addr                        = *     # any.  127.0.0.1 == localhost
#       ipv6addr                        = ::    # any.  ::1 == localhost
        proto                           = *
        secret                          = XXXXXXXXXX
        require_message_authenticator   = no
#       shortname                       = localhost
        nas_type                        = other # localhost isn't usually a NAS...
#       login                           = !root
#       password                        = someadminpas
#       virtual_server                  = home1
#       coa_server                      = coa
#       response_window                 = 10.0

        limit {
                max_connections         = 16
                lifetime                = 0
                idle_timeout            = 30
        }
}

client xxx.xxx.xxx.xxx {
        ipaddr                          = xxx.xxx.xxx.xxx
        secret                          = HASLO
}


nastepnie radiusd.conf

Kod:
prefix = /usr
exec_prefix = /usr
sysconfdir = /etc
localstatedir = /var
sbindir = ${exec_prefix}/sbin
logdir = /var/log/freeradius
raddbdir = /etc/freeradius/3.0
radacctdir = ${logdir}/radacct

name = freeradius

confdir = ${raddbdir}
modconfdir = ${confdir}/mods-config
certdir = ${confdir}/certs
cadir   = ${confdir}/certs
run_dir = ${localstatedir}/run/${name}

db_dir = ${raddbdir}

libdir = /usr/lib/freeradius
pidfile = ${run_dir}/${name}.pid

correct_escapes = true

# panic_action = "gdb %e %p"
# panic_action = "gdb -silent -x ${raddbdir}/panic.gdb %e %p 2>&1 | tee ${logdir}/gdb-${name}-%p.log"

max_request_time = 30
cleanup_delay = 5
max_requests = 16384

hostname_lookups = no

log {
        destination = files
        colourise = yes
        file = ${logdir}/radius.log
#       requests = ${logdir}/radiusd-%{%{Virtual-Server}:-DEFAULT}-%Y%m%d.log
        syslog_facility = daemon

        stripped_names = yes
        auth = yes
        auth_badpass = yes
        auth_goodpass = yes

#       msg_goodpass = ""
#       msg_badpass = ""

        msg_denied = "You are already logged in - access denied"
}

checkrad = ${sbindir}/checkrad

security {
#       chroot = /path/to/chroot/directory
        user = freerad
        group = freerad
        allow_core_dumps = no
        max_attributes = 200
        reject_delay = 1
        status_server = yes

}
proxy_requests  = yes
$INCLUDE proxy.conf

$INCLUDE clients.conf

thread pool {
        start_servers = 5
        max_servers = 32
        min_spare_servers = 3
        max_spare_servers = 10
#       max_queue_size = 65536
        max_requests_per_server = 0
        auto_limit_acct = no
}
# $INCLUDE trigger.conf

modules {
        $INCLUDE mods-enabled/
}

instantiate {
#       daily
}

policy {
        $INCLUDE policy.d/
}

$INCLUDE sites-enabled/


No i plik default w /etc/freeradius/3.0/sites-enabled
Kod:
server default {

listen {
        ipaddr = xxx.xxx.xxx.xxx
        port = XXXX
        type = auth
#       interface = eth0
#       clients = per_socket_clients
#       limit {
#               max_connections = 16
#               lifetime = 0
#               idle_timeout = 30
#       }
}

listen {
        ipaddr = xxx.xxx.xxx.xxx
        port = XXXX
        type = acct
#       interface = eth0
#       clients = per_socket_clients
#       limit {
#               max_pps = 0
#               idle_timeout = 0
#               lifetime = 0
#               max_connections = 0
#       }
}

authorize {
        suffix
        sql_pppoe
        expiration
        logintime
}

authenticate {
        Auth-Type CHAP {
        chap
        }
        Auth-Type MS-CHAP {
        mschap
        }
        digest
#       unix
        eap
}

preacct {
        preprocess
        files
}

accounting {
        detail
#       daily
        unix
        radutmp
        sradutmp
        sql_nodesessions
        sql_acc
        exec
        attr_filter.accounting_response
}

session {
        radutmp
#       sql
}

post-auth {
        sql_last_online
        exec
        Post-Auth-Type REJECT {
        attr_filter.access_reject
        }
}

post-proxy {
        eap
}

}



Ta konfiguracja działa mi na freeradius 2.0 (oczywiście przed zmiana...zmiennych) prawidłowo, tzn pppoe + queues do MT są ładnie wstrzykiwane, jedynie z czym miałem problem to ONLINE użytkownicy dlatego proszę o przeanalizowanie mojego pliku... zaś tutaj z freeradiusem 3.0 podmianka radiusa w MT w trakcie funkcjonowania widzę że ładnie uzupełnia wszystkie dane w bazie jedynie jest problem z połączeniem nowych userów.

Tak wyglądają logi:

Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_nodesessions): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_acc): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_pppoe): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_mac): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online_mac): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Sun Jul 8 13:15:20 2018 : Warning: [/etc/freeradius/3.0/mods-config/attr_filter/access_reject]:11 Check item "FreeRADIUS-Response-Delay" found in filter list for realm "DEFAULT".
Sun Jul 8 13:15:20 2018 : Warning: [/etc/freeradius/3.0/mods-config/attr_filter/access_reject]:11 Check item "FreeRADIUS-Response-Delay-USec" found in filter list for realm "DEFAULT".
Sun Jul 8 13:15:20 2018 : Info: rlm_sql_mysql: libmysql version: 10.1.26-MariaDB
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_nodesessions): Attempting to connect to database "lms"
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_nodesessions): Opening additional connection (0), 1 of 10 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_nodesessions): Opening additional connection (1), 1 of 9 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_nodesessions): Opening additional connection (2), 1 of 8 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_nodesessions): Opening additional connection (3), 1 of 7 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_nodesessions): Opening additional connection (4), 1 of 6 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_acc): Attempting to connect to database "lms"
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_acc): Opening additional connection (0), 1 of 10 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_acc): Opening additional connection (1), 1 of 9 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_acc): Opening additional connection (2), 1 of 8 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_acc): Opening additional connection (3), 1 of 7 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_acc): Opening additional connection (4), 1 of 6 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_pppoe): Attempting to connect to database "lms"
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_pppoe): Opening additional connection (0), 1 of 10 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_pppoe): Opening additional connection (1), 1 of 9 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_pppoe): Opening additional connection (2), 1 of 8 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_pppoe): Opening additional connection (3), 1 of 7 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_pppoe): Opening additional connection (4), 1 of 6 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_mac): Attempting to connect to database "lms"
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_mac): Opening additional connection (0), 1 of 10 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_mac): Opening additional connection (1), 1 of 9 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_mac): Opening additional connection (2), 1 of 8 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_mac): Opening additional connection (3), 1 of 7 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_mac): Opening additional connection (4), 1 of 6 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online_mac): Attempting to connect to database "lms"
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online_mac): Opening additional connection (0), 1 of 10 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online_mac): Opening additional connection (1), 1 of 9 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online_mac): Opening additional connection (2), 1 of 8 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online_mac): Opening additional connection (3), 1 of 7 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online_mac): Opening additional connection (4), 1 of 6 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online): Attempting to connect to database "lms"
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online): Opening additional connection (0), 1 of 10 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online): Opening additional connection (1), 1 of 9 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online): Opening additional connection (2), 1 of 8 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online): Opening additional connection (3), 1 of 7 pending slots used
Sun Jul 8 13:15:20 2018 : Info: rlm_sql (sql_last_online): Opening additional connection (4), 1 of 6 pending slots used
Sun Jul 8 13:15:20 2018 : Info: Loaded virtual server <default>
Sun Jul 8 13:15:20 2018 : Warning: Ignoring "sql" (see raddb/mods-available/README.rst)
Sun Jul 8 13:15:20 2018 : Warning: Ignoring "ldap" (see raddb/mods-available/README.rst)
Sun Jul 8 13:15:20 2018 : Info: Loaded virtual server inner-tunnel
Sun Jul 8 13:15:20 2018 : Info: Loaded virtual server default
Sun Jul 8 13:15:20 2018 : Info: Ready to process requests

Próba połączenia "orpz" usera:
Sun Jul 8 12:58:22 2018 : Info: rlm_sql (sql_pppoe): Opening additional connection (6), 1 of 9 pending slots used
Sun Jul 8 12:58:22 2018 : Auth: (533) Login incorrect (No Auth-Type found: rejecting the user via Post-Auth-Type = Reject): [orpz/<CHAP-Password>] (from client XXX.XXX.XXX.XXX port 15753630 cli A0:F3:C1:59:A6:89)
Sun Jul 8 12:59:00 2018 : Info: rlm_sql (sql_pppoe): Need 3 more connections to reach 3 spares
Sun Jul 8 12:59:00 2018 : Info: rlm_sql (sql_pppoe): Opening additional connection (7), 1 of 8 pending slots used
Sun Jul 8 12:59:00 2018 : Auth: (566) Login incorrect (No Auth-Type found: rejecting the user via Post-Auth-Type = Reject): [orpz/<CHAP-Password>] (from client XXX.XXX.XXX.XXX port 15753631 cli A0:F3:C1:59:A6:89)


Na górę
Post: wt, 10 lip 2018 9:59:07 
Odpowiedz z cytatem
Offline
Młodszy czytelnik
Młodszy czytelnik

Rejestracja: wt, 19 mar 2013 12:29:37
Posty: 58
Radius wysyła zapytania do SQL? Jeśli tak to podejrzyj sobie logi z SQL. Będziesz wtedy wiedział na 100% gdzie jest problem.
Jeśli nie wysyła to szukaj problemu w konfiguracji z SQL.


Na górę
Post: czw, 12 lip 2018 17:34:18 
Odpowiedz z cytatem
Offline
Żółtodziób
Żółtodziób

Rejestracja: sob, 27 sie 2016 15:17:18
Posty: 11
Hej, sql działa prawidłowo tak jak pisałem wyżej, nawet klienci którzy już są autoryzowani (innym serwerem radius) to ładnie aktualizuje na tym freeradius 3.0 w bazie mysql, jedynie jest problem z autoryzacja.. i nie wiem gdzie szukac problemu w tym 3.0, moj stary freeradius 2.0 dziala bez zarzutu...

Tak wygladaja logi w freeradius 3.0:

Kod:
Thu Jul 12 17:22:54 2018 : Auth: (684) Login incorrect (No Auth-Type found: rejecting the user via Post-Auth-Type = Reject): [jarzabek_pawel/<CHAP-Password>] (from client xxx.xxx.xxx.xxx port 15754875 cli 18:D0:71:F1:5C:A1)
Thu Jul 12 17:22:59 2018 : Auth: (687) Login incorrect (No Auth-Type found: rejecting the user via Post-Auth-Type = Reject): [jarzabek_pawel/<CHAP-Password>] (from client xxx.xxx.xxx.xxx port 15754876 cli 18:D0:71:F1:5C:A1)
Thu Jul 12 17:23:04 2018 : Auth: (691) Login incorrect (No Auth-Type found: rejecting the user via Post-Auth-Type = Reject): [jarzabek_pawel/<CHAP-Password>] (from client xxx.xxx.xxx.xxx port 15754877 cli 18:D0:71:F1:5C:A1)
Thu Jul 12 17:23:10 2018 : Auth: (694) Login incorrect (No Auth-Type found: rejecting the user via Post-Auth-Type = Reject): [jarzabek_pawel/<CHAP-Password>] (from client xxx.xxx.xxx.xxx port 15754878 cli 18:D0:71:F1:5C:A1)
Thu Jul 12 17:23:15 2018 : Auth: (696) Login incorrect (No Auth-Type found: rejecting the user via Post-Auth-Type = Reject): [jarzabek_pawel/<CHAP-Password>] (from client xxx.xxx.xxx.xxx port 15754879 cli 18:D0:71:F1:5C:A1)

a w mysql:
Kod:
    1153 Query   SELECT        id, lower(name) as UserName , 'Framed-IP-Address' as Attribute, inet_ntoa(ipaddr) as Value, '==' as op    FROM nodes    WHERE name = 'jarzabek_pawel'   UNION       SELECT 0 AS id, 'jarzabek_pawel' AS UserName, 'Mikrotik-Rate-Limit' AS Attribute,             CONCAT(ROUND(COALESCE(x.upceil, z.upceil)),'k','/', ROUND(COALESCE(x.downceil, z.downceil)),'k') AS Value, '==' AS op        FROM (           SELECT n.id, MIN(n.name) AS name, SUM(t.downceil/o.cnt) AS downceil, SUM(t.upceil/o.cnt) AS upceil            FROM nodeassignments na            JOIN assignments a ON (na.assignmentid = a.id)        JOIN tariffs t ON (a.tariffid = t.id)       JOIN nodes n ON (na.nodeid = n.id)       JOIN (      SELECT assignmentid, COUNT(*) AS cnt           FROM nodeassignments            GROUP BY assignmentid        ) o ON (o.assignmentid = na.assignmentid)       WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0)       AND (a.dateto > unix_timestamp() OR a.dateto = 0)       AND a.suspended = 0 AND n.name = 'jarzabek_pawel'       GROUP BY n.id   ) x   RIGHT JOIN (       SELECT n.id, n.name, 64 AS downceil, 64 AS upceil            FROM nodes n WHERE n.name = 'jarzabek_pawel'        ) z ON (1=1)


Na górę
Post: czw, 12 lip 2018 23:57:01 
Odpowiedz z cytatem
Offline
Młodszy czytelnik
Młodszy czytelnik

Rejestracja: wt, 19 mar 2013 12:29:37
Posty: 58
Na pierwszy rzut oka wygląda, że SQL zwyczajnie nie zwraca poprawnej wartości - w sensie takiej, która by świadczyła o ACCEPT.

Wklej to query do bazy, która masz przypiętą do Radiusa i zobacz co Tobie zwraca. Zobacz czy we właściwie nazwanych kolumnach zwraca to co powinien.


Na górę
Post: pt, 13 lip 2018 16:29:47 
Odpowiedz z cytatem
Offline
Żółtodziób
Żółtodziób

Rejestracja: sob, 27 sie 2016 15:17:18
Posty: 11
Dzięki za zainteresowanie...

Kod:
180713 16:24:26  1266 Query     SELECT      id, lower(name) as UserName , 'Cleartext-Password' as Attribute , passwd as Value, ':=' as op       FROM nodes      WHERE name = 'franko'   UNION         SELECT id, lower(name) as UserName , 'Simultaneous-Use' as Attribute, '1' as Value, ':=' as op         FROM nodes         WHERE name = 'franko'  UNION   SELECT 0 AS id, 'franko' AS UserName, 'Max-Octets' AS Attribute,             CONCAT(ROUND(COALESCE(x.dlimit, y.dlimit))) AS Value, ':=' AS op  FROM (      SELECT n.id, MIN(n.name) AS name, SUM(t.dlimit/o.cnt) AS dlimit         FROM nodeassignments na         JOIN assignments a ON (na.assignmentid = a.id)         JOIN tariffs t ON (a.tariffid = t.id)           JOIN nodes n ON (na.nodeid = n.id)              JOIN (              SELECT assignmentid, COUNT(*) AS cnt          FROM nodeassignments                     GROUP BY assignmentid               ) o ON (o.assignmentid = na.assignmentid)               WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0)                  AND (a.dateto > unix_timestamp() OR a.dateto = 0)                AND a.suspended = 0 AND n.name = 'franko'           GROUP BY n.id        ) x        RIGHT JOIN (            SELECT SUM(t.dlimit)/o.cnt AS dlimit            FROM assignments a            JOIN tariffs t ON (a.tariffid = t.id)            JOIN nodes n ON (a.customerid = n.ownerid)            JOIN (                SELECT COUNT(*) AS cnt, ownerid                FROM nodes              WHERE NOT EXISTS (                  SELECT 1 FROM nodeassignments, assignments a                    WHERE assignmentid = a.id AND nodeid = nodes.id                     AND a.suspended = 0 AND (a.dateto > unix_timestamp() OR a.dateto = 0))                GROUP BY ownerid            ) o ON (o.ownerid = n.ownerid)            WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0)          AND (a.dateto > unix_timestamp() OR a.dateto = 0)               AND a.suspended = 0 AND t.dlimit != '0'                AND NOT EXISTS (                     SELECT 1 FROM nodeassignments                 WHERE assignmentid = a.id)                   AND n.name = 'franko'            GROUP BY n.id        ) y ON (1=1)


Po wklejeniu do bazy zwraca jak należy:
Kod:
+-----+----------+--------------------+----------+----+
| id  | UserName | Attribute          | Value    | op |
+-----+----------+--------------------+----------+----+
| 224 | franko   | Cleartext-Password | 20150324 | := |
| 224 | franko   | Simultaneous-Use   | 1        | := |
+-----+----------+--------------------+----------+----+
2 rows in set (0.01 sec)


1266 Query SELECT id, lower(name) as UserName , 'Framed-IP-Address' as Attribute, inet_ntoa(ipaddr) as Value, '==' as op FROM nodes WHERE name = 'franko' UNION SELECT 0 AS id, 'franko' AS UserName, 'Mikrotik-Rate-Limit' AS Attribute, CONCAT(ROUND(COALESCE(x.upceil, z.upceil)),'k','/', ROUND(COALESCE(x.downceil, z.downceil)),'k') AS Value, '==' AS op FROM ( SELECT n.id, MIN(n.name) AS name, SUM(t.downceil/o.cnt) AS downceil, SUM(t.upceil/o.cnt) AS upceil FROM nodeassignments na JOIN assignments a ON (na.assignmentid = a.id) JOIN tariffs t ON (a.tariffid = t.id) JOIN nodes n ON (na.nodeid = n.id) JOIN ( SELECT assignmentid, COUNT(*) AS cnt FROM nodeassignments GROUP BY assignmentid ) o ON (o.assignmentid = na.assignmentid) WHERE (a.datefrom <= unix_timestamp() OR a.datefrom = 0) AND (a.dateto > unix_timestamp() OR a.dateto = 0) AND a.suspended = 0 AND n.name = 'franko' GROUP BY n.id ) x RIGHT JOIN ( SELECT n.id, n.name, 64 AS downceil, 64 AS upceil FROM nodes n WHERE n.name = 'franko' ) z ON (1=1)

Kod:
+-----+----------+---------------------+--------------+----+
| id  | UserName | Attribute           | Value        | op |
+-----+----------+---------------------+--------------+----+
| 224 | franko   | Framed-IP-Address   | xxx.xxx.xxx.xxx | == |
|   0 | franko   | Mikrotik-Rate-Limit | 2048k/20480k | == |
+-----+----------+---------------------+--------------+----+
2 rows in set (0.00 sec)


Także zapytania działają prawidłowo - zresztą w freeradius 2.0 działa to także prawidłowo jak pisałem wyżej...
Problem mam jakiś z freeradiusem 3.0 który nie autoryzuje tych klientów...

Pomysły?


Na górę
Post: sob, 21 lip 2018 13:47:48 
Odpowiedz z cytatem
Offline
Żółtodziób
Żółtodziób

Rejestracja: sob, 27 sie 2016 15:17:18
Posty: 11
Nikt, nic ? - Proszę o odpłatną pomoc.


Na górę
Wyświetl posty nie starsze niż:  Sortuj wg  
Nowy temat  Odpowiedz w temacie


Kto jest online

Użytkownicy przeglądający to forum: Obecnie na forum nie ma żadnego zarejestrowanego użytkownika i 2 gości


Nie możesz tworzyć nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz dodawać załączników

Szukaj:
Przejdź do:  
Dzisiaj jest ndz, 23 wrz 2018 0:16:18

Strefa czasowa UTC+02:00

Moderatorzy: Administratorzy, Moderatorzy

Nakarm glodne dziecko - wejdz na strone www.Pajacyk.pl


Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
SubSilver2 modified for Trzepak.pl by Colir
Polski pakiet językowy dostarcza phpBB.pl