SQL auth against existing database

[Charles Sprickman]

> On Mar 28, 2018, at 7:55 PM, Brian Julin <BJulin at 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