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