4.11 SQL Configuration — ‘raddb/sqlserver’

The ‘raddb/sqlserver’ file configures the connection to SQL server.

The file uses simple line-oriented ‘keyword --- value’ format. Comments are introduced by ‘#’ character.

The ‘sqlserver’ statements can logically be subdivided into following groups: SQL Client Parameters, configuring the connection between SQL client and the server, Authentication Server Parameters, Authorization Parameters, and Accounting server parameters.

4.11.1 SQL Client Parameters

These parameters configure various aspects of connection between SQL client and the server.

interface iface-type

Specifies the SQL interface to use. Currently supported values for iface-type are mysql and postgres. Depending on this, the default communication port number is set: it is 3306 for interface mysql and 5432 for interface postgres. Use of this statement is only meaningful when the package was configured with both ‘--with-mysql’ and ‘--with-postgres’ option.

server string

Specifies the hostname or IP address of the SQL server.

port number

Sets the SQL communication port number. It can be omitted if your server uses the default port.

login string

Sets the SQL user login name.

password password

Sets the SQL user password.

keepopen bool

Specify whether radiusd should try to keep the connection open. When set to no (the default), radiusd will open new connection before the transaction and close it right after finishing it. We recommend setting keepopen to yes for heavily loaded servers, since opening the new connection can take a substantial amount of time and slow down the operation considerably.

idle_timeout number

Set idle timeout in seconds for an open SQL connection. The connection is closed if it remains inactive longer that this amount of time.

4.11.2 Authentication Server Parameters

(This message will disappear, once this node revised.)

These parameters configure the SQL authentication. The general syntax is:

doauth bool

When set to yes, enables authentication via SQL. All auth_ keywords are ignored if doauth is set to no.

auth_db string

Specifies the name of the database containing authentication information.

auth_query string

Specifies the SQL query to be used to obtain user's password from the database. The query should return exactly one string value — the password.

group_query string

Specifies the query that retrieves the list of user groups the user belongs to. This query is used when Group or Group-Name attribute appears in the LHS of a user's or hint's profile.

auth_success_query string

This query is executed when an authentication succeeds. See section Controlling Authentication Probes, for the detailed discussion of its purpose.

auth_failure_query string

This query is executed upon an authentication failure. See section Controlling Authentication Probes, for the detailed discussion of its purpose.

Example of Authentication Server Parameters

Let's suppose the authentication information is kept in the tables passwd and groups.

The passwd table contains user passwords. A user is allowed to have different passwords for different services. The table structure is:

CREATE TABLE passwd (
  user_name           varchar(32) binary default '' not null,
  service             char(16) default 'Framed-PPP' not null,
  password            char(64) 
);

Additionally, the table groups contains information about user groups a particular user belongs to. Its structure is:

CREATE TABLE groups (
  user_name           char(32) binary default '' not null,
  user_group          char(32) 
);

The queries used to retrieve the information from these tables will then look like:

auth_query  SELECT password
            FROM passwd
            WHERE user_name = '%C{User-Name}'
            AND service = '%C{Auth-Data}'

group_query SELECT user_group
            FROM groups
            WHERE user_name = '%C{User-Name}'

It is supposed, that the information about the particular service a user is wishing to obtain, will be kept in Auth-Data attribute in LHS of a user's profile.

4.11.3 Authorization Parameters

These parameters define queries used to retrieve the authorization information from the SQL database. All the queries refer to the authentication database.

check_attr_query string

This query must return a list of triplets:

attr-name, attr-value, opcode

The query is executed before comparing the request with the profile entry. The values returned by the query are added to LHS of the entry. opcode here means one of valid operation codes: ‘=’, ‘!=’, ‘<’, ‘>’, ‘<=’, ‘>=’.

reply_attr_query string

This query must return pairs:

attr-name, attr-value

The query is executed after a successful match, the values it returns are added to the RHS list of the matched entry, and are therefore returned to the NAS in the reply packet.

Example of Authorization Parameters

Suppose your attribute information is stored in a SQL table of the following structure:

CREATE TABLE attrib (
  user_name varchar(32) default '' not null,
  attr      char(32) default '' not null,
  value     char(128),
  op enum("=", "!=", "<", ">", "<=", ">=") default null
);

Each row of the table contains the attribute-value pair for a given user. If op field is NULL, the row describes RHS (reply) pair. Otherwise, it describes a LHS (check) pair. The authorization queries for this table will look as follows:

check_attr_query  SELECT attr,value,op \
                  FROM attrib \
                  WHERE user_name='%u' \
                  AND op IS NOT NULL

reply_attr_query  SELECT attr,value \
                  FROM attrib \
                  WHERE user_name='%u' \
                  AND op IS NULL

Now, let's suppose the ‘raddb/users’ contains only one entry:

DEFAULT Auth-Type = SQL
        Service-Type = Framed-User   

And the attrib table contains following rows:

Then, when the user jsmith is trying to authenticate, the following happens:

1. Radius finds the matching entry (DEFAULT) in the ‘raddb/users’.
2. It queries the database using the check_attr_query. The triplets it returns are then added to the LHS of the profile entry. Thus, the LHS will contain:

   Auth-Type = SQL, NAS-IP-Address = 10.10.10.1, NAS-Port-Id <= 20

3. Radius compares the incoming request with the LHS pairs thus obtained. If the comparison fails, it rejects the authentication. Note that the Auth-Type attributes itself triggers execution of auth_query, described in the previous section.
4. After a successful authentication, Radius queries the database, using reply_attr_query, and adds its return to the list of RHS pairs. The RHS pairs will then be:

   Service-Type = Framed-User, Framed-Protocol = PPP, Framed-IP-Address = 10.10.10.11

   This list is returned to the NAS along with the authentication accept packet.

Thus, this configuration allows the user jsmith to use only NAS 10.10.10.1, ports from 1 to 20 inclusive. If the user meets these conditions, he is allowed to use PPP service, and is assigned IP address 10.10.10.11.

4.11.4 Accounting Parameters

To perform the SQL accounting radiusd needs to know the database where it is to store the accounting information. This information is supplied by the following statements:

doacct bool

When set to yes enables SQL accounting. All acct_ keywords are ignored if doacct is set to no.

acct_db string

Specifies the name of the database where the accounting information is to be stored.

Further, radiusd needs to know which information it is to store into the database and when. Each of five accounting request types (see section Accounting Requests) has a SQL query associated with it. Thus, when radius receives an accounting request, it determines the query to use by the value of Acct-Status-Type attribute.

Following statements define the accounting queries:

acct_start_query string

Specifies the SQL query to be used when Session Start Packet is received. Typically, this would be some INSERT statement (see section Writing SQL Accounting Query Templates).

acct_stop_query string

Specifies the SQL query to be used when Session Stop Packet is received. Typically, this would be some UPDATE statement.

acct_stop_query string

Specifies the SQL query to be executed upon arrival of a Keepalive Packet. Typically, this would be some UPDATE statement.

acct_nasup_query string

Specifies the SQL query to be used upon arrival of an Accounting Off Packet.

acct_nasdown_query string

Specifies the SQL query to be used when a NAS sends Accounting On Packet.

None of these queries should return any values.

Three queries are designed for use by multiple login checking mechanism (see section Multiple Login Checking):

mlc_user_query string

A query retrieving a list of sessions currently opened by the given user.

mlc_realm_query string

A query to retrieve a list of sessions currently open for the given realm.

mlc_stop_query string

A query to mark given record as hung.

4.11.4.1 Writing SQL Accounting Query Templates

Let's suppose you have an accounting table of the following structure:

CREATE TABLE calls (
  status              int(3),
  user_name           char(32),
  event_date_time     datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  nas_ip_address      char(17),
  nas_port_id         int(6),
  acct_session_id     char(16) DEFAULT '' NOT NULL,
  acct_session_time   int(11),
  acct_input_octets   int(11),
  acct_output_octets  int(11),
  connect_term_reason int(4),
  framed_ip_address   char(17),
  called_station_id   char(32),
  calling_station_id  char(32)
);

On receiving the Session Start Packet we would insert a record into this table with status set to 1. At this point the columns acct_session_time, acct_input_octets, acct_output_octets as well as connect_term_reason are unknown, so we will set them to 0:

# Query to be used on session start
acct_start_query     INSERT INTO calls \
                     VALUES(%C{Acct-Status-Type},\
                            '%u',\
                            '%G',\
                            '%C{NAS-IP-Address}',\
                            %C{NAS-Port-Id},\
                            '%C{Acct-Session-Id}',\
                            0,\
                            0,\
                            0,\
                            0,\
                            '%C{Framed-IP-Address}',\
                            '%C{Called-Station-Id}',\
                            '%C{Calling-Station-Id}')

Then, when the Session Stop Packet request arrives we will look up the record having status = 1, user_name matching the value of User-Name attribute, and acct_session_id matching that of Acct-Session-Id attribute. Once the record is found, we will update it, setting

status = 2
acct_session_time = value of Acct-Session-Time attribute
acct_input_octets = value of Acct-Input-Octets attribute
acct_output_octets = value of Acct-Output-Octets attribute
connect_term_reason = value of Acct-Terminate-Cause attribute

Thus, every record with status = 1 will represent the active session and every record with status = 2 will represent the finished and correctly closed record. The constructed acct_stop_query is then:

# Query to be used on session end
acct_stop_query      UPDATE calls \
                     SET status=%C{Acct-Status-Type},\
                         acct_session_time=%C{Acct-Session-Time},\
                         acct_input_octets=%C{Acct-Input-Octets},\
                         acct_output_octets=%C{Acct-Output-Octets},\
                         connect_term_reason=%C{Acct-Terminate-Cause} \
                     WHERE user_name='%C{User-Name}' \
                     AND status = 1 \
                     AND acct_session_id='%C{Acct-Session-Id}' 

Upon receiving a Keepalive Packet we will update the information stored with acct_start_query:

acct_alive_query  UPDATE calls \
                  SET acct_session_time=%C{Acct-Session-Time},\
                      acct_input_octets=%C{Acct-Input-Octets},\
                      acct_output_octets=%C{Acct-Output-Octets},\
                      framed_ip_address=%C{Framed-IP-Address} \
                  WHERE user_name='%C{User-Name}' \
                  AND status = 1 \
                  AND acct_session_id='%C{Acct-Session-Id}'

Further, there may be times when it is necessary to bring some NAS down. To correctly close the currently active sessions on this NAS we will define a acct_nasdown_query so that it would set status column to 2 and update acct_session_time in all records having status = 1 and nas_ip_address equal to IP address of the NAS. Thus, all sessions on a given NAS will be closed correctly when it brought down. The acct_session_time can be computed as difference between the current time and the time stored in event_date_time column:

# Query to be used when a NAS goes down, i.e. when it sends 
# Accounting-Off packet
acct_nasdown_query UPDATE calls \
                   SET status=2,\
                       acct_session_time=unix_timestamp(now())-\
                               unix_timestamp(event_date_time) \
                   WHERE status=1 \
                   AND nas_ip_address='%C{NAS-IP-Address}'

We have not covered only one case: when a NAS crashes, e.g. due to a power failure. In this case it does not have a time to send Accounting-Off request and all its records remain open. But when the power supply is restored, the NAS will send an Accounting On packet, so we define a acct_nasup_query to set status column to 3 and update acct_session_time in all open records belonging to this NAS. Thus we will know that each record having status = 3 represents a crashed session. The query constructed will be:

# Query to be used when a NAS goes up, i.e. when it sends 
# Accounting-On packet
acct_nasup_query   UPDATE calls \
                   SET status=3,\
                       acct_session_time=unix_timestamp(now())-\
                               unix_timestamp(event_date_time) \
                   WHERE status=1 \
                   AND nas_ip_address='%C{NAS-IP-Address}'

If you plan to use SQL database for multiple login checking (see section Multiple Login Checking), you will have to supply at least two additional queries for retrieving the information about currently active sessions for a given user and realm (see section Retrieving Session Data). Each of these queries must return a list consisting of 5-element tuples:

user-name, nas-ip-address, nas-port-id, acct-session-id

For example, in our setup these queries will be:

mlc_user_query SELECT user_name,nas_ip_address,\
                      nas_port_id,acct_session_id \
               FROM calls \
               WHERE user_name='%C{User-Name}' \
               AND status = 1

mlc_realm_query SELECT user_name,nas_ip_address,\
                       nas_port_id,acct_session_id \
                FROM calls \
                WHERE realm_name='%C{Realm-Name}'     

While performing multiple login checking radiusd will eventually need to close hung records, i.e. such records that are marked as open in the database (status=1, in our setup), but are actually not active (See section Verifying Active Sessions, for the description of why it may be necessary). It will by default use acct_stop_query for that, but it has a drawback that hung records will be marked as if they were closed correctly. This may not be suitable for accounting purposes. The special query mlc_stop_query is provided to override acct_stop_query. If we mark hung records with status=4, then the mlc_stop_query will look as follows:

mlc_stop_query UPDATE calls \
               SET status=4,\
                acct_session_time=unix_timestamp(now())-\
                                  unix_timestamp(event_date_time) \
               WHERE user_name='%C{User-Name}' \
                 AND status = 1 \
                 AND acct_session_id='%C{Acct-Session-Id}' 

This document was generated by Sergey Poznyakoff on December, 6 2008 using texi2html 1.78.