sql (PAM-modules Manual)

6 SQL Authentication and Session Management.

The package provides two modules for SQL authentication and session management: pam_mysql, for MySQL and pam_pgsql for PostgreSQL. Both modules share the same set of options and provide similar functionality.

Connecting to an SQL database requires a set of credentials that cannot be conveniently passed via the command line. Therefore, both SQL modules use a special configuration file to obtain the necessary data. By default, this file is located in the system configuration directory (usually, /usr/local/etc), and is named pam_sql.conf. However, another location can be specified in the command line, using config command line option.

The command line options understood by both modules are:

config=file: Read SQL access credentials from the given file.
use_authtok: Do not prompt the user for password, take it from the saved authentication tokens. This option is useful when this module is not the first in the stack of authentication modules.

6.1 Configuration File.

Configuration file has a simple line-oriented syntax. Empty lines and lines beginning with ‘#’ are ignored. Nonempty lines consist of a keyword and its value, separated by any amount of white space.

Long statements can be split over several lines by placing ‘\’ character at the end of each line, e.g.:

query select password \
      from users \
      where user_name='$user'

Basic configuration statements provide SQL credentials needed for accessing the database:

host hostname: Sets hostname or IP address of the machine where the database is running. If the database is only listening on the local socket (--skip-networking for MySQL, or lack of -i for PostgreSQL), then host should be the name of the local socket.
port number: Sets the SQL port number. This statement is optional. Use it only if your database is running on a port different from the standard.
db database: Sets database name.
login string: Sets SQL user name.
pass password: Sets SQL user password.
default-file file: Name of the MySQL default file, which should be consulted in order to obtain connection parameters and credentials. When specified, the keywords described above become optional.
default-group name: Name of the group in MySQL default file to use. Default is ‘mysql’. This keyword is meaningful only if default-file is given.

6.2 Using SQL modules in authentication stack.

When used in the auth stack, both SQL modules work as follows. First, the module connects to the database using credentials supplied in the configuration file (see the previous section). Then, it retrieves the value of passwd-query from the configuration file and performs PAM item expansion over it (see item expansion). The resulting query is sent to the SQL server. If this query produces a non-empty result, the first column from the first tuple is used as encrypted user password and compared with the supplied authentication token. If it matches, the user is authenticated successfully. The comparison consists of the following checks, performed in that order until one of them returns match or the list is exhausted:

System crypt function.
MySQL password encoding algorithm (for MySQL only)
Compare MD5 sum of the token with the encrypted password.
Compare passwords using LDAP algorithm.
Compare both strings literally (only if allow-plaintext-pass is set in the configuration file.

The following configuration keywords can be used to disable or enable particular stages of the comparison. The value bool should be ‘yes’, ‘true’ or ‘t’ to indicate true. Any other value is taken to mean false.

allow-plaintext-pass bool: The returned password may be plaintext. Without this option, it is supposed to be encrypted using the system crypt function.
allow-ldap-pass bool: The returned password may be a LDAP-style password hash, i.e. the hash value encoded as base-64 and prefixed with a hashing algorithm name in curly braces. This variable is true by default.
allow-md5-pass bool: The returned password may be encrypted using MySQL md5 function. This keyword is specific for pam_mysql.
allow-mysql-pass bool: The returned password may be encrypted using MySQL password function. This keyword is specific for pam_mysql.

6.3 Setting PAM environment from an SQL database.

This is an experimental feature, available when compiled with Linux PAM libraries. It allows to pass some additional information from the database to the application program using PAM environment.

Special configuration keyword setenv-query defines an SQL query for setting the environment. After expanding PAM items (see item expansion), this query is executed and the first tuple (row) is taken from its result. Each column in this tuple creates an environment variable: the column name becomes the name of environment variable, the column value becomes the variable value.

Consider for example, the following SQL table:

CREATE TABLE userprop (
  username varchar(32),
  dir varchar(128),
  uid int,
  gid int
);

which contains, among others, the following data:

("smith", "/var/spool/dir/1", 16, 10000)

Let the configuration file contain this query definition:

setenv-query SELECT dir as home, uid, gid \
             FROM userprop \
             WHERE username='$user'

Now assume that the user ‘smith’ is authenticated using pam_mysql. The setenv-query is executed. Then, after pam_authenticate the PAM environment will contain:

home=/var/spool/dir/1
uid=16
gid=10000

6.4 Using SQL modules for session management.

Both pam_mysql and pam_pgsql can be used for session management. This makes it possible to use your SQL database instead of system wtmp/utmp files, or as a complement to them.

To enable SQL session management, the configuration file must define the following two variables:

session-start-query query: Defines the query to be executed when the session begins.
session-stop-query query: Defines the query to be executed when the session ends.

Before executing, both queries are subject to item expansion (see item expansion).

As an example, consider the following configuration file statements:

session-start-query INSERT INTO acct \
                    (status, username, tty, starttime) \
                    VALUES(0, '$user', now(), '$tty')
session-stop-query  UPDATE acct \
                    SET status=1,
                        sessiontime=age(now(), starttime) \
                    WHERE username='$user'

They assume that the PostgreSQL table ‘acct’ has the following structure:

status int: Status of the record: ‘0’ if the session is active, ‘1’ if it is closed.
username varchar(32): User name.
tty varchar(16): TTY from where the user logged in.
starttime timestamp: Time when the session was started.
sessiontime interval: Duration of the session if status=1.

6.5 Summary of configuration statements.

This section summarizes all available configuration file statements. For each statement it provides a short description and a reference to the section in this manual where it is described.

allow-ldap-pass bool

The returned password may be a LDAP-style password hash, i.e. the hash value encoded as base-64 and prefixed with a hashing algorithm name in curly braces. This variable is true by default. See sql auth.

allow-md5l-pass bool

The returned password may be encrypted using MySQL md5 function. This keyword is specific for pam_mysql. See sql auth.

allow-mysql-pass bool

The returned password may be encrypted using MySQL password function. This keyword can be used only in pam_mysql configuration. See sql auth.

allow-plaintext-pass bool

The returned password may be plaintext. Without this option, it is supposed to be encrypted using the system crypt function. See sql auth.

db database

Sets the database name. See config.

port number

Defines the SQL port number. See config.

login string

Sets the SQL user name. See config.

pass password

Sets the SQL user password. See config.

passwd-query query

Defines the query used to obtain the user’s password from the database. The query is subject to item expansion (see item expansion).

See sql auth, for a detailed description.

session-start-query query

Defines the query to be executed on session start. The query is subject to item expansion (see item expansion). See sql session, for a detailed description.

session-stop-query query

Defines the query to be executed on session stop. The query is subject to item expansion (see item expansion). See sql session, for a detailed description.

setenv-query query

This query is available when the package is compiled with Linux PAM implementation. It allows to select arbitrary data from the database and to store them in PAM environment. The first tuple returned from query is selected, the column names are used as environment variable names, and column values as their values.

The query is subject to item expansion (see item expansion).

See sql setenv, for a detailed description.

This document was generated on August 11, 2021 using makeinfo.

Verbatim copying and distribution of this entire article is permitted in any medium, provided this notice is preserved.

PAM-Modules

A Collection of Modules for PAM