mod_auth_postfixadmin

Introduction

Authenticate users against a PostfixAdmin-style mailbox table in an SQL database. Unlike the stock mod_auth_sql, this module is tailored to the PostfixAdmin schema (local_part, domain, password, active, xmpp_active, …) and additionally supports SCRAM-SHA-1/SCRAM-SHA-256 (and their -PLUS variants) as well as password changes.

On a successful PLAIN login the module derives and stores SCRAM secrets for all supported algorithms in the scram_secrets column, so subsequent logins can use SCRAM.

Configuration

As with all auth modules, there is no need to add this to modules_enabled. Simply add in the global section, or for the relevant hosts:

    authentication = "postfixadmin"

Database connection (reuses mod_storage_sql style; sql also works as fallback):

    auth_postfixadmin = { driver = "PostgreSQL", database = "postfixadmin",
                         username = "postfixro", password = "secret",
                         host = "localhost" }

Optional password-hash algorithm:

    auth_postfixadmin_password_hash = "bf"

Valid values are the gen_salt() types supported by pgcrypto: des, xdes, md5, bf (bcrypt, default), sha256crypt and sha512crypt (sha256crypt/sha512crypt require PostgreSQL 18 or later). MySQL/MariaDB only supports bf; any other value is rejected at startup.

SCRAM advertisement:

    auth_postfixadmin_advertise_scram = false

Defaults to false. Set to true to start offering SCRAM-SHA-1/256 and their -PLUS variants; PLAIN remains available. This is effectively a one-way switch: once enabled, do not disable it again. See SCRAM provisioning before enabling it on an existing deployment.

SCRAM provisioning

SCRAM only works for accounts that already have secrets in scram_secrets. These are created on a successful PLAIN login (or when setting a password), so a mailbox that has never logged in through this module cannot use SCRAM.

SCRAM mechanisms are advertised before the username is known, and some clients (notably Gajim) select SCRAM first and never fall back to PLAIN when the SCRAM attempt fails. Such users cannot log in at all until secrets exist for their account.

Therefore, SCRAM advertisement must be disabled:

    auth_postfixadmin_advertise_scram = false

until every user has logged in with PLAIN at least once. Those logins store their scram_secrets; only then change the option to true and reload Prosody. Accounts created later, and accounts whose password is changed outside this module, may still have missing or stale secrets, so repeat the PLAIN login (or use password setting) before relying on SCRAM.

Once this option has been set to true, do not set it back to false. Clients that have authenticated with SCRAM keep selecting SCRAM and do not fall back to PLAIN, so disabling advertisement again means nothing will be able to re-authenticate. Treat this flag as one-way for an existing deployment.

Database schema

Requires a mailbox table with at least:

• local_part - First part of the JID, before the @
• domain - Domain part of the JID
• active - '1' when the mailbox is active
• xmpp_active - boolean, TRUE when XMPP login is allowed
• password - Password hash (CRYPT/ENCRYPT compatible)
• scram_secrets - JSON/JSONB column for SCRAM secrets (nullable)
• created - Mailbox creation time (used for account info)
• modified - Last modification time (used as password_updated)

xmpp_active and scram_secrets are not part of the stock PostfixAdmin schema, so add them:

-- PostgreSQL
CREATE EXTENSION IF NOT EXISTS pgcrypto;
ALTER TABLE mailbox ADD COLUMN IF NOT EXISTS scram_secrets jsonb;
ALTER TABLE mailbox ADD COLUMN IF NOT EXISTS xmpp_active boolean NOT NULL DEFAULT TRUE;

-- MySQL / MariaDB
ALTER TABLE mailbox ADD COLUMN scram_secrets TEXT;
ALTER TABLE mailbox ADD COLUMN xmpp_active TINYINT(1) NOT NULL DEFAULT 1;

pgcrypto is required for CRYPT() and gen_salt().

Database privileges

The module only reads the columns used for authentication and account info. It writes scram_secrets on successful PLAIN logins, and password, scram_secrets and modified when changing a password, so an existing read-only user such as postfixro only needs column-level grants:

-- PostgreSQL
GRANT CONNECT ON DATABASE postfixadmin TO postfixro;
GRANT USAGE ON SCHEMA public TO postfixro;
GRANT SELECT (local_part, domain, active, xmpp_active, password, scram_secrets, created, modified)
    ON mailbox TO postfixro;
GRANT UPDATE (password, scram_secrets, modified) ON mailbox TO postfixro;

-- MySQL / MariaDB
GRANT SELECT (local_part, domain, active, xmpp_active, password, scram_secrets, created, modified),
      UPDATE (password, scram_secrets, modified)
    ON postfixadmin.mailbox TO 'postfixro'@'localhost';

This permits reading the authentication columns and updating only those three columns; updating anything else (e.g. active, domain) or selecting other columns is denied. The database/schema/host names must match the configured connection.

The role must not own the mailbox table, since owners bypass these grants. On PostgreSQL, crypt() and gen_salt() need no extra grant — pgcrypto functions are executable by PUBLIC by default.

Without the UPDATE grants PLAIN authentication still works, but SCRAM secrets can never be stored, so SCRAM logins always fail and every successful login logs a permission error.

Account information

get_account_info() returns:

• created - mailbox.created as a Unix timestamp
• password_updated - mailbox.modified as a Unix timestamp. The module updates it when changing a password via set_password(), but PostfixAdmin also updates modified on any mailbox change, so it is only an approximation of the last password change.
• enabled - true when active = '1' AND xmpp_active = '1'

This is used by modules such as mod_account_activity, mod_tokenauth and mod_user_account_management. It is also available through the user-manager API:

    require("core.usermanager").get_account_info("test", "localhost")

Compatibility

• PostgreSQL (recommended; all gen_salt() types: bf, md5, des, xdes, plus sha256crypt/sha512crypt on PostgreSQL 18+)
• MariaDB (bcrypt via ENCRYPT; needs a libc crypt implementation with bcrypt)
• MySQL 8.0+ removed ENCRYPT(), so only MySQL 5.7 or older would work there, and only if libc crypt supports bcrypt

Notes

• set_password is supported on PostgreSQL and MySQL, and updates password, scram_secrets and modified in the same statement.
• get_password is not supported (passwords are stored hashed).
• SCRAM cannot be derived from a stored password hash, so it only works for accounts that already have scram_secrets. SASL mechanisms are advertised before the username is known, so SCRAM is offered by default even for accounts without secrets; use auth_postfixadmin_advertise_scram = false until every account has logged in once with PLAIN. Accounts without secrets can still use PLAIN, and are upgraded on their next successful PLAIN login (or via set_password).
• Password verification uses constant-time comparison.
• user_exists() reports whether the mailbox row exists, regardless of active/xmpp_active; is_enabled() reports whether login is allowed.

sudo prosodyctl install --server=https://modules.prosody.im/rocks/ mod_auth_postfixadmin