Freeradius with remote mariadb server



  • Hi, i was using freeradius/daloradius combo with maria db on a remote server, all is working fine, now i installed a pfsense 2.4.4 i loved the built in freeradius gui configurator, i can do wharewer i want with it, i followed the online tutorials to install and configure radius using a remote mariadb server, the problem i am facing is that freeradius is not reading mariadb tables at all, in example, this is a working installation of freeradius3, in daloradius i created several nas and users, but when i enter pfsense freeraduis gui, users and nas tabs are empty, it is not reading the already populated (by daloradius) mariadb radcheck and nas tables, if try to create a nas or an user, it creates them in users and clients files in pfsense's local hard disk and they are not inserted to the tables of remote mariadb server. this is my freeradius config (copied from gui):

    /usr/local/etc/raddb/radiusd.conf
    prefix = /usr/local
    exec_prefix = ${prefix}
    sysconfdir = ${prefix}/etc
    localstatedir = /var
    sbindir = ${exec_prefix}/sbin
    logdir = ${localstatedir}/log
    raddbdir = ${sysconfdir}/raddb
    radacctdir = ${logdir}/radacct
    name = radiusd
    confdir = ${raddbdir}
    modconfdir = ${confdir}/mods-config
    certdir = ${confdir}/certs
    cadir = ${confdir}/certs
    run_dir = ${localstatedir}/run
    db_dir = ${raddbdir}
    libdir = /usr/local/lib/freeradius-3.0.17
    pidfile = ${run_dir}/${name}.pid
    max_request_time = 30
    cleanup_delay = 5
    max_requests = 1024
    hostname_lookups = no
    regular_expressions = yes
    extended_expressions = yes

    log {
    destination = syslog
    colourise = yes
    file = ${logdir}/radius.log
    syslog_facility = daemon
    stripped_names = no
    auth = yes
    auth_badpass = no
    auth_goodpass = no
    msg_goodpass = ""
    msg_badpass = ""
    msg_denied = "You are already logged in - access denied"
    }

    checkrad = ${sbindir}/checkrad
    security {
    allow_core_dumps = no
    max_attributes = 200
    reject_delay = 1
    status_server = no
    # Disable this check since it may not be accurate due to how FreeBSD patches OpenSSL
    allow_vulnerable_openssl = yes
    }

    $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
    }

    modules {
    $INCLUDE ${confdir}/mods-enabled/
    }

    instantiate {
    exec
    expr
    expiration
    logintime
    ### Dis-/Enable sql instatiate

    redundant sql {
    	sql1
    	### sql2 DISABLED ###
    }
    

    }
    policy {
    $INCLUDE policy.d/
    }
    $INCLUDE sites-enabled/

    /usr/local/etc/raddb/mods-enabled/sql

    sql sql1 {
    database = "mysql"
    driver = "rlm_sql_${database}"
    dialect = "${database}"
    server = "192.168.71.36"
    port = 3306
    login = "radius"
    password = "J4s0n2019$"
    radius_db = "radius"
    acct_table1 = "radacct"
    acct_table2 = "radacct"
    postauth_table = "radpostauth"
    authcheck_table = "radcheck"
    authreply_table = "radreply"
    groupcheck_table = "radgroupcheck"
    groupreply_table = "radgroupreply"
    usergroup_table = "radusergroup"
    read_groups = yes
    delete_stale_sessions = yes
    logfile = ${logdir}/sqltrace.sql
    read_clients = yes
    client_table = "nas"
    pool {
    start = ${thread[pool].start_servers}
    min = ${thread[pool].min_spare_servers}
    max = 5
    spare = ${thread[pool].max_spare_servers}
    uses = 0
    retry_delay = 60
    lifetime = 0
    idle_timeout = 60
    }
    group_attribute = "${.:instance}-SQL-Group"
    $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf
    }

    /usr/local/etc/raddb/clients.conf
    client pfsense {
    ipaddr = 127.0.0.1
    secret = pfsense
    shortname = pfsense
    limit {
    max_connections = 16
    lifetime = 0
    idle_timeout = 30
    }
    }

    /usr/local/etc/raddb/sites-enabled/default
    server default {
    listen {
    type = auth
    ipaddr = 127.0.0.1
    port = 1812
    }
    listen {
    type = acct
    ipaddr = 127.0.0.1
    port = 1813
    }

    authorize {

    filter_username

    filter_password

    preprocess
    

    operator-name

    cui

    AUTHORIZE FOR PLAIN MAC-AUTH IS DISABLED

    auth_log

    chap
    mschap
    digest
    

    wimax

    IPASS

    suffix
    ntdomain
    eap {
    	ok = return
    

    updated = return

    }
    

    unix

    files
    
    redundant sql {
    	sql1
    	### sql2 DISABLED ###
    }
    

    smbpasswd

    ldap

    # Formerly checkval
    if (&request:Calling-Station-Id == &control:Calling-Station-Id) {
    	ok
    }
    expiration
    logintime
    pap
    Autz-Type Status-Server {
    
    }
    

    }

    authenticate {
    Auth-Type PAP {
    pap
    }
    Auth-Type CHAP {
    chap
    }
    Auth-Type MS-CHAP {
    mschap
    }
    mschap
    Auth-Type MOTP {
    motp
    }
    Auth-Type GOOGLEAUTH {
    googleauth
    }
    digest

    pam

    unix

    #Auth-Type LDAP {
    	#ldap
    	#### ldap2 disabled ###
    #}
    
    eap
    

    Auth-Type eap {

    eap {

    handled = 1

    }

    if (handled && (Response-Packet-Type == Access-Challenge)) {

    attr_filter.access_challenge.post-auth

    handled # override the "updated" code from attr_filter

    }

    }

    }

    preacct {
    preprocess

    ACCOUNTING FOR PLAIN MAC-AUTH DISABLED

    acct_counters64

    update request {
    	&FreeRADIUS-Acct-Session-Start-Time = "%{expr: %l - %{%{Acct-Session-Time}:-0} - %{%{Acct-Delay-Time}:-0}}"
    }
    

    acct_unique

    IPASS

    suffix
    ntdomain
    files
    

    }

    accounting {

    cui

    detail
    ### This makes it possible to run the datacounter_acct module only on accounting-stop and interim-updates
    if ((request:Acct-Status-Type == Stop) || (request:Acct-Status-Type == Interim-Update)) {
    	datacounterdaily
    	datacounterweekly
    	datacountermonthly
    	datacounterforever
    }
    

    unix

    radutmp
    

    sradutmp

    main_pool

    redundant sql {
    	sql1
    	### sql2 DISABLED ###
    }
    

    if (noop) {

    ok

    }

    pgsql-voip

    exec
    attr_filter.accounting_response
    Acct-Type Status-Server {
    
    }
    

    }

    session {

    radutmp

    redundant sql {
    	sql1
    	### sql2 DISABLED ###
    }
    

    }

    post-auth {

    if (!&reply:State) {

    update reply {

    State := "0x%{randstr:16h}"

    }

    }

    update {
    	&reply: += &session-state:
    }
    

    main_pool

    cui

    reply_log

    redundant sql {
    	sql1
    	### sql2 DISABLED ###
    }
    

    ldap

    exec
    

    wimax

    update reply {

    Reply-Message += "%{TLS-Cert-Serial}"

    Reply-Message += "%{TLS-Cert-Expiration}"

    Reply-Message += "%{TLS-Cert-Subject}"

    Reply-Message += "%{TLS-Cert-Issuer}"

    Reply-Message += "%{TLS-Cert-Common-Name}"

    Reply-Message += "%{TLS-Cert-Subject-Alt-Name-Email}"

    Reply-Message += "%{TLS-Client-Cert-Serial}"

    Reply-Message += "%{TLS-Client-Cert-Expiration}"

    Reply-Message += "%{TLS-Client-Cert-Subject}"

    Reply-Message += "%{TLS-Client-Cert-Issuer}"

    Reply-Message += "%{TLS-Client-Cert-Common-Name}"

    Reply-Message += "%{TLS-Client-Cert-Subject-Alt-Name-Email}"

    }

    insert_acct_class

    if (&reply:EAP-Session-Id) {

    update reply {

    EAP-Key-Name := &reply:EAP-Session-Id

    }

    }

    remove_reply_message_if_eap
    Post-Auth-Type REJECT {
    	# log failed authentications in SQL, too.
    	sql
    	attr_filter.access_reject
    	eap
    	remove_reply_message_if_eap
    }
    Post-Auth-Type Challenge {
    
    }
    

    }

    pre-proxy {

    operator-name

    cui

    files

    attr_filter.pre-proxy
    

    pre_proxy_log

    }

    post-proxy {

    post_proxy_log

    attr_filter.post-proxy
    eap
    

    Post-Proxy-Type Fail-Accounting {

    detail

    }

    }
    }


Log in to reply