SQL auth against existing database
Hi, I’m looking through the SQL docs on the wiki, and they seem very complete, but I’m not quite grasping how to accomplish what I want. In short, I have an existing database that’s primarily used for our email system. I currently also have gnu-radius using this db - in the past for dialup, but at this point just a few remaining oddball services like usenet. I’m moving away from gnu-radius because it’s not very actively developed. What I’d like to do is this: - Use existing db for user/pass check - Use static files for everything else - Optionally alter my sql query based on which client is asking for auth Is this possible? Right now I don’t need to really return anything other than Access-Accept or Access-Reject - literally any other reply items are ignored. I’m on a fresh install of FreeRadius 3.0.15. Database schema is very simple, columns are: pw_name, pw_domain, pw_passwd, pw_gid (vpopmail, for the curious) It’s not practical to change the schema at this point… Thanks, Charles
Charles Sprickman wrote:
I’m looking through the SQL docs on the wiki, and they seem very complete, but I’m not quite grasping how to accomplish what I want.
Most of those docs pertain to automatic behaviors for using a preconfigured schema which the rlm_sql has special code to handle.
What I’d like to do is this:
- Use existing db for user/pass check - Use static files for everything else - Optionally alter my sql query based on which client is asking for auth
Is this possible?
Most likely. In addition to providing behaviors for auth/acct/etc, rlm_sql also provides what is known as an "xlate". You'd want to configure an additional (named) instance of the sql module by copying sql.conf, chopping some unused bits of it out, adding a name before the first brace, and configuring the parameters to access your db. Then include that file either automatically by putting it in mods_enabled or via an explicit include directory. Henceforth you can refer to that module by the name you gave it rather than "sql" and create as many as you need for as many databases as you use. Now, instead of adding that module as a step in one of the phases (authenticate/authorize/etc) you use an xlate to launch an sql query that evaluates to your password, and shove that password in the Cleartext-Password attribute in the authenticate section before FreeRADIUS needs to check the password. update control { Cleartext-Password := "%{mymodulename:SELECT pw_passwd FROM `mytable` WHERE pw_name = '%{Stripped-User-Name}'" } ... or something like that. You can use unlang expressions/statements to decide which update block to run based on attributes in the request to get different per-user behavior.
On Mar 28, 2018, at 7:55 PM, Brian Julin <BJulin@clarku.edu> wrote:
Charles Sprickman wrote:
I’m looking through the SQL docs on the wiki, and they seem very complete, but I’m not quite grasping how to accomplish what I want.
Most of those docs pertain to automatic behaviors for using a preconfigured schema which the rlm_sql has special code to handle.
What I’d like to do is this:
- Use existing db for user/pass check - Use static files for everything else - Optionally alter my sql query based on which client is asking for auth
Is this possible?
Most likely. In addition to providing behaviors for auth/acct/etc, rlm_sql also provides what is known as an "xlate". You'd want to configure an additional (named) instance of the sql module by copying sql.conf, chopping some unused bits of it out, adding a name before the first brace, and configuring the parameters to access your db. Then include that file either automatically by putting it in mods_enabled or via an explicit include directory.
Henceforth you can refer to that module by the name you gave it rather than "sql" and create as many as you need for as many databases as you use.
I think I ended up with something similar to this. I copied raddb/mods-config/sql/main/mysql/queries.conf into raddb as “sql.conf” and I explicitly included that in mods-enabled/sql. So that’s how I got the config included...
Now, instead of adding that module as a step in one of the phases (authenticate/authorize/etc) you use an xlate to launch an sql query that evaluates to your password, and shove that password in the Cleartext-Password attribute in the authenticate section before FreeRADIUS needs to check the password.
update control { Cleartext-Password := "%{mymodulename:SELECT pw_passwd FROM `mytable` WHERE pw_name = '%{Stripped-User-Name}'" }
... or something like that. You can use unlang expressions/statements to decide which update block to run based on attributes in the request to get different per-user behavior.
I commented out everything but two queries and opted to just do the work in the queries - one to actually auth and one to just sort of show that I can return AV pair: authorize_check_query = "\ SELECT 1 AS id, pw_name AS UserName, 'Crypt-Password' AS Attribute, pw_passwd AS Value, \ ':=' AS Op \ FROM vpopmail \ WHERE pw_name = '%{SQL-User-Name}' AND pw_domain=‘bway.net <http://bway.net/> !(pw_gid & 256)" authorize_reply_query = "\ SELECT 1 AS id, pw_name AS UserName, 'Session-Timeout' AS Attribute, '36400' AS Value, \ ':=' AS Op \ FROM vpopmail \ WHERE pw_name = '%{SQL-User-Name}' AND pw_domain='bway.net’” This will hold me over for now and let me get rid of gnu-radius. What I’m having trouble with still is setting a variable based on the client the request comes from. In the query about you see that weird bitwise camparison (pw_gid & 256). For some clients I want that to be 256, for others 64, etc. Thoughts on that? Not urgent and might never need it, but I’m trying to maintain parity with my prior setup. Thanks, Charles
Charles Sprickman <spork@bway.net> wrote:
I commented out everything but two queries and opted to just do the work in the queries - one to actually auth and one to just sort of show that I can return AV pair:
That's another way to do it.
What I’m having trouble with still is setting a variable based on the client the request comes from. In the query about you see that weird bitwise camparison (pw_gid & 256). For some clients I want that to be 256, for others 64, etc. Thoughts on that? Not urgent and might never need it, but I’m trying to maintain parity with my prior setup.
A few ways tp go about it: One is to Make another copy of your sql instance, give it a different name, and (I don't know what your criteria are) then do something like: if (User-Name ~= /\@something$/) { name_of_256_module } else { name_of_64_module } ...instead of just calling the same module. Another is to either define a local attribute in your dicts file (using the FreeRADIUS internal attribute VSA range), or abuse an unused attribute: if (User-Name ~= /\@something$/) { update control { My-Attrib := 256 } } else { update control { My-Attrib := 64 } } ...and then put a %{control:My-Attrib} in the query string where the number should be. Note the "control" attribute set is the internal one that FreeRADIUS uses to keep state that it does not want to put in the the request or reply or other attribute sets that correspond to actual packets.
On Thu, 2018-03-29 at 02:11 -0400, Charles Sprickman via Freeradius- Users wrote:
What I’m having trouble with still is setting a variable based on the client the request comes from.
In clients.conf set a variable in the client definition, something like client localhost { ipaddr = 127.0.0.1 myvar = "hello" ... } then use this where you need it with %{client:myvar}. -- Matthew
On Mar 29, 2018, at 10:12 AM, Matthew Newton <mcn@freeradius.org> wrote:
On Thu, 2018-03-29 at 02:11 -0400, Charles Sprickman via Freeradius- Users wrote:
What I’m having trouble with still is setting a variable based on the client the request comes from.
In clients.conf set a variable in the client definition, something like
client localhost { ipaddr = 127.0.0.1 myvar = "hello" ... }
then use this where you need it with %{client:myvar}.
This is awesome, thank you. Officially simpler than what I had to do in gnu-radius. Charles
-- Matthew
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Mar 30, 2018, at 1:00 AM, Charles Sprickman via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
This is awesome, thank you. Officially simpler than what I had to do in gnu-radius.
Yup. In addition to GNU RADIUS being crap, it hasn't had a release in 10 years. The *only* reason it was started was to have a "EFF / GNU" version of a RADIUS server. i.e. it was started for political reasons, not technical ones. It was one guy, who didn't do a good job of getting community involvement. And from day 1 it had fewer features and slower development than FreeRADIUS. Which goes a long way to explaining why it died. While I don't mind competition, *incompetent* competition is just annoying. Alan DeKok.
On Mar 30, 2018, at 7:52 AM, Alan DeKok <aland@deployingradius.com> wrote:
On Mar 30, 2018, at 1:00 AM, Charles Sprickman via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
This is awesome, thank you. Officially simpler than what I had to do in gnu-radius.
Yup.
In addition to GNU RADIUS being crap, it hasn't had a release in 10 years.
That’s probably the last time I touched it - for us dialup went away, roaming dialup went away, and usenet is barely used so some old version just sat there until it stopped working. It now just segfaults on startup with no hints in a ktrace dump as to why, so ¯\_(ツ)_/¯ .
The *only* reason it was started was to have a "EFF / GNU" version of a RADIUS server. i.e. it was started for political reasons, not technical ones.
It was one guy, who didn't do a good job of getting community involvement.
He was a nice guy, but it was literally only him. I used to have a FreeBSD box in my home that I let him use to make sure his software worked outside of Linux.
And from day 1 it had fewer features and slower development than FreeRADIUS. Which goes a long way to explaining why it died.
I think prior to gnu-radius I was on Cistron and prior to that Merit (dating myself).
While I don't mind competition, *incompetent* competition is just annoying.
At this point it should just be pulled from all vendor packaging, it’s dead. I think it’s probably time for me to get with the times and figure out what all the cool kids are doing with Radius now that dialup is dead. Looking forward to trying this out for wifi auth. Looking at all the certificate-based stuff and all, it’s shocking how much the software has evolved. Thanks, Charles
Alan DeKok.
On Apr 3, 2018, at 2:28 PM, Charles Sprickman via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
That’s probably the last time I touched it - for us dialup went away, roaming dialup went away, and usenet is barely used so some old version just sat there until it stopped working. It now just segfaults on startup with no hints in a ktrace dump as to why, so ¯\_(ツ)_/¯ .
Maybe upgraded libraries. But still...
I think prior to gnu-radius I was on Cistron and prior to that Merit (dating myself).
I was part of there Merit consortium for a while. We couldn't even get their new releases to *compile*. That's why I got involved with Cistron, and then started FreeRADIUS. So you can blame Merit for all of this.
At this point it should just be pulled from all vendor packaging, it’s dead.
Yeah. Vendors don't like doing that, tho.
I think it’s probably time for me to get with the times and figure out what all the cool kids are doing with Radius now that dialup is dead. Looking forward to trying this out for wifi auth. Looking at all the certificate-based stuff and all, it’s shocking how much the software has evolved.
RADIUS is a lot more complex than it used to be. :( Alan DeKok.
participants (4)
-
Alan DeKok -
Brian Julin -
Charles Sprickman -
Matthew Newton