Section 3.16. <ClientListSQL>

3.16.1. GetClientQuery

This parameter specifies the SQL query that is used to fetch client details from the SQL database specified by DBSource. The database can store all the same parameters that are used to configure a <Client> clause. For more information, see Section 3.14. <Client xxxxxx>. The recommend configuration with Radiator 4.24 and later is to use ClientColumnDef with GetClientQuery. For more information about ClientColumDef, see Section 3.16.2. ClientColumnDef.

The default GetClientQuery works with the sample database schemas provided in the goodies/ of your Radiator distribution. GetClientQuery defaults to:

select NASIDENTIFIER,SECRET,IGNOREACCTSIGNATURE,DUPINTERVAL,
            DEFAULTREALM,NASTYPE,SNMPCOMMUNITY,LIVINGSTONOFFS,
            LIVINGSTONHOLE,FRAMEDGROUPBASEADDRESS,
            FRAMEDGROUPMAXPORTSPERCLASSC,REWRITEUSERNAME,
            NOIGNOREDUPLICATES,PREHANDLERHOOK from RADCLIENTLIST

Your database table must include at least the first and second fields, which are the NAS name or IP address or MAC address and the shared secret. All the other fields are optional.

When ClientColumnDef is not configured, the other fields must occur in the given order. When they occur, they are used to initialise the Client parameter of the same name as shown above. The FRAMEDGROUPBASEADDRESS column may contain multiple comma-separated base addresses. The PREHANDLERHOOK column can contain either the text of a hook or a hook filename in the form ‘file:/path/to/hook’. You can customise the GetClientQuery select clause to have additional fields. If they are present in the result of GetClientQuery, they are used as described below. Field number 0 as the first field, so for example Identifier, field 14 has an index of 14, but is the 15th entry in the returned array.

Identifier field as field 14
DefaultReply as field 15
FramedGroup as field 16
StripFromReply as field 17
AllowInReply as field 18
AddToReply as field 19
AddToReplyIfNotExist as field 20
DynamicReply as field 21
AddToRequest as field 22
StripFromRequest as field 23
AddToRequestIfNotExist as field 24
ClientHook as field 25
UseContentsForDuplicateDetection as field 26
A comma-separated list of flag names as field 27. Each comma-separated name in the field is used to set a Client flag type parameter. For example, if field 27 has the value "IgnoreAcctSignature,UseOldAscendPasswords,StatusServerShowClientDetails", it sets the IgnoreAcctSignature, UseOldAscendPasswords, and StatusServerShowClientDetails flag parameters in the resulting Client.
TACACSPLUSKey as field 28

Here is an example that fetches the required information and DefaultRealm:

# Our custom client table only has NAS identifier,
# shared secret and default realm in it:
GetClientQuery select NAME,SECRET,NULL,NULL,DREALM from CLIENTS

Here is the same example with ClientColumnDef:

# We do not need to pad with NULL columns
GetClientQuery select NAME,SECRET,DREALM from CLIENTS
ClientColumnDef 0, Name
ClientColumnDef 1, Secret
ClientColumnDef 2, DefaultRealm

3.16.2. ClientColumnDef

This optional parameter allows you to specify an alternate mapping between the fields returned by GetClientQuery and the parameters used to define a Client. If ClientColumnDef is not specified, the mapping is the default as described in Section 3.16.1. GetClientQuery

The format of ClientColumnDef is:

ClientColumnDef n,clientparamname

where n is the column number of the fields as returned by GetClientQuery (starting at 0), and clientparamname is the name of the Client clause parameter or special value GENERIC.

For more information about Client parameters, see Section 3.14. <Client xxxxxx>. There must be at least two, and usually there are multiple, ClientColumnDef parameters. The only ones that must be provided are for Name and Secret. If the specified column has NULL value, then the matching clientparamname will not be set and will assume its default value according to the normal behaviour of the Client clause.

Special clientparamname GENERIC indicates that the column is a list of comma separated clientparamname=value pairs. For examples of this format, see AuthColumnDef in AuthBy SQL

Here is a minimal example. It uses the sample database schema provided in the goodies/ of your Radiator distribution.

GetClientQuery select NASIDENTIFIER, SECRET from RADCLIENTLIST
ClientColumnDef 0, Name
ClientColumnDef 1, Secret

Here is an example that sets Identifier parameter, see Section 3.14.14. Identifier. Column 3 must contain name=value pairs, for example: StatusServer=minimal,RequireMessageAuthenticator=1.

GetClientQuery select NASIDENTIFIER, SECRET, CLIENT_IDENTIFIER, GENERIC \
                      from RADCLIENTLIST
ClientColumnDef 0, Name
ClientColumnDef 1, Secret
ClientColumnDef 2, Identifier
ClientColumnDef 3, GENERIC

3.16.3. RefreshPeriod

If this optional parameter is set to non-zero, it specifies the time period in seconds that ClientListSQL will refresh its client list by rereading the database. If set to 0, then ClientListSQL will only read the client list from the database once at startup and on SIGHUP. Defaults to 0. The % formats are permitted.

When the RefreshPeriod expires and the list of clients is read from the SQL database, any Clients previously created by this ClientList are cleared and a new set of clients read from the database. This means that Clients defined in the configuration file will not be removed. It also means that multiple ClientListSQL clauses with non-zero RefreshPeriods will not remove each others Clients.

# Reread the client list every hour
RefreshPeriod 3600

3.16.4. DisconnectAfterQuery

This optional parameter causes the SQL database to be disconnected after each database query. This can be helpful in cases where firewalls etc close connections that have been idle for a long time.

3.16.5. ConnectionHook

This optional parameter specifies a Hook that is run every time this clause connects or reconnects to the SQL database. This is most useful for executing func() to configure the database connection in customised ways. The hook is called with 2 arguments. The first is a reference to the clause object that is making the connection. The second argument is the DBH handle to the newly connected database.

In the following example, the hook calls DBI func() to configure an Interbase database connection for custom requirements:

ConnectionHook sub {$_[1]->func(-access_mode => 'read_write',\
      -isolation_level => 'read_committed',\
      -lock_resolution => 'wait',\
      'ib_set_tx_param')}

3.16.6. ConnectionAttemptFailedHook

You can run this hook whenever Radiator attempts to connect to an SQL database and fails to connect. The default is to log the failure. The hook is called with 4 arguments: $object, $dbsource, $dbusername, $dbauth. $object is the SqlDb object trying to connect. The other parameters are the currently used values for DBSource, DBUsername, and DBAuth.

In the following example the default hook is replaced with a hook that logs unobscured password.

ConnectionAttemptFailedHook sub { \
 my $self = $_[0]; my $dbsource = $_[1]; \
 my $dbusername = $_[2]; my $dbauth = $_[3]; \
 $self->log($main::LOG_ERR, "Could not connect to SQL database with DBI->connect \
            $dbsource, $dbusername, $dbauth: $@ $DBI::errstr"); }

3.16.7. NoConnectionsHook

You can run this hook whenever Radiator fails connect to any SQL server. The default is to log the failure. The hook is called with 1 argument: $object. $object is the SqlDb object that was trying to connect.

In the following example the default hook is replaced with a hook that logs a very short message.

NoConnectionsHook sub { \
  my $self = $_[0]; \
  $self->log($main::LOG_ERR, "Could not connect to any SQL database"); }

3.16.8. FarmWorkerSpacing

If this optional parameter is set to non-zero, it specifies the time in seconds for spacing out refresh done by server farm workers. Defaults to not set which causes all farm workers to refresh client list at the same moment. This parameter has only effect when both RefreshPeriod and global FarmSize parameters are configured.

# Reread the client list every hour, use 30 second offset between each worker
RefreshPeriod 3600
FarmWorkerSpacing 30

3.16. <ClientListSQL>

Tip