How To install MySQL on Pfsense 2.4.5-RELEASE-p1

[ontzuevanhussen]

@Gertjan Yes, I have VPS on digitalocean with ubuntu server 18.04.4 LTS

[Gertjan]

@ontzuevanhussen said in How To install MySQL on Pfsense 2.4.5-RELEASE-p1:

ubuntu

Well .....

sudo apt install mysql-server

and you enter in the world of 'maintaining a real database'.

When everything is set up correctly : check :

That your VPS - mysql server port (default) 3306 is protected with a firewall rule that accepts traffic from your WAN IP pfSense only. When you instal mysql server on Ubuntu, by default, it will be bound to 127.0.0.1 - or maybe even only a local socket.
mysql server ports should never be exposed to "the Internet".
Consider also using the SSL version of the traffic, not the "all clear standard protocol" (traffic between myslsql-client running on pfSEnse and mysql-server running on your VPS) because your using 'public roads' (the Internet) for interconnecting 'local only' services.
Anyway : there a million or so tutos on the net about how to set it up.

[ontzuevanhussen]

@Gertjan Already open port on my VPS,

And this my database for FreeRADIUS server:

This is on my pfSense:

All my steps is correct? What is the next step? Please help. Thank you

[ontzuevanhussen]

@Gertjan This is my System Logs

[Gertjan]

First image : That's a click-interface. I don't understand.
I use the universal language :

netstat -naptul | grep '3306'

That one works for decades now.
=> Although : check firewall is needed. Again : never ever leave a MySQL server port open on the net, or you will loose your entire VPS server "very soon".

Second image : phpmyadmin. Nice. More classic isn't possible.

Third image :
Nothing is filled in yet.
Shake your head, and the info will fall out, onto the keyboard, on the screen.
I could even fill it in for you, except the password for the MySQL user that accesses the database 'radius'. That one is in your head only.

Last image : I don't get it.
You do not have any authentication credentials filled in - and you are surprised the FreeRadius package can't connect to 'the server' ?
When you connect to your mail server, you need to have it's address like IP or host adress, login code, password, port number etc - right ? here, it's the very same thing.

Btw : My settings are very identical :

[ontzuevanhussen]

@Gertjan said in How To install MySQL on Pfsense 2.4.5-RELEASE-p1:

netstat -naptul | grep '3306'

This is my VPS:

[ontzuevanhussen]

@Gertjan I use mariadb:

[ontzuevanhussen]

@Gertjan my ufw status on VPS :

[ontzuevanhussen]

@Gertjan This is my pfSense SQL configuration:

[ontzuevanhussen]

@Gertjan This my System logs on pfSense:

[ontzuevanhussen]

@Gertjan I have create user and allow from remote access my database:

[Gertjan]

First image :

Doesn't that ring a bell ?
A service that is listening on address 127.0.0.1 can only be reached by the system itself. That's the defintion of localhost or 127.0.0.1.
Everybody else, locally on a LAN ,and the Internet : negative.
You have to listen (add) to address "0.0.0.0" which means all available IPv4 addresses.
This will include the one you masked here :

You should have a :
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 13538/mysqld

and of course, please, limit the access to this port with a firewall rule, so it accepts only traffic from your pfSense.
If not, "you get hacked in no time", because the MySQL traffic is non intended to be exposed to the entire Internet.

I'll leave it up to you to find the instruction to be placed into /etc/my.cnf so the mysql server daemon listens to 'everything'.

[ontzuevanhussen]

@Gertjan Ok now it works well.

but table nas, radacct, radcheck, radgroupcheck, radgroupreply, radreply, and radusergroup is no data. why?

[Gertjan]

When you enable all "SQL" options :

only two tables get filled in with data from the Freeradius process :
radpostauth and radacct.

The radcheck, radgroupcheck should by filled in by you, using phpmyadmin.
Their content can not be modified from within pfSense.
As you have figured out : the users and passsword could have been placed also in a database table, but pfSense uses us a file on disk for this ( PackageFreeRADIUS > Users > Users )
The Freeradius package only exposes a (very) small part of all possible FreeRadius possibilities.
Most option are hard coded, and can't be changed using the pfSEnse GUI.

To get the idea, see this file : /usr/local/etc/raddb/sites-enabled/default and the entire directory /usr/local/etc/raddb/ (all files and folders).

What these tables do, how to fill them in, etc, there is only one way to find that out : https://freeradius.org/documentation/

Remember : MySQL (MariadDB) is actually doing nothing special. It's just the scratch pad area of FreeRadius.

"FreeRadius" is a complex thing.

[ontzuevanhussen]

@Gertjan Ok, I try to insert table Radcheck and radgroupcheck, like this:

Is that true? Can you help me again how to insert this table..

[Gertjan]

You can't.

To have FreeRadius use all the MySQL database tables, you have to modify /usr/local/etc/raddb/sites-enabled/default - and probably other files as well - which means you have to modify pfSense itself.
This means thorough FreeRadius knowledge and a good understanding about how pfSense makes the set up files.
If that was the case, you wouldn't ask question, you were just doing it.

[sapsin]

This post is deleted!