Authcheck table and groupreply table on two different databases

1839Paolo 1839 at uniurb.it
Sun Feb 28 14:58:19 CET 2010


Here I’m again. Ty for the hint. And sorry this will be a looong post. So I
want to send a big thanks-you to everyone in advance for reading.

Paolo.


Alan DeKok-2 wrote:
> 
> 
>   Configure two SQL instances.  One queries the main server, and sets a
> group attribute.  The other queries the secondary server.
> 
>   Alan DeKok.
> -
> 


I’m happy  to report I’ve nearly landed. But still having trouble on
re-using on the second instance the attribute/variable set in the first
instance. Looks like I’m missing the syntax to do something like 


        group_membership_query = "SELECT groupname \
          FROM ${usergroup_table} \
          WHERE username = '%{Sql-Group}' \
          ORDER BY priority"


Or whatever variable I try to set up via the first instance. Always expand
to void …


The full story.

I set up a test environment with FreeRADIUS Version 2.1.8 (Debian Squeeze)
and four mysql databases. For the sake of simplicity anything is on the same
host.

Realm @stud main database stud, secondary database radius
Realm @staff main database staff, secondary database radius
Realm @affiliate main database affiliate, secondary database radius

Let we talk about @stud realm.

sql sql_stud {
        database = "mysql"
        driver = "rlm_sql_${database}"

        # Connection info:
        server = "localhost"
        login = "radius"
        password = "************"

        radius_db = "stud"

        authcheck_table  = "radcheck"
        authreply_table  = "radcheck"
        usergroup_table  = "radcheck"
        groupcheck_table = "radcheck"
        groupreply_table = "radcheck"

        read_groups = yes
        deletestalesessions = yes
        sqltrace = no
        sqltracefile = ${logdir}/sqltrace.sql
        num_sql_socks = 5
        connect_failure_retry_delay = 60
        nas_table = "nas"

        # Read driver-specific configuration
        $INCLUDE sql/${database}/dialup.stud
}

Notice I only have radcheck table onboard that database and just containing
username/password pair.

dialup.stud follow

desktop:/etc/freeradius# more sql/mysql/dialup.stud 

        sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}}"

        authorize_check_query = "SELECT id, username, attribute, value, op \
          FROM ${authcheck_table} \
          WHERE username = '%{SQL-User-Name}' \
          ORDER BY id"

        authorize_reply_query = "SELECT id, username, attribute, value, op \
          FROM ${authreply_table} \
          WHERE username = '%{SQL-User-Name}' \
          ORDER BY id"

        group_membership_query = "SELECT \"stud\" \
          FROM ${usergroup_table} \
          WHERE username = '%{SQL-User-Name}' \
          ORDER BY id"

        authorize_group_check_query = "SELECT
'001','stud','Simultaneous-Use','001',':=' \
          FROM ${groupcheck_table} WHERE username = '%{SQL-User-Name}' "
        
        authorize_group_reply_query = "SELECT
'001','stud','Session-Timeout','3600',':=' \
          FROM ${groupreply_table} WHERE username = '%{SQL-User-Name}' "
#

Notice I HARD CODED that damn ‘stud’ group in group_membership_query
Notice also I hard-resolve some attributes, just as a test.


About sql sql_radius nothing to say, it’s quite the standard stuff

The key is dialup.radius

        group_membership_query = "SELECT groupname \
          FROM ${usergroup_table} \
          WHERE username = 'stud' \
          ORDER BY priority"

Notice, again, I hard-coded the word ‘stud’. As said, I’m unable to use a
variable here.
Notice: looks like I don’t need to resolve username, thanks God, but must
resolve radusergroup, else the module will exit without querying
radgroupcheck and radgroupreply.


How it works: 

mysql> use radius;
Database changed
mysql> 
mysql> select * from radcheck;
Empty set (0.00 sec)

mysql> select * from radusergroup;
+-----------+-----------+----------+
| username  | groupname | priority |
+-----------+-----------+----------+
| stud      | stud      |        1 |
| affiliate | affiliate |        1 |
| staff     | staff     |        1 |
+-----------+-----------+----------+


and of course now I’m able to resolve attributes via tables into the
secondary database.

On the end, I attach debug for both a successfoul query and a failing one.

Success, when I hard-code ‘stud’ in dialup.radius

desktop:/etc/freeradius# radtest test at stud test 127.0.0.1 0 test
Sending Access-Request of id 175 to 127.0.0.1 port 1812
        User-Name = "test at stud"
        User-Password = "test"
        NAS-IP-Address = 192.168.2.3
        NAS-Port = 0
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=175,
length=38
        Session-Timeout = 3600
        Acct-Interim-Interval = 600
        Idle-Timeout = 300





rad_recv: Access-Request packet from host 127.0.0.1 port 56744, id=175,
length=61
        User-Name = "test at stud"
        User-Password = "test"
        NAS-IP-Address = 192.168.2.3
        NAS-Port = 0
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
[suffix] Looking up realm "stud" for User-Name = "test at stud"
[suffix] Found realm "stud"
[suffix] Adding Stripped-User-Name = "test"
[suffix] Adding Realm = "stud"
[suffix] Authentication realm is LOCAL.
++[suffix] returns ok
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
++[unix] returns notfound
[files] users: Matched entry DEFAULT at line 126
++[files] returns ok
++[expiration] returns noop
++[logintime] returns noop
[pap] WARNING! No "known good" password found for the user.  Authentication
may fail because of this.
++[pap] returns noop
Using Autz-Type stud
+- entering group stud {...}
[sql_stud]      expand: %{Stripped-User-Name} -> test
[sql_stud]      expand: %{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}}
-> test
[sql_stud] sql_set_user escaped user --> 'test'
rlm_sql (sql_stud): Reserving sql socket id: 4
[sql_stud]      expand: SELECT id, username, attribute, value, op          
FROM radcheck           WHERE username = '%{SQL-User-Name}'           ORDER
BY id -> SELECT id, username, attribute, value, op           FROM radcheck          
WHERE username = 'test'           ORDER BY id
[sql_stud] User found in radcheck table
[sql_stud]      expand: SELECT id, username, attribute, value, op          
FROM radcheck           WHERE username = '%{SQL-User-Name}'           ORDER
BY id -> SELECT id, username, attribute, value, op           FROM radcheck          
WHERE username = 'test'           ORDER BY id
[sql_stud]      expand: SELECT "stud"           FROM radcheck          
WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT "stud"          
FROM radcheck           WHERE username = 'test'           ORDER BY id
[sql_stud]      expand: SELECT '001','stud','Simultaneous-Use','001',':='          
FROM radcheck WHERE username = '%{SQL-User-Name}'  -> SELECT
'001','stud','Simultaneous-Use','001',':='           FROM radcheck WHERE
username = 'test' 
[sql_stud] User found in group stud
[sql_stud]      expand: SELECT '001','stud','Session-Timeout','3600',':='          
FROM radcheck WHERE username = '%{SQL-User-Name}'  -> SELECT
'001','stud','Session-Timeout','3600',':='           FROM radcheck WHERE
username = 'test' 
rlm_sql (sql_stud): Released sql socket id: 4
++[sql_stud] returns ok
[sql_radius]    expand: %{User-Name} -> test at stud
[sql_radius] sql_set_user escaped user --> 'test at stud'
rlm_sql (sql_radius): Reserving sql socket id: 4
[sql_radius]    expand: SELECT id, username, attribute, value, op          
FROM radcheck           WHERE username = '%{SQL-User-Name}'           ORDER
BY id -> SELECT id, username, attribute, value, op           FROM radcheck          
WHERE username = 'test at stud'           ORDER BY id
[sql_radius]    expand: SELECT groupname           FROM radusergroup          
WHERE username = 'stud'           ORDER BY priority -> SELECT groupname          
FROM radusergroup           WHERE username = 'stud'           ORDER BY
priority
[sql_radius]    expand: SELECT id, groupname, attribute,           Value, op          
FROM radgroupcheck           WHERE groupname = '%{Sql-Group}'          
ORDER BY id -> SELECT id, groupname, attribute,           Value, op          
FROM radgroupcheck           WHERE groupname = 'stud'           ORDER BY id
[sql_radius] User found in group stud
[sql_radius]    expand: SELECT id, groupname, attribute,           value, op          
FROM radgroupreply           WHERE groupname = '%{Sql-Group}'          
ORDER BY id -> SELECT id, groupname, attribute,           value, op          
FROM radgroupreply           WHERE groupname = 'stud'           ORDER BY id
rlm_sql (sql_radius): Released sql socket id: 4
++[sql_radius] returns ok
WARNING: Please update your configuration, and remove 'Auth-Type = Local'
WARNING: Use the PAP or CHAP modules instead.
User-Password in the request is correct.
+- entering group session {...}
[radutmp]       expand: /var/log/freeradius/radutmp ->
/var/log/freeradius/radutmp
[radutmp]       expand: %{User-Name} -> test at stud
++[radutmp] returns ok
+- entering group post-auth {...}
++[exec] returns noop
Sending Access-Accept of id 175 to 127.0.0.1 port 56744
        Session-Timeout := 3600
        Acct-Interim-Interval := 600
        Idle-Timeout := 300
Finished request 0.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 0 ID 175 with timestamp +15
Ready to process requests.



Don’t access attributes if 
        group_membership_query = "SELECT groupname \
          FROM ${usergroup_table} \
          WHERE username = '%{SQL-Group-Name}' \
          ORDER BY priority"

Or whatever else I tried

[sql_radius] WARNING: Unknown module "SQL-Group-Name" in string expansion
"%'           ORDER BY priority"
[sql_radius] xlat "SELECT groupname           FROM radusergroup          
WHERE username = '%{SQL-Group-Name}'           ORDER BY priority" failed.
[sql_radius] Error retrieving group list
[sql_radius] Error processing groups; rejecting user



-- 
View this message in context: http://old.nabble.com/Authcheck-table-and-groupreply-table-on-two-different-databases-tp27714401p27735104.html
Sent from the FreeRadius - User mailing list archive at Nabble.com.





More information about the Freeradius-Users mailing list