SQL Database

Stalwart can authenticate users and look up account metadata against an SQL database such as PostgreSQL, MySQL, or SQLite. This makes it possible to reuse an existing SQL-backed user directory, or to manage accounts in a relational schema that other applications already share.

An SQL directory is configured through the SQL variant of the Directory object (found in the WebUI under Settings › Authentication › Directories).

Account provisioning

Accounts are not copied from the database in advance. Stalwart materialises a local account the first time it needs one, either when the user authenticates or when a message arrives for their address and queryRecipient confirms it as a valid recipient. Because that query can be run at any time, an account that has never signed in can still receive mail.

Removal is the case this does not cover. Just-in-time provisioning never deletes anything: a row removed from the accounts table stops matching queries, but the mailbox Stalwart created from it remains, keeps accepting mail, and keeps counting against licensed mailbox limits.

Where that matters, SCIM provisioning can be enabled per domain so that the identity provider suspends and deletes accounts explicitly. Doing so makes SCIM authoritative for the accounts in that domain and stops just-in-time synchronisation from writing to them. Provisioning models compares the two and covers how authority is assigned.

Configuration

The main fields on the SQL variant of the Directory object are:

• description: a human-readable description of this directory.
• store: the backend where the account tables live. The SqlAuthStore variant selects between Default (the server’s data store, when that is itself SQL), PostgreSql, MySql, and Sqlite. Each backend variant carries its own connection parameters (host, port, database, username, and secret) detailed on the Directory reference page.

Directory queries

The SQL variant runs the following queries against the configured store; all of them use positional parameters ($1, $2, …) and sensible defaults that can be overridden per deployment:

• queryLogin: resolves account details by login value. Default "SELECT name, secret, description, type FROM accounts WHERE name = $1".
• queryRecipient: resolves account details by recipient email address or alias. Default "SELECT name, secret, description, type FROM accounts WHERE name = $1 AND active = true".
• queryMemberOf: returns the groups an account is a member of. Default "SELECT member_of FROM group_members WHERE name = $1".
• queryEmailAliases: returns the email aliases of an account. Default "SELECT address FROM emails WHERE name = $1".

Column mappings

Column names in the database are mapped to account fields through the following fields on the SQL variant:

• columnEmail: column holding the account login / primary address. Default "name".
• columnSecret: column holding the password hash. Default "secret". Hashes must be in a supported format; plain text is possible but not recommended.
• columnClass: column holding the account kind (individual, person, or group). Default "type".
• columnDescription: column holding the account’s full name or description. Default "description".

There is no column mapping for a per-account disk quota: quotas are held on the Account and Tenant objects rather than read from the SQL directory (see Quotas).

For example:

{
  "@type": "Sql",
  "description": "External SQL directory",
  "store": {
    "@type": "Default"
  },
  "columnEmail": "name",
  "columnSecret": "secret",
  "columnDescription": "description",
  "columnClass": "type"
}

Sample directory schema

The following schema is a reference example for an SQL-backed directory. It may need to be adapted for specific deployments; it is not intended to be used as-is.

Table schema

SQLite

CREATE TABLE accounts (name TEXT PRIMARY KEY, secret TEXT, description TEXT, type TEXT NOT NULL, active BOOLEAN DEFAULT 1)
CREATE TABLE group_members (name TEXT NOT NULL, member_of TEXT NOT NULL, PRIMARY KEY (name, member_of))
CREATE TABLE emails (name TEXT NOT NULL, address TEXT NOT NULL, type TEXT, PRIMARY KEY (name, address))

PostgreSQL

CREATE TABLE accounts (name TEXT PRIMARY KEY, secret TEXT, description TEXT, type TEXT NOT NULL, active BOOLEAN DEFAULT true);
CREATE TABLE group_members (name TEXT NOT NULL, member_of TEXT NOT NULL, PRIMARY KEY (name, member_of));
CREATE TABLE emails (name TEXT NOT NULL, address TEXT NOT NULL, type TEXT, PRIMARY KEY (name, address));

MySQL

CREATE TABLE accounts (name VARCHAR(32) PRIMARY KEY, secret VARCHAR(1024), description VARCHAR(1024), type VARCHAR(32) NOT NULL, active BOOLEAN DEFAULT 1);
CREATE TABLE group_members (name VARCHAR(32) NOT NULL, member_of VARCHAR(32) NOT NULL, PRIMARY KEY (name, member_of));
CREATE TABLE emails (name VARCHAR(32) NOT NULL, address VARCHAR(128) NOT NULL, type VARCHAR(32), PRIMARY KEY (name, address));

Creating user accounts

Before inserting an account, hash the password. One way is via openssl, for example to produce a SHA-512 crypt hash:

$ openssl passwd -6

Once the hashed secret is available, insert the account and its primary address:

INSERT INTO accounts (name, secret, description, type) VALUES ('<ACCOUNT_NAME>', '<HASHED_SECRET>', '<ACCOUNT_FULL_NAME>', 'individual')
INSERT INTO emails (name, address, type) VALUES ('<ACCOUNT_NAME>', '<PRIMARY_EMAIL_ADDRESS>', 'primary')

Replace:

• <ACCOUNT_NAME> with the account login name, for example john.
• <HASHED_SECRET> with the hashed password produced above.
• <ACCOUNT_FULL_NAME> with the account’s full name, for example John Doe.
• <PRIMARY_EMAIL_ADDRESS> with the primary address, for example [email protected].

Adding an email alias

To add an alias to an existing account:

INSERT INTO emails (name, address, type) VALUES ('<ACCOUNT_NAME>', '<EMAIL_ALIAS>', 'alias')

For example, to add the aliases [email protected] and [email protected] to the account john:

INSERT INTO emails (name, address, type) VALUES ('john', '[email protected]', 'alias')
INSERT INTO emails (name, address, type) VALUES ('john', '[email protected]', 'alias')

Alternatively, the postmaster account can be configured as the catch-all recipient for example.org by adding @example.org as an alias:

INSERT INTO emails (name, address, type) VALUES ('postmaster', '@example.org', 'alias')

Creating group accounts

INSERT INTO accounts (name, description, type) VALUES ('<GROUP_NAME>', '<GROUP_DESCRIPTION>', 'group')

Replace <GROUP_NAME> with the group name and <GROUP_DESCRIPTION> with a human-readable description.

Adding members to a group

INSERT INTO group_members (name, member_of) VALUES ('<ACCOUNT_NAME>', '<GROUP_NAME>')