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@stud test 127.0.0.1 0 test Sending Access-Request of id 175 to 127.0.0.1 port 1812 User-Name = "test@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@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@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@stud [sql_radius] sql_set_user escaped user --> 'test@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@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@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-... Sent from the FreeRadius - User mailing list archive at Nabble.com.