Pfsene captive port + freeradius3 + Mysql integration error

[Jangchu Dorji]

Dear all;
I am little new to pfsense .therefor i would like request expert suggestion here.
I am trying freeradius3 integration with Mysql.

My freeradius3 is running fine i am able fetch my mysql which i have configured pfsense only.

I did radtest before enabling mysql support.That time my wasnt rejected.
When i enable mysql support I am getting attached error.

I have used this link for refrence http://netpower.fr/sites/default/files/soft/html-doc/pfSense-cp-auth-onestep_0.html

All your help and suggestion will higly apcreciated by me

Thanking you

[viktor_g]

Which version of FreeRADIUS pkg are you using?
some issues have been fixed recently:
https://forum.netgate.com/topic/160549/captive-portal-error

[Jangchu Dorji]

@viktor_g
Thank you for your reply.
I have installed 0.15.7_29 version.Seems like version was not problem.I did radiusd -X
Failed binding to auth address 172.10.0.1 port 1812 bound to server default: Address already in use
/usr/local/etc/raddb/sites-enabled/default[2]: Error binding to port for 172.10. 0.1 port 1812
I check runing prot using netstat...1812 wasnt used my radius only.
inside this /usr/local/etc/raddb/sites-enabled/ there are two file ( inner-tunnel-peap inner-tunnel-ttls)
inner-tunnel-ttls has config something like this(server inner-tunnel-ttls {
listen {
ipaddr = 127.0.0.1
port = 18127
type = auth
}
)
inner-tunnel-peap has config ( listen {
ipaddr = 127.0.0.1
port = 18128
type = auth
})
I am little confused..

your help will be highly appcreciated by me.

[Gertjan]

@jangchu-dorji said in Pfsene captive port + freeradius3 + Mysql integration error:

I did radiusd -X

Before you do that, stop the running 'GUI' Radius first.

This one, like this :

Now you can start radius in debugging mode on the console/SSH access :

radiusd -X

It should progress up until it shows :

Ready to process requests

Some (3 ?) yellow lines will scroll by. These are harmless.
Red lines means troubles.

edit :

ipaddr = 127.0.0.1

The real cause is : FreeRadius is way more complex as what you imagined.

It listens to a 'NAS', this is the 'client' which is a source that needs authentication.
Examples are simple pfSense logins, or a Captivve portal.
These are running locally, on the same machne, pfSense, so it listens - radius is a server here - on localhost, 127.0.0.1.
The captive portal, as a client, connect also to localhost, 127.0.0.1 - same port.

FreeRadius can use (locally stored) plain files keep it records (data) up to date.
It can also use several types of databases, MySQL is just one of them.
In that case, it should use an Interface and an IP of the MySQL server (running MySQL server on pfSense is also possible but not encouraged/supported).

Etc.

[Jangchu Dorji]

Thank you for suggestion.
This is the what got after stoping freeradius:There is not red line, whats is peap and ttls is listening to 18128 and 18127...
Enlighten me :)

Listening on auth address 172.10.0.1 port 1812 bound to server default
Listening on acct address 172.10.0.1 port 1813 bound to server default
Listening on status address 172.10.0.1 port 1816 bound to server default
Listening on auth address 127.0.0.1 port 18127 bound to server inner-tunnel-ttls
Listening on auth address 127.0.0.1 port 18128 bound to server inner-tunnel-peap
Ready to process requests

[Gertjan]

@jangchu-dorji said in Pfsene captive port + freeradius3 + Mysql integration error:

Ready to process requests

Now you login with some some capitve portal ..... and see what happens on the screen.
This is what happens :

(15) Received Access-Request Id 147 from 192.168.2.1:45466 to 192.168.2.1:1812 length 161
(15)   Service-Type = Login-User
(15)   User-Name = "x"
(15)   User-Password = "x"
(15)   NAS-IP-Address = 192.168.2.1
(15)   NAS-Identifier = "CaptivePortal-cpzone1"
(15)   Calling-Station-Id = "f6:f2:a2:34:a8:53"
(15)   Called-Station-Id = "00:15:17:77:d1:1b:pfsense.brit-hotel-fumel.net"
(15)   NAS-Port-Type = Ethernet
(15)   NAS-Port = 2018
(15)   Framed-IP-Address = 192.168.2.102
(15) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default
(15)   authorize {
(15)     [preprocess] = ok
(15)     [chap] = noop
(15)     [mschap] = noop
(15)     [digest] = noop
(15) suffix: Checking for suffix after "@"
(15) suffix: No '@' in User-Name = "x", skipping NULL due to config.
(15)     [suffix] = noop
(15) ntdomain: Checking for prefix before "\"
(15) ntdomain: No '\' in User-Name = "x", skipping NULL due to config.
(15)     [ntdomain] = noop
(15) eap: No EAP-Message, not doing EAP
(15)     [eap] = noop
(15) files: users: Matched entry x at line 386
(15)     [files] = ok
(15)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept")) {
(15)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept"))  -> FALSE
(15) dailycounter: WARNING: Couldn't find check attribute, control:Max-Daily-Session, doing nothing...
(15)     [dailycounter] = noop
(15) monthlycounter: WARNING: Couldn't find check attribute, control:Max-Monthly-Session, doing nothing...
(15)     [monthlycounter] = noop
(15) noresetcounter: WARNING: Couldn't find check attribute, control:Max-All-Session, doing nothing...
(15)     [noresetcounter] = noop
(15) expire_on_login: WARNING: Couldn't find check attribute, control:Expire-After, doing nothing...
(15)     [expire_on_login] = noop
(15)     if (&request:Calling-Station-Id == &control:Calling-Station-Id) {
(15)     ERROR: Failed retrieving values required to evaluate condition
(15)     [expiration] = noop
(15)     [logintime] = noop
(15)     [pap] = updated
(15)   } # authorize = updated
(15) Found Auth-Type = PAP
(15) # Executing group from file /usr/local/etc/raddb/sites-enabled/default
(15)   Auth-Type PAP {
(15) pap: Login attempt with password
(15) pap: Comparing with "known good" Cleartext-Password
(15) pap: User authenticated successfully
(15)     [pap] = ok
(15)   } # Auth-Type PAP = ok
(15) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default
(15)   post-auth {
(15)     update {
(15)       No attributes updated for RHS &session-state:
(15)     } # update = noop
(15)     redundant sql {
(15) sql1: EXPAND .query
(15) sql1:    --> .query
(15) sql1: Using query template 'query'
rlm_sql (sql1): Reserved connection (0)
(15) sql1: EXPAND %{User-Name}
(15) sql1:    --> x
(15) sql1: SQL-User-Name set to 'x'
(15) sql1: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S.%M')
(15) sql1:    --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'x', 'x', 'Access-Accept', '2021-03-03 11:21:55.219611')
(15) sql1: EXPAND /var/log/sqltrace.sql
(15) sql1:    --> /var/log/sqltrace.sql
(15) sql1: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'x', 'x', 'Access-Accept', '2021-03-03 11:21:55.219611')
(15) sql1: SQL query returned: success
(15) sql1: 1 record(s) updated
rlm_sql (sql1): Released connection (0)
(15)       [sql1] = ok
(15)     } # redundant sql = ok
(15) exec: Executing: /bin/sh /usr/local/etc/raddb/scripts/datacounter_auth.sh x daily:
(15) exec: Program returned code (0) and output ''
(15) exec: Program executed successfully
(15)     [exec] = ok
(15)     policy remove_reply_message_if_eap {
(15)       if (&reply:EAP-Message && &reply:Reply-Message) {
(15)       if (&reply:EAP-Message && &reply:Reply-Message)  -> FALSE
(15)       else {
(15)         [noop] = noop
(15)       } # else = noop
(15)     } # policy remove_reply_message_if_eap = noop
(15)   } # post-auth = ok
(15) Sent Access-Accept Id 147 from 192.168.2.1:1812 to 192.168.2.1:45466 length 0
(15)   WISPr-Bandwidth-Max-Up = 1000000
(15)   WISPr-Bandwidth-Max-Down = 5000000
(15)   Acct-Interim-Interval = 500
(15)   WISPr-Redirection-URL = "https://www.google.com/"
(15)   pfSense-Max-Total-Octets = 0
(15) Finished request
Waking up in 4.9 seconds.
(16) Received Accounting-Request Id 181 from 192.168.2.1:59258 to 192.168.2.1:1813 length 173
(16)   Service-Type = Login-User
(16)   User-Name = "x"
(16)   Acct-Status-Type = Start
(16)   Acct-Authentic = RADIUS
(16)   NAS-IP-Address = 192.168.2.1
(16)   NAS-Identifier = "CaptivePortal-cpzone1"
(16)   NAS-Port-Type = Ethernet
(16)   NAS-Port = 2018
(16)   Acct-Session-Id = "88ffb89ea065ace8"
(16)   Framed-IP-Address = 192.168.2.102
(16)   Calling-Station-Id = "f6:f2:a2:34:a8:53"
(16)   Called-Station-Id = "00:15:17:77:d1:1b:pfsense.brit-hotel-fumel.net"
(16) # Executing section preacct from file /usr/local/etc/raddb/sites-enabled/default
(16)   preacct {
(16)     [preprocess] = ok
(16)     update request {
(16)       EXPAND %{expr: %l - %{%{Acct-Session-Time}:-0} - %{%{Acct-Delay-Time}:-0}}
(16)          --> 1614766915
(16)       &FreeRADIUS-Acct-Session-Start-Time = Mar  3 2021 11:21:55 CET
(16)     } # update request = noop
(16)     policy acct_unique {
(16)       update request {
(16)         &Tmp-String-9 := "ai:"
(16)       } # update request = noop
(16)       if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) &&      ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) {
(16)       EXPAND %{hex:&Class}
(16)          -->
(16)       EXPAND ^%{hex:&Tmp-String-9}
(16)          --> ^61693a
(16)       if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) &&      ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i))  -> FALSE
(16)       else {
(16)         update request {
(16)           EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}}
(16)              --> 5145365f2f035454afc56f02dee08f9a
(16)           &Acct-Unique-Session-Id := 5145365f2f035454afc56f02dee08f9a
(16)         } # update request = noop
(16)       } # else = noop
(16)     } # policy acct_unique = noop
(16) suffix: Checking for suffix after "@"
(16) suffix: No '@' in User-Name = "x", skipping NULL due to config.
(16)     [suffix] = noop
(16) ntdomain: Checking for prefix before "\"
(16) ntdomain: No '\' in User-Name = "x", skipping NULL due to config.
(16)     [ntdomain] = noop
(16)     [files] = noop
(16)   } # preacct = ok
(16) # Executing section accounting from file /usr/local/etc/raddb/sites-enabled/default
(16)   accounting {
(16) detail: EXPAND /var/log/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
(16) detail:    --> /var/log/radacct/192.168.2.1/detail-20210303
(16) detail: /var/log/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radacct/192.168.2.1/detail-20210303
(16) detail: EXPAND %t
(16) detail:    --> Wed Mar  3 11:21:55 2021
(16)     [detail] = ok
(16)     if ((request:Acct-Status-Type == Stop) || (request:Acct-Status-Type == Interim-Update)) {
(16)     if ((request:Acct-Status-Type == Stop) || (request:Acct-Status-Type == Interim-Update))  -> FALSE
(16) radutmp: EXPAND /var/log/radutmp
(16) radutmp:    --> /var/log/radutmp
(16) radutmp: EXPAND %{User-Name}
(16) radutmp:    --> x
(16)     [radutmp] = ok
(16)     redundant sql {
(16) sql1: EXPAND %{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}
(16) sql1:    --> type.start.query
(16) sql1: Using query template 'query'
rlm_sql (sql1): Reserved connection (4)
(16) sql1: EXPAND %{User-Name}
(16) sql1:    --> x
(16) sql1: SQL-User-Name set to 'x'
(16) sql1: EXPAND INSERT INTO radacct (acctsessionid,           acctuniqueid,           username, realm,                        nasipaddress,           nasportid, nasporttype,        acctstarttime,           acctupdatetime, acctstoptime,           acctsessiontime,        acctauthentic, connectinfo_start,       connectinfo_stop,       acctinputoctets, acctoutputoctets,      calledstationid,        callingstationid, acctterminatecause,   servicetype,            framedprotocol, framedipaddress,        framedipv6address,      framedipv6prefix, framedinterfaceid,    delegatedipv6prefix) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Framed-IPv6-Address}', '%{Framed-IPv6-Prefix}', '%{Framed-Interface-Id}', '%{Delegated-IPv6-Prefix}')
(16) sql1:    --> INSERT INTO radacct (acctsessionid,           acctuniqueid,           username, realm,                        nasipaddress,           nasportid, nasporttype,        acctstarttime,           acctupdatetime, acctstoptime,           acctsessiontime,        acctauthentic, connectinfo_start,       connectinfo_stop,       acctinputoctets, acctoutputoctets,      calledstationid,        callingstationid, acctterminatecause,   servicetype,            framedprotocol, framedipaddress,        framedipv6address,      framedipv6prefix, framedinterfaceid,    delegatedipv6prefix) VALUES ('88ffb89ea065ace8', '5145365f2f035454afc56f02dee08f9a', 'x', '', '192.168.2.1', '2018', 'Ethernet', FROM_UNIXTIME(1614766915), FROM_UNIXTIME(1614766915), NULL, '0', 'RADIUS', '', '', '0', '0', '00:15:17:77:d1:1b:pfsense.brit-hotel-fumel.net', 'f6:f2:a2:34:a8:53', '', 'Login-User', '', '192.168.2.102', '', '', '', '')
(16) sql1: EXPAND /var/log/sqltrace.sql
(16) sql1:    --> /var/log/sqltrace.sql
(16) sql1: Executing query: INSERT INTO radacct (acctsessionid,         acctuniqueid,           username, realm,                        nasipaddress,           nasportid, nasporttype,acctstarttime,           acctupdatetime, acctstoptime,           acctsessiontime,        acctauthentic, connectinfo_start,       connectinfo_stop,       acctinputoctets, acctoutputoctets,      calledstationid,        callingstationid, acctterminatecause,   servicetype,            framedprotocol, framedipaddress,        framedipv6address,      framedipv6prefix, framedinterfaceid,    delegatedipv6prefix) VALUES ('88ffb89ea065ace8', '5145365f2f035454afc56f02dee08f9a', 'x', '', '192.168.2.1', '2018', 'Ethernet', FROM_UNIXTIME(1614766915), FROM_UNIXTIME(1614766915), NULL, '0', 'RADIUS', '', '', '0', '0', '00:15:17:77:d1:1b:pfsense.brit-hotel-fumel.net', 'f6:f2:a2:34:a8:53', '', 'Login-User', '', '192.168.2.102', '', '', '', '')
(16) sql1: SQL query returned: success
(16) sql1: 1 record(s) updated
rlm_sql (sql1): Released connection (4)
(16)       [sql1] = ok
(16)     } # redundant sql = ok
(16)     [exec] = noop
(16) attr_filter.accounting_response: EXPAND %{User-Name}
(16) attr_filter.accounting_response:    --> x
(16) attr_filter.accounting_response: Matched entry DEFAULT at line 12
(16)     [attr_filter.accounting_response] = updated
(16)   } # accounting = updated
(16) Sent Accounting-Response Id 181 from 192.168.2.1:1813 to 192.168.2.1:59258 length 0
(16) Finished request
(16) Cleaning up request packet ID 181 with timestamp +159
Waking up in 4.9 seconds.

As you can see, there are tons of 'SQL' details.

And again : this is just for simple 'a captive portal login'.
You didn't even see the interim checkups :

These interim connections enforce 'byte counting' and 'timed connections'.

There is even a red (error) line :

(45)     ERROR: Failed retrieving values required to evaluate condition

but again : harmless.

[Jangchu Dorji]

@gertjan
Thank you for kind suggestion it had hlep me so much.For now i have intsalled fresh pfsense and upgraded.After that we worked.

Thank you Gertan