Migrate from "files" to "sql" with multiple DEFAULT users

[Alan DeKok]

On May 30, 2023, at 12:01 PM, Robert Senger <robert.senger at lists.microscopium.de> wrote:
> I am trying to migrate from wifi users stored in the 'authorize' file
> to wifi users stored in a mysql database.

 It should mostly be straightforward.

> The 'sites-available/default' configuration file says:
> 
> "The schema of the database is meant to mirror the 'users' file."

 Yes... see also http://wiki.freeradius.org, and look for "rlm_sql".  It has more documentation,

> Ok, this is fine with single users with known username. But what to do
> if there is more than one DEFAULT user? The 'authorize' file says, that
> it is processed by order, but this will not work in a database.

 You can add multiple entries for one user, including the DEFAULT user.  The queries order the entries by ID, which is the first column.

> My config is like this, to allow different sets of passwords for
> different SSIDs (staff and guests):
> 
> <...>
> 
> DEFAULT Auth-Type := ACCEPT, Called-Station-Id != "02-A5-04-3D-96-
> 13:Cassiopeia", Called-Station-Id != "02-A5-04-3D-96-23:Cassiopeia"
>       Tunnel-Password = "passwordforuser1",
>       Tunnel-Password = "passwordforuser2",
>       Tunnel-Password = "passwordforuser3"
> 
> DEFAULT Auth-Type := ACCEPT, Called-Station-Id == "02-A5-04-3D-96-
> 13:Cassiopeia"
>       Tunnel-Password = "passwordforguest1",
>       Tunnel-Password = "passwordforguest2",
>       Tunnel-Password = "passwordforguest3"
> 
> DEFAULT Auth-Type := ACCEPT, Called-Station-Id == "02-A5-04-3D-96-
> 23:Cassiopeia"
>       Tunnel-Password = "passwordforguest1",
>       Tunnel-Password = "passwordforguest2",
>       Tunnel-Password = "passwordforguest3"
> 
> DEFAULT Auth-Type := Reject
> 
> <EOF>
> 
> I have no idea how to build this in the mysql database... Any
> suggestions?

 Put those entries into SQL pretty much as-is.

 For full documentation, see https://wiki.freeradius.org/modules/Rlm_sql

 The operation of the module is explained in detail.

 Alan DeKok.