Documentation > MySQL HowTo
Quick Guide to XTRadius and MySQL
(written by Frederic Faure)
This tutorial will show you how to set up XTRadius through the latest and greatest RPM package, and have XTRadius authenticate a user from a MySQL database.
NOTE! This document is no longer mainted or supported, by Frederic Faure. Please mail questions or comments to the mailing list, or project admins.
Setting up XTRadius
Get the latest RPM from either XTRadius or RPM Find. On my Red Hat 6.2, it does not require additional packages.
To begin playing with XTRadius, default options in /etc/raddb/users are fine. This file lists locally-defined user accounts along with a default profile.
Authenticating Users through /etc/passwd or /etc/shadow
That's the first test you should run to make sure that XTRadius works, before trying to authenticate users through MySQL. The default authentication method specified in /etc/raddb/users is System, ie. either /etc/passwd or ./shadow.
Radtest should return Sending request to server radius, port 1812. radrecv: Reply from host 127.0.0.1 code=2, id=77, length=20, and /var/log/radius.log should contain Auth: Login OK: [myuser] (from nas local/S0).
FYI, /etc/raddb/clients lists the Network Access Systems (NAS's) which are allowed to query XTRadius.
- Create a user through the usual useradd command, and set a password through passwd myuser
- On the same host where XTRadius is installed, type radtest myuser mypasswd localhost localhost testing123.
If you get a Authenticate: from client radius.acme.com - Security Breach: myuser, check /etc/raddb/clients, and make sure that the host localhost is listed there (that's the first localhost in the example). When done, tell XTRadius to re-read its configuration files through /etc/rc.d/init.d/radiusd reload.
Authenticating Users through MySQL
- Install MySQL and the Perl DBI module. Note that the add-on described later works with the stable version of MySQL, namely the 3.22.x tree, but not with the beta version, 3.23.x. Here are the required packages:
- perl-DBD-msql-mysql.i386.rpm (it contains mysql.pm)
- MySQL could have been launched already (ps aux | grep sql). If it isn't, launch it with /etc/rc.d/init.d/mysql start. As prompted by the install script, add a password for the MySQL administrator through /usr/bin/mysqladmin -u root password "test".
- From XTRadius' web site, download and compile Ian M Ellison's radauth-0.4.tar.gz. Until someone comes up with an RPM version, you will have to modify the conf, Makefile, and users files manually. Here's the one I used that match the installation described thus far (only the parameters that need to be changed are included):
The DBUSER/DBPASS must be allowed to create the Accounting database and the Login, Logout, and Users tables within that database. Obviously, this account must allow be allowed to query for users' passwords, and create entries in the Login/Logout tables.
#define DBHOST "localhost"
#define DBUSER "root"
#define DBPASS "test"
#define DBBASE "accounting"
Next, run make createdb, followed by make createtables to create the needed database and tables in MySQL.
Log on to MySQL, and create the test account with a hashed password as protection against prying eyes:
# mysql -u root -ptest accounting
mysql> insert into users values("mysqluser",encrypt("mysqlpassword","AB"),"Just a test account","Active");
Important: Although Paul DuBois mentions the use of PASSWORD() to hash passwords in his otherwise excellent book on MySQL available from NewRiders, and that (on page 458) the algorithm used by this MySQL function is different from Unix's traditional crypt(), MySQL does support crypt() by using ENCRYPT() when creating/updating a user's password.
As explained on page 546, remember to use a salt if you don't want the generated password to be different every time, and that starting with MySQL 3.22.16, salt can be longer than 2 characters. FYI, Ian's radauth.c reads the first two bytes to extract the salt from the password that it reads in MySQL, generates its own hashed password through the crypt() function, and compares the two strings.
Next, rename the original to /etc/raddb/users.orig, create an empty users file, and add the following stuff:
Pay also attention to the trailing , before the Fall-Through line: You must not use a comma on the first line, but you must use one if you have another statement following. Here, "Exec-Program-Wait" is followed by another line in this section, namely "Fall-Through". Therefore, you must add a comma to show Radius that this section is not over yet. I like Windows' and Samba's INI configuration files better...
Note that the users sample that came with the tarball that Ian sent me still contained Ahmad's original entries, namely accounting and authmysql, respectively.
DEFAULT Acct-Status-Type = "Start"
Exec-Program-Account = "/usr/local/bin/radacct %u"
DEFAULT Acct-Status-Type = "Stop"
Exec-Program-Account = "/usr/local/bin/radacct %u %t %f %i %o"
DEFAULT Auth-Type = External
Exec-Program-Wait = "/usr/local/bin/radauth %u",
Fall-Through = Yes
At this point, run make, followed by make install. Have XTRadius re-read its configuration files through /etc/rc.d/init.d/radiusd reload, and run radtest mysqluser mysqlpassword localhost localhost testing123. Hopefully, XTRadius will tell you that the user was authenticated successfully. If you get "Access Denied", make sure that you did use ENCRYPT() with a salt when setting the user's password in MySQL.
What is Radius, and what packages are available?
Development seems to have stalled with Merit (last binary in 1998) and PerlRadius.Radiator is a Perl-based commercial product about which we have heard nice things. Other commercial products are often bundled with a NAS, eg. Lucent NavisRadius 3.0, Funk, JamRadius, Simple SQL Radius, DTC Radius, IEA Software, Vircom Online Platform (VOP) Radius, Nortel Networks BaySecure Access Control and Preside Radius, Shiva Access Manager .
As a summary of the man page for radiusd, a Radius client can be either an Network Access Server at an ISP (more likely) or an actual PC (less likely.) The Radius server returns "access denied", or "access OK" along with an IP address if need be. The source for users' passwords that is fed to the Radius server ranges from a basic /etc/passwd file all the way to SQL servers like MySQL. Since Radius is known as an Authentication, Authorization, and Accounting (AAA) server, all access are logged (into /var/log/radwtmp) to allow for accounting purposes.
Radius uses two processes and two ports, one for authentication, and the other for accounting.
As of August 16th 2K, XTRadius is the only Radius server that I successfully installed and used to authenticate users through MySQL. Other software, either open- or closed-source, would either not install due to missing files or information, or would not work with MySQL right out of the box.
Radtest just sits there trying to connect
# radtest toto test radius.acme.com localhost testing123
Make sure that entries in /etc/hosts match what it says in /etc/raddb/clients, eg. if localhost.localdomain is the first entry in hosts, that's what it should say in clients.
Sending request to server radius.acme.com, port 1812.
(just sitting there...)
[root@linux raddb]# tail /var/log/radius.log
Mon Aug 14 17:21:24 2000: Info: Starting - reading configuration files ...
Mon Aug 14 17:21:24 2000: Info: Ready to process requests.
Mon Aug 14 17:21:44 2000: Error: request from unknown client: linux.acme.com
Mon Aug 14 17:21:44 2000: Error: Authenticate: from client linux.acme.com - Security Breach: toto
Changes to XTRadius' configuration files are not taken into account
Remember to run /etc/rc.d/init.d/radius reload
It doesn't work!
Before calling for help, read the scarce documents available for the different open-source Radius servers, launch XTRadius in debug mode with /usr/sbin/radiusd -xxyz &, and check infos in /var/log/radiusd.log.