Miss Logging on FreeRadius

vahidmoghadam

Hello,

I'm using CaptivePortal with FreeRadius for our personnel and guest's internet access.
I installed the freeradius server on the local pfSense machine for the guest hotspot and the external freeradius server on the other server for the personnel hotspot.
These are using separate MySQL databases.

Now, I have a problem with writing logs in radposauth and radaact tables.

When a personnel user tries to log in to use the internet, the log is written to the radacct table of guest MySQL.

I want to have personnel access logs on its MySQL database.

Does anyone have any ideas?

Gertjan

@vahidmoghadam

The radposauth table has an entry added every minute or so for the auth result of every connected client.
The radaact table contains the state and variables of every connected user.

What is your question ?

vahidmoghadam

@Gertjan Exactly,

But my personnel freeradius logs combinate with the guest freeradius logs, and I cant see anything in radposauth and radacct tables on the personnel freeradius MySQL but I can see its on the guest freeradius MySQL. They blend unintentionally. They are two separate servers and two separate databases

I hope I was able to explain my problem correctly.

Gertjan

@vahidmoghadam

So you have two System > User Manager > Authentication Servers ? (not counting the Local Database- pfSense) ?

You are using :

?

@vahidmoghadam said in Miss Logging on FreeRadius:

I cant see anything in radposauth and radacct tables on the personnel freeradius MySQL

Probably, IMHO : as it isn't used.

vahidmoghadam

@Gertjan Thanks for responding...

I made a mistake in the configuration of the personnel captive portal. I selected free radius server of the Guest radius server.
Now I changed to the correct server but it doesn't insert logs to related tables of both radius servers.

Can the problem be from the queries on the /etc/raddb/mods-config/sql/main/mysql/queries.conf?

Gertjan

@vahidmoghadam said in Miss Logging on FreeRadius:

Can the problem be from the queries on the /etc/raddb/mods-config/sql/main/mysql/queries.conf?

Works for me so why wouldn't it work for you ?

The pfSense package FreeRadius can be mae very verbose :
Stop it in the GUI.
On the command line, type

radiusd -X

vahidmoghadam

@Gertjan
this is not pfSense package.
it is an external free radius server. I've installed it on Almalinux 8.

I try radiusd -X on my external radius server and don't get any error while logging in to the captive portal.

just get one warning:

...
...
...
(30) # Executing section accounting from file /etc/raddb/sites-enabled/default
(30)   accounting {
(30) detail: EXPAND /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
(30) detail:    --> /var/log/radius/radacct/31.7.xxx.xxx/detail-20240603
(30) detail: /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radius/radacct/31.7.xxx.xxx/detail-20240603
(30) detail: EXPAND %t
(30) detail:    --> Mon Jun  3 15:45:53 2024
(30)     [detail] = ok
(30)     [unix] = ok
(30) sql: EXPAND .query
(30) sql:    --> .query
(30) sql: WARNING: No such configuration item .query
(30)     [sql] = noop
(30)     [exec] = noop
...
...
...

Gertjan

@vahidmoghadam said in Miss Logging on FreeRadius:

I try radiusd -X on my external radius server ...

It's this external radius server that handle its own database.
Not a pfSense issue then.

vahidmoghadam

@Gertjan
Yes, There is not any problem from the pfSense side.
I just said I would ask my question here in case someone could help me.

Gertjan

@vahidmoghadam said in Miss Logging on FreeRadius:

someone could help me

You already did help yourself

After the /var/log/radius/radacct/... log notification, it also want to do something with SQL, probably logging into the radacct table.

But, IMHO, you saw the warning. It didn't find the query to do so.
Compare what you find here : /usr/local/etc/raddb/mods-config/sql/main/mysql/queries.conf with your Radius 'SQL' file.