Description
This document describes configuration options for the Cyrus SASL auxiliary property plugin sql.
sql is a generic plugin for various SQL backends. Currently it provides access to either MySQL, PostgreSQL or SQLite databases.
Note
The plugin requires that passwords in the database are stored in plaintext format in order to use shared-secret mechanisms.
Configuration Syntax
The following syntax is mandatory for sql plugin configuration:
- SQL statements specified with sql_select, sql_select and sql_select must not be enclosed in quotes.
- Macros, e.g. %u, %r and %v, specified within SQL statements must be quoted individually.
See ? for a valid configuration example.
Options
The following configuration parameters are applicable in the context of the sql plugin:
- sql_engine (default: mysql)
Specifies the type of SQL engine to use for connections to the SQL backend. The following types are available:
- mysql
- Enables the mysql driver for connections to a MySQL server.
- pgsql
- Enables the pgsql driver for connections to a PostgreSQL server.
- sqlite
- Enables the sqlite driver for connections to a SQLite 2 database.
- sqlite3
- Enables the sqlite driver for connections to a SQLite 3 database.
- sql_hostnames (default: empty)
A comma-separated list of one or more SQL servers the plugin should try to connect to and query from. Specify servers separated in hostname[:port] format.
Note
Specify localhost when using the MySQL engine to communicate over a UNIX domain socket and 127.0.0.1 to attempt a connection that uses a TCP socket.
Note
This option will be ignored if sql_engine is set to either sqlite or sqlite3.
- sql_user (default empty)
Configures the username the plugin will send when it authenticates to the SQL server.
Note
This option will be ignored if sql_engine is set to either sqlite or sqlite3.
- sql_passwd (defaults: empty)
Configures the password the plugin will send when it authenticates to the SQL server.
Note
This option will be ignored if sql_engine is set to either sqlite or sqlite3.
- sql_database (default: empty)
- Specifies the name of the database which contains auxiliary properties (e.g. username, realm, password etc.)
- sql_select (default: empty)
- Mandatory SELECT statement used to fetch properties from the SQL database.
- sql_insert (default: empty)
- Optional INSERT statement used to create properties for new users in the SQL database.
- sql_update (default: empty)
- Optional UPDATE statement used to modify properties in the SQL database.
- sql_usessl (default: no)
Specify either yes, on, 1 or true, and the plugin will try to establish a secure connection to the SQL server.
Note
This option is available for MySQL backends only. It will be ignored if sql_engine is set to either sqlite or sqlite3.
Macros
The sql plugin provides macros to build sql_select, sql_select and sql_select statements. They will be replaced with arguments sent from the client. The following macros exist:
- %u
- The name of the user whose properties are being selected, inserted or updated.
- %p
- The name of the property being selected, inserted or updated. While this could technically be anything, Cyrus SASL will try userPassword and cmusaslsecret (where MECHNAME is the name of a SASL mechanism).
- %r
- Name of the realm to which the user belongs. This could be the KERBEROS realm, the FQDN of the computer the SASL application is running on or whatever is after the @ on a username.
- %v
Value of the property (generally userPassword) being stored during insert or update operations.
Note
This option will be ignored if sql_engine is set to either sqlite or sqlite3.
Example
The following example shows a typical sql configuration:
# GENERIC options pwcheck_method: auxprop auxprop_plugin: sql mech_list: plain login cram-md5 digest-md5 # SQL auxprop plugin options sql_engine: pgsql sql_hostnames: 127.0.0.1, 192.0.2.1 sql_user: username sql_passwd: secret sql_database: company sql_select: SELECT password FROM users WHERE user = '%u'@'%r'