Please read the previous part first
This post is the sixth in the "Setting Up a Mail Server on Linux" series.
In the previous part, we completed the design of a virtual user account table using PostgreSQL.
If you haven't checked it out yet, I recommend reading it first.
👉 Previous Part: Dovecot Virtual User Concept and PostgreSQL Table Design

What is Dovecot's auth-sql.conf.ext configuration?

To authenticate virtual users via a database rather than system accounts, you need to configure the auth-sql.conf.ext file in Dovecot.
This configuration allows Dovecot to retrieve user information from PostgreSQL for authentication.

SQL Authentication Settings for Dovecot (auth-sql.conf.ext)

The key items that need to be configured for Dovecot to connect to PostgreSQL are as follows:

1. Configuration File Path

The configuration file is located at the following path:

/etc/dovecot/conf.d/auth-sql.conf.ext

If the file does not exist, you can create it with the following command:

sudo touch /etc/dovecot/conf.d/auth-sql.conf.ext

2. Key Configuration Items

driver = sql
connect = host=localhost dbname=mail user=mailadmin password=yourpassword

default_pass_scheme = SHA256-CRYPT

password_query = SELECT email AS user, password FROM mail_users WHERE email='%u';

user_query = SELECT home_directory AS home FROM mail_users WHERE email='%u';

Configuration Description

• driver = sql → Use SQL-based authentication
• connect = host=localhost dbname=mail user=mailadmin password=yourpassword → PostgreSQL connection information
• default_pass_scheme = SHA256-CRYPT → Specify the default password hashing algorithm; if using bcrypt, set it to 'BLF-CRYPT'
• password_query → Compare the entered password with the hashed value retrieved from the database
• user_query → Return the user's email storage path

3. Restart the Dovecot Service

After applying the configuration, restart Dovecot.

sudo systemctl restart dovecot

Testing Dovecot and PostgreSQL Integration

Use the following command to check if the configuration has been applied correctly:

1. Authentication Test

doveadm auth test user@example.com password123

If configured correctly, passdb authentication should succeed.

2. Check the Error Log

If the configuration has not been applied correctly, you can check the logs to analyze the issue.

journalctl -xe | grep dovecot

Password Hashing Methods (Comparison and Application of MD5, SHA-256, BCRYPT)

When integrating Dovecot and PostgreSQL, passwords must be stored in a hashed format.
The security level and performance vary depending on the hashing method used.

1. Comparison of Hashing Methods

It is advised not to use MD5 due to its vulnerabilities.
SHA-256 is commonly used and balances speed and security well.
BCRYPT provides the strongest security, but its slow speed should be considered in performance-critical environments.

Hashing with Dovecot Command (doveadm pw)

You can also hash passwords directly in Dovecot.

1. SHA-256 Hashing

doveadm pw -s SHA256-CRYPT

After inputting the password, a hash value like the following will be generated:

{SHA256-CRYPT}$5$rounds=5000$abc123def456$ABCDEF1234567890ABCDEF1234567890

2. BCRYPT Hashing

doveadm pw -s BLF-CRYPT

Output example:

{BLF-CRYPT}$2a$05$abcdefghij1234567890ABCDEFGHIJKL$CDEFGHIJKLMNOPQRSTUVWXYZ123456

This hash value should be stored in the mail_users table in PostgreSQL.

Hashing Directly in PostgreSQL (pgcrypto Extension Usage)

Using the pgcrypto extension in PostgreSQL allows hashing passwords without doveadm.

1. Activating the pgcrypto Extension

CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. Storing Passwords with SHA-256 Hashing

INSERT INTO mail_users (domain_id, email, password, home_directory)
VALUES (1, 'user@example.com', crypt('mypassword', gen_salt('bf')), '/var/mail/vmail/user@example.com');

3. Storing Passwords with BCRYPT Hashing

INSERT INTO mail_users (domain_id, email, password, home_directory)
VALUES (1, 'user@example.com', crypt('mypassword', gen_salt('bf', 12)), '/var/mail/vmail/user@example.com');

Summary and Next Steps

Topics covered in this post - How to configure Dovecot's auth-sql.conf.ext
- How to test integration between Dovecot and PostgreSQL
- How to apply SHA-256 and BCRYPT hashing methods
- How to hash directly in PostgreSQL

Next Part Preview: Integrating SMTP Authentication of Postfix and Dovecot
Now that Dovecot is configured, the next step is how to set up Postfix to utilize Dovecot's authentication.

👉 Next Part: Integrating SMTP Authentication of Postfix and Dovecot (Coming Soon!)