



= SQL Module

The `sql` module handles configuration for the SQL drivers.

The database schemas and queries are located in subdirectories:

[options="header,autowidth"]
|===
| 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
xref:raddb:mods-config/files/users.adoc[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,

[options="header,autowidth"]
|===
| 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.

WARNING: Except for Oracle database.



.If you are using `Oracle` then use this instead.



.If you're using `postgresql` this can also be used instead of the connection info parameters.



NOTE: 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.

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

e.g:

[source,sql]
----
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.

NOTE: 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:

[options="header,autowidth"]
|===
| Driver     | Description
| firebird   | Likely possible but no documentation.
| oracle     | Not possible.
| postgresql | Should be set via the radius_db string instead.
|===



[NOTE]
====
  * 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.

NOTE: 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
}
```
