Section 3.56. <AuthBy SQLRADIUS>

3.56. <AuthBy SQLRADIUS>

This clause proxies requests to a target RADIUS server. The target host is determined by a table lookup in an SQL database. This allows the easy management of large numbers of downstream RADIUS servers, such as in a wholesale ISP. It inherits from both common SQL module and <AuthBy RADIUS>.

<AuthBy SQLRADIUS> runs the HostSelect query to determine the details of the target RADIUS server until either an acknowledgment is received from the target or Num-Hosts is exceeded. This permits fallback radius servers to be configured.

HostSelect can be configured to select the target RADIUS server based on any attribute in the incoming request. The default is the user's Realm, but other possibilities, such as Called-Station-Id may be more useful for your organisation.

Tip

There are example SQL table definitions in the goodies/*.sql scripts. These tables work with the default HostSelect allowing the selection of a target host primary and secondary based on Realm.

Tip

If HostSelect fails to select any rows, <AuthBy SQLRADIUS> attempts to proxy according any <Host xxxxxx> clauses contained within the <AuthBy SQLRADIUS> clause. For more information, see Section 3.43. <Host xxxxxx> within <AuthBy RADIUS>. This permits unknown realms to be proxied to a catchall target server.

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

3.56.1. HostSelect

This parameter defines the SQL statement that is run to determine the details of the target RADIUS server. It is run for each request that is handled by the AuthBy. If no reply is received by the target RADIUS server for a given request, it is rerun to find a secondary server, and so on until either HostSelect returns no more rows, or the number of times exceeds NumHosts.

If HostSelect returns no rows, and if <AuthBy SQLRADIUS> contains <Host xxxxxx> clauses, then the request is proxied according to the <Host> clauses in order, the same as with

<AuthBy
                     RADIUS>

. This is a useful catchall for unknown realms, and could be used to proxy to a GoRemote (GRIC) server or similar.

HostSelect is expected to return at least the target host name/address and the shared secret in that order. Optionally, you can also fetch a number of other columns to control the proxying process, including RetryCount, target ports and such. The columns fetched by HostSelect are used to determine the following <AuthBy RADIUS> Host parameters in this order. Any column that is NULL is ignored.

Target host name or IP address
Secret
AuthPort
AcctPort
Retries
RetryTimeout
UseOldAscendPasswords
ServerHasBrokenPortNumbers
ServerHasBrokenAddresses
IgnoreReplySignature
Failure policy
This is an integer in the range 0 to 4 inclusive that indicates what sort of reply to send to the NAS in the event that proxying fails. You can use this to determine how to handle the failure of a downstream RADIUS server. The integers mean the following replies:
- 0: ACCEPT
- 1: REJECT
- 2: IGNORE
- 3: CHALLENGE
FailureBackoffTime
MaxFailedRequests
MaxFailedGraceTime

For more information about how these attributes are used to control proxying, see Section 3.43. <Host xxxxxx> within <AuthBy RADIUS>.

HostSelect can contain any of the special characters. For more information, see Section 3.3. Special formatters. Also, %0 is replaced by the current host counter for this request. The counter starts with the value of StartHost which defaults to 1. You can therefore use %0 to select a different column each time HostSelect is run. %1 is replaced with SQL quoted realm.

The default value is:

HostSelect select HOST%0, SECRET, AUTHPORT, ACCTPORT,\
RETRIES, RETRYTIMEOUT, USEOLDASCENDPASSWORDS, \
SERVERHASBROKENPORTNUMBERS,SERVERHASBROKENADDRESSES, \
IGNOREREPLYSIGNATURE, FAILUREPOLICY from RADSQLRADIUS \
where TARGETNAME=%1

The default value works with the example tables supplied in goodies/*.sql. Note that this allows for up to 2 target hosts per Realm, primary and secondary, and that the Realm to match goes in the TARGETNAME column.

Note

Details about failure history, backoff times and such are cached within Radiator memory, not in the SQL database.

Example

If you have a simple SQL table with one target host per Realm, <AuthBy SQLRADIUS> contains:

HostSelect select HOST%0, SECRET, AUTHPORT, ACCTPORT, RETRIES,\
RETRYTIMEOUT, USEOLDASCENDPASSWORDS, \
SERVERHASBROKENPORTNUMBERS, SERVERHASBROKENADDRESSES, \
IGNOREREPLYSIGNATURE, FAILUREPOLICY from RADSQLRADIUS where TARGETNAME=?
HostSelectParam %1
NumHosts 1

Example

If you want to choose the target RADIUS server based on Called- Station-Id and Realm, and multiple Called-Station-Ids can map to the same target RADIUS servers, and if the target has a primary and a secondary RADIUS server, you can use the example RADSQLRADIUS and RADSQLRADIUSINDIRECT tables, plus an

<AuthBy
                     SQLRADIUS>

containing:

HostSelect select R.HOST%0, R.SECRET, R.AUTHPORT, \
R.ACCTPORT, R.RETRIES, R.RETRYTIMEOUT, \
R.USEOLDASCENDPASSWORDS, R.SERVERHASBROKENPORTNUMBERS, \
R.SERVERHASBROKENADDRESSES, R.IGNOREREPLYSIGNATURE, \
R.FAILUREPOLICY from RADSQLRADIUS R, RADSQLRADIUSINDIRECT I \
where I.SOURCENAME=? and I.TARGETNAME=R.TARGETNAME
HostSelectParam %{Called-Station-Id}
NumHosts 2

3.56.2. HostSelectParam

This optional parameter specifies a bind variable to be used with HostSelect. %1 is replaced with unquoted realm. For more information, see Section 3.8.1. SQL bind variables.

Here is an example of using HostSelectParam:

# Use bound parameters to improve performance in SQL
HostSelect select HOST%0, SECRET, AUTHPORT, ACCTPORT, RETRIES,\
RETRYTIMEOUT, USEOLDASCENDPASSWORDS, \
SERVERHASBROKENPORTNUMBERS, SERVERHASBROKENADDRESSES, \
IGNOREREPLYSIGNATURE, FAILUREPOLICY from RADSQLRADIUS where TARGETNAME=?
HostSelectParam %1
NumHosts 1

3.56.3. NumHosts

This parameter defines the maximum number of times that HostSelect will be called for as given request. If NumHosts is exceeded for a given request, the proxying of the request fails. Defaults to 2. The current counter is available as %0 in HostSelect.

3.56.4. StartHost

StartHost sets the initial host number. Defaults to 1.

3.56.5. HostColumnDef

This optional parameter allows you to specify an alternate mapping between the fields returned by HostSelect and the parameters used to define the Host. If HostColumnDef is not specified, the mapping is the default as described in Section 3.56.1. HostSelect.

The format of HostColumnDef is:

HostColumnDef n,paramspec

Where n is the column number of the fields as returned by HostSelect (starting at 0), and paramspec may be one of the following:

Host
FailurePolicy
any valid Host parameter as described in the Host clause. For more information, see Section 3.43. <Host xxxxxx> within <AuthBy RADIUS>. Also StripFromRequest, RewriteUsername, and AddToRequest are usable

In the following example, HostSelect returns five fields. The first defines the Host name or address, the second is the shared secret for that host, the third is the maximum retry count, and the fourth is the failure policy. The last is a comma-separated list of reply items that is added to the reply.

HostSelect select HOST%0, SECRET, RETRIES, FAILUREPOLICY, ADDTOREQUEST \
           from RADSQLRADIUS where TARGETNAME=?
HostSelectParam %1

HostColumnDef 0, Host
HostColumnDef 1, Secret
HostColumnDef 2, Retries
HostColumnDef 3, failurePolicy
HostColumnDef 4, AddToRequest

Tip

If a Host has FailurePolicy defined, and NoReplyHook is defined, then NoReplyHook is run before the automatic replies are sent.