There is several ways to do this. The easiest one however is to have only one software do the authentication - in your case this would be Dovecot as postfix can be configured to use Dovecot SASL for authentication.
It is also convenient to only have one software managing directories for emails, so I recommend to use Dovecot as LDA (local delivery agent aka "the software that puts files in directories") instead of letting postfix do this itself.
Below I will show you the relevant parts from my config, keep in mind thogh that those are in no way complete, working configurations. There is no point in posting my complete mail stack config though as your exact needs will likely differ from mine.
I use PostgreSQL as database, however MySQL should work just as well - just make sure to change the driver and also install an matching driver if your dovecot does not ship with one.
From postfix/main.cf
virtual_mailbox_domains = $mydomain, pgsql:/etc/postfix/pgsql_domains.cf
virtual_alias_maps = pgsql:/etc/postfix/pgsql_aliases.cf
virtual_mailbox_maps = pgsql:/etc/postfix/pgsql_mailboxes.cf
######################
### Authentication ###
######################
# Basic
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/dovecot-auth
smtpd_sasl_auth_enable = yes
smtpd_sasl_local_domain = $mydomain
# Allowed Methods
smtpd_tls_auth_only = yes
smtpd_sasl_security_options = noanonymous
smtpd_sasl_tls_security_options = noanonymous
########################
### Mailbox Settings ###
########################
mail_spool_directory = /srv/mail/localhost/
virtual_mailbox_base = /srv/mail/
mailbox_command = /usr/lib/dovecot/dovecot-lda -f "$SENDER" -a
"$RECIPIENT" -d "$USER"
virtual_transport = dovecot
Make sure to also set your client restrictions to include permit_sasl_authenticated but exclude other senders for outgoing email to not create an open relay.
content of /etc/postfix/pgsql_domains.cf
hosts = /var/run/postgresql
user = mail
dbname = mail
query = SELECT DISTINCT 1 FROM users WHERE domain='%s';
contents of /etc/postfix/pgsql_aliases.cf
hosts = /var/run/postgresql
user = mail
dbname = mail
query = SELECT email||'@'||domain AS alias FROM users WHERE '%u'='users' AND domain='%d';
contents of /etc/postfix/pgsql_mailboxes.cf
hosts = /var/run/postgresql
user = mail
dbname = mail
query = SELECT 1 FROM email WHERE email='%u' AND domain='%d';
from postfix/master.cf
dovecot unix - n n - - pipe
flags=DRhu user=mail:mail argv=/usr/lib/dovecot/dovecot-lda -f ${sender} -a ${recipient} -d ${user}@${nexthop}
From dovecot config (doesn't matter if you put it in a file that gets included somewhere or directly in the main config):
service auth {
#-- Default Socket
unix_listener auth-userdb {
mode = 0666
user = mail
group = mail
}
#-- Socket for Postfix
unix_listener /var/spool/postfix/private/dovecot-auth {
mode = 0660
user = postfix
group = postfix
}
}
######################
### Authentication ###
######################
passdb {
driver = sql
args = /etc/dovecot/conf.d/sql-login.conf.ext
}
userdb {
driver = sql
args = /etc/dovecot/conf.d/sql-login.conf.ext
}
#########################
### Location Settings ###
#########################
# %u - username
# %n - user part in user@domain, same as %u if there's no domain
# %d - domain part in user@domain, empty if there's no domain
# %h - home directory
mail_home = /srv/mail/%d/%n
mail_location = maildir:~/mai
Adjust /var/spool/postfix to wherever your postfix is chrooted to (if it is, but it should be)
Contents of /etc/dovecot/conf.d/sql-login.conf.ext referenced above:
driver = pgsql
connect = host=/var/run/postgresql/ dbname=mail user=mail
default_pass_scheme = SHA512
password_query = SELECT email as username, domain, password FROM users WHERE email = '%n' AND domain = '%d';
user_query = SELECT email as username, domain FROM users WHERE email = '%n' AND domain = '%d';
iterate_query = SELECT email as username, domain FROM users;
(Make sure to adjust the connection string according to your database host, db name, user and possibly also password)
My database layout:
Table "mail.users"
Column | Type | Modifiers
---------------+---------+---------------
email | text | not null
domain | text | not null
password | text |
Indexes:
"users_pkey" PRIMARY KEY, btree (email, domain)
Keep in mind that those are taken literally from my mail config and might need adjustments and need to be integrated in your current config. This is especially true for dovecot, where your distribution might or might not already have some of the settings in files scattered around your config directory - it is common for dovecot distributions to use a lot of includes.
ALso make sure that inbox folders for users /the config above puts them in /srv/mail) get created and dovecot-lda can write and access all folders.
The config above also delivers email of local email users to /srv/mail/localhost for consistency, this is fully optional though and you can deliver them wherever you already have them.
You can do a lot more once you have that setup - configure master users who can login as anybody, configure blocklists for temporarily disabled accounts, configure alias emails, and so on. But what is above should be sufficient to authenticate against a DB and deliver email to virtual user accounts.
[Edit]: I just realized that Postfix DOES need some lookups for email-relaying, specifically for virtual_domain, virtual_alias and virtual_mailbox configurations. I added the relevant parts.
