Cyrus SASL sql man page

Abstract

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

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'
Patrick Koetter, 07. January 2015