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 = yeslog {
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 instatiateredundant 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
}
digestpam
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 {
preprocessACCOUNTING 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
}
}
}