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:

  1. Download, compile, and install FreeRADIUS. This process is detailed in Chapter 5. Using MySQL in conjunction with radiusd doesn't call for any special compile-time or install-time flags, so a vanilla installation should function correctly.

  2. Configure the test RADIUS system, also as described in Chapter 5. You will want to add a user in the shell (use the useradd command) to use for authentication purposes. The remainder of this section will assume you created a user "radius" in the system that belongs to a group "radius."

  3. Test the setup using the NTRadPing utility, as described in Chapter 5.

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:

  1. Add sql to the authorize section, between the suffix and files entries.

  2. In the accounting section, between unix and radutmp , add sql to the mix.

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.

  1. In the usergroup table, create entries matching user account names to group names .

  2. In the radcheck table, create entries for each of the usernames you created in Step 1 and specify their passwords in the Password attribute. You may leave the Op field empty.

  3. In the radreply table, match the usernames to the specific attributes to be returned when FreeRADIUS replies to an authentication request.

  4. Finally, inside the radgroupreply , create replies to be matched when requests are made from users inside certain groups. (This step is optional: I will continue with it in this example to demonstrate the functionality, but for simple setups nothing is needed in this table for the database authentication to work properly. Additionally, you may leave the Op field empty.)

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 exampleand, 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:

  1. Open /etc/raddb/sql.conf in your favorite text editor.

  2. Locate the section called query config: username .

  3. Uncomment the line sql_user_name = "%{Stripped-User-Name:-%{User-Name:-none}}";

  4. Comment out the line sql_user_name = "%{User-Name}"

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 machinethis 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 masterfor 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.



Radius
Radius
ISBN: 0596003226
EAN: 2147483647
Year: 2005
Pages: 89

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net