#### 3.2.19 The `sql` Statement ¶

#### Syntax ¶

    sql {
      # Set SQL interface to use.
      interface ‘mysql|odbc|postgres’;
      # SQL server host name.
      host arg;
      # SQL user name.
      user arg;
      # Password for the SQL user.
      passwd arg;
      # SQL server port.
      port arg;
      # Database name.
      db arg;
      # Type of password returned by getpass query.
      password-type ‘plain | hash | scrambled’;
      # Set a field-map for parsing SQL replies.
      field-map list;
      # SQL query returning the user’s password.
      getpass query;
      # SQL query to use for getpwnam requests.
      getpwnam query;
      # SQL query to use for getpwuid requests.
      getpwuid query;
    }

#### Description ¶

The `sql` statement configures access credentials to SQL database and the queries for authentication and authorization.

GNU Mailutils supports three types of SQL interfaces: MySQL, PostgreSQL and ODBC. The latter is a standard API for using database management systems, which can be used to communicate with a wide variety of DBMS.

Configuration: interface type ¶

  Configures type of DBMS interface. Allowed values for type are:

  mysql

    Interface with a MySQL server (http://www.mysql.org).

  odbc

    Use ODBC interface. See http://www.unixodbc.org, for a detailed description of ODBC configuration.

  postgres

    Interface with a PostgreSQL server (http://www.postgres.org).

The database and database access credentials are configured using the following statements:

Configuration: host arg ¶

  The host running the SQL server. The value can be either a host name or an IP address in dotted-quad notation, in which case an INET connection is used, or a full pathname to a file, in which case a connection to UNIX socket is used.

Configuration: port arg ¶

  TCP port the server is listening on (for INET connections). This parameter is optional. Its default value depends on the type of database being used.

Configuration: db arg; ¶

  Name of the database.

Configuration: user arg ¶

  SQL user name.

Configuration: passwd arg; ¶

  Password to access the database.

Configuration: password-encryption arg; ¶

  Defines type of encryption used by the password returned by `getpass` query (see below). Possible arguments are:

  plain

    Password is in plain text.

  crypt
  hash

    Password is encrypted by system `crypt` function (see crypt in crypt(3) man page).

  scrambled

    Password is encrypted by MySQL `password` function.

Configuration: getpwnam query ¶

  Defines SQL query that returns information about the given user. The query is subject to variable expansion (see Configuration Variables). The only variable defined is ‘`$user`’, which expands to the user name.

  The query should return a single row with the following columns:

  name

    User name.

  passwd

    User password.

  uid

    UID of the user.

  gid

    GID of the primary group.

  gecos

    Textual description of the user.

  dir

    User’s home directory

  shell

    User’s shell program.

  The following columns are optional:

  mailbox

    Full pathname of the user’s mailbox. If not returned or NULL, the mailbox is determined using the default algorithm (see Mailbox).

  quota

    Upper limit on the size of the mailbox. The value is either an integer number optionally followed by one of the usual size suffixes: ‘`K`’, ‘`M`’, ‘`G`’, or ‘`T`’ (case-insensitive).

Configuration: getpwuid query ¶

  Defines SQL query that returns information about the given UID. The query is subject to variable expansion (see Configuration Variables). The only variable defined is ‘`$user`’, which expands to the UID.

  The query should return a single row, as described for `getpwnam`.

Configuration: getpass query ¶

  Defines SQL query that returns the password of the given user. The query is subject to variable expansion (see Configuration Variables). The only variable defined is ‘`$user`’, which expands to the user name.

  The query should return a row with a single column, which gives the password. The password can be encrypted as specified by the `password-encryption` statement.

Configuration: field-map list ¶

  Defines a translation map for column names. The list is a list of mappings. Each mapping is a string ‘`name=column`’, where name is one of the names described in getpw column names, and column is the name of the column in the returned row that should be used instead. The effect of this statement is similar to that of SQL `AS` keyword. E.g. the statement

      field-map (uid=user_id);

  has the same effect as using ‘`SELECT user_id AS uid`’ in the SQL statement.