Section 3.41. <AuthBy SQL>

3.41. <AuthBy SQL>

<AuthBy SQL> authenticates users from an SQL database, and stores accounting records to an SQL database. It is implemented in AuthSQL.pm.

<AuthBy
                     SQL>

is very powerful and configurable, and it has many parameters in order to customise its behaviour. You need to have some familiarity with SQL and relational databases in order to configure and use <AuthBy SQL>.

<AuthBy
                     SQL>

uses the Perl DBI/DBD interface to connect to your database. You can therefore use <AuthBy SQL> with a large number of commercial and free SQL database systems. In order to use SQL, you will need to install your database software, install the matching Perl DBD module, and install the Perl DBI module before <AuthBy SQL> works.

When <AuthBy SQL> receives an Access-Request message, it tries to find a password and check and reply items for the user in a database table (you can change this behaviour with the AuthColumnDef parameter). Radiator constructs an SQL select statement from the AuthSelect parameter. By changing AuthSelect, you can control the table it looks in, and the names of the columns for the password, check and reply columns. If the user is found, all the check items are compared with the attributes in the request, including Expiration and other special check items. For more information about check and reply items, see Section 7. Check and reply items.

If all the check items are satisfied by the attributes in the request, <AuthBy SQL> replies with an Access-Accept message containing all the attributes in the reply items. If the user does not appear in the database, or if any check attribute does not match, an Access-Reject message is sent to the client.

If your AuthSelect statement does not generate a simple password, check items, and reply items as a result, you can configure Radiator how to interpret the columns in the result with the AuthColumnDef parameter. If you do not specify any AuthColumnDef parameters, Radiator assumes that AuthSelect returns password, check items, and reply items in that order.

When <AuthBy SQL> receives an Accounting-Request message, it can store any number of the attributes from the request in an SQL table. You can control the table it stores in, and the names of the columns where the attributes are stored, and the attribute that is stored there. To enable SQL accounting you must define AccountingTable and you must define at least one AcctColumnDef. If you do not do both of these <AuthBy SQL> acknowledges Accounting-Request message but will not store them anywhere. The example goodies/sql.cfg in the Radiator distribution shows a typical setup that will work with the table schemas in goodies/*Create.sql files.

Some example scripts for constructing database tables for various RDBMSs can be found in the goodies/*Create.sql files in the Radiator distribution. Regard these as a starting point for constructing large scalable user and accounting databases.

<AuthBy
                     SQL>

understands also the same parameters as <AuthBy xxxxxx>. For more information, see Section 3.32. <AuthBy xxxxxx>. It supports also all the common SQL configuration parameters. For more information about the SQL configuration parameters, see Section 3.8. SQL configuration.

3.41.1. AuthSelect

This is an SQL select statement that will be used to find and fetch the password and possibly check items and reply items for the user who is attempting to log in. You can use the special formatting characters. %0 is replaced with the quoted and escaped user name. The first column returned is expected to be the password; the second is the check items (if any) and the third is the reply items (if any) (you can change this expectation with the AuthColumnDef parameter). Defaults to "select PASSWORD from SUBSCRIBERS where USERNAME=%0", which does not return any check or reply items. You can make arbitrarily complicated SQL statements so that you will only authenticate users for example whose account status is OK or who have not exceeded their download limit etc. For information about how check items and reply items are used, see Section 7. Check and reply items. If the password (or encrypted password) column for a user is NULL in the database, then any password will be accepted for that user.

The password column may be in any of the formats described in Section 7.1.1. User-Password, Password.

# Check user status is current. No reply items in DB
# Note: The entire statement must be on one line
AuthSelect select PW, CHECK from USERS where NAME=%0 and STATUS = 1

If AuthSelect is defined as an empty string, SQL will not attempt to authenticate at all.

If one or more AuthSelectParam parameters are specified, they will be used in order to replace any fields marked with a question mark in AuthSelect.

Tip

By default, many SQL servers do case-insensitive string comparison. This means that AuthBy SQL, AuthBy RADMIN etc. would match, for example mikem, MIKEM and MiKeM as being the same user. Some SQL databases allow you to force case-sensitive comparisons. For example, In the case of MySQL, the 'BINARY' keyword forces the following comparison to be case-sensitive. Therefore you could force case-sensitive user names in an AuthSQL for MySQL with something like:

AuthSelect select PASSWORD from SUBSCRIBERS where BINARY USERNAME=%0

Tip

You can improve the performance of AuthSelect queries executed by the SQL server with AuthSelectParam.

Note

In the event of a SQL timeout, Radiator will reconnect to the database and the AuthSelect query will be tried again. This means there may be up to 2 Timeout intervals before the entire AuthSelect query fails.

3.41.2. AuthSelectParam

This optional parameter specifies a bind variable to be used with AuthSelect. See Section 3.8.1. SQL bind variables for information about how to use bind variables.

3.41.3. AuthColumnDef

This optional parameter allows you to change the way Radiator interprets the result of the AuthSelect statement. If you do not specify any AuthColumnDef parameters, Radiator assumes that the first column returned is the password; the second is the check items (if any) and the third is the reply items (if any). If you specify any AuthColumnDef parameters, Radiator uses the column definitions you provide.

AuthColumnDef ignores the returned columns if their value is one of the following:

NULL
Empty value
Single NULL octet value

You can specify any number of AuthColumnDef parameters, one for each interesting field returned by AuthSelect. The general format is:

AuthColumnDef n, attributename, type[, formatted]

n is the index of the field in the result of AuthSelect. 0 is the first field.
attributename is the name of the attribute to be checked or replied. The value of the attribute is in the nth field of the result. The special attributename "GENERIC" indicates that it is a list of comma separated attribute=value pairs.
type indicates whether it is a check or reply item. A type of request sets the named attribute in the incoming request, from where it can be retrieved later in the authentication process with special formatting characters.
formatted, if this keyword is present, the value retrieved from the database is subject to special character processing before its value is used, and can therefore contain %{something} forms which are replaced at authentication time. Reply items values are always formatted when they are added to a reply. Therefore there is typically no need to use this flag when the type is reply.

Example

The standard default AuthSelect statement is:

AuthSelect select PASSWORD from SUBSCRIBERS \
      where USERNAME=%0

This returns a single plain text password check item. The result can be interpreted with:

AuthColumnDef 0, User-Password, check

Example

Here is a more complicated AuthSelect statement:

AuthSelect select PASSWORD, CHECKATTR, REPLYATTR \
      from SUBSCRIBERS \
      where USERNAME=%0

The previous example returns 3 fields in the result. The first is a plain text password, the second is a string of check items like "Service-Type=Framed-User, Expiration="Feb 2 1999"", and the third field is a string of reply items like "Framed-Protocol=PPP,Framed-IP-Netmask = 255.255.255.0,OSC-Timestamp=%t,....". Special %-formats are expanded when a reply is constructed. This applies to all attributes, including those have special name "GENERIC". The result can be interpreted with:

AuthColumnDef 0, User-Password, check
AuthColumnDef 1, GENERIC, check
AuthColumnDef 2, GENERIC, reply

Note

If your PASSWORD column contains a Unix encrypted password and you are using AuthColumnDef, you need to set it like this:

AuthColumnDef 0, Encrypted-Password, check

Example

Here is an example AuthSelect statement:

AuthSelect select SERVICE, PASSWORD, MAXTIME 
      from SUBSCRIBERS \
      where USERNAME=%0

This returns 3 fields in the result. The first is a Service-Type to check, the next is a plain text password and the last is the number of seconds to send back in Session-Timeout. The result can be interpreted with:

AuthColumnDef 0, Service-Type, check, formatted
AuthColumnDef 1, User-Password, check
AuthColumnDef 2, Session-Timeout, reply

In this example, column 0 is interpreted for special characters before being used as a check item for the Service-Type parameter.

Example

Here is an example of using NULL values for customising user authorisation:

AuthColumnDef 1, NAS-IP-Address, check
AuthColumnDef 2, Framed-IP-Address, reply

This allows you to restrict certain users so that they can only log in from a certain NAS. The unrestricted users have column 1 set to NULL. Likewise, users with static IP address have non-NULL value in column 2.

3.41.4. AccountingTable

This is the name of the table that will be used to store accounting records. Defaults to “ACCOUNTING”. If AccountingTable is defined to be an empty string, all accounting requests will be accepted and acknowledged, but no accounting data will be stored. You must also define at least one AcctColumnDef before accounting data will be stored.

The AccountingTable table name can contain special formatting characters: table names based on the current year and/or month might be useful, so you can rotate your accounting tables.

# store accounting records in RADUSAGEyyyymm table
AccountingTable RADUSAGE%Y%m

3.41.5. EncryptedPassword

This parameter should be set if and only if your AuthSelect statement will return a bare Unix encrypted password, and you are not using AuthColumnDef. Encrypted passwords cannot be used with CHAP or MSCHAP authentication. If the encrypted password column for a user is NULL in the database, then any password will be accepted for that user. If the encrypted password column for a user is set to the empty string (as opposed to NULL), then no password will be accepted for that user.

EncryptedPassword is a hint to Radiator about how to deal with passwords that have no explicit password type prefix. If EncryptedPassword is not set, bare passwords (i.e. without a prefix) are treated as plaintext passwords. If EncryptedPassword is set, they are treated as a Unix crypt password. If a password has a prefix, then the password will be interpreted according to that type of password, independent of the setting of EncryptedPassword. For more information, see Section 7.1.1. User-Password, Password.

Note

This parameter is ignored if you have defined your own AuthSelect column definitions with AuthColumnDef.

# unix Encrypted password are in CRYPTPW
AuthSelect select CRYPTPW from USERS where N = %0
EncryptedPassword

3.41.6. HandleAcctStatusTypes

This optional parameter specifies a list of Acct-Status-Type attribute values that will be processed in Accounting requests. The value is a comma-separated list of valid Acct-Status-Type attribute values including, Start, Stop, Alive, Modem-Start, Modem-Stop, Cancel, Accounting-On and Accounting-Off. See your dictionary for a full list.

If HandleAcctStatusTypes is specified and an Accounting request has an Acct-Status-Type not mentioned in HandleAcctStatusTypes, then the request will be ACCEPTed but not otherwise processed by the enclosing clause. The default is to handle all Acct-Status-Type values.

# Only process Start and Stop requests, ACCEPT and acknowledge everything else
HandleAcctStatusTypes Start,Stop

3.41.7. AccountingStartsOnly

If this parameter is defined, it forces AuthBy SQL to only log Accounting Start requests to the database. All other Accounting requests are accepted and acknowledged, but are not stored in the SQL database.

Note

This parameter is now deprecated and will not be supported in the future. Use Section 3.38.6. HandleAcctStatusTypes instead.

Note

It does not make sense to set AccountingStartsOnly and AccountingStopsOnly.

3.41.8. AccountingAlivesOnly

If this parameter is defined, it forces AuthBy SQL to only log Accounting Alive requests to the database. All other Accounting requests are accepted and acknowledged, but are not stored in the SQL database.

Note

This parameter is now deprecated and will not be supported in the future. Use Section 3.38.6. HandleAcctStatusTypes instead.

3.41.9. AccountingStopsOnly

If this parameter is defined, it forces AuthBy SQL to only log Accounting Stop requests to the database. All other Accounting requests are accepted and acknowledged, but are not stored in the SQL database.

Note

this parameter is now deprecated and will not be supported in the future. Use Section 3.38.6. HandleAcctStatusTypes instead.

You may want to use this parameter if your accounting system does not use or need Accounting Start to do its billing.

# We only want Stops
AccountingStopsOnly

Note

It does not make sense to set AccountingStartsOnly and AccountingStopsOnly.

3.41.10. DateFormat

This optional parameter specifies the format to be used to format dates for insertion into the AccountingTable. All date formatting characters are permitted. For more information, see Section 3.4. Date formatting. Defaults to

%b %e,
                     %Y %H:%M

which formats to, for example,

Sep 3, 1995
                     13:37

.

# Include seconds in dates in a way that MS-SQL likes
DateFormat %b %e, %Y %H:%M:%S

3.41.11. AcctColumnDef

AcctColumnDef is used to define which attributes in accounting requests are inserted into AccountingTable. It also specifies which column they are inserted into, and optionally the data type of that column. The general form is:

AcctColumnDef Column,Attribute[,Type][,Format]

Column is the name of the SQL column where the data is inserted. Attribute is the name of the RADIUS attribute to store there. Type is an optional data type specifier, which specifies the data type of the SQL column. Format is an optional format string that can be used to format the value. Columns and their values are included in accounting SQL statements in alphabetical order by column name.

The following Type names are recognised:

integer
The insertion is done as an integer data type. RADIUS attributes that have VALUE names defined are inserted as their integer RADIUS value.
integer-date
The attribute value is converted from Unix seconds to an SQL datetime string using the date formatting characters. For more information, see Section 3.4. Date formatting. The Format field is used as the date format (if it is present), otherwise the standard DateFormat parameter for this AuthBy SQL is used (which defaults to the format 'Sep 3, 1995 13:37'). This is useful for inserting the Timestamp attribute as an SQL datetime type. The default is compatible with at least Microsoft SQL and Sybase datetime columns. If it is not suitable for your database, consider defining your own DateFormat parameter for this AuthBy SQL. The resulting value is quoted after conversion.
formatted-date
formatted-date is now deprecated, and new installations should use integer-date instead. It has a much wider range of formatting and date options.

The attribute is converted by Perl TimeDate module Date::Format according to the format string. TimeDate is available from CPAN. For more information, see Section 2.1.2. CPAN. It is most useful for SQL databases with unusual date formats, like Oracle. formatted-date is now only provided for historical reasons, and new installations should probably use integer-date in conjunction with DateFormat instead. The resulting value is not quoted after conversion.
formatted
The attribute field is processed looking for the special characters described in Section 3.3. Special formatters. If the resulting string is empty it is not inserted. This is useful for inserting data from other places besides the current request, such as a GlobalVar you have defined elsewhere, or from a data item that the previous AuthBy put in the current reply packet. The resulting data is quoted. See literal below to generate unquoted data.
literal
Similar to formatted, except that the resulting value is not quoted.
inet_aton
Converts a dotted quad IP address (such as 10.1.1.5) to a 32 bit unsigned integer.
Anything else
Any other type string causes the named RADIUS attribute to be inserted literally as a string. The resulting value is quoted.

You can use formatted-date to create date formats to suit your SQL database. This example inserts the Timestamp into an Oracle DATE or TIMESTAMP type column called TIME_STAMP:

AcctColumnDef TIME_STAMP,Timestamp,formatted-date,\
      to_date('%e %m %Y %H:%M:%S', 'DD MM YYYY HH24:MI:SS')

The insert statement is this:

insert into ACCOUNTING (TIME_STAMP, ......) values 
(to_date('16 02 1999 16:40:02', 'DD MM YYYY HH24:MI:SS'), ....)

For types other than formatted-date and integer-date, the format field can be used to build custom values in your insert statement. This can be very useful to call SQL conversion functions on your data. If you specify a format, it is used as a sprintf-style format, where %s is replaced by your value.

If any named attribute is not present in the accounting request, nothing is inserted in the column for that value. The attribute doe not appear in the insert statement at all, and the SQL server's default value (usually NULL) is used for that column. With some SQL servers, you can change the default value to be used when a column is not specified in an insert statement.

You can have 0 or more AcctColumnDef lines, one for each attribute you want to store in the accounting table. If there are no AcctColumnDef lines, then the accounting table is not updated.

The attribute Timestamp is always available for insertion, and is set to the time the packet was received, adjusted by value of Acct-Delay-Time attribute (if present), as an integer number of seconds since midnight Jan 1, 1970 UTC. The Timestamp attribute is added by Radiator to all received Accounting requests, and is set to the current time according to the host on which the Radiator is running.

Here is an example column configuration:

AcctColumnDef USERNAME,User-Name
AcctColumnDef TIME_STAMP,Timestamp,integer
AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
AcctColumnDef ACCTINPUTOCT,Acct-Input-Octets,integer
AcctColumnDef ACCTOUTPUTOCT,Acct-Output-Octets,integer
AcctColumnDef ACCTSESSIONID,Acct-Session-Id
AcctColumnDef ACCTSESSTIME,Acct-Session-Time,integer
AcctColumnDef ACCTTERMINATECAUSE,Acct_Terminate-Cause
AcctColumnDef NASIDENTIFIER,NAS-Identifier
AcctColumnDef NASPORT,NAS-Port,integer
# Insert date-time without and with seconds
DateFormat %Y-%m-%d %H:%M
AcctColumnDef DATE_TIME,Timestamp, integer-date
AcctColumnDef DATE_TIME_SEC,Timestamp, integer-date, %Y-%m-%d %H:%M:%S

Note

If your accounting table inserts are not working, run Radiator at a trace level of 4, and you see each insert statement logged before it is executed. This helps you determine if your AcctColumnDef lines are correct.

Note

If there are multiple definitions for the same column with non-null values, the last one in the configuration file is used.

Note

SQL table and column names are generally case sensitive, and usually can consist only of letters, digits or the underscore character ‘_’.

Note

You can further customise the accounting insert query with AcctInsertQuery.

Note

The formatted type is useful for inserting values set up in GlobalVars, or to get values from the current reply (possibly put there by a preceding AuthBy).

AcctColumnDef ACCOUNTTYPE,%{Reply:accounttype},formatted
AcctColumnDef SERVERNAME,%{GlobalVar:servername},formatted

Note

You can get SQL to calculate the start time of an accounting packet with something like:

AcctColumnDef START_TIME,%b-0%{Acct-Session-Time},literal

3.41.12. AuthSQLStatement

This parameter allows you to execute arbitrary SQL statements each time an authentication request is received, but before authentication is done.

You can have as many AuthSQLStatement parameters as you like (i.e. 0 or more). Each one will have its special formatting macros replaced at run time (the ones of the format %{attribute-name} are probably the most useful). %0 is replaced with the quoted and escaped user name. They are executed in the order they appear in the configuration file, before AuthSelect is run. They are run even if AuthSelect is empty.

3.41.13. AcctSQLStatement

This parameter allows you to execute arbitrary SQL statements each time an accounting request is received. You might want to do it to handle processing in addition to the normal inserts defined by AcctColumnDef, or you might want to construct a much more complicated SQL statement than AcctColumnDef can handle. You only need this if the accounting definitions provided by AcctColumnDef are not powerful enough.

You can have as many AcctSQLStatement parameters as you like (i.e. 0 or more). Each one will have its special formatting macros replaced at run time (the ones of the format %{attribute-name} are probably the most useful). %0 is replaced with the quoted and escaped user name. They are executed in the order they appear in the configuration file.

AcctSQLStatement delete from ONLINE where SessionID='%{Quote:%{Acct-Session-Id}}'

Note

By having multiple AuthBy SQL clauses, and by using AccountingStartsOnly and AccountingStopsOnly, in conjunction with AcctSQLStatement, you could implement a "who is online" table.

3.41.14. AcctInsertQuery

This optional parameter allows you to customise the exact form of the insert query used to insert accounting data. All date formatting characters are permitted. For more information, see Section 3.4. Date formatting. %0 is replaced with the value of the AccountingTable parameter. %1 is replaced with the comma separated list of column names to be inserted (derived from Acct- ColumnDef), and %2 is replaced by the data values to be inserted for each column in the same order as %1. The columns and values will be in alphabetical order by column name. The default is ‘insert into %0 (%1) values (%2)’.

You can use this to use special features in your SQL server, such as this for MySQL:

# maybe update if this is a duplicate
AcctInsertQuery update or insert into %0 (%1) values (%2)

3.41.15. AcctFailedLogFileName

The name of a file used to log failed Accounting-Request messages in the standard radius accounting log format. If the SQL database insert fails, the accounting message will be logged to the named file. For more information about file format, see Section 9.5. Accounting log file. If no AcctFailedLogFileName is defined, failed accounting messages will not be logged. The default is no logging. The file name can include special formatting characters as described in Section 3.3. Special formatters, which means that, for example, using the %c specifier, you can maintain separate accounting log files for each Client. The AcctFailedLogFileName file is always opened written and closed for each failed insertion, so you can safely rotate it at any time.

The user name that is recorded in the log file is the rewritten user name when RewriteUsername is enabled.

Note

You can change the logging format with AcctLogFileFormat.

Note

You may want to make the filename depend on the date, so you get one missed accounting file per day.

# Log all accounting to a single log file in LogDir
AcctFailedLogFileName %L/misseddetails

3.41.16. AcctLogFileFormat

This optional parameter is used to alter the format of the accounting log file from the standard radius format when AcctLogFileFormatHook is not defined. AcctLogFileFormat is a string containing special formatting characters. It specifies the format for each line to be printed to the accounting log file. A newline will be automatically appended. It is most useful if you use the %{attribute} style of formatting characters (to print the value of the attributes in the current packet).

AcctLogFileFormat %{Timestamp} %{Acct-Session-Id}\
%{User-Name}

3.41.17. AcctLogFileFormatHook

Specifies an optional Perl hook that will is used to alter the format of the failed accounting log file from the standard radius format when defined. The hook must return the formatted accounting record. A newline will be automatically appended. By default no hook is defined and AcctLogFileFormat or the default format is used. The hook parameter is the reference to the current request.

3.41.18. PostAuthSelectHook

This optional parameter allows you to define a Perl function that will be run during the authentication process. The hook will be called after the AuthSelect results have been received, and before Radiator has processed the attributes it is interested in. This hook runs only once even if the DB returns multiple rows. If there are no rows, the hook is not run.

The first argument passed to the hook is a handle to the current AuthBy SQL object. The second argument is the name of the user being authenticated. The third argument is a pointer to the current request. The fourth argument is a pointer to the User object being constructed to hold the check and reply items for the user being authenticated. The fifth argument ($_[4]) is a reference to the @row resulting from AuthSelect.

# Change the flag in the first field to 'Accept' or 'Reject'
PostAuthSelectHook sub{my($self,$name,$p,$user,$row)=@_;\
      my $flag = $row->[0];\
      $row->[0] = $flag ? 'Reject’:Accept';\
      }

3.41.19. GroupMembershipQuery

This optional parameter defines an SQL query which will be used to determine whether a user is a member of a group in order to implement the Group check item. Special characters are supported. %0 is replaced by the user name being checked. %1 is replaced by the group name being checked. You can use bound variables with GroupMembershipQueryParam. GroupMembershipQuery is expected to return a single row, where the first field is the name of the group the user belongs to.

3.41.20. GroupMembershipQueryParam

You can also use GroupMembershipQueryParam to provide bound variables for Group- MembershipQuery. %0 is replaced by the user name being checked. %1 is replaced by the group name being checked.

3.41.21. AcctTotalQuery

This optional parameter defines an SQL query which will be used to determine the total of all session times for a given user. Special characters are supported. %0 is replaced by the user name being checked. It is expected to return a single field containing the total session time in seconds.