Authcheck table and groupreply table on two different databases
Hello everybody, I’ll present myself as a new subscriber; my name is Paolo, I’m from Italy and I work as sysadm at Università di Urbino. The easy think, the question: it’s possible to setup authcheck_table into a database/host and both groupcheck_table, groupreply_table into a different database/host? Now I’ll try to explain the goal, else if that will be not as easy. Ad maybe someone will lead me to a working solution. Maybe will lead to a quite different approach. We have, of course, down there some nice application to manage things, like student, staff etc.; those applications do have their own proprietary databases and they’re authoritative on fields like anagraphics and else usernames and passwords. In an effort to clean that nightly mess of batch scripts transferring credentials around, we decided to leave data where they live, into authoritative databases. It’s easy to pull username/password from a proprietary database, just know where fields are; but I cannot alter the database’s structure and I need some group related radius attributes, say Session-Timeout. I know what the group is, I just ran a query on a student account so the group is stud. I need now to pass ‘stud’ to an authorize_group_reply_query, but must be on a different database. Any hint? And thanks in advance. Paolo. -- 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.
1839Paolo wrote:
The easy think, the question: it’s possible to setup authcheck_table into a database/host and both groupcheck_table, groupreply_table into a different database/host?
No.
It’s easy to pull username/password from a proprietary database, just know where fields are; but I cannot alter the database’s structure and I need some group related radius attributes, say Session-Timeout. I know what the group is, I just ran a query on a student account so the group is stud. I need now to pass ‘stud’ to an authorize_group_reply_query, but must be on a different database.
Configure two SQL instances. One queries the main server, and sets a group attribute. The other queries the secondary server. Alan DeKok.
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.
participants (2)
-
1839Paolo -
Alan DeKok