[freeradius] Support for remote MySQL database

[jokay]

Hello

It would be great to have support for MySQL databases in plugin os-freeradius.

In the pfSense FreeRADIUS package this is already available for MySQL and PostgreSQL.

[mimugmail]

You need MySQL support or you want to run a local MySQL db?

[jokay]

Changed the subject 😉

Something like this and this.

Have a look at this document, page 12 & 13 to get an idea of it. It states localhost but can be (and should be) a remote server.

[mimugmail]

For accounting sqlite should be enough which is already integrated?

[jokay]

Not only for accounting but for authorization too.

There is a table radcheck which contains the authenticated users as in the sample.

Given the following use case for a hotel Wi-Fi:

• Guest connects to a open Wi-Fi and gets to the captive portal (registration form)
• Registration will be made by guest (e.g. firstname, lastname, email)
  This will insert a record in table radcheck and a second one in table guest.
• A service will then check the newly registered guest and send an email for confirmation
• If the link in the email is not clicked within e.g. 30min, the service will then delete the radcheck record and the guest needs to register again, otherwise access is granted for 6 months.

We have this use case and it's currently setup and running on a pfSense.
It first checks the MAC in the table radcheck and fallbacks to the captive portal page (for the registration).

As the firewall and the service needs access to the data, it would be great if we could use the FreeRADIUS feature to access MySQL databases.

But this will show us the next problem for the use case. We got this option in pfSense RADIUS MAC Secret (RADIUS MAC will automatically try to authenticate devices with their MAC address as username, and the password entered below as password. Devices will still need to make one HTTP request to get connected, throught.) which is also needed for the use case.
This will insert the MAC address and as passwort the RADIUS MAC Secret in the radcheck table.

[mimugmail]

And is the UI missing something? Remote MySQL should be OK but when you have an Input field for MySQL credentials is this enough?

[jokay]

Yes, there is no UI to enter the SQL values which will create the config for radius.

/usr/local/etc/raddb/mods-enabled/sql

sql sql1 {
	database = "mysql"
	driver = "rlm_sql_${database}"
	dialect = "${database}"
	server = "192.168.10.100"
	port = 3306
	login = "radius"
	password = "radpass"
	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
}

Just install pfSense and the freeradius3 package and you will see it 😉

[mimugmail]

Perhaps you get me wrong, do you have an external captive portal feeding the mysql DB?

If we do this, the port has to be compiled with mysql flag, also I can easily add this part what you need. But I dont see any chance to tweak the captive portal with this logic (registration, sent email etc.).

That's why I'm asking if it would be enough to have a credentials form for the DB.

[jokay]

No, it's a service running on server inside the network, not on the firewall itself.

The captive portal should only be able to use php mysqli to insert the data in the tables radcheck and guest but this is only one part of the use case.

The other part is radius which need to be able to read the data from the database (table radcheck) for the authorization.

This will be MAC as username and RADIUS MAC Secret (new UI field in Captive Portal setup) as password for this use case.

[AdSchellevis]

It might be worth investigating your complete solution first before adding a lot of glue in the freeradius plugin, our captive portal for instance won't allow php code to be executed directly like the other project does (separation of concerns)

[jokay]

@AdSchellevis there isn't something like pkg install php-mysqli mysql-client which can be executed to be able to allow the captive portal to insert data in a MySQL database?

[AdSchellevis]

no, not directly. The user login can only execute api endpoints, although is fairly easy to write custom authenticators or extend with your own endpoints for additional functionality.

[jokay]

@AdSchellevis ok, good to know.

[fabianfrz]

@mimugmail If you work on a generic SQL backend, can you make it in a way that PostgreSQL is supported too? It is easy to build the server from the ports tree but the client tool pg needs some dependencies.

Maybe it can be added as someday to OPNsense if it is required for more plugins.

[mimugmail]

FreeRADIUS already have all "dialects" included but right now I'm not commited in supporting user backend for all types.

[mimugmail]

@x-jokay can you close this one or do you still need some features?

[jokay]

@mimugmail sure, I can close this issue, even remote mysql databases for radius authentication are not yet supported but wouldn't it be better to let this one open until the feature is available?

[mimugmail]

There are too many topics mixed like php-mysqli or CP writing into MySQL. Actually I have no idea what you really need

[jokay]

This is what I need #1092 (comment), an UI to enter the values for a remote MySQL database for radius authentication which will then create the file for the radius service.

I mentionned php-mysqli in CP only to describe my use case.

[mimugmail]

@fichtner What do you think about this? I already have a branch ready, but it would need to enable mysql here:
https://github.com/opnsense/tools/blob/master/config/19.1/make.conf#L60

Not sure if we also have to add mysql-client as a plugin dependency.
As I don't know how much work this would be for you in addition and use case is low I'm open for acceptance or decline

[fichtner]

Does radius build support multiple database backends?

[mimugmail]

No, I already added a Single select constraint and put MySQL in advanced. I can open a WIP pr if you want to have a look

[fabianfrz]

@fichtner https://github.com/opnsense/ports/blob/master/net/freeradius3/Makefile#L47

Options:

• MYSQL - MariaDB and MySQL
• PGSQL - PostgreSQL
• SQLITE3 - sqlite3

[fichtner]

SQLITE3 was always on for us, MYSQL support in c38b9a4 -- we'll defer PGSQL for when plugin parts are strictly required and written

[mimugmail]

@fichtner close? It's already in ..