6.5 Using MySQL with FreeRADIUS

Many FreeRADIUS users have been toying with interacting radiusd with MySQL, which is a wonderful open source database product. Using a database allows the administrator to query data and produce reports after transactions are complete using a standard language, SQL, which is supported across platforms. Also, a database allows users and passwords to be kept in a central place, and other services can access it and make said database an extensible, complete resource. Additionally, it's a centralized administration point, which reduces the administrative headache of offering a service to the public. This section describes one possible setup to allow FreeRADIUS to authenticate against a user database held inside MySQL.

By using MySQL, you put the contents of the users file inside the database, and instead of storing all of the user information in one file, with separate stanzas for each user, the data will now exist in several different database tables. This majorly improves speed and scalability and offers a modicum of flexibility, too.

First, download, compile, and install MySQL for your RADIUS machine. There are several web resources available to assist you in doing this:

• The MySQL web site (http://www.mysql.com) offers database downloads as well as API information, graphical tools to manage the database, applications contributed by third parties, and complete documentation for the core database product.

• There is also a convenient Windows-based tool to manage a remote MySQL database called SQLion (http://www.exxatools.com/SQLion.html) that will make it easy to create and populate tables. Of course, in lieu of a desktop-based product, there is also the venerable Linux tool, phpMyAdmin (http://www.phpwizard.net/projects/phpMyAdmin/), which can be used over the Web for much the same purpose.

It is imperative that you have the mysql-devel package installed (with headers and libraries included) before compiling and installing FreeRADIUS. If you don't, radiusd will not compile with MySQL support properly.

To begin the rest, follow these steps:

Some users have noted that NTRadPing operates correctly using test authentication requests and replies but completely ignores or malfunctions when sending accounting packets of any type. This is because the accounting process needs the Acct-Session-Time attribute to properly account for the packet. In NTRadPing, ensure you add that attribute (with a value of 9999, perhaps) before sending the accounting stop and start packets.

At this point, your RADIUS setup should respond correctly to test requests. The authentication information is being retrieved from and compared against data in text files. Now let's add MySQL to the fray.

Once MySQL is installed, create the schema for your user database. There exists within the standard FreeRADIUS distribution a command script file that will easily create a SQL database and populate it with the necessary fields. Of course, you can create your own database schema, but for the purposes of this tutorial, I'll assume that you've used the schema creation script included with the FreeRADIUS distribution. You can find this script, db_mysql.sql, in the {unpacked}/src/modules/rlm_sql/drivers/rlm_sql_mysql directory, where {unpacked} is the location in your file system where the unpacked distribution files reside.

There are several ways to run this script. Perhaps the simplest way is to run the script locally on the RADIUS machine from the shell using this command:

mysql -u{root} -p{rootpass} radius < db_mysql.sql

where {root} is the root user or a specific username you've configured for the RADIUS/MySQL interaction, and {rootpass} is that user's password. Make sure to leave off the curly braces; they are added only for clarification. Also note that there is no space between the flag and the data. You can also use the database management tools described earlier; to execute the SQL script, consult the individual product's documentation.

Next, instruct FreeRADIUS that you intend to use SQL for all RADIUS functions. Open /etc/raddb/radiusd.conf in your favorite text editor and make the following changes:

Example 6-1 and Example 6-2 illustrate what the final modifications to the radiusd.conf file should look like.

Example 6-1. The "authorize" section

authorize {
        preprocess
#       counter
#       attr_filter
#       eap
        suffix
        sql
        files
#       mschap
}

Example 6-2. The "accounting" section

accounting {   
#       acct_unique
        detail
#       counter
        unix
        sql
        radutmp
#       sradutmp
}

Next, add the username and password you configured for the MySQL user database (if you've been following the examples to the letter, I've simply used the root user) to /etc/raddb/sql.conf. You can leave the rest of the file alone if you used the automated script to create the database schema. You may want to turn sqltrace on to see the various communications between radiusd and MySQL. The following reflects these modifications to sql.conf:

sql {
        
# Database type
# Current supported are: rlm_sql_mysql, rlm_sql_postgresql,
# rlm_sql_iodbc, rlm_sql_oracle, rlm_sql_unixodbc
driver = "rlm_sql_mysql"

# Connect info
server = "localhost"
login = "root"
password = "rootpass"

# Database table configuration
radius_db = "radius"

# If you want both stop and start records logged to the
# same SQL table, leave this as is.  If you want them in
# different tables, put the start table in acct_table1
# and stop table in acct_table2
acct_table1 = "radacct"
acct_table2 = "radacct"

authcheck_table = "radcheck"
authreply_table = "radreply"
        
groupcheck_table = "radgroupcheck"
groupreply_table = "radgroupreply"
        
usergroup_table = "usergroup"
         
# Remove stale session if checkrad does not see a double login
deletestalesessions = yes
        
# Print all SQL statements when in debug mode (-x)        
sqltrace = yes
sqltracefile = ${logdir}/sqltrace.sql

Despite the previous example, avoid placing your root username and password in the file. I included it above for the sake of simplicity while testing the configuration. Before moving a new database into production, add a non-privileged account and use that login information in this file.

The configuration is almost complete. For any testing of the new database setup to work, you need to add user information so that there is data to authenticate against. Follow these steps to add some general user information with which to test.

Table 6-2, Table 6-3, Table 6-4, and Table 6-5 show some sample data for your new user database.

Table 6-2. Usergroup

ID	UserName	GroupName
1	Jhassell	Dialin
2	Rneis	Staticdial
3	Bgrossman	Suspended
4	Awatson	dialin

Table 6-3. Radcheck

ID	UserName	Attribute	Value	Op
1	Jhassell	Password	Changeme	==
2	Rneis	Password	Thewb	==
3	Bgrossman	Password	Sarah	==
4	Awatson	Password	Moo	==

Table 6-4. Radreply

ID	UserName	Attribute	Value
1	Rneis	Framed-IP-Address	66.26.224.46
2	Bgrossman	Auth-Type	Reject

Table 6-5. Radgroupreply

ID	GroupName	Attribute	Value	Op
34	Dialin	Framed-Compression	Van-Jacobsen-TCP-IP	==
33	Dialin	Framed-Protocol	PPP	==
32	Dialin	Service-Type	Framed-User	==
31	Dialin	Auth-Type	Local	:=
35	Dialin	Framed-MTU	1500	==
36	Staticdialin	Auth-Type	Local	:=
37	Staticdialin	Framed-Protocol	PPP	==
38	Staticdialin	Service-Type	Framed-User	==
39	Staticdialin	Framed-Compression	Van-Jacobsen-TCP-IP	==

With the configuration now complete, restart FreeRADIUS and test your setup using the instructions for using NTRadPing in Chapter 5. Test each of your usernames and ensure that the proper attributes are returned as they're configured in the radreply and radgroupreply tables.

6.5.1 Extending the MySQL Functionality

Now that the basic MySQL support has been installed, configured, and tested, this section will help you enable more advanced features that extend the capabilities of the FreeRADIUS/MySQL combination.

6.5.1.1 Realm support

It is fairly simple to get realm support when using the database model for FreeRADIUS authentication. You need to enable the stripped usernames feature of FreeRADIUS in order for realm support to work. If not, FreeRADIUS passes the full value of the username attribute—jhassell@raleighinternet, for example—and, in this case, the database is not set up to support that. By enabling stripped usernames, FreeRADIUS deletes the @raleighinternet portion of the username, which allows the query against the database to proceed successfully.

To enable the stripped user name functionality:

The realm functionality should work as expected. If you, in fact, do need to distinguish users in your database (only if your usernames are not unique across all realms), then edit the user entries inside the authentication database and disable the realm-stripping feature.

6.5.1.2 Redundancy with MySQL

To introduce a level of fault tolerance and added data integrity security, you may want to consider having two machines running MySQL that replicate the user authentication database between themselves. While it's certainly not a customized high-availability scenario like that covered in Chapter 10, it is a relatively inexpensive and functional way to ensure that some type of contingency plan covers your customers.

To begin, make sure you have two machines running exactly the same version and revision of MySQL. This reduces the complexity of the entire setup and makes it easier to troubleshoot should something go wrong. Determine which machine will act as the master server and which will act as slave (there really is no significance to which machine you select other than the push direction of the replication).

Next, copy the database directories as a set over to the slave machine to ensure both machines already have the databases. Change the ownership of the newly copied directories on the slave machine by executing the following:

chown -R mysql:mysql /path/to/data/directories

You may want to use the mysqldump utility instead to "dump" a copy of the database from the master server to the slave machine—this approach works best if the two servers are running different operating systems. Then open up /etc/my.cnf (or create it if it doesn't exist) on the master server and add the following lines:

[mysqld]
socket=/tmp/mysql.sock
server-id=1
log-bin

Replace /tmp/mysql.sock with the path to your mysql.sock file if it's not located in /tmp.

Finally, grant permission for the slave server to connect and replicate the master server's database by running the following SQL command (where x.x.x.x is the IP address of your slave server and password is a password of your choosing that the slave server will be configured to use):

GRANT FILE ON *.* TO replicate@x.x.x.x IDENTIFIED BY 'password';

Kill the MySQL daemon on the master machine, restart it, and then ensure everything still works as it did before.

Now it's time to configure the slave server. Open /etc/my.cnf (or create it if it doesn't exist) on the slave server and add the following lines:

[mysqld]
socket=/tmp/mysql.sock
server-id=2
master-host=x.x.x.x
master-user=replicate
master-password=password

Replace /tmp/mysql.sock with the path to your mysql.sock file if it's not located in /tmp. Also note that the server-id value must be different than the master computer's ID, and that the master-password value is what you configured in the previous SQL statement that was executed on the master server. Next, kill the MySQL daemon on the slave machine, restart it, and then ensure everything still works as it did before.

Finally, execute the following SQL command to see if the replication is working:

SHOW SLAVE STATUS;

You should see a message indicating the procedure was successful. To test whether the replication functionality is indeed working, change some data on the master—for instance, change a password for a user in the radcheck table. Then query that same record on the slave machine: it should reflect the change, since the replication is instantaneous.