Freeradius with remote mariadb server

miguelmirandag

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

}

}
}