OUR SITES NetworkRADIUS FreeRADIUS

SQL Module

The sql module handles configuration for the SQL drivers.

The database schemas and queries are located in subdirectories:

Directory Description

sql/<driver>/main/schema.sql

Schema

sql/<driver>/main/queries.conf

Authorisation and Accounting queries

Driver specific configuration options are located in sql prefix config files in:

mods-config/sql/driver/<driver>

Where <driver> is cassandra, db2, firebird, freetds, null, mysql oracle, postgresql, sqlite and unixodbc.

For authorization queries, the default schema mirrors the functionality of the files module, including check items and reply items. See the users file documentation for information on the format of the check items and reply items.

Configuration Settings

dialect

The dialect of SQL you want to use.

Allowed dialects are:

  • cassandra

  • firebird

  • mysql

  • mssql

  • oracle

  • postgresql

  • sqlite

    driver

    The sub-module to use to execute queries. It should usually match the dialect above.

In some cases, the driver is different from the dialect.

The null driver can be used with any dialect, along with the logfile directive below. It will then write the SQL queries to a log file.

For MS-SQL, there are multiple driver options, as given in the table below. If the databases require syntax different from MS-SQL, please submit bug reports so that we can fix them,

Driver Dialect

db2

mssql

freetds

mssql

null

any

unixodbc

mssql

Include driver specific configuration file if one exists. These are in a separate configuration file in mods-config/sql/driver/<driver>.

Connection info:
radius_db

Database table configuration for everything.

Except for Oracle database.
If you’re using postgresql this can also be used instead of the connection info parameters.
Postgreql doesn’t take tls{} options in its module config like mysql does, if you want to use SSL connections then use this form of connection info parameter.
acct_table1
acct_table2

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.

postauth_table

Allow for storing data after authentication.

authcheck_table
groupcheck_table

Tables containing check items.

authreply_table
groupreply_table

Tables containing reply items.

usergroup_table

Table to keep group info.

read_groups

Read the groups from the database.

If set to yes, we read the group tables unless Fall-Through = no in the reply table. If set to no, we do not read the group tables unless Fall-Through = yes in the reply table.

Default is yes.

read_profiles

Read the user profile attributes from the database.

Following check and reply item handling and, depending on the read_groups option and Fall-Through value, groups processing, the SQL module will treat values of the User-Profile attributes in the control list as additional groups that the user belongs to and repeat the group check and reply processing.

If set to yes, we process the user profiles unless Fall-Through = no in the reply list. If set to no, we do not process the user profiles unless Fall-Through = yes in the reply list.

Default is yes.

logfile

Write SQL queries to a logfile.

This is potentially useful for tracing issues with authorization queries. See also logfile directives in mods-config/sql/main/*/queries.conf. You can enable per-section logging by enabling logfile there, or global logging by enabling logfile here.

Per-section logging can be disabled by setting "logfile = ''"

query_timeout

Set the maximum query duration for cassandra and unixodbc

pool { …​ }

The connection pool is new for 3.0, and will be used in many modules, for all kinds of connection-related activity.

When the server is not threaded, the connection pool limits are ignored, and only one connection is used.

If you want to have multiple SQL modules reuse the same connection pool, use pool = name instead of a pool section.

e.g:

sql sql1 {
 ...
 pool {
   ...
 }
}

# sql2 will use the connection pool from sql1
sql sql2 {
 ...
 pool = sql1
}
start

Connections to create during module instantiation.

If the server cannot create specified number of connections during instantiation it will exit. Set to 0 to allow the server to start without the external service being available.

min

Minimum number of connections to keep open.

max

Maximum number of connections.

If these connections are all in use and a new one is requested, the request will NOT get a connection.

Setting max to LESS than the number of threads means that some threads may starve, and you will see errors like No connections available and at max connection limit.

Setting max to MORE than the number of threads means that there are more connections than necessary.

If max is not specified, then it defaults to the number of workers configured.

spare

Spare connections to be left idle.

Idle connections WILL be closed if idle_timeout is set. This should be less than or equal to max above.
uses

Number of uses before the connection is closed.

0 means "infinite".

retry_delay

The number of seconds to wait after the server tries to open a connection, and fails.

During this time, no new connections will be opened.

lifetime

The lifetime (in seconds) of the connection.

idle_timeout

idle timeout (in seconds).

A connection which is unused for this length of time will be closed.

connect_timeout

Connection timeout (in seconds).

The maximum amount of time to wait for a new connection to be established.

Not supported by:

Driver Description

firebird

Likely possible but no documentation.

oracle

Not possible.

postgresql

Should be set via the radius_db string instead.

  • All configuration settings are enforced. If a connection is closed because of idle_timeout, uses, or lifetime, then the total number of connections MAY fall below min. When that happens, it will open a new connection. It will also log a WARNING message.

  • The solution is to either lower the "min" connections, or increase lifetime/idle_timeout.
group_attribute

The group attribute specific to this instance of rlm_sql.

The "group_membership_query" is used to select which groups the user is a member of.

The module loops over all groups, and places the group name into the "group_attribute".

The group attribute is used in the "authorize_group_check_query" and "authorize_group_check_query" to select entries which match that particular group.

After all groups in SQL have been run, the module looks for &control.User-Profile, and runs those profiles for "authorize_group_check_query" and "authorize_group_check_query". i.e. a user profile is essentially a group.

These group queries can also update &control.User-Profile, which lets you have a group create a "fall through" to a DEFAULT group. However, these groups are not cross-checked against each other. So it is possible to create loops, which is highly not recommended.

If caching is enabled, then the module is done looping over groups, the module adds the names of groups to the control list. The "group_attribute" can then be used to check group membership. That check will be done internally, and will not result in a database lookup. This also means that it is now possible to do group comparisons based on regular expressions.

It is possible to force a dynamic group lookup via the expansion %sql.group(foo). This expansion returns true if the user is a member of that SQL group, and false otherwise.

The SQL-Group attribute is only available after the SQL module has been run.

The name of the group attribute is automatically determined from the module name. By default, the name is SQL-Group. if the module is an instance such as sql sql1 { …​ }, then the name of the group attribute is SQL1-Group.

cache_groups

whether or not we cache the list of SQL groups

The groups are cached in the control list. So any comparisons must be done as &control.SQL-Group = …​

Default is no.

Read database-specific queries.

Not all drivers ship with query.conf or schema.sql files. For those which don’t, please create them and contribute them back to the project.

Default Configuration

sql {
	dialect = "sqlite"
	driver = "${dialect}"
	$-INCLUDE ${modconfdir}/sql/driver/${driver}
#	server = "localhost"
#	port = 3306
#	login = "radius"
#	password = "radpass"
	radius_db = "radius"
#	radius_db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=your_sid)))"
#	radius_db = "dbname=radius host=localhost user=radius password=radpass"
#	radius_db = "host=localhost port=5432 dbname=radius user=radius password=raddpass sslmode=verify-full sslcert=/etc/ssl/client.crt sslkey=/etc/ssl/client.key sslrootcert=/etc/ssl/ca.crt"
	acct_table1 = "radacct"
	acct_table2 = "radacct"
	postauth_table = "radpostauth"
	authcheck_table = "radcheck"
	groupcheck_table = "radgroupcheck"
	authreply_table = "radreply"
	groupreply_table = "radgroupreply"
	usergroup_table = "radusergroup"
#	read_groups = yes
#	read_profile = yes
#	logfile = ${logdir}/sqllog.sql
#	query_timeout = 5
	pool {
		start = 0
		min = 0
#		max =
		spare = 1
		uses = 0
		retry_delay = 30
		lifetime = 0
		idle_timeout = 60
		connect_timeout = 3.0
	}
	group_attribute = "${.:instance}-Group"
#	cache_groups = no
	$INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf
}